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 `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个字节

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字节