MySQL explain你掌握了吗？

最新推荐文章于 2024-07-25 23:12:48 发布
最新推荐文章于 2024-07-25 23:12:48 发布
阅读量2.4k 收藏 3
点赞数 2
分类专栏： MySQL 文章标签： mysql 数据库 explain java 后端
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/WODESHENNI/article/details/120115822
版权

文章目录

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

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
UNIONNone在UNION后面的SELECT语句
DEPENDENT UNIONdependent (true)UNION依赖外层的查询
UNION RESULTunion_result从UNION获取结果的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
    评论
专栏目录
mysql explain update,MySQL中explain命令详解
weixin_39736650的博客
03-17 832
MySQL中的explain命令显示了mysql如何使用索引来处理select语句以及连接表。explain显示的信息可以帮助选择更好的索引和写出更优化的查询语句。MySQL中explain命令的使用其实很简单，只要explain后边跟着SQL语句就行了。例如：explain select * from tb_student;好奇的同学可能要问了，MySQL中的explain命令有什么特别之处吗？...
MySQL，你就只会写sql？
qq_38715542的博客
05-24 224
MySQL的执行流程 很多人使用MySQL，对数据库进行操作，都只是停留在，创建数据库，执行CRUD，完成数据，之后根据业务对数据进行操作，insert就多一条数据，update就修改一条数据，delete就删除了一条数据，但是大部分人都不知道，为什么执行了这些语句就能实现这功能，MySQL内部是怎么实现这一个过程的。希望这篇文章能让你有个初步的认识，能让你对MySQL感兴趣。 网络连接由线程来处理 我们知道MySQL驱动，通过网络连接，我们能访问数据库，写数据库请求，但是谁负责从连接中监听网络请求呢？谁负
Mysql的explain，你真的会用吗？
03-24 3054
数据库性能优化是每个后端程序猿必备的基础技能之一，而Mysql中的explain堪称Mysql的性能优化分析神器，我们可以通过它来分析SQL语句的对应的执行计划在Mysql底层到底是如何执行的，它对于我们评估SQL的执行效率以及确定Mysql的性能优化方向具有重要的意义。但是很多同学对于如何根据explain对已有SQL进行深度的执行分析还是丈二和尚摸不着头脑，因此本文详细阐述通过explain分析定位数据库性能问题。
你真的了解 MySQL 数据库的运行状况吗？
wangpeng198688的专栏
01-06 1650
2015年第三方市场调查机构 Evans 数据公司最近公布的一系列客户调查数据显示，在过去两年里，MySQL 在所有开发者使用的数据库中获得了25%的市场份额，Evans 公司的本次调查显示，数据库的使用者中有40%是开发人员，而两年前这一数据是32%。此外 MySQL 越来越被企业级所接受，如今数据日益膨胀，应用越来越广泛，随之而来的 MySQL 性能分析，监控告警，集成可视化的讨论也越来越多了，
mysql中explain用法详解
09-10
总之，掌握`EXPLAIN`的使用是提升MySQL查询性能的关键步骤，它让我们能够深入理解查询的执行过程，为数据库优化提供有力的支持。在开发和维护数据库系统时，定期使用`EXPLAIN`分析复杂查询，可以有效避免不必要的...
mysql面试题有哪些?
06-27
MySQL 是一个广泛使用的开源...掌握这些知识点对于理解和解决实际工作中遇到的问题至关重要，也是评估一个数据库管理员或开发人员能力的重要标准。在面试中，深入理解并能灵活应用这些概念，将有助于展现你的专业技能。
看懂MySQL的SQL EXPLAIN
01-05
### MySQL的SQL EXPLAIN详解 ...通过上述分析，我们已经掌握了`EXPLAIN`的基本使用方法及其输出项的含义。在日常开发工作中，我们应该充分利用这些知识来提高数据库查询效率，进而提升整个应用程序的性能。
Mysql之EXPLAIN显示using filesort介绍
12-15
MySQL中的`EXPLAIN`语句是数据库管理员和开发人员用于分析和优化SQL查询的重要工具。它可以帮助理解查询的执行过程，包括数据是如何被检索、如何排序以及如何进行连接操作的。`EXPLAIN`的主要目的是揭示MySQL如何...
web小项目-曼波生日录(Servlet+JSP+MySQL)
m0_75138009的博客
07-24 872
当记录条数过多时会自动出现滚轮，数据不会超出紫框。
MySql 主从同步会不会影响到SQL执行速度？
weixin_44892327的博客
07-24 826
作用：负责将主库的二进制日志（binary log）数据传输到从库。工作方式：当从库连接到主库时，主库会为每个连接的从库开启一个 Binlog Dump 线程。这个线程从主库的二进制日志中读取日志记录，并将其发送给从库。Binlog Dump 线程：将二进制日志传输给从库。Binlog Dump GTID 线程（在启用 GTID 模式时）：处理 GTID 相关的复制任务。
《MySQL DBA 修炼之道》第四章 开发进阶
Trishia的博客
07-25 612
博主学习《MySQL DBA 修炼之道》结合自身理解的总结。主要讲解了范式、反范式、慢SQL查询、事务和锁等。
测试环境搭建整套大数据系统（十七：mysql同步，字段类型映射错误问题）
weixin_43446246的博客
07-24 441
mysql表hive表其中 type是字符串类型，但是yarn上一直报错number这个类是往int类型转才会使用到的。
AutoMySQLBackup execution.. Backup failed Docker部署mysql 自动备份失败！！
童龙辉的博客
07-25 774
Using a password on the command line interface can be insecure. mysqldump: [Warning] Using a password on the command line interface can be insecure. mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-o
MySQL中的CREATE EVENT 语句详解
u011565038的博客
07-24 593
CREATE EVENT语句在 MySQL 中用于创建一个事件调度器（Event Scheduler）的事件。事件调度器允许你安排数据库任务（如查询、数据更新等）在将来的某个时间点自动执行，或者根据特定的时间间隔重复执行。这对于需要定期执行的任务特别有用，比如数据清理、报表生成等。
[Mysql-DML数据操作语句]
2301_78794952的博客
07-23 1081
insert into student (sid,sname,birthday,ssex,classid) values(9,'张三','2007-1-1','男',1);insert into student(sname,ssex) values('某某1','男'),('某某2','女'),('某某3','男');insert into student values(9,'张三','2007-1-1','男',1);-- 将姓名为某某的生日改为1888-1-1。
详解Mysql InnoDB引擎 04
程序员进阶之路
07-24 700
InnoDB 是MySQL数据库管理系统的一个存储引擎，它为MySQL提供了事务处理、外键约束和并发控制等关系型数据库管理系统（RDBMS）中重要的功能。以下是关于InnoDB存储引擎的一些关键特点和特性：
mysql的存储过程：
m0_71178834的博客
07-25 378
完成特点功能的sql语句的集合。把定义好的sql集合在一个特定的sql的函数当中每次执行调用函数即可，还可以实现传参的调用。
Mysql中DML的几种操作
最新发布
m0_63227888的博客
07-25 359
DML（Data Manipulation Language，数据操纵语言）是SQL中用于添加、删除、更新和查询数据库记录的一类语句。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

打赏作者

人生逆旅我亦行人

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

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

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

打赏作者

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

抵扣说明：

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

余额充值