开发者社区> 北在南方> 正文

【MySQL】order by 结果不准确的问题及解决

简介:
+关注继续查看
一 介绍 
  相信作为DBA 在和开发打交道的过程中,经常会遇到分页查询 order by 排序这样的需求。本文源于生产过程中的案例,5.6版本的数据库使用limit和order by 一个非唯一字段时,结果集并不总是确定的.已经确定为bug,详见:MySQL 官方的bug 
提醒读者朋友注意。
二 分析 

环境准备 


CREATE TABLE `tb1` (
 
  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  `a` decimal(19,2) NOT NULL,

  `acid` bigint(20) NOT NULL,

  `prid` bigint(20) NOT NULL,

  PRIMARY KEY (`id`),

  KEY `idx_prid` (`prid`),

  KEY `idx_acid` (`acid`)

) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 

注意字段a 上面是没有索引的。
初始化数据


INSERT INTO `tb1` (`id`, `a`, `acid`, `prid`) 
 
VALUES (1,2.00,3,2),(2,3.00,3,2),(3,4.00,2,3),(4,5.00,2,3),(5,6.00,2,3),(6,8.00,2,3),(7,10.00,2,3),(8,12.00,2,3),(9,16.00,2,3),(10,20.00,2,3),(11,6.00,2,4),(12,8.00,2,4),(13,10.00,2,4),(14,12.00,2,4),(15,5.00,2,2),(16,6.00,2,2); 

执行两个 根据非索引字段且有重复值的 order by 排序


mysql> select * from tb1 order by a desc limit 4;
 
+----+-------+------+------+

| id | a     | acid | prid |

+----+-------+------+------+

| 10 | 20.00 | 2    | 3    |

| 9  | 16.00 | 2    | 3    |

| 14 | 12.00 | 2    | 4    |

| 8  | 12.00 | 2    | 3    |

+----+-------+------+------+

4 rows in set (0.00 sec) 

得到id 为10, 9, 14, 8 的结果集


mysql> select * from tb1 order by a desc limit 3;
 
+----+-------+------+------+

| id | a     | acid | prid |

+----+-------+------+------+

| 10 | 20.00 | 2    | 3    |

| 9  | 16.00 | 2    | 3    | 
| 8  | 12.00 | 2    | 3    |

+----+-------+------+------+

3 rows in set (0.00 sec) 

得到id 为10 9 8 的结果集
为a字段加上索引 


mysql> alter table tb1 add key ind_tb1a(a);
 
Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from tb1 order by a desc limit 3; 

+----+-------+------+------+

| id | a     | acid | prid |

+----+-------+------+------+

| 10 | 20.00 | 2    | 3    |

| 9  | 16.00 | 2    | 3    |

| 8  | 12.00 | 2    | 3    |

+----+-------+------+------+

3 rows in set (0.00 sec) 

得到id 为10 9 8 的结果集


mysql> select * from tb1 order by a desc limit 4;
 
+----+-------+------+------+

| id | a     | acid | prid |

+----+-------+------+------+

| 10 | 20.00 | 2    | 3    |

| 9  | 16.00 | 2    | 3    |

| 14 | 12.00 | 2    | 4    |

| 8  | 12.00 | 2    | 3    |

+----+-------+------+------+

4 rows in set (0.00 sec) 

得到id 为10, 9, 14, 8 的结果集
从上面的测试来看对于一个非唯一字段 无论是否含有索引,结果集都是不确定的。

三 解决方法 
1 业务属性确保 a 字段不能唯一,则需要针对排序结果再加上 一个唯一字段的排序 比如id 


mysql> select * from tb1 order by a desc ,id desc limit 4;
 
+----+-------+------+------+

| id | a     | acid | prid |

+----+-------+------+------+

| 10 | 20.00 | 2    | 3    |

| 9  | 16.00 | 2    | 3    |

| 14 | 12.00 | 2    | 4    |

| 8  | 12.00 | 2    | 3    |

+----+-------+------+------+

4 rows in set (0.00 sec) 

mysql> select * from tb1 order by a desc ,id desc limit 3;
 
+----+-------+------+------+

| id | a     | acid | prid |

+----+-------+------+------+

| 10 | 20.00 | 2    | 3    |

| 9  | 16.00 | 2    | 3    |

| 14 | 12.00 | 2    | 4    |

+----+-------+------+------+

3 rows in set (0.00 sec) 

使用order by id/unique_key 排序之后,前三个结果集是一致的10,9,14 。 结果集满足我们的需求。从而解决不确定性带来的问题。
2 是否可以去掉不必要的order by,这个是由业务逻辑决定的。

四 总结
  DBA在和开发沟通/培训过程中要注意这一点 基于非唯一字段的排序 结果集是不确定的。如果业务逻辑对分页或者order by结果集有比较高的严格要求 ,请记得利用唯一键排序。 


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
mysql 查询结果异常分析
--- title: MySQL · mysql · mysql 查询结果异常分析 author: 张远 --- # 现象 查询条件类型变化后,查询出了不正确的结果。 ``` create table t1(id int primary key, a varchar(50) DEFAULT NULL, key idx_a(a)) engine=innodb; sho
1703 0
使用OpenApi弹性释放和设置云服务器ECS释放
云服务器ECS的一个重要特性就是按需创建资源。您可以在业务高峰期按需弹性的自定义规则进行资源创建,在完成业务计算的时候释放资源。本篇将提供几个Tips帮助您更加容易和自动化的完成云服务器的释放和弹性设置。
18670 0
解决Setting property 'source' to 'org.eclipse.jst.jee.server的问题
对于这个问题,我相信我的方法已经能帮90%的人解决了! 当你用Eclipse运行web项目的时候,你就会看到控制台出现:WARNING: [SetPropertiesRule]{Server/Service/Engine/Host/Context} Setting property 'source' to 'org.eclipse.jst.jee.server:firstProject'
1008 0
Python编程:Python2编码问题与pymysql查询结果乱码解决
Python编程:Python2编码问题与pymysql查询结果乱码解决
55 0
解决Mysql中删除重复记录的问题
题记 本文主要介绍Mysql数据库表中,如何删除相同索引值、更新日期较早的记录行。
28 0
App如何实现就近接入?如何改善调度不准问题?
购买阿里云ECS服务器实例规格型号、功能、型号级别介绍及选择
183 0
mysql解决乱码问题
应用场景 在使用mysql数据库的过程中,发现数据导入后中文出现乱码,数据库中出现文字乱码等等,sql语句中查询中文无法查出结果,影响系统使用,以及数据无法正确查询。
729 0
mysql太多连接问题及解决方案
不管是JavaEE开发还是其他,只要是Linux系统下安装的mysql,通常默认最大连接为270。 如果你的客户端连接超过这个数,通常要么是配置文件修改,或者是命令行修改,配置文件修改和命令行修改的区别是,配置文件,既然是文件通常持久化到硬盘,保存最长,如果是命令行的话,仅仅只能保持在Mysql运行状态,如果一旦MySQL因为某种原因宕机或者关闭了,命令行的方式就失效了。
1069 0
+关注
640
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
OceanBase 入门到实战教程
立即下载
阿里云图数据库GDB,加速开启“图智”未来.ppt
立即下载
实时数仓Hologres技术实战一本通2.0版(下)
立即下载