Mysql order by与limit混用陷阱

缺乏、安全感 2021-10-01 06:40 361阅读 0赞

在Mysql中我们常常用order by来进行排序，使用limit来进行分页，当需要先排序后分页时我们往往使用类似的写法select \* from 表名 order by 排序字段 limt M,N。但是这种写法却隐藏着较深的使用陷阱。在排序字段有数据重复的情况下，会很容易出现排序结果与预期不一致的问题。

比如现在有一张user表，表结构及数据如下：

![2][]

表结构

![2 1][]

表数据

现在想根据创建时间升序查询user表，并且分页查询，每页2条，那很容易写出sql为：select \* from user order by create\_time limit pageNo,2;

在执行查询过程中会发现：  
1、查询第一页数据时：

![2 2][]

第一页查询结果

2、查询第四页数据时：

![2 3][]

第四页查询结果

user表共有8条数据，有4页数据，但是实际查询过程中第一页与第四页竟然出现了相同的数据。

这是什么情况？难道上面的分页SQL不是先将两个表关联查询出来，然后再排好序，再取对应分页的数据吗？？？

上面的实际执行结果已经证明现实与想像往往是有差距的，实际SQL执行时并不是按照上述方式执行的。这里其实是Mysql会对Limit做优化，具体优化方式见官方文档：[https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html][https_dev.mysql.com_doc_refman_5.7_en_limit-optimization.html]  
这个是5.7版本的说明，提取几个问题直接相关的点做下说明。

![2 4][]

Paste\_Image.png

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

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

![2 5][]

Paste\_Image.png

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

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

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

![2 6][]

Paste\_Image.png

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

基于这个我们就基本知道为什么分页会不准了，因为我们排序的字段是create\_time，正好又有几个相同的值的行，在实际执行时返回结果对应的行的顺序是不确定的。对应上面的情况，第一页返回的name为8的数据行，可能正好排在前面，而第四页查询时name为8的数据行正好排在后面，所以第四页又出现了。

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

官方给出了解决方案：

![2 7][]

Paste\_Image.png

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

所以上面的情况下可以在SQL再添加个排序字段，比如fund\_flow的id字段，这样分页的问题就解决了。修改后的SQL可以像下面这样：  
SELECT \* FROM `user` ORDER BY create\_time,id LIMIT 6,2;

再次测试问题解决！！

用心写代码，不辜负程序员之名。

前言：在使用order by时，经常出现Using filesort，因此对于此类sql语句需尽力优化，使其尽量使用Using index。

--------------------

### 0.准备 ###

\#1.创建test表。

![复制代码][copycode.gif]

