一篇搞定MySQL索引長度（key_len）計算規則

MySQL索引長度（key_len）計算計算規則索引欄位：沒有設置 NOT NULL，則需要加 1 個位元組。定長欄位：tinyint 占 1 個位元組、int 占 4個位元組、bitint 占 8 個位元組、date 占 3個位元組、datetime 占 5 個位元組、char(n) 占 n 個位元組。 ...

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  |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+