关于mysql:相同执行计划为何有执行快慢的差别

GreatSQL社区原创内容未经受权不得随便应用，转载请分割小编并注明起源。
GreatSQL是MySQL的国产分支版本，应用上与MySQL统一。

前言

明天遇到一个很神奇的景象，在数据库中，雷同的执行打算，执行SQL所须要的工夫相差很大，执行快的SQL霎时出后果，执行慢的SQL要几十秒才出后果，一度让我狐疑是数据库抽风了，前面才发现是见识有余，又进入了常识空白区。

场景复现

数据库版本应用的是8.0.23 MySQL Community Server - GPL

因为生产环境数据敏感，禁止随便折腾，我在本人的测试环境，通过如下步骤，结构了一批数据，勉强可能复现出雷同的场景来

应用sysbench结构一万张表，每张表10行记录即可。
create table test.test_col as select * from information_schema.columns;
create table test.test_tab as select * from information_schema.tables;
create table test.test_tc as select * from information_schema.table_constraints;
执行10次 insert into test.test_tab select * from test.test_tab;
创立必要的索引

alter table test_col add key(table_schema, table_name);alter table test_col add key(column_name);alter table test_tab add key(table_schema, table_name);alter table test_tc add key(table_name);

最终我测试表的数据如下

mysql> select count(1) from test_col;+----------+| count(1) |+----------+|   1395616|+----------+1 row in set (3.29 sec)mysql> select count(1) from test_tab;+----------+| count(1) |+----------+|    10338 |+----------+1 row in set (0.12 sec)mysql> select count(1) from test_tc;+----------+| count(1) |+----------+|    10143 |+----------+1 row in set (0.06 sec)

先看执行快的SQL和它的执行打算

mysql> select count(1) from (select t1.TABLE_CATALOG, t2.TABLE_SCHEMA, t2.TABLE_NAME, t1.COLUMN_NAME, t1.DATA_TYPE, t3.CONSTRAINT_TYPE   from test_col t1   inner join test_tab t2 on t1.TABLE_SCHEMA = t2.TABLE_SCHEMA and t1.table_name = t2.table_name  inner join test_tc t3 on t2.TABLE_SCHEMA = t3.TABLE_SCHEMA and t2.TABLE_NAME = t3.TABLE_NAME limit 3 ) t;+----------+| count(1) |+----------+|        3 |+----------+1 row in set (0.00 sec)mysql> explain select count(1) from (select t1.TABLE_CATALOG, t2.TABLE_SCHEMA, t2.TABLE_NAME, t1.COLUMN_NAME, t1.DATA_TYPE, t3.CONSTRAINT_TYPE   from test_col t1   inner join test_tab t2 on t1.TABLE_SCHEMA = t2.TABLE_SCHEMA and t1.table_name = t2.table_name  inner join test_tc t3 on t2.TABLE_SCHEMA = t3.TABLE_SCHEMA and t2.TABLE_NAME = t3.TABLE_NAME limit 3 ) t;+----+-------------+------------+------------+-------+---------------+--------------+---------+-----------------------------------------+-------+----------+--------------------------+| id | select_type | table      | partitions | type  | possible_keys | key          | key_len | ref                                     | rows  | filtered | Extra                    |+----+-------------+------------+------------+-------+---------------+--------------+---------+-----------------------------------------+-------+----------+--------------------------+|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL         | NULL    | NULL                                    |     3 |   100.00 | NULL                     ||  2 | DERIVED     | t2         | NULL       | index | TABLE_SCHEMA  | TABLE_SCHEMA | 390     | NULL                                    | 10240 |   100.00 | Using where; Using index ||  2 | DERIVED     | t3         | NULL       | ref   | TABLE_NAME    | TABLE_NAME   | 195     | test.t2.TABLE_NAME                      |     1 |    10.00 | Using where              ||  2 | DERIVED     | t1         | NULL       | ref   | TABLE_SCHEMA  | TABLE_SCHEMA | 390     | test.t2.TABLE_SCHEMA,test.t2.TABLE_NAME |    61 |   100.00 | NULL                     |+----+-------------+------------+------------+-------+---------------+--------------+---------+-----------------------------------------+-------+----------+--------------------------+4 rows in set, 1 warning (0.00 sec)

