执行计划之type详解

最新推荐文章于 2024-05-30 15:32:44 发布

lang20150928

最新推荐文章于 2024-05-30 15:32:44 发布

阅读量845

点赞数

分类专栏： sql mysql 文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/m0_37607945/article/details/122317801

版权

mysql 同时被 2 个专栏收录

61 篇文章 2 订阅

订阅专栏

sql

48 篇文章 0 订阅

订阅专栏

MySQL学习系列

官方文档路径：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types

我们前边说过执行计划的一条记录就代表着 MySQL 对某个表的执行查询时的访问方法/访问类型，其中的 type 列就表明了这个访问方法/访问类型是个什么东西，是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

出现比较多的是 system > const > eq_ref > ref > range > index > ALL一般来说，得保证查询至少达到 range 级别，最好能达到 ref。

system

当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、 Memory，那么对该表的访问方法就是 system。

比如

explain select * from test_myisam;

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table test_myisam (cq INT) engine = MyISAM;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into test_myisam values (1);
Query OK, 1 row affected (0.08 sec)

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

当然，如果改成使用 InnoDB 存储引擎，试试看执行计划的 type 列的值是什么。


mysql> ALTER TABLE test_myisam ENGINE=InnoDB;
Query OK, 1 row affected (0.17 sec)
Records: 1  Duplicates: 0  Warnings: 0

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

const

就是当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是 const。因为只匹配一行数据，所以很快。例如将主键置于 where 列表中

EXPLAIN SELECT * FROM s1 WHERE id = 716;


mysql> EXPLAIN SELECT * FROM s1 WHERE id = 716;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.11 sec)

B+树叶子节点中的记录是按照索引列排序的，对于聚簇索引来说，它对应的B+树叶子节点中的记录就是按照id列排序的。B+树矮胖，所以这样根据主键值定位一条记录的速度很快。类似的，我们根据唯一二级索引列来定位一条记录的速度也很快的，比如下边这个查询：

SELECT * FROM order_exp WHERE insert_time=’’ and order_status='' and expire_time='' ;

这个查询的执行分两步，第一步先从u_idx_day_status对应的B+树索引中根据索引列与常数的等值比较条件定位到一条二级索引记录，然后再根据该记录的id值到聚簇索引中获取到完整的用户记录。

MySQL 把这种通过主键或者唯一二级索引列来定位一条记录的访问方法定义为： const，意思是常数级别的，代价是可以忽略不计的。

不过这种 const 访问方法只能在主键列或者唯一二级索引列和一个常数进行等值比较时才有效，如果主键或者唯一二级索引是由多个列构成的话，组成索引的每一个列都是与常数进行等值比较时，这个 const 访问方法才有效。

对于唯一二级索引来说，查询该列为 NULL 值的情况比较特殊，因为唯一二级索引列并不限制 NULL 值的数量，所以上述语句可能访问到多条记录，也就是说 is null 不可以使用 const 访问方法来执行。

eq_ref

在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的〈如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较)，则对该被驱动表的访问方法就是 eq_ref。

驱动表与被驱动表： A 表和 B 表 join 连接查询，如果通过 A 表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到 B 表中查询数据，然后合并结果。那么我们称 A 表为驱动表， B 表为被驱动表
比方说:

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+-------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref        | rows  | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+-------+----------+-------+
|  1 | SIMPLE      | s2    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL       | 10621 |   100.00 | NULL  |
|  1 | SIMPLE      | s1    | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | test.s2.id |     1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+-------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

从执行计划的结果中可以看出， MySQL 打算将 s2 作为驱动表， s1 作为被驱动表，重点关注 s1 的访问方法是 eq_ref，表明在访问 s1 表的时候可以通过主键的等值匹配来进行访问。

ref

当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是 ref。
本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他属于查找和扫描的混合体.

EXPLAIN SELECT * FROM s1 WHERE order_no = 'a';


mysql> EXPLAIN SELECT * FROM s1 WHERE order_no = 'a';
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | ref  | idx_order_no  | idx_order_no | 152     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

对于这个查询，我们当然可以选择全表扫描来逐一对比搜索条件是否满足要求，我们也可以先使用二级索引找到对应记录的 id 值，然后再回表到聚簇索引中查找完整的用户记录。

由于普通二级索引并不限制索引列值的唯一性，所以可能找到多条对应的记录，也就是说使用二级索引来执行查询的代价取决于等值匹配到的二级索引记录条数。如果匹配的记录较少，则回表的代价还是比较低的，所以 MySQL 可能选
择使用索引而不是全表扫描的方式来执行查询。这种搜索条件为二级索引列与常数等值比较，采用二级索引来执行查询的访问方法称为： ref。

