MySQL explain你掌握了吗?

1. 前言

关于EXPLAIN想必每位同学都有使用过,我们可以通过它来评估一条SQL的效率,通过查看输出列,进而有针对性的对慢SQL进行优化。它是我们必须要掌握的“利器”,本文将对EXPLAIN相关知识进行梳理,做到查漏补遗。

版本

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.25    |
+-----------+
1 row in set (0.00 sec)

2. 语法

EXPLAIN可以作用在SELECTDELETEINSERTREPLACEUPDATE语句上。

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一起执行时,会显示来自于优化器的执行计划信息。

ColumnJSON NameMeaning
idselect_id标识符
select_typeNone类型
tabletable_name输出行对应表
partitionspartitions匹配的分区
typeaccess_type访问类型
possible_keyspossible_keys可能选择的索引
keykey实际选择的索引
key_lenkey_length索引长度(越短越好)
refref与索引比较的列
rowsrows预估检查的行数(越少越好)
filteredfiltered按表条件过滤的行百分比
ExtraNone附加信息

3.1 id

主要用于标识语句执行顺序。

执行顺序规则

  1. id相同:则按照从上到下的顺序执行;
  2. id不同:根据ID值,按照从大到小的顺序执行;
  3. 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

ValueJSON NameMeaning
SIMPLENone简单查询(不使用 UNION或子查询)
PRIMARYNone复杂查询中,最外面的那个SELECT
UNIONNoneUNION后面的SELECT语句
DEPENDENT UNIONdependent (true)UNION依赖外层的查询
UNION RESULTunion_resultUNION获取结果的SELECT
SUBQUERYNone子查询
DEPENDENT SUBQUERYdependent (true)子查询依赖于外部查询
DERIVEDNone派生表
MATERIALIZEDmaterialized_from_subquery物化子查询
UNCACHEABLE SUBQUERYcacheable (false)SUBQUERY查询结果不能被缓存
UNCACHEABLE UNIONcacheable (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

输出行所引用的表名称或指定的别名。

也会出现以下值:

  1. <union M,N>:该行是指id为M与N的并集。
  2. <derived N>:该行是指引用id为N的派生表。
  3. <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 HAVINGHAVING子句始终为 false
Impossible WHEREWHERE子句始终为 false
No matching min/max row没有行满足查询条件
no matching row in const tableJOIN的查询,存在空表或存在未满足唯一索引条件的行的表
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 filesortMySQL 必须执行额外的检查来找出如何按排序顺序检索行。
Using index查询时覆盖索引,无需回表查询
Using index condition使用了索引,需要回表查询
Using temporaryMySQL 需要创建一个临时表来保存结果。
Using where使用where进行条件过滤
Zero limit存在一个LIMIT 0的子句

总结

工欲善其事,必先利其器,EXPLAIN的使用是我们必须要掌握的利器,本文仅是对EXPLAIN的简单介绍,以后会针对关键指标逐一介绍。

参考文档

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

  • 2
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

人生逆旅我亦行人

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值