首页 > mysql > mysql执行计划explain中key_len计算方式
2017
06-13

mysql执行计划explain中key_len计算方式

概述

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)


下面分别执行以下两条SQL语句,具体如下

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 ;

给表key_len_demo添加create_time字段,并建立联合索引(create_time,remark)

接下来执行以下的SQL语句,分析执行计划中的key_len

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

可以看出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语句,关注执行计划信息

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+1int占用4字节,允许为null时,+1字节
bigint not nullkey_len=8bigint占用8字节
char(30) utf8key_len=30*3+1utf8每个字符占用3字节,允许为null时,+1字节
varchar(30) not null utf8key_len=30*3+2utf8每个字符占用3字节,变长数据类型+2字节
varchar(30) utf8key_len=30*3+2+1utf8每个字符占用3字节,允许为null时,+1字节,变长数据类型+2字节


本文》有 0 条评论

留下一个回复