vlambda博客
学习文章列表

MySQL索引命中分析和长度的计算你清楚吗?


今天程序猿码不停问了个问题:在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 列不再命中。

码不停继续追问:嗯…… 口说无凭据,怎么证明呢?

MySQL索引命中分析和长度的计算你清楚吗?


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;

然后得到输出:

MySQL索引命中分析和长度的计算你清楚吗?

想要分析 SQL 就需要懂得每个字段的含义


2、EXPLAIN 字段解释

id:每个执行计划都有一个 id,如果是一个联合查询,这里还将有多个 id。

select_type:表示 SELECT 查询类型,这条SQL 是 SIMPLE(普通查询,即没有联合查询、子查询)类型。其他还有 PRIMARY(主查询)、UNION(UNION 中后面的查询)、SUBQUERY(子查询)等。

MySQL索引命中分析和长度的计算你清楚吗?

table:当前执行计划查询的 test 表,如果给表起别名了,则显示别名信息。

MySQL索引命中分析和长度的计算你清楚吗?

partitions:访问的分区表信息。

type:表示从表中查询到行所执行的方式,查询方式是 SQL 优化中一个很重要的指标,结果值从好到差依次是:system > const > eq_ref > ref > range > index > ALL

system/const:表中只有一行数据匹配,此时根据索引查询一次就能找到对应的数据。

eq_ref:使用唯一索引扫描,常见于多表连接中使用主键和唯一索引作为关联条件。

ref:非唯一索引扫描,还可见于唯一索引最左原则匹配扫描。

range:索引范围扫描,比如,<,>,between 等操作。

index:索引全表扫描,此时遍历整个索引树。

ALL:表示全表扫描,需要遍历全表来找到对应的行。

MySQL索引命中分析和长度的计算你清楚吗?

这里因为使用了 b > 条件,所以使用 range 类型。

possible_keys:可能使用到的索引。因为表中还有 index_ad 索引,所以可能用到的索引是两个。

MySQL索引命中分析和长度的计算你清楚吗?

key:实际使用到的索引。最终判断使用索引 index_abc.。

MySQL索引命中分析和长度的计算你清楚吗?

key_len:当前使用的索引的长度。(重要!重要重要重要的事情说三遍!!!)通过 key_len 便可以判断索引的命中情况。

MySQL索引命中分析和长度的计算你清楚吗?

ref:关联 id 等信息。

rows:查找到记录所扫描的行数。

filtered:查找到所需记录占总扫描记录数的比例。

Extra:额外信息。

MySQL索引命中分析和长度的计算你清楚吗?

通过上述结束,每个字段我们都明白什么含义。并且证明了虽然原始 sql 中abc的顺序是乱序的,但是我们还是可以命中索引 index_abc。但是具体命中索引中的哪几列呢?通过 key_len 又如何判断。

MySQL索引命中分析和长度的计算你清楚吗?

索引长度计算

因为联合索引的结构特点, 如果命中索引,那么命中的列只有可能是这几种情况: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 解析:

MySQL索引命中分析和长度的计算你清楚吗?

key_len = 4 (a是int类型 4字节)+ 1a可为null 1字节 + 4(bint类型 4字节 + 1(b可为null 1字节 + 4(cint类型 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字节)+ 1a可为null 1字节 = 68

3、确定命中列

explain 的结果 key_len 长度是 10,怎么得出来的呢?套用公式。

4 (a是int类型 4字节)+ 1a可为null 1字节 + 4(bint类型 4字节 + 1(b可为null 1字节 = 10  

所以,我们得出最终结论,SQL SELECT * from test WHERE c = 1 and b > 1 and a = 1 命中了索引 index_abc 的 a和b列!

看到这里,码不停同学点了点头。


◆ ◆ ◆  ◆ 

关注并后台回复 “面试” 或者  “视频

即可免费获取最新2019BAT

大厂面试题和大数据微服务视频

如果文章有帮助到你,请点击再看或者转发支持!