Explain执行计划key_len详解

我们在使用Explain查看SQL执行计划时，其中有一列为key_ken

EXPLAIN select * FROM user WHERE id = 1;

key_len表示使用的索引长度，key_len可以衡量索引的好坏，key_len越小 索引效果越好，那么key_len的长度是如何计算的？

常见的列类型长度计算：

列类型	是否为空	长度	key_len	备注
tinyint	允许Null	1	key_len = 1+1	允许NULL，key_len长度加1
tinyint	不允许Null	1	key_len = 1	不允许NULL
int	允许Null	4	key_len = 4+1	允许NULL，key_len长度加1
int not null	不允许Null	4	key_len = 4	不允许NULL
bigint	允许Null	8	key_len = 8+1	允许NULL，key_len长度加1
bigint not null	不允许Null	8	key_len = 8	不允许NULL
char(1)	允许Null	utf8mb4=4,utf8=3,gbk=2	key_len = 1*3 + 1	允许NULL，字符集utf8，key_len长度加1
char(1) not null	不允许Null	utf8mb4=4,utf8=3,gbk=2	key_len = 1*3	不允许NULL，字符集utf8
varchar(10)	允许Null	utf8mb4=4,utf8=3,gbk=2	key_len = 10*3 + 2 + 1	动态列类型，key_len长度加2，允许NULL，key_len长度加1
varchar(10) not null	不允许Null	utf8mb4=4,utf8=3,gbk=2	key_len = 10*3+ 2	动态列类型，key_len长度加2

 

CREATE TABLE `user` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` int(2) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_name` (`name`),
  KEY `idx_age` (`age`),
  KEY `idx_sex` (`sex`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

id字段类型为bigint，长度为8，id为主键，不允许Null ，key_len = 8 。

EXPLAIN select * FROM user WHERE id = 1;

name的字段类型是varchar(10)，允许Null，字符编码是utf8，一个字符占用3个字节，varchar为动态类型，key长度加2，key_len = 10 * 3 + 2 + 1 = 33 。

EXPLAIN select * FROM user WHERE name = '张三';

age的字段类型是int，长度为4，允许Null ，key_len = 4 + 1 = 5 。

EXPLAIN select * FROM user WHERE age = 19;

sex的字段类型是char(1)，允许Null ，字符编码是utf8，一个字符占用3个字节，key_len = 1 * 3 + 1 = 4 。

EXPLAIN select * FROM user WHERE sex = '1';

 

备注：

VARCHAR（TEXT\BLOB不允许整列创建索引，如果创建部分索引，也被视为动态列类型），其key_len还需要再加 2 bytes

联合索引key_len计算

我们删除user表其他辅助索引，建立一个联合索引

ALTER TABLE user DROP INDEX `idx_name`, DROP INDEX `idx_age`, DROP INDEX `idx_sex`;

ALTER TABLE user ADD INDEX `idx_name_age`(`name`, `age`);

1、部分索引生效的情况

我们使用name进行查询

EXPLAIN select * FROM user WHERE name = '张三'; 

由于联合索引，根据最左匹配原则，使用到索引只有name这一列，name的字段类型是varchar(10)，允许Null，字符编码是utf8，一个字符占用3个字节，varchar为动态类型，key长度加2，key_len = 10 * 3 + 2 + 1 = 33 。

2、联合索引完全使用索引的情况 

EXPLAIN select * FROM user WHERE name = '张三' AND age = 19; 

 

由于联合索引，使用到（name，age）联合索引，name的字段类型是varchar(10)，允许Null，字符编码是utf8，一个字符占用3个字节，varchar为动态类型，key长度加2，key_len = 10 * 3 + 2 + 1 = 33 ，age的字段类型是int，长度为4，允许Null ，key_len = 4 + 1 = 5 。联合索引的key_len 为 key_len = 33 + 5 = 38。

我们可以根据key_len来判断联合索引是否生效。