key_len (JSON name: key_length)
The key_len column indicates the length of the key that MySQL decided to use. The value of key_len enables you to determine how many parts of a multiple-part key MySQL actually uses. If the key column says NULL, the key_len column also says NULL.
Due to the key storage format, the key length is one greater for a column that can be NULL than for a NOT NULL column.
出自mysql官网:https://dev.mysql.com/doc/refman/5.6/en/explain-output.html#explain_key_len
也就是说某一列如果可以为NULL,那么这一列会比不允许为Null的列多1字节。
规则列表
列类型 | 是否为空 | 长度 | key_len | 规则 |
tinyint | DEFAULT NULL | 1 | 1+1=2 | DEFAULT NULL需要多加1 |
tinyint | NOT NULL | 1 | 1 | |
int | DEFAULT NULL | 4 | 4+1=5 | DEFAULT NULL需要多加1 |
int | NOT NULL | 4 | 4 | |
char(1) | DEFAULT NULL | utf8mb4=4,utf8=3,gbk=2 | 1*长度+1 | DEFAULT NULL需要多加1 |
char(1) | NOT NULL | utf8mb4=4,utf8=3,gbk=2 | 1*长度 | |
varchar(10) | DEFAULT NULL | utf8mb4=4,utf8=3,gbk=2 | 10*长度+1+2 |
|
varchar(10) | NOT NULL | utf8mb4=4,utf8=3,gbk=2 | 10*长度+2 |
|
场景举例
表结构:
CREATE TABLE `device_alarm_history` (
`id` bigint(20) unsigned NOT NULL COMMENT '告警id',
`ent_id` varchar(32) DEFAULT NULL COMMENT '企业id',
`alarm_content` text COMMENT '告警内容',
`alarm_date` datetime DEFAULT NULL COMMENT '告警时间',
`device_code` varchar(255) DEFAULT NULL COMMENT '设备编码',
`delete_flag` tinyint(4) NOT NULL COMMENT '删除标识',
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_alarm_statistics` (`ent_id`,`delete_flag`,`alarm_date`,`device_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='告警信息历史表';
查询语句:
SELECT
alarm_content
FROM
device_alarm_history
WHERE
ent_id = "666"
AND delete_flag = 0
AND alarm_date >= "2022-01-01 17:32:08"
AND "2022-12-01 17:32:08" >= alarm_date
AND device_code IN ( "DEVICE_CODE_1", "DEVICE_CODE_2" )
索引是:`ent_id`, `delete_flag`, `alarm_date`, `device_code`的情况下,explain后的结果是多少呢?
列名 | 类型 | 备注 | |
ent_id | varchar(32) | 32*4+2+1=131 |
|
delete_flag | tinyint(4) | 1=1 |
|
alarm_date | datetime | 5+1=6 |
|
device_code | varchar(255) | 255*4+2+1=1023 |
|