900w条数据的SQL，请优化一下

有一张财务流水表，未分库分表，目前的数据量为 9555695，分页查询使用到了 limit，优化之前的查询耗时 16 s 938 ms (execution: 16 s 831 ms, fetching: 107 ms)，按照下文的方式调整 SQL 后，耗时 347 ms (execution: 163 ms, fetching: 184 ms)；

操作：查询条件放到子查询中，子查询只查主键 ID，然后使用子查询中确定的主键关联查询其他的属性字段；

原理：减少回表操作；

一，前言

首先说明一下 MySQL 的版本：

表结构：

id 为自增主键，val 为非唯一索引。

灌入大量数据，共 500 万：

我们知道，当 limit offset rows 中的 offset 很大时，会出现效率问题：

时间相差很明显。

为什么会出现上面的结果？我们看一下

的查询过程：

查询到索引叶子节点数据。

根据叶子节点上的主键值去聚簇索引上查询需要的全部字段值。

类似于下面这张图：

像上面这样，需要查询 300005 次索引节点，查询 300005 次聚簇索引的数据，最后再将结果过滤掉前 300000 条，取出最后 5 条。MySQL 耗费了大量随机 I/O 在查询聚簇索引的数据上，而有 300000 次随机 I/O 查询到的数据是不会出现在结果集当中的。

肯定会有人问：既然一开始是利用索引的，为什么不先沿着索引叶子节点查询到最后需要的 5 个节点，然后再去聚簇索引中查询实际数据。这样只需要 5 次随机 I/O，类似于下面图片的过程：

其实我也想问这个问题。

证实

下面我们实际操作一下来证实上述的推论：

为了证实

是扫描 300005 个索引节点和 300005 个聚簇索引上的数据节点，我们需要知道 MySQL 有没有办法统计在一个 SQL 中通过索引节点查询数据节点的次数。我先试了 Handler_read_*系列，很遗憾没有一个变量能满足条件。

我只能通过间接的方式来证实：

InnoDB 中有 buffer pool。里面存有最近访问过的数据页，包括数据页和索引页。所以我们需要运行两个 SQL，来比较 buffer pool 中的数据页的数量。预测结果是运行

之后，buffer pool 中的数据页的数量远远少于

对应的数量，因为前一个 SQL 只访问 5 次数据页，而后一个 SQL 访问 300005 次数据页。

可以看出，目前 buffer pool 中没有关于 test 表的数据页。

可以看出，此时 buffer pool 中关于 test 表有 4098 个数据页，208 个索引页。

为了防止上次试验的影响，我们需要清空 buffer pool，重启 MySQL。

运行 SQL：

我们可以看明显的看出两者的差别：第一个 SQL 加载了 4098 个数据页到 buffer pool，而第二个 SQL 只加载了 5 个数据页到 buffer pool。符合我们的预测。也证实了为什么第一个 SQL 会慢：读取大量的无用数据行（300000），最后却抛弃掉。

而且这会造成一个问题：加载了很多热点不是很高的数据页到 buffer pool，会造成 buffer pool 的污染，占用 buffer pool 的空间。遇到的问题

为了在每次重启时确保清空 buffer pool，我们需要关闭innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup，这两个选项能够控制数据库关闭时 dump 出 buffer pool 中的数据和在数据库开启时载入在磁盘上备份 buffer pool 的数据。

hello，我是御风，很高兴在这里与你相遇。我是一名程序员，也是一名B站UP主。我乐于分享，勤于学习。希望我们共同成长。永远相信梦想的力量

往期经典