网站导航
> 文档中心 > 你真的会用EXPLAIN么，SQL性能优化王者晋级之路

你真的会用EXPLAIN么，SQL性能优化王者晋级之路

网友: 文档中心 2022-04-25 11:18:39

MySQL 8.0新特性专栏目录

你真的会用EXPLAIN么，SQL性能优化王者晋级之路
索引三剑客之降序索引和不可见索引
千呼万唤始出来，MySQL 8.0索引三剑客之函数索引
双重密码，MySQL 8.0创新特性
sql_mode兼容性，MySQL 8.0 升级踩过的坑
警惕参数变化，MySQL 8.0 升级避免再次踩坑

你真的会用EXPLAIN么，SQL性能优化王者晋级之路

  • MySQL 8.0新特性专栏目录
  • 前言
  • 1. 青铜选手使用EXPLAIN - EXPLAIN
  • 2. 铂金选手使用EXPLAIN - EXPLAIN FOR CONNECTION
  • 3. 钻石选手使用EXPLAIN - EXPLAIN ANALYZE
  • 4. 最强王者使用EXPLAIN - OPTIMIZER_TRACE
  • 总结

前言

MySQL作为全球最流行的数据库，相关从业者不计其数，可以说十个码农里至少有九个使用过MySQL。MySQL的开发人员或者DBA，经常使用EXPLAIN语句来查看SQL的执行计划。EXPLAIN的解读文章多如牛毛，每个开发人员对EXPLAIN结果都有自己的理解。然而，你真的会使用EXPLAIN吗？

1. 青铜选手使用EXPLAIN - EXPLAIN

我们以sysbench的测试表sbtest1上的查询为例，来看看大家都是怎么使用EXPLAIN的。

# 测试表Create Table: CREATE TABLE `sbtest1` (  `id` int NOT NULL AUTO_INCREMENT,  `k` int NOT NULL DEFAULT '0',  `c` char(120) NOT NULL DEFAULT '',  `pad` char(60) NOT NULL DEFAULT '',  PRIMARY KEY (`id`),  KEY `idx_k` (`k`) /*!80000 INVISIBLE */,  KEY `idx_c`(`c`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

表sbtest1上有两个二级索引idx_k和idx_c，执行以下SQL：select pad from sbtest1 where c = ‘64613629’ and k = 64613629;

青铜选手一般直接使用explain select ...查看SQL的执行计划。

MySQL [sbtest]> explain select pad from sbtest1 where c = '64613629' and k = 64613629;+----+-------------+---------+------------+------+---------------+-------+---------+-------+------+----------+-------------+| id | select_type | table   | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra|+----+-------------+---------+------------+------+---------------+-------+---------+-------+------+----------+-------------+|  1 | SIMPLE      | sbtest1 | NULL| ref  | idx_c   | idx_c | 120     | const |    1 |     5.00 | Using where |+----+-------------+---------+------------+------+---------------+-------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.01 sec)

根据EXPLAIN的输出结果，我们可以看到这条查询SQL的执行计划为二级索引idx_c的索引扫描。

那么实际SQL执行时，真的如EXPLAIN所显示的那样使用到了idx_c这个索引吗？

2. 铂金选手使用EXPLAIN - EXPLAIN FOR CONNECTION

针对上述测试表的例子，实际执行时到底是使用idx_k还是idx_c索引，其实我们是不知道的。

那么，要想知道SQL实际执行时的索引选择，也是有方法的。

这里，我们介绍一个EXPLAIN的高级用法 - explain for connection。explain for connection可以查看正在执行的会话中SQL的执行计划。

还是以上面的测试表为例，首先，我们在会话1中执行SQL。

# Session 1MySQL [sbtest]> select pad from sbtest1 where c = 64613629 and k = '64613629';

然后，开启另外一个会话2，在会话2中查看当前正在执行的SQL以及对应的PROCESSID。

