mysql 给表起别名_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 列不再命中。

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

3a262d0439e59707a586fb8e78a2f3e3.png

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;

然后得到输出:

bf986604583826af062ff1ae1ee6029e.png

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

2、EXPLAIN 字段解释

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

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

beb1684f4ed7783a71c832fd500effb4.png

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

f50fd832e9ff7cf53cc6972a5ef82277.png

partitions:访问的分区表信息。

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

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

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

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

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

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

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

0efdc8be8d22b7688e9fdea420f45964.png

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

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

af883908637fd1d5cd572f735d027369.png

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

40e44464eecb70d250d32e4072e4b9e1.png

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

8353f67869f1d2516cb37f26bd1cd854.png

ref:关联 id 等信息。

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

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

Extra:额外信息。

ab5c8f47b554b41376f38e7ccb4613b3.png

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

b9d634941b250fcc98582828a73ef4c4.png

索引长度计算

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

55c033af26bfc8323761a9eb9fb464e7.png

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 解析:

39eba891df230dd3d1de1ca9f64485f1.png

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列!

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值