【MySQL explain 字段详解】

置顶 已于 2023-02-25 16:57:53 修改
阅读量1.2k 收藏 12
点赞数 3
分类专栏： 数据库 文章标签： mysql 数据库 sql
于 2022-10-20 23:20:56 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/jtshongke/article/details/127437233
版权

MySQL explain 字段详解

前言

我们平常在分析MySQL中的慢SQL时，经常会用到 explain 来分析 SQL 的执行情况。explain 有很多字段，比如：id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra等，
网上的各种文章看的眼花缭乱，还有不少错误。下面我主要参照MySQL官方文档来分析下这些字段以及字段值都代表什么含义。

环境准备

MySQL 版本为5.7.36，

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.36    |
+-----------+

5.7和之前的版本还是有差别的，默认开启了一些优化指标，
我们可以通过 SELECT @@optimizer_switch\G 命令来查看 MySQL中默认打开的指标开关如下：

SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: 
index_merge=on,
index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on,
mrr=on,mrr_cost_based=on,
block_nested_loop=on,
batched_key_access=off,
materialization=on,
semijoin=on,
loosescan=on,
firstmatch=on,
duplicateweedout=on,
subquery_materialization_cost_based=on,
use_index_extensions=on,
condition_fanout_filter=on,
derived_merge=on,
prefer_ordering_index=on
1 row in set (0.00 sec)

在接下来的某些字段讲解中，需要关掉某些指标开关才能看到这些字段值以及演示的效果。
我下面的案例只会关闭部分指标，如果对这些指标开关感兴趣的同学点击 MySQL官网 查看。

💡 所以你用5.7之前的版本和我这里的结果对比可能会有些许不同，望请知晓。

下面所有的表使用的数据库引擎都是InnoDB。

用到的表：
t_order：订单表

CREATE TABLE `t_order` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键自增id',
  `cid` varchar(11) NOT NULL DEFAULT '' COMMENT '公司id',
  `product_id` int(11) NOT NULL DEFAULT '0' COMMENT '产品id',
  PRIMARY KEY (`id`),
  KEY `idx_cid` (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

t_product: 产品表

CREATE TABLE `t_product` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键id',
  `cid` varchar(11) NOT NULL DEFAULT '' COMMENT '公司id',
  PRIMARY KEY (`id`),
  KEY `idx_cid` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;

由于MySQL 5.7中默认优化了很多指标来提升sql的执行效率，

explain中的字段

mysql> explain select * from t_order where product_id =101;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

上面可以看到执行explain之后出现的所有字段：id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra。

在MySQL5.7版本，使用explain参数会自动带上extended和partitions，加上 extended会比之前的版本多一个filtered字段，还会多显示了warning。使用 show warnings 命令可以看到优化器优化之后的查询语句，优化后的语句可能不符合sql语法，但是MySQL是可以识别的，下面的案例中我也会使用到 show warnings 命令。加上partitions会多一个分区字段，但是现在一般都不会再用分区了，所以基本上这个字段的值都是null。

id

id 是序列号，sql 里面有几个select,就有几个id, 而不是查询了几张表，就有几个id。
比如下面的这个sql，只查了一个表，但是有两个select，explain之后是有两列，两个id的。

explain select * from t_order where id = (select id from t_order where cid = '100000035');
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | t_order | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | t_order | NULL       | ref   | idx_cid       | idx_cid | 35      | const |    1 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.02 sec)

执行顺序是和id相关的，id越大越先执行，如果id值相同，则从上到下执行。如果id为null则最后执行。什么情况下 id 会为null呢？比如在使用 union的时候：

mysql> explain select id from t_order where product_id = 101 union select id from t_product where id = 101;
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra           |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
|  1 | PRIMARY      | t_order    | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |  100 |    10.00 | Using where     |
|  2 | UNION        | t_product  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index     |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+

select_type

从官网文档可以看到 select_type的所有值如下：

  • SIMPLE: 简单查询（没有使用联合查询或者子查询）比如：
explain select * from t_order where product_id =101;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
  • PRIMARY：复杂查询中的最外层子查询，比如下面id=1的这一列：
explain select * from t_order where id = (select id from t_order where cid = '100000035');
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | t_order | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | t_order | NULL       | ref   | idx_cid       | idx_cid | 35      | const |    1 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)
  • SUBQUERY: 子查询的第一个select，比如上面的sql中id为2的这一列
  • DEPENDENT SUBQUERY：依赖于外部查询的子查询。大致意思是这样，比如你执行下面的sql:
explain select * from t_product where id in (select product_id from t_order where cid = '10000005') ;

按我们预想的执行计划应该是先从 t_order中 找到 cid = ‘10000005’ 的product_id，然后再去 t_product表中查找id相对应的数据。但是 MySQL 优化器可能把sql优化成下面这样子：

select * from t_product where exists (select * from t_order 
	where t_product.id=t_order.product_id 
	and t_order.cid = '10000005')

因为需要 product_id 字段，所以无法先执行子查询。这样的话就会导致扫描 t_product 全表，如果生产环境遇到这种情况一定要注意，可以改为 join 连接查询。

