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，以及字段类型相关