要想通过面试，MySQL的Limit子句底层原理你不可不知

柔情只为你懂 2022-09-15 12:54 173阅读 0赞

### 文章目录 ###

*  1.老样子，建个表
 *  2.从sql执行计划看Limit的影响
 *  3. 从server层和存储引擎层分析Limit执行过程

# 1.老样子，建个表 #

还是这张表，表里我创建了近`10W`条数据

CREATE TABLE demo_info(
        id INT NOT NULL auto_increment,
        key1 VARCHAR(100),
        key2 INT,
        key3 VARCHAR(100),
        key_part1 VARCHAR(100),
        key_part2 VARCHAR(100),
        key_part3 VARCHAR(100),
        common_field VARCHAR(100),
        PRIMARY KEY (id),
        KEY idx_key1 (key1),
        UNIQUE KEY uk_key2 (key2),
        KEY  idx_key3 (key3),
        KEY idx_key_part(key_part1, key_part2, key_part3)
    )ENGINE = INNODB CHARSET=utf8mb4;

`id`列是主键，`key1`列是二级索引列。

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

# 2.从sql执行计划看Limit的影响 #

分析一下`sql`执行计划

explain select * from demo_info order by key1 limit 1;

![a9bea7c42cf44b3997220baeb002885d.png][]

在二级索引`idx_key1`中，`key1`列是有序的，查找按`key1`列排序的第`1`条记录，`MySQL`只需要从`idx_key1`中获取到第一条二级索引记录，然后直接回表取得完整的记录即可，这个很容易理解。

如果我们把上边语句的`limit 1`换成`limit 10000, 1`，则却需要进行全表扫描，并进行`filesort`，执行计划如下：

explain select * from demo_info order by key1 limit 10000, 1;

![aa36fffb24a94f4282e10c2990d33a42.png][]

有的同学就很不理解了：`limit 10000, 1`也可以使用二级索引`idx_key1`呀，我们可以先扫描到第`10001`条二级索引记录，对第`10001`条二级索引记录进行回表操作就好了啊。

由于`MySQL`实现缺陷，不会出现上述的理想情况，它只会全表扫描+`filesort`，下边我们分析一下。

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

# 3. 从server层和存储引擎层分析Limit执行过程 #

`MySQL`其实是分为`server`层和存储引擎层的：

*  `server`层负责处理一些通用的事情，诸如连接管理、`SQL`语法解析、分析执行计划之类的东西
 *  存储引擎层负责具体的数据存储，诸如数据是存储到文件上还是内存里，具体的存储格式是什么样的之类的。我们现在基本都使用`InnoDB`存储引擎，其他存储引擎使用的非常少了，所以我们也就不讨论其他存储引擎了。

`MySQL`中一条`SQL`语句的执行是通过`server`层和存储引擎层的多次交互才能得到最终结果的。先不用`Limit`子句举一个简单例子分析：

SELECT * FROM demo_info WHERE key1 > 'a' AND key1 < 'b' AND common_field != 'a';

`server`层会分析到上述语句可以使用下边两种方案执行：

*  方案一：使用全表扫描
 *  方案二：使用二级索引`idx_key1`，此时需要扫描`key1`列值在`('a', 'b')`之间的全部二级索引记录，并且每条二级索引记录都需要进行回表操作。

`server`层会分析上述两个方案哪个成本更低，然后选取成本更低的那个方案作为执行计划。然后就调用存储引擎提供的接口来真正的执行查询了。

这里假设采用方案二，也就是使用二级索引`idx_key1`执行上述查询。那么`server`层和存储引擎层的执行过程如下：

`server`层：“去查查`idx_key1`二级索引的`('a', 'b')`区间的第一条记录，然后把回表后把完整的记录返给我”

`InnoDB`层：`InnoDB`就通过`idx_key1`二级索引对应的`B+`树，快速定位到扫描区间`('a','b')`的第一条二级索引记录，然后进行回表，得到完整的聚集索引记录返回给`server`层。 `server`层收到完整的聚集索引记录后，继续判断`common_field!='a'`条件是否成立，如果不成立则舍弃该记录，否则将该记录发送到客户端。然后对存储引擎说：“请把下一条记录给我”

> 注意：
> 
>   此处将记录发送给客户端其实是发送到本地的网络缓冲区，缓冲区大小由`net_buffer_length`控制，默认是`16KB`大小。等缓冲区满了才真正发送网络包到客户端。

`InnoDB`层：`InnoDB`找到`idx_key1`的`('a', 'b')`区间的下一条二级索引记录，然后进行回表操作，将得到的完整的聚集索引记录返回给`server`层。

> 注意：
> 
>   不论是聚集索引记录还是二级索引记录，都包含一个称作`next_record`的属性，各个记录根据`next_record`连成了一个链表，并且链表中的记录是按照键值排序的（对于聚集索引来说，键值指的是主键的值，对于二级索引记录来说，键值指的是二级索引列的值）。

`server`层收到完整的聚集索引记录后，继续判断`common_field!='a'`条件是否成立，如果不成立则舍弃该记录，否则将该记录发送到客户端。然后对存储引擎说：“请把下一条记录给我哈”

… 然后就不停的重复上述过程。

