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

MySQL索引长度(key_len)计算
 计算规则
  • 索引字段:没有设置 NOT NULL,则需要加 1 个字节。
  • 定长字段:tinyint1 个字节、int4个字节、bitint8 个字节、date3个字节、datetime个字节、char(n)n 个字节。
  • 变长字段:varchar (n) 占 n 个字符 + 2 个 字节

注意(字符和字节在不同编码之间的转换) 

不同的字符集,一个字符占用的字节数不同
  • latin1 编码: 每个字符占用一个字节。
  • gbk编码:每个字符占用两个字节。
  • utf8编码:每个字符占用三个字节。
  • utf8mb4编码:每个字符占用四个字节。
案例分析
(一)、编码为utf8
  1. 创建测试表
    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;
  2. 创建索引
    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       |
    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
  3. 插入测试数据
    insert into test3(id, name, sex, email, age) values(1, 'tom', 1, '[email protected]', 16),(2, 'lucy', 2, '[email protected]', 18); 
  4. 分析查询计划
  • 根据主键 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
  1. 创建测试表
     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;
  2. 创建索引
    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       |
    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
  3. 插入测试数据
    1 insert into test4(id, name, sex, email, age) values(1, 'tom', 1, '[email protected]', 16),(2, 'lucy', 2, '[email protected]', 18); 
  4. 分析查询计划
  • 根据 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  |
    +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+

 

热门相关:无量真仙   梦回大明春   法医娇宠,扑倒傲娇王爷   修仙界最后的单纯   最强装逼打脸系统