mysql explain 中的key_len 的长度计算

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql explain 中的key_len 的长度计算，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5931字，纯文字阅读大概需要9分钟。

内容图文

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）

内容总结

以上是互联网集市为您收集整理的mysql explain 中的key_len 的长度计算全部内容，希望文章能够帮你解决mysql explain 中的key_len 的长度计算所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端