关于sql:一次-SQL-查询优化原理分析900W-数据从-17s-到-300ms

有一张财务流水表，未分库分表，目前的数据量为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