一条查询语句在经过MySQL
查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划
,这个执行计划展示了接下来具体执行查询的方式,比如多表连接的顺序是什么,对于每个表采用什么访问方法来具体执行查询等等。可以使用EXPLAIN
命令来查看某个查询语句的具体执行计划
如果我们想看看某个查询的执行计划的话,可以在具体的查询语句前边加一个EXPLAIN
,就像这样:
mysql> EXPLAIN SELECT 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 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.01 sec)
然后这输出的一大坨东西就是所谓的执行计划
,先有一个大概的认识,把EXPLAIN
语句输出的各个列的作用先大致罗列一下:
列名 | 描述 |
---|---|
id | 在一个大的查询语句中每个SELECT 关键字都对应一个唯一的id |
select_type | SELECT 关键字对应的那个查询的类型 |
table | 表名 |
partitions | 匹配的分区信息 |
type | 针对单表的访问方法 |
possible_keys | 可能用到的索引 |
key | 实际上使用的索引 |
key_len | 实际使用到的索引长度 |
ref | 当使用索引列等值查询时,与索引列进行等值匹配的对象信息 |
rows | 预估的需要读取的记录条数 |
filtered | 某个表经过搜索条件过滤后剩余记录条数的百分比 |
Extra | 一些额外的信息 |
CREATE TABLE single_table (
id INT NOT NULL AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
KEY idx_key1 (key1),
UNIQUE KEY idx_key2 (key2),
KEY idx_key3 (key3),
KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;
我们仍然假设有两个和single_table
表构造一模一样的s1
、s2
表,而且这两个表里边儿有10000条记录,除id列外其余的列都插入随机值。为了让大家有比较好的阅读体验,我们下边并不准备严格按照EXPLAIN
输出列的顺序来介绍这些列分别是干嘛的,大家注意一下就好了。
执行计划输出中各列详解
table
不论我们的查询语句有多复杂,里边儿包含了多少个表,到最后也是需要对每个表进行单表访问的,所以规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法,该条记录的table列代表着该表的表名。所以我们看一条比较简单的查询语句:
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 | 9688 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
这个查询语句只涉及对s1
表的单表查询,所以EXPLAIN
输出中只有一条记录,其中的table
列的值是s1
,表明这条记录是用来说明对s1
表的单表访问方法的。
下边我们看一下一个连接查询的执行计划:
mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9688 | 100.00 | NULL |
| 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9954 | 100.00 | Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
2 rows in set, 1 warning (0.01 sec)
可以看到这个连接查询的执行计划中有两条记录,这两条记录的table
列分别是s1
和s2
,这两条记录用来分别说明对s1
表和s2
表的访问方法是什么。
id
我们知道我们写的查询语句一般都以SELECT
关键字开头,比较简单的查询语句里只有一个SELECT
关键字,比如下边这个查询语句:
SELECT * FROM s1 WHERE key1 = 'a';
稍微复杂一点的连接查询中也只有一个SELECT
关键字,比如:
SELECT * FROM s1 INNER JOIN s2
ON s1.key1 = s2.key1
WHERE s1.common_field = 'a';
但是下边两种情况下在一条查询语句中会出现多个SELECT
关键字:
-
查询中包含子查询的情况
比如下边这个查询语句中就包含2个
SELECT
关键字:SELECT * FROM s1 WHERE key1 IN (SELECT * FROM s2);
-
查询中包含
UNION
语句的情况比如下边这个查询语句中也包含2个
SELECT
关键字:SELECT * FROM s1 UNION SELECT * FROM s2;
查询语句中每出现一个SELECT
关键字,设计MySQL
的大叔就会为它分配一个唯一的id
值。这个id
值就是EXPLAIN
语句的第一个列,比如下边这个查询中只有一个SELECT
关键字,所以EXPLAIN
的结果中也就只有一条id
列为1
的记录:
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 8 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.03 sec)
对于连接查询来说,一个SELECT
关键字后边的FROM
子句中可以跟随多个表,所以在连接查询的执行计划中,每个表都会对应一条记录,但是这些记录的id值都是相同的,比如:
mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9688 | 100.00 | NULL |
| 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9954 | 100.00 | Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
2 rows in set, 1 warning (0.01 sec)
可以看到,上述连接查询中参与连接的s1
和s2
表分别对应一条记录,但是这两条记录对应的id
值都是1
。这里需要大家记住的是,在连接查询的执行计划中,每个表都会对应一条记录,这些记录的id列的值是相同的,出现在前边的表表示驱动表,出现在后边的表表示被驱动表。所以从上边的EXPLAIN
输出中我们可以看出,查询优化器准备让s1
表作为驱动表,让s2
表作为被驱动表来执行查询。
对于包含子查询的查询语句来说,就可能涉及多个SELECT
关键字,所以在包含子查询的查询语句的执行计划中,每个SELECT
关键字都会对应一个唯一的id
值,比如这样:
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9688 | 100.00 | Using where |
| 2 | SUBQUERY | s2 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9954 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.02 sec)
从输出结果中我们可以看到,s1
表在外层查询中,外层查询有一个独立的SELECT
关键字,所以第一条记录的id
值就是1
,s2
表在子查询中,子查询有一个独立的SELECT
关键字,所以第二条记录的id
值就是2
。
但是这里大家需要特别注意,查询优化器可能对涉及子查询的查询语句进行重写,从而转换为连接查询。所以如果我们想知道查询优化器对某个包含子查询的语句是否进行了重写,直接查看执行计划就好了,比如说:
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key3 FROM s2 WHERE common_field = 'a');
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+------------------------------+
| 1 | SIMPLE | s2 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9954 | 10.00 | Using where; Start temporary |
| 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | xiaohaizi.s2.key3 | 1 | 100.00 | End temporary |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+------------------------------+
2 rows in set, 1 warning (0.00 sec)
可以看到,虽然我们的查询语句是一个子查询,但是执行计划中s1
和s2
表对应的记录的id
值全部是1
,这就表明了查询优化器将子查询转换为了连接查询。
对于包含UNION
子句的查询语句来说,每个SELECT
关键字对应一个id
值也是没错的
select_type
通过上边的内容我们知道,一条大的查询语句里边可以包含若干个SELECT
关键字,每个SELECT
关键字代表着一个小的查询语句,而每个SELECT
关键字的FROM
子句中都可以包含若干张表(这些表用来做连接查询),每一张表都对应着执行计划输出中的一条记录,对于在同一个SELECT
关键字中的表来说,它们的id
值是相同的。
设计MySQL
的大叔为每一个SELECT
关键字代表的小查询都定义了一个称之为select_type
的属性,意思是我们只要知道了某个小查询的select_type
属性,就知道了这个小查询在整个大查询中扮演了一个什么角色,口说无凭,我们还是先来见识见识这个select_type
都能取哪些值(为了精确起见,我们直接使用文档中的英文做简要描述,随后会进行详细解释的):
名称 | 描述 |
---|---|
SIMPLE | Simple SELECT (not using UNION or subqueries) |
PRIMARY | Outermost SELECT |
UNION | Second or later SELECT statement in a UNION |
UNION RESULT | Result of a UNION |
SUBQUERY | First SELECT in subquery |
DEPENDENT SUBQUERY | First SELECT in subquery, dependent on outer query |
DEPENDENT UNION | Second or later SELECT statement in a UNION, dependent on outer query |
DERIVED | Derived table |
MATERIALIZED | Materialized subquery |
UNCACHEABLE SUBQUERY | A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query |
UNCACHEABLE UNION | The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY) |
-
SIMPLE
查询语句中不包含
UNION
或者子查询的查询都算作是SIMPLE
类型,比方说下边这个单表查询的select_type
的值就是SIMPLE
: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 | 9688 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
当然,连接查询也算是
SIMPLE
类型,比如:mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9688 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9954 | 100.00 | Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+ 2 rows in set, 1 warning (0.01 sec)
-
PRIMARY
对于包含
UNION
、UNION ALL
或者子查询的大查询来说,它是由几个小查询组成的,其中最左边的那个查询的select_type
值就是PRIMARY
,比方说:mysql> EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2; +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9688 | 100.00 | NULL | | 2 | UNION | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9954 | 100.00 | NULL | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 3 rows in set, 1 warning (0.00 sec)
从结果中可以看到,最左边的小查询
SELECT * FROM s1
对应的是执行计划中的第一条记录,它的select_type
值就是PRIMARY
。 -
UNION
对于包含
UNION
或者UNION ALL
的大查询来说,它是由几个小查询组成的,其中除了最左边的那个小查询以外,其余的小查询的select_type
值就是UNION
,可以对比上一个例子的效果,这就不多举例子了。 -
UNION RESULT
MySQL
选择使用临时表来完成UNION
查询的去重工作,针对该临时表的查询的select_type
就是UNION RESULT
,例子上边有,就不赘述了。 -
SUBQUERY
如果包含子查询的查询语句不能够转为对应的
semi-join
的形式,并且该子查询是不相关子查询,并且查询优化器决定采用将该子查询物化的方案来执行该子查询时,该子查询的第一个SELECT
关键字代表的那个查询的select_type
就是SUBQUERY
,比如下边这个查询:mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a'; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9688 | 100.00 | Using where | | 2 | SUBQUERY | s2 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9954 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)
可以看到,外层查询的
select_type
就是PRIMARY
,子查询的select_type
就是SUBQUERY
。需要大家注意的是,由于select_type为SUBQUERY的子查询由于会被物化,所以只需要执行一遍。 -
DEPENDENT SUBQUERY
如果包含子查询的查询语句不能够转为对应的
semi-join
的形式,并且该子查询是相关子查询,则该子查询的第一个SELECT
关键字代表的那个查询的select_type
就是DEPENDENT SUBQUERY
,比如下边这个查询:mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a'; +----+--------------------+-------+------------+------+-------------------+----------+---------+-------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+------+-------------------+----------+---------+-------------------+------+----------+-------------+ | 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9688 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | s2 | NULL | ref | idx_key2,idx_key1 | idx_key2 | 5 | xiaohaizi.s1.key2 | 1 | 10.00 | Using where | +----+--------------------+-------+------------+------+-------------------+----------+---------+-------------------+------+----------+-------------+ 2 rows in set, 2 warnings (0.00 sec)
需要大家注意的是,select_type为DEPENDENT SUBQUERY的查询可能会被执行多次。
-
DEPENDENT UNION
在包含
UNION
或者UNION ALL
的大查询中,如果各个小查询都依赖于外层查询的话,那除了最左边的那个小查询之外,其余的小查询的select_type
的值就是DEPENDENT UNION
。说的有些绕哈,比方说下边这个查询:mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b'); +----+--------------------+------------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+------------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+ | 1 | PRIMARY | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9688 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 12 | 100.00 | Using where; Using index | | 3 | DEPENDENT UNION | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 8 | 100.00 | Using where; Using index | | NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------------+------------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+ 4 rows in set, 1 warning (0.03 sec)
这个查询比较复杂啊,大查询里包含了一个子查询,子查询里又是由
UNION
连起来的两个小查询。从执行计划中可以看出来,SELECT key1 FROM s2 WHERE key1 = 'a'
这个小查询由于是子查询中第一个查询,所以它的select_type
是DEPENDENT SUBQUERY
,而SELECT key1 FROM s1 WHERE key1 = 'b'
这个查询的select_type
就是DEPENDENT UNION
。 -
DERIVED
对于采用物化的方式执行的包含派生表的查询,该派生表对应的子查询的
select_type
就是DERIVED
,比方说下边这个查询:mysql> EXPLAIN SELECT * FROM (SELECT key1, count(*) as c FROM s1 GROUP BY key1) AS derived_s1 where c > 1; +----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 9688 | 33.33 | Using where | | 2 | DERIVED | s1 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9688 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)
从执行计划中可以看出,
id
为2
的记录就代表子查询的执行方式,它的select_type
是DERIVED
,说明该子查询是以物化的方式执行的。id
为1
的记录代表外层查询,大家注意看它的table
列显示的是<derived2>
,表示该查询是针对将派生表物化之后的表进行查询的。 -
MATERIALIZED
当查询优化器在执行包含子查询的语句时,选择将子查询物化之后与外层查询进行连接查询时,该子查询对应的
select_type
属性就是MATERIALIZED
,比如下边这个查询:mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2); +----+--------------+-------------+------------+--------+---------------+------------+---------+-------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+-------------+------------+--------+---------------+------------+---------+-------------------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 9688 | 100.00 | Using where | | 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_key> | <auto_key> | 303 | xiaohaizi.s1.key1 | 1 | 100.00 | NULL | | 2 | MATERIALIZED | s2 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9954 | 100.00 | Using index | +----+--------------+-------------+------------+--------+---------------+------------+---------+-------------------+------+----------+-------------+ 3 rows in set, 1 warning (0.01 sec)
执行计划的第三条记录的
id
值为2
,说明该条记录对应的是一个单表查询,从它的select_type
值为MATERIALIZED
可以看出,查询优化器是要把子查询先转换成物化表。然后看执行计划的前两条记录的id
值都为1
,说明这两条记录对应的表进行连接查询,需要注意的是第二条记录的table
列的值是<subquery2>
,说明该表其实就是id
为2
对应的子查询执行之后产生的物化表,然后将s1
和该物化表进行连接查询。
partitions
一般情况下我们的查询语句的执行计划的partitions
列的值都是NULL
。
type
我们前边说过执行计划的一条记录就代表着MySQL
对某个表的执行查询时的访问方法,其中的type
列就表明了这个访问方法是个啥,比方说下边这个查询:
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 8 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.04 sec)
可以看到type
列的值是ref
,表明MySQL
即将使用ref
访问方法来执行对s1
表的查询。 InnoDB
存储引擎的表进行单表访问的一些访问方法,完整的访问方法如下:system
,const
,eq_ref
,ref
,fulltext
,ref_or_null
,index_merge
,unique_subquery
,index_subquery
,range
,index
,ALL
。
-
system
当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的,比如MyISAM、Memory,那么对该表的访问方法就是
system
。比方说我们新建一个MyISAM
表,并为其插入一条记录:mysql> CREATE TABLE t(i int) Engine=MyISAM; Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO t VALUES(1); Query OK, 1 row affected (0.01 sec)
然后我们看一下查询这个表的执行计划:
mysql> EXPLAIN SELECT * FROM t; +----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | t | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
可以看到
type
列的值就是system
了。 -
const
这个我们前边唠叨过,就是当我们根据主键或者唯一二级索引列与常数进行等值匹配时,对单表的访问方法就是
const
,比如:mysql> EXPLAIN SELECT * FROM s1 WHERE id = 5; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec)
-
eq_ref
在连接查询时,如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的(如果该主键或者唯一二级索引是联合索引的话,所有的索引列都必须进行等值比较),则对该被驱动表的访问方法就是
eq_ref
,比方说: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 | s1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 9688 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | xiaohaizi.s1.id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+ 2 rows in set, 1 warning (0.01 sec)
从执行计划的结果中可以看出,
MySQL
打算将s1
作为驱动表,s2
作为被驱动表,重点关注s2
的访问方法是eq_ref
,表明在访问s2
表的时候可以通过主键的等值匹配来进行访问。 -
ref
当通过普通的二级索引列与常量进行等值匹配时来查询某个表,那么对该表的访问方法就可能是
ref
,最开始举过例子了,就不重复举例了。 -
fulltext
全文索引
-
ref_or_null
当对普通二级索引进行等值匹配查询,该索引列的值也可以是
NULL
值时,那么对该表的访问方法就可能是ref_or_null
,比如说:mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL; +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | s1 | NULL | ref_or_null | idx_key1 | idx_key1 | 303 | const | 9 | 100.00 | Using index condition | +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning (0.01 sec)
-
index_merge
一般情况下对于某个表的查询只能使用到一个索引,但我们唠叨单表访问方法时特意强调了在某些场景下可以使用
Intersection
、Union
、Sort-Union
这三种索引合并的方式来执行查询,忘掉的回去补一下哈,我们看一下执行计划中是怎么体现MySQL
使用索引合并的方式来对某个表执行查询的:mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a'; +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+ | 1 | SIMPLE | s1 | NULL | index_merge | idx_key1,idx_key3 | idx_key1,idx_key3 | 303,303 | NULL | 14 | 100.00 | Using union(idx_key1,idx_key3); Using where | +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+ 1 row in set, 1 warning (0.01 sec)
从执行计划的
type
列的值是index_merge
就可以看出,MySQL
打算使用索引合并的方式来执行对s1
表的查询。 -
unique_subquery
类似于两表连接中被驱动表的
eq_ref
访问方法,unique_subquery
是针对在一些包含IN
子查询的查询语句中,如果查询优化器决定将IN
子查询转换为EXISTS
子查询,而且子查询可以使用到主键进行等值匹配的话,那么该子查询执行计划的type
列的值就是unique_subquery
,比如下边的这个查询语句:mysql> EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 where s1.key1 = s2.key1) OR key3 = 'a'; +----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+ | 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9688 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | s2 | NULL | unique_subquery | PRIMARY,idx_key1 | PRIMARY | 4 | 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
类似,只不过访问子查询中的表时使用的是普通的索引,比如这样:mysql> EXPLAIN SELECT * FROM s1 WHERE common_field IN (SELECT key3 FROM s2 where s1.key1 = s2.key1) OR key3 = 'a'; +----+--------------------+-------+------------+----------------+-------------------+----------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+----------------+-------------------+----------+---------+------+------+----------+-------------+ | 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9688 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | s2 | NULL | index_subquery | idx_key1,idx_key3 | idx_key3 | 303 | func | 1 | 10.00 | Using where | +----+--------------------+-------+------------+----------------+-------------------+----------+---------+------+------+----------+-------------+ 2 rows in set, 2 warnings (0.01 sec)
-
range
如果使用索引获取某些
范围区间
的记录,那么就可能使用到range
访问方法,比如下边的这个查询:mysql> EXPLAIN SELECT * FROM s1 WHERE key1 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_key1 | idx_key1 | 303 | NULL | 27 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.01 sec)
或者:
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b'; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 294 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
-
index
当我们可以使用索引覆盖,但需要扫描全部的索引记录时,该表的访问方法就是
index
,比如这样:mysql> EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a'; +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | s1 | NULL | index | NULL | idx_key_part | 909 | NULL | 9688 | 10.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
上述查询中的搜索列表中只有
key_part2
一个列,而且搜索条件中也只有key_part3
一个列,这两个列又恰好包含在idx_key_part
这个索引中,可是搜索条件key_part3
不能直接使用该索引进行ref
或者range
方式的访问,只能扫描整个idx_key_part
索引的记录,所以查询计划的type
列的值就是index
。小贴士: 再一次强调,对于使用InnoDB存储引擎的表来说,二级索引的记录只包含索引列和主键列的值,而聚簇索引中包含用户定义的全部列以及一些隐藏列,所以扫描二级索引的代价比直接全表扫描,也就是扫描聚簇索引的代价更低一些。
-
ALL
最熟悉的全表扫描,就不多唠叨了,直接看例子:
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 | 9688 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
一般来说,这些访问方法按照我们介绍它们的顺序性能依次变差。其中除了All
这个访问方法外,其余的访问方法都能用到索引,除了index_merge
访问方法外,其余的访问方法都最多只能用到一个索引。
possible_keys和key
在EXPLAIN
语句输出的执行计划中,possible_keys
列表示在某个查询语句中,对某个表执行单表查询时可能用到的索引有哪些,key
列表示实际用到的索引有哪些,比方说下边这个查询:
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';
+----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | s1 | NULL | ref | idx_key1,idx_key3 | idx_key3 | 303 | const | 6 | 2.75 | Using where |
+----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
上述执行计划的possible_keys
列的值是idx_key1,idx_key3
,表示该查询可能使用到idx_key1,idx_key3
两个索引,然后key
列的值是idx_key3
,表示经过查询优化器计算使用不同索引的成本后,最后决定使用idx_key3
来执行查询比较划算。
不过有一点比较特别,就是在使用index
访问方法来查询某个表时,possible_keys
列是空的,而key
列展示的是实际使用到的索引,比如这样:
mysql> EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | s1 | NULL | index | NULL | idx_key_part | 909 | NULL | 9688 | 10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
另外需要注意的一点是,possible_keys列中的值并不是越多越好,可能使用的索引越多,查询优化器计算查询成本时就得花费更长时间,所以如果可以的话,尽量删除那些用不到的索引。
key_len
key_len
列表示当优化器决定使用某个索引执行查询时,该索引记录的最大长度,它是由这三个部分构成的:
-
对于使用固定长度类型的索引列来说,它实际占用的存储空间的最大长度就是该固定值,对于指定字符集的变长类型的索引列来说,比如某个索引列的类型是
VARCHAR(100)
,使用的字符集是utf8
,那么该列实际占用的最大存储空间就是100 × 3 = 300
个字节。 -
如果该索引列可以存储
NULL
值,则key_len
比不可以存储NULL
值时多1个字节。 -
对于变长字段来说,都会有2个字节的空间来存储该变长列的实际长度。
比如下边这个查询:
mysql> EXPLAIN SELECT * FROM s1 WHERE id = 5;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
由于id
列的类型是INT
,并且不可以存储NULL
值,所以在使用该列的索引时key_len
大小就是4
。当索引列可以存储NULL
值时,比如:
mysql> EXPLAIN SELECT * FROM s1 WHERE key2 = 5;
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | const | idx_key2 | idx_key2 | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
可以看到key_len
列就变成了5
,比使用id
列的索引时多了1
。
对于可变长度的索引列来说,比如下边这个查询:
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 8 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
由于key1
列的类型是VARCHAR(100)
,所以该列实际最多占用的存储空间就是300
字节,又因为该列允许存储NULL
值,所以key_len
需要加1
,又因为该列是可变长度列,所以key_len
需要加2
,所以最后ken_len
的值就是303
。
有的同学可能有疑问:你在前边唠叨InnoDB
行格式的时候不是说,存储变长字段的实际长度不是可能占用1个字节或者2个字节么?为什么现在不管三七二十一都用了2
个字节?这里需要强调的一点是,执行计划的生成是在MySQL server
层中的功能,并不是针对具体某个存储引擎的功能,设计MySQL
的大叔在执行计划中输出key_len
列主要是为了让我们区分某个使用联合索引的查询具体用了几个索引列,而不是为了准确的说明针对某个具体存储引擎存储变长字段的实际长度占用的空间到底是占用1个字节还是2个字节。比方说下边这个使用到联合索引idx_key_part
的查询:
mysql> EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | ref | idx_key_part | idx_key_part | 303 | const | 12 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
我们可以从执行计划的key_len
列中看到值是303
,这意味着MySQL
在执行上述查询中只能用到idx_key_part
索引的一个索引列,而下边这个查询:
mysql> EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';
+----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | ref | idx_key_part | idx_key_part | 606 | const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
这个查询的执行计划的ken_len
列的值是606
,说明执行这个查询的时候可以用到联合索引idx_key_part
的两个索引列。
ref
当使用索引列等值匹配的条件去执行查询时,也就是在访问方法是const
、eq_ref
、ref
、ref_or_null
、unique_subquery
、index_subquery
其中之一时,ref
列展示的就是与索引列作等值匹配的东东是个啥,比如只是一个常数或者是某个列。大家看下边这个查询:
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 8 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
可以看到ref
列的值是const
,表明在使用idx_key1
索引执行查询时,与key1
列作等值匹配的对象是一个常数,当然有时候更复杂一点:
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 | s1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 9688 | 100.00 | NULL |
| 1 | SIMPLE | s2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | xiaohaizi.s1.id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
可以看到对被驱动表s2
的访问方法是eq_ref
,而对应的ref
列的值是xiaohaizi.s1.id
,这说明在对被驱动表进行访问时会用到PRIMARY
索引,也就是聚簇索引与一个列进行等值匹配的条件,于s2
表的id
作等值匹配的对象就是xiaohaizi.s1.id
列(注意这里把数据库名也写出来了)。
有的时候与索引列进行等值匹配的对象是一个函数,比方说下边这个查询:
mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);
+----+-------------+-------+------------+------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9688 | 100.00 | NULL |
| 1 | SIMPLE | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | func | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+----------+---------+------+------+----------+-----------------------+
2 rows in set, 1 warning (0.00 sec)
我们看执行计划的第二条记录,可以看到对s2
表采用ref
访问方法执行查询,然后在查询计划的ref
列里输出的是func
,说明与s2
表的key1
列进行等值匹配的对象是一个函数。
rows
如果查询优化器决定使用全表扫描的方式对某个表执行查询时,执行计划的rows
列就代表预计需要扫描的行数,如果使用索引来执行查询时,执行计划的rows
列就代表预计扫描的索引记录行数。比如下边这个查询:
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 266 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
我们看到执行计划的rows
列的值是266
,这意味着查询优化器在经过分析使用idx_key1
进行查询的成本之后,觉得满足key1 > 'z'
这个条件的记录只有266
条。
filtered
之前在分析连接查询的成本时提出过一个condition filtering
的概念,就是MySQL
在计算驱动表扇出时采用的一个策略:
-
如果使用的是全表扫描的方式执行的单表查询,那么计算驱动表扇出时需要估计出满足搜索条件的记录到底有多少条。
-
如果使用的是索引执行的单表扫描,那么计算驱动表扇出的时候需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。
比方说下边这个查询:
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
| 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 266 | 10.00 | Using index condition; Using where |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)
从执行计划的key
列中可以看出来,该查询使用idx_key1
索引来执行查询,从rows
列可以看出满足key1 > 'z'
的记录有266
条。执行计划的filtered
列就代表查询优化器预测在这266
条记录中,有多少条记录满足其余的搜索条件,也就是common_field = 'a'
这个条件的百分比。此处filtered
列的值是10.00
,说明查询优化器预测在266
条记录中有10.00%
的记录满足common_field = 'a'
这个条件。
对于单表查询来说,这个filtered
列的值没什么意义,我们更关注在连接查询中驱动表对应的执行计划记录的filtered
值,比方说下边这个查询:
mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 9688 | 10.00 | Using where |
| 1 | SIMPLE | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | xiaohaizi.s1.key1 | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
从执行计划中可以看出来,查询优化器打算把s1
当作驱动表,s2
当作被驱动表。我们可以看到驱动表s1
表的执行计划的rows
列为9688
, filtered
列为10.00
,这意味着驱动表s1
的扇出值就是9688 × 10.00% = 968.8
,这说明还要对被驱动表执行大约968
次查询。
Extra
Extra
列是用来说明一些额外信息的,我们可以通过这些额外信息来更准确的理解MySQL
到底将如何执行给定的查询语句。
-
No tables used
当查询语句的没有
FROM
子句时将会提示该额外信息,比如:mysql> EXPLAIN SELECT 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 | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
-
Impossible WHERE
查询语句的
WHERE
子句永远为FALSE
时将会提示该额外信息,比方说:mysql> EXPLAIN SELECT * FROM s1 WHERE 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 | Impossible WHERE | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ 1 row in set, 1 warning (0.01 sec)
-
No matching min/max row
当查询列表处有
MIN
或者MAX
聚集函数,但是并没有符合WHERE
子句中的搜索条件的记录时,将会提示该额外信息,比方说:mysql> EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+ | 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 matching min/max row | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+ 1 row in set, 1 warning (0.00 sec)
-
Using index
当我们的查询列表以及搜索条件中只包含属于某个索引的列,也就是在可以使用索引覆盖的情况下,在
Extra
列将会提示该额外信息。比方说下边这个查询中只需要用到idx_key1
而不需要回表操作:mysql> EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a'; +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 8 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
-
Using index condition
有些搜索条件中虽然出现了索引列,但却不能使用到索引,比如下边这个查询:
SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';
其中的
key1 > 'z'
可以使用到索引,但是key1 LIKE '%a'
却无法使用到索引,在以前版本的MySQL
中,是按照下边步骤来执行这个查询的:-
先根据
key1 > 'z'
这个条件,从二级索引idx_key1
中获取到对应的二级索引记录。 -
根据上一步骤得到的二级索引记录中的主键值进行回表,找到完整的用户记录再检测该记录是否符合
key1 LIKE '%a'
这个条件,将符合条件的记录加入到最后的结果集。
但是虽然
key1 LIKE '%a'
不能组成范围区间参与range
访问方法的执行,但这个条件毕竟只涉及到了key1
列,所以设计MySQL
的大叔把上边的步骤改进了一下:-
先根据
key1 > 'z'
这个条件,定位到二级索引idx_key1
中对应的二级索引记录。 -
对于指定的二级索引记录,先不着急回表,而是先检测一下该记录是否满足
key1 LIKE '%a'
这个条件,如果这个条件不满足,则该二级索引记录压根儿就没必要回表。 -
对于满足
key1 LIKE '%a'
这个条件的二级索引记录执行回表操作。
我们说回表操作其实是一个随机
IO
,比较耗时,所以上述修改虽然只改进了一点点,但是可以省去好多回表操作的成本。设计MySQL
的大叔们把他们的这个改进称之为索引条件下推
(英文名:Index Condition Pushdown
)。如果在查询语句的执行过程中将要使用
索引条件下推
这个特性,在Extra
列中将会显示Using index condition
,比如这样:mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%b'; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 266 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.01 sec)
-
-
Using where
当我们使用全表扫描来执行对某个表的查询,并且该语句的
WHERE
子句中有针对该表的搜索条件时,在Extra
列中会提示上述额外信息。比如下边这个查询:mysql> EXPLAIN SELECT * FROM s1 WHERE common_field = 'a'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9688 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec)
当使用索引访问来执行对某个表的查询,并且该语句的
WHERE
子句中有除了该索引包含的列之外的其他搜索条件时,在Extra
列中也会提示上述额外信息。比如下边这个查询虽然使用idx_key1
索引执行查询,但是搜索条件中除了包含key1
的搜索条件key1 = 'a'
,还有包含common_field
的搜索条件,所以Extra
列会显示Using where
的提示:mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a'; +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 8 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
-
Using join buffer (Block Nested Loop)
在连接查询执行过程中,当被驱动表不能有效的利用索引加快访问速度,
MySQL
一般会为其分配一块名叫join buffer
的内存块来加快查询速度,也就是我们所讲的基于块的嵌套循环算法
,比如下边这个查询语句:mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9688 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9954 | 10.00 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ 2 rows in set, 1 warning (0.03 sec)
可以在对
s2
表的执行计划的Extra
列显示了两个提示:-
Using join buffer (Block Nested Loop)
:这是因为对表s2
的访问不能有效利用索引,只好退而求其次,使用join buffer
来减少对s2
表的访问次数,从而提高性能。 -
Using where
:可以看到查询语句中有一个s1.common_field = s2.common_field
条件,因为s1
是驱动表,s2
是被驱动表,所以在访问s2
表时,s1.common_field
的值已经确定下来了,所以实际上查询s2
表的条件就是s2.common_field = 一个常数
,所以提示了Using where
额外信息。
-
-
Not exists
当我们使用左(外)连接时,如果
WHERE
子句中包含要求被驱动表的某个列等于NULL
值的搜索条件,而且那个列又是不允许存储NULL
值的,那么在该表的执行计划的Extra
列就会提示Not exists
额外信息,比如这样:mysql> EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL; +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9688 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | xiaohaizi.s1.key1 | 1 | 10.00 | Using where; Not exists | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------------------+ 2 rows in set, 1 warning (0.00 sec)
上述查询中
s1
表是驱动表,s2
表是被驱动表,s2.id
列是不允许存储NULL
值的,而WHERE
子句中又包含s2.id IS NULL
的搜索条件,这意味着必定是驱动表的记录在被驱动表中找不到匹配ON
子句条件的记录才会把该驱动表的记录加入到最终的结果集,所以对于某条驱动表中的记录来说,如果能在被驱动表中找到1条符合ON
子句条件的记录,那么该驱动表的记录就不会被加入到最终的结果集,也就是说我们没有必要到被驱动表中找到全部符合ON子句条件的记录,这样可以稍微节省一点性能。 -
Using intersect(...)
、Using union(...)
和Using sort_union(...)
如果执行计划的
Extra
列出现了Using intersect(...)
提示,说明准备使用Intersect
索引合并的方式执行查询,括号中的...
表示需要进行索引合并的索引名称;如果出现了Using union(...)
提示,说明准备使用Union
索引合并的方式执行查询;出现了Using sort_union(...)
提示,说明准备使用Sort-Union
索引合并的方式执行查询。比如这个查询的执行计划:mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND key3 = 'a'; +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+-------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+-------------------------------------------------+ | 1 | SIMPLE | s1 | NULL | index_merge | idx_key1,idx_key3 | idx_key3,idx_key1 | 303,303 | NULL | 1 | 100.00 | Using intersect(idx_key3,idx_key1); Using where | +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+-------------------------------------------------+ 1 row in set, 1 warning (0.01 sec)
其中
Extra
列就显示了Using intersect(idx_key3,idx_key1)
,表明MySQL
即将使用idx_key3
和idx_key1
这两个索引进行Intersect
索引合并的方式执行查询。 -
Zero limit
当我们的
LIMIT
子句的参数为0
时,表示压根儿不打算从表中读出任何记录,将会提示该额外信息,比如这样:mysql> EXPLAIN SELECT * FROM s1 LIMIT 0; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+ | 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 | Zero limit | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+ 1 row in set, 1 warning (0.00 sec)
-
Using filesort
有一些情况下对结果集中的记录进行排序是可以使用到索引的,比如下边这个查询:
mysql> EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | index | NULL | idx_key1 | 303 | NULL | 10 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.03 sec)
这个查询语句可以利用
idx_key1
索引直接取出key1
列的10条记录,然后再进行回表操作就好了。但是很多情况下排序操作无法使用到索引,只能在内存中(记录较少的时候)或者磁盘中(记录较多的时候)进行排序,设计MySQL
的大叔把这种在内存中或者磁盘上进行排序的方式统称为文件排序(英文名:filesort
)。如果某个查询需要使用文件排序的方式执行查询,就会在执行计划的Extra
列中显示Using filesort
提示,比如这样:mysql> EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9688 | 100.00 | Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ 1 row in set, 1 warning (0.00 sec)
需要注意的是,如果查询中需要使用
filesort
的方式进行排序的记录非常多,那么这个过程是很耗费性能的,我们最好想办法将使用文件排序
的执行方式改为使用索引进行排序。 -
Using temporary
在许多查询的执行过程中,
MySQL
可能会借助临时表来完成一些功能,比如去重、排序之类的,比如我们在执行许多包含DISTINCT
、GROUP BY
、UNION
等子句的查询过程中,如果不能有效利用索引来完成查询,MySQL
很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表,在执行计划的Extra
列将会显示Using temporary
提示,比方说这样:mysql> EXPLAIN SELECT DISTINCT common_field 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 | 9688 | 100.00 | Using temporary | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 1 row in set, 1 warning (0.00 sec)
再比如:
mysql> EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9688 | 100.00 | Using temporary; Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ 1 row in set, 1 warning (0.00 sec)
不知道大家注意到没有,上述执行计划的
Extra
列不仅仅包含Using temporary
提示,还包含Using filesort
提示,可是我们的查询语句中明明没有写ORDER BY
子句呀?这是因为MySQL
会在包含GROUP BY
子句的查询中默认添加上ORDER BY
子句,也就是说上述查询其实和下边这个查询等价:EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field ORDER BY common_field;
如果我们并不想为包含
GROUP BY
子句的查询进行排序,需要我们显式的写上ORDER BY NULL
,就像这样:mysql> EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field ORDER BY NULL; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9688 | 100.00 | Using temporary | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 1 row in set, 1 warning (0.00 sec)
这回执行计划中就没有
Using filesort
的提示了,也就意味着执行查询时可以省去对记录进行文件排序的成本了。另外,执行计划中出现
Using temporary
并不是一个好的征兆,因为建立与维护临时表要付出很大成本的,所以我们最好能使用索引来替代掉使用临时表,比方说下边这个包含GROUP BY
子句的查询就不需要使用临时表:mysql> EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9688 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
从
Extra
的Using index
的提示里我们可以看出,上述查询只需要扫描idx_key1
索引就可以搞定了,不再需要临时表了。 -
Start temporary, End temporary
我们前边唠叨子查询的时候说过,查询优化器会优先尝试将
IN
子查询转换成semi-join
,而semi-join
又有好多种执行策略,当执行策略为DuplicateWeedout
时,也就是通过建立临时表来实现为外层查询中的记录进行去重操作时,驱动表查询执行计划的Extra
列将显示Start temporary
提示,被驱动表查询执行计划的Extra
列将显示End temporary
提示,就是这样:mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key3 FROM s2 WHERE common_field = 'a'); +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+------------------------------+ | 1 | SIMPLE | s2 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9954 | 10.00 | Using where; Start temporary | | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | xiaohaizi.s2.key3 | 1 | 100.00 | End temporary | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+------------------------------+ 2 rows in set, 1 warning (0.00 sec)
-
LooseScan
在将
In
子查询转为semi-join
时,如果采用的是LooseScan
执行策略,则在驱动表执行计划的Extra
列就是显示LooseScan
提示,比如这样:mysql> EXPLAIN SELECT * FROM s1 WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 > 'z'); +----+-------------+-------+------------+-------+---------------+----------+---------+-------------------+------+----------+-------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+-------------------+------+----------+-------------------------------------+ | 1 | SIMPLE | s2 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 270 | 100.00 | Using where; Using index; LooseScan | | 1 | SIMPLE | s1 | NULL | ref | idx_key3 | idx_key3 | 303 | xiaohaizi.s2.key1 | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+----------+---------+-------------------+------+----------+-------------------------------------+ 2 rows in set, 1 warning (0.01 sec)
-
FirstMatch(tbl_name)
在将
In
子查询转为semi-join
时,如果采用的是FirstMatch
执行策略,则在被驱动表执行计划的Extra
列就是显示FirstMatch(tbl_name)
提示,比如这样:mysql> EXPLAIN SELECT * FROM s1 WHERE common_field IN (SELECT key1 FROM s2 where s1.key3 = s2.key3); +----+-------------+-------+------------+------+-------------------+----------+---------+-------------------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-------------------+----------+---------+-------------------+------+----------+-----------------------------+ | 1 | SIMPLE | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9688 | 100.00 | Using where | | 1 | SIMPLE | s2 | NULL | ref | idx_key1,idx_key3 | idx_key3 | 303 | xiaohaizi.s1.key3 | 1 | 4.87 | Using where; FirstMatch(s1) | +----+-------------+-------+------------+------+-------------------+----------+---------+-------------------+------+----------+-----------------------------+ 2 rows in set, 2 warnings (0.00 sec)
来源:掘金小册子《MySQL 是怎样运行的:从根儿上理解 MySQL》