MySQL Explain执行计划key_len详解(特意针对date和datetime详细测试说明)
我们在使用Explain查看SQL执行计划时，其中有一列为key_ken
key_len表示使用的索引长度，那么key_len的长度是如何计算的？ 对于复合索引，通过key_len可以看出sql使用了复合索引的哪几个字段。
常见的列类型长度计算：
| 列类型 | 是否为空 | 长度 | key_len | 备注 |
|---|---|---|---|---|
| tinyint | 允许Null | 1 | key_len = 1+1 | 允许NULL，key_len长度加1 |
| tinyint | 不允许Null | 1 | key_len = 1 | 不允许NULL |
| int | 允许Null | 4 | key_len = 4+1 | 允许NULL，key_len长度加1 |
| int not null | 不允许Null | 4 | key_len = 4 | 不允许NULL |
| bigint | 允许Null | 8 | key_len = 8+1 | 允许NULL，key_len长度加1 |
| bigint not null | 不允许Null | 8 | key_len = 8 | 不允许NULL |
| char(1) | 允许Null | utf8mb4=4,utf8=3,gbk=2 | key_len = 1*3 + 1 | 允许NULL，字符集utf8，key_len长度加1 |
| char(1) not null | 不允许Null | utf8mb4=4,utf8=3,gbk=2 | key_len = 1*3 | 不允许NULL，字符集utf8 |
| varchar(10) | 允许Null | utf8mb4=4,utf8=3,gbk=2 | key_len = 10*3 + 2 + 1 | 动态列类型，key_len长度加2，允许NULL，key_len长度加1 |
| varchar(10) not null | 不允许Null | utf8mb4=4,utf8=3,gbk=2 | key_len = 10*3+ 2 | 动态列类型，key_len长度加2 |
其他:
- 所有的索引字段，如果设置允许null，key_len则需要加1。
- char和varchar型的字段长度，char和varchar是日常使用最多的字符类型。char（N）用于保存固定长度的字符串，长度最大为255，比指定长度大的值将被截短，而比指定长度小的值将会用空格进行填补。
- varchar（N）用于保存可以变长的字符串，长度最大为65535，只存储字符串实际实际需要的长度（它会增加一个额外字节来存储字符串本身的长度），varchar使用额外的1~2字节来存储值的的长度，如果列的最大长度小于或者等于255，则用1字节，否则用2字节。
- char和varchar跟字符编码也有密切的联系，latin1占用1个字节，gbk占用2个字节，utf8占用3个字节，utf8mb4占用4个。（不同字符编码占用的存储空间不同）
- 索引长度 char()和varchar()索引长度的计算公式：key_len=基础长度 * 列长度 + 1(如果是允许null) + 2(如果是变长列) (Character Set基础长度：utf8mb4=4,utf8=3,gbk=2,latin1=1)
- 浮点型: float key_len=4 ; double key_len=8
日期&时间类型的字段长度（根据版本有变化，单独说明）
| DataType orage Required | Before MySQL 5.6.4 (版本低于5.6.4 ) | Required as of MySQL 5.6.4 (版本高于5.6.4 ) |
|---|---|---|
| YEAR | 1byte | 1byte |
| DATE | 3bytes | 3bytes |
| TIME | 3bytes | 3 bytes + fractional seconds storage |
| DATETIME | 8bytes | 5 bytes + fractional seconds storage |
| TIMESTAMP | 4bytes | 4 bytes + fractional seconds storage |
查看db版本信息 select version() from dual;
如果使用的是mariadb，可查看官网的说明，（1、MariaDB 10.1 发布时间2015 对应 MySQL 5.6 发布时间2013 2、MariaDB 10.2 发布时间2017 对应 MySQL 5.7 发布时间2015）
验证SQL
drop table if exists TM_TEST_KEY_LEN;
CREATE TABLE `TM_TEST_KEY_LEN` (
`account_id` bigint(20) NOT null ,
`test_varchar_notnull` varchar(32) NOT null DEFAULT '',
`test_varchar_null` varchar(32) DEFAULT null ,
`test_char_notnull` char(32) NOT null DEFAULT '',
`test_char_null` char(32) DEFAULT null ,
`test_date_notnull` date NOT null ,
`test_date_null` date DEFAULT null ,
`test_datetime_notnull` datetime NOT null ,
`test_datetime_null` datetime DEFAULT null ,
PRIMARY KEY (`account_id`),
KEY IDX_test_varchar_notnull(test_varchar_notnull),
KEY IDX_test_varchar_null(test_varchar_null),
KEY IDX_test_char_notnull(test_char_notnull),
KEY IDX_test_char_null(test_char_null),
KEY IDX_test_date_notnull(test_date_notnull),
KEY IDX_test_date_null(test_date_null),
KEY IDX_test_datetime_notnull(test_datetime_notnull),
KEY IDX_test_datetime_null(test_datetime_null)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 查看索引
show index from tm_test_key_len ;
-- 重新组织索引
analyze table tm_test_key_len;
-- 插入一条测试数据
INSERT INTO tm_test_key_len (account_id, test_varchar_notnull, test_varchar_null, test_char_notnull, test_char_null, test_date_notnull, test_date_null, test_datetime_notnull, test_datetime_null)
VALUES(1, 'qq', 'qq', 'ww', 'ww', '2022-09-01', '2022-09-01', '2022-09-01', '2022-09-01');
-- 测试结果
# IDX_test_varchar_notnull 结果key_len=130 = 4*32+2
# IDX_test_varchar_null 结果key_len=131 = 4*32+2+1
# IDX_test_char_notnull 结果key_len=128 = 4*32
# IDX_test_char_null 结果key_len=129 = 4*32+1
# IDX_test_date_notnull 结果key_len=3 = 3
# IDX_test_date_null 结果key_len=4 = 3+1
# IDX_test_datetime_notnull 结果key_len=8= 8 -- 此处为8是因为我用MariaDb版本比较低，还是10.0.10版本 相当于MySQL5.6 最新版本MySQL中测试结果是5
# IDX_test_datetime_null 结果key_len=9= 8+1
explain
SELECT * from TM_TEST_KEY_LEN where test_datetime_null = '2022-09-01' ;