有一张财务流水表，未分库分表，目前的数据量为9555695，分页查问应用到了limit，优化之前的查问耗时16 s 938 ms (execution: 16 s 831 ms, fetching: 107 ms)，依照下文的形式调整SQL后，耗时347 ms (execution: 163 ms, fetching: 184 ms)；
操作： 查问条件放到子查问中，子查问只查主键ID，而后应用子查问中确定的主键关联查问其余的属性字段；
原理： 缩小回表操作；
-- 优化前SQLSELECT 各种字段FROM`table_name`WHERE 各种条件LIMIT0,10; -- 优化后SQLSELECT 各种字段FROM`table_name` main_taleRIGHTJOIN(SELECT 子查问只查主键FROM`table_name`WHERE 各种条件LIMIT0,10;) temp_table ON temp_table.主键 = main_table.主键 找到的原理剖析：MySQL 用 limit 为什么会影响性能？
一，前言
首先阐明一下MySQL的版本：
mysql> selectversion();+-----------+| version() |+-----------+| 5.7.17 |+-----------+1 row in set (0.00 sec) 表构造：
mysql> desc test;+--------+---------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+---------------------+------+-----+---------+----------------+| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment || val | int(10) unsigned | NO | MUL | 0 | || source | int(10) unsigned | NO | | 0 | |+--------+---------------------+------+-----+---------+----------------+3 rows in set (0.00 sec) id为自增主键，val为非惟一索引。
灌入大量数据，共500万：
mysql> selectcount(*) fromtest;+----------+| count(*) |+----------+| 5242882 |+----------+1 row in set (4.25 sec) 咱们晓得，当limit offset rows中的offset很大时，会呈现效率问题：
mysql> select * fromtestwhere val=4limit300000,5;+---------+-----+--------+| id | val | source |+---------+-----+--------+| 3327622 | 4 | 4 || 3327632 | 4 | 4 || 3327642 | 4 | 4 || 3327652 | 4 | 4 || 3327662 | 4 | 4 |+---------+-----+--------+5 rows in set (15.98 sec) 为了达到雷同的目标，咱们个别会改写成如下语句：
mysql> select * fromtest a innerjoin (selectidfromtestwhere val=4limit300000,5) b on a.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 |+---------+-----+--------+---------+5 rows in set (0.38 sec) 工夫相差很显著。
为什么会呈现下面的后果？咱们看一下select * from test where val=4 limit 300000,5;的查问过程：
查问到索引叶子节点数据。依据叶子节点上的主键值去聚簇索引上查问须要的全副字段值。
相似于上面这张图：
像下面这样，须要查问300005次索引节点，查问300005次聚簇索引的数据，最初再将后果过滤掉前300000条，取出最初5条。MySQL消耗了大量随机I/O在查问聚簇索引的数据上，而有300000次随机I/O查问到的数据是不会呈现在后果集当中的。
必定会有人问：既然一开始是利用索引的，为什么不先沿着索引叶子节点查问到最初须要的5个节点，而后再去聚簇索引中查问理论数据。这样只须要5次随机I/O，相似于上面图片的过程：
其实我也想问这个问题。
证实
上面咱们实际操作一下来证实上述的推论：
为了证实select from test where val=4 limit 300000,5是扫描300005个索引节点和300005个聚簇索引上的数据节点，咱们须要晓得MySQL有没有方法统计在一个sql中通过索引节点查问数据节点的次数。我先试了Handler_read_系列，很遗憾没有一个变量能满足条件。
我只能通过间接的形式来证实：
InnoDB中有buffer pool。外面存有最近拜访过的数据页，包含数据页和索引页。所以咱们须要运行两个sql，来比拟buffer pool中的数据页的数量。预测后果是运行select from test a inner join (select id from test where val=4 limit 300000,5); 之后，buffer pool中的数据页的数量远远少于select from test where val=4 limit 300000,5;对应的数量，因为前一个sql只拜访5次数据页，而后一个sql拜访300005次数据页。
select * fromtestwhere val=4limit300000,5 mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('val','primary') and TABLE_NAME like'%test%'groupby index_name;Empty set (0.04 sec) 能够看出，目前buffer pool中没有对于test表的数据页。
mysql> select * fromtestwhere val=4limit300000,5;+---------+-----+--------+| id | val | source |+---------+-----+--------+|3327622 | 4 | 4 || 3327632 | 4 | 4 || 3327642 | 4 | 4 || 3327652 | 4 | 4 || 3327662 | 4 | 4 |+---------+-----+--------+5 rows in set (26.19 sec)mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('val','primary') and TABLE_NAME like'%test%'groupby index_name;+------------+----------+| index_name | count(*) |+------------+----------+| PRIMARY | 4098 || val | 208 |+------------+----------+2 rows in set (0.04 sec) 能够看出，此时buffer pool中对于test表有4098个数据页，208个索引页。
select * from test a inner join (select id from test where val=4 limit 300000,5) ; 为了避免上次试验的影响，咱们须要清空buffer pool，重启mysql。
mysqladmin shutdown/usr/local/bin/mysqld_safe & mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('val','primary') and TABLE_NAME like'%test%'groupby index_name;Empty set (0.03 sec) 运行sql：
mysql> select * fromtest a innerjoin (selectidfromtestwhere val=4limit300000,5) b on a.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 |+---------+-----+--------+---------+5 rows in set (0.09 sec)mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('val','primary') and TABLE_NAME like'%test%'groupby index_name;+------------+----------+| index_name | count(*) |+------------+----------+| PRIMARY | 5 || val | 390 |+------------+----------+2 rows in set (0.03 sec) 咱们能够看显著的看出两者的差异：第一个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的数据。
参考资料
1.https://explainextended.com/2...
2.https://dev.mysql.com/doc/ref...
起源：Muscleape
jianshu.com/p/0768ebc4e28d