1.创建测试表
CREATE TABLE `test_index_len` (
`id` int(11) DEFAULT NULL,
`num` int(11) NOT NULL,
`num_1` int 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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;alter table test_index_len add index idx_id(id),add index idx_id_num_2(id,num_1),add index idx_num(num),add index idx_name_1(name_1),add index idx_name_2(name_2),add index idx_name_3(name_3),add index idx_name_4(name_4),add index idx_id_name_1(id,name_1),add index idx_name_3_name_4(name_3,name_4);
2.测试表结构
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
3. Mysql key_len 解释
explain 中的 key_len 表示使用的索引长度,是以字节为单位。根据这个值,就可以判断索引使用情况,比如在使用组合索引的时候,判断是否所有的索引字段是否都被查询用到
4. 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 表示某个字符集中表示一个字符最多需要使用的字节数,utf8 Maxlen 为3,utf8mb4 Maxlen 为4
##字符型有 char,varchar,tinytext,text,mediumtext,longtext
5)Mysql 对单列索引及组合索引的长度都是有限制的,详情可参考官方文档或如下链接
5.下面列出一些常见类型在计算 key_len 时本身占用空间大小
NOTE:不同版本各数据类型的存储空间要求可能会有变化,详情请参考官方文档
https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html#data-types-storage-reqs-date-time
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个字节
6.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