概述
key_len表示索引使用的字节数,根据这个值可以判断索引的使用情况,特别是在使用联合索引的时候,判断该索引有多少部分被使用到非常重要。
key_len的长度计算公式很重要(key_len越小,说明索引效果越好)
准备结构和数据
在MySQL数据库中,新建表,表名为key_len_demo
CREATE TABLE `key_len_demo` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, `name` CHAR(20) NOT NULL DEFAULT '', `address` CHAR(20) DEFAULT NULL, `remark` VARCHAR(20) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `key_len_demo` (`id`, `name`, `address`, `remark`) VALUES ('1', '张三', '上海道', '测试1'); INSERT INTO `key_len_demo` (`id`, `name`, `address`, `remark`) VALUES ('2', '李四', '杭州道', '测试2'); INSERT INTO `key_len_demo` (`id`, `name`, `address`, `remark`) VALUES ('3', '王五', '福建到道', '3000'); INSERT INTO `key_len_demo` (`id`, `name`, `address`, `remark`) VALUES ('4', '赵柳', '杭州道', '2500');
上面的表结构非常简单,有一个主键索引(id字段),还有一个辅助索引(name字段)。下面将以此为例,来分析一下执行计划,看看key_len是如何计算的。
如果是单列索引不用去计算,因为没有意义;
如果是组合索引,知道key_len的长度是非常有意义的。
key_len越小,说明索引效果越好
单列索引的key_len计算
explain select * from key_len_demo where name='张三'; #结果如下 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE key_len_demo ref name name 60 const 1 Using index condition
key_len的长度是60,那么这个60是如何计算出来的呢?
在创建key_len_demo表的语句中,==name CHAR(20) NOT NULL DEFAULT ‘’==定义了name字段为char(20),且非空
表用的是utf8字符集,我们知道utf8字符集占用3个字节,name字段又是char(20),所以可知60的来源是key_len=20*3=60
同理,按照上面的思路可以验证其他单列索引key_len的情况
1)整数类型
tinyint类型的索引长度,在NOT NULL 和 NULL 的时候 分别是1和2,tinyint字段长度为1,因为NULL 需要额外一个字节标记为空
bigint类型的索引长度,同样是 NOT NULL 和 NULL值的时候,分别是8和9
smallint类型索引长度,同样是 NOT NULL 和 NULL值的时候,分别是2和3 smallint长度为2,允许为空需要一个字节标记
mediumint类型索引长度,同样是 NOT NULL 和 NULL值的时候,分别是3和4
2)浮点数类型
float类型的索引长度,NOT NULL和NULL的时候,分别是4和5
double类型的索引长度,NOT NULL和NULL的时候,分别是8和9
3)时间类型
date类型的索引长度,在NOT NULL和NULL的时候,分别是3和4
timestamp类型的时候索引长度,在NOT NULL 和 NULL的时候,分别是4和5
联合索引的key_len计算
案例1
现将key_len_demo表上name这个字段的索引去掉,添加一个联合索引key(name,address)
explain select * from key_len_demo where name='张三'; #结果如下 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE key_len_demo ref name name 60 const 1 Using index condition explain select * from key_len_demo where name='张三' and address='上海道'; #结果如下 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE key_len_demo ref name name 121 const,const 1 Using index condition
第一条查询和第二条查询的执行计划中的key_len和ref不一样?为什么第二条SQL语句的key_len为121,这是如何计算的呢?
在建表语句中,address字段信息为char(20) DEFAULT NULL,可以发现address字段的定义为DEFAULT NULL,其他没有变化。所以MySQL需要1个字节来标识NULL,所以第二条SQL的key_len=20*3+(20*3+1)=121,通过计算,可知2个字段的索引完全用上了。
案例2
在表key_len_demo中添加一个字段,并添加一个联合索引,便于进行范围查询,如下如下:
ALTER TABLE `key_len_demo` ADD COLUMN `create_time` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP AFTER `remark`; ALTER TABLE `key_len_demo` ADD INDEX `idx_key_create_time_remark` (`create_time`, `remark`) USING BTREE ;
explain select * from key_len_demo where create_time>'2020-03-01 00:00:00' and create_time<'2020-04-03 00:00:00' group by remark order by null; #结果如下 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE key_len_demo range idx_key_create_time_remark idx_key_create_time_remark 4 null 1 Using index condition; Using temporary
可以看出用到了创建的联合索引idx_key_create_time_remark,但是是否完全用到了呢?实际上,从type可知是一个范围查询,肯定没有完全用到。后面字段的索引就用不到,若果这里不order by null,还会看到Using filesort。
我们知道timestamp占用4个字节,显而易见,看见key_len是4,说明只用到了联合索引idx_key_create_time_remark中的create_time字段。
案例3
再看分析由char字段和varchar字段组成的联合索引的情况,在表中新增联合索引,具体如下:
ALTER TABLE `key_len_demo` ADD INDEX `address` (`address`, `remark`) USING BTREE ;
执行以下SQL语句,分析执行计划
explain select * from key_len_demo where remark='3000' and address='上海道'; #结果如下 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE key_len_demo ref address address 123 const,const 1 Using index condition
从2个字段的定义来看,1个允许NULL,一个NOT NULL;一个char,一个varchar
所以key_len=(20*3+1)+(20*3+2)=123,其中,+1是因为MySQL需要1个字节标识NULL,+2是因为remark字段为varchar,是变长字段需要+2。
案例4
再来分析一下,key_len关注索引的位置,是where 、order by、group by中的索引都关注呢还是只关注某一部分?
建立联合索引idx_key_address_remark(address,remark),执行下面的SQL语句,关注执行计划信息
explain select * from key_len_demo where address='上海道' order by remark; #结果如下 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE key_len_demo ref address address 61 const 1 Using index condition; Using where explain select remark from key_len_demo where address='上海道' group by remark; #结果如下 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE key_len_demo ref address address 61 const 1 Using where; Using index
key_len只指示了where中用于条件过滤时被选中的索引列,是不包含order by、group by这一部分被选中的索引列的。
总结
在计算key_len时,需要考虑以下几点:
1)注意索引字段的附加信息
可以分为变长和定长数据类型,当索引字段为定长数据类型时,如char、int、datetime,需要有是否为空的标记,这个标记占用1个字节(对于not null的字段来说,则不需要这1个字节)
对于变长数据类型,比如varchar,除了是否为空的标记之外,还需要有长度信息,需要占用2个字节
char、varchar、blob、text等字符集来说,key_len的长度还和字符集有关
在latin1字符集中,一个字符占用1个字节
在GBK字符集中,一个字符占用2个字节
在utf8字符集中,一个字符占用3个字节
3) 整数类型、浮点数类型、时间类型的索引长度
NOT NULL=字段本身的字段长度
NULL=字段本身的字段长度+1,因为需要有是否为空的标记,这个标记需要占1个字节
datetime类型在5.6中字段长度是5个字节
varchr(20)变长字段且允许NULL
20 * ( character set:utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
varchr(20)变长字段且不允许NULL
20 *( character set:utf8=3,gbk=2,latin1=1)+2(变长字段)
char(20)固定字段且允许NULL
20 * ( character set:utf8=3,gbk=2,latin1=1)+1(NULL)
char(20)固定字段且不允许NULL
20 * ( character set:utf8=3,gbk=2,latin1=1)
在MySQL数据库中,执行计划中的key_len长度计算公式如下:
列类型 | key_len计算公式 | 说明 |
int | key_len=4+1 | int占用4字节,允许为null时,+1字节 |
bigint not null | key_len=8 | bigint占用8字节 |
char(30) utf8 | key_len=30*3+1 | utf8每个字符占用3字节,允许为null时,+1字节 |
varchar(30) not null utf8 | key_len=30*3+2 | utf8每个字符占用3字节,变长数据类型+2字节 |
varchar(30) utf8 | key_len=30*3+2+1 | utf8每个字符占用3字节,允许为null时,+1字节,变长数据类型+2字节 |
- 本文固定链接: https://www.phpmianshi.com/?id=5128
- 转载请注明: admin 于 PHP面试网 发表
《本文》有 0 条评论