type
status
date
slug
summary
tags
category
icon
password
分页查询是一个常见的功能，实现它也很简单，在 MySQL 中，使用
LIMIT 子句就行，比如下面这条查询语句：SELECT * FROM discuss_post ORDER BY create_time DESC LIMIT 10, 10; 。但是，当涉及到深度分页时，这种简单语句的查询效率就会急剧下降，比如对于 LIMIT 280000, 10 ，MySQL 会先对 280010 条记录都进行回表查询，再剔除前 280000 条，最后返回接下来的 10 条。网上也提供了很多优化的方法，比如延迟关联、子查询等，原理基本上都是相同的，先找出所需要的记录的主键，随后再去聚簇索引中获取这些记录的完整数据，从而减少回表次数。知道了优化的方法，也知道了优化的原理，本来到这里就可以结束了，但是，我在用
EXPLAIN 查看优化前后的两种查询语句的执行计划时，发现了一个比较有意思的地方，在执行计划的 rows 列，两种语句的数值相差并不大，也就是说，两者扫描的行数差不多，但是执行效率却非常大，这好像不是很合理。我尝试搜寻了一些资料，想看看这两种查询语句实际上扫描了多少记录，但还没有找到一个精确的方法，所以这篇文章并不严谨，里面还有部分自己的猜测，就当是一个记录吧。延迟关联优化前后的效率对比
先看一下我们的表结构：
这是一个文章表，主键是 id，还有一个根据创建时间 create_time 倒序排列的索引列。表中一共有 300158 条记录。
现在我们想要根据创建时间的倒序排列，获取第 280001～280010 条记录。我们分别执行优化前后的 SQL，看看它们查询时间的差异。
先看一下优化前的 SQL 及其查询时间：
再看一下优化后的 SQL 及其查询时间：
可以看到，两条语句的执行结果是相同的，但是优化前的查询用了 700 ms，而优化后的查询只用了 50 ms，效率相差了 10 倍有余，可以说优化效果是非常明显的。
EXPLAIN 中的一点疑问
为什么两者的执行效率会相差这么大呢？最常用的方法，就是用 EXPLAIN 语句看看它们的执行计划。
我们先看一看优化前的执行计划：
可以看到，优化前的语句使用了全表扫描（type=ALL），并且采取了额外的排序操作（Extra=Using filesort），而扫描的行数处在 LIMIT 所需的记录数和表的总记录数之间（rows=287615）。
下面看一看优化后的执行计划：
执行计划中的每一个 id 代表一个 SELECT 子句，id 大的子句先执行。
我们先看「id=2」的这一行，这一行是延迟关联中的 SELECT 子句的执行计划：
SELECT id FROM discuss_post ORDER BY create_time DESC LIMIT 280000, 10 。可以看到它使用了全索引扫描（type=index），扫描的是 discuss_post 表中的 idx_create_time 索引树，而且用到了覆盖索引（Extra=Using index），扫描的行数是 LIMIT 所需的记录数（rows=280010）。Extra 列中没有提到 Using filesort，所以这里不需要额外的排序操作。再看「id=1」的这两行，这两行对应的就是外围的 SELECT 语句，对表 d1（也就是 discuss_post）和表 <derived2>（就是前面 SELECT 子句的结果表）做 JOIN 连接后返回结果。可以看到，需要对表 <derived2> 进行全表扫描（type=ALL），同时，对于 <derived2> 中的每一条记录，使用表 d1 的主键索引进行唯一匹配（type=eq_ref，key=PRIMARY）。
对比两个执行计划，优化前的语句没有用到索引，而且进行了额外的排序操作，这可能是它效率低下的原因之一。另一个原因网上很多地方都提到，延迟关联会让回表的次数变少，但是观察它们的 rows 列，外围 SELECT 语句在做 JOIN 连接时，扫描 <derived2> 表中的记录数仍是 280010，和优化前的扫描行数相差不多，而 <derived2> 表是 SELECT 子句的查询结果，不应该只有 10 条记录吗？这就是让我感到疑惑的地方。
对 rows 列的精确度问题，MySQL 文档做过说明：
Therowscolumn indicates the number of rows MySQL believes it must examine to execute the query.ForInnoDBtables, this number is an estimate, and may not always be exact.
也就是说，对于 InnoDB 引擎，rows 列的数值是一个预估值，而不是一个精确值。那么有较为精确的方式吗？查了一些资料后，找到两种方法可以从侧面反映实际的扫描行数。
Server 层的扫描行数
根据 MySQL 文档，慢查询日志中的 Rows_examined 字段统计了类似于扫描行数的信息：
Rows_examined:The number of rows examined by the server layer (not counting any processing internal to storage engines).
需要注意的是，Rows_examined 只记录了 server 层处理的行数，并不包含存储引擎层。所以我们先看一下 server 层的不同。
先打开 MySQL 的慢查询日志（之后别忘了关闭它）：
随后，让我们看一看优化前的语句的慢查询日志：
「Rows_examined」的值为 580168，它刚好等于 300158 + 280010，也就是 discuss_post 的记录总数与 LIMIT 所需要的记录数之和。整体的执行流程这里先不赘述，后面结合 InnoDB 层的扫描行数再说一下我的猜测。
接下来，让我们看看优化后的语句的慢查询日志：
可以看到，「Rows_examined」的值大幅度下降到了 20，server 层需要处理的记录数非常少，说明大部分的操作在存储引擎层就已经完成，交给 server 层处理的记录数近似于我们需要的 10 条记录，这也符合我们的预期。但为什么不是 10 呢？我还没有找到相关的解释资料，以后再来补充吧。
根据上面慢查询日志的对比，我们可以很明显的看到，使用延迟关联优化后，server 层处理记录的负担变小了很多。但是慢查询日志只能查看 server 层的数据，那么在 InnoDB 引擎中，扫描的行数也会有较大的差异吗？
InnoDB 层的扫描行数
「Innodb_rows_read」会累计从 InnoDB 表中读取的行数：
Innodb_rows_read:The number of rows read from InnoDB tables.
所以，计算 SQL 执行前后 Innodb_rows_read 的差值就可以得知这个 SQL 语句在 InnoDB 中扫描的行数。
首先让我们看一下优化前的 SQL 语句：
通过计算，30753521 - 30173353 = 580168 = 300158 + 280010，和 Server 层一样。我没有找到这个字段的具体记录规则，但我猜测这和这条语句的执行计划有关：首先，通过全表扫描把 InnoDB 表中的所有记录都交给 server 层进行排序，这里可能只读取了主键 id 和排序字段 create_time，这一步让 InnoDB 层和 server 层都扫描了 300158 条记录，随后，server 层取出排序后的前 280010 条记录的 id，再次回表取出 SELECT 所需要的字段，这一步让 InnoDB 层和 server 层再次扫描了 280010 条记录。
现在让我们看一下优化后的 SQL 语句：
通过计算，31033541 - 30753521 = 280020 = 280010 + 10，我猜测整个执行流程是这样的：在 SELECT 子句中，扫描 create_time 索引树的前 280010 条记录，然后获取所需要的 10 条记录的 id ，并生成一张临时表。随后，将这张临时表和 discuss_post 做 JOIN 连接，这里扫描了临时表中的 10 条记录。不论如何，InnoDB 层的扫描行数基本和 LIMIT 所需的行数相同，相比于优化前下降的非常多。
总结
通过一个表格汇总一下上面的分析：
ㅤ | 延迟关联优化前 | 延迟关联优化后 |
执行计划 | 全表扫描，额外的排序操作 | 索引树扫描，不用额外排序 |
Server 层扫描行数 | 580168 | 20 |
InnoDB 层扫描行数 | 580168 | 280020 |
总结一下，为什么延迟关联能快这么多呢？首先，它利用了索引有序的性质，避免了额外排序。其次，直接扫描索引树的前 280010 条记录，拿到所需要的 10 条记录的 id，再去主键索引中获取所需要的字段，大幅度减少了回表次数。最后，经过优化后，不论是 server 层还是 InnoDB 层，扫描的行数都有显著的降低。
- Author:LINKSEE
- URL:https://www.linksee.top//article/1abf44a0-eb9d-80f2-b373-c6d14e9e4115
- Copyright:All articles in this blog, except for special statements, adopt BY-NC-SA agreement. Please indicate the source!
