Mysql提供了EXPLAIN命令，通过对查询语句分析，输出执行详细情况，以供开发人员针对性优化。
EXPLAIN命令输出内容大致如下：
EXPLAIN SELECT * FROM user WHERE id =1 \G
************************************************
id: 1
select_type: SIMPLE
table: user
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const
rows: 1
filtered: 100.00
Extra: NULL
以下将逐个解释字段含义
id
id可以理解为表示语句执行的优先级，更大的id最先被执行，id相同时，执行由上至下。（如果这一row是其他row的union，id可为NULL。此时，table字段会展示为<union M,N>）
select_type
select_type表示查询语句类型，常用取值有
-
SIMPLE: 表示不包含UNION查询或子查询
-
PRIMARY: 表示最外层SELECT
-
SUBQUERY: 子查询中的第一个SELECT语句
-
DEPENDENT SUBQUERY: 子查询的第一个SELECT，取决于外面的查询。
-
DERIVED：衍生表。用于from子句中有子查询的情况，MYSQL会递归执行这些子查询，将结果放在临时表里。
-
DEPENDENT DERIVED: 依赖于另一个表的衍生表
-
UNION: UNION之后的SELECT语句
mysql> EXPLAIN (SELECT * FROM user_info WHERE id IN (1, 2, 3)) -- 这是最外层的select，所以是primary -> UNION -- union前后两个select的结果，所以type为union result -> (SELECT * FROM user_info WHERE id IN (3, 4, 5)); -- union之后的select，所以type为union +----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | user_info | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 3 | 100.00 | Using where | | 2 | UNION | user_info | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 3 | 100.00 | Using where | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ -
DEPENDET UNION,UNION类型+取决于外面的查询
-
UNION RESULT: UNION语句的结果
table
输出查询涉及的表或衍生表
partitions
被匹配到的数据的分区，NULL代表无分区表。
type
type描述了表是怎么连接起来的。以下将从性能最好的type到最差的逐个阐述
-
system: const的特殊形式，表中只有这一条数据
-
const: 针对主键或唯一索引的等值查询扫描，最多只返回一行数据。
-
eq_ref: 通常出现在多表的join查询，表示前后两表的匹配是1对1的，查询比较操作通常为=
EXPLAIN SELECT * FROM user ,order WHERE user.id=order.user_id \G *************** 1. row ***************** id: 1 select_type: SIMPLE table: order partitions: NULL type: index possible_keys: user_product_detail_index key: user_product_detail_index key_len: 314 ref: NULL rows: 9 filtered: 100.00 Extra: Using where; Using index ***************** 2. row *********** id: 1 select_type: SIMPLE table: user partitions: NULL type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: test.order_info.user_id rows: 1 -
ref: 通常出现在多表的join查询中，针对非唯一或非主键索引，或者是使用了最左前缀规则的索引查询
SELECT * FROM ref_table WHERE key_column=expr; SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column; SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1; -
fulltext
这个join使用了fulltext索引
-
ref or null
类似于ref，但是额外加入了查询行是否为NULL
SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;需要注意的是只有当查询列为null且该列被声明为not null时，优化才会发生。在我看来，ref or null是不应该发生的，需要被优化。
参考：https://dev.mysql.com/doc/refman/8.0/en/is-null-optimization.html
-
index merge
此type表示index merge optimization被使用。
Index Merge Optimization是在5.0引入的新特性，当查询中单个表可以使用多个索引时，同时扫描多个索引并将扫描结果合并
主要应用于以下场景：
- OR求并集，如查询SELECT * FROM TB1 WHERE c1=“xxx” OR c2=""xxx"时，如果c1和c2列上分别有索引，可以按照c1和c2条件进行查询，再将查询结果合并（union）操作，得到最终结果
- 对AND语句求交集，如查询SELECT * FROM TB1 WHERE c1=“xxx” AND c2=""xxx"时，如果c1和c2列上分别有索引，可以按照c1和c2条件进行查询，再将查询结果取交集（intersect）操作，得到最终结果
- 对AND和OR组合语句求结果
该特性可以在一些场景中大幅度提升查询性能，但受限于MySQL糟糕的统计信息，也导致很多场景查询性能极差甚至导致数据库崩溃。
以SELECT * FROM TB1 WHERE c1=“xxx” AND c2="“xxx” 为例：-
当c1,c2选择性较高时，按照c1，c2进行条件查询性能高且返回数据集少，对两个数据量较小的求交集成本也低，所以查询高效
-
当c1列或c2列选择性较差且统计信息不准时，比如整表数据量2000万，按照c2列条件返回1500万数据，按照c1列返回1000条数据，此时按照c2列条件进行索引扫描+聚集索引查找的操作成本极高(可能是整表扫描的百倍消耗)，对1000条数据和1500万数据求交集的成本也极高，最终导致整条SQL需要消耗大量CPU和IO资源且相应时间超长，而如果值使用c1列的索引，查询消耗资源较少且性能较高。
参考自 https://www.jianshu.com/p/67b39af2f851
-
unique_subquery
eq_ref是等值连接，一一对应，而unique_subquery是in连接主键、唯一索引集
value IN (SELECT primary_key FROM single_table WHERE some_expr) -
index_subquery
与unique_subquery的不同在于index_subquery in后面的是普通索引
-
range: 表示使用索引范围查询，通过索引字段范围获取表中部分数据，通常出现在<>,<,>,IS NULL等范围操作中
当type为range时，那么EXPLAIN输出的ref字段为NULL，并且key_len字段则是此次查询中使用到的索引最长的那个
EXPLAIN SELECT * FROM user WHERE id BETWEEN 1 AND 3 \G **************** 1. row ***************** id: 1 select_type: SIMPLE table: user_info partitions: NULL type: range possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: NULL rows: 7 filtered: 100.00 Extra: Using where -
index: 全索引扫描，扫描所有索引。通常出现在所要查询的数据直接在索引树中就可以获取到，而不需要扫描数据。在这种情况下，Extra字段会显示Using index.
EXPLAIN SELECT name FROM user \G ******************* 1. row ***************** id: 1 select_type: SIMPLE table: user_info partitions: NULL type: index possible_keys: NULL key: name_index key_len: 152 ref: NULL rows: 10 filtered: 100.00 Extra: Using index上面的例子中, 我们查询的 name 字段恰好是一个索引, 因此我们直接从索引中获取数据就可以满足查询的需求了, 而不需要查询表中的数据. 因此这样的情况下, type 的值是
index, 并且 Extra 的值是Using index. -
all: 表示全表扫描，这是性能最差的查询之一，需要对所有的表进行扫描
EXPLAIN SELECT age FROM user_info WHERE age = 20 \G **************** 1. row ***************** id: 1 select_type: SIMPLE table: user_info partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 10 filtered: 10.00 Extra: Using where由上面的例子可以看到全表扫描时，possible_keys和key字段都是NULL，表示没有使用到索引，并且rows十分的大
possible_keys
possible_keys表示Mysql在查询时能够使用到的索引，但并不代表一定会被Mysql真正使用到，Mysql在查询时具体使用到了哪些索引由key字段决定。
key
key代表Mysql在当前查询时真正使用到的索引
以下是查询到productor为’WHH’的user name的例子
EXPLAIN SELECT name FROM user_info WHERE id IN (SELECT user_id FROM order_info WHERE productor='WHH');
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: order_info
partitions: NULL
type: index
possible_keys: user_product_detail_index
key: user_product_detail_index
key_len: 254
ref: NULL
rows: 9
filtered: 11.11
Extra: Using where; Using index; LooseScan
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: user_info
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: test.order_info.user_id
rows: 1
filtered: 100.00
Extra: NULL
有一个很疑惑的点，就是明明row1 并不符合最左前缀匹配原则，为什么还使用到了user_product_detail_index联合索引呢？
其实我们由type字段可以知道虽然它使用到了联合索引，但是是对整个索引树进行了扫描，正好匹配到了该索引，与最左匹配原则无关。
一般只要是某联合索引的一部分，但又不遵循最左匹配原则时，都可能会采用index类型的方式扫描，但它的效率远不如最做匹配原则的查询效率高，index类型类型的扫描方式是从索引第一个字段一个一个的查找，直到找到符合的某个索引，与all不同的是，index是对所有索引树进行扫描，而all是对整个磁盘的数据进行全表扫描。
key_len
表示查询优化器使用了索引的字节数，可以评估组合索引是否完全被使用。
计算规则如下：
-
字符串
- char(n): n 字节长度
- varchar(n): 如果是 utf8 编码, 则是 3 n + 2字节; 如果是 utf8mb4 编码, 则是 4 n + 2 字节.
-
数值类型
- TINYINT: 1字节
- SMALLINT: 2字节
- MEDIUMINT: 3字节
- INT: 4字节
- BIGINT: 8字节
-
时间类型
- DATE: 3字节
- TIMESTAMP: 4字节
- DATETIME: 8字节
-
字段属性
NULL 属性 占用一个字节. 如果一个字段是 NOT NULL 的, 则没有此属性.
rows
rows是Mysql查询优化器根据统计信息，估算SQL要查找到结果集所需要扫描读取的数据行数。
显然这个值越少越好
rows是如何估算的呢？
rows是Mysql认为必须要逐行去检查判断记录的条数
举例来说，语句select * from t where a=1 and b=2;
全表假设有100条记录，a有索引，b没有索引，a=1的记录有20条，a=1 and b=2 的记录有5条。
那么rows应该显示20，因为Mysql必须要逐条去判断是否满足where条件。
当然以上是理想情况，事实上rows是对表部分数据采样得到的，所以仅供参考
filtered
表示被表条件所筛选的估计百分比。
最大值为100代表没有发生行筛选
Extra
EXPLAIN中很多额外的信息都会在Extra字段显示，常见的有
-
Using filesort
表示Mysql需要额外的排序操作，不能通过索引顺序达到排序效果
-
Using index
覆盖索引扫描，表示查询在索引树中就可以查找到所需要的数据，不用扫描表数据文件
-
Using temporary
查询有使用临时表，一般出现于排序，分组和多表join情况，查询效率不高。
Ref
- https://segmentfault.com/a/1190000008131735
- https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
扫一扫
794
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
