关于mysql:一文读懂-MySQL-Explain-执行计划

3次阅读

共计 21709 个字符，预计需要花费 55 分钟才能阅读完成。

一、前言

上周老周的一个好敌人让我出一篇教你读懂 SQL 执行打算，和我另一位读者反馈的面试题如何排查慢 SQL 的强相干，索性先出一篇一文读懂 MySQL Explain 执行打算。Explain 执行打算你肯定得会看，不然你简历上就别去写什么你会 SQL 调优，不然面试官会感觉，Explain 执行打算你都不会看，那你还 SQL 调啥优啊？SQL 调你吧？？？开个小玩笑，玩笑归玩笑，重要是真的重要！！！

二、Explain 执行打算是什么？

什么是执行打算？简而言之，就是 SQL 在数据库中执行时的体现状况，通常用于 SQL 性能剖析、优化和加锁剖析等场景，执行过程会在 MySQL 查问过程中由解析器，预处理器和查问优化器独特生成。在 MySQL 中应用 explain 关键字来查看。

2.1 执行打算有什么用？

它能够用来剖析 SQL 语句和表构造的性能瓶颈

  • 关联查问的执行程序
  • 查问操作的操作类型
  • 哪些索引能够被命中
  • 哪些索引理论被命中
  • 每张表有多少记录参加查问
  • …

2.2 MySQL 执行过程

如上图所示，MySQL 数据库由 Server 层和 Engine 层组成：

  • Server 层有 SQL 分析器、SQL 优化器、SQL 执行器，用于负责 SQL 语句的具体执行过程；
  • Engine 层负责存储具体的数据，如最常应用的 MyISAM、InnoDB 存储引擎，还有用于在内存中存储长期后果集的 TempTable 引擎。

SQL 优化器会剖析所有可能的执行打算，抉择老本最低的执行，这种优化器称之为：CBO（Cost-based Optimizer，基于老本的优化器）。

而在 MySQL 中，一条 SQL 的计算成本计算如下所示：

Cost = Server Cost + Engine Cost
= CPU Cost + IO Cost

其中，CPU Cost 示意计算的开销，比方索引键值的比拟、记录值的比拟、后果集的排序等这些操作都在 Server 层实现；

IO Cost 示意引擎层 IO 的开销，MySQL 8.0 能够通过辨别一张表的数据是否在内存中，别离计算读取内存 IO 开销以及读取磁盘 IO 的开销。

数据库 mysql 下的表 server_cost、engine_cost 则记录了对于各种老本的计算，如：


表 server_cost 记录了 Server 层优化器各种操作的老本，这外面包含了所有 CPU Cost，其具体含意如下：

  • disk_temptable_create_cost：创立磁盘长期表的老本，默认为 20。
  • disk_temptable_row_cost：磁盘长期表中每条记录的老本，默认为 0.5。
  • key_compare_cost：索引键值比拟的老本，默认为 0.05，老本最小。
  • memory_temptable_create_cost：创立内存长期表的老本：默认为 1。
  • memory_temptable_row_cost：内存长期表中每条记录的老本，默认为 0.1。
  • row_evaluate_cost：记录间的比拟老本，默认为 0.1。

能够看到，MySQL 优化器认为如果一条 SQL 须要创立基于磁盘的长期表，则这时的老本是最大的，其老本是基于内存长期表的 20 倍。而索引键值的比拟、记录之间的比拟，其实开销是非常低的，但如果要比拟的记录数十分多，则老本会变得十分大。

而表 engine_cost 记录了存储引擎层各种操作的老本，这里蕴含了所有的 IO Cost，具体含意如下：

  • io_block_read_cost：从磁盘读取一个页的老本，默认值为 1。
  • memory_block_read_cost：从内存读取一个页的老本，默认值为 0.25。

也就是说，MySQL 优化器认为从磁盘读取的开销是内存开销的 4 倍。

三、Explain 执行打算详解

咱们先来筹备以下 SQL 脚本：

