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> explain select * from t1 where name='xuanzhi'; +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+ | 1 | SIMPLE | t1 | ref | name | name | 31 | const | 1 | Using index condition | +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+ 1 row in set (0.00 sec) mysql>
可以看到key_len=31,这个31是字节长度，它是怎么算出来的呢？让我们一起来分析下：
从表结构可以看到字符集是utf8，那就一个字符3个字节，那么char(10)代表的是10个字符相当30个字节，Null 占1个字节，char类型不需要额外的字节来存储值的的长度，所以得到：key_len：10x3+1=31,可以看到跟上面的结果一致的。
（2）不允许为Null时
mysql> alter table t1 change name name char(10) not null; Query OK, 2 rows affected (0.06 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> explain select * from t1 where name='xuanzhi'; +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+ | 1 | SIMPLE | t1 | ref | name | name | 30 | const | 1 | Using index condition | +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+ 1 row in set (0.01 sec) mysql>
算法和上面差不多，只是字段不允许为Null，所以比上面的例子少了一个字节，key_len=10x3=30
2、索引字段为varchar类型且允许为Null时的key_len计算：
(1)允许为Null时
mysql> alter table t1 change name name varchar(10); Query OK, 2 rows affected (0.06 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> explain select * from t1 where name='xuanzhi'; +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+ | 1 | SIMPLE | t1 | ref | name | name | 33 | const | 1 | Using index condition | +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+ 1 row in set (0.02 sec) mysql>
还是utf8的字符集，所以还是一个字符3个字节，那么varchar(10)就是10个字符30个字节，Null占一个字节，由于varchar类型需要额外的1~2字节来存储值的的长度：所以key_len：10x3+1+2=33
（2）不允许为Null时
mysql> alter table t1 change name name varchar(10) not null; Query OK, 2 rows affected (0.07 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> explain select * from t1 where name='xuanzhi'; +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+ | 1 | SIMPLE | t1 | ref | name | name | 32 | const | 1 | Using index condition | +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+ 1 row in set (0.00 sec) 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> desc aa; +--------+--------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------------------+------+-----+---------+-------+ | id | int(3) | YES | | NULL | | | number | int(5) unsigned zerofill | YES | | NULL | | +--------+--------------------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) 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> desc t1; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | date | datetime | YES | MUL | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) 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 | 9 | const | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ 1 row in set (0.00 sec) mysql>
分析：因为datetime类型存储8个节点，允许为Null，所以多占一个字节，所以key_len: 8+1=9
2、不允许为null时：
mysql> desc t1; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | date | datetime | NO | MUL | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) 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) mysql> desc t1; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | date | datetime | YES | MUL | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) 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 | 6 | const | 1 | NULL | +----+-------------+-------+------+---------------+------+---------+-------+------+-------+ 1 row in set (0.00 sec)
分析：mysql5.6的datetime已经不是存储8个字节了，应该存储5个字节了，允许为Null，所以加一个字节，所以key_len：5+1
2、不允许Null时：
mysql> alter table t1 modify date datetime not null; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 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 | 5 | const | 1 | NULL | +----+-------------+-------+------+---------------+------+---------+-------+------+-------+ 1 row in set (0.00 sec) 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> explain select * from xuanzhi where name='xuanzhi' and addr='shanghai'; +----+-------------+---------+------+---------------+------+---------+-------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+-------------+------+--------------------------+ | 1 | SIMPLE | xuanzhi | ref | name | name | 124 | const,const | 1 | Using where; Using index | +----+-------------+---------+------+---------------+------+---------+-------------+------+--------------------------+ 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 您的支持是对博主最大的鼓励，感谢您的认真阅读。本文版权归作者所有，欢迎转载，但请保留该声明。
|