直到`InnoDB`发现根据二级索引记录的`next_record`获取到的下一条二级索引记录不在`('a', 'b')`区间中，就跟`server`层说：“`('a', 'b')`区间没有下一条记录了”

`server`层收到`InnoDB`说的没有下一条记录的消息，就结束查询。

现在大家就知道了`server`层和存储引擎层的基本交互过程了。

**那`limit`在哪里起作用呢？**

**`MySQL`是在`server`层准备向客户端发送记录的时候才会去处理`limit`子句中的内容。** 举个例子：

select * from demo_info order by key1 limit 10000, 1;

如果使用`idx_key1`执行上述查询，那么`MySQL`会这样处理：

*  `server`层向`InnoDB`要第`1`条记录，**`InnoDB`从`idx_key1`中获取到第一条二级索引记录，然后进行回表操作得到完整的聚集索引记录，然后返回给`server`层。`server`层准备将其发送给客户端，此时发现还有个`limit 10000, 1`的要求，意味着符合条件的记录中的第`10001`条才可以真正发送给客户端**，所以在这里先做个统计，我们假设`server`层维护了一个称作`limit_count`的变量用于统计已经跳过了多少条记录，此时就应该将`limit_count`设置为`1`。
 *  `server`层再向`InnoDB`要下一条记录，`InnoDB`再根据二级索引记录的`next_record`属性找到下一条二级索引记录，再次进行回表得到完整的聚集索引记录返回给`server`层。`server`层在将其发送给客户端的时候发现`limit_count`才是`1`，所以就放弃发送到客户端的操作，将`limit_count`加`1`，此时`limit_count`变为了`2`。
 *  … 重复上述操作
 *  直到`limit_count`等于`10000`的时候，`server`层才会真正的将`InnoDB`返回的完整聚集索引记录发送给客户端。

从上述过程中我们可以看到，**`MySQL`中是在实际向客户端发送记录前才会去判断`limit`子句是否符合要求，所以如果使用二级索引执行上述查询的话，意味着要进行`10001`次回表操作。**`server`层在进行执行计划分析的时候会觉得执行这么多次回表的**成本**太大了，还不如直接全表扫描+`filesort`快呢，全表扫描+`filesort`就是把聚集索引中的记录都依次与给定的搜索条件进行比较，把符合搜索条件的记录再进行排序，`MySQL`认为这样操作的成本比多次回表成本低，所以就选择了后者执行查询。

> `MySQL`是根据成本来选择对应索引查询的，如果你不知道成本怎么计算，可以看我前一篇[MySQL查询为什么选择使用这个索引？——基于MySQL8.0.22索引成本计算][MySQL_MySQL8.0.22]

> 注意：  
>   有一个点很容易混淆，走`PRIMARY`索引和全表扫描有什么区别呢？他们其实**都是在聚集索引上操作**的（聚集索引`B+`树的叶子结点是根据主键排好序的完整的用户记录，包含表里的所有字段），区别就在于  
>   
>   全表扫描将聚集索引`B+`树的叶子结点依次顺序扫描并判断条件，在以下几种情况会走全表扫描：
> 
>  *  `select * from demo_info`这种无条件的查询语句
>  *  `select * from demo_info where common_field != 'a'`这种条件字段`common_field`没有建索引的情况
>  *  `select * from demo_info order by key1 limit 10000, 1`条件字段`key1`建了索引但是`MySQL`认为走二级索引的成本比全表扫描成本高的情况。  
>       
>     
> 
>   `PRIMARY`索引是利用二分思想将聚集索引`B+`树到指定范围区间进行扫描，比如`select * from demo_info where id in (1, 2)`这种条件字段是主键`id`，可以很好的利用`PRIMARY`索引进行二分的快速查询。

怎么解决这个问题？

由于`MySQL`实现`limit`子句的局限性，在处理诸如`limit 10000, 1`这样的语句时就无法通过使用二级索引来加快查询速度了么？其实也不是，只要把上述语句改写成：

select * from demo_info d, 
    (select id from demo_info order by key1 limit 10000, 1) t 
    WHERE d.id = t.id;
    -- 或者这么写
    select * from demo_info d
    join 
    (select id from demo_info order by key1 limit 10000, 1) t
    on d.id = t.id

这样，`select id from demo_info order by key1 limit 10000, 1`作为一个子查询单独存在，由于该子查询的查询列表只有一个`id`列，`MySQL`可以通过仅扫描二级索引`idx_key1`的叶子结点不用回表，然后再根据子查询中获得到的主键值去表`demo_info`中进行查找。这样就省去了前`10000`条记录的回表操作，从而大大提升了查询效率！

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

欢迎一键三连~  
  
有问题请留言，大家一起探讨学习  
  
\----------------------Talk is cheap, show me the code-----------------------

[a9bea7c42cf44b3997220baeb002885d.png]: /images/20220828/2fcd205d619a480f8b8e32c001ecd384.png
[aa36fffb24a94f4282e10c2990d33a42.png]: /images/20220828/93dc3e3410ea4576b8a7e93f88e88245.png
[MySQL_MySQL8.0.22]: https://blog.csdn.net/qq_34115899/article/details/120217907