文章目录
1. 前言
关于
EXPLAIN
想必每位同学都有使用过,我们可以通过它来评估一条SQL的效率,通过查看输出列,进而有针对性的对慢SQL进行优化。它是我们必须要掌握的“利器”,本文将对EXPLAIN
相关知识进行梳理,做到查漏补遗。
版本
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.25 |
+-----------+
1 row in set (0.00 sec)
2. 语法
EXPLAIN
可以作用在SELECT
、DELETE
、INSERT
、REPLACE
和UPDATE
语句上。
EXPLAIN SELECT FROM tableName WHERE condition;
-- JSON格式化输出
EXPLAIN FORMAT=JSON SELECT FROM tableName WHERE condition;
示例:
mysql> EXPLAIN SELECT * FROM person;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | person | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN FORMAT=JSON SELECT * FROM person;
+------------------------------------------------------------------------------------------------------------------
| EXPLAIN
+------------------------------------------------------------------------------------------------------------------
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1.20"
},
"table": {
"table_name": "person",
"access_type": "ALL",
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "1.00",
"eval_cost": "0.20",
"prefix_cost": "1.20",
"data_read_per_join": "776"
},
"used_columns": [
"id",
"name"
]
}
}
} |
+------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)
3. EXPLAIN 输出列的含义
当执行语句与
EXPLAIN
一起执行时,会显示来自于优化器的执行计划信息。
Column | JSON Name | Meaning |
---|---|---|
id | select_id | 标识符 |
select_type | None | 类型 |
table | table_name | 输出行对应表 |
partitions | partitions | 匹配的分区 |
type | access_type | 访问类型 |
possible_keys | possible_keys | 可能选择的索引 |
key | key | 实际选择的索引 |
key_len | key_length | 索引长度(越短越好) |
ref | ref | 与索引比较的列 |
rows | rows | 预估检查的行数(越少越好) |
filtered | filtered | 按表条件过滤的行百分比 |
Extra | None | 附加信息 |
3.1 id
主要用于标识语句执行顺序。
执行顺序规则
- id相同:则按照
从上到下
的顺序执行; - id不同:根据ID值,按照
从大到小
的顺序执行; - id相同与不同并存:不同则按照从大到小的顺序执行,相同则按照从上到下的顺序执行;
示例
--ID相同
mysql> EXPLAIN SELECT * FROM test.person p JOIN test.person_info i ON p.id = i.person_id JOIN test.person_info_desc d ON i.id = d.person_info_id;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | i | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | Using where |
| 1 | SIMPLE | p | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.i.person_id | 1 | 100.00 | NULL |
| 1 | SIMPLE | d | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+----------------------------------------------------+
3 rows in set, 1 warning (0.03 sec)
-- ID不同
mysql> EXPLAIN SELECT * FROM person WHERE id = ( SELECT person_id FROM person_info WHERE id = ( SELECT person_info_id FROM person_info_desc WHERE id = 1 ) );
+----+-------------+------------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | PRIMARY | person | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 2 | SUBQUERY | person_info | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 3 | SUBQUERY | person_info_desc | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+------------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
3 rows in set, 1 warning (0.00 sec)
-- id相同与不同并存
mysql> EXPLAIN SELECT * FROM person WHERE id = ( SELECT person_id FROM person_info WHERE id IN ( SELECT person_info_id FROM person_info_desc WHERE id =1 ) );
+----+-------------+------------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | PRIMARY | person | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 2 | SUBQUERY | person_info_desc | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 2 | SUBQUERY | person_info | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+------------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
3 rows in set, 1 warning (0.00 sec)
3.2 select_type
Value | JSON Name | Meaning |
---|---|---|
SIMPLE | None | 简单查询(不使用 UNION 或子查询) |
PRIMARY | None | 复杂查询中,最外面的那个SELECT |
UNION | None | 在UNION 后面的SELECT 语句 |
DEPENDENT UNION | dependent (true ) | UNION 依赖外层的查询 |
UNION RESULT | union_result | 从UNION 获取结果的SELECT |
SUBQUERY | None | 子查询 |
DEPENDENT SUBQUERY | dependent (true ) | 子查询依赖于外部查询 |
DERIVED | None | 派生表 |
MATERIALIZED | materialized_from_subquery | 物化子查询 |
UNCACHEABLE SUBQUERY | cacheable (false ) | SUBQUERY 查询结果不能被缓存 |
UNCACHEABLE UNION | cacheable (false ) | UNION 查询结果不能被缓存 |
3.2.1 SIMPLE
SIMPLE
:简单查询,不包含不使用UNION或子查询。
mysql> EXPLAIN SELECT * FROM PERSON;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | PERSON | NULL | ALL | NULL | NULL | NULL | NULL | 1383 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
3.2.2 PRIMARY/SUBQUERY
PRIMARY
:最外层的SELECT被被标记为PRIMARY
;
SUBQUERY
:子查询。
mysql> EXPLAIN SELECT * FROM PERSON WHERE ID NOT IN(SELECT PERSON_ID FROM PERSON_INFO);
+----+-------------+-------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | PRIMARY | PERSON | NULL | ALL | NULL | NULL | NULL | NULL | 24561 | 100.00 | Using where |
| 2 | SUBQUERY | PERSON_INFO | NULL | ALL | NULL | NULL | NULL | NULL | 24830 | 100.00 | NULL |
+----+-------------+-------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
3.2.3 UNION/UNION RESULT
UNION
:在UNION后面的SELECT语句;
UNION RESULT
:从UNION获取结果的SELECT,不依赖于外部查询结果集。
mysql> EXPLAIN SELECT * FROM PERSON WHERE ID BETWEEN 0 AND 20 UNION SELECT * FROM PERSON WHERE ID BETWEEN 21 AND 30;
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | PERSON | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 20 | 100.00 | Using where |
| 2 | UNION | PERSON | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 10 | 100.00 | Using where |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)
3.2.4 DEPENDENT UNION
DEPENDENT UNION
:内层的SELECT语句依赖于外层查询。
简单来说就是SQL中的UNION
语句的查询次数等于外部查询行数,将导致SQL执行效率极差
!
mysql> EXPLAIN SELECT * FROM PERSON WHERE ID IN (SELECT ID FROM PERSON WHERE ID BETWEEN 0 AND 20 UNION SELECT ID FROM PERSON WHERE ID BETWEEN 21 AND 30);
+----+--------------------+------------+------------+--------+---------------+---------+---------+------+-------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+------------+------------+--------+---------------+---------+---------+------+-------+----------+--------------------------+
| 1 | PRIMARY | PERSON | NULL | ALL | NULL | NULL | NULL | NULL | 30511 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | PERSON | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using where; Using index |
| 3 | DEPENDENT UNION | PERSON | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using where; Using index |
| NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------------+------------+------------+--------+---------------+---------+---------+------+-------+----------+--------------------------+
4 rows in set, 1 warning (0.00 sec)
3.2.5 DEPENDENT SUBQUERY
DEPENDENT SUBQUERY
:子查询依赖于外部查询。
与DEPENDENT UNION
一样,SQL语句中的子查询的查询次数等于外部查询行数的次数,同样的这 将导致SQL执行效率极差
!
mysql> EXPLAIN SELECT id FROM person p WHERE id = ( SELECT MAX(person_id) FROM person_info info WHERE p.id = info.person_id);
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+--------------------------+
| 1 | PRIMARY | p | NULL | index | NULL | PRIMARY | 4 | NULL | 54758 | 100.00 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | info | NULL | ALL | NULL | NULL | NULL | NULL | 54729 | 10.00 | Using where |
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+--------------------------+
2 rows in set, 2 warnings (0.00 sec)
3.2.6 DERIVED
派生表
,FROM子句的子查询。
mysql> EXPLAIN SELECT * FROM (SELECT ID FROM PERSON WHERE ID BETWEEN 0 AND 20 UNION SELECT ID FROM PERSON WHERE ID BETWEEN 21 AND 30) AS UN;
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 30 | 100.00 | NULL |
| 2 | DERIVED | PERSON | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 20 | 100.00 | Using where; Using index |
| 3 | UNION | PERSON | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 10 | 100.00 | Using where; Using index |
| NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
4 rows in set, 1 warning (0.00 sec)
3.2.7 MATERIALIZED
SQL执行过程中,第一次需要子查询结果时,执行子查询并将子查询的结果保存为临时表 ,后续对子查询结果集的访问将直接通过临时表获得。 与此同时,优化器还具有延迟物化子查询的能力,先通过其它条件判断子查询是否真的需要执行。物化子查询优化SQL执行的关键点在于对子查询只需要执行一次。 与之相对的执行方式是对外表的每一行都对子查询进行调用,其执行计划中的查询类型为
DEPENDENT SUBQUERY
。
mysql> EXPLAIN SELECT * FROM person WHERE id IN ( SELECT person_id FROM person_info );
+----+--------------+-------------+------------+--------+---------------+------------+---------+----------------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+------------+--------+---------------+------------+---------+----------------+-------+----------+-------------+
| 1 | SIMPLE | person | NULL | ALL | PRIMARY | NULL | NULL | NULL | 50722 | 100.00 | Using where |
| 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_key> | <auto_key> | 5 | test.person.id | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | person_info | NULL | ALL | NULL | NULL | NULL | NULL | 50693 | 100.00 | NULL |
+----+--------------+-------------+------------+--------+---------------+------------+---------+----------------+-------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)
3.2.8 UNCACHEABLE SUBQUERY
SUBQUERY
查询结果不能被缓存
mysql> EXPLAIN SELECT * FROM person WHERE id = ( SELECT person_id FROM person_info WHERE ID = @@SORT_BUFFER_SIZE );
+----+----------------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+----------------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | PRIMARY | person | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 2 | UNCACHEABLE SUBQUERY | person_info | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+----------------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.02 sec)
3.2.9 UNCACHEABLE UNION
UNION
查询结果不能被缓存。
mysql> EXPLAIN SELECT * FROM (SELECT ID FROM PERSON WHERE ID BETWEEN 0 AND 20 UNION SELECT ID FROM PERSON WHERE ID = @@SORT_BUFFER_SIZE) AS UN;
+----+-------------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 21 | 100.00 | NULL |
| 2 | DERIVED | PERSON | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 20 | 100.00 | Using where; Using index |
| 3 | UNCACHEABLE UNION | PERSON | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index |
| NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+-------------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
4 rows in set, 1 warning (0.00 sec)
3.3 table
输出行所引用的表名称或指定的别名。
也会出现以下值:
<union M,N>
:该行是指id
为M与N的并集。<derived N>
:该行是指引用id
为N的派生表。<subquery N>
:该行是指id
为N的物化子查询的结果。
3.3 partitions
匹配记录对应的分区,对于非分区表,值为NULL。
3.4 type
访问类型。
效率排序从优至差如下表所示:
system |
const |
eq_ref |
ref |
fulltext |
ref_or_null |
index_merge |
unique_subquery |
index_subquery |
range |
index |
ALL |
3.4.1 system
system
:系统表,表中仅有单行数据,是一种特殊的const
访问类型。
mysql> EXPLAIN SELECT * FROM MYSQL.PROXIES_PRIV;
+----+-------------+--------------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+--------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | PROXIES_PRIV | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+--------------+------------+--------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
3.4.2 const
使用主键或唯一索引进行查询,最多存在一个匹配行,由于仅有一行,所以
const
非常快!
mysql> EXPLAIN SELECT ID FROM PERSON WHERE ID = 1;
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | PERSON | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
3.4.3 eq_ref
JOIN
查询时,通过聚集索引(主键或唯一索引列)进行关联。
mysql> EXPLAIN SELECT * FROM PERSON P JOIN PERSON_INFO I ON P.ID = I.PERSON_ID ;
+----+-------------+-------+------------+--------+---------------------------+---------+---------+------------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------------------+---------+---------+------------------+--------+----------+-------------+
| 1 | SIMPLE | I | NULL | ALL | IDX_PERSON_INFO_PERSON_ID | NULL | NULL | NULL | 975026 | 100.00 | Using where |
| 1 | SIMPLE | P | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.I.person_id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------------------+---------+---------+------------------+--------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
3.4.4 ref
非聚集索引查询。
mysql> explain select * from person where name = '1';
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | person | NULL | ref | idx_person_name | idx_person_name | 768 | const | 1 | 100.00 | Using index |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
3.4.5 fulltext
通过
fulltext
索引进行查询。
mysql> EXPLAIN SELECT DES FROM PERSON_INFO_DESC WHERE MATCH(DES) AGAINST(1);
+----+-------------+------------------+------------+----------+----------------------+----------------------+---------+-------+------+----------+-------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------+------------+----------+----------------------+----------------------+---------+-------+------+----------+-------------------------------+
| 1 | SIMPLE | PERSON_INFO_DESC | NULL | fulltext | IDX_PERSON_INFO_DESC | IDX_PERSON_INFO_DESC | 0 | const | 1 | 100.00 | Using where; Ft_hints: sorted |
+----+-------------+------------------+------------+----------+----------------------+----------------------+---------+-------+------+----------+-------------------------------+
1 row in set, 1 warning (0.07 sec)
3.4.6 ref_or_null
对某字段进行等值查询且需要null值的查询。
mysql> EXPLAIN SELECT * FROM PERSON WHERE NAME = '1' OR NAME IS NULL;
+----+-------------+--------+------------+-------------+-----------------+-----------------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------------+-----------------+-----------------+---------+-------+------+----------+--------------------------+
| 1 | SIMPLE | PERSON | NULL | ref_or_null | idx_person_name | idx_person_name | 768 | const | 2 | 100.00 | Using where; Using index |
+----+-------------+--------+------------+-------------+-----------------+-----------------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.10 sec)
3.4.7 index_merge
索引合并优化,The Index Merge access method retrieves rows with multiple range scans and merges their results into one。
mysql> EXPLAIN SELECT * FROM PERSON WHERE NAME = '1' OR ID = 34;
+----+-------------+--------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+---------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+---------------------------------------------------+
| 1 | SIMPLE | PERSON | NULL | index_merge | PRIMARY,idx_person_name | idx_person_name,PRIMARY | 768,4 | NULL | 2 | 100.00 | Using union(idx_person_name,PRIMARY); Using where |
+----+-------------+--------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+---------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
3.4.8 range
范围性的索引扫描,查询语句通过使用=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, 或 IN()运算符进行范围查询。
mysql> EXPLAIN SELECT * FROM PERSON WHERE ID < 0;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | PERSON | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 1 | 100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
3.4.9 index
全表扫描,但它是根据查询列索引回表获取数据。
mysql> EXPLAIN SELECT * FROM PERSON;
+----+-------------+--------+------------+-------+---------------+-----------------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-----------------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | PERSON | NULL | index | NULL | idx_person_name | 768 | NULL | 1000030 | 100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+-----------------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
3.4.10 ALL
查询列不存在索引,全表扫描。
mysql> EXPLAIN SELECT NAME FROM PERSON_INFO;
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------+
| 1 | SIMPLE | PERSON_INFO | NULL | ALL | NULL | NULL | NULL | NULL | 975026 | 100.00 | NULL |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)
3.5 possible_keys
该列会列出可能使用到的索引,不代表一定使用其中的索引,NULL值,则代表查询列不存在相关索引。
3.6 key
该列代表实际使用的索引,为NULL,则代表未使用索引。
3.7 key_len
该列代表使用
key
列对应索引的长度。
3.8 ref
显示
key
对应索引被哪一列使用。
3.9 filtered
该列指按照条件过滤表数据行的百分比。
3.10 Extra
该列输出额外信息描述,该列的输出信息也是我们在编写合理SQL必看的一个依据。
比较常见描述信息 | |
---|---|
Impossible HAVING | HAVING子句始终为 false |
Impossible WHERE | WHERE子句始终为 false |
No matching min/max row | 没有行满足查询条件 |
no matching row in const table | JOIN的查询,存在空表或存在未满足唯一索引条件的行的表 |
No tables used | 查询没有FROM子句,或存在FROM DUAL子句 |
Not exists | 例如SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;在t2表中不存在id为空的情况,MYSQL不会再去扫描该表的其余关联行。 |
unique row not found | 根据主键或唯一索引未获取到匹配行 |
Using filesort | MySQL 必须执行额外的检查来找出如何按排序顺序检索行。 |
Using index | 查询时覆盖索引,无需回表查询 |
Using index condition | 使用了索引,需要回表查询 |
Using temporary | MySQL 需要创建一个临时表来保存结果。 |
Using where | 使用where进行条件过滤 |
Zero limit | 存在一个LIMIT 0的子句 |
总结
工欲善其事,必先利其器,EXPLAIN
的使用是我们必须要掌握的利器,本文仅是对EXPLAIN
的简单介绍,以后会针对关键指标逐一介绍。