# Session 2MySQL [sbtest]> show processlist;+----------+------------+----------------------+--------+------------------+---------+---------------------------------------------------------------+--------------------------------------------+| Id| User| Host   | db     | Command   | Time    | State | Info    |+----------+------------+----------------------+--------+------------------+---------+---------------------------------------------------------------+--------------------------------------------+| 20255132 | wang| 127.0.0.1:57932      | sbtest | Query     |0 | init  | show processlist      || 20264939 | wang| 127.0.0.1:50656      | sbtest | Query     |5 | executing    | select pad from sbtest1 where c = 64613629 and k = '64613629' |+----------+------------+----------------------+--------+------------------+---------+---------------------------------------------------------------+--------------------------------------------+

最后，根据show processlist显示的线程号，使用explain for connection {PROCESSID} 查看会话1的SQL执行计划。可以看到，会话1的执行计划实际上是个全表扫描，而非idx_c上的索引扫描。

# Session 2MySQL [sbtest]> explain for connection 20264939;+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra|+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+|  1 | SIMPLE      | sbtest1 | NULL| ALL  | idx_c  | NULL | NULL    | NULL | 93776256 |   100.00 | Using where |+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+

使用explain for connection可以查看指定会话的SQL执行计划。这样就可以看到当前正在实际执行的SQL的执行计划。有了这个神器，我们可以更加直接地验证SQL的执行计划。

然而，假设会话1的SQL执行持续时间很短，或者数据库中当前连接过多，想要找到指定SQL的连接并查看其执行计划，并不是一件易事。

要想更进一步，我们需要一个更加直观有效的查看执行计划的方法。

3. 钻石选手使用EXPLAIN - EXPLAIN ANALYZE

MySQL 8.0.18中引入了一种新形式的EXPLAIN语句 - EXPLAIN ANALYZE。EXPLAIN ANALYZE可以将SQL的每一步执行计划的估算成本和实际成本以树形格式展示。 也就是说，EXPLAIN ANALYZE会先根据统计信息估算出SQL的执行成本，然后实际地执行SQL，再将SQL实际执行的成本和每一步的调用次数、返回行数等信息一并展示出来。我们可以对比估算和实际执行的情况，对SQL的执行计划有个全面的了解。

还是以上述测试表的查询为例，我们来验证一下EXPLAIN ANALYZE是否真的这么神奇。

# explain analyze查看实际执行计划MySQL [sbtest]> explain analyze select pad from sbtest1 where c = 64613629 and k = '64613629';+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| EXPLAIN    |+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| -> Filter: ((sbtest1.c = 64613629) and (sbtest1.k = 64613629))  (cost=100959.75 rows=9938) (actual time=0.046..344.477 rows=1 loops=1)    -> Table scan on sbtest1  (cost=100959.75 rows=993820) (actual time=0.041..243.610 rows=1000000 loops=1) |+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.35 sec)

可以看到，EXPLAIN ANALYZE的输出结果跟之前的EXPLAIN输出相比差别很大。在MySQL 8.0中，为了更清晰地展示执行计划，引入了format=tree的输出格式。（事实上，同时引入的还有另外一种format=json的输出格式，该格式对于程序中处理输出结果很有帮助）

树形输出格式的执行计划，阅读顺序秉持两个原则：

  1. 缩进越大的行越先执行；
  2. 缩进相同的行从上往下执行。

这个例子中，先执行缩进大的第二行：Table scan on sbtest1，即对sbtest1进行全表扫描；第一个括号中展示估算的执行成本为100959.75，估算返回行数为993820；第二个括号中展示实际的执行情况，actual time=0.041…243.610 表示获取第一行的执行时间是0.041ms，获取所有行的时间为243.610ms（注意，如果循环执行了多次，这里表示每次获取所有行的平均时间），实际返回行数100w，循环1次。

