MYSQL的explain执行计划中key_len计算方式

AaronJonah

已于 2023-06-15 14:39:48 修改

阅读量770

点赞数 1

分类专栏： mysql 文章标签： mysql 数据库 sql

于 2023-06-15 11:37:32 首次发布

本文链接：https://blog.csdn.net/weixin_44991038/article/details/131193861

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

零、引言

在执行计划中，key_len指的是使用的索引字段的长度。计算方式是将使用索引字段的长度相加，然后再加上索引类型所占用的字节数。这个值对于MySQL优化器来说非常重要，因为它可以帮助MySQL选择最优的索引。

如果key_len的值很大，那么查询就会变得很慢，因为MySQL需要读取更多的数据。因此，在创建索引时，要尽量减小key_len的值，以提高查询性能（key_len越小，说明索引效果越好）。

一、创建测试表和数据

在mysql库中，新建测试表key_len_test（表采用的utf8mb4字符集）

CREATE TABLE `key_len_test` (
  `id`                  bigint(32)          unsigned AUTO_INCREMENT COMMENT '主键ID',
  `age`                 bigint              not null comment '年龄',
  `name`                char(32)            NOT NULL COMMENT '姓名',
  `sex`                 char(32)            NULL COMMENT '性别 1-男、2-女',
  `address`             varchar(32)         NULL COMMENT '地址',
  `total_score`         bigint              NULL COMMENT '总成绩',
  `ext`                 varchar(512)        NOT NULL DEFAULT '' COMMENT '扩展信息',
  `create_time`         datetime            NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
  `update_time`         timestamp           NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录最后更新时间',
  PRIMARY KEY (`id`),  
  KEY `idx_age` (`age`),
  KEY `idx_name` (`name`),
  KEY `idx_sex` (`sex`),
  KEY `idx_address` (`address`),
  KEY `idx_total_score` (`total_score`),
  KEY `idx_ext` (`ext`),
  KEY `idx_created_time` (`create_time`),
  KEY `idx_modified_time` (`update_time`)
)  COMMENT='key_len测试表';

向表中插入一些数据，用于演示

insert into key_len_test(age, name, sex, address, total_score, ext) values (18, '张三', '1', '北京海淀', 600, '高考成绩');
insert into key_len_test(age, name, sex, address, total_score, ext) values (18, '李四', '2', '上海浦东', 590, '高考成绩');
insert into key_len_test(age, name, sex, address, total_score, ext) values (18, '王五', '1', '天津河西', 610, '高考成绩');
insert into key_len_test(age, name, sex, address, total_score, ext) values (18, '小明同学', '1', '北京朝阳', 700, '高考成绩');

上面的表比较简单，有一个主键索引id和其他普通辅助索引，下面以这个表为例，看下key_len如何计算的。

二、计算key_len

1、单列索引key_len计算

有上图可知，key_len的长度为128，那128怎么计算的呢？

在创建表时字段 “name” 语句 = `name` char(32) NOT NULL COMMENT '姓名'，表用的utf8mb4字符集，utf8字符集和utf8mb4字符集分别占3字节和4字节，字段name为char(32)，所以128来源 key_len= 32 * 4 =128。若字符集为utf8，则key_len= 32 * 3 = 96。

查看表创建脚本和表字符集：show create table key_len_test;

查看表中索引：show index from key_len_test;

a.比对not null 与 null 在计算key_len的不同

字段 `sex` char(32) NULL COMMENT '性别 1-男、2-女' 与图1中的name对比是not null 和null的区别，因为NULL 需要额外一个字节标记为空。129 = 32 * 4 + 1 = 129。

b.比对 varchar 与 char 在计算key_len的不同

与图2比，字段 `address` varchar(32) NULL COMMENT '地址'与字段sex 只是类型不同，但key_len却不一样，原因是 varchar类型字段为变长字段类型，还有长度信息，需占2个字节。所以 131 = 32 * 4 + 2 + 1 = 131。

同理，按照上面的方式可以验证其他类型单列索引的key_len情况。

字段类型索引	字节（NOT NULL）	字节（NULL）	说明
tinyint	1	2	因为NULL 需要额外一个字节标记为空
int	4	5	因为NULL 需要额外一个字节标记为空
bigint	8	9	因为NULL 需要额外一个字节标记为空
smallint	2	3	因为NULL 需要额外一个字节标记为空
mediumint	3	4	因为NULL 需要额外一个字节标记为空
float	4	5	因为NULL 需要额外一个字节标记为空
double	8	9	因为NULL 需要额外一个字节标记为空
date	3	4	因为NULL 需要额外一个字节标记为空
timestamp	4	5	因为NULL 需要额外一个字节标记为空
datetime	5	6	因为NULL 需要额外一个字节标记为空，在5.6中字段长度是5个字节

