一次MySQL Limit执行过程探究

小明的程序员爸爸

已于 2022-11-29 19:29:53 修改

阅读量783

点赞数 3

分类专栏： MySQL 文章标签： mysql 数据库

于 2022-11-29 19:16:21 首次发布

本文链接：https://blog.csdn.net/weixin_37585619/article/details/128098275

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

故事还得从下面的图说起：

what? 两条sql执行结果的id列居然不一致。。。。。。

一、LIMIT 处理过程

为了故事的顺利发展，我们得先创建一张表：

CREATE TABLE `t_null_index` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `key1` char(1) DEFAULT NULL,
  `common_field` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_key1` (`key1`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3

表 t_null_index 包含3个列，id列是主键，key1列是二级索引列。表中包含9999条数据。

前戏结束🔚

=========================================================================

正片开始🎬

mysql> select * from t_null_index order by key1 limit 1;
+-------+------+----------------------------------+
| id    | key1 | common_field                     |
+-------+------+----------------------------------+
| 10019 | a    | a9ecd8f845cd4e6791e99af406e075c1 |
+-------+------+----------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from t_null_index order by key1 limit 1;
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-------+
| id | select_type | table        | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t_null_index | NULL       | index | NULL          | idx_key1 | 4       | NULL |    1 |   100.00 | NULL  |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

当我们执行上面的这条sql，是使用了 idx_key1 二级索引，这个好理解，因为在二级索引idx_key1中，key1列是有序的。而查询是要取按照key1列排序的第1条记录，那MySQL只需要从idx_key1中获取到第一条二级索引记录，然后直接回表得到完整聚簇索引的记录返回客户端即可。

但是如果我们把上边语句的 limit 1 换成 limit 5000, 1，效果会如何？

mysql> select * from t_null_index order by key1 limit 5000, 1;
+-------+------+----------------------------------+
| id    | key1 | common_field                     |
+-------+------+----------------------------------+
| 10125 | e    | e90499ca17b44727ab44a08c1cf609e8 |
+-------+------+----------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from t_null_index order by key1 limit 5000, 1;
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t_null_index | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9847 |   100.00 | Using filesort |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.01 sec)

当 limit 1 换成 limit 5000, 1 后，我们发现没有使用 idx_key1 二级索引，反而使用了全表扫描，并且进行 Using filesort。

开始我很不理解，limit 5000, 1 也可以使用二级索引 idx_key1啊，我们可以先扫描到第5001条二级索引记录，对5001条二级索引记录通过主键id回表取得完成聚簇索引记录不就好了吗？这样的代价也比全表扫描+filesort牛批啊。

Limit具体是怎么搞？

我们知道，MySQL 内部其实是分为 server层和存储引擎层，具体 server层和存储引擎层具体的交互这里就不说了。

对于limit的操作，MySQL是在server层准备向客户端发送记录的时候才会去处理limit子句中的内容。
select * from t_null_index order by key1 limit 5000, 1;
如果使用 idx_key1 索引执行上述查询，那么MySQL会这样处理：

（1）server层向InnoDB要第1条记录，InnoDB从idx_key1中获取到第1条二级索引记录，然后进行回表操作得到完整的聚簇索引记录，然后返回给server层。server层准备将其发送给客户端，此时发现还有个limit 5000, 1的要求，意味着符合条件的记录中的第5001条才可以返回给客户端，则不能将记录返回给客户端，同时会先记录下当前是第1条。

（2）server层再向InnoDB要下一条记录，InnoDB再根据二级索引记录的next_record属性找到下一条二级索引记录，再次进行回表得到完整的聚簇索引记录返回给server层。server层再将其发送给客户端的时候发现当前记录仍然不是5001条，所以就放弃了将记录发送给客户端，同时将记录数+1。

（3）。。。重复上述操作

（4）直到server层发现InnoDB返回的聚簇索引记录是5001条的时候，server层才会将InnoDB返回的完整聚簇索引记录发送给客户端。

从上述过程中我们可以看出，由于MySQL中是server层实际向客户端发送记录前才会判断limit子句是否符合要求，所以如果使用二级索引执行上述查询的话，意味着需要进行5001次回表操作。server层在执行执行计划分析的时候会觉得执行这么多次回表的成本太大了，还不如直接全表扫描+filesort 快呢，所以就选择了全表扫描+filesort 执行查询。

二、开始的图

说着说着，差点忘记了故事的前奏的图了😂

奇怪了？为什么都是 limit 5000,1，而两条sql执行结果的id列居然不一致，我们来看一下两条sql的执行计划：

mysql> explain select id from t_null_index limit 5000, 1;
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_null_index | NULL       | index | NULL          | idx_key1 | 4       | NULL | 9847 |   100.00 | Using index |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_null_index limit 5000, 1;
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t_null_index | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9847 |   100.00 | NULL  |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

通过执行计划，我们可以看出 select id from t_null_index limit 5000, 1; 这条sql执行过程采用了idx_key1，我们上面说到 limit 5000, 1 这个条件意味着会进行5001次回表操作，为什么这里又走了 idx_key1 索引呢？

其实，由于 select id 查询的查询列表只有一个 id 列，而 idx_key1 索引的叶子节点包含了索引列key1+主键id 的信息，故MySQL可以通过仅扫描二级索引idx_key1，然后无需回表操作直接就可以获取到想要的id列并且返回server层，server层再判断是否满足第5001条记录，如果不满足，再向InnoDB要下一条记录，直到满足为止。这样就省去了5001条记录的回表操作，从而大大提升了查询效率。

那到底为啥两条sql执行结果的id列值不一样？

我们来画一画 idx_key1索引的示意图，如图所示：

通过图上，我们可以看出 idx_key1 索引B+树的叶子节点，根据key1值由左向右升序排列，当key1列相同的节点，则按照id升序由左向右排序。

mysql> explain select id from t_null_index limit 5000, 1;
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_null_index | NULL       | index | NULL          | idx_key1 | 4       | NULL | 9847 |   100.00 | Using index |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

对于上述SQL，由于扫描二级索引 idx_key1，其实结果集是按照 key1 和 id 这两个键进行排序的，可以通过 select * from t_null_index order by key1, id limit 5000, 1; 来验证结果的id列是否和上面图中的SQL结果一致。而对于 select * from t_null_index limit 5000, 1; 该SQL由于走全表扫描并且默认按照主键id升序排序，两条SQL执行的排序规则不一致，所以就会导致两条结果的id列值不一致。

通过上图，我们可以看出，扫描 idx_key1 索引列的SQL 和显示 order by key1,id 的SQL的执行结果id列值是相同的。

那如果显示对 select * from t_null_index order by key1 limit 5000, 1; 结果会如何？

通过执行结果，我们可以看出扫描 idx_key1 索引列的SQL 和显示 order by key1 的SQL的执行结果id列值还是不相同的。

根据前面我们的分析，我们知道 select id from t_null_index limit 5000, 1; 会通过扫描二级索引 idx_key1 来获得结果集，并且结果集是按照 key1 和 id 这两个键进行排序的。而对于 select * from t_null_index order by key1 limit 5000, 1; 这条SQL执行会直接全表扫描后再在引擎层根据key1进行文件堆排序。这种排序的结果集存在根据key1升序的情况下，相同的key1，id列可能是乱序，所以就会出现图中两个值不相等的情况。

附：select * from t_null_index order by key1 limit 5000, 1; 执行计划

mysql> explain select * from t_null_index order by key1 limit 5000, 1;
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t_null_index | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9847 |   100.00 | Using filesort |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

附：select * from t_null_index order by key1 limit 4990, 20; 出现乱序的情况