路过次次 发表于 2023-6-3 13:23:39

一篇搞定MySQL索引长度(key_len)计算规则

MySQL索引长度(key_len)计算

 计算规则


[*]索引字段:没有设置 NOT NULL,则需要加 1 个字节。
[*]定长字段:tinyint 占 1 个字节、int 占 4个字节、bitint 占 8 个字节、date 占 3个字节、datetime 占 5 个字节、char(n) 占 n 个字节。
[*]变长字段:varchar (n) 占 n 个字符 + 2 个 字节。
注意(字符和字节在不同编码之间的转换) 
不同的字符集,一个字符占用的字节数不同


[*]latin1 编码: 每个字符占用一个字节。
[*]gbk编码:每个字符占用两个字节。
[*]utf8编码:每个字符占用三个字节。
[*]utf8mb4编码:每个字符占用四个字节。
案例分析

(一)、编码为utf8


[*]创建测试表CREATE TABLE `test3` (
`id` int NOT NULL,
`name` varchar(20) NOT NULL COMMENT '姓名',
`sex` tinyint NOT NULL COMMENT '性别,1:男,2:女',
`email` varchar(20) DEFAULT NULL,
`age` tinyint default 0,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
[*]创建索引mysql> show index from test3;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test3 |          0 | PRIMARY|            1 | id          | A         |         2 |   NULL |   NULL |      | BTREE      |         |               | YES   | NULL       |
| test3 |          1 | idx_age|            1 | age         | A         |         2 |   NULL |   NULL | YES| BTREE      |         |               | YES   | NULL       |
| test3 |          1 | idx_name |            1 | name      | A         |         2 |   NULL |   NULL |      | BTREE      |         |               | YES   | NULL       |
| test3 |          1 | idx_eml|            1 | email       | A         |         2 |   NULL |   NULL | YES| BTREE      |         |               | YES   | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
[*]插入测试数据insert into test3(id, name, sex, email, age) values(1, 'tom', 1, 'tom@163.com', 16),(2, 'lucy', 2, 'lucy@163.com', 18);
[*]分析查询计划


[*]根据主键 id 分析,由于 id 为 int 类型,设置的NOT NULL ,key_len为 4 mysql> explain select * from test3 where id =1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type| possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|1 | SIMPLE      | test3 | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL|
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
 

[*]根据 age 分析,由于 age 为 tinyint 类型,可以为NULL ,key_len为 1 + 1  = 2mysql> explain select * from test3 where age =16;
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|1 | SIMPLE      | test3 | NULL       | ref| idx_age       | idx_age | 2       | const |    1 |   100.00 | NULL|
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
 

[*]根据 name 分析,由于 name 为 varchar 类型,设置NOT NULL ,key_len为 20 *3 + 2  =  62 mysql> explain select * from test3 where name = 'tom';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|1 | SIMPLE      | test3 | NULL       | ref| idx_name      | idx_name | 62      | const |    1 |   100.00 | NULL|
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
 

[*]根据 email 分析,由于 email  为 varchar 类型,没有设置NOT NULL  ,key_len为 20 *3 + 2 + 1  =  63mysql> explain select * from test3 where email = 'tom@163.com';
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|1 | SIMPLE      | test3 | NULL       | ref| idx_eml       | idx_eml | 63      | const |    1 |   100.00 | NULL|
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
 
(二)、编码为utf8mb4


[*]创建测试表 CREATE TABLE `test4` (
`id` int NOT NULL,
`name` varchar(20) NOT NULL COMMENT '姓名',
`sex` tinyint NOT NULL COMMENT '性别,1:男,2:女',
`email` varchar(20) DEFAULT NULL,
`age` tinyint default 0,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
[*]创建索引mysql> show index from test3;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test3 |          0 | PRIMARY|            1 | id          | A         |         2 |   NULL |   NULL |      | BTREE      |         |               | YES   | NULL       |
| test3 |          1 | idx_age|            1 | age         | A         |         2 |   NULL |   NULL | YES| BTREE      |         |               | YES   | NULL       |
| test3 |          1 | idx_name |            1 | name      | A         |         2 |   NULL |   NULL |      | BTREE      |         |               | YES   | NULL       |
| test3 |          1 | idx_eml|            1 | email       | A         |         2 |   NULL |   NULL | YES| BTREE      |         |               | YES   | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
[*]插入测试数据1 insert into test4(id, name, sex, email, age) values(1, 'tom', 1, 'tom@163.com', 16),(2, 'lucy', 2, 'lucy@163.com', 18);
[*]分析查询计划


[*]根据 name 分析,由于 name 为 varchar 类型,设置NOT NULL ,key_len为 20 *4 + 2  =  82mysql> explain select * from test4 where name = 'tom';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|1 | SIMPLE      | test4 | NULL       | ref| idx_name      | idx_name | 82      | const |    1 |   100.00 | NULL|
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
 

[*]根据 email 分析,由于 email 为 varchar 类型,没有设置NOT NULL ,key_len为 20 *4 + 2 +1  =  83mysql> explain select * from test4 where email = 'tom@163.com';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|1 | SIMPLE      | test4 | NULL       | ref| idx_email   | idx_email | 83      | const |    1 |   100.00 | NULL|
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
 

来源:https://www.cnblogs.com/it-ws/p/17453801.html
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作!
页: [1]
查看完整版本: 一篇搞定MySQL索引长度(key_len)计算规则