对于普通的二级索引来说，通过索引列进行等值比较后可能匹配到多条连续的记录，而不是像主键或者唯一二级索引那样最多只能匹配 1 条记录，所以这种ref 访问方法比 const 要差些，但是在二级索引等值比较时匹配的记录数较少时的
效率还是很高的（如果匹配的二级索引记录太多那么回表的成本就太大了）。不过需要注意下边两种情况：
二级索引列值为 NULL 的情况
不论是普通的二级索引，还是唯一二级索引，它们的索引列对包含 NULL 值的数量并不限制，所以我们采用 key IS NULL 这种形式的搜索条件最多只能使用ref 的访问方法，而不是 const 的访问方法。

对于某个包含多个索引列的二级索引来说，只要是最左边的连续索引列是与常数的等值比较就可能采用 ref 的访问方法，比方说下边这几个查询：

EXPLAIN SELECT * FROM order_exp WHERE insert_time = '2021-03-22 18:28:23';
EXPLAIN SELECT * FROM order_exp WHERE insert_time = '2021-03-22 18:28:23' AND order_status = 0;


mysql> EXPLAIN SELECT * FROM order_exp WHERE insert_time = '2021-03-22 18:28:23';
+----+-------------+-----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | order_exp | NULL       | ref  | u_idx_day_status | u_idx_day_status | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM order_exp WHERE insert_time = '2021-03-22 18:28:23' AND order_status = 0;
+----+-------------+-----------+------------+------+------------------+------------------+---------+-------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra |
+----+-------------+-----------+------------+------+------------------+------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | order_exp | NULL       | ref  | u_idx_day_status | u_idx_day_status | 7       | const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+------------------+------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

但是如果最左边的连续索引列并不全部是等值比较的话，它的访问方法就不能称为 ref 了，比方说这样：

EXPLAIN SELECT * FROM order_exp WHERE insert_time = '2021-03-22 18:28:23' AND order_status > -1;


mysql> EXPLAIN SELECT * FROM order_exp WHERE insert_time = '2021-03-22 18:28:23' AND order_status > -1;
+----+-------------+-----------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | order_exp | NULL       | range | u_idx_day_status | u_idx_day_status | 7       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

fulltext

全文索引，跳过～。

ref_or_null

有时候我们不仅想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为 NULL 的记录也找出来，就像下边这个查询：

explain SELECT * FROM order_exp_cut WHERE order_no= 'abc' OR order_no IS NULL;

mysql> explain SELECT * FROM order_exp_cut WHERE order_no= 'abc' OR order_no IS NULL;
+----+-------------+---------------+------------+-------------+---------------+--------------+---------+-------+------+----------+-----------------------+
| id | select_type | table         | partitions | type        | possible_keys | key          | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+---------------+------------+-------------+---------------+--------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | order_exp_cut | NULL       | ref_or_null | idx_order_no  | idx_order_no | 153     | const |    2 |   100.00 | Using index condition |
+----+-------------+---------------+------------+-------------+---------------+--------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

注意，上面的表改为了 order_exp_cut， order_exp_cut 相对于 order_exp 就是把一些列改为了允许 null，其他的无变化。
在这里插入图片描述
这个查询相当于先分别从 order_exp_cut 表的 idx_order_no 索引对应的 B+树中找出 order_no IS NULL 和 order_no= 'abc’的两个连续的记录范围，然后根据这些二级索引记录中的 id 值再回表查找完整的用户记录。

index_merge

一般情况下对于某个表的查询只能使用到一个索引，在某些场景下可以使用索引合并的方式来执行查询:

EXPLAIN SELECT * FROM s1 WHERE order_no = 'a' OR insert_time = '2021-03-22 18:36:47';

mysql> EXPLAIN SELECT * FROM s1 WHERE order_no = 'a' OR insert_time = '2021-03-22 18:36:47';
+----+-------------+-------+------------+-------------+-----------------------------------------------+------------------------------+---------+------+------+----------+--------------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys                                 | key                          | key_len | ref  | rows | filtered | Extra                                                  |
+----+-------------+-------+------------+-------------+-----------------------------------------------+------------------------------+---------+------+------+----------+--------------------------------------------------------+
|  1 | SIMPLE      | s1    | NULL       | index_merge | u_idx_day_status,idx_order_no,idx_insert_time | idx_order_no,idx_insert_time | 152,5   | NULL |   15 |   100.00 | Using union(idx_order_no,idx_insert_time); Using where |
+----+-------------+-------+------------+-------------+-----------------------------------------------+------------------------------+---------+------+------+----------+--------------------------------------------------------+
1 row in set, 1 warning (0.02 sec)

unique_subquery

类似于两表连接中被驱动表的 eg_ref 访问方法， unique _subquery 是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划
的 type 列的值就是 unique_subquery，比如下边的这个查询语句:

EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2 where s1.insert_time = s2.insert_time) OR order_no = 'a';

