前言

产品经理：“哎，最近上线的页面为啥那么慢，”。

小王：“经理，我先排查下，排查后给答复”。

从上面的对话中，可以看出来，作为一个运维工程师，小王需要排查SQL性能问题，针对性能问题，百度也有很多解决方案，例如查询性能优化SQL脚本、优化查询索引长度、复杂的JOIN操作简单化等等。

在解决这个问题前，我们在多数情况下需要使用关键字 EXPLAIN 来协助我们排查问题以及解决问题。

演示环境

当前所有操作均在都在 MySQL 8.0.31 中执行，如果有出入，还望指出。

操作环境：

操作系统：MacOS 13.2
MySQL版本：MySQL 8.0.31
安装MySQL方式：Homebrew
演示库、表：db_test库、it_test1表

EXEPLAIN

在 MySQL 中，EXPLAIN 是一个非常有用的关键字，它可以帮助我们理解已经写好的 SQL 查询的执行计划，进而优化查询，提升性能。通过 EXPLAIN 命令，可以查看 MySQL 如何使用索引，以及查询需要扫描多少行等等，这对于诊断和解决性能问题非常有帮助。

1 执行格式

在MySQL中，我们可以使用 explain 关键字后面跟上我们需要测试的SQL脚本，然后根据需要查询的信息来处理原SQL，来提升性能。

explain [SQL]

这里真得感谢发明这个SQL的大佬，就是一个关键字，再加上你需要执行的语句。例如：

mysql> explain select * from it_test1;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | it_test1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql>

2 格式详解

从上面的执行上来看，大体上有如下字段，下面这张图是从官网拿来的。

备注：图来自 https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

id：在SQL语句执行的过程中，每执行一次都会产生与关键字SELECT相对应的一个唯一ID，这个ID是顺序产生。
select_type：查询的类型。类型大体SIMPLE、PRIMARY、UNION、DEPENDENT UNION、UNION RESULT、SUBQUERY、DEPENDENT SUBQUERY、DERIVED、DEPENDENT DERIVED、MATERIALIZED、UNCACHEABLE SUBQUERY、UNCACHEABLE UNION。
table：涉及到的表的名称。
partitions：涉及到的分区信息。
type：单表时访问方法。
possible_keys：可能命中的key。
key：使用使用到的key。
key_len：使用使用到的key长度。
ref：当你使用索引key查询时，与索引匹配的对象的信息。
rows：预估匹配到的行的数量。
filtered：过滤列表示表条件过滤的表行的估计百分比。最大值为100，这意味着没有发生行过滤（有时时无效过滤）。从100开始递减的值表示过滤量增加。行显示检查的估计行数，行×过滤显示与下表连接的行数。例如，如果行为1000，过滤为50.00（50%），则要与下表连接的行数为1000×50%=500。
Extra：其他信息。

如果此时还不能解决或者消除你的疑问，建议可以参考下SELECT语句的用法，一看便知。

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [into_option]
into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

3 详细讲解

为了方便演示，这里创建多张表：it_test1 、 it_test2 、it_test3

--- 表it_test1以及数据
CREATE TABLE `it_test1` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '序号',
  `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '姓名',
  `sex` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '性别',
  `age` int DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC COMMENT='演示-字符集'
insert into it_test1 values(null, 'zhang', '男', 20);
insert into it_test1 values(null, 'zhang', '男', 20);
insert into it_test1 values(null, 'wang', '女', 18);
insert into it_test1 values(null, 'li', '男', 20);
--- 表it_test2以及数据
CREATE TABLE `it_test2` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '序号',
  `t1_id` int(10) COMMENT 't1ID', 
  `grade` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '班级',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC COMMENT='演示-字符集2';
insert into it_test2 values(null, 1, '一年级');
insert into it_test2 values(null, 2, '一年级');
insert into it_test2 values(null, 3, '二年级');
insert into it_test2 values(null, 4, '三年级');

4 关键关注 type

无论SQL语句是如何的复杂，当我们执行这条语句时，最终我们可以看到的都是这对单表来操作，以及单表的操作详细。如下所示it_test1、it_test2两条记录：

mysql> explain select * from it_test1 t1 inner join it_test2 t2 on t1.id = t2.t1_id;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL             |    4 |   100.00 | Using where |
|  1 | SIMPLE      | t1    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | db_test.t2.t1_id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
mysql>

查询的访问类型，是较为重要的一个数据参数，该类型可以说直接反映你SQL语句执行的快慢。结果值从最高到最低依次为：

system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all

我们平时SQL调优的时候，一般得保证查询至少达到range级别，最好能达到ref。

5 关键关注key和key_len

索引长度

KEY为真实使用的索引。如果为NULL，表示没有使用索引。在上面的查询过程中，可以看到t1已经被命中 PRIMARY 类型的索引，结合索引长度可以看到为 4 。这里为什么是4呢？原来呀，这里是这个字段类型的实际数据最多占用的存储空间长度就是4（INT类型的长度），索引长度为索引中使用的字节数。key_len字段能够帮你检查是否充分的利用上了索引。ken_len越长，说明索引使用得越充分。

索引长度为NULL

it_test2中就是NULL，主要是it_test1为主要驱动查询，而it_test2为被动驱动查询。

6 关键关注ref

一般情况下，这个也是我们需要关注的，例如当前查询下为 db_test.t2.t1_id 可以看出来，这里是执行命中到db_test库的t2表中的t1_id列。此时对应的匹配类型为 eq_ref （相等映射关联）。索引多的时候，此处比较复杂，本篇不在多述。

7 关键关注 filtered

filtered 表示某个表经过搜索条件过滤后剩余记录条数的百分比，如果使用的是索引执行的单表扫描，那么计算时需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。

mysql> explain select * from it_test1 t1 inner join it_test2 t2 on t1.id = t2.t1_id where t2.t1_id = 1 ;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | t2    | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |    4 |    25.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
mysql>

当我们增加一个限制条件 t2.t1_id = 1，可以看到从 t2 表中扫描获取到 1 条信息，所占比例为1/4 也就是25 %。

8 关键关注 Extra

这里使用了一些额外的信息协助我们理解SQL脚本，例如条件查询、聚合函数、索引、扫描区间匹配、使用临时表、排序等等。下面列举一些常用的常看到的：

Using filesort（降低性能）： MySQL 中无法利用索引完成的排序操作称为“文件排序”。
Using temporary（降低性能）：使了用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
Using index（提高性能）：Using index 代表表示相应的 select 操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！如果同时出现 using where，表明索引被用来执行索引键值的查找；如果没有同时出现 using where，表明索引只是用来读取数据而非利用索引执行查找。利用索引进行了排序或分组。
Using where：表明使用了 where 过滤。
Using join buffer：使用了连接缓存
impossible where where ：子句的值总是 false，不能用来获取任何元组。

总结

本篇简单讲述mysql中explain关键字的使用，以及explain关键字中我们常用的一些含义，结合调用情况作分析，可以结合查询情况来做进一步分析，本篇没有做数据量测试，只是简单描述，后续再写一篇从量上来测试。

【引用】

[官网explain](!https://dev.mysql.com/doc/refman/8.0/en/execution-plan-information.html)

MySQL｜浅谈explain的使用

前言

演示环境

EXEPLAIN

1 执行格式

2 格式详解

3 详细讲解

4 关键关注 type

5 关键关注key和key_len

6 关键关注ref

7 关键关注 filtered

8 关键关注 Extra

总结

数据库

热门文章

最新文章

相关产品

相关课程

相关电子书

相关实验场景

推荐镜像