Explain之key_len长度计算 By 陆炫志

Explain之key_len长度计算

以前我很好奇通过执行计划Explain去分析SQL的时候看到的key_len值有时很小，有时看到很大，那时也不知道它是怎么算出来的，现在终于搞懂了，嘻。因为网上对key_len的长度的计算的资料也很少，官网也如此。我相信还有很多人不关心key_len的值是怎么来的，或者key_len有什么用的。key_len表示索引使用的字节数，根据这个值，就可以判断索引使用情况，特别是在组合索引的时候，判断所有的索引字段是否都被查询用到。好啦，废话不多说，我们通过例子来说明吧！

在说key_len长度计算长度之前，先和大家温习字符类型的知识：

char和varchar是日常使用最多的字符类型。char（N）用于保存固定长度的字符串，长度最大为255，比指定长度大的值将被截短，而比指定长度小的值将会用空格进行填补。

varchar（N）用于保存可以变长的字符串，长度最大为65535，只存储字符串实际实际需要的长度（它会增加一个额外字节来存储字符串本身的长度），varchar使用额外的1~2字节来存储值的的长度，如果列的最大长度小于或者等于255，则用1字节，否则用2字节。

char和varchar跟字符编码也有密切的联系，latin1占用1个字节，gbk占用2个字节，utf8占用3个字节。（不同字符编码占用的存储空间不同）

Latinl如下：

Gbk如下：

Utf8如下：

一、字符串类型的key_len计算

测试表的表结构如下：

复制代码

mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(10) DEFAULT NULL, `addr` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`), KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) 》

复制代码

1、索引字段为char类型的key_len计算：

（1）允许为Null时

复制代码

mysql>

复制代码

可以看到key_len=31,这个31是字节长度，它是怎么算出来的呢？让我们一起来分析下：
从表结构可以看到字符集是utf8，那就一个字符3个字节，那么char(10)代表的是10个字符相当30个字节，Null 占1个字节，char类型不需要额外的字节来存储值的的长度，所以得到：key_len：10x3+1=31,可以看到跟上面的结果一致的。

（2）不允许为Null时

复制代码

mysql>

复制代码

算法和上面差不多，只是字段不允许为Null，所以比上面的例子少了一个字节，key_len=10x3=30

2、索引字段为varchar类型且允许为Null时的key_len计算：

(1)允许为Null时

复制代码

mysql>

复制代码

还是utf8的字符集，所以还是一个字符3个字节，那么varchar(10)就是10个字符30个字节，Null占一个字节，由于varchar类型需要额外的1~2字节来存储值的的长度：所以key_len：10x3+1+2=33

（2）不允许为Null时

复制代码

mysql>

复制代码

相信大家都会算了吧，哈哈，不允许为Null就少了一个字符，所以Key_len：10x3+2=32

** 二、数值类型的key_len计算，先来回顾数值类型的一些根本知识：**

所有整数类型可以有一个可选(非标准)属性UNSIGNED。当你想要在列内只允许非负数和该列需要较大的上限数值范围时可以使用无符号值。如果设置了ZEROFILL扩展属性试，默认就有了无符号属性(UNSIGNED)，所以INT(1)与INT(11)后的括号中的字符表示显示宽度，整数列的显示宽度与MySQL需要用多少个字符来显示该列数值与该整数需要的存储空间的大小都没有关系，INT类型的字段能存储的数据上限还是2147483647(有符号型)和4294967295(无符号型)。其实当我们在选择使用INT的类型的时候，不论是INT(1)还是INT(11)，它在数据库里面存储的都是4个字节的长度

（1）int型允许为Null

复制代码

mysql> alter table t1 add age int(3); Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t1 add key (age); Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from t1 where age=20; +----+-------------+-------+------+---------------+------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-------+ | 1 | SIMPLE | t1 | ref | age | age | 5 | const | 1 | NULL | +----+-------------+-------+------+---------------+------+---------+-------+------+-------+ 1 row in set (0.00 sec)

mysql>

复制代码

分析：int(3)，就是4个字节，上面已经提及到，int（N）都是4个字节长度，允许为Null占一个字节，所以key_len：4+1=5

（2）不允许Null时（不能通过alter table t1 change age age int(3) not null，要drop掉才能添加为not null）

复制代码

mysql> alter table t1 change age age int(3) not null;
ERROR 1138 (22004): Invalid use of NULL value

mysql> alter table t1 drop age;
Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t1 add age int(3) not null;
Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t1 add key (age); Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from t1 where age=20; +----+-------------+-------+------+---------------+------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-------+ | 1 | SIMPLE | t1 | ref | age | age | 4 | const | 1 | NULL | +----+-------------+-------+------+---------------+------+---------+-------+------+-------+ 1 row in set (0.00 sec)

mysql>

复制代码

如果是Not null的话,int(N)的key_len都是4个字节
当结合可选扩展属性ZEROFILL使用时，默认补充的空格用零代替。例如，对于声明为INT(5) ZEROFILL的列，值4检索为00004，看例子：

复制代码

mysql> mysql> insert into aa (id,number) values (11,4); Query OK, 1 row affected (0.00 sec)

mysql> select * from aa; +------+--------+ | id | number | +------+--------+ | 11 | 00004 | +------+--------+ 1 rows in set (0.00 sec)

复制代码

其它数值类型也是同理的，相信大家找到共同点了，这里我就不作测试。

三、日期时间型的Key_len计算，先来回顾一下日期时间型的基本知知识：

Datetime类型key_len计算（针对MySQL5.5版本之前）：

1、允许为Null时：

复制代码

mysql>

复制代码

分析：因为datetime类型存储8个节点，允许为Null，所以多占一个字节，所以key_len: 8+1=9

2、不允许为null时：

复制代码

mysql> explain select * from t1 where date='2015-05-03 12:10:10'; +----+-------------+-------+------+---------------+------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-------+ | 1 | SIMPLE | t1 | ref | date | date | 8 | const | 1 | | +----+-------------+-------+------+---------------+------+---------+-------+------+-------+ 1 row in set (0.00 sec)

mysql>

复制代码

分析：当不为空时，datetime都是存储8个字节，所以key_len=8。

MySQL5.6 datetime的key_len计算：

1、允许为空时：

复制代码

mysql> select version(); +------------+ | version() | +------------+ | 5.6.10-log | +------------+ 1 row in set (0.00 sec)

复制代码

分析：mysql5.6的datetime已经不是存储8个字节了，应该存储5个字节了，允许为Null，所以加一个字节，所以key_len：5+1

2、不允许Null时：

复制代码

mysql>

复制代码

分析：不为Null时，则存储自身的字节大小，所以key_len=5

四、联合索引的key_len计算，在说联合索引计算之前，我们先回顾一个索引的限制

InnoDB: INNODB的索引会限制单独Key的最大长度为767字节，超过这个长度必须建立小于等于767字节的前缀索引。

MyISAM: MyISAM存储引擎的表，索引前缀的长度可以达到1000字节长。

前缀索引能提高索引建立速度和检索速度，但是无法使用：索引覆盖扫描和通过索引的排序

复制代码

mysql> show create table xuanzhi\G *************************** 1. row *************************** Table: xuanzhi Create Table: CREATE TABLE `xuanzhi` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(20) DEFAULT NULL, `addr` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`), KEY `name` (`name`,`addr`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

mysql>

复制代码

可以看到表结构里有一个联合索引name,那么上面的key_len是怎么算出来的呢，相信到现在，同学们都有计算的思路，好吧，我们算一下：
name的key_len计算：utf8:char(20)x3+null:1=61

addr的key_len计算： utf8:varchar(20)x3+null:1+2=63 (如果不明白为什么+2往前面再看一次)

联合索引name('name','addr') key_len：61+63=124

嘻嘻，我想到现在没多少人不会算了吧，通过key_len可以让我们知道它是否有充分利用索引

还有一些类型没有说到的，希望同学们自己测试一下，下面我总结一下计算公式：

复制代码

char和varchar类型key_len计算公式: varchr(N)变长字段且允许NULL = N * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段) varchr(N)变长字段且不允许NULL = N * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)
char(N)固定字段且允许NULL = N * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL) char(N)固定字段且允许NULL = N * ( character set：utf8=3,gbk=2,latin1=1) 数值数据的key_len计算公式： TINYINT允许NULL = 1 + 1(NULL) TINYINT不允许NULL = 1 SMALLINT允许为NULL = 2+1(NULL) SMALLINT不允许为NULL = 2 INT允许为NULL = 4+1(NULL) INT不允许为NULL = 4 日期时间型的key_len计算：（针对mysql5.5及之前版本） DATETIME允许为NULL = 8 + 1(NULL) DATETIME不允许为NULL = 8 TIMESTAMP允许为NULL = 4 + 1(NULL) TIMESTAMP不允许为NULL = 4

