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, '[email protected]', 16),(2, 'lucy', 2, '[email protected]', 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 = 2
mysql> 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 = 63
mysql> explain select * from test3 where email = '[email protected]'; +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+ | 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, '[email protected]', 16),(2, 'lucy', 2, '[email protected]', 18);
- 分析查询计划
- 根据 name 分析,由于 name 为 varchar 类型,设置NOT NULL ,key_len为 20 *4 + 2 = 82
mysql> 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 = 83
mysql> explain select * from test4 where email = '[email protected]'; +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+ | 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 | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
标签:test3,name,idx,len,key,MySQL,NULL,id From: https://www.cnblogs.com/it-ws/p/17453801.html