有一张财务流水表，未分库分表，目前的数据量为 9555695，分页查问应用到了 limit，优化之前的查问耗时 16 s 938 ms (execution: 16 s 831 ms, fetching: 107 ms)，依照下文的形式调整 SQL 后，耗时 347 ms (execution: 163 ms, fetching: 184 ms)；
操作： 查问条件放到子查问中，子查问只查主键 ID，而后应用子查问中确定的主键关联查问其余的属性字段；
原理： 缩小回表操作；
-- 优化前 SQL
SELECT 各种字段
FROM`table_name`
WHERE 各种条件
LIMIT0,10; -- 优化后 SQL
SELECT 各种字段
FROM`table_name` main_tale
RIGHTJOIN
(
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