我们在使用Explain查看SQL执行计划时,其中有一列为key_ken
EXPLAIN select * FROM user WHERE id = 1;
key_len表示使用的索引长度,key_len可以衡量索引的好坏,key_len越小 索引效果越好,那么key_len的长度是如何计算的?
常见的列类型长度计算:
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来判断联合索引是否生效。