序言
Explain
语句提供了mysql如何执行语句的信息,包括select
,delete
,insert
, replace
, update
Explain输出的列
Column | JSON Name | Meaning |
---|---|---|
id |
select_id |
The SELECT identifier |
select_type |
None | The SELECT type |
table |
table_name |
The table for the output row |
partitions |
partitions |
The matching partitions |
type |
access_type |
The join type |
possible_keys |
possible_keys |
The possible indexes to choose |
key |
key |
The index actually chosen |
key_len |
key_length |
The length of the chosen key |
ref |
ref |
The columns compared to the index |
rows |
rows |
Estimate(估算) of rows to be examined(检查) |
filtered |
filtered |
Percentage(百分比) of rows filtered(过滤) by table condition |
Extra |
None | Additional information |
select_type
select
的类型,包含如下值:
Value | Meaning |
---|---|
SIMPLE |
Simple SELECT (not using UNION or subqueries) |
PRIMARY |
Outermost SELECT
|
UNION |
Second or later SELECT statement in a UNION
|
DEPENDENT UNION |
Second or later SELECT statement in a UNION , dependent on outer query |
UNION RESULT |
Result of a UNION . |
SUBQUERY |
First SELECT in subquery |
DEPENDENT SUBQUERY |
First SELECT in subquery, dependent on outer query |
DERIVED |
Derived table |
DEPENDENT DERIVED |
Derived table dependent on another table |
MATERIALIZED |
Materialized subquery |
UNCACHEABLE SUBQUERY |
A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query |
UNCACHEABLE UNION |
The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY ) |
type
顺序从好到差依次介绍如下:
system
:表只有一行。system
是const
的特例-
const
:const
用于将主键或唯一索引的所有部分与常量值进行=
测试,表最多有一个匹配行SELECT * FROM tbl_name WHERE primary_key=1; SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2; --上述查询是const类型的
-
eq_ref
:多表联结中使用primary key
或者unique not null
的列,进行=
测试,比较值可以是常量,也可以是在该表之前读取的表中的列的表达式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; --上述查询是eq_ref类型的
create table table_0(id_0 bigint primary key, v_0 int, index(v_0)); create table table_1(id_1 bigint primary key, v_1 int, index(v_1)); insert into table_0(id_0, v_0) values (0, 1), (1, 2); insert into table_1(id_1, v_1) values (0, 1), (1, 2); explain SELECT * from table_0, table_1 where table_0.id_0 = table_1.id_1
id select_type table partitions type possible_keys key key_len ref rows filtered extra 1 simple table_0 null index primary v_0 5 null 2 100 using index 1 simple table_1 null eq_ref primary primary 8 szn.table_0.id+0 1 100 null -
ref
:类似于eq_ref
,ref
操作的索引不是primary key
或者unique not null
,所以返回的行数可能大于1行(在索引列上使用的依然是=
操作)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; --上述查询是ref类型的
explain SELECT * from table_0, table_1 where table_0.v_0 = table_1.v_1 --表结构及包含的数据同eq_ref时举的例子
id select_type table partitions type possible_keys key key_len ref rows filtered extra 1 simple table_0 null index v_0 v_0 5 null 2 100 Using where; Using index 1 simple table_1 null ref v_1 v_1 5 szn.table_0.id+0 1 100 Using index fulltext
:使用全文索引-
ref_or_null
:类似于ref
,但是增加了搜索行是否为空的条件SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL; --上述查询是ref_or_null类型的
index_merge
:索引合并优化被使用-
unique_subquery
:在以下形式的in
子查询中,unique_subquery
代替了eq_ref
value IN (SELECT primary_key FROM single_table WHERE some_expr)
-
index_subquery
:类似于unique_subquery
,但是作用的index
不是唯一索引value IN (SELECT key_column FROM single_table WHERE some_expr)
-
range
:使用索引来检索一个范围的行,支持的操作符:=
,<>
,>
,>=
,<
,<=
,IS NULL
,<=>
,BETWEEN
,LIKE
, orIN()
SELECT * FROM tbl_name WHERE key_column = 10; SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20; SELECT * FROM tbl_name WHERE key_column IN (10,20,30); SELECT * FROM tbl_name WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
-
index
:将扫描索引树,有二种情况:- 如果查询的索引是覆盖索引,则只有索引树被扫描(速度很快)。
Extra
列显示using index
。 - 以索引顺序,通过读取的索引,执行全表扫描来寻找数据。
Extra
列不会显示using index
- 如果查询的索引是覆盖索引,则只有索引树被扫描(速度很快)。
all
:执行全表扫描。应尽量避免这种类型
key
-
mysql
实际决定使用的key
- 有可能不会出现在
possible_keys
的列表中
ref
ref
指明了和key
比较的是那一列或者常量
create table table_0(id_0 bigint primary key, v_0 int, index(v_0));
insert into table_0(id_0, v_0) values (0, 1), (1, 2);
explain SELECT * from table_0 where table_0.id_0 = 1
--ref列,值为 const
explain SELECT * from table_0 where id_0 = v_0
--ref列,值为 v_0
rows
-
mysql
认为执行查询必须检查的行数 - 对于
Innodb
,这个值是一个估算值,并不准确
filtered
- 根据查询条件,未过滤的行数的百分比
- 100表示未进行过滤
- 此值是一个预估值