下面是一次 explain 返回的一条 SQL 语句的执行计划的内容:
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
| 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 个字段:
假如我们有如下一张表(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字段,不同取值对查询性能有显著影响:
explain select * from t2 where f='Paidaxing';
explain select * from t1 join t2 on t1.id = t2.id where t1.f = 'P';
explain select * from t2 where a = 'Paidaxing';
explain select * from t2 where a > 'a' and a < 'c';
explain select c from t2 where b = 'P';
explain select * from t2 where d = "ni";
需要注意的是,以上类型由快到慢排列为:system > const > eq_ref > ref > range > index > ALL
。
接下来我们来探讨两个常被忽略但十分重要的字段:
possible_keys 和 key 字段:
接着说一个很重要!的字段,但是经常被忽略的字段 extra,这个字段描述了 MySQL 在执行查询时所做的一些附加操作。下面是 Extra 可能的取值及其含义:
extra 字段:
explain select * from t2 where d = "ni"; # 非索引字段查询
explain select d from t2 where b = "ni"; # 未索引覆盖,用联合索引的非前导列查询
explain select b,c from t2 where a = "ni"; # 索引覆盖
explain select d from t2 where a = "ni" and b like "s%"; # 使用到索引下推。
explain select a from t2 where b = "ni"; # 索引覆盖,但是不符合最左前缀
explain select b from t2 where a in ('a','d','sd'); # 索引覆盖,但是前导列是个范围
explain select * from t1 join t2 on t1.id = t2.id where a = 's';
explain select count(*),b from t2 group by b;
explain select count(*),b from t2 group by b;
首先看 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 |
+----+-------+---------------+----------+--------------------------+
表示没有用到索引。
好了,本章节到此告一段落。希望对你有所帮助,祝学习顺利。
我正在参与2024腾讯技术创作特训营最新征文,快来和我瓜分大奖!
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。