再看执行慢的SQL和它的执行打算

mysql> select count(1) from (select t1.TABLE_CATALOG, t2.TABLE_SCHEMA, t2.TABLE_NAME, t1.COLUMN_NAME, t1.DATA_TYPE, t3.CONSTRAINT_TYPE   from test_col t1   inner join test_tab t2 on t1.TABLE_SCHEMA = t2.TABLE_SCHEMA and t1.table_name = t2.table_name  inner join test_tc t3 on t2.TABLE_SCHEMA = t3.TABLE_SCHEMA and t2.TABLE_NAME = t3.TABLE_NAME ) t;+----------+| count(1) |+----------+|   1333088|+----------+1 row in set (2.45 sec)mysql> explain select count(1) from (select t1.TABLE_CATALOG, t2.TABLE_SCHEMA, t2.TABLE_NAME, t1.COLUMN_NAME, t1.DATA_TYPE, t3.CONSTRAINT_TYPE   from test_col t1   inner join test_tab t2 on t1.TABLE_SCHEMA = t2.TABLE_SCHEMA and t1.table_name = t2.table_name  inner join test_tc t3 on t2.TABLE_SCHEMA = t3.TABLE_SCHEMA and t2.TABLE_NAME = t3.TABLE_NAME ) t;+----+-------------+-------+------------+-------+---------------+--------------+---------+-----------------------------------------+-------+----------+--------------------------+| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref                                     | rows  | filtered | Extra                    |+----+-------------+-------+------------+-------+---------------+--------------+---------+-----------------------------------------+-------+----------+--------------------------+|  1 | SIMPLE      | t2    | NULL       | index | TABLE_SCHEMA  | TABLE_SCHEMA | 390     | NULL                                    | 10240 |   100.00 | Using where; Using index ||  1 | SIMPLE      | t3    | NULL       | ref   | TABLE_NAME    | TABLE_NAME   | 195     | test.t2.TABLE_NAME                      |     1 |    10.00 | Using where              ||  1 | SIMPLE      | t1    | NULL       | ref   | TABLE_SCHEMA  | TABLE_SCHEMA | 390     | test.t2.TABLE_SCHEMA,test.t2.TABLE_NAME |    61 |   100.00 | Using index              |+----+-------------+-------+------------+-------+---------------+--------------+---------+-----------------------------------------+-------+----------+--------------------------+3 rows in set, 1 warning (0.00 sec)

比照两个SQL执行打算，抉择索引雷同，表关联程序雷同，快的执行0.00秒，慢的执行2.45秒，生产环境数据量更多，差别更大。两条SQL差异是执行快的SQL子查问中多了limit 3。

从上述执行打算，咱们能够看出，t2表为驱动表，先与t3做关联，失去后果后再与t1做关联，最初将后果集返回给客户端。

咱们都晓得，MySQL从server层返回数据给client，是一行一行返回的。也就是下层后果集与t1表每关联一行，有后果后，在没有排序的状况下，就是间接返回，并不会等所有行关联完后一起返回。

那么整个关联门路，是怎么样的呢，简化流程后应该是上面两种状况的一个

从t2取出所有数据，与t3表关联失去所有后果集后；再从t1中取一行关联，每失去一行后果，返回一次数据
从t2取一行数据，与t3表关联失去一行后果后，再从t1中取一行关联，每失去一行后果，返回一次数据

新的技巧

因为下面两个SQL执行打算、预估老本都雷同，无奈看出具体执行过程中差别点在什么中央导致执行性能差这么多.

在MySQL 8.0.18及之后，有一个新性能explain analyze，能够定量分析SQL执行过程中的耗时及理论数据拜访条数，拿到咱们的场景具体应用一下

