mysql 执行计划中key_len的计算规则

墨竹~

于 2024-04-30 08:33:48 发布

阅读量885

点赞数 12

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/m0_61738548/article/details/138333821

版权

mysql 执行计划中key_len的计算规则

在查看mysql执行计划时，对执行计划中key_len字段计算规则不是很清楚，所以就做了如下资料整理。

+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t2    | NULL       | ref  | idx_t2_name   | idx_t2_name | 6       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

key_len的含义

key_len列表示SQL使用的索引长度，单位为字节。key_len的值能够确定MySQL实际使用组合索引中索引字段是否被用到。如果key列为NULL，则key_len列也为NULL。

key_len计算规则

计算公式

key_len=索引列数据类型本身占用的空间+额外空间（如果索引列为空或者索引列是变长类型计算key_len需要加上额外空间）

当索引为组合索引时，key_len为多个字段累加和。

计算规则

在计算key_len时，常见类型占用的字节数

数值类型占用字节数列表

数据类型	占用字节数
TINYINT	1 byte
SMALLINT	2 bytes
MEDIUMINT	3 bytes
INT, INTEGER	4 bytes
BIGINT	8 bytes
FLOAT§	4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53
FLOAT	4 bytes
DOUBLE [PRECISION],REAL	8 bytes
DECIMAL(M,D), NUMERIC(M,D)	下面详细介绍
BIT(M)	近似 (M+7)/8 bytes

我们以decimal介绍， decimal类型本身所占字节数的计算方式比较复杂

DECIMAL(M, D) M指的是总的位数，D指的就是小数位数。M的范围是1~ 65，D的范围是0~ 30，且D的值不能超过M。

定点数是精确的小数，为了达到精确的目的我们不能把它转换成二进制之后再存储(这可能会产生四舍五入的情况)。Mysql把一个小数分成3块来存储：

小数点左边的整数，小数点，以及小数点右边的小数。小数点左边及右边的整数以9位数为1组，用4个字节保存。

如果小数点前后的整数低于9位数，需要的字节数如下：

位数	占用的字节数
0	0
1	1
2	1
3	2
4	2
5	3
6	3
7	4
8	4

例如：

decimal(22,6) ：小数点左边16位，小数点右边6位，小数点左边需要4+4个字节，小数点右边6位需要3个字节，一共需要11个字节；

decimal(19,8) ：小数点左边11位，小数点右边8位，小数点左边需要4+1个字节，小数点右边需要4个字节，一共需要9个字节；

--创建测试表
create table test_int (id int not null,id2 bigint not null,id3  float(6) not null,id4  float(30) not null,id5  double not null,id6  decimal(19,8) not null,id7  bit(10) not null);
insert into test_int values(1,23625555,32123.345,2322222.342333333322,5948473.321,234.1234,b'1000001110');

create index idx_test_int_id on test_int(id);
create index idx_test_int_id2 on test_int(id2);
create index idx_test_int_id3 on test_int(id3);
create index idx_test_int_id4 on test_int(id4);
create index idx_test_int_id5 on test_int(id5);
create index idx_test_int_id6 on test_int(id6);
create index idx_test_int_id7 on test_int(id7);
explain select * from test_int where id = 1;
--索引字段为int，key_len值为4
mysql> explain select * from test_int where id = 1;
+----+-------------+----------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_int | NULL       | ref  | idx_test_int_id | idx_test_int_id | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
--索引字段为bigint，key_len值为8
mysql>  explain select * from test_int where id2 = 1;
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_int | NULL       | ref  | idx_test_int_id2 | idx_test_int_id2 | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
--索引字段为float(6)，key_len值为4

mysql>  explain select * from test_int where id3 = 1;
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table    | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | test_int | NULL       | ref  | idx_test_int_id3 | idx_test_int_id3 | 4       | const |    1 |   100.00 | Using index condition |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
--索引字段为float(30)，key_len值为8

mysql>  explain select * from test_int where id4 = 1;
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_int | NULL       | ref  | idx_test_int_id4 | idx_test_int_id4 | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
--索引字段为double，key_len值为8
mysql>  explain select * from test_int where id5 = 1;
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_int | NULL       | ref  | idx_test_int_id5 | idx_test_int_id5 | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
--索引字段为decimal(19,8)，key_len值为9，key_len=(4+1)(11整数位，分为9位整数用4个字节，剩余2位用1个字节及4+1)+4(8个小数位用4个字节)
mysql>  explain select * from test_int where id6 = 1;
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_int | NULL       | ref  | idx_test_int_id6 | idx_test_int_id6 | 9       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
--索引字段为big，根据公式计算 (10+7)/8 bytes近似值为2，执行计划中key_len值为2,
mysql>  explain select * from test_int where id7 = b'1000001110';
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_int | NULL       | ref  | idx_test_int_id7 | idx_test_int_id7 | 2       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