MySQL 5.7 版本对这种子查询默认做了优化。

explain select * from t_product where id in (select product_id from t_order where cid = '10000005') ;
+----+--------------+-------------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys | key     | key_len | ref                    | rows | filtered | Extra       |
+----+--------------+-------------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
|  1 | SIMPLE       | <subquery2> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                   | NULL |   100.00 | NULL        |
|  1 | SIMPLE       | t_product   | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | <subquery2>.product_id |    1 |   100.00 | Using where |
|  2 | MATERIALIZED | t_order     | NULL       | ref    | idx_cid       | idx_cid | 35      | const                  |    1 |   100.00 | NULL        |
+----+--------------+-------------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
3 rows in set, 1 warning (0.03 sec)

执行完上面的explain 之后，我们看到这里有一个：MATERIALIZED（物化，简单来说就是将子查询的结果存储为临时表，后面会讲到），还有一个1 warning，然后我们使用 show warnings; 命令来查看优化后的sql语句：

show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                         |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `seata_order`.`t_product`.`id` AS `id`,`seata_order`.`t_product`.`cid` AS `cid` from `seata_order`.`t_product` semi join (`seata_order`.`t_order`) where ((`seata_order`.`t_order`.`cid` = '10000005') and (`seata_order`.`t_product`.`id` = `<subquery2>`.`product_id`))

可以看到这里使用了 semi join 这种半连接的方式来进行了优化。

如果想看到之前版本未优化的sql语句，我们可以先执行命令关闭materialization和semijoin开关：

set session optimizer_switch = 'materialization=off';
set session optimizer_switch = 'semijoin=off';

再次执行 explain:

explain select * from t_product where id in (select product_id from t_order where cid = '10000005') ;
+----+--------------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type        | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra                    |
+----+--------------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
|  1 | PRIMARY            | t_product | NULL       | index | NULL          | idx_cid | 35      | NULL  |  100 |   100.00 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | t_order   | NULL       | ref   | idx_cid       | idx_cid | 35      | const |    1 |    10.00 | Using where              |
+----+--------------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

可以看到select_type中已经有DEPENDENT SUBQUERY了。

再次执行 show warnings;：

show warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                            |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `seata_order`.`t_product`.`id` AS `id`,`seata_order`.`t_product`.`cid` AS `cid` from `seata_order`.`t_product` where <in_optimizer>(`seata_order`.`t_product`.`id`,<exists>(/* select#2 */ select 1 from `seata_order`.`t_order` where ((`seata_order`.`t_order`.`cid` = '10000005') and (<cache>(`seata_order`.`t_product`.`id`) = `seata_order`.`t_order`.`product_id`))))

可以看到这里MySQL是使用exists来执行此语句的。

  • UNION：在使用了UNION的情况下，比如上文中提到的id列为null的情况：
explain select id from t_order where product_id = 101 union select id from t_product where id = 101
  • DEPENDENT UNION：依赖于外部查询的UNION，和上文讲到的DEPENDENT SUBQUERY类似，由于 UNION 不经常使用，这里不再进行赘述。
  • UNION RESULT：UNION之后的结果，比如上文中提到的id列为null的情况
mysql> explain select id from t_order where product_id = 101 union select id from t_product where id = 101;
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra           |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
|  1 | PRIMARY      | t_order    | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |  100 |    10.00 | Using where     |
|  2 | UNION        | t_product  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index     |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
3 rows in set, 1 warning (0.03 sec)
  • DERIVED ：派生表，from 当中的子查询派生出来的新表。
explain select * from (select distinct(id) from t_order) t1;
+----+-------------+------------+------------+-------+-----------------+---------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys   | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+-----------------+---------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL            | NULL    | NULL    | NULL |  100 |   100.00 | NULL        |
|  2 | DERIVED     | t_order    | NULL       | index | PRIMARY,idx_cid | idx_cid | 35      | NULL |  100 |   100.00 | Using index |
+----+-------------+------------+------------+-------+-----------------+---------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

并不是所有from中的子查询都会产生派生表，有个派生表的指标 derived_merge=on 在 MySQL 5.7中默认是打开的。比如下面的sql并不会出现DERIVED。

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

这是因为 MySQL 优化器将派生表合并到外部查询中了，通过 show warnings;可以看到真正执行的时候是没有子查询的，所以也就没有产生派生表。

show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                          |
+-------+------+------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select '1' AS `id`,'10000001' AS `cid`,'101' AS `product_id` from `seata_order`.`t_order` where 1 |
+-------+------+------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

我们可以通过关闭开关来查看未优化前的结果：

`set session optimizer_switch = 'derived_merge=off';`
explain select * from (select * from t_order where id =1) t1;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
| 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     | t_order    | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

可以看到这里就产生了派生表。

  • MATERIALIZED 物化，上面在讲子查询时有提及到。它在SQL执行过程中，将子查询的结果集保存到一个临时表中，后序获取该结果集的的数据直接访问此临时表。
  • UNCACHEABLE SUBQUERY ：不可缓存的子查询，对于外层的主表，子查询不可被物化，每次都需要重新计算获取数据，比较耗时
  • UNCACHEABLE UNION ：不可缓存的 UNION，UNION结果不可被物化，每次都需要重新计算获取数据，比较耗时