mysql> explain analyze select count(1) from (select t1.TABLE_CATALOG, t2.TABLE_SCHEMA, t2.TABLE_NAME, t1.COLUMN_NAME, t1.DATA_TYPE, t3.CONSTRAINT_TYPE   from test_col t1   inner join test_tab t2 on t1.TABLE_SCHEMA = t2.TABLE_SCHEMA and t1.table_name = t2.table_name  inner join test_tc t3 on t2.TABLE_SCHEMA = t3.TABLE_SCHEMA and t2.TABLE_NAME = t3.TABLE_NAME limit 3 ) t \G*************************** 1. row ***************************EXPLAIN: -> Aggregate: count(1)  (actual time=0.348..0.349 rows=1 loops=1)    -> Table scan on t  (cost=2.84 rows=3) (actual time=0.003..0.004 rows=3 loops=1)        -> Materialize  (cost=75298.09 rows=3) (actual time=0.339..0.340 rows=3 loops=1)            -> Limit: 3 row(s)  (cost=75298.09 rows=3) (actual time=0.179..0.205 rows=3 loops=1)                -> Nested loop inner join  (cost=75298.09 rows=132366) (actual time=0.177..0.203 rows=3 loops=1)                    -> Nested loop inner join  (cost=4648.25 rows=1024) (actual time=0.130..0.130 rows=1 loops=1)                        -> Filter: ((t2.`TABLE_NAME` is not null) and (t2.TABLE_SCHEMA is not null))  (cost=1064.25 rows=10240) (actual time=0.065..0.065 rows=1 loops=1)                            -> Index scan on t2 using TABLE_SCHEMA  (cost=1064.25 rows=10240) (actual time=0.053..0.053 rows=1 loops=1)                        -> Filter: (t3.TABLE_SCHEMA = t2.TABLE_SCHEMA)  (cost=0.25 rows=0) (actual time=0.062..0.062 rows=1 loops=1)                            -> Index lookup on t3 using TABLE_NAME (TABLE_NAME=t2.`TABLE_NAME`)  (cost=0.25 rows=1) (actual time=0.059..0.059 rows=1 loops=1)                    -> Index lookup on t1 using TABLE_SCHEMA (TABLE_SCHEMA=t2.TABLE_SCHEMA, TABLE_NAME=t2.`TABLE_NAME`)  (cost=56.08 rows=129) (actual time=0.044..0.070 rows=3 loops=1)1 row in set (0.00 sec)mysql> explain analyze select count(1) from (select t1.TABLE_CATALOG, t2.TABLE_SCHEMA, t2.TABLE_NAME, t1.COLUMN_NAME, t1.DATA_TYPE, t3.CONSTRAINT_TYPE   from test_col t1   inner join test_tab t2 on t1.TABLE_SCHEMA = t2.TABLE_SCHEMA and t1.table_name = t2.table_name  inner join test_tc t3 on t2.TABLE_SCHEMA = t3.TABLE_SCHEMA and t2.TABLE_NAME = t3.TABLE_NAME ) t \G*************************** 1. row ***************************EXPLAIN: -> Aggregate: count(1)  (actual time=2130.310..2130.311 rows=1 loops=1)    -> Nested loop inner join  (cost=19704.44 rows=132366) (actual time=0.114..2006.259 rows=1333088 loops=1)        -> Nested loop inner join  (cost=4648.25 rows=1024) (actual time=0.094..108.093 rows=10143 loops=1)            -> Filter: ((t2.`TABLE_NAME` is not null) and (t2.TABLE_SCHEMA is not null))  (cost=1064.25 rows=10240) (actual time=0.051..17.021 rows=10338 loops=1)                -> Index scan on t2 using TABLE_SCHEMA  (cost=1064.25 rows=10240) (actual time=0.049..12.845 rows=10338 loops=1)            -> Filter: (t3.TABLE_SCHEMA = t2.TABLE_SCHEMA)  (cost=0.25 rows=0) (actual time=0.007..0.008 rows=1 loops=10338)                -> Index lookup on t3 using TABLE_NAME (TABLE_NAME=t2.`TABLE_NAME`)  (cost=0.25 rows=1) (actual time=0.007..0.008 rows=1 loops=10338)        -> Index lookup on t1 using TABLE_SCHEMA (TABLE_SCHEMA=t2.TABLE_SCHEMA, TABLE_NAME=t2.`TABLE_NAME`)  (cost=1.79 rows=129) (actual time=0.010..0.172 rows=131 loops=10143)1 row in set (2.13 sec)mysql>

