有一个金融流水表，还没有分库分表。当前数据量为9555695，分页查询使用了limit，优化前的查询耗时16s938ms（执行：16s831ms，fetching：107ms），按照如下方法调整SQL后，耗时347毫秒（执行：163毫秒，获取：184毫秒）；操作：将查询条件放在子查询中，子查询只检查主键ID，然后使用子查询中确定的主键关联查询其他属性字段；原理：减少返表操作；--优化前SQLSELECT各个字段FROM`table_name`WHERE各种条件LIMIT0,10；--优化SQLSELECT各种字段FROM`table_name`main_taleRIGHTJOIN(SELECT子查询只检查主键FROM`table_name`WHERE各种条件LIMIT0,10;)temp_tableONtemp_table.Primarykey=main_table.Primarykey1、前言首先说明MySQL的版本：mysql>selectversion();+------------+|version()|+----------+|5.7.17|+------------+1rowinset(0.00sec)表结构：mysql>desctest;+--------+--------------------+-----+-----+---------+----------------+|字段|类型|空|键|默认|额外|+--------+--------------------+-----+-----+--------+----------------+|id|bigint(20)unsigned|NO|PRI|NULL|auto_increment||val|int(10)unsigned|NO|MUL|0|||source|int(10)unsigned|NO||0||+------+---------------------+------+-----+--------+----------------+3rowsinset(0.00sec)id为自增主键，val为非唯一索引，填入大量数据，一共500万条:mysql>selectcount(*)fromtest;+-----------+|count(*)|+--------+|5242882|+-----------+1rowinset(4.25sec)我们知道，当limitoffsetrows中的offset很大时，会出现效率问题：mysql>select*fromtestwhereval=4limit300000,5;+--------+-----+-------+|id|val|source|+--------+-----+--------+|3327622|4|4||3327632|4|4||3327642|4|4||3327652|4|4||3327662|4|4|+--------+-----+--------+5rowsinset（15.98秒)为了达到同样的目的，我们一般改写如下语句：mysql>select*fromtesttainnerjoin(selectidfromtestwhereval=4limit300000,5)bona.id=b.id;+--------+-----+--------+--------+|id|val|source|id|+--------+-----+--------+--------+|3327622|4|4|3327622||3327632|4|4|3327632||3327642|4|4|3327642||3327652|4|4|3327652||3327662|4|4|3327662|+--------+-----+--------+--------+5rowsinset(0.38sec)时间差很明显。为什么会出现上面的结果？再看select*fromtestwhereval=4limit300000,5;的查询过程：查询索引叶子节点数据。根据叶子节点上的主键值，检索出在聚类索引上查询所需的所有字段值。类似下图：如上，需要查询索引节点300005次，聚集索引的数据查询300005次，最后过滤掉前300000条结果，取出后5条。MySQL在查询聚簇索引数据时消耗了大量的随机I/O，30万次随机I/O查询的数据不会出现在结果集中。肯定有人会问：既然一开始就用到索引，那为什么不先沿着索引叶子节点查询到最后需要的5个节点，然后再去聚簇索引中查询实际数据。这样只需要5个随机I/O，和下图的过程类似。其实我也想问这个问题。确认我们来做一下来确认上面的推论：为了确认select*fromtestwhereval=4limit300000,5是在300005个聚簇索引上扫描300005个索引节点和数据节点，我们需要知道MySQL有没有办法统计一条sql中通过索引节点查询数据节点的次数。我先尝试了Handler_read_*系列，可惜没有一个变量能满足条件。我只能间接证实：InnoDB中有缓冲池。它包含最近访问的数据页，包括数据页和索引页。所以我们需要运行两个SQL来比较缓冲池中数据页的数量。预测结果是运行select*fromtestainnerjoin(selectidfromtestwhereval=4limit300000,5);之后，缓冲池中的数据页数远小于select*fromtestwhereval=4limit300000,5;对应的数字，因为前面的sql只访问数据页5次，后面的sql访问数据页300005次。select*fromtestwhereval=4limit300000,5mysql>selectindex_name,count(*)frominformation_schema.INNODB_BUFFER_PAGEwhereINDEX_NAMEin('val','primary')andTABLE_NAMlike'%test%'groupbyindex_name;Emptyset(0.04sec)可以看出，当前buffer池中没有test表的数据页。mysql>select*fromtestwhereval=4limit300000,5;+--------+-----+--------+|id|val|source|+---------+-----+--------+|3327622|4|4||3327632|4|4||3327642|4|4||3327652|4|4||3327662|4|4|+--------+-----+--------+5rowsinset(26.19sec)mysql>selectindex_name,count(*)frominformation_schema.INNODB_BUFFER_PAGEwhereINDEX_NAMEin('val','primary')andTABLE_NAMlike'%test%'groupbyindex_name;+------------+---------+|index_name|count(*)|+-------------+----------+|PRIMARY|4098||val|208|+------------+----------+2rowsinset(0.04sec)可以看出此时缓冲池中test表有4098个数据页和208个索引页。select*fromtestainnerjoin(selectidfromtestwhereval=4limit300000,5);为了防止上次测试的影响，我们需要清空缓冲池，重启mysql。mysqladminshutdown/usr/local/bin/mysqld_safe&mysql>selectindex_name,count(*)frominformation_schema.INNODB_BUFFER_PAGEwhereINDEX_NAMEin('val','primary')andTABLE_NAMElike'%test%'groupbyindex_name;Emptyset(0.03sec)runsql:mysql>selecttainer*jofrom(selectidfromtestwhereval=4limit300000,5)bona.id=b.id;+--------+-----+-------+--------+|id|val|source|id|+--------+-----+--------+--------+|3327622|4|4|3327622||3327632|4|4|3327632||3327642|4|4|3327642||3327652|4|4|3327652||3327662|4|4|3327662|+--------+-----+--------+--------+5rowsinset(0.09sec)mysql>selectindex_name,count(*)frominformation_schema.INNODB_BUFFER_PAGEwhereINDEX_NAMEin('val','primary')andTABLE_NAMlike'%test%'groupbyindex_name;+------------+---------+|index_name|count(*)|+-------------+------------+|PRIMARY|5||val|390|+------------+---------+2rowsinset(0.03sec)我们可以很明显的看出两者的区别：第一个sql加载了4098个数据页到bufferpool，而第二个sql只加载了5个数据页到bufferpool。符合我们的预测。也证实了第一个sql慢的原因：读取了大量无用的数据行（300000），最后还是丢弃了。而这样会造成一个问题：加载很多热点低的数据页到缓冲池中，会造成缓冲池的污染，占用缓冲池的空间。遇到的问题为了保证每次重启都清空bufferpool，我们需要关闭innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup。这两个选项可以控制数据库关闭时缓冲池中数据的转储和数据库打开时将备份缓冲区加载到磁盘上。池数据。
