一篇搞定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. +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+

  复制代码

 

来源:https://www.cnblogs.com/it-ws/p/17453801.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！