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计算公式:
- varchr(N)变长字段且允许NULL = N * ( character set：utf8mb4为4字节，utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
- varchr(N)变长字段且不允许NULL = N * ( character set：utf8mb4为4字节，utf8=3,gbk=2,latin1=1)+2(变长字段)
- char(N)固定字段且允许NULL = N * ( character set：utf8mb4为4字节，utf8=3,gbk=2,latin1=1)+1(NULL)
- char(N)固定字段且不允许NULL = N * ( character set：utf8mb4为4字节，utf8=3,gbk=2,latin1=1)
数值数据的key_len计算公式：
- TINYINT允许NULL = 1 + 1(NULL)
- TINYINT不允许NULL = 1
- SMALLINT允许为NULL = 2+1(NULL)
- SMALLINT不允许为NULL = 2
- INT允许为NULL = 4+1(NULL)
- INT不允许为NULL = 4
- 日期时间型的key_len计算：（针对mysql5.5及之前版本）
- DATETIME允许为NULL = 8 + 1(NULL)
- DATETIME不允许为NULL = 8
- TIMESTAMP允许为NULL = 4 + 1(NULL)
- TIMESTAMP不允许为NULL = 4