作者: 阿笠在健身 后端开发技术
今天程序猿码不停问了个问题:在MySQL的InnoDB引擎下,有表 test,表中有索引 idx_a_b_c('a', 'b', 'c') ,那么SQL SELECT * from test WHERE c = 1 and b > 1 and a = 1 的索引命中情况是怎样的呢?
大家一看,这不是很普通的索引最左匹配问题吗?首先,答案是肯定的,可以命中索引!虽然索引顺序是 a、b、c,但是因为SQL执行前,MySQL查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高,最后才生成真正的执行计划 SELECT * from test WHERE and a = 1 and b > 1 and c = 1 。由于 b 使用了范围查询,所以索引只命中了 a 和 b 两列,b 之后的 c 列不再命中。
码不停继续追问:嗯…… 口说无凭据,怎么证明呢?
EXPLAIN解析
对于SQL的执行分析,我们首先想到的就是 EXPLAIN 来解析。如果你还对 EXPLAIN 不熟悉,那该补补基础了。这里只做一个实战介绍,就以上述问题中的 SQL 举例。建表语句如下:
CREATE TABLE `test` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, KEY `index_abc` (`a`,`b`,`c`) USING BTREE, KEY `index_ad` (`a`,`d`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
1、执行 EXPLAIN 分析 SQL
首先,执行下面 SQL:
EXPLAIN select * from test where c = 1 and b > 1 and a = 1;
然后得到输出:
想要分析 SQL 就需要懂得每个字段的含义
2、EXPLAIN 字段解释
id:每个执行计划都有一个 id,如果是一个联合查询,这里还将有多个 id。
select_type:表示 SELECT 查询类型,这条SQL 是 SIMPLE(普通查询,即没有联合查询、子查询)类型。其他还有 PRIMARY(主查询)、UNION(UNION 中后面的查询)、SUBQUERY(子查询)等。
table:当前执行计划查询的 test 表,如果给表起别名了,则显示别名信息。
partitions:访问的分区表信息。
type:表示从表中查询到行所执行的方式,查询方式是 SQL 优化中一个很重要的指标,结果值从好到差依次是:system > const > eq_ref > ref > range > index > ALL。
system/const:表中只有一行数据匹配,此时根据索引查询一次就能找到对应的数据。
eq_ref:使用唯一索引扫描,常见于多表连接中使用主键和唯一索引作为关联条件。
ref:非唯一索引扫描,还可见于唯一索引最左原则匹配扫描。
range:索引范围扫描,比如,,between 等操作。
index:索引全表扫描,此时遍历整个索引树。
ALL:表示全表扫描,需要遍历全表来找到对应的行。
这里因为使用了 b > 条件,所以使用 range 类型。
possible_keys:可能使用到的索引。因为表中还有 index_ad 索引,所以可能用到的索引是两个。
key:实际使用到的索引。最终判断使用索引 index_abc.。
key_len:当前使用的索引的长度。(重要!重要!重要!重要的事情说三遍!!!)通过 key_len 便可以判断索引的命中情况。
ref:关联 id 等信息。
rows:查找到记录所扫描的行数。
filtered:查找到所需记录占总扫描记录数的比例。
Extra:额外信息。
通过上述结束,每个字段我们都明白什么含义。并且证明了虽然原始 sql 中abc的顺序是乱序的,但是我们还是可以命中索引 index_abc。但是具体命中索引中的哪几列呢?通过 key_len 又如何判断。
索引长度计算
因为联合索引的结构特点, 如果命中索引,那么命中的列只有可能是这几种情况:a、ab、abc。就像一个链,你无法在跳过 b 节点的情况下找到c节点。所以现在问题就转换成,如何确认命中索引 index_abc 是命中了 a 列、ab列 还是 abc 列。想要会分析,就需要掌握索引长度的计算方法了。
1、索引长度公式
1.所有的索引字段,如果没有设置not null,则需要加一个字节。
2.定长字段,int占四个字节、date占三个字节、char(n)占n个字符。
3.对于变成字段varchar(n),则有n个字符+两个字节。
4.不同的字符集,一个字符占用的字节数不同。latin1编码的,一个字符占用一个字节,gbk编码的,一个字符占用两个字节,utf8编码的,一个字符占用三个字节。
5.索引长度 char()、varchar()索引长度的计算公式:
Character Set:utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列长度 + 1(允许null) + 2(变长列)
2、举例:
1.SQL SELECT * from test WHERE c = 1 and b = 1 and a = 1 解析:
key_len = 4 (a是int类型 4字节)+ 1(a可为null 1字节) + 4(b是int类型 4字节) + 1(b可为null 1字节) + 4(c是int类型 4字节) + 1(c可为null 1字节) = 15
2.SQL SELECT * from test WHERE b = 4 and d = c 解析:
key_len = 20(utf8每个字符 3字节) * 3 + 2(varchar) + 1(d可为null) + 4 (a是int类型 4字节)+ 1(a可为null 1字节) = 68
3、确定命中列
explain 的结果 key_len 长度是 10,怎么得出来的呢?套用公式。
4 (a是int类型 4字节)+ 1(a可为null 1字节) + 4(b是int类型 4字节) + 1(b可为null 1字节) = 10
所以,我们得出最终结论,SQL SELECT * from test WHERE c = 1 and b > 1 and a = 1 命中了索引 index_abc 的 a和b列!
看到这里,码不停同学点了点头。