MySQL查询计划key_len如何计算

文章目录

最近在分析一个联合索引执行效率，其中key_len可以分析这个sql可以用到联合索引的哪些列。事后收集资料总结下。

key_len 解释

desc 中的 key_len 表示使用的索引长度，是以字节为单位。根据这个值，就可以判断索引使用情况，特别在使用联合索引的时候，判断有多少的索引字段能被查询用到。

key_len 规则

  • 如果索引列是字符型，比如 CHAR(M) 类型占用空间为 M * Maxlen 。

    • 字符型计算key_len需要考虑字符集，Maxlen 表示某个字符集中表示一个字符最多需要使用的字节数，utf8 Maxlen 为3，utf8mb4 Maxlen 为4。
    • 如果索引列是变长的(比如 varchar,varbinary)，则在索引列数据类型本身占用空间的基础上再加 2 ,比如 varchar(3) utf8mb4 not null，则实际占用空间是 3*4+2 = 14。
    • 如果索引列可以为空，则在索引列数据类型本身占用空间基础上加1。比如 varchar(3) utf8mb4 null，则实际占用空间是 3*4+2+1 = 15。

  • 如果索引列是整数类型见下图：
    在这里插入图片描述

  • 如果索引列是日期和时间类型见下图，mysql版本也对存储空间有不同要求：
    在这里插入图片描述
    MySQL5.6.4及之后版本，TIME、DATETIME、TIMESTAMP这几种类型添加了对毫秒、微妙的支持。由于毫秒、微秒都不到1秒，所以也被称为小数秒，MySQL最多支持6位小数秒的精度，比如DATETIME(0)表示精确到秒，DATETIME(3)表示精确到毫秒，DATETIME(5)表示精确到10微秒。所以如果你在使用TIME、DATETIME、TIMESTAMP这几种类型的时候精确到了小数秒，那么需要额外的存储空间，不同的小数秒精度需要的存储空间不同，如下表：
    在这里插入图片描述

  • 如果索引列是浮点数类型见下图：
    在这里插入图片描述

  • 如果索引列是定点数类型：
    DECIMAL(M, D) M指的是总的位数，D指的就是小数位数。M的范围是1 - 65，D的范围是0 - 30，且D的值不能超过M。 定点数是精确的小数，小数点前与小数点后的数字分开存储,且以9位数为1组,用4个字节保存,如果低于9位数,需要的字节数如下：
    在这里插入图片描述
    例如：

  • decimal(20,6)=> 小数点左边14位,小数点右边6位 => 小数点左边分组为5 + 9,需要3个字节+4个字节存储,小数点右边一个分组,需要3个字节存储 => 总共需要10个字节

  • decimal(18,9)=> 小数点左边9位数,小数点右边9位数 => 分别使用4个字节存储 => 共需要 8个字节

  • decimal(18,2)=> 小数点左边16位数,小数点右边2位数 => 分组为7 + 9,需要8个字节存储,小数点右边1个字节存储 => 共需要9个字节

key_len 实战

最后用一个实战例子计算一下联合索引用了哪几列。

联合索引：

create index idx_user_id_task_code_is_deleted_task_complete_status
    on u_task_progress (user_id, task_code, is_deleted, task_complete_status);

各列的ddl定义：

user_id                  bigint(50)            not null comment '用户ID'
task_code                varchar(50)           not null comment '任务编码'
is_deleted               tinyint(10) default 0 not null comment '是否删除,默认0,1:删除'
task_complete_status     tinyint(10) default 1 not null comment '任务完成状态，1:完成中，2:已完成，默认0'

某sql执行后 key_len 为211,现在分析一下这个联合索引执行了哪几列。

  • user_id为bigint not null，所以是 8
  • task_code为变长字符型mb4 not null，所以是 4*50+2=202
  • is_deleted为tinyint not null，所以是 1

最后加起来为211，所以只走了前面三个索引字段。key_len有助于了解我们设置的索引是否合理，是不是很方便？

鸣谢

MySQL查询计划key_len全知道
Mysql 索引 key_len 计算方式最强解析
mysql的日期时间类型及精度问题

修订记录

2022.11.4 mysql的日期时间类型占用空间根据mysql版本进行修订，还修改了一些文字表达错误