再执行缩进比较小的第一行：Filter，结果集过滤，第一个括号展示估算的执行成本为100959.75，估算返回行数为9938；第二个括号展示实际的执行情况，actual time=0.046…344.477表示获取第一行的执行时间是0.046ms，获取所有行的时间为344.477ms，实际返回行数1行，循环1次。（注意，缩进较小的行对应的返回所有行的时间是包括他之下的缩进较大的行的返回时间）。

EXPLAIN ANALYZE中最后一步显示这条SQL的实际执行时间为344.477ms，即总的执行时间约为0.35s（跟MySQL返回的SQL执行时间1 row in set (0.35 sec) 一致）。

那么，EXPLAIN ANALYZE展示的实际执行时间，是不是这条SQL的真实执行时间呢？ 其实也并不是的。

我们来实际执行这条SQL，看看具体执行时间。

# 实际执行SQL，查看执行时间MySQL [sbtest]> select pad from sbtest1 where c = 64613629 and k = '64613629';+-------------------------------------------------------------+| pad |+-------------------------------------------------------------+| 22195207048-70116052123-74140395089-76317954521-98694025897 |+-------------------------------------------------------------+1 row in set (0.29 sec)

可以看到，SQL实际执行时间为0.29s，时间短于EXPLAIN ANALYZE中显示的0.35s，相差了20%。是的，EXPLAIN ANALYZE追踪SQL的实际执行过程，自然会产生一些额外的开销。 EXPLAIN ANALYZE的执行开销还是有点大的，这里希望官方能够再优化一下。

不管怎么说，MySQL 8.0引入的EXPLAIN ANALYZE向我们清晰展示了SQL的估算执行成本和实际执行消耗，对于优化SQL性能有着非常大的价值。

没有使用过MySQL 8.0的EXPLAIN analyze 就不算真的会用EXPLAIN！

4. 最强王者使用EXPLAIN - OPTIMIZER_TRACE

前面介绍了EXPLAIN的几种使用姿势，用好EXPLAIN工具我们就能够很好的应对SQL性能优化了。最后，我们介绍一个不用EXPLAIN也能查看优化器执行计划的方式，性能优化中的最强王者 - optimizer_trace。

optimizer_trace可以将MySQL优化器的决策和执行过程统统记录下来，以JSON格式保存在INFORMATION_SCHEMA.OPTIMIZER_TRACE表，以便于DBA和开发人员深入了解优化器的决策全过程。

还是以上述测试SQL，我们来测试验证一下。

# 1.打开optimizer_traceSET optimizer_trace="enabled=on";# 2. 执行测试SQLselect pad from sbtest1 where c = 64613629 and k = '64613629';# 查看INFORMATION_SCHEMA.OPTIMIZER_TRACE表SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;# 关闭optimizer_traceSET optimizer_trace="enabled=off";

INFORMATION_SCHEMA.OPTIMIZER_TACER表中内容很长，截取其中的决策部分如下：

# optimizer_trace的部分输出{"considered_execution_plans": [{"plan_prefix": [],"table": "`sbtest1`","best_access_path": {"considered_access_paths": [{"rows_to_scan": 993820,"access_type": "scan","resulting_rows": 993820,"cost": 100960,"chosen": true}]},"condition_filtering_pct": 100,"rows_for_plan": 993820,"cost_for_plan": 100960,"chosen": true

对于SQL执行计划与我们预料的结果不一致时，可以使用optimizer_trace这一终极大杀器。由于现实中在MySQL中执行的SQL并不复杂，所以一般情况下也就用不上这个核武器级别的工具。

总结

本文介绍了查看SQL优化器执行过程的几种方式：EXPLAIN、EXPLAIN FOR CONNECTION、EXPLAIN ANALYZE、OPTIMIZER_TRACE，助力开发人员在SQL性能优化路上披荆斩棘，从青铜选手走向自强王者。

喜欢的同学麻烦点个关注和点赞

不搭配

网络标签：

相关问题