table

这个比较简单，表名，代表访问的是哪个表或者是表的别名。

partitions

分区，如果使用了 MySQL 的表分区功能来存储数据，这里可以看到查询的是哪个分区。现在一般直接分表的，所以此字段看到的值基本都是 null。
那么为什么现在基本不使用分区了呢？

  1. 底层实现的分区表，对 MySQL来说是有性能消耗的，它需要扫描所有的分区列表。如果要操作数据，还需要锁住所有分区。
  2. 分区依然是单库，在一些需要高并发的场景中优化空间非常有限。面对日益增长的海量数据，优化存储能力有限。
    总之，MySQL 的表分区实现偏底层，定制不灵活且性能有限，维护成本高，所以大多数情况下都是不建议使用的。

type

type 代表 MySQL 访问数据时要走的索引类型，可以参考 官网。
性能从好到差依次为：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。

官方给这种的性能好坏也不是绝对的，有可能有时候靠在前面的type比靠在后面的type执行时间还要久，还和扫描的数据量有关，具体情况还是要以执行结果为准。

  • system:
    只有一行能匹配，它是 const 的一种特例，比如上文中我们讲到的派生表:
`set session optimizer_switch = 'derived_merge=off';`
explain select * from (select * from t_order where id =1) t1;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
| 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     | t_order    | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+

可以看到，id为1的这一列，type 就是 system。 表示派生表中只有一条数据供他查询。

  • const:
    表示只能查询到一条数据，比如下面根据id查询：
explain select * from t_order where id = 2;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_order | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

这里我们也可以看到它和system的不同，system 是已知派生表中只有一行数据能匹配。但是在 innodb 引擎中中是没有存储表的行数的，所以即使你表中只有一条数据，根据id查询，type也是const。
平常我们单表根据查询唯一索引(字段不允许为空)或者主键索引查询时，type基本都是const。

唯一索引 如果字段允许为空，该字段是可以有多个null值的，如果这个时候我们用 字段 is null 去查询，可能会有多个结果匹配，type就是ref而不是const了。

  • eq_ref:
    在表连接中，驱动表中的每一条数据，被驱动表最多有一条数据与驱动表匹配（唯一非空索引和主键索引），它是表连接中最好的类型。
    首先，我们给 t_order 表的 product_id 字段添加唯一索引：
alter table t_order add unique key `idx_pid` (product_id);

执行下面的语句可以看到对于被驱动表 t_order 的 type 就是 eq_ref:

explain select * from t_product p left join t_order o on o.product_id = p.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE      | p     | NULL       | index  | NULL          | idx_cid | 35      | NULL             |  100 |   100.00 | Using index |
|  1 | SIMPLE      | o     | NULL       | eq_ref | idx_pid       | idx_pid | 4       | seata_order.p.id |    1 |   100.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
  • ref:
    等值查询，可能返回多条匹配记录，普通索引或者唯一索引可为null的情况下都可能会出现ref：
explain select * from t_order where cid = '100000062';
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_order | NULL       | ref  | idx_cid       | idx_cid | 35      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+

我们把 t_order 的 product_id 字段改为允许为null的唯一索引，然后再执行explain，可以看到type也是ref:

explain select * from t_order where product_id is null; 
+----+-------------+--------------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table        | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t_order      | NULL       | ref  | idx_pid       | idx_pid | 5       | const |    2 |   100.00 | Using index condition |
+----+-------------+--------------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+

  • fulltext：
    使用全文索引时才会出现的类型，全文索引可以简单的理解为类似ES的倒排索引，由于实验过于复杂，这里不再进行演示（个人觉得如果频繁应用这种场景的话，还不如直接上ES）。对全文索引感兴趣的同学可以参考大目的：MySQL 用全文索引解决模糊查询

  • ref_or_null:
    与前面的ref类似，但是条件中多了为空判断，只有在字段允许为空的时候才会出现，比如我们将 t_order 表中的 cid字段改为允许为null之后，执行下面的语句就会出现ref_or_null：

mysql> explain select * from t_order where cid = '100000062' or cid is null;
+----+-------------+--------------+------------+-------------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table        | partitions | type        | possible_keys | key     | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------------+------------+-------------+---------------+---------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t_order      | NULL       | ref_or_null | idx_cid       | idx_cid | 36      | const |    2 |   100.00 | Using index condition |
+----+-------------+--------------+------------+-------------+---------------+---------+---------+-------+------+----------+-----------------------+
  • index_merge:
    表示查询使用了多个索引，去后取交集或者并集，在 OR 和 AND 的条件中比较常见，比如下面的sql：
explain select * from t_order where product_id =100 or cid = '100000062'; 
+----+-------------+---------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+
| id | select_type | table   | partitions | type        | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                                     |
+----+-------------+---------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+
|  1 | SIMPLE      | t_order | NULL       | index_merge | idx_pid,idx_cid | idx_pid,idx_cid | 4,35    | NULL |    2 |   100.00 | Using union(idx_pid,idx_cid); Using where |
+----+-------------+---------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+