日期和时间类型占用字节数列表

对于TIME, DATETIME和TIMESTAMP列，MySQL 5.6.4之前创建的表所需的存储空间与5.6.4以后创建的表不同。这是由于5.6.4中的功能更新，允许这些类型具有小数部分，这需要从0到3个字节。

数据类型	MySQL 5.6.4之前的存储要求	MySQL 5.6.4之后的存储要求
YEAR	1 byte	1 byte
DATE	3 bytes	3 bytes
TIME	3 bytes	3 bytes +小数秒存储
DATETIME	8 bytes	5 bytes +小数秒存储
TIMESTAMP	4 bytes	4 bytes +小数秒存储

MySQL5.6.4及之后版本，TIME、DATETIME、TIMESTAMP这几种类型添加了对毫秒、微妙的支持。由于毫秒、微秒都不到1秒，所以也被称为小数秒，MySQL最多支持6位小数秒的精度，比如DATETIME(0)表示精确到秒，DATETIME(3)表示精确到毫秒，DATETIME(5)表示精确到微秒。所以如果你在使用TIME、DATETIME、TIMESTAMP这几种类型的时候精确到了小数秒，那么需要额外的存储空间，不同的小数秒精度需要的存储空间不同，如下表：

小数秒精度	存储要求
0	0 bytes
1, 2	1 byte
3, 4	2 bytes
5, 6	3 bytes

--创建测试表
create table test_date (dt date not null,dt2 time not null,dt3 time(1) not null,dt4 datetime(3) not null,dt5 timestamp(5) not null);
insert into test_date values(now(),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP(1),CURRENT_TIMESTAMP(3),CURRENT_TIMESTAMP(5));
--创建索引
create index idx_test_date_dt on test_date(dt);
create index idx_test_date_dt2 on test_date(dt2);
create index idx_test_date_dt3 on test_date(dt3);
create index idx_test_date_dt4 on test_date(dt4);
create index idx_test_date_dt5 on test_date(dt5);
--索引字段为date，key_len值为3
mysql> explain select * from test_date where dt < '2024-01-02 17:10:38';
+----+-------------+-----------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test_date | NULL       | range | idx_test_date_dt | idx_test_date_dt | 3       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
--索引字段为time，key_len值为3

mysql> explain select * from test_date where dt2 < '2024-01-02 17:10:38';
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test_date | NULL       | range | idx_test_date_dt2 | idx_test_date_dt2 | 3       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
--索引字段为time(1)，key_len值为4及3+1
mysql> explain select * from test_date where dt3 < '2024-01-02 17:10:38';
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test_date | NULL       | range | idx_test_date_dt3 | idx_test_date_dt3 | 4       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
--索引字段为datetime(3)，key_len值为7及5+2
mysql> explain select * from test_date where dt4 < '2024-01-02 17:10:38';
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test_date | NULL       | range | idx_test_date_dt4 | idx_test_date_dt4 | 7       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)
--索引字段为timestamp(5)，key_len值为7及4+3
mysql> explain select * from test_date where dt5 < '2024-01-02 17:10:38';
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test_date | NULL       | range | idx_test_date_dt5 | idx_test_date_dt5 | 7       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

explain select * from test_date where dt5 < '2024-01-02 17:10:38';

字符类型占用字节数列表

在下表中，M表示声明的列长度，对于非二进制字符串类型以字符为单位，对于二进制字符串类型以字节为单位。L表示给定字符串值的实际字节长度。

数据类型	存储要求
CHAR(M)	紧凑的InnoDB行格式优化了可变长度字符集的存储。参见COMPACT行格式存储特性。否则，M × w字节，<= M <= 255，其中w是字符集中最大长度字符所需的字节数。
BINARY(M)	`M` 字节, 0 <= M<= 255
VARCHAR(M), VARBINARY(M)	如果列值需要0 ~ 255字节，则L + 1字节;如果列值需要超过255字节，则L + 2字节。但是经过实际验证发现，在计算索引长度的时候，统一加2字节存储该变长列的实际长度，与是否超过255字节无关。
TINYBLOB, TINYTEXT	`L` + 1 字节, 其中 `L` < 2^8
BLOB, TEXT	`L` + 2字节, 其中 `L` < 2^16
MEDIUMBLOB, MEDIUMTEXT	`L` + 3字节, 其中 `L` < 2^24
LONGBLOB, LONGTEXT	`L` + 4 字节, 其中 `L` < 2^32

验证CHAR、BINARY、VARCHAR、VARBINARY