复制代码

还有一些没写出来，相信大家对key_len有一定的认识了，所以这里就不把所有的都写出来了。

总结：

一、INT型如果不结合可选扩展属性ZEROFILL使用，INT(1)和INT（N），它在数据库里面存储的都是4个字节的长度,当然N最大的上限

二、从上面的例子可以看到，定义表结构时，如果字段允许为NULL，会有额外的开销，所以建议字段尽量不要使用允许NULL，提高索引的使用效率

三、INNODB的索引会限制单独Key的最大长度为767字节，MyISAM索引前缀的长度可以达到1000字节长，如果order by也使用了索引则key_len不计算在内

参考资料：

http://www.cnblogs.com/gomysql/p/3616366.html

http://www.cnblogs.com/LMySQL/p/4525867.html

<<深入浅出MySQL>>

作者：陆炫志

出处：xuanzhi的博客 http://www.cnblogs.com/xuanzhi201111

小傅哥(微信：fustack)，公众号：bugstack虫洞栈 | bugstack.cn - 沉淀、分享、成长，让自己和他人都能有所收获！

📝 首页

🌏 知识星球：码农会锁

实战项目：「DDD+RPC分布式抽奖系统」、专属小册、问题解答、简历指导、架构图稿、视频课程

🐲 头条

💥 🎁 Lottery 抽奖系统 - 基于领域驱动设计的四层架构的互联网分布式开发实践
小傅哥的《重学 Java 设计模式》 - 全书彩印、重绘类图、添加内容
⭐小傅哥的《Java 面经手册》 - 全书5章29节，417页11.5万字，完稿&发版
小傅哥的《手撸 Spring》 - 通过带着读者手写简化版 Spring 框架，了解 Spring 核心原理
🌈小傅哥的《SpringBoot 中间件设计和开发》 - 小册16个中间件开发30个代码库

⛳ 目录

源码 - 公众号：bugstack虫洞栈 文章所涉及到的全部开源代码
Java
Spring
面向对象
中间件
- 5.1 SpringBoot 中间件开发
- 5.2 IDEA-Plugin
Netty 4.x
字节码编程
💯实战项目
部署 Dev-Ops
📚PDF 下载
关于

💋 精选

🐾 友链

转载分享

建立本开源项目的初衷是基于个人学习与工作中对 Java 相关技术栈的总结记录，在这里也希望能帮助一些在学习 Java 过程中遇到问题的小伙伴，如果您需要转载本仓库的一些文章到自己的博客，请按照以下格式注明出处，谢谢合作。

作者：小傅哥
链接：https://bugstack.cn
来源：bugstack虫洞栈

参与贡献

2021年10月24日，小傅哥 的文章全部开源到代码库 CodeGuide 中，与同好同行，一起进步，共同维护。

这里我提供 3 种方式：

提出 Issue ：在 Issue 中指出你觉得需要改进/完善的地方(能够独立解决的话，可以在提出 Issue 后再提交 PR )。
处理 Issue : 帮忙处理一些待处理的 Issue。
提交 PR：对于错别字/笔误这类问题可以直接提交PR，无需提交Issue 确认。

详细参考：CodeGuide 贡献指南 - 非常感谢你的支持，这里会留下你的足迹

与我联系 💌

加群交流 本群的宗旨是给大家提供一个良好的技术学习交流平台，所以杜绝一切广告！由于微信群人满 100 之后无法加入，请扫描下方二维码先添加作者 “小傅哥” 微信(fustack)，备注：加群。

微信：fustack

公众号(bugstack虫洞栈) - 沉淀、分享、成长，专注于原创专题案例，以最易学习编程的方式分享知识，让自己和他人都能有所收获。

公众号：bugstack虫洞栈

致谢

感谢以下人员对本仓库做出的贡献或者对小傅哥的赞赏，当然不仅仅只有这些贡献者，这里就不一一列举了。如果你希望被添加到这个名单中，并且提交过 Issue 或者 PR，请与我联系。

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Explain之key_len长度计算 By 陆炫志

Explain之key_len长度计算

转载分享

参与贡献

与我联系 💌

致谢

Clone this wiki locally