EXPLAIN 之 key_len 计算

通常在优化SQL查询的时候，我们都会使用EXPLAIN分析SQL执行计划，通常来说当用到联合（组合）索引的时候我们如何判断索引完全用上呢？细心的小伙伴肯定发现EXPLAIN/desc执行计划中有一列 key_len ，它表示在本次查询中，所选择的索引长度有多少字节，通常我们可借此判断联合索引有多少列被选择了。换句话说，表示索引覆盖长度，用来判断联合索引应用的长度。

如何计算key_len=？

列的key_len长度，按照每列的最大预留长度（最大储值长度）来做的计算。单表越少越好，联合表越多越好。

影响最大存储预留长度（字节）因素：

1.和数据类型有关

2.是否非空
3.字符集（字符集类型）

话不多说了，我们直接上例子。表结构如下。^_

create table test (
id int  not null primary key auto_increment,
a int not null ,                    #4
b char(10) not null ,               #4*10       40  
c char(5) ,                         #5*4+1      21
d varchar(20) not null ,            #20*4+2     82
e varchar(10)                       #10*4+2+11  43
)engine=innodb charset=utf8mb4;

如果a,b,c,d,e的联合索引，全部覆盖到，key_len是多少

select 4+40+21+82+43;

如果a,b,c,d的联合索引，全部覆盖到，key_len是多少

select 4+40+21+82;

如果a,b,c的联合索引，全部覆盖到，key_len是多少

select 4+40+21;

如果a,b的联合索引，全部覆盖到，key_len是多少

select 4+40;

联合索引应用细节
举个栗子：

create table test (
id int not null primary key auto_increment,
a  int not null ,                  # 4
b  int ,                           # 5
c  char(10) not null ,             # 40
d  varchar(10),                    # 43
e  varchar(10) not null            # 42
)engine=innodb charset=utf8mb4;

图一

图二

喔，咋回事捏？
图一：在联合索引应用中，隐式转换导致索引失效
图二：在联合索引应用中，需要满足最左原则
a.建立联合索引时，选择重复值最少的列作为最左列。
b.使用联合索引时，查询条件中，必须包含最左列，才可能应用到联合索引
联合索引不同覆盖场景
举个栗子：

CREATE TABLE `t100w` (
  `id` int(11) DEFAULT NULL,
  `num` int(11) DEFAULT NULL,                # 5
  `k1` char(2) DEFAULT NULL,                 # 9
  `k2` char(4) DEFAULT NULL,                 # 17
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

全覆盖

情景1：走联合索引

desc select * from t100w where num=913759 and k1="ej" and k2="EFfg";

情景2：条件顺序无所谓

desc select * from t100w where k1="ej" and k2="EFfg" and num=913759;

情景3：in查询走联合索引

desc select * from t100w where k1="ej" and k2 in ("EFfg","abc") and num=913759;

情景4：最后一个非等于走联合索引

desc select * from t100w where num=913759 and k1="ej" and k2 like "EF%";

情景5：最后一个非等于走联合索引

desc select * from t100w where num=913759 and k1="ej" and k2 > "EFfg";

ii.部分覆盖

中间非等于不走联合索引

desc select * from t100w where num=913759 and k1 > "zz" and k2 like "EF%";

desc select * from t100w where num=913759 and k1="ej";

desc select * from t100w where num=913759 and  k2 like "EF%";

desc select * from t100w where num=913759 and k1 != "zz" and k2 like "EFfg";

更多精彩内容欢迎关注微信公众号