--创建测试表，验证CHAR、BINARY、VARCHAR、VARBINARY
create table test_var (var char(6) not null,var2 BINARY(6) not null,var3 VARCHAR(6) not null,var4 VARCHAR(300) not null,var5 VARBINARY(6) not null,var6 VARBINARY(300) not null);
insert into test_var values('abc','abc','abc','abc','abc','abc');
--创建索引
create index idx_test_var_var on test_var(var);
create index idx_test_var_var2 on test_var(var2);
create index idx_test_var_var3 on test_var(var3);
create index idx_test_var_var4 on test_var(var4);
create index idx_test_var_var5 on test_var(var5);
create index idx_test_var_var6 on test_var(var6);

--索引字段为char(6)，key_len值为18及为6*3
mysql> explain select * from test_var where var ='a';
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_var | NULL       | ref  | idx_test_var_var | idx_test_var_var | 18      | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
--索引字段为BINARY(6)，key_len值为6
mysql> explain select * from test_var where var2 ='a';
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table    | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | test_var | NULL       | ref  | idx_test_var_var2 | idx_test_var_var2 | 6       | const |    1 |   100.00 | Using index condition |
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
--索引字段为VARCHAR(6)，key_len值为20及为6*3+2
mysql> explain select * from test_var where var3 ='a';
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_var | NULL       | ref  | idx_test_var_var3 | idx_test_var_var3 | 20      | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
--索引字段为VARCHAR(100)，key_len值为302及为100*3+2
mysql> explain select * from test_var where var4 ='a';
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_var | NULL       | ref  | idx_test_var_var4 | idx_test_var_var4 | 302     | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
--索引字段为VARBINARY(6)，key_len值为8及为6+2
mysql> explain select * from test_var where var5 ='a';
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table    | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | test_var | NULL       | ref  | idx_test_var_var5 | idx_test_var_var5 | 8       | const |    1 |   100.00 | Using index condition |
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
--索引字段为VARBINARY(100)，key_len值为102及为100+2
mysql> explain select * from test_var where var6 ='a';
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table    | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | test_var | NULL       | ref  | idx_test_var_var6 | idx_test_var_var6 | 102     | const |    1 |   100.00 | Using index condition |
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

验证TINYBLOB、 TINYTEXT、BLOB、 TEXT、MEDIUMBLOB、 MEDIUMTEXT、 LONGBLOB、LONGTEXT

--创建测试表，验证TINYBLOB、 TINYTEXT、BLOB、 TEXT、MEDIUMBLOB、 MEDIUMTEXT、 LONGBLOB、LONGTEXT
create table test_var2 (var TINYBLOB not null,var2 TINYTEXT not null,var3 BLOB not null,var4 TEXT not null,var5 MEDIUMBLOB not null,var6 MEDIUMTEXT not null,var7 LONGBLOB not null,var8 LONGTEXT not null);

insert into test_var2 values('abc','abc','abc','abc','abc','abc','abc','abc');
insert into test_var2 values('abcde','abcde','abcde','abcde','abcde','abcde','abcde','abcde');
--创建索引
create index idx_test_var2_var on test_var2(var(10));
create index idx_test_var2_var2 on test_var2(var2(10));
create index idx_test_var2_var3 on test_var2(var3(10));
create index idx_test_va2r_var4 on test_var2(var4(10));
create index idx_test_var2_var5 on test_var2(var5(10));
create index idx_test_var2_var6 on test_var2(var6(10));
create index idx_test_var2_var7 on test_var2(var7(10));
create index idx_test_va2r_var8 on test_var2(var8(10));
--索引字段为TINYBLOB且给前10个字节创建索引，key_len值为12及10+2
mysql> explain select * from test_var2 where var='ab';
+----+-------------+-----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test_var2 | NULL       | ref  | idx_test_var2_var | idx_test_var2_var | 12      | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
--索引字段为TINYTEXT且给前10个字符创建索引，key_len值为32及10*3+2
mysql> explain select * from test_var2 where var2='ab';
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test_var2 | NULL       | ref  | idx_test_var2_var2 | idx_test_var2_var2 | 32      | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
--索引字段为BLOB且给前10个字节创建索引，key_len值为12及10+2
mysql> explain select * from test_var2 where var3='ab';
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test_var2 | NULL       | ref  | idx_test_var2_var3 | idx_test_var2_var3 | 12      | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
--索引字段为TEXT且给前10个字符创建索引，key_len值为32及10*3+2
mysql> explain select * from test_var2 where var4='ab';
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test_var2 | NULL       | ref  | idx_test_va2r_var4 | idx_test_va2r_var4 | 32      | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
--索引字段为MEDIUMBLOB且给前10个字节创建索引，key_len值为12及10+2
mysql> explain select * from test_var2 where var5='ab';
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test_var2 | NULL       | ref  | idx_test_var2_var5 | idx_test_var2_var5 | 12      | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
--索引字段为MEDIUMTEXT且给前10个字符创建索引，key_len值为32及10*3+2
mysql> explain select * from test_var2 where var6='ab';
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test_var2 | NULL       | ref  | idx_test_var2_var6 | idx_test_var2_var6 | 32      | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
--索引字段为LONGBLOB且给前10个字节创建索引，key_len值为12及10+2
mysql> explain select * from test_var2 where var7='ab';
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test_var2 | NULL       | ref  | idx_test_var2_var7 | idx_test_var2_var7 | 12      | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
--索引字段为LONGTEXT且给前10个字符创建索引，key_len值为32及10*3+2
mysql> explain select * from test_var2 where var8='ab';
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test_var2 | NULL       | ref  | idx_test_va2r_var8 | idx_test_va2r_var8 | 32      | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