对索引合并算法感兴趣的同学可以参考：官网。

  • unique_subquery
    where 条件的子查询中每一条数据只有一条数据与外部查询关联，它在子查询中查的字段是不重复的唯一值，类似于 eq_ref，只不过这个是在子查询中才会出现。
    首先我们关闭物化和半连接的开关：
set session optimizer_switch = 'materialization=off';
set session optimizer_switch = 'semijoin=off';

执行下面的sql:

explain select * from t_order where product_id in (select id from t_product);
+----+--------------------+-----------+------------+-----------------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type        | table     | partitions | type            | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+--------------------+-----------+------------+-----------------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | PRIMARY            | t_order   | NULL       | ALL             | NULL          | NULL    | NULL    | NULL |  100 |   100.00 | Using where              |
|  2 | DEPENDENT SUBQUERY | t_product | NULL       | unique_subquery | PRIMARY       | PRIMARY | 4       | func |    1 |   100.00 | Using where; Using index |
+----+--------------------+-----------+------------+-----------------+---------------+---------+---------+------+------+----------+--------------------------+

id 在 t_product 表中是主键索引，这里的type就是unique_subquery。

  • index_subquery
    index_subquery和unique_subquery类似，只不过 unique_subquery只对应一条数据，index_subquery 可能对应多条数据。它们两个之间的关系类似于eq_ref和ref的关系。

下面我用cid字段来做实验，对该字段建立的是普通索引：

set session optimizer_switch = 'materialization=off';
set session optimizer_switch = 'semijoin=off';

explain select * from t_order where cid in (select cid from t_product);
+----+--------------------+-----------+------------+----------------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type        | table     | partitions | type           | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+-----------+------------+----------------+---------------+---------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | t_order   | NULL       | ALL            | NULL          | NULL    | NULL    | NULL |  100 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t_product | NULL       | index_subquery | idx_cid       | idx_cid | 35      | func |    1 |   100.00 | Using index |
+----+--------------------+-----------+------------+----------------+---------------+---------+---------+------+------+----------+-------------+

可以看到这里的 type 是 index_subquery。

  • range：范围索引，比如我们常用的 >、< 、>=、<=、BETWEEN、in 等操作。
explain select * from t_order where id >50;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_order | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   50 |   100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

in操作的type也可能是range：

explain select * from t_order where id in (50,51);
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_order | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  • index：表示扫描整个索引树才能拿到结果。
explain select cid from t_order;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_order | NULL       | index | NULL          | idx_cid | 35      | NULL |  100 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

统计表总行数：

explain select count(*) from t_order;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_order | NULL       | index | NULL          | idx_pid | 4       | NULL |  100 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

由于innodb引擎没有存储表的总行数，所以它需要遍历一遍主键索引才能得到总行数。

  • all ：代表没有使用到索引，需要全表扫描。下面几个情况都可能导致全表扫描。
    • 查询全表、
    • 查询条件字段没有加索引
    • 有索引但是不符合最左匹配原则
    • 字段有索引但字段区分度低，MySQL觉得走索引还不如全表扫描
    • 对查询字段使用了函数
    • 对查询字段做了运算
    • 关联字段类型不匹配

如果线上系统有type为all的情况我们一定要注意，如果表的数据量很大，会给服务器造成很大的压力，还会将这些数据放到MySQL的Buff Pool中，占用缓存空间。如果是 udpate 更新数据还可能锁全表。

由于全表扫描的例子比较多，这里就不一一举例，随便举个全模糊查询的例子：

explain select * from t_order where cid like '%666%';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |    11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
  • null
    还有一种type为null的情况, 官网没写。 null 表示MySQL在优化分析阶段分析出不需要访问表或者索引就能得到预期的结果，它的性能比system还要好。 比如我们执行下面的sql：
explain select 1+1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 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 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

下面的这个min函数的例子，type也是null:

explain select min(cid) from t_order;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 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 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

这里的 Extra字段值是：Select tables optimized away，它代表MySQL做的一种优化，这时候MySQL在优化阶段只需要访问 B+Tree 的最左节点即可得到数据，反之访问最右节点就能获得最大值。下面在讲Extra字段的时候就不再重复讲Select tables optimized away这个的含义了。

possible_keys

possible_keys表示sql执行时可能用到的索引，但是不一定就是走这些索引，具体走不走索引或者走哪个索引，还要以下面讲到的 key 为准。

key

key表示sql执行时实际走的索引名，key为null表示没有用到索引。
比如下面的语句用到了名称为idx_pid的索引：

explain select * from t_order where product_id =101;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_order | NULL       | const | idx_pid       | idx_pid | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

key_len

key_len 表示MySql在索引里使用的字节数，如果是联合索引的话，根据这个值我们可以计算出有几个字段用到了索引。
比如我们先删除t_order表中cid字段和product_id字段的单独索引，再对他们建立联合索引，首先where条件里只根据cid去查：

