MySQL Explain执行计划key_len详解(特意针对date和datetime详细测试说明)

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

其他:

1. 所有的索引字段，如果设置允许null，key_len则需要加1。
2. char和varchar型的字段长度，char和varchar是日常使用最多的字符类型。char（N）用于保存固定长度的字符串，长度最大为255，比指定长度大的值将被截短，而比指定长度小的值将会用空格进行填补。
   1. varchar（N）用于保存可以变长的字符串，长度最大为65535，只存储字符串实际实际需要的长度（它会增加一个额外字节来存储字符串本身的长度），varchar使用额外的1~2字节来存储值的的长度，如果列的最大长度小于或者等于255，则用1字节，否则用2字节。
   2. char和varchar跟字符编码也有密切的联系，latin1占用1个字节，gbk占用2个字节，utf8占用3个字节，utf8mb4占用4个。（不同字符编码占用的存储空间不同）
3. 索引长度 char()和varchar()索引长度的计算公式：key_len=基础长度 * 列长度 + 1(如果是允许null) + 2(如果是变长列) (Character Set基础长度：utf8mb4=4,utf8=3,gbk=2,latin1=1)
4. 浮点型: 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' ;