通过上面的验证可知当索引字段为字符串时，key_len值受如下

char、varchar、text、LONGTEXT、MEDIUMTEXT、TINYTEXT字段类型受字符集影响
varchar 、VARBINARY、TINYBLOB、 TINYTEXT、BLOB、 TEXT、MEDIUMBLOB、 MEDIUMTEXT、 LONGBLOB、LONGTEXT变长字段，通过验证发现，在计算索引长度的时候，统一加2字节存储该变长列的实际长度，与其他列长度或者实际长度没有关系

索引字段为字符串时，索引列数据类型本身占用的空间跟字符集有关

常用的字符编码占用字节数量如下：

字符编码占用字节数
GBK 2
UTF8 3
ISO8859-1 1
GB2312 2
UTF-16 2
下面测试数据库字符编码格式为UTF8占3个字节，目前测试发现只有char、varchar、text、LONGTEXT、MEDIUMTEXT、TINYTEXT字段类型受字符集影响

比如char(M)字段占用空间：M*Maxlen(字符集占用字节数)，详细验证步骤可参考字符类型占用字节数列表的验证步骤

注意：MySQL5.0版本以上，字符串定义列的长度单位为字符

字符编码	占用字节数
GBK	2
UTF8	3
ISO8859-1	1
GB2312	2
UTF-16	2

索引字段列为NULL时，key_len计算时，需要加1；索引字段为NOT NULL时只计算索引列数据类型本身占用的空间

--创建测试表
create table test_null (id int,name1 varchar(10),name2 varchar(10) not null);
insert into test_null values(1,'wang','wang');
insert into test_null values(2,'li','wang');
create index idx_test_null_name1 on test_null(name1);
create index idx_test_null_name2 on test_null(name2);
--索引字段name1为空时，使用索引idx_test_null_name1,key_len=10*3+2(可变长字段的长度)+1(索引字段为NULL)
mysql> explain select * from test_null where name1 = '1';
+----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys       | key                 | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_null | NULL       | ref  | idx_test_null_name1 | idx_test_null_name1 | 33      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
--索引字段name2不为空时，使用索引idx_test_null_name2，key_len=10*3+2(可变长字段的长度)
mysql> explain select * from test_null where name2 = '2';
+----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys       | key                 | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_null | NULL       | ref  | idx_test_null_name2 | idx_test_null_name2 | 32      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

key_len的作用

MySQL执行计划中输出key_len列主要是为了让我们区分使用组合索引的查询语句具体用了几个索引列，另外由于组合索引有最左前缀的特性，如果组合索引能全部使用上，则是组合索引字段key_len为各索引字段的索引长度之和，通过索引长度的比对就可以判断索引是部分使用，还是全部使用。

总结

经过对大部分字段类型的测试，索引字段的key_len的值受如下的因素影响：

数值类型中，DECIMAL(M,D), NUMERIC(M,D)字段类型精度和BIT(M)字段类型长度会影响key_len值，其他数值类型占用存储空间都为固定字节数
日期时间类型中，TIME、DATETIME、TIMESTAMP的字段类型得时间精度会影响key_len值，剩余的year、date占用存储空间为固定字节数
char、varchar、text、LONGTEXT、MEDIUMTEXT、TINYTEXT字段类型受字符集影响，比如字符集为UTF8时，char(M)字段占用空间：M*Maxlen(字符集占用字节数)
varchar 、VARBINARY、TINYBLOB、 TINYTEXT、BLOB、 TEXT、MEDIUMBLOB、 MEDIUMTEXT、 LONGBLOB、LONGTEXT变长字段，通过验证发现，在计算索引长度的时候，统一加2字节存储该变长列的实际长度，与其他列长度或者实际长度没有关系
索引字段列为NULL时，key_len计算时，需要加1
TINYBLOB、 TINYTEXT、BLOB、 TEXT、MEDIUMBLOB、 MEDIUMTEXT、 LONGBLOB、LONGTEXT创建索引时，只支持前缀索引及指定在前L个字符（TEXT）或者L个字节（字节）上加索引，在计算key_len值时使用的是指定索引字段的长度L

参考

[1]https://www.modb.pro/db/52861