问题描述
当MySQL版本>=5.6时，使用order by field limit n，如果数据行中filed字段有多个值相同且大于n时，每次取出来的n条数据是随机的。最常见的问题就是，分页展示列表时，第i页和第i+1页可能展示同一条数据。
重现问题
废话不多说，直接上demo
先创建表a和b
CREATE TABLE `a` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`value` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `b` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`aid` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `a`(`id`,`value`) values (1,2),(2,2),(3,5),(4,2),(5,2),(6,2),(7,5),(8,2),(9,2);
insert into `b`(`id`,`aid`) values (1,2),(2,3),(3,6),(4,1),(5,4),(6,5),(7,9),(8,8);
插入完后表a的数据
| id | value |
|---|---|
| 1 | 2 |
| 2 | 2 |
| 3 | 5 |
| 4 | 2 |
| 5 | 2 |
| 6 | 2 |
| 7 | 5 |
| 8 | 2 |
| 9 | 2 |
表b的数据
| id | aid |
|---|---|
| 1 | 2 |
| 2 | 3 |
| 3 | 6 |
| 4 | 1 |
| 5 | 4 |
| 6 | 5 |
| 7 | 9 |
| 8 | 8 |
使用使用JDBC执行SQL
SELECT a.id FROM a INNER JOIN b ON b.aid=a.id ORDER BY a.value ASC LIMIT 4
执行结果
| 第一次 | 第二次 | 第三次 |
|---|---|---|
| 9 | 1 | 6 |
| 8 | 4 | 1 |
| 2 | 5 | 4 |
| 6 | 9 | 5 |
a表中有7条value为2的数据，取出4条，每次得到的结果不一样，甚至是随机数据，而且也不是根据物理排序或者id排序。
结论
当使用order by value 和 limit n共用时，如果数据行中value字段有多个值相同且大于n时，每次取出来的n条数据是随机的。
可能会影响的问题
- 每次取出来的数据随机问题
- 分页错乱和数据重复问题
解决方案
order by a.value改成order by a.value,a.id即可保证当value相同时按照id排序
原因
MySQL5.6及以后的版本使用order by field limit进行查询时，会做一个优化，使用优先队列排序，即priority queue，优先队列即使用最小/大堆实现。
以下是MySQL5.7官方文档： dev.mysql.com/doc/refman/…
If you combine LIMIT row_count with ORDER BY, MySQL stops sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause are selected, and most or all of them are sorted, before the first row_count are found. After the initial rows have been found, MySQL does not sort any remainder of the result set.
One manifestation of this behavior is that an ORDER BY query with and without LIMIT may return rows in different order, as described later in this section.
也就是说，MySQL并不会对ORDER BY后的数据全部排序再取n条，而是只要取到n条最小/大的就行。
这是不是让你想起了一个算法问题： 一堆数里面获取最小的n个数的问题？使用最大堆(堆大小为n)即可完成。
感悟
作为一个程序员，不仅要知其然还应知其所以然，宇宙间的奥秘和原理总是惊人的相似，要学会融会贯通。