mysql> EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2 where s1.insert_time = s2.insert_time) OR order_no = 'a';
+----+--------------------+-------+------------+-----------------+------------------------------------------+---------+---------+------+-------+----------+-------------+
| id | select_type        | table | partitions | type            | possible_keys                            | key     | key_len | ref  | rows  | filtered | Extra       |
+----+--------------------+-------+------------+-----------------+------------------------------------------+---------+---------+------+-------+----------+-------------+
|  1 | PRIMARY            | s1    | NULL       | ALL             | idx_order_no                             | NULL    | NULL    | NULL | 10692 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | s2    | NULL       | unique_subquery | PRIMARY,u_idx_day_status,idx_insert_time | PRIMARY | 8       | func |     1 |    10.00 | Using where |
+----+--------------------+-------+------------+-----------------+------------------------------------------+---------+---------+------+-------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

可以看到执行计划的第二条记录的 type 值就是 unique_subquery，说明在执行子查询时会使用到 id 列的索引。

index_subquery

index_subquery 与 unique_subquery 类似，只不过访问⼦查询中的表时使用的是普通的索引：

EXPLAIN SELECT * FROM s1 WHERE order_no IN (SELECT order_no FROM s2 where s1.insert_time = s2.insert_time) OR order_no = 'a';

这个语句和 unique_subquery 章节中的唯一不同是什么？就是 in 子句的字段由 id 变成了 order_no。


mysql> EXPLAIN SELECT * FROM s1 WHERE order_no IN (SELECT order_no FROM s2 where s1.insert_time = s2.insert_time) OR order_no = 'a';
+----+--------------------+-------+------------+----------------+-----------------------------------------------+--------------+---------+------+-------+----------+-------------+
| id | select_type        | table | partitions | type           | possible_keys                                 | key          | key_len | ref  | rows  | filtered | Extra       |
+----+--------------------+-------+------------+----------------+-----------------------------------------------+--------------+---------+------+-------+----------+-------------+
|  1 | PRIMARY            | s1    | NULL       | ALL            | idx_order_no                                  | NULL         | NULL    | NULL | 10692 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | s2    | NULL       | index_subquery | u_idx_day_status,idx_order_no,idx_insert_time | idx_order_no | 152     | func |     1 |    10.00 | Using where |
+----+--------------------+-------+------------+----------------+-----------------------------------------------+--------------+---------+------+-------+----------+-------------+
2 rows in set, 2 warnings (0.01 sec)

range

如果使用索引获取某些范围区间的记录，那么就可能使用到range访问方法，一般就是在你的 where 语句中出现了 between、 <、 >、 in 等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

EXPLAIN SELECT * FROM s1 WHERE order_no IN ('a', 'b', 'c');
EXPLAIN SELECT * FROM s1 WHERE order_no > 'a' AND order_no < 'b';

mysql> EXPLAIN SELECT * FROM s1 WHERE order_no IN ('a', 'b', 'c');
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | s1    | NULL       | range | idx_order_no  | idx_order_no | 152     | NULL |    3 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

mysql> EXPLAIN SELECT * FROM s1 WHERE order_no > 'a' AND order_no < 'b';
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | s1    | NULL       | range | idx_order_no  | idx_order_no | 152     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

这种利用索引进行范围匹配的访问方法称之为： range。

此处所说的使用索引进行范围匹配中的 索引 可以是聚簇索引，也可以是二级索引

Only rows that are in a given range are retrieved, using an index to select the rows. The key column in the output row indicates which index is used. The key_len contains the longest key part that was used. The ref column is NULL for this type.

range can be used when a key column is compared to a constant using any of the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN() operators

index

当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是 index。

EXPLAIN SELECT insert_time FROM s1 WHERE expire_time = '2021-03-22 18:36:47';

mysql> EXPLAIN SELECT insert_time FROM s1 WHERE expire_time = '2021-03-22 18:36:47';
+----+-------------+-------+------------+-------+---------------+------------------+---------+------+-------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key              | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+------------------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | s1    | NULL       | index | NULL          | u_idx_day_status | 12      | NULL | 10692 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------------------+---------+------+-------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

all

最熟悉的全表扫描，将遍历全表以找到匹配的行

EXPLAIN SELECT * FROM s1;


mysql> EXPLAIN SELECT * FROM s1;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10692 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)

lang20150928

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
打赏
0
评论
执行计划之type详解

MySQL学习系列官方文档路径：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types我们前边说过执行计划的一条记录就代表着 MySQL 对某个表的执行查询时的访问方法/访问类型，其中的 type 列就表明了这个访问方法/访问类型是个什么东西，是较为重要的一个指标，结果值从最好到最坏依次是：system > const > eq_ref > ref > fulltex.
复制链接

扫一扫