- 当前位置：首页 >数据库 >相同执行计划，为何有执行快慢的差别
相同执行计划，为何有执行快慢的差别
发布时间：2025-11-05 02:43:00 来源：创站工坊 作者：应用开发
-
前言
今天遇到一个很神奇的相同行快现象，在数据库中，执行相同的计划执行计划，执行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);1.2.3.4.最终我测试表的数据如下：
复制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)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.先看执行快的b2b供应网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)
mysql>1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.再看执行慢的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)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.对比两个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
源码库亿华云企商汇IT资讯网益华科技服务器租用香港云服务器源码下载IT技术网益华IT技术论坛益强编程舍汇智坊码上建站益强科技益华科技全栈开发亿华科技亿华智慧云益强智未来益强智囊团亿华云计算益强科技创站工坊亿华灵动益强资讯优选极客编程思维库多维IT资讯云站无忧益强数据堂益华科技益强IT技术网IT资讯网码力社编程之道智能时代科技前瞻益强前沿资讯云智核亿华互联运维纵横技术快报
- Copyright © 2025 Powered by 相同执行计划，为何有执行快慢的差别,创站工坊 滇ICP备2023006006号-44sitemap