MySQL的执行计划key

key_len的含义

参考mysql 8.0官方文档的解释：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

key_len是explain输出字段中的一列。

含义是：The length of the chosen key，所选键的长度。其单位是字节。

key_len的作用

根据这个值，就可以判断索引使用情况。比如当key_len列显示为NULL时，key列也就会显示为NULL, 说明语句没有用到索引。比如在使用组合索引的时候，判断是否所有的索引字段是否都被用到。

如何根据key_len的值判断是否所有的索引字段都被用到，就要知道key_len的计算规则。

key_len 计算规则

1.可以为NULL的列的key长度比非NULL列的key长度大1。

 

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.

看个例子,有一张表a_test,其表结构如所示：

CREATE TABLE `a_test` (
  `id` int(4) unsigned NOT NULL AUTO_INCREMENT,
  `server_id` int(4) NOT NULL DEFAULT '0',
  `user_id` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_server_id` (`server_id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

可以看到表a_test，有两个普通索引idx_server_id和idx_user_id。server_id的字段类型是int，有not null约束。user_id的字段类型是int，没有not null约束，默认值是null。

我们来看下分别使用这两个索引时，key_len的值。

mysql> explain select * from a_test where server_id=1;
+----+-------------+--------+------+---------------+---------------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys | key           | key_len | ref   | rows | Extra |
+----+-------------+--------+------+---------------+---------------+---------+-------+------+-------+
|  1 | SIMPLE      | a_test | ref  | idx_server_id | idx_server_id | 4       | const |    1 | NULL  |
+----+-------------+--------+------+---------------+---------------+---------+-------+------+-------+
1 row in set (0.02 sec)

mysql> explain select * from a_test where user_id=1;
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys | key         | key_len | ref   | rows | Extra |
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------+
|  1 | SIMPLE      | a_test | ref  | idx_user_id   | idx_user_id | 5       | const |    1 | NULL  |
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------+
1 row 

如上所示，当使用idx_user_id索引时，key_len的值是5(int类型长度4+1），而使用idx_server_id索引时，key_len的值是4（仅为int类型长度4）。

2.如果索引列是字符型字段，则索引列数据类型本身占用空间跟字符集有关。

不同的字符集下，同一个字符存储到表中的时候，它所占用的空间大小是不同的。一个字符存储在表中，到底占用多少个字节byte，需要根据不同的字符集来分别计算。

常用的几种字符集下，字符character和字节byte的换算关系如下：

字符集	1个字符占用字节数（Maxlen）
GBK	2
UTF8	3
UTF8mb4	4
latin1	1

注：latin1字符集编码下，不支持插入中文字符。

所以CHAR(M)类型占用空间为M * Maxlen 。

验证一下：

3. 如果索引列是变长的(比如varchar)，则在索引列数据类型本身占用空间的基础上再加2。

我们把上面的char类型替换成varchar。

看个组合索引的例子

我有一张表kill_log。

`timestamp` datetime DEFAULT NULL,
`db` varchar(64) DEFAULT NULL,
...
KEY `idx_timestamp_db` (`timestamp`,`db`)
) ENGINE=InnoDB AUTO_INCREMENT=77559 DEFAULT CHARSET=utf8mb4

查看如下语句的执行计划。

SELECT
*
FROM
  `kill_log`
  FORCE INDEX(`idx_timestamp_db`)
WHERE
  1 = 1
  and timestamp >'2022-05-06T16:22:39.206273Z' and timestamp < '2022-05-07T15:22:39.206323Z'
and db = 'db_common'

其输出的执行计划如下：

key_len为265。

我们来分析下这个265怎么算出来的吧。

1. key_len = len(idx_timestamp_db) = len(timestamp) + len(db)

2. len(timestamp) = timestamp占用字节5 + null值1 = 6

3. len(db) = varchar(64)*utf8mb4 maxlen 4 + 变2 + null值1 = 259

4. 259 + 6 = 365

把查询语句db的条件去掉。

SELECT
*
FROM
  `kill_log`
  FORCE INDEX(`idx_timestamp_db`)
WHERE
  1 = 1
  and timestamp >'2022-05-06T16:22:39.206273Z' and timestamp < '2022-05-07T15:22:39.206323Z'

再看下执行计划。

此时查询只用到了组合索引中的第一个字段timestamp，长度为6。

参考文档：

https://blog.csdn.net/javaanddonet/article/details/111992505

https://www.modb.pro/db/52861

关注我，和我一起拯救吧！