mysql explain

参考：http://weikeqin.com/2020/02/05/mysql-explain/

作用与示例

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

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

-- 多层
mysql>  explain
    ->  select count(*)
    ->  from table_c
    ->  where l_id <> ''
    ->  and l_id is not null
    ->  and 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       | range          | idx_l_id      | idx_l_id     | 51      | NULL | 98519 |   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.01 sec)

参数总览

id

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

select_type

select_type语句类型有以下几种:

table：当前表名

partitions：匹配的分区

type：重点

当前表内访问方式，性能由好到坏排序：

ALL < index < range ~ index_merge < ref < eq_ref < const < system

例子：由坏到好

type=ALL，全表扫描，MySQL遍历全表来找到匹配行
一般是没有where条件或者where条件没有使用索引的查询语句

EXPLAIN SELECT * FROM customer WHERE active=0;

——————————————————————————————————————————————————
type=index，索引全扫描，MySQL遍历整个索引来查询匹配行，并不会扫描表
一般是查询的字段都有索引的查询语句

EXPLAIN SELECT store_id FROM customer;

——————————————————————————————————————————————————
type=range，索引范围扫描，常用于<、<=、>、>=、between等操作

EXPLAIN SELECT * FROM customer WHERE customer_id>=10 AND customer_id<=20;< span>

注意这种情况下比较的字段是需要加索引的，如果没有索引，则MySQL会进行全表扫描，如下面这种情况，create_date字段没有加索引：

EXPLAIN SELECT * FROM customer WHERE create_date>='2006-02-13' ;

————————————————————————————————————————————————
type=ref，使用非唯一索引或唯一索引的前缀扫描，返回匹配某个单独值的记录行
store_id字段存在普通索引（非唯一索引）

EXPLAIN SELECT * FROM customer WHERE store_id=10;

ref类型还经常会出现在join操作中：

customer、payment表关联查询，关联字段customer.customer_id（主键），payment.customer_id（非唯一索引）。表关联查询时必定会有一张表进行全表扫描，此表一定是几张表中记录行数最少的表，然后再通过非唯一索引寻找其他关联表中的匹配行，以此达到表关联时扫描行数最少。

因为customer、payment两表中customer表的记录行数最少，所以customer表进行全表扫描，payment表通过非唯一索引寻找匹配行。

EXPLAIN SELECT * FROM customer customer INNER JOIN payment payment ON customer.customer_id = payment.customer_id;

—————————————————————————————————————————————————
type=eq_ref，类似ref，区别在于使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配
eq_ref一般出现在多表连接时使用primary key或者unique index作为关联条件。

film、film_text表关联查询和上一条所说的基本一致，只不过关联条件由非唯一索引变成了主键。

EXPLAIN SELECT * FROM film film INNER JOIN film_text film_text ON film.film_id = film_text.film_id;

————————————————————————————————————————————————
type=const/system，单表中最多有一条匹配行，查询起来非常迅速，所以这个匹配行的其他列的值可以被优化器在当前查询中当作常量来处理
const/system出现在根据主键primary key或者 唯一索引 unique index 进行的查询

根据主键primary key进行的查询：

EXPLAIN SELECT * FROM customer WHERE customer_id =10;

根据唯一索引unique index进行的查询：

EXPLAIN SELECT * FROM customer WHERE email ='MARY.SMITH@sakilacustomer.org';

type=NULL，MySQL不用访问表或者索引，直接就能够得到结果

其他的一些例子：https://segmentfault.com/a/1190000008131735

key：MYSQL使用的索引

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;

ref

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

rows：越小越好

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

filterrd：越大越好

按表条件过滤行的百分比，过滤越多越好

Extra：重点

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

一些例子：https://segmentfault.com/a/1190000008131735