MYSQL中使用order by…limit时候遇到的坑学习心得分享

文章目录[隐藏]

问题复现
深入学习

之前分享过一篇文章《MySQL与MariaDB对子查询中order by的处理的差异》中介绍了MYSQL和MariaDB对order by排序的差异；

最近遇到这样的一个奇怪的问题——SQL中排序分页的查询结果中出现了重复数据。百思不得其解，检查了代码，没有发现任何问题，但这种现象就是会出现。

select * from daimai where 1 and ((`type`=1 and bid='10169') or (`type`=2 and `bid2`='10169')) and time>='2019-03-14' and time <='2019-03-15' order by time desc,ctime desc limit 50; 
select * from daimai where 1 and ((`type`=1 and bid='10169') or (`type`=2 and `bid2`='10169')) and time>='2019-03-14' and time <='2019-03-15' order by time desc,ctime desc limit 0,30; 
select * from daimai where 1 and ((`type`=1 and bid='10169') or (`type`=2 and `bid2`='10169')) and time>='2019-03-14' and time <='2019-03-15' order by time desc,ctime desc limit 30,30;

现象就是在后面两个SQL语句的查询结果中尽然有重复的数据，这在实际的业务中肯定是无法接受的；

通过对这种现象进行分析以及查阅MYSQL官方文档资料，终于找到了弄清了事情的原委。这是一个比较常见的通用问题；

接下来我们就来通过案例来复现一下这个问题，看看到底是什么原因导致出现的这个问题的？又该怎么去解决这个问题？

问题复现

创建测试表

root@10.105.54.118 [wjq]>show create table wjqlimit\G;
*************************** 1. row ***************************
       Table: wjqlimit
Create Table: CREATE TABLE `wjqlimit` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

批量的往表中查询数据，表中总数据量为200

root@10.105.54.118 [wjq]>select count(*) from wjqlimit;
+----------+
| count(*) |
+----------+
|      200 |
+----------+
1 row in set (0.00 sec)

现在想根据time字段过滤wjqlimit表中时间小于2019-03-18 14:59:20，并且分页查询，每页20条，那很容易写出sql为：select * from wjqlimit where time<‘2019-03-18 14:59:20’ order by time limit 20;

在执行查询过程中会发现：

1、首先，执行一次带order by的查询，limit 20。结果为排序前20条数据，不用细看，不会有任何的疑问。

2、然后，执行同样带order by的查询，limit10。结果为排序前10条数据，和limit 20查询结果中的前20项进行比对，发现不一致。留意下框住中的几个数据项。

3、最后，执行同样带order by的查询，limit 10,10。结果为排序第11-20条数据，注意红框中的数据项，竟然和前10条数据有重复，这显然不是我们所期望的结果

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

上面的实际执行结果已经证明现实与想像往往是有差距的，实际SQL执行时并不是按照上述方式执行的。这里其实是Mysql会对Limit做优化，具体优化方式见官方文档：https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html

如下是5.7版本的官方文档说明，提取几个问题直接相关的点做下说明。

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.

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

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

root@10.105.54.118 [wjq]>explain select * from wjqlimit where time<'2019-03-18 14:59:20' order by time limit 20\G;   
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: wjqlimit
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
     filtered: 33.33
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)

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

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

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

If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.

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

If an index is not used for ORDER BY but a LIMIT clause is also present, the optimizer may be able to avoid using a merge file and sort the rows in memory using an in-memory filesort operation.

在ORDER BY + LIMIT的查询语句中，如果ORDER BY不能使用索引的话，优化器可能会使用in-memory sort操作。

详情请参考:https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html#order-by-filesort-in-memory

那这种情况应该怎么解决呢？官方给出了解决方案：

If it is important to ensure the same row order with and without LIMIT, include additional columns in the ORDER BY clause to make the order deterministic. For example, if id values are unique, you can make rows for a given category value appear in id order by sorting like this:

解决方案一

额外加一个排序条件。例如id字段是唯一的，可以考虑在排序字段中额外加个id排序去确保顺序稳定。

解决方案二

为排序的字段上增加一个普通索引

root@10.105.54.118 [wjq]>ALTER TABLE wjqlimit ADD INDEX idx_time(time);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

