相同执行计划，为何有执行快慢的差别

前言

今天遇到一个很神奇的相同行快现象，在数据库中，执行相同的计划执行计划，执行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 | | 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)

mysql>

再看执行慢的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