EXPLAIN SELECT * FROM t_order WHERE cid = '10000001';
+----+-------------+--------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t_order      | NULL       | ref  | idx_cid_pid   | idx_cid_pid | 35      | const |    1 |   100.00 | Using index |
+----+-------------+--------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+

cid字段是VARCHAR类型，一个长度占3个字节，还要预留两个字节来存储字符串长度，共：11*3+2=35个字节，说明这里只用到了cid字段的索引。

然后我们再根据cid和product_id的条件一起去查：

EXPLAIN SELECT * FROM t_order WHERE cid = '10000001' AND product_id = 101;
+----+-------------+--------------+------------+------+---------------+-------------+---------+-------------+------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key         | key_len | ref         | rows | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+-------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | t_order      | NULL       | ref  | idx_cid_pid   | idx_cid_pid | 39      | const,const |    1 |   100.00 | Using index |
+----+-------------+--------------+------------+------+---------------+-------------+---------+-------------+------+----------+-------------+

可以看到这里的key_len为39，int类型占4个字节，35（cid）+4(product_id)=39，说明用到了两个字段的索引。

这里的ref字段值为：“const,const”，也从侧面证明了是用到了两个字段的索引。

ref

ref 列表示在 key 列记录的索引中，表查找值所用到的列名或常量。
可能有如下几个值：

  • const 索引字段等值常量查询：
explain select * from t_order where product_id = 101;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_order | NULL       | const | idx_pid       | idx_pid | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  • 表连接中关联表所用到的字段名：
EXPLAIN SELECT * FROM t_order ,t_product WHERE t_order.product_id=t_product.id;
+----+-------------+-----------+------------+--------+---------------+---------+---------+--------------------------------+------+----------+-------------+
| id | select_type | table     | partitions | type   | possible_keys | key     | key_len | ref                            | rows | filtered | Extra       |
+----+-------------+-----------+------------+--------+---------------+---------+---------+--------------------------------+------+----------+-------------+
|  1 | SIMPLE      | t_order   | NULL       | ALL    | idx_pid       | NULL    | NULL    | NULL                           |  100 |   100.00 | NULL        |
|  1 | SIMPLE      | t_product | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | seata_order.t_order.product_id |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+--------+---------------+---------+---------+--------------------------------+------+----------+-------------+

上面第二条数据的ref为product_id字段，表明了两个表是通过product_id字段进行关联的。

  • 条件中发生了隐式转换或者使用了某些函数，比如我们上面讲到的index_subquery：
set session optimizer_switch = 'materialization=off';
set session optimizer_switch = 'semijoin=off';

explain select * from t_order where cid in (select cid from t_product);
+----+--------------------+-----------+------------+----------------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type        | table     | partitions | type           | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+-----------+------------+----------------+---------------+---------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | t_order   | NULL       | ALL            | NULL          | NULL    | NULL    | NULL |  100 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t_product | NULL       | index_subquery | idx_cid       | idx_cid | 35      | func |    1 |   100.00 | Using index |
+----+--------------------+-----------+------------+----------------+---------------+---------+---------+------+------+----------+-------------+

这里的ref就是func了。通过show warnings;可以看到这里是把in转换为了exist。

show warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                          |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `seata_order`.`t_order`.`id` AS `id`,`seata_order`.`t_order`.`cid` AS `cid`,`seata_order`.`t_order`.`product_id` AS `product_id` from `seata_order`.`t_order` where <in_optimizer>(`seata_order`.`t_order`.`cid`,<exists>(<index_lookup>(<cache>(`seata_order`.`t_order`.`cid`) in t_product on idx_cid))) |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  • null 非等值查询或者没有用到索引，ref基本都是null，比如上面提到的全模糊查询：
explain select * from t_order where cid like '%666%';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |    11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+

rows

rows表示MySQL预估要扫描的行数，这个是估值，并不一定是实际所扫描的行数。

filtered

filtered这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，这里的值是比例。

explain select * from t_order where product_id >101;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_order | NULL       | ALL  | idx_pid       | NULL | NULL    | NULL |  100 |    99.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+

比如这里预估扫描100行，filtered是99%，也就是预估在存储引擎层扫描了100行数据，在server层过滤后，返回了99行数据。

Extra

Extra包含了 MySQL 如何解析查询的附加信息，我这里会选一些经常看到的字段值进行演示，如果要看所有的值，请查看 官方文档。

  • Using where 代表数据库引擎（innodb）返回结果后 server 层还要再次筛选。
    这个网上有各种说法，有些文章说出现了Using where是没有用到索引，这个结论是错误的。我这里以高性能mysql第三版说法为准。
    首先我们来看下MySQL的体系架构：

    上面是server 层，下面是存储引擎层（Innodb、Myisam、Memory等）。我理解就是Using where从存储引擎拿到数据后需要在server层进行再次筛选。

💡 用到了索引和没有用到索引，都可能会出现using where。

走了product_id字段索引的案例：

explain select *  from t_order where product_id >150;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_order | NULL       | ALL  | idx_pid       | NULL | NULL    | NULL |  100 |    50.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+

预计在数据库索引层扫描100行，然后在server层过滤了50%。

没有用到索引的案例：

