EXPLAIN执行计划中的key_len大小计算

key_len

背景-优化sql时发现一条sql未使用到索引

mysql> explain SELECT `id`,`vip`,`level`,`is_dormant` FROM channel WHERE `staff_code`=15011603318612105450;
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys  | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | channel | NULL       | ALL  | idx_staff_code | NULL | NULL    | NULL |   11 |    10.00 | Using where |
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.01 sec)

查了下索引，发现该字段已经上了索引，进而查了下字段类型发现是隐式转换导致的未用到索引

mysql> show index from channel;
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| channel |          0 | PRIMARY        |            1 | id          | A         |          11 |     NULL | NULL   |      | BTREE      |         |               |
| channel |          1 | idx_staff_code |            1 | staff_code  | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
| channel |          1 | idx_code       |            1 | code        | A         |          11 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.02 sec)

//查询条件类型不对，导致全表扫描，每次计算都要把字段转为int型来比较，所以全表扫描

mysql> explain SELECT `id`,`vip`,`level`,`is_dormant` FROM channel WHERE `staff_code`=15011603318612105450;
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys  | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | channel | NULL       | ALL  | idx_staff_code | NULL | NULL    | NULL |   11 |    10.00 | Using where |
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.01 sec)

//改变了where条件这下用上了索引，但索引的长度为82，字段长度为20，进而想到索引的长度是怎么计算的

mysql> explain SELECT `id`,`vip`,`level`,`is_dormant` FROM channel WHERE `staff_code`='15011603318612105450';
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | channel | NULL       | ref  | idx_staff_code | idx_staff_code | 82      | const |    4 |   100.00 | NULL  |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

字符集utf8mb4_general_ci（一个字符占四个字节）

如下：
staff_code varchar(20 ) utf8mb4_general_ci not null 

key_len = 4*20（字符长度）+ 2 （变长字符要多留2个字节）

staff_code varchar(20 ) utf8mb4_general_ci null

key_len = 4*20（字符长度）+ 2 （变长字符要多留2个字节）

其他类型都可以推算，keylen和字符集，是否为null，以及字段类型相关