以上只是针对不受字符集影响的常见字段类型索引所占的字节的总结。CHAR、VARCHAR、TEXT、BLOB 类型会因表字符集不同，所占字节数不同。

2、联合索引key_len计算

a.将表中name和sex字段索引删除，添加联合索引key(name,sex)

-- 删除name和sex单列索引
alter table key_len_test drop index idx_name,drop index idx_sex ;
-- 添加name和sex联合索引
alter table key_len_test add index idx_name_sex(name,sex);

执行单个字段sql查询

执行两个字段sql查询

以上两个sql执行计划中key_len和ref不同，第二个sql的key_len为257 ，怎么计算出来的呢。

在建表脚本中name 为char(32) NOT NULL 、sex 为char(32) NULL ，从上面知道NULL 需1个字节来标识，所以第二条sql key_len = 32 * 4 + 32 * 4 + 1 = 257。通过计算两个字段索引都完全里使用。

b.添加时间字段联合索引

-- 添加name和create_time联合索引
alter table key_len_test add index idx_created_time_name(create_time,name);

执行联合索引sql查询时间范围

指定时间执行联合索引sql查询

根据执行结果可以看出，两个执行结果都用到了联合索引idx_created_time_name，但两个key_len是不一样的，是否充分利用到索引，从结果分析图4未充分使用索引，因为 mysql 5.6后 datetime 占5字节，说明图4 只用到了联合索引idx_created_time_name中created_time字段。而图5采用的是时间定值 key_len= 32 * 4 + 5 = 133，充分利用了联合索引。

c.char字段和varchar字段组成的联合索引

执行联合索引查询sql

根据结果来看，key_len为260。索引是否完全用到了呢？那么key_len是如何计算的呢？

`sex`  char(32) NULL COMMENT '性别 1-男、2-女',`address` varchar(32) NULL COMMENT '地址'

从2个字段的定义来看，2个允许NULL，一个char，一个varchar；

所以key_len= (32 * 4 + 1) + ( 32 * 4 + 2 + 1) =260，其中,+1是因为MySQL需要1个字节标识NULL，+2是因为address字段为varchar，是变长字段需要+2。所以完全用到了联合索引。

d.key_len使用索引的位置，是where 、order by、group by中的索引都关注呢还是只关注某一部分

1）order by 使用索引列

2） group by 使用索引列

key_len只指示了where中用于条件过滤时被选中的索引列，是不包含order by、group by这一部分被选中的索引列的。

三、总结

1、注意点

NULL 和NOT NULL 的所占字节不同，NULL 标记占1字节。NOT NULL标记不占字节。
变长数据类型，如varchar 、text、blob，还有长度信息，占2个字节。
char、varchar、blob、text等字符集来说，key_len的长度还和字符集有关。
- 在latin1字符集中，一个字符占用1个字节
- 在GBK字符集中，一个字符占用2个字节
- 在utf8字符集中，一个字符占用3个字节
- 在utf8mb4字符集中，一个字符占用4个字节。
  注意：当为 BLOB、TEXT 或其变体类型的列创建索引时，必须指定索引长度。这是因为这些数据类型的值可以非常大，并且 MySQL 需要知道要从哪里开始和结束索引。
  如果在创建索引时没有指定长度，就会出现 "BLOB/TEXT column 'column_name' used in key specification without a key length" 的错误提示。

2、在MySQL数据库中，常见类型执行计划中的key_len长度计算

索引列类型	latin1	GBK	utf8	utf8mb4	key_len计算	说明
char(30)	1	2	3	4	30 * n + 1	n：1字符占用n字节，不同字符集不同 +1：允许为NULL 占1字节
varchar(30)	1	2	3	4	30 * n + 2 + 1	n：1字符占用n字节，不同字符集不同 +1：允许为NULL 占1字节 +2：变长数据类型需2字节占位
blob(30)	1	2	3	4	30 * n + 2 + 1	n：1字符占用n字节，不同字符集不同 +1：允许为NULL 占1字节 +2：变长数据类型需2字节占位
text(30)	1	2	3	4	30 * n + 2 + 1	n：1字符占用n字节，不同字符集不同 +1：允许为NULL 占1字节 +2：变长数据类型需2字节占位
int	4	4	4	4	4 + 1	+1：允许为NULL 占1字节
bigint	4	8	8	8	8 + 1	+1：允许为NULL 占1字节