explain select * from t_order where cid like '%666%';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |    11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+

这里将所有数据返回server层再进行筛选。

  • Using index：表示使用到了覆盖索引，不需再进行回表查询，效率较好。比如下面这个sql，通过扫描cid字段建立二级索引就能拿到结果，Extra就是Using index。
explain select cid from t_order;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_order | NULL       | index | NULL          | idx_cid | 35      | NULL |  100 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  • Using filesort
    表示MySQL有可能要使用文件排序，比如我们常见的order by和group by（默认会对by后面的字段进行排序）。如果出现需要排序的数据量超过了sort_buff_size的大小或者需要排序的行数据长度超过了max_length_for_sort_data大小，MySQL会将这些数据放到文件中进行排序，这时候就会出现using filesort。比如下面的案例：
explain select * from t_order where product_id>130  order by product_id;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | t_order | NULL       | ALL  | idx_pid       | NULL | NULL    | NULL |  100 |    70.00 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+

如果需要放在文件中排序的话，执行效率肯定会低，一般可以通过以下方式来解决：

  1. 添加 limit 限制，不返还那么多数据
  2. 看下是否能够使用覆盖索引，大部分情况下是没必要返还全部字段的
  3. 修改sort_buff_size和max_length_for_sort_data参数
  4. group by默认会按照 by后面的字段进行排序，如果不想要排序的话，可以在group by后面加上 order by null，也能避免一些Using filesort的产生。

比如对上面的语句添加limit，可以看到这里已经没有filesort了：

explain select * from t_order where product_id>130  order by product_id limit 5;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t_order | NULL       | range | idx_pid       | idx_pid | 4       | NULL |   70 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
  • Using index condition ：代表可以使用索引下推，索引下推是MySQL 5.6版本推出的新特性，其实就是把要在server层完成的筛选，下推到存储引擎中进行筛选。减少存储引擎返回的数据。
    举个栗子，比如给t_order表加个联合索引，为了方便测试，再加个title字段（这里表结构比较简单，不加字段可能直接走覆盖索引了）：
alter table t_order add index idx_cid_pid(cid,product_id);
alter table t_order add column title varchar(12) not null default '';

执行下面的sql发现有using index condition：

explain select * from t_order where cid like '2000%' and product_id =10;
+----+-------------+--------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table        | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t_order      | NULL       | range | idx_cid_pid   | idx_cid_pid | 39      | NULL |    6 |    10.00 | Using index condition |
+----+-------------+--------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+

如果没有使用索引下推的话，首先会根据cid在存储引擎层进行筛选，把筛选后符合cid的数据根据主键id进行回表查询根据product_id字段进行筛选.

使用索引下推之后，在存储引擎层不仅会根据cid字段筛选，还会根据product_id字段筛选，直接就减少了需要回表的次数。

💡 出现 Using index condition 并不一定是走了索引下推，只是代表可以使用索引下推。索引下推一般是指在联合索引的情况下，根据索引中的数据可以直接做过滤。

  • Using temporary：表示需要用到临时表来解决本次查询。比如上面讲到的union出现了Using temporary。
mysql> explain select id from t_order where product_id = 101 union select id from t_product where id = 101;
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra           |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
|  1 | PRIMARY      | t_order    | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |  100 |    10.00 | Using where     |
|  2 | UNION        | t_product  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index     |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+

单表查询也可能会出现Using temporary：

explain select cid,product_id,title from t_order where cid = '20000001' group by cid,product_id,title order by null;
+----+-------------+--------------+------------+------+---------------+-------------+---------+-------+------+----------+----------------------------------------+
| id | select_type | table        | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                                  |
+----+-------------+--------------+------------+------+---------------+-------------+---------+-------+------+----------+----------------------------------------+
|  1 | SIMPLE      | t_order      | NULL       | ref  | idx_cid_pid   | idx_cid_pid | 35      | const |    6 |   100.00 | Using index condition; Using temporary |
+----+-------------+--------------+------------+------+---------------+-------------+---------+-------+------+----------+----------------------------------------+

这里我们可以通过把title字段也加入到联合索引中来去掉Using temporary：

alter table t_order add index idx_cid_pid_title(cid,product_id,title);
explain select cid,product_id,title from t_order where cid = '20000001' group by cid,product_id,title order by null;
+----+-------------+--------------+------------+------+-------------------+-------------------+---------+-------+------+----------+--------------------------+
| id | select_type | table        | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+--------------+------------+------+-------------------+-------------------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | t_order      | NULL       | ref  | idx_cid_pid_title | idx_cid_pid_title | 35      | const |    6 |   100.00 | Using where; Using index |
+----+-------------+--------------+------------+------+-------------------+-------------------+---------+-------+------+----------+--------------------------+

💡 索引并不是越多越好，毕竟维护索引也是需要”代价“的，我们需要做各种权衡之后再决定是否需要添加索引。

  • Using join buffer (Block Nested Loop): 出现这个代表join连接查询时出现了嵌套循环，一般是连接的字段没有使用到索引导致的。执行下面的sql语句，将t_order表和t_product表通过cid字段进行关联：
