《Mysql应用浅谈mysql explain中key_len的计算方法》要点：
本文介绍了Mysql应用浅谈mysql explain中key_len的计算方法，希望对您有用。如果有疑问，可以联系我们。
MYSQL入门mysql的explain命令可以分析sql的性能,其中有一项是key_len(索引的长度)的统计.本文将分析mysql explain中key_len的计算办法.
MYSQL入门1、创建测试表及数据
MYSQL入门
CREATE TABLE `member` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`age` tinyint(3) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `member` (`id`, `name`, `age`) VALUES (NULL, 'fdipzone', '18'), (NULL, 'jim', '19'), (NULL, 'tom', '19');
MYSQL入门2、查看explain
MYSQL入门name的字段类型是varchar(20),字符编码是utf8,一个字符占用3个字节,那么key_len应该是 20*3=60.
MYSQL入门
mysql> explain select * from `member` where name='fdipzone';
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+
| 1 | SIMPLE | member | ref | name | name | 63 | const | 1 | Using index condition |
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+
MYSQL入门explain的key_len为63,多出了3.
MYSQL入门name字段是允许NULL,把name改为NOT NULL再测试
MYSQL入门
ALTER TABLE `member` CHANGE `name` `name` VARCHAR(20) NOT NULL;
mysql> explain select * from `member` where name='fdipzone';
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+
| 1 | SIMPLE | member | ref | name | name | 62 | const | 1 | Using index condition |
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+
MYSQL入门现在key_len为62,比刚才少了1,但还是多了2.可以确定,字段为NULL会多占用一个字节.
MYSQL入门name字段类型为varchar,属于变长字段,把varchar改为char再测试
MYSQL入门
ALTER TABLE `member` CHANGE `name` `name` CHAR(20) NOT NULL;
mysql> explain select * from `member` where name='fdipzone';
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+
| 1 | SIMPLE | member | ref | name | name | 60 | const | 1 | Using index condition |
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+
MYSQL入门改为定长字段后,key_len为60,与预测的一致.
MYSQL入门总结：使用变长字段需要额外增加2个字节,使用NULL需要额外增加1个字节,因此对于是索引的字段,最好使用定长和NOT NULL定义,提高性能.
MYSQL入门以上这篇浅谈mysql explain中key_len的计算办法就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持维易PHP.
维易PHP培训学院每天发布《Mysql应用浅谈mysql explain中key_len的计算方法》等实战技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培养人才。
转载请注明本页网址：
http://www.vephp.com/jiaocheng/8057.html
