IT评测·应用市场-qidao123.com

标题: 一篇搞定MySQL索引长度（key_len）计算规则 [打印本页]

作者: 不到断气不罢休 时间: 2023-6-3 17:59
标题: 一篇搞定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

创建测试表
1. CREATE TABLE `test3` (
2. `id` int NOT NULL,
3. `name` varchar(20) NOT NULL COMMENT '姓名',
4. `sex` tinyint NOT NULL COMMENT '性别,1：男，2：女',
5. `email` varchar(20) DEFAULT NULL,
6. `age` tinyint default 0,
7. PRIMARY KEY (`id`)
8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
复制代码
创建索引
1. mysql> show index from test3;
2. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
4. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
5. | test3 | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | YES | NULL |
6. | test3 | 1 | idx_age | 1 | age | A | 2 | NULL | NULL | YES | BTREE | | | YES | NULL |
7. | test3 | 1 | idx_name | 1 | name | A | 2 | NULL | NULL | | BTREE | | | YES | NULL |
8. | test3 | 1 | idx_eml | 1 | email | A | 2 | NULL | NULL | YES | BTREE | | | YES | NULL |
9. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
复制代码
插入测试数据
1. 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
1. mysql> explain select * from test3 where id =1;
2. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
4. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
5. | 1 | SIMPLE | test3 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
6. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
复制代码

根据 age 分析，由于 age 为 tinyint 类型，可以为NULL ，key_len为 1 + 1 = 2
1. mysql> explain select * from test3 where age =16;
2. +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
4. +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
5. | 1 | SIMPLE | test3 | NULL | ref | idx_age | idx_age | 2 | const | 1 | 100.00 | NULL |
6. +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
复制代码

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

根据 email 分析，由于 email 为 varchar 类型，没有设置NOT NULL ，key_len为 20 *3 + 2 + 1 = 63
1. mysql> explain select * from test3 where email = 'tom@163.com';
2. +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
4. +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
5. | 1 | SIMPLE | test3 | NULL | ref | idx_eml | idx_eml | 63 | const | 1 | 100.00 | NULL |
6. +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
复制代码

（二）、编码为utf8mb4

创建测试表
1. CREATE TABLE `test4` (
2. `id` int NOT NULL,
3. `name` varchar(20) NOT NULL COMMENT '姓名',
4. `sex` tinyint NOT NULL COMMENT '性别,1：男，2：女',
5. `email` varchar(20) DEFAULT NULL,
6. `age` tinyint default 0,
7. PRIMARY KEY (`id`)
8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
复制代码
创建索引
1. mysql> show index from test3;
2. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
4. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
5. | test3 | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | YES | NULL |
6. | test3 | 1 | idx_age | 1 | age | A | 2 | NULL | NULL | YES | BTREE | | | YES | NULL |
7. | test3 | 1 | idx_name | 1 | name | A | 2 | NULL | NULL | | BTREE | | | YES | NULL |
8. | test3 | 1 | idx_eml | 1 | email | A | 2 | NULL | NULL | YES | BTREE | | | YES | NULL |
9. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
复制代码
插入测试数据
1. 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 = 82
1. mysql> explain select * from test4 where name = 'tom';
2. +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
4. +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
5. | 1 | SIMPLE | test4 | NULL | ref | idx_name | idx_name | 82 | const | 1 | 100.00 | NULL |
6. +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
复制代码

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

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

欢迎光临 IT评测·应用市场-qidao123.com (https://dis.qidao123.com/)