Mysql 索引 key_len 计算方式最强解析

渔夫数据库笔记 2021-04-18

4723

创建测试表

CREATE TABLE `test_index_len` (
  `id` int(11) DEFAULT NULL,
  `num` int(11) NOT NULL,
  `num_1` int(11) DEFAULT NULL,
  `name_1` char(10) DEFAULT NULL,
  `name_2` char(10) NOT NULL,
  `name_3` varchar(10) DEFAULT NULL,
  `name_4` varchar(10) NOT NULL,
  KEY `idx_id` (`id`),
  KEY `idx_id_num_2` (`id`,`num_1`),
  KEY `idx_num` (`num`),
  KEY `idx_name_1` (`name_1`),
  KEY `idx_name_2` (`name_2`),
  KEY `idx_name_3` (`name_3`),
  KEY `idx_name_4` (`name_4`),
  KEY `idx_id_name_1` (`id`,`name_1`),
  KEY `idx_name_3_name_4` (`name_3`,`name_4`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Mysql key_len 解释

explain 中的 key_len 表示使用的索引长度，是以字节为单位。根据这个值，就可以判断索引使用情况，比如在使用组合索引的时候，判断是否所有的索引字段是否都被查询用到。

Mysql key_len 计算规则

MySQL数据库中，key_len的计算规则如下：

1)key_len 由索引列数据类型本身占用空间+额外空间(如果索引列可以为空或者索引列是变长类型计算key_len需要加上额外空间)。

2)如果索引列可以为空，则在索引列数据类型本身占用空间基础上加1。比如索引(`id`,`num_1`) id 列占用4个字节，num_1列占用4个字节，且两列都可以为空，所以key_len=4+4+2=10。

3)如果索引列是变长的(比如 varchar,varbinary)，则在索引列数据类型本身占用空间的基础上再加2，比如索引(`name_3`,`name_4`)，name_3占用字节数4*10,name_4占用字节数40，name_3可以为空，并且name_3，name_4两列都是变成类型，所以key_len=40+40+1+2+2=85。

4)如果索引列是字符型，则索引列数据类型本身占用空间跟字符集有关，比如 VARCHAR(M) 类型占用空间为 M * Maxlen 。

##Maxlen 表示某个字符集中表示一个字符最多需要使用的字节数，utf8Maxlen 为3，utf8mb4 Maxlen 为4。

##字符型有 char,varchar,tinytext,text,mediumtext,longtext。

5)Mysql 对单列索引及组合索引的长度都是有限制的，详情可参考官方文档或如下链接：

https://blog.csdn.net/shaochenshuo/article/details/51064685

常见类型在计算key_len时本身占用空间大小

1)整数类型

2)日期和时间类型

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

3)字符串类型的计算上面已经说过

M * Maxlen

4)浮点数类型

5)定点数类型

decimal类型本身所占字节数的计算方式比较复杂

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

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

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

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

例如：

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

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

key_len计算实例

1)
[root@127.0.0.1][test_shao]> explain select id from test_index_len where id=1;
+----+-------------+----------------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table          | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test_index_len | NULL       | ref  | idx_id        | idx_id | 5       | const |    1 |   100.00 | Using index |
+----+-------------+----------------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
idx_id为单列索引，索引列id为int类型(int 列类型本身占用4个字节)，并且id列可以为null 所以 key_len=4+1=5


2)
[root@127.0.0.1][test_shao]> explain select num from test_index_len where num=1;
+----+-------------+----------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table          | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test_index_len | NULL       | ref  | idx_num       | idx_num | 4       | const |    1 |   100.00 | Using index |
+----+-------------+----------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
idx_num为单列索引，num 列为int类型，且id列不为null所以 key_len=4