explain select * from t_order ,t_product where t_order.cid = t_product.cid;
+----+-------------+-----------+------------+------+---------------+---------+---------+-------------------------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key     | key_len | ref                     | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+---------+---------+-------------------------+------+----------+-------------+
|  1 | SIMPLE      | t_order   | NULL       | ALL  | idx_cid       | NULL    | NULL    | NULL                    |  100 |   100.00 | NULL        |
|  1 | SIMPLE      | t_product | NULL       | ref  | idx_cid       | idx_cid | 35      | seata_order.t_order.cid |    1 |   100.00 | Using index |
+----+-------------+-----------+------------+------+---------------+---------+---------+-------------------------+------+----------+-------------+

然后我们把cid字段的索引去掉，就会发现出现了Using join buffer (Block Nested Loop)：

explain select * from t_order ,t_product where t_order.cid = t_product.cid;
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t_order     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t_product   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
  • Using MRR：Multi-Range Read（多范围读）。在我们根据非聚餐索引（二级索引）查询时，由于二级索引的顺序可能和主键索引的顺序不一致，我们拿到这些二级索引后再去回表查询就会导致很多的随机IO，MRR就是在内存中对查出来的这些二级索引做下排序，让其按照主键索引的顺序进行排序，这样再次查询的时候就会变成顺序IO了（MySQL是按页来读数据的，每页默认读16K）。

与MRR相关的开关有两个分表是mrr和mrr_cost_based，mrr=on代表开启MRR，mrr_cost_based=on表示由优化器决定是否使用MRR，mrr_cost_based=off表示强制使用MRR。MySQL 5.7对MRR相关的默认值是：

mrr=on,mrr_cost_based=on

这里为了演示效果，我把mrr_cost_based改为off，强制使用MRR:

set session optimizer_switch = 'mrr_cost_based=off';
explain select * from t_order where product_id>100 limit 10;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                            |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+
|  1 | SIMPLE      | t_order | NULL       | range | idx_pid       | idx_pid | 4       | NULL |   95 |   100.00 | Using index condition; Using MRR |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+

5.小结

本文主要讲解了explain各个字段含义，这里面字段以及字段的值还是很多的，感兴趣的小伙伴可以先收藏下，以便以后在使用explain时有不了解的信息可以随时查看，毕竟这里可是有官网传送门的，以便你可以随时跳到官网随时查看，哈哈。

好了，这篇文章就到这里了，感谢大家的观看！如有错误，请及时指正！欢迎大家关注我的公众号：贾哇技术指南

参考

