乐趣区

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

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

前言

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

场景复现

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

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

  1. 应用 sysbench 结构一万张表,每张表 10 行记录即可。
  2. create table test.test_col as select * from information_schema.columns;
  3. create table test.test_tab as select * from information_schema.tables;
  4. create table test.test_tc as select * from information_schema.table_constraints;
  5. 执行 10 次 insert into test.test_tab select * from test.test_tab;
  6. 创立必要的索引
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 表每关联一行,有后果后,在没有排序的状况下,就是间接返回,并不会等所有行关联完后一起返回。

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

  1. 从 t2 取出所有数据,与 t3 表关联失去所有后果集后;再从 t1 中取一行关联,每失去一行后果,返回一次数据
  2. 从 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 analyzeexplain format=tree 的补充,两者都是 8.0 呈现的新性能,这里简略介绍一下我集体了解的查看这种执行打算的程序,如果有不正确的中央,还请斧正:最先查看第一个缩进最多的行,没有雷同缩进时,再向上一个缩进查看,再查看雷同缩进的行(如果它有子缩进行,也是先查看缩进最多的行),以如下 SQL 为例,它的执行打算查看程序为 10->9->12->11->8->13->7->6->5->4->3

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

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

总结

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

须要留神点:

  1. explain analyze 过程中会理论执行具体 SQL,但并不会 SQL 的执行后果,返回的后果集是具体执行步骤
  2. 目前只反对 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 群。

退出移动版