3)
[root@127.0.0.1][test_shao]> explain select id,num_1 from test_index_len force index(idx_id_num_2) where id=1 and num_1=1;
+----+-------------+----------------+------------+------+---------------+--------------+---------+-------------+------+----------+-------------+
| id | select_type | table          | partitions | type | possible_keys | key          | key_len | ref         | rows | filtered | Extra       |
+----+-------------+----------------+------------+------+---------------+--------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | test_index_len | NULL       | ref  | idx_id_num_2  | idx_id_num_2 | 10      | const,const |    1 |   100.00 | Using index |
+----+-------------+----------------+------------+------+---------------+--------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
idx_id_num_2为组合索引，索引列为(id,num_2)两列都为int类型且都可以为null 所以 key_len=(4+1)+(4+1)=10


4)
[root@127.0.0.1][test_shao]> explain select name_1 from test_index_len where name_1='xxxx';
+----+-------------+----------------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
| id | select_type | table          | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test_index_len | NULL       | ref  | idx_name_1    | idx_name_1 | 41      | const |    1 |   100.00 | Using index |
+----+-------------+----------------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
idx_name_1为单列索引，索引列name_1类型为 char(10)，可以为null，列的字符集为utf8mb4，所以key_len=10*4+1=41


5)
[root@127.0.0.1][test_shao]> explain select name_1 from test_index_len where name_3='xxxx';
+----+-------------+----------------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table          | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_index_len | NULL       | ref  | idx_name_3    | idx_name_3 | 43      | const |    1 |   100.00 | NULL  |
+----+-------------+----------------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
idx_name_3为单列索引，索引列 name_3 类型为 varchar(10)，是可变类型，可以为null,列的字符集为utf8mb4，所以key_len=10*4+2+1=43


6)
[root@127.0.0.1][test_shao]> explain select id,name_1 from test_index_len force index(idx_id_name_1) where id=1 and name_1='xxx';
+----+-------------+----------------+------------+------+---------------+---------------+---------+-------------+------+----------+-------------+
| id | select_type | table          | partitions | type | possible_keys | key           | key_len | ref         | rows | filtered | Extra       |
+----+-------------+----------------+------------+------+---------------+---------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | test_index_len | NULL       | ref  | idx_id_name_1 | idx_id_name_1 | 46      | const,const |    1 |   100.00 | Using index |
+----+-------------+----------------+------------+------+---------------+---------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
idx_id_name_1是组合索引，索引列id为int类型，可以为null，索引列name_1为char(10)类型，不可变长且可以为null，所以key_len=(4+1)+(4*10+1)=46


7)
[root@127.0.0.1][test_shao]> explain select name_3,name_4 from test_index_len force index(idx_name_3_name_4) where name_3='xxxx' and name_4='xxxx';
+----+-------------+----------------+------------+------+-------------------+-------------------+---------+-------------+------+----------+-------------+
| id | select_type | table          | partitions | type | possible_keys     | key               | key_len | ref         | rows | filtered | Extra       |
+----+-------------+----------------+------------+------+-------------------+-------------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | test_index_len | NULL       | ref  | idx_name_3_name_4 | idx_name_3_name_4 | 85      | const,const |    1 |   100.00 | Using index |
+----+-------------+----------------+------------+------+-------------------+-------------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
idx_name_3_name_4是组合索引，索引列name_3为varchar(10),可以为null，索引列name_4为varchar(10),不可以为null，所以key_len=(4*10+1+2)+(4*10+2)


8)
[root@127.0.0.1][test_shao]> explain select name_3,name_4 from test_index_len force index(idx_name_3_name_4) where name_3='xxxx';
+----+-------------+----------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type | table          | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test_index_len | NULL       | ref  | idx_name_3_name_4 | idx_name_3_name_4 | 43      | const |    1 |   100.00 | Using index |
+----+-------------+----------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
idx_name_3_name_4 索引的key_len算出来应该是85，但是这里显示是43，因为key_len 表示使用的索引长度，这个查询只使用了组合索引中的第一个列，所以key_len=40+2+1

扫描二维码

获取更多精彩

渔夫数据库笔记

如果有用请点在看！

mysql

文章转载自渔夫数据库笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。