陷阱之MySql中ORDER BY和LIMIT混合使用

今天线上项目测试人员反馈按照年级查询不到个别班级下的学生数据，后来把查询的SQL拿下来测试了一下，按照分页方式一页页跳转发现确实少了很多数据，这是什么原因导致的呢，SQL如下：

首先第一想法就是我按照排序以后获取数据全部数据的顺序应该是如下图所示的：

如果我按照limit0,10分页的话我应该获取的是前10条数据，如下图所示：

但是结果确是和预期的不一致，如下图所示：

那这是怎么一回事呢？我们实现查询数据量大时往往需要分页来实现，而且需要显示最新的数据在前面，MySql中我们常常使用 order by来排序，limit来进行分页，通常的写法是select * from user order by create_time limit m,n,但是这种写法却隐藏着较深的使用陷阱。在排序字段有数据重复的情况下，会很容易出现排序结果与预期不一致的问题。

找到问题的所在就很容易解决了，官方文档也已经给出了解决方案：这里其实是MySql会对limit做优化，具体优化方式见官方文档：https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html

因为我的MySql版本是5.7，所以用的5.7版本的说明，只获取了几个和上面问题相关的说明一下

如果你将limit row_count与order by混用，MySql会找到排序的row_count行后立马返回，而不是排序整个查询结果再返回。如果是通过索引排序，会非常快；如果是文件排序，所有匹配查询的行（不带limit的）都会被选中，被选中的大多数或者全部会被排序，直到limit要求的row_count被找到了。如果limit要求的row_count行一旦被找到，MySql就不会排序结果集中剩余的行了。

这里我们查看下对应SQL的执行计划：

可以确认是用的文件排序，表确实也没有加额外的索引。所以我们可以确定这个SQL执行时是会找到limit要求的行后立马返回查询结果的。

不过就算它立马返回，为什么分页会不准呢？

官方文档里面做了如下说明：

如果order by的字段有多个行都有相同的值，MySql是会随机的顺序返回查询结果的，具体依赖对应的执行计划。也就是说如果排序的列是无序的，那么排序的结果行的顺序也是不确定的。

基于这个我们就基本知道为什么分页会不准了，因为我们排序的字段是create_time，而时间都是相同的值，在实际执行时返回结果对应的行的顺序是不确定的，既然不确定那就有可能出现重复查询的数据了。

那这种情况应该怎么解决呢？

官方给出了解决方案：

如果想在limit存在或不存在的情况下，都保证排序结果相同，可以额外加一个排序条件。例如id字段是唯一的，可以考虑在排序字段中额外加个id排序去确保顺序稳定。

所以上面的情况下我在SQL中以id字段进行排序，修改后的SQL如下图所示：

最后问题都解决了，保证项目bug无处遁形。哈哈哈