从下面的剖析后果来看，在驱动表t2执行Index scan on t2 using TABLE_SCHEMA这一步的时候，就存在很大的差别了，执行快的SQL在这一步只扫描了一行记录，耗时0.053毫秒，而执行快的SQL在这一步扫描数量基本上和执行打算预计的统一，扫描了10338行记录，耗时12.845毫秒；驱动表扫描记录越多，那么和后续表关联的nested loop join次数也越多，导致两条SQL执行工夫差别微小。

加大limit的返回限度为5000，驱动表t2扫描的行数减少至99行，执行工夫减少至0.201毫秒

mysql> explain analyze select count(1) from (select t1.TABLE_CATALOG, t2.TABLE_SCHEMA, t2.TABLE_NAME, t1.COLUMN_NAME, t1.DATA_TYPE, t3.CONSTRAINT_TYPE   from test_col t1   inner join test_tab t2 on t1.TABLE_SCHEMA = t2.TABLE_SCHEMA and t1.table_name = t2.table_name  inner join test_tc t3 on t2.TABLE_SCHEMA = t3.TABLE_SCHEMA and t2.TABLE_NAME = t3.TABLE_NAME limit 5000) t \G*************************** 1. row ***************************EXPLAIN: -> Aggregate: count(1)  (actual time=33.395..33.396 rows=1 loops=1)    -> Table scan on t  (cost=565.00 rows=5000) (actual time=0.005..0.765 rows=5000 loops=1)        -> Materialize  (cost=75298.09 rows=5000) (actual time=31.863..33.046 rows=5000 loops=1)            -> Limit: 5000 row(s)  (cost=75298.09 rows=5000) (actual time=0.126..25.326 rows=5000 loops=1)                -> Nested loop inner join  (cost=75298.09 rows=132366) (actual time=0.124..24.757 rows=5000 loops=1)                    -> Nested loop inner join  (cost=4648.25 rows=1024) (actual time=0.095..0.834 rows=20 loops=1)                        -> Filter: ((t2.`TABLE_NAME` is not null) and (t2.TABLE_SCHEMA is not null))  (cost=1064.25 rows=10240) (actual time=0.046..0.201 rows=99 loops=1)                            -> Index scan on t2 using TABLE_SCHEMA  (cost=1064.25 rows=10240) (actual time=0.044..0.157 rows=99 loops=1)                        -> Filter: (t3.TABLE_SCHEMA = t2.TABLE_SCHEMA)  (cost=0.25 rows=0) (actual time=0.005..0.006 rows=0 loops=99)                            -> Index lookup on t3 using TABLE_NAME (TABLE_NAME=t2.`TABLE_NAME`)  (cost=0.25 rows=1) (actual time=0.005..0.006 rows=0 loops=99)                    -> Index lookup on t1 using TABLE_SCHEMA (TABLE_SCHEMA=t2.TABLE_SCHEMA, TABLE_NAME=t2.`TABLE_NAME`)  (cost=56.08 rows=129) (actual time=0.011..1.171 rows=250 loops=20)1 row in set (0.04 sec)mysql>

从下面的analyze后果，也能够看进去，在测试应用的SQL构造中，关联程序是办法2，也就是从t2取一行数据，与t3表关联失去一行后果后，再从t1中取一行关联，每失去一行后果，返回一次数据。

