+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | t2 | NULL | index | NULL | idx_abc | 198 | NULL | 5 | 20.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
一个执行计划中,共有 12 个字段,每个字段都十分重要。简单介绍这 12 个字段:
- id:执行计划中每个操作的独特标识符。对于一条查询语句,每个操作都有其唯一的 id。然而,在多表连接时,一次解释中的多个记录可能具有相同的 id。
- select_type:操作的种类。常见种类包括 SIMPLE、PRIMARY、SUBQUERY、UNION 等。不同种类的操作会影响查询的执行效率。
- table:当前操作所涉及的表。
- partitions:当前操作所涉及的分区。
- type:表示查询时所使用的索引类型,包括 ALL、index、range、ref、eq_ref、const 等。
- possible_keys:表示可能被查询优化器选择使用的索引。
- key:表示查询优化器选择使用的索引。
- key_len:表示索引的长度。索引的长度越短,查询时的效率越高。
- ref:用来表示哪些列或常量被用来与 key 列中命名的索引进行比较。
- rows:表示此操作需要扫描的行数,即扫描表中多少行才能得到结果。
- filtered:表示此操作过滤掉的行数占扫描行数的百分比。该值越大,表示查询结果越准确。
- Extra:表示其他额外的信息,包括 Using index、Using filesort、Using temporary 等。
假如我们有如下一张表(MySQL Innodb 5.7):
CREATE TABLE `t2` (
`id` INT(11),
`a` varchar(64) NOT NULL,
`b` varchar(64) NOT NULL,
`c` varchar(64) NOT NULL,
`d` varchar(64) NOT NULL,
`f` varchar(64) DEFAULT NULL,
PRIMARY KEY(id),
UNIQUE KEY `f` (`f`),
KEY `idx_abc` (`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
首先,我们来分析几个重要字段的不同取值及其区别:
对于type字段,不同取值对查询性能有显著影响:
- system:表示系统表,数据量较小,通常不需要进行磁盘 IO。
- const:使用常数索引,MySQL 在查询时只会使用常数值进行匹配。比如:
explain select * from t2 where f='Paidaxing';
- 此时使用了唯一性索引进行唯一查询。
- eq_ref:唯一索引扫描,只会扫描索引树中的一个匹配行。比如:
explain select * from t1 join t2 on t1.id = t2.id where t1.f = 'P';
- 当连接操作中使用了唯一索引或主键索引,并且连接条件是基于这些索引的等值条件时,MySQL 通常会选择 eq_ref 连接类型,以提高查询性能。
- ref:非唯一索引扫描,只会扫描索引树中的一部分来查找匹配的行。比如:
explain select * from t2 where a = 'Paidaxing';
- 此时使用了非唯一索引进行查询。
- range:范围扫描,只会扫描索引树中的一个范围来查找匹配的行。比如:
explain select * from t2 where a > 'a' and a < 'c';
- 此时使用了索引进行性范围查询。
- index:全索引扫描,会遍历索引树来查找匹配的行。比如:
explain select c from t2 where b = 'P';
- 这里的 index 表示做了索引树扫描,效率并不高,不符合最左前缀匹配的查询。
- ALL:全表扫描,将遍历全表来找到匹配的行。比如:
explain select * from t2 where d = "ni";
- 此时使用了非索引字段进行查询。
需要注意的是,以上类型由快到慢排列为:system > const > eq_ref > ref > range > index > ALL。
接下来我们来探讨两个常被忽略但十分重要的字段:
possible_keys 和 key 字段:
- possible_keys(可能的索引):这一字段表示查询语句中可能可以利用的索引,但并不一定实际使用这些索引。possible_keys 列出了所有可能用于查询的索引,包括联合索引的组合。
- key(使用的索引):相对应地,key 字段表示实际被查询所使用的索引。如果在查询中使用了索引,则该字段将显示使用的索引名称。它是实际用于查询的索引。
接着说一个很重要!的字段,但是经常被忽略的字段 extra,这个字段描述了 MySQL 在执行查询时所做的一些附加操作。下面是 Extra 可能的取值及其含义:
extra 字段:
- Using where(使用 where):这表示 MySQL 在检索行后会再次进行条件过滤,使用 WHERE 子句进行进一步的筛选。这可能出现在列未被索引覆盖,或者 where 筛选条件涉及非索引的前导列或非索引列。
explain select * from t2 where d = "ni"; # 非索引字段查询
explain select d from t2 where b = "ni"; # 未索引覆盖,用联合索引的非前导列查询
- Using index(使用索引):MySQL 使用了覆盖索引来优化查询,只需扫描索引而无需回到数据表中检索行。
explain select b,c from t2 where a = "ni"; # 索引覆盖
- Using index condition(使用索引条件):表示查询在索引上执行了部分条件过滤,通常与索引下推有关。
explain select d from t2 where a = "ni" and b like "s%"; # 使用到索引下推。
- Using where; Using index(使用 where;使用索引):查询的列被索引覆盖,且 where 筛选条件是索引列之一,但不是索引的前导列,或者 where 筛选条件是索引列前导列的一个范围。
explain select a from t2 where b = "ni"; # 索引覆盖,但是不符合最左前缀
explain select b from t2 where a in ('a','d','sd'); # 索引覆盖,但是前导列是个范围
- Using join buffer(使用连接缓存):MySQL 使用了连接缓存。
explain select * from t1 join t2 on t1.id = t2.id where a = 's';
- Using temporary(使用临时表):MySQL 创建了临时表来存储查询结果,通常在排序或分组时发生。
explain select count(*),b from t2 group by b;
- Using filesort(使用文件排序):MySQL 将使用文件排序而不是索引排序,通常发生在无法使用索引进行排序时。
explain select count(*),b from t2 group by b;
- Using index for group-by(使用索引进行分组):MySQL 在分组操作中使用了索引。通常发生在分组操作涉及到索引中的所有列时。
- Using filesort for group-by(使用文件排序进行分组):MySQL 在分组操作中使用了文件排序。这通常发生在无法使用索引进行分组操作时。
- Range checked for each record(为每条记录检查范围):表示 MySQL 在使用索引范围查找时,需要对每一条记录进行检查。
- Using index for order by(使用索引进行排序):MySQL 在排序操作中使用了索引。通常发生在排序涉及到索引中的所有列时。
- Using filesort for order by(使用文件排序进行排序):MySQL 在排序操作中使用了文件排序。这通常发生在无法使用索引进行排序时。
- Using index for group-by; Using index for order by(在分组和排序中使用索引):表示 MySQL 在分组和排序操作中都使用了索引。
课外补充
如何判断一条 SQL 走没有索引
首先看 key 字段有没有值,有值表示用到了索引树,但是具体是怎么用的,还得看 type 和 extra。
简单说以下几个情况:
情况一:
explain select b from t2 where a in ('a','d','sd');
+----+-------+---------------+----------+--------------------------+
| id | type | possible_keys | key | Extra |
+----+-------+---------------+----------+--------------------------+
| 1 | index | NULL | idx_abc | Using where; Using index |
+----+-------+---------------+----------+--------------------------+
type = index,key = idx_abc,extra = 使用 where;使用 index。这表明查询利用了 idx_abc 的联合索引,但未严格遵守最左前缀匹配,或者虽然遵守了最左前缀,但在 a 字段上进行了范围查询。因此,实际上仍需扫描索引树,效率并不理想。
情况二:
explain select * from t2 where a = 'Paidaxing';
+----+-------+---------------+----------+--------------------------+
| id | type | possible_keys | key | Extra |
+----+-------+---------------+----------+--------------------------+
| 1 | ref | idx_abc | idx_abc | NULL |
+----+-------+---------------+----------+--------------------------+
表示用到了索引进行查询,并且用到的是 idx_abc 这个非唯一索引。
情况三:
explain select * from t2 where f = 'f';
+----+-------+---------------+----------+--------------------------+
| id | type | possible_keys | key | Extra |
+----+-------+---------------+----------+--------------------------+
| 1 | const | f | f | NULL |
+----+-------+---------------+----------+--------------------------+
表示用到了索引进行查询,并且用到的是 f 这个唯一索引。
情况四:
explain select b,c from t2 where a = 'Paidaxing';
+----+-------+---------------+----------+--------------------------+
| id | type | possible_keys | key | Extra |
+----+-------+---------------+----------+--------------------------+
| 1 | ref | idx_abc | idx_abc | Using index |
+----+-------+---------------+----------+--------------------------+
表示用到了索引进行查询,并且用到了 idx_abc 这个索引,而且查询用到了覆盖索引,不需要回表。
情况五:
explain select b,c from t2 where d = 'Paidaxing';
+----+-------+---------------+----------+--------------------------+
| id | type | possible_keys | key | Extra |
+----+-------+---------------+----------+--------------------------+
| 1 | ALL | NULL | NULL | Using where |
+----+-------+---------------+----------+--------------------------+
表示没有用到索引。