mysql explain 中的key_len 的长度计算

1 查看字体集

mysql> show variables like '%char%';
+--------------------------+----------------------------------------------------------------+
| Variable_name            | Value                                                          |
+--------------------------+----------------------------------------------------------------+
| character_set_client     | utf8                                                           |
| character_set_connection | utf8                                                           |
| character_set_database   | utf8                                                           |
| character_set_filesystem | binary                                                         |
| character_set_results    | utf8                                                           |
| character_set_server     | utf8                                                           |
| character_set_system     | utf8                                                           |
| character_sets_dir       | /usr/local/mysql-5.7.19-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
8 rows in set (0.00 sec)

2.创建测试表

mysql> create table w2 (id int,a char(5),b varchar(5), c char(5) not null, d varchar(5) not null);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into w2 values(1,'1','1','1','1');
Query OK, 1 row affected (0.01 sec)

mysql> insert into w2 values(2,'2','2','2','2');
Query OK, 1 row affected (0.00 sec)

mysql> insert into w2 values(3,'3','3','3','3');
Query OK, 1 row affected (0.00 sec)

mysql> insert into w2 values(4,'4','4','4','4');
rt into w2 values(10,'10','10','10','10');Query OK, 1 row affected (0.01 sec)

mysql> insert into w2 values(5,'5','5','5','5');
Query OK, 1 row affected (0.00 sec)

mysql> insert into w2 values(6,'6','6','6','6');
Query OK, 1 row affected (0.00 sec)

mysql> insert into w2 values(7,'7','7','7','7');
Query OK, 1 row affected (0.00 sec)

mysql> insert into w2 values(8,'8','8','8','8');
Query OK, 1 row affected (0.00 sec)

mysql> insert into w2 values(9,'9','9','9','9');
Query OK, 1 row affected (0.00 sec)

mysql> insert into w2 values(10,'10','10','10','10');
Query OK, 1 row affected (0.00 sec)

3.测试结果

mysql> show create table w2\G
*************************** 1. row ***************************
       Table: w2
Create Table: CREATE TABLE `w2` (
  `id` int(11) DEFAULT NULL,
  `a` char(5) DEFAULT NULL,
  `b` varchar(5) DEFAULT NULL,
  `c` char(5) NOT NULL,
  `d` varchar(5) NOT NULL,
  KEY `w1` (`id`),
  KEY `w2` (`a`),
  KEY `w3` (`b`),
  KEY `w4` (`c`),
  KEY `w5` (`d`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> explain select * from w2 where id=1;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | w2    | NULL       | ref  | w1            | w1   | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from w2 where a='1';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | w2    | NULL       | ref  | w2            | w2   | 16      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from w2 where b='1';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | w2    | NULL       | ref  | w3            | w3   | 18      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from w2 where c='1';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | w2    | NULL       | ref  | w4            | w4   | 15      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from w2 where d='1';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | w2    | NULL       | ref  | w5            | w5   | 17      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

结论

 

varchar 是可变要加2，NULL是为空要加1

公式：

 key_len = 定义长度*5 + NULL（1）+可变（2）