CREATE TABLE `user` (`id` INT (11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR (20) DEFAULT NULL COMMENT "用户名",
    PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = "用户表";

CREATE TABLE `user_robot_relate` (`id` INT (11) NOT NULL AUTO_INCREMENT,
    `user_id` INT (11) NOT NULL COMMENT "用户 id",
    `robot_id` INT (11) NOT NULL COMMENT "机器人 id",
    PRIMARY KEY (`id`), 
    KEY `idx_user_id` (`user_id`), 
    KEY `idx_robot_id` (`robot_id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = "用户与机器人表";

CREATE TABLE `robot` (`id` INT (11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR (20) DEFAULT NULL COMMENT "机器人名",
    PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = "机器人表";

INSERT INTO user VALUES (1, 'riemann');
INSERT INTO user VALUES (2, 'andy');

INSERT INTO user_robot_relate VALUES (1, 1, 1);
INSERT INTO user_robot_relate VALUES (2, 1, 2);
INSERT INTO user_robot_relate VALUES (3, 2, 3);

INSERT INTO robot VALUES (1, '小白鲸');
INSERT INTO robot VALUES (2, '扫地机');
INSERT INTO robot VALUES (3, '扫拖一体机');

咱们创立三张表 user、user_robot_relate、robot，表之间的关系 user.id = user_robot_relate.user_id AND user_robot_relate.robot_id = robot.id。

先来看下我的 MySQL 版本，是 5.7.37 的。

接着咱们看一下执行打算有哪些字段，先看个整体的，让大家有个大略的意识后，咱们再逐个去详解剖析。


explain 执行后输入的后果集蕴含 12 列，别离是 id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered 和 Extra，上面对这些字段进行解释。

  • id：Query Optimizer 所选定的执行打算中查问的序列号
  • select_type：显示本行是简略或简单 select。如果查问有任何简单的子查问，则最外层标记为 PRIMARY、DERIVED.、UNION、UNION RESUIT 等。
  • table：显示这一步所拜访的数据库中的表的名称
  • partitions：查问时匹配到的分区信息，对于非分区表值为 NULL，当查问的是分区表时，partitions 显示分区表命中的分区状况。
  • type：数据拜访、读取操作类型（ALL、index、range、ref、eq_ref、const、system）等
  • possible_keys：该查问能够利用的索引，如果没有任何索引能够应用，就会显示成 null，这一

项内容对于优化时候索引的调整十分重要。

  • key：MySQL Query Optimizer 从 possible_keys 中所抉择应用的索引
  • key_len：被选中应用索引的索引键长度
  • ref：列出是通过常量（const），还是某个表的某个字段（如果是 join）来过滤（通过 key）

的。

  • rows：MySQL Query Optimizer 通过零碎收集到的统计信息估算进去的后果集记录条数
  • filtered：示意存储引擎返回的数据在通过过滤后，剩下满足条件的记录数量的比例。
  • Extra：查问中每一步实现的额定细节信息，如 Using filesort、index 等。

3.1 id

看到三条记录的 id 都雷同，能够了解成这三个表为一组，具备同样的优先级，执行程序由上而下，具体程序由优化器决定。

3.1.1 id 雷同 

mysql> EXPLAIN SELECT * FROM user u WHERE u.id = (SELECT ur.user_id FROM user_robot_relate ur WHERE ur.robot_id = (SELECT r.id FROM robot r WHERE r.name = '扫地机'));
+----+-------------+-------+------------+------+--------------------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys            | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+--------------------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | ur    | NULL       | ALL  | idx_user_id,idx_robot_id | NULL | NULL    | NULL |    3 |   100.00 | NULL                                               |
|  1 | SIMPLE      | u     | NULL       | ALL  | PRIMARY                  | NULL | NULL    | NULL |    2 |    50.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | r     | NULL       | ALL  | PRIMARY                  | NULL | NULL    | NULL |    3 |    50.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+--------------------------+------+---------+------+------+----------+----------------------------------------------------+

3.1.2 id 不同

如果咱们的 SQL 中存在子查问，那么 id 的序号会递增，id 值越大优先级越高，越先被执行。当三个表顺次嵌套，发现最里层的子查问 id 最大，最先执行。

mysql> EXPLAIN SELECT * FROM user u WHERE u.id = (SELECT ur.user_id FROM user_robot_relate ur WHERE ur.robot_id = (SELECT r.id FROM robot r WHERE r.name = '扫地机'));
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | u     | NULL       | const | PRIMARY       | PRIMARY      | 4       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | ur    | NULL       | ref   | idx_robot_id  | idx_robot_id | 4       | const |    1 |   100.00 | Using where |
|  3 | SUBQUERY    | r     | NULL       | ALL   | NULL          | NULL         | NULL    | NULL  |    3 |    33.33 | Using where |
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+

3.1.3 以上两种同时存在

将上边的 SQL 略微批改一下，减少一个子查问，发现 id 的以上两种同时存在。雷同 id 划分为一组，这样就有三个组，同组的从上往下程序执行，不同组 id 值越大，优先级越高，越先执行。

mysql> EXPLAIN SELECT * FROM user u WHERE u.id = (SELECT ur.user_id FROM user_robot_relate ur WHERE ur.robot_id = (SELECT r.id FROM robot r WHERE r.name = '扫地机')) AND u.id IN (SELECT u.id FROM user u WHERE u.name = 'riemann');
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | u     | NULL       | const | PRIMARY       | PRIMARY      | 4       | const |    1 |   100.00 | NULL        |
|  1 | PRIMARY     | u     | NULL       | const | PRIMARY       | PRIMARY      | 4       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | ur    | NULL       | ref   | idx_robot_id  | idx_robot_id | 4       | const |    1 |   100.00 | Using where |
|  3 | SUBQUERY    | r     | NULL       | ALL   | NULL          | NULL         | NULL    | NULL  |    3 |    33.33 | Using where |
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+

3.2 select_type

select_type：示意 select 查问的类型，次要是用于辨别各种简单的查问，例如：一般查问、联结查问、子查问等。

  • SIMPLE：示意最简略的 select 查问语句，也就是在查问中不蕴含子查问或者 union 交并差集等操作。
  • PRIMARY：当查问语句中蕴含任何简单的子局部，最外层查问则被标记为 PRIMARY。
  • SUBQUERY：当 select 或 where 列表中蕴含了子查问，该子查问被标记为 SUBQUERY。
  • DERIVED：示意蕴含在 from 子句中的子查问的 select，在咱们的 from 列表中蕴含的子查问会被标记为 derived。
  • UNION：如果 union 后边又呈现的 select 语句，则会被标记为 union；若 union 蕴含在 from 子句的子查问中，外层 select 将被标记为 derived。

  • UNION RESULT：代表从 union 的长期表中读取数据，而 table 列的 <union1,4> 示意用第一个和第四个 select 的后果进行 union 操作。

    mysql> EXPLAIN SELECT t.user_id, (SELECT u.id FROM user u) o FROM (SELECT ur.user_id, ur.robot_id FROM user_robot_relate ur WHERE ur.id = 2) t UNION (SELECT r.id, r.name FROM robot r);
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra           |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
|  1 | PRIMARY      | ur         | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL            |
|  2 | SUBQUERY     | u          | NULL       | index | NULL          | PRIMARY | 4       | NULL  |    2 |   100.00 | Using index     |
|  4 | UNION        | r          | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |    3 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,4> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+

3.3 table

查问的表名，并不一定是实在存在的表，有别名显示别名，也可能为长期表，例如上边的 DERIVED、<union1,4> 等。

3.4 partitions

查问时匹配到的分区信息，对于非分区表值为 NULL，当查问的是分区表时，partitions 显示分区表命中的分区状况。

3.5 type

type：查问应用了何种类型，它在 SQL 优化中是一个十分重要的指标，以下性能从好到坏顺次是：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

  • system：当表仅有一行记录时（零碎表），数据量很少，往往不须要进行磁盘 IO，速度十分快。

  • const：示意查问时命中 primary key 主键或者 unique 惟一索引，或者被连贯的局部是一个常量（const）值。这类扫描效率极高，返回数据量少，速度十分快。

    mysql> EXPLAIN SELECT * FROM robot WHERE id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | robot | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

  • eq_ref：查问时命中主键 primary key 或者 unique key 索引，type 就是 eq_ref。

    mysql> EXPLAIN SELECT u.name FROM user u, user_robot_relate ur WHERE u.id = ur.id; 
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
|  1 | SIMPLE      | u     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL      |    2 |   100.00 | NULL        |
|  1 | SIMPLE      | ur    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.u.id |    1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+

  • ref：区别于 eq_ref，ref 示意应用非唯一性索引，会找到很多个符合条件的行。

    mysql> EXPLAIN SELECT id FROM user_robot_relate WHERE user_id = 2; 
+----+-------------+-------------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table             | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user_robot_relate | NULL       | ref  | idx_user_id   | idx_user_id | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+

  • ref_or_null：这种连贯类型相似于 ref，区别在于 MySQL 会额定搜寻蕴含 NULL 值的行。

    -- 为了模仿这个场景，我又新增了一个 user_test 表。mysql> CREATE TABLE `user_test` (-> `id` INT (11) NOT NULL AUTO_INCREMENT,
    -> `name` VARCHAR (20) DEFAULT NULL COMMENT "用户名",
    -> PRIMARY KEY (`id`), 
    -> KEY `idx_name` (`name`)
    -> ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = "用户测试表";
Query OK, 0 rows affected (0.01 sec)

mysql> EXPLAIN SELECT id FROM user_test WHERE name = 'riemann' OR name IS NULL; 
+----+-------------+-----------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+
| id | select_type | table     | partitions | type        | possible_keys | key      | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | user_test | NULL       | ref_or_null | idx_name      | idx_name | 83      | const |    2 |   100.00 | Using where; Using index |
+----+-------------+-----------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+

  • index_merge：应用了索引合并优化办法，查问应用了两个以上的索引。

    -- 下边示例中同时应用到主键 id 和字段 user_id 的索引。mysql> EXPLAIN SELECT * FROM user_robot_relate WHERE id > 1 AND user_id = 2; 
+----+-------------+-------------------+------------+-------------+---------------------+---------------------+---------+------+------+----------+---------------------------------------------------+
| id | select_type | table             | partitions | type        | possible_keys       | key                 | key_len | ref  | rows | filtered | Extra                                             |
+----+-------------+-------------------+------------+-------------+---------------------+---------------------+---------+------+------+----------+---------------------------------------------------+
|  1 | SIMPLE      | user_robot_relate | NULL       | index_merge | PRIMARY,idx_user_id | idx_user_id,PRIMARY | 8,4     | NULL |    1 |   100.00 | Using intersect(idx_user_id,PRIMARY); Using where |
+----+-------------+-------------------+------------+-------------+---------------------+---------------------+---------+------+------+----------+---------------------------------------------------+

  • unique_subquery：替换上面的 IN 子查问，子查问返回不反复的汇合。

    value IN (SELECT primary_key FROM single_table WHERE some_expr)

  • index_subquery：区别于 unique_subquery，用于非惟一索引，能够返回反复值。

    value IN (SELECT key_column FROM single_table WHERE some_expr)

  • range：应用索引抉择行，仅检索给定范畴内的行。简略点说就是针对一个有索引的字段，给定范畴检索数据。在 where 语句中应用 bettween…and、<、>、<=、in 等条件查问 type 都是 range。

    -- user_robot_relate 表中 id 为惟一主键，name 一般字段未建索引。mysql> EXPLAIN SELECT * FROM user_robot_relate WHERE id BETWEEN 2 AND 3;
+----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table             | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user_robot_relate | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

    从后果中看到只有对设置了索引的字段，做范畴检索 type 才是 range。

    mysql> EXPLAIN SELECT * FROM user WHERE name BETWEEN 2 AND 3;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

  • index：Index 与 ALL 其实都是读全表，区别在于 index 是遍历索引树读取，而 ALL 是从硬盘中读取。

    -- id 为主键，不带 where 条件全表查问，type 后果为 index。mysql> EXPLAIN SELECT id FROM robot;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | robot | NULL       | index | NULL          | PRIMARY | 4       | NULL |    3 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

  • ALL：将遍历全表以找到匹配的行，性能最差。

    mysql> EXPLAIN SELECT * FROM robot;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | robot | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

3.6 possible_keys

possible_keys：示意在 MySQL 中通过哪些索引，能让咱们在表中找到想要的记录，一旦查问波及到的某个字段上存在索引，则索引将被列出，但这个索引并不定一会是最终查问数据时所被用到的索引。具体请参考上边的例子。

3.7 key

key：区别于 possible_keys，key 是查问中理论应用到的索引，若没有应用索引，显示为 NULL。具体请参考上边的例子。

当 type 为 index_merge 时，可能会显示多个索引。

3.8 key_len

key_len：示意查问用到的索引长度（字节数），原则上长度越短越好。

  • 单列索引，那么须要将整个索引长度算进去；
  • 多列索引，不是所有列都能用到，须要计算查问中理论用到的列。

注：key_len 只计算 where 条件中用到的索引长度，而排序和分组即使是用到了索引，也不会计算到 key_len 中。

3.9 ref

列出是通过常量（const），还是某个表的某个字段（如果是 join）来过滤（通过 key）的。

3.10 rows

rows：以表的统计信息和索引应用状况，估算要找到咱们所需的记录，须要读取的行数。

这是评估 SQL 性能的一个比拟重要的数据，MySQL 须要扫描的行数，很直观的显示 SQL 性能的好坏，个别状况下 rows 值越小越好。

3.11 filtered

filtered 这个是一个百分比的值，表里符合条件的记录数的百分比。简略点说，这个字段示意存储引擎返回的数据在通过过滤后，剩下满足条件的记录数量的比例。

在 MySQL.5.7 版本以前想要显示 filtered 须要应用 explain extended 命令。MySQL.5.7 后，默认 explain 间接显示 partitions 和 filtered 的信息。

3.12 Extra

Extra：不适宜在其余列中显示的信息，Explain 中的很多额定的信息会在 Extra 字段显示。

3.12.1 Using index

Using index：咱们在相应的 select 操作中应用了笼罩索引，艰深一点讲就是查问的列被索引笼罩，应用到笼罩索引查问速度会十分快，SQL 优化中现实的状态。

什么又是笼罩索引?

一条 SQL 只须要通过索引就能够返回，咱们所须要查问的数据（一个或几个字段），而不用通过二级索引，查到主键之后再通过主键查问整行数据（SELECT *）。

id 为 user 表的主键 

mysql> EXPLAIN SELECT id FROM user; 
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | PRIMARY | 4       | NULL |    2 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

留神：想要应用到笼罩索引，咱们在 select 时只取出须要的字段，不可 SELECT *，而且该字段建了索引。

mysql> EXPLAIN SELECT * FROM user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

3.12.2 Using where

Using where：查问时未找到可用的索引，进而通过 where 条件过滤获取所需数据，但要留神的是并不是所有带 where 语句的查问都会显示 Using where。

下边示例 name 并未用到索引，type 为 ALL，即 MySQL 通过全表扫描后再按 where 条件筛选数据。

mysql> EXPLAIN SELECT name FROM user WHERE name = 'riemann';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

3.12.3 Using temporary

Using temporary：示意查问后后果须要应用长期表来存储，个别在排序或者分组查问时用到。

mysql> EXPLAIN SELECT name FROM user WHERE id IN (1, 2) GROUP BY name;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                        |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | user  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------+

3.12.4 Using filesort

Using filesort：示意无奈利用索引实现的排序操作，也就是 ORDER BY 的字段没有索引，通常这样的 SQL 都是须要优化的。

mysql> EXPLAIN SELECT id FROM user ORDER BY name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

如果 ORDER BY 字段有索引就会用到笼罩索引，相比执行速度快很多。

mysql> EXPLAIN SELECT id FROM user ORDER BY id;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | PRIMARY | 4       | NULL |    2 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

3.12.5 Using join buffer

Using join buffer：在咱们联表查问的时候，如果表的连贯条件没有用到索引，须要有一个连贯缓冲区来存储两头后果。

mysql> EXPLAIN SELECT u.name FROM user u, user_test t WHERE u.name = t.name;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t     | NULL       | index | idx_name      | idx_name | 83      | NULL |    1 |   100.00 | Using index                                        |
|  1 | SIMPLE      | u     | NULL       | ALL   | NULL          | NULL     | NULL    | NULL |    2 |    50.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+

3.12.6 Impossible where

Impossible where：示意在咱们用不太正确的 where 语句，导致没有符合条件的行。

mysql> EXPLAIN SELECT name FROM user WHERE 1=2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+

3.12.7 No tables used

No tables used：咱们的查问语句中没有 FROM 子句，或者有 FROM DUAL 子句。

mysql> EXPLAIN SELECT now();
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
正文完
mysql
发表至： mysql
2022-04-05
 0
关于mysql:我身边的高T问了Java面试者这样的问题
关于mysql:MySQL-索引类型索引结构索引的操作
关于mysql:数据库运维实操优质文章分享含OracleMySQL等-2023年5月刊
关于mysql:Mysql专栏-mysqlinnodb存储引擎binlog的工作流程

站内搜索

-「」