下面在看一下执行计划：

root@10.105.54.118 [wjq]>explain select * from wjqlimit where time<'2019-03-18 14:59:20' order by time limit 20\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: wjqlimit
   partitions: NULL
         type: range
possible_keys: idx_time
          key: idx_time
      key_len: 4
          ref: NULL
         rows: 99
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

到这里问题就已经解决了，那么我们下面在深入一点去看一下这个问题；

深入学习

使用上述解决方案，问题就解决了，很高兴。但或许你仍然心存疑问，MySQL针对此语句到底进行了怎样的优化，到底是否使用了堆排序算法？我们使用explain语句来看一下，结果如下

root@10.105.54.118 [wjq]>explain select * from wjqlimit where time<'2019-03-18 14:59:20' order by time limit 20\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: wjqlimit
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
     filtered: 33.33
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)

只能看到使用了filesort，但具体使用了怎样的排序算法，从explain的结果看不出来。

继续查资料，阅读上面提到的The In-Memory filesort Algorithm官方文档，可以知道MySQL的filesort有3种优化算法，分别是：

基本filesort
改进filesort
In memory filesort

三种算法在该页面中有介绍，推荐花10分钟阅读。也可以阅读这篇博客MySQL排序内部原理探秘。

官方文档指出

The sort buffer has a size of sort_buffer_size. If the sort elements for N rows are small enough to fit in the sort buffer (M+N rows if M was specified), the server can avoid using a merge file and performs an in-memory sort by treating the sort buffer as a priority queue.

也就是说，In memory filesort使用了优先级队列，而优先级队列的原理就是二叉堆。

下面我们验证一下，真实的查询中是否使用了优先级队列。怎么看呢？官方文档也给出了方法：

MYSQL的优化器Optimizer Tracer非常强大，但没有默认开启：

root@10.105.54.118 [wjq]>SHOW VARIABLES LIKE '%trace%';
+----------------------------------+----------------------------------------------------------------------------+
| Variable_name                    | Value                                                                      |
+----------------------------------+----------------------------------------------------------------------------+
| optimizer_trace                  | enabled=off,one_line=off                                                   |
| optimizer_trace_features         | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |
| optimizer_trace_limit            | 1                                                                          |
| optimizer_trace_max_mem_size     | 16384                                                                      |
| optimizer_trace_offset           | -1                                                                         |
| rpl_semi_sync_master_trace_level | 32                                                                         |
+----------------------------------+----------------------------------------------------------------------------+
6 rows in set (0.01 sec)

手动开启：

root@10.105.54.118 [wjq]>set session optimizer_trace = "enabled=on";              
Query OK, 0 rows affected (0.00 sec)

root@10.105.54.118 [wjq]>SHOW VARIABLES LIKE '%trace%';
+----------------------------------+----------------------------------------------------------------------------+
| Variable_name                    | Value                                                                      |
+----------------------------------+----------------------------------------------------------------------------+
| optimizer_trace                  | enabled=on,one_line=off                                                    |
| optimizer_trace_features         | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |
| optimizer_trace_limit            | 1                                                                          |
| optimizer_trace_max_mem_size     | 16384                                                                      |
| optimizer_trace_offset           | -1                                                                         |
| rpl_semi_sync_master_trace_level | 32                                                                         |
+----------------------------------+----------------------------------------------------------------------------+
6 rows in set (0.01 sec)

接着进行查询。查询执行完后，查看tracer：

root@10.105.54.118 [wjq]>select * from wjqlimit where time<'2019-03-18 14:59:20' order by time limit 20;

Optimizer Tracer使用一个Blob字段存放优化记录，格式为json。可以看到，确实使用了优先级队列。

root@10.105.54.118 [wjq]>SELECT * FROM information_schema.optimizer_trace\G;

如果想要更加深入的学习MYSQL排序的原理可参考：MySQL排序内部原理探秘

如果您觉得本站对你有帮助，那么可以收藏和推荐本站，帮助本站更好地发展，在此谢过各位网友的支持。
转载请注明原文链接：MYSQL中使用order by…limit时候遇到的坑

一	二	三	四	五	六	日
« 4月
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

问题复现

解决方案一

解决方案二

深入学习

Hi，您需要填写昵称和邮箱！