从官网文档中介绍，explain analyze是explain format=tree的补充，两者都是8.0呈现的新性能，这里简略介绍一下我集体了解的查看这种执行打算的程序，如果有不正确的中央，还请斧正：最先查看第一个缩进最多的行，没有雷同缩进时，再向上一个缩进查看，再查看雷同缩进的行(如果它有子缩进行，也是先查看缩进最多的行)，以如下SQL为例，它的执行打算查看程序为10->9->12->11->8->13->7->6->5->4->3

第一个缩进最多的行是第10行，执行打算判断以索引扫描的形式从t2表读取10240条记录，理论从t2表读取了99条记录，在读取这99条记录的操作过程中，读取到第1条记录耗时0.044毫秒，读取到第99条耗时0.157毫秒，因为它是第一个读取的表，也是查问的驱动表，只会读取一次数据
查看第9行，数据从存储引擎获取后，须要在server层过滤，打算是过滤10240条记录，实际上过滤了99条记录，过滤这99条记录的过程中，第1条记录执行实现耗时是0.046毫秒，第99条记录执行实现耗时是0.201毫秒，驱动表过滤操作也只进行一次
第11行与第9行缩进雷同，然而因为它有子缩进第12行，所以先执行第12行，以一般索引等值查找的形式扫描t3表，这里执行打算每个关联会返回一条记录，然而理论数据返回0条，是因为这个值是平均值，即t2表的99行记录在t3表中查问记录数除以99，取整后失去的值。
第12行，对从存储引擎层返回的数据，做进一步过滤，这里也循环99次
第8行，t2 表与t3表的关联，关联后返回记录20条，实现关联耗时为0.834毫秒
第13行，以一般索引等值查问，从t1表中获取数据,一共要实现20次循环查问，每次循环获取第一条记录的均匀工夫是0.011毫秒,每次循环获取最初一条记录的工夫是1.171毫秒，每次循环均匀获取250条记录。
第7行，与t1关联查问的办法和后果，一共返回5000条记录，返回第1条记录耗时0.124毫秒，返回第5000条记录耗时24.757毫秒
第6行，limit判断，耗时25.326毫秒
第5行，物化这5000行记录，物化实现耗时33.046毫秒
第4行，扫描物化表数据5000条记录，扫描耗时0.765毫秒
第3行，数据做聚合，返回count数量，耗时33.396毫秒，也是整个SQL执行的总耗时

explain analyze 将执行过程中的索引、连贯形式、过滤等信息嵌入了每个执行步骤，首次接触时，能够应用explain后果进行比照查看，以更容易接受和了解执行过程

总结

雷同的SQL执行打算，却有不同的数据获取过程，这个在以前的版本中，是很难剖析到的，explain\optimizer_trace\profile都不行，当初通过explain analyze可能轻易实现，通过这个工具，也加深了对多表join的一个执行过程的了解，是一个十分实用的工具。

须要留神点：

explain analyze过程中会理论执行具体SQL，但并不会SQL的执行后果，返回的后果集是具体执行步骤
目前只反对select语句，对于insert\ update \delete未反对，这点和explain有差异

参考链接

https://dev.mysql.com/doc/ref...

Enjoy GreatSQL :)

文章举荐：

乏味的SQL DIGEST

ulimits不失效导致数据库启动失败和相干设置阐明
MGR及GreatSQL资源汇总

GreatSQL MGR FAQ

在Linux下源码编译装置GreatSQL/MySQL

## 对于 GreatSQL

GreatSQL是由万里数据库保护的MySQL分支，专一于晋升MGR可靠性及性能，反对InnoDB并行查问个性，是实用于金融级利用的MySQL分支版本。

Gitee:
https://gitee.com/GreatSQL/GreatSQL

GitHub:
https://github.com/GreatSQL/GreatSQL

Bilibili：
https://space.bilibili.com/1363850082/favlist

微信（左）&QQ群（右）：

微信：扫码增加GreatSQL社区助手微信好友，发送验证信息“加群”退出GreatSQL/MGR交换微信群。

QQ：间接扫码退出GreatSQL/MGR交换QQ群。