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用法详解
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 875
当记录条数过多时会自动出现滚轮，数据不会超出紫框。
MySql 主从同步会不会影响到SQL执行速度？
weixin_44892327的博客
07-24 827
作用：负责将主库的二进制日志（binary log）数据传输到从库。工作方式：当从库连接到主库时，主库会为每个连接的从库开启一个 Binlog Dump 线程。这个线程从主库的二进制日志中读取日志记录，并将其发送给从库。Binlog Dump 线程：将二进制日志传输给从库。Binlog Dump GTID 线程（在启用 GTID 模式时）：处理 GTID 相关的复制任务。
《MySQL DBA 修炼之道》第四章 开发进阶
Trishia的博客
07-25 616
博主学习《MySQL DBA 修炼之道》结合自身理解的总结。主要讲解了范式、反范式、慢SQL查询、事务和锁等。
测试环境搭建整套大数据系统（十七：mysql同步，字段类型映射错误问题）
weixin_43446246的博客
07-24 444
mysql表hive表其中 type是字符串类型，但是yarn上一直报错number这个类是往int类型转才会使用到的。
AutoMySQLBackup execution.. Backup failed Docker部署mysql 自动备份失败！！
童龙辉的博客
07-25 779
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 596
CREATE EVENT语句在 MySQL 中用于创建一个事件调度器（Event Scheduler）的事件。事件调度器允许你安排数据库任务（如查询、数据更新等）在将来的某个时间点自动执行，或者根据特定的时间间隔重复执行。这对于需要定期执行的任务特别有用，比如数据清理、报表生成等。
[Mysql-DML数据操作语句]
2301_78794952的博客
07-23 1082
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 702
InnoDB 是MySQL数据库管理系统的一个存储引擎，它为MySQL提供了事务处理、外键约束和并发控制等关系型数据库管理系统（RDBMS）中重要的功能。以下是关于InnoDB存储引擎的一些关键特点和特性：
mysql的存储过程：
m0_71178834的博客
07-25 379
完成特点功能的sql语句的集合。把定义好的sql集合在一个特定的sql的函数当中每次执行调用函数即可，还可以实现传参的调用。
Mysql中DML的几种操作
最新发布
m0_63227888的博客
07-25 360
DML（Data Manipulation Language，数据操纵语言）是SQL中用于添加、删除、更新和查询数据库记录的一类语句。
Mysql的索引
2401_84592402的博客
07-24 502
索引是一个排序的列表，在列表当中存储索引的值以及索引对应数据所在的物理行索引值和数据是一个映射关系。
mysql的主从复制和读写分离
QChestnut的博客
07-25 763
2、中间层代理，在客户端和主从架构之间有一个代理服务器，代理服务器收到客户端的请求之后通过客户端的sql语句来进行判断，读转发到从，写转发到主。异步模式，主库在更新完事务之后会立即把结果返回给从服务器，并不关心从库是否接收到，以及从库是否处理成功。主库在更新完事务之后，立即把结果返回到从库，所有的从库执行完毕之后才能继续下一个同步。实现是基于mysql二进制日志，根据主库的二进制文件的标志位，实现主和从的分布。每次提交事务将二进制日志的内容保存在磁盘，确保日志的持久性，提高了安全性。查看position。
mysqlbackup与xtrabackup的对比
cc20100608的专栏
07-24 336
在MySQL数据库管理中，备份是确保数据安全性和可恢复性的重要环节。在众多备份工具中，mysqlbackup和xtrabackup是两款备受关注的工具。本文将围绕这两款工具进行详细对比，以探讨它们的优缺点，并试图给出在何种场景下更适合使用哪款工具。
MySQL第五次作业
2201_75988692的博客
07-25 855
建立触发器，订单表中增加订单数量后，商品表商品数量同步减少对应的商品订单出数量,并测试。创建一个存储过程s3，有2个参数，传入部门号，返回该部门的平均工资。·建立触发器，实现功能:客户取消订单，恢复商品表对应商品的数量。建立触发器，实现功能:客户修改订单，商品表对应商品数量同步更新。·建立两个表:goods(商品表)、orders(订单表)('B0001'，'小楷本'，2.8，210)，('A0001','橡皮'，2.5，100)('C0001'，铅笔'，1.2，120)在商品表中导入商品记录。
MySQL：增删改查、临时表、授权相关示例
变成我......
07-24 878
Linux系统下MySQL服务的增删改查、临时表、授权相关实例，以及相关概念的讲解
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

打赏作者

人生逆旅我亦行人

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

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

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

打赏作者

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

抵扣说明：

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

余额充值