drop table if exists test;
    create table test(
    id int primary key auto_increment,
    c1 varchar(10),
    c2 varchar(10),
    c3 varchar(10),
    c4 varchar(10),
    c5 varchar(10)
    ) ENGINE=INNODB default CHARSET=utf8;
    
    insert into test(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');
    insert into test(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');
    insert into test(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');
    insert into test(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');
    insert into test(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');

![复制代码][copycode.gif]

\#2.创建索引。

![706569-20180625223438203-1033999353.png][]

### 1.根据Case分析order by的使用情况 ###

Case 1：

![706569-20180625223944999-13584041.png][]

分析：

①在c1,c2,c3,c4上创建了索引，直接在c1上使用范围，导致了索引失效，全表扫描：type=ALL，ref=Null。因为此时c1主要用于排序，并不是查询。

②使用c1进行排序，出现了Using filesort。

③解决方法：使用覆盖索引。

![706569-20180625225150972-757485019.png][]

Case 1.1：

![706569-20180625225605390-1287916353.png][]

分析：

排序时按照索引的顺序，所以不会出现Using filesort。

Case 1.2：

![706569-20180625230033609-1505851091.png][]

分析：

出现了Using filesort。原因：排序用的c2，与索引的创建顺序不一致，对比Case1.1可知，排序时少了c1（带头大哥），因此出现Using filesort。

Case 1.3：

![706569-20180625230403937-1781972191.png][]

分析：

出现了Using filesort。因为排序索引列与索引创建的顺序相反，从而产生了重排，也就出现了Using filesort。

Case 2：

![706569-20180625231117168-925966033.png][]

![706569-20180625231342815-2077452190.png][]

分析：

直接使用c2进行排序，出现Using filesort，因为不是从最左列索引开始排序的（没有带头大哥）。

Case 2.1：

![706569-20180625231451690-583449657.png][]

分析：

排序使用了索引顺序（带头大哥在），因此不会出现Using filesort。

Case 2.2：

![706569-20180625231710896-1971557040.png][]

分析：

虽然排序的字段列与索引顺序一样，且order by默认升序，这里c2 desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。

### 总结： ###

①MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。

②order by满足两种情况会使用Using index。

\#1.order by语句使用索引最左前列。

\#2.使用where子句与order by子句条件列组合满足索引最左前列。

③尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最佳左前缀法则。

④如果order by的条件不在索引列上，就会产生Using filesort。

\#1.filesort有两种排序算法：双路排序和单路排序。

双路排序：在MySQL4.1之前使用双路排序，就是两次磁盘扫描，得到最终数据。读取行指针和order by列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对应的数据输出。即从磁盘读取排序字段，在buffer进行排序，再从磁盘取其他字段。

如果使用双路排序，取一批数据要对磁盘进行两次扫描，众所周知，I/O操作是很耗时的，因此在MySQL4.1以后，出现了改进的算法：单路排序。

单路排序：从磁盘中查询所需的列，按照order by列在buffer中对它们进行排序，然后扫描排序后的列表进行输出。它的效率更高一些，避免了第二次读取数据，并且把随机I/O变成了顺序I/O，但是会使用更多的空间，因为它把每一行都保存在内存中了。

\#2.单路排序出现的问题。

当读取数据超过sort\_buffer的容量时，就会导致多次读取数据，并创建临时表，最后多路合并，产生多次I/O，反而增加其I/O运算。

解决方式：

a.增加sort\_buffer\_size参数的设置。

b.增大max\_length\_for\_sort\_data参数的设置。

⑤提升order by速度的方式：

\#1.在使用order by时，不要用select \*，只查询所需的字段。

因为当查询字段过多时，会导致sort\_buffer不够，从而使用多路排序或进行多次I/O操作。

\#2.尝试提高sort\_buffer\_size。

\#3.尝试提高max\_length\_for\_sort\_data。

⑥附上一张从视频中截取出来的总结图。

![706569-20180626091734130-496909449.png][]

⑦group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最佳左前缀法则。当无法使用索引列的时候，也要对sort\_buffer\_size和max\_length\_for\_sort\_data参数进行调整。注意where高于having，能写在where中的限定条件就不要去having限定了。

--------------------

by Shawn Chen，2018.6.26日，上午。

--------------------

### 相关内容 ###

[MySQL高级知识系列目录][MySQL]

### ========================================================= ###

### 比你优秀的人比你还努力，你有什么资格不去奋斗！ ###

### \_\_一个有理想的程序员。 ###

### ========================================================= ###

参考 [ https://www.cnblogs.com/developer\_chan/category/1020652.html][https_www.cnblogs.com_developer_chan_category_1020652.html]

转载于:https://www.cnblogs.com/yuluoxingkong/p/10681583.html

[2]: /images/20210724/dfbd61ef8ac1475087f0baddcec45871.png
[2 1]: /images/20210724/c1e6916575ae454a8f22657c24dd8292.png
[2 2]: /images/20210724/d058cb06c0e4455eab991eb5d4d39928.png
[2 3]: /images/20210724/645f6275b0d646ec95dd83caacb1270d.png
[https_dev.mysql.com_doc_refman_5.7_en_limit-optimization.html]: https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html
[2 4]: /images/20210724/d4ad5e3a2d4444a485a7b6d3b0adcc71.png
[2 5]: /images/20210724/d194233011e74ad8a1149f9c39977959.png
[2 6]: /images/20210724/387732fdbbdb4f06b5601609acb917db.png
[2 7]: /images/20210724/5464a8a32d4442818aed84fb4af9c97a.png
[copycode.gif]: /images/20210724/b4c07aa6a87a474bbb33f798d450689f.png
[706569-20180625223438203-1033999353.png]: /images/20210724/132502bae6bb40bbb7471352a3042781.png
[706569-20180625223944999-13584041.png]: /images/20210724/7a4c61f26a2f4e9ea6a8993dc5e033b1.png
[706569-20180625225150972-757485019.png]: /images/20210724/1450ca1a3315478f9712f39942e98d33.png
[706569-20180625225605390-1287916353.png]: /images/20210724/221a6b659ed84404b165cf901ae0e641.png
[706569-20180625230033609-1505851091.png]: /images/20210724/602bd2565d9545b98da220a2e38cc19b.png
[706569-20180625230403937-1781972191.png]: /images/20210724/354ad21ac71c4a1caf50475df3eef4d1.png
[706569-20180625231117168-925966033.png]: /images/20210724/b513d588f72247b9b17d293b5abb8b69.png
[706569-20180625231342815-2077452190.png]: /images/20210724/368f3bc9bd2149e995a21fd3e19dccb9.png
[706569-20180625231451690-583449657.png]: /images/20210724/ad90f5c1c5344f51a572d9da8db3e589.png
[706569-20180625231710896-1971557040.png]: /images/20210724/0c0fab29f6414b959c13e31bb239d242.png
[706569-20180626091734130-496909449.png]: /images/20210724/f96b075cce2244018cb9016a64158b73.png
[MySQL]: https://www.cnblogs.com/developer_chan/p/9234769.html
[https_www.cnblogs.com_developer_chan_category_1020652.html]: https://www.cnblogs.com/developer_chan/category/1020652.html