AI 助理
备案控制台
开发者社区 数据库 文章 正文

MySQL - Explain深度剖析

2024-01-02 66
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介： MySQL - Explain深度剖析

生猛干货

带你搞定MySQL实战，轻松对应海量业务处理及高并发需求，从容应对大场面试

官方文档

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

Explain介绍

使用EXPLAIN关键字可以模拟优化器执行SQL语句，分查询语句或是结构的性能瓶颈

在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL。

如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中 。

测试数据

DB Version

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.29-log |
+------------+
1 row in set (0.00 sec)
mysql> 
DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (
 `id` int(11) NOT NULL,
 `name` varchar(45) DEFAULT NULL,
 `update_time` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017-12-22 15:27:18'), (2,'b','2017-12-22 15:27:18'), (3,'c','2017-12-22 15:27:18');
###############################
DROP TABLE IF EXISTS `film`;
CREATE TABLE `film` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(10) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');
###############################
DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor` (
 `id` int(11) NOT NULL,
 `film_id` int(11) NOT NULL,
 `actor_id` int(11) NOT NULL,
 `remark` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);

explain 使用

explain 两个扩展的使用

explain extended: 提供： 额外一些查询优化的信息 （‘EXTENDED’ is deprecated and will be removed in a future release.）

mysql> explain extended select * from film where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | film  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)
#   2 warnings
# 可以通过 show warnings 命令查看
mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------+
| Level   | Code | Message                                                                          |
+---------+------+----------------------------------------------------------------------------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release.                |
| Note    | 1003 | /* select#1 */ select '1' AS `id`,'film1' AS `name` from `dbtest`.`film` where 1 |
+---------+------+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>

filtered 列： 百分比，计算公式 rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表） ， 供参考

第二个 ‘PARTITIONS’ is deprecated and will be removed in a future

mysql> explain partitions select * from film where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | film  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------+
| Level   | Code | Message                                                                          |
+---------+------+----------------------------------------------------------------------------------+
| Warning | 1681 | 'PARTITIONS' is deprecated and will be removed in a future release.              |
| Note    | 1003 | /* select#1 */ select '1' AS `id`,'film1' AS `name` from `dbtest`.`film` where 1 |
+---------+------+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>

所以只使用explain就足够了 。

explain重要列说明

mysql> explain select * from film_actor a where a.actor_id  = (select id from actor where name = 'a');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
|  2 | SUBQUERY    | actor | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

id

id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。

执行顺序：

id越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行

select_type

表示的对应行是简单还是复杂的查询

simple

简单查询，查询不包含子查询和union

mysql> explain select * from film where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | film  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql>

primary

复杂查询中最外层的 select

mysql> explain select * from film_actor a where a.actor_id  = (select id from actor where name = 'a');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
|  2 | SUBQUERY    | actor | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

subquery

包含在 select 中的子查询（不在 from 子句中）

mysql> explain select * from film_actor a where a.actor_id  = (select id from actor where name = 'a');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
|  2 | SUBQUERY    | actor | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

derived

包含在 from 子句中的子查询。

MySQL会将结果存放在一个临时表中，也称为派生表

derived: 衍生的

mysql> set session optimizer_switch='derived_merge=off'; #关闭mysql5.7新特性对衍生表的合并优化
Query OK, 0 rows affected (0.00 sec)
mysql> explain select (select 1 from  actor where id = 1 ) from (select * from film where id =1 ) t ;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | <derived3> | NULL       | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL        |
|  3 | DERIVED     | film       | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | actor      | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)
mysql>

union

在 union 中的第二个和随后的 select

mysql> EXPLAIN select 1 union select 1 ;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | NULL       | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used  |
|  2 | UNION        | NULL       | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used  |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)
mysql>

table

表示 explain 的一行正在访问哪个表

mysql> explain select (select 1 from  actor where id = 1 ) from (select * from film where id =1 ) t ;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | <derived3> | NULL       | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL        |
|  3 | DERIVED     | film       | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | actor      | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

当 from 子句中有子查询时，table列是 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。

mysql> EXPLAIN select 1 union select 1 ;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | NULL       | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used  |
|  2 | UNION        | NULL       | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used  |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)
mysql>

当有 union 时，UNION RESULT 的 table 列的值为，1和2表示参与 union 的 select 行id。

type

表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。

依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL

一般来说，得保证查询达到range级别，最好达到ref

NULL

mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引.

例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

mysql> explain select min(id) from actor;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 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 | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

const, system

mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。

system是const的特例，表里只有一条元组匹配时为system.

mysql> EXPLAIN select * from (select * from film where id=1) t ;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL  |
|  2 | DERIVED     | film       | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+---------------------------------------------------------------+
| Level | Code | Message                                                       |
+-------+------+---------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select '1' AS `id`,'film1' AS `name` from dual |
+-------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>

eq_ref

primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

mysql> EXPLAIN select * from film_actor a  left join film b on a.film_id = b.id ;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
|  1 | SIMPLE      | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL             |    3 |   100.00 | NULL  |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | dbtest.a.film_id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                   |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `dbtest`.`a`.`id` AS `id`,`dbtest`.`a`.`film_id` AS `film_id`,`dbtest`.`a`.`actor_id` AS `actor_id`,`dbtest`.`a`.`remark` AS `remark`,`dbtest`.`b`.`id` AS `id`,`dbtest`.`b`.`name` AS `name` from `dbtest`.`film_actor` `a` left join `dbtest`.`film` `b` on((`dbtest`.`b`.`id` = `dbtest`.`a`.`film_id`)) where 1 |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>

ref

相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行.

【 简单 select 查询，name是普通索引（非唯一索引）】

mysql> show INDEX  from  film ;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| film  |          0 | PRIMARY  |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| film  |          1 | idx_name |            1 | name        | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> 
mysql> EXPLAIN select * from film a where a.name = 'film0';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | ref  | idx_name      | idx_name | 33      | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql>

【关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分。】

mysql> show index from film_actor;
+------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| film_actor |          0 | PRIMARY           |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| film_actor |          1 | idx_film_actor_id |            1 | film_id     | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| film_actor |          1 | idx_film_actor_id |            2 | actor_id    | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
mysql> 
mysql> EXPLAIN select film_id from film  left join  film_actor  on film.id =film_actor.film_id ;
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+----------------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys     | key               | key_len | ref            | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+----------------+------+----------+-------------+
|  1 | SIMPLE      | film       | NULL       | index | NULL              | idx_name          | 33      | NULL           |    3 |   100.00 | Using index |
|  1 | SIMPLE      | film_actor | NULL       | ref   | idx_film_actor_id | idx_film_actor_id | 4       | dbtest.film.id |    1 |   100.00 | Using index |
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+----------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
mysql>

range

范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

mysql> explain select * from actor  where id > 1 ;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | actor | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

index

扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些

mysql> explain select * from film;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | film  | NULL       | index | NULL          | idx_name | 33      | NULL |    3 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

ALL

全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了

mysql> explain select * from actor ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | actor | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

possible_keys

显示查询可能使用哪些索引来查找

explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

key

mysql实际采用哪个索引来优化对该表的访问。

如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

key_len

显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

举个例子 ：

film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4字节。

mysql> explain select * from film_actor where film_id=1;
+----+-------------+------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | film_actor | NULL       | ref  | idx_film_actor_id | idx_film_actor_id | 4       | const |    2 |   100.00 | NULL  |
+----+-------------+------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql>

通过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。

key_len计算规则

【字符串】

  • char(n)：n字节长度
  • varchar(n)：如果是utf-8，则长度 3n + 2 字节，加的2字节用来存储字符串长度

【数值类型】

  • tinyint：1字节
  • smallint：2字节
  • int：4字节
  • bigint：8字节

【时间类型】

  • date：3字节
  • timestamp：4字节
  • datetime：8字节

如果字段允许为 NULL，需要1字节记录是否为 NULL

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引

ref

显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）

mysql> explain select * from film_actor where film_id=1;
+----+-------------+------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | film_actor | NULL       | ref  | idx_film_actor_id | idx_film_actor_id | 4       | const |    2 |   100.00 | NULL  |
+----+-------------+------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN select film_id from film  left join  film_actor  on film.id =film_actor.film_id ;
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+----------------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys     | key               | key_len | ref            | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+----------------+------+----------+-------------+
|  1 | SIMPLE      | film       | NULL       | index | NULL              | idx_name          | 33      | NULL           |    3 |   100.00 | Using index |
|  1 | SIMPLE      | film_actor | NULL       | ref   | idx_film_actor_id | idx_film_actor_id | 4       | dbtest.film.id |    1 |   100.00 | Using index |
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+----------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

rows

mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

Extra

展示的是额外信息。

列举几个常见的值

Using index

使用覆盖索引 ： 无需回表

mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；

覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值

mysql> explain select film_id from film_actor where film_id=1;
+----+-------------+------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | film_actor | NULL       | ref  | idx_film_actor_id | idx_film_actor_id | 4       | const |    2 |   100.00 | Using index |
+----+-------------+------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql>

Using where

使用 where 语句来处理结果，并且查询的列未被索引覆盖

mysql> show index from actor;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| actor |          0 | PRIMARY  |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> explain select * from actor where name = 'a';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | actor | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql>

Using index condition

查询的列不完全被索引覆盖，where条件中是一个前导列的范围；

mysql> explain select * from film_actor where film_id > 1 ;
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
| id | select_type | table      | partitions | type  | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | film_actor | NULL       | range | idx_film_actor_id | idx_film_actor_id | 4       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql>

Using temporary

mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。

【actor.name没有索引，此时创建了张临时表来distinct】

mysql> explain select distinct name from actor;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | actor | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)

【film.name建立了idx_name索引，此时查询时extra是using index,没有用临时表】

mysql> explain select distinct name from film;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | film  | NULL       | index | idx_name      | idx_name | 33      | NULL |    3 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql>

Using filesort

将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的

【 actor.name未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录】

mysql> explain select * from actor order by name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | actor | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
mysql>

【film.name建立了idx_name索引,此时查询时extra是using index】

mysql> explain select * from film order by name ;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | film  | NULL       | index | NULL          | idx_name | 33      | NULL |    3 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql>

Select tables optimized away

使用某些聚合函数（比如 max、min）来访问存在索引的某个字段

搞定MySQL实战


文章标签：
云数据库 RDS MySQL 版
关系型数据库
索引
MySQL
SQL
机器学习/深度学习
关键词：
云数据库 RDS MySQL 版explain
云数据库 RDS MySQL 版深度剖析
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
小小工匠
目录
相关文章
见贤思₇
|
6月前
|
SQL 存储 关系型数据库
SQL优化之Explain详解(mysql)
`Explain`是MySQL中用于分析SQL查询执行计划的工具。它可以帮助我们了解MySQL如何执行SQL语句，包括如何使用索引、预计的行数以及查询的顺序。以下是`Explain`输出的关键列及其含义的简要摘要： 1. **id**：查询的序列号，表示查询中的子句层次，id越大优先级越高。 2. **select_type**：表示查询的类型，如SIMPLE（简单查询）、PRIMARY（主查询，多表查询中的第一个查询）、SUBQUERY（子查询）、DERIVED（派生表）或UNION（UNION操作的查询部分）。 3. **table**：查询涉及的表名，如果是子查询，可能显示为衍生表
见贤思₇
85 0
猿java
|
1月前
|
SQL 关系型数据库 MySQL
MySQL EXPLAIN该如何分析？
本文将详细介绍MySQL中`EXPLAIN`关键字的工作原理及结果字段解析，帮助优化查询性能。`EXPLAIN`可显示查询SQL的执行计划，其结果包括`id`、`select_type`、`table`等字段。通过具体示例和优化建议，帮助你理解和应用`EXPLAIN`，提升数据库查询效率。
猿java
61 0
2012_126com-16008
|
2月前
|
关系型数据库 MySQL
The EXPLAIN statement provides information about how MySQL executes statements
EXPLAIN Output Format
2012_126com-16008
34 6
jianz123
|
4月前
|
SQL 关系型数据库 MySQL
mysql性能调优：EXPLAIN命令21
【7月更文挑战第21天】掌握SQL性能调优：深入解析EXPLAIN命令的神奇用法！
jianz123
58 1
六月暴雪飞梨花
|
4月前
|
SQL 缓存 关系型数据库
MySQL｜浅谈explain的使用
【7月更文挑战第11天】
六月暴雪飞梨花
83 1
三分钟热度的鱼
|
4月前
|
SQL 存储 数据库
MySQL设计规约问题之性能分析工具如Sql explain、show profile和mysqlsla在数据库性能优化中有什么作用
MySQL设计规约问题之性能分析工具如Sql explain、show profile和mysqlsla在数据库性能优化中有什么作用
三分钟热度的鱼
53 2
三分钟热度的鱼
|
4月前
|
SQL 索引 关系型数据库
MySQL设计规约问题之为什么推荐使用EXPLAIN来检查SQL查询
MySQL设计规约问题之为什么推荐使用EXPLAIN来检查SQL查询
三分钟热度的鱼
32 0
sky_qiyi
|
5月前
|
SQL 关系型数据库 MySQL
MySQL数据库——索引（4）-SQL性能分析-profile详情、explain（profile查看指令，explain执行计划中各个字段的含义）
MySQL数据库——索引（4）-SQL性能分析-profile详情、explain（profile查看指令，explain执行计划中各个字段的含义）
sky_qiyi
62 2
进朱者赤
|
5月前
|
SQL 存储 关系型数据库
MySQL的查询计划（EXPLAIN）
MySQL的查询计划（EXPLAIN）
进朱者赤
52 2
qiuqiushuibx
|
6月前
|
存储 SQL 关系型数据库
MySQL 底层数据结构 聚簇索引以及二级索引 Explain的使用
MySQL 底层数据结构 聚簇索引以及二级索引 Explain的使用
qiuqiushuibx
67 0

热门文章

最新文章

  • 1
    测试开启MySQL performance_schema后对性能的影响
  • 2
    MySQL调优之服务器参数优化实践
  • 3
    Mysql5注射技巧总结
  • 4
    mysql左关联不走索引的一个问题的排查过程及思考
  • 5
    应用MySQL MGR
  • 6
    mysql修改用户密码
  • 7
    如何修改WAMP中mysql默认空密码
  • 8
    MySQL-5.5.33主从复制
  • 9
    mysql的总结6--视图的学习
  • 10
    删除MySQL重复数据
  • 1
    MySQL上亿数据查询优化：实践与技巧
    275
  • 2
    如何理解Mysql的索引及他们的原理--------二叉查找树和平衡二叉树和B树和B+树
    46
  • 3
    MySQL：union all与union区别详解
    85
  • 4
    学习mysql中使用inner join，left join 等
    47
  • 5
    【最佳实践】MySQL数据库迁移到PXC集群
    85
  • 6
    MySQL PXC集群配置IPv6
    45
  • 7
    【实操记录】MySQL主从配置
    131
  • 8
    【实操记录】MySQL二进制安装包部署
    176
  • 9
    mysql模糊查询指定根据第几个字符来匹配
    225
  • 10
    mysql使用 CONCAT(字段,字段) 函数拼接
    59

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布
  • 阿里云企业级自治数据库 RDS 详解
  • 阿里云MySQL云数据库产品体系介绍

    • 相关实验场景

    更多
  • 灵活弹性的PolarDB Serverless确保数据业务持续在线
  • 云端MySQL的安装及应用
  • 体验PolarDB MySQL多主集群
  • 基于ECS+RDS的在线留言系统
  • RDS MySQL的SQL问题诊断与调优
  • RDS MySQL Serverless资源与负载自适应

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    DataWorks售前咨询