mysql explain 详解

在使用MySQL时,如果发现查询语句耗时,会进行排查及调优,其中常用的一个方法是用explain查看sql执行计划。

(1) explain小试牛刀

通过一个简单的例子来了解explain的使用

CREATE TABLE `t2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` int DEFAULT NULL,
  `b` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`),
  KEY `idx_b` (`b`)
) ENGINE=InnoDB ;

往表 t2 中插入 10 万行记录,取值按整数递增,即:(1,1,1),(2,2,2),(3,3,3) 直到 (100000,100000,100000)。

用存储过程来插入10万数据,sql如下:

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;

call idata(); /** 调用存储过程 */

假设需要执行下面这条语句,语句的执行结果最多只有1条,因为有limit 1,但是表t2有2个索引 idx_a idx_b ,MySQL会选用哪个?
如果是我们来分析,肯定是选idx_a,因为 a between 1 and 1000 只需要扫描idx_a索引1000个值,而 b between 50000 and 100000需要扫描idx_b索引50000个值

mysql> select * from t where (a between 1 and 1000)  and (b between 50000 and 100000) order by b limit 1;
mysql> explain select * from t2 where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                              |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | range | a,b           | b    | 5       | NULL | 50128 |     1.00 | Using index condition; Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

结合慢查询日志

# Time: 2022-12-10T17:11:35.593173+08:00
# User@Host: admin[admin] @ localhost [127.0.0.1]  Id:    16
# Query_time: 0.105205  Lock_time: 0.000003 Rows_sent: 0  Rows_examined: 50001
SET timestamp=1670663495;
select * from t2 where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

慢日志的Rows_examined:执行该SQL,实际扫描的行数,真实值
慢日志的Rows_sent:执行该SQL,实际返回的行数

之前优化器选择使用索引 b,是因为它认为使用索引 b 可以避免排序(b 本身是索引,已经是有序的了,如果选择索引 b 的话,不需要再做排序,只需要遍历),所以即使扫描行数多,也判定为代价更小。

mysql> explain select * from t2 where (a between 1 and 1000) and (b between 50000 and 100000) order by b,a limit 1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | range | a,b           | a    | 5       | NULL | 1000 |    50.00 | Using index condition; Using where; Using filesort |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
1 row in set, 1 warning (0.01 sec)

mysql>

order by b,a 这种写法,要求按照 b,a 排序,就意味着使用这两个索引都需要排序。因此,扫描行数成了影响决策的主要条件,于是此时优化器选了只需要扫描 1000 行的索引 a。

# Time: 2022-12-10T17:27:04.352721+08:00
# User@Host: admin[admin] @ localhost [127.0.0.1]  Id:    16
# Query_time: 0.020284  Lock_time: 0.000020 Rows_sent: 0  Rows_examined: 1000
SET timestamp=1670664424;
select * from t2 where (a between 1 and 1000) and (b between 50000 and 100000) order by b,a limit 1;
mysql>  explain
    ->  select count(*)
    ->  from table_c
    ->  where l_id not in (
    ->    select l_id from table_l
    ->  ) ;
+----+--------------------+---------------+------------+----------------+---------------+--------------+---------+------+--------+----------+------------------------------------+
| id | select_type        | table         | partitions | type           | possible_keys | key          | key_len | ref  | rows   | filtered | Extra                              |
+----+--------------------+---------------+------------+----------------+---------------+--------------+---------+------+--------+----------+------------------------------------+
|  1 | PRIMARY            | table_c       | NULL       | index          | NULL          | idx_l_id     | 51      | NULL | 197037 |   100.00 | Using where; Using index           |
|  2 | DEPENDENT SUBQUERY | table_l       | NULL       | index_subquery | idx_l_id      | idx_l_id     | 387     | func |      2 |   100.00 | Using index; Full scan on NULL key |
+----+--------------------+---------------+------------+----------------+---------------+--------------+---------+------+--------+----------+------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql>

(2) explain参数详解

id Columns JSON Name Meaning
1 id select_id 每个select子句的标识id
2 select_type None select语句的类型
3 table table_name 当前表名
4 partitions partitions 匹配的分区
5 type access_type 当前表内访问方式 join type
6 possible_keys possible_keys 可能使用到的索引
7 key key 经过优化器评估最终使用的索引
8 key_len key_length 使用到的索引长度
9 ref ref 引用到的上一个表的列
10 rows rows rows_examined,要得到最终记录索要扫描经过的记录数
11 filtered filtered 按表条件过滤行的百分比
12 Extra None 额外的信息说明

(2.1) id

SELECT识别符。这是SELECT查询序列号。这个不重要,查询序号即为sql语句执行的顺序。

(2.2) select_type

select_type语句类型有

select类型,它有以下几种:

id select_type value JSON name Meaning
1 SIMPLE None 简单的SELECT语句(不包括UNION操作或子查询操作)
2 PRIMARY None PRIMARY:查询中最外层的SELECT(如两表做UNION或者存在子查询的外层的表操作为PRIMARY,内层的操作为UNION)
3 UNION None UNION:UNION操作中,查询中处于内层的SELECT(内层的SELECT语句与外层的SELECT语句没有依赖关系)
4 DEPENDENT UNION dependent(true) DEPENDENT UNION:UNION操作中,查询中处于内层的SELECT(内层的SELECT语句与外层的SELECT语句有依赖关系)
5 UNIOIN RESULT union_result UNION RESULT:UNION操作的结果,id值通常为NULL
6 SUBQUERY None SUBQUERY:子查询中首个SELECT(如果有多个子查询存在)
7 DEPENDENT SUBQUERY dependent(true) DEPENDENT SUBQUERY:子查询中首个SELECT,但依赖于外层的表(如果有多个子查询存在)
8 DERIVED None DERIVED:被驱动的SELECT子查询(子查询位于FROM子句)
9 MATERIALIZED materialized_form_subquery MATERIALIZED:被物化的子查询
10 UNCACHEABLE SUBQUERY cacheable(false) UNCACHEABLE SUBQUERY:对于外层的主表,子查询不可被物化,每次都需要计算(耗时操作)
11 UNCACHEABLE UNION cacheable(false) UNCACHEABLE UNION:UNION操作中,内层的不可被物化的子查询(类似于UNCACHEABLE SUBQUERY)

(2.3) table

当前表名

(2.4) partitions

匹配的分区

(2.5) type

当前表内访问方式

性能由好到坏排序:

id type value Meaning
1 system 表中只有一行
2 const 单表中最多有一个匹配行,primary key 或者 unique index的检索
3 eq_ref 多表连接中被驱动表的连接列上有primary key或者unique index的检索
4 ref 与eq_ref类似,但不是使用primary key或者unique index,而是普通索引。也可以是单表上non-unique索引检索
5 fulltext 使用FULLTEXT索引执行连接
6 ref_or_null 与ref类似,区别在于条件中包含对NULL的查询
7 index_merge 索引合并优化,利用一个表里的N个索引查询,key_len表示这些索引键的和最长长度。
8 unique_subquery in的后面是一个查询primary key\unique字段的子查询
9 index_subquery in的后面是一个查询普通index字段的子查询
10 range 单表索引中的范围查询,使用索引查询出单个表中的一些行数据。ref列会变为null
11 index 等于ALL。它有两种情况:(1)覆盖索引 (2)用索引的顺序做一个全表扫描。
12 all 全表扫描

(2.6) possible_keys

提示使用哪个索引会在该表中找到行

(2.7) key

MYSQL使用的索引

(2.8) key_len

key_len 说明
key_len: 4 // INT NOT NULL
key_len: 5 // INT DEFAULT NULL
key_len: 30 // CHAR(30) NOT NULL
key_len: 32 // VARCHAR(30) NOT NULL
key_len: 92 // VARCHAR(30) NOT NULL CHARSET=utf8

key_len大小的计算规则:
a、一般地,key_len 等于索引列类型字节长度,例如int类型为4-bytes,bigint为8-bytes;
b、如果是字符串类型,还需要同时考虑字符集因素,例如:CHAR(30) UTF8则key_len至少是90-bytes;
c、若该列类型定义时允许NULL,其key_len还需要再加 1-bytes;
d、若该列类型为变长类型,例如 VARCHAR(TEXT\BLOB不允许整列创建索引,如果创建部分索引,也被视为动态列类型),其key_len还需要再加 2-bytes;

(2.9) ref

ref列显示使用哪个列或常数与key一起从表中选择行。

(2.10) rows

rows_examined,要得到最终记录索要扫描经过的记录数,这个数越小越好。

(2.11) filterrd

按表条件过滤行的百分比

(2.12) Extra

Extra是对执行计划的额外说明,包含重要信息。

例如:

id type value Meaning
1 const row not found 所要查询的表为空
2 Distinct mysql正在查询distinct值,因此当它每查到一个distinct值之后就会停止当前组的搜索,去查询下一个值
3 Impossible WHERE where条件总为false,表里没有满足条件的记录
4 Impossible WHERE noticed after reading const tables 在优化器评估了const表之后,发现where条件均不满足
5 no matching row in const table 当前join的表为const表,不能匹配
6 Not exists 优化器发现内表记录不可能满足where条件
7 Select tables optimized away 在没有group by子句时,对于MyISAM的select count(*)操作,或者当对于min(),max()的操作可以利用索引优化,优化器发现只会返回一行。
8 Using filesort 使用filesort来进行order by操作
9 Using index 覆盖索引
10 Using index for group-by 对于group by列或者distinct列,可以利用索引检索出数据,而不需要去表里查数据、分组、排序、去重等等
11 Using join buffer 之前的表连接在nested loop之后放进join buffer,再来和本表进行join。适用于本表的访问type为range,index或all
12 Using sort_union,using union,using intersect index_merge的三种情况
13 Using temporary 使用了临时表来存储中间结果集,适用于group by,distinct,或order by列为不同表的列。
14 Using where 在存储引擎层检索出记录后,在server利用where条件进行过滤,并返回给客户端

(3) Explain妙用

查询表的大概数据总数。
EXPLAIN SELECT * FROM t1 ;
查询表t1的大概总数。

References

[1] EXPLAIN Statement
[2] explain-output
[3] MYSQL explain详解