贾哇技术指南
关注
  • 3
    点赞
  • 踩
  • 12
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
举例解释一下explain各字段的含义
KunLunDB_Linda的博客
02-23 670
前言 之前文章介绍索引失效的场景（聊一聊MySQL索引失效的问题），用到了explain执行计划，执行计划返回执行过程中每一步的信息，而不是执行它。 通过返回的一行或多行信息，显示出执行计划中的每一部分和执行的次序，从而可以从分析结果中，找到查询语句或是表结构的性能瓶颈。 今天我们举一些实际的例子，来解释一下explain执行计划各字段的含义。 首先建立 用户，角色，关系，分区表。 CREATE TABLE table_user( id INT AUTO_INCREMENT, u
MySQL的explain字段解释
gaohuijie521的博客
07-27 1923
学习
MySQL explain字段解释
weixin_30511107的博客
08-07 106
1.select_type 　　simple：它表示简单的select,没有union和子查询 dependent union：union依赖外层的查询 　　dependent subquery：subquery依赖外层的查询 2.type 　　system：表仅有一行，这是const类型的特列，平时不会出现，一般出现在mysql内建系统表中 　　const ：表最多有...
MySQL的EXPLAIN语句之type字段详解
最新发布
qq_38671485的博客
07-18 1032
语句时，MySQL会返回一个关于查询计划的描述，这被称为执行计划或查询计划。执行计划包含了MySQL如何执行查询的详细信息，包括访问哪些表、按什么顺序访问、以及如何从这些表中检索数据。字段可以帮助我们诊断查询性能问题，并采取措施优化查询，如添加合适的索引或调整查询结构。，这一列描述了MySQL访问表的方式，也称为“联接类型”或“访问类型”。字段的值可以有多种，每一种都表示了不同的效率级别。语句是一个非常有用的工具，用于帮助分析和优化查询性能。语句返回的结果集中有一列名为。
MySQL Explain 字段详解
我的博客
03-26 1906
结合查询示例对 Explain 工具查询的列进行讲解
最完整的Explain总结，再也不用担心SQL优化了
weixin_50205273的博客
08-25 575
在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询时，会返回执行计划的信息，而不是执行这条SQL（如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中） CREATETABLE`film`( `id`int(11)NOTNULLAUTO_INCREMENT, `name`varchar(10)DEFAULTNULL, PRIMARYKEY(`id`), KEY`idx_name`(`name`) ...
Explain中的字段
ycd500756的博客
07-25 396
1.id：操作表的顺序 根据id的值出现的情况不同，操作表的顺序或者执行select字句的顺序也会有所不同 id值出现的情况 执行的顺序 id值相同的时候 由上之下执行 id值不同的时候 id值越大越是先执行 id值有相同也有不同的时候 id值越大的先执行，id值相同的由上之下执行 2.select_type:查询的类型 出...
MySQL进阶系列： 一文详解explain各字段含义
yyyy11119的博客
08-30 2343
explain有何用处呢：为了知道优化SQL语句的执行，需要查看SQL语句的具体执行过程，以加快SQL语句的执行效率。 可以使用explain+SQL语句来模拟优化器执行SQL查询语句，从而知道mysql是如何处理sql语句的。通过查看执行计划了解执行器是否按照我们想的那样处理SQL。 explain执行计划中包含的信息如下： id: 查询序列号 select_type: 查询类型 table: 表名或者别名 partitions: 匹配的分区 type: 访问类型 possible_keys: 可能用到
MySQL Explain使用详解
09-09
MySQL的`EXPLAIN`命令是数据库管理员和开发者用来分析SQL查询执行计划的重要工具。通过`EXPLAIN`，我们可以了解MySQL如何解析和优化SQL语句，从而优化查询性能，避免全表扫描，提升数据库效率。 首先，`EXPLAIN`...
mysql中explain用法详解
09-10
在`EXPLAIN`输出中，每个字段都有其特定的含义： 1. **id**: 这是SELECT语句的查询序列号，表示查询的层次结构。对于简单的查询，所有行的id都相同。在复杂的查询中，如包含UNION或子查询，id会递增表示嵌套的级别...
mysql explain详解
03-29
MySQL中的`EXPLAIN`命令是用于分析SQL查询执行计划的重要工具，它可以帮助数据库管理员和开发者了解MySQL如何处理SQL语句，从而进行性能优化。在深入理解`EXPLAIN`之前，我们先来了解一下数据库优化的基本概念。 ...
mysql explain ref null_MySQL Explain使用详解
weixin_36415835的博客
02-07 2290
在日常工作中，我们会有时会开慢查询去记录一些执行时间比较久的SQL语句，找出这些SQL语句并不意味着完事了，些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略...
mysql explain字段含义
qq_35425070的博客
02-10 2117
MySQL查询优化 在使用mysql的过程中，随着数据量的增大，sql语句的优化是必不可少的，而优化中一个非常实用的工具就是explain。 这里啰嗦几句mysql，不喜欢的可以跳过直接看下面。 先看一下mysql的逻辑结构，如图 （图片来自网络，mysql 版本为5.6） mysql 经常使用的引擎为 myisam 和 innodb 下图为Innodb引擎 结构体系图： 了解操作系统的都知道...
explain字段解释
fuzhongyu2的博客
10-05 3195
EXPLAIN列的解释： table：显示这一行的数据是关于哪张表的 type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句。这个列表是在优化过程的早期创建的，因此有些列出
Explain详解与索引最佳实践
Xx__WangQi的博客
05-19 949
目录 Explain工具介绍 Explain分析示例 explain 两个变种 explain中的列 1. id列 2. select_type列 3. table列 4. type列 5. possible_keys列 6. key列 7. key_len列 8. ref列 9. rows列 10. Extra列 索引最佳实践 1.全值匹配 2.最左前缀法则 3.不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描 4.存储..
mysql explain字段意思解释
码瘾的空间
11-27 304
mysql explain字段意思解释 explain包含id、select_type、table、type、possible_keys、key、key_len、ref、rows、extra字段 id id列的编号是select的序列号，有几个select就有几个id，并且id的顺序是按select出现的顺序增长的。 select_type se...
explain 各字段介绍
jiaomubai的博客
07-31 2921
explain 各字段介绍
MySQL explain字段总结
不吃茄子啦的博客
10-07 143
目录作用表组成id（表的读取顺序）select_type（数据读取操作的操作类型）字段type字段possible_keykey 作用 表的读取顺序 数据读取操作的操作类型 那些索引可以使用 哪些索引实际被使用 表之间的引用 每张表有多少行被优化器查询 表组成 id select_type table type possible_keys key key_len ref id（表的读取顺序） 为select查询的序列号，包含一组数字,表的加载顺序 id相同，执行顺序为从上到下顺序执行。
Explain字段解析（Mysql）
杨宇java的博客
08-13 349
explain + sql语句 了解次条sql语句的执行计划 字段如下： id： select查询的序列号：id值越大，优先级越高，id值相同时，则执行顺序自上而下 并不是有where子查询时id就会增加，具体何时会导致id增加暂时不清楚（下图） select_type：查询的类型 simple ：简单子查询（很多博客说不使用union和子查询）---应该指的是不包括in关键字吧。。 primary:最外层子查询 subQuery:第一层子查询 ...
MySQL EXPLAIN详解与性能分析
"这份PDF文件主要讲解了MySQL中的EXPLAIN工具的使用，以及如何通过它来分析SQL查询的执行计划，以优化数据库性能。文件中提供了创建和填充测试表的示例，包括`actor`, `film` 和 `film_actor`三张表，以及对`film`表...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值