概述
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 `jdbc`.`key_len_demo` (`id`, `name`, `address`, `remark`) VALUES ('1', '张三', '上海道', '测试1');
INSERT INTO `jdbc`.`key_len_demo` (`id`, `name`, `address`, `remark`) VALUES ('2', '李四', '杭州道', '测试2');
INSERT INTO `jdbc`.`key_len_demo` (`id`, `name`, `address`, `remark`) VALUES ('3', '王五', '福建到道', '3000');
INSERT INTO `jdbc`.`key_len_demo` (`id`, `name`, `address`, `remark`) VALUES ('4', '赵柳', '杭州道', '2500');
上面的表结构非常简单,有一个主键索引(id字段),还有一个辅助索引(name字段)。下面将以此为例,来分析一下执行计划,看看key_len是如何计算的。
-
如果是单列索引不用去计算,因为没有意义;
-
如果是组合索引,知道key_len的长度是非常有意义的。
-
key_len越小,说明索引效果越好
单列索引的key_len计算
由上图可知,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)
下面分别执行以下两条SQL语句,具体如下
上图可知,第一条查询和第二条查询的执行计划中的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中添加一个字段,并添加一个联合索引,便于进行范围查询,如下如下:
给表key_len_demo添加create_time字段,并建立联合索引(create_time,remark)
接下来执行以下的SQL语句,分析执行计划中的key_len
可以看出用到了创建的联合索引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字段组成的联合索引的情况,在表中新增联合索引,具体如下:
执行以下SQL语句,分析执行计划
可以看出key_len的长度是123。索引是否完全用到了呢?稍微了解索引内容的,可以知道完全用到了。那么key_len是如何计算的呢?
address字段是char(20) DEFAULT NULL、remark字段是varchar(20) NOT NULL DEFAULT ''
从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语句,关注执行计划信息
如上图可知,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 | ken_len =8 | bigint占8个字节 |
char(30) utf8 | key_len=30*3+1 | utf8每个字符占3字节,允许为NULL时,+1字节 |
varchar(30) not null utf8 | ken_len=30*3+2 | utf8每个字符为3字节,变长数据类型+2字节 |
varchar(30) utf8 | ken_len=30*3+2+1 | utf8每个字符为3字节,允许为NULL时,+1字节,变长数据类型+2字节 |