Mysql中索引长度key_len探索

Mysql中索引长度key_len探索

表结构

mysql> desc emp;
+--------------+---------------+------+-----+-------------------+-----------------------------+
| Field        | Type          | Null | Key | Default           | Extra                       |
+--------------+---------------+------+-----+-------------------+-----------------------------+
| empno        | int(10)       | NO   | PRI | NULL              | auto_increment              |
| ename        | varchar(50)   | YES  | MUL | NULL              |                             |
| job          | varchar(100)  | YES  |     | NULL              |                             |
| mgr          | int(10)       | YES  |     | NULL              |                             |
| hiredate     | datetime      | YES  |     | NULL              |                             |
| sal          | decimal(10,2) | YES  |     | NULL              |                             |
| comm         | decimal(10,2) | YES  |     | NULL              |                             |
| deptno       | int(11)       | YES  |     | NULL              |                             |
| created_time | datetime      | YES  |     | CURRENT_TIMESTAMP |                             |
| updated_time | datetime      | YES  |     | NULL              | on update CURRENT_TIMESTAMP |
| is_deleted   | tinyint(1)    | YES  |     | 0                 |                             |
| version      | int(1)        | YES  |     | 1                 |                             |
+--------------+---------------+------+-----+-------------------+-----------------------------+
12 rows in set (0.07 sec)

表中的索引

mysql> show index from emp;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| emp   |          0 | PRIMARY        |            1 | empno       | A         |          48 | NULL     | NULL   |      | BTREE      |         |               |
| emp   |          1 | idx_ename      |            1 | ename       | A         |          24 | NULL     | NULL   | YES  | BTREE      |         |               |
| emp   |          1 | idx_mgr        |            1 | mgr         | A         |           7 | NULL     | NULL   | YES  | BTREE      |         |               |
| emp   |          1 | idx_hiredate   |            1 | hiredate    | A         |          20 | NULL     | NULL   | YES  | BTREE      |         |               |
| emp   |          1 | idx_sal        |            1 | sal         | A         |           7 | NULL     | NULL   | YES  | BTREE      |         |               |
| emp   |          1 | idx_is_deleted |            1 | is_deleted  | A         |           2 | NULL     | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.08 sec)

explain

mysql> explain select * from emp where ename = '张三';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | idx_ename     | idx_ename | 153     | const |   20 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set (0.10 sec)

mysql>

key_len

到底key_len是怎么计算出来的?

各种数据类型计算规则如下：

char和varchar类型key_len计算公式:

1. varchr(N)变长字段且允许NULL = N * ( character set：utf8mb4为4字节，utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
2. varchr(N)变长字段且不允许NULL = N * ( character set：utf8mb4为4字节，utf8=3,gbk=2,latin1=1)+2(变长字段)
3. char(N)固定字段且允许NULL = N * ( character set：utf8mb4为4字节，utf8=3,gbk=2,latin1=1)+1(NULL)
4. char(N)固定字段且不允许NULL = N * ( character set：utf8mb4为4字节，utf8=3,gbk=2,latin1=1)

数值数据的key_len计算公式：

1. TINYINT允许NULL = 1 + 1(NULL)
2. TINYINT不允许NULL = 1
3. SMALLINT允许为NULL = 2+1(NULL)
4. SMALLINT不允许为NULL = 2
5. INT允许为NULL = 4+1(NULL)
6. INT不允许为NULL = 4
7. 日期时间型的key_len计算：（针对mysql5.5及之前版本）
8. DATETIME允许为NULL = 8 + 1(NULL)
9. DATETIME不允许为NULL = 8
10. TIMESTAMP允许为NULL = 4 + 1(NULL)
11. TIMESTAMP不允许为NULL = 4