有一个金融流表，不分库分表，当前数据量9555695，分页查询使用limit，优化前查询取16s938ms(execution:16s831ms,fetching:107ms)，按照如下方式调整SQL后，耗时347ms(execution:163ms,fetching:184ms)。操作查询条件放在子查询中，子查询只检查主键ID，然后使用子查询中确定的主键关联查询其他属性字段。原理是减少回表操作，使用延迟关联或者子查询来优化超多分页场景。--优化前SQLSELECTvariousfieldsFROM`table_name`WHEREvariousconditionsLIMIT0,10;--优化后SQLSELECTvariousfieldsFROM`table_name`main_taleRIGHTJOIN(SELECT子查询只检查主键FROM`table_name`WHEREvariousconditionsLIMIT0,10;)temp_tableONtemp_table。主键=main_table。主键发现原理分析：为什么MySQL使用limit会影响性能？前言先说明一下MySQL版本：mysql>selectversion();+------------+|版本()|+------------+|5.7.17|+------------+1rowinset(0.00sec)表结构：mysql>desctest;+--------+-----------------------+--------+-----+--------+----------------+|领域|类型|空|键|默认|额外|+--------+--------------------+------+-----+---------+----------------+|编号|bigint(20)无符号|否|优先级|空|自动递增||值|int(10)无符号|否|多个|0|||来源|--------------+------+-----+--------+--------------+3rowsinset(0.00sec)id是自增主键，val是非唯一索引。填写大量数据，一共500万：mysql>selectcount(*)fromtest;+--------+|计数(*)|+----------+|5242882|+--------+1rowinset(4.25sec)我们知道当limitoffsetrows中的offset很大时，会出现效率问题：mysql>select*fromtestwhereval=4极限300000,5;+--------+-----+------+|编号|值|来源|+--------+-----+--------+|3327622|4|4||3327632|4|4||3327642|4|4||+--------+-----+--------+5rowsinset(15.98sec)为了达到同样的目的，我们一般改写成如下语句:mysql>select*fromtestainnerjoin(selectidfromtestwhereval=4limit300000,5)bona.id=b.id;+--------+-----+--------+--------+|编号|值|来源|id|+--------+-----+---------+--------+|3327622|4|4|3327622||3327632|4|4|3327632||3327642|4|4|3327642|||4|4|3327662|+--------+-----+--------+--------+5行集合(0.38秒）时间差很明显。为什么会出现上面的结果？下面看一下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_NAMElike'%test%'groupbyindex_name;空集(0.04sec)可以看到缓冲池中目前没有关于test表的数据页。mysql>select*fromtestwhereval=4limit300000,5;+--------+-----+------+|编号|值|来源|+---------+-----+--------+|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_PAGE其中INDEX_NAME在('val','primary')andTABLE_NAMElike'%test%'groupbyindex_name;+------------+----------+|索引名称|计数(*)|+------------+---------+|初级|4098||值|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_PAGE其中INDEX_NAMEin('val','primary')andTABLE_NAMElike'%test%'groupbyindex_name;Emptyset(0.03秒)运行sql：mysql>select*fromtestainnerjoin(selectidfromtestwhereval=4limit300000,5)bona.id=b.id;+--------+-----+--------+--------+|编号|值|来源|id|+--------+-----+--------+--------+|3327622|4|4|3327622||3327632|4|4|3327632||3327642|4|4|3327642||3327652|4|4|3327652||3327662|4|4|3327662|+--------+-----+--------+--------+5组中的行（0.09秒）mysql>选择索引名称,count(*)frominformation_schema.INNODB_BUFFER_PAGEwhereINDEX_NAMEin('val','primary')andTABLE_NAMElike'%test%'groupbyindex_name;+------------+---------+|索引名称|计数(*)|+------------+---------+|初级|5||值|390|+------------+----------+2rowsinset(0.03sec)我们可以清楚地看到两者的区别：第一个sql加载了4098个数据页到缓冲池，而第二个sql只加载5个数据页到缓冲池，这符合我们的预测。也证实了第一个sql慢的原因：读取了大量无用的数据行（300000），最后还是丢弃了。而这样会造成一个问题：加载很多热点低的数据页到缓冲池中，会造成缓冲池的污染，占用缓冲池的空间。为了保证每次重启都清空缓冲池，我们需要关闭innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup。这两个选项可以控制数据库关闭时缓冲池中数据的转储和数据库打开时将备份缓冲池中的数据加载到磁盘上。
