专门找人做软件的网站,成都知名网站建设,外包服务费税率,怎么创建自己的网络平台mysql的explain命令可以分析sql的性能#xff0c;其中有一项是key_len(索引的长度)的统计。本文将分析mysql explain中key_len的计算方法。1、创建测试表及数据CREATE TABLE member (id int(10) unsigned NOT NULL AUTO_INCREMENT,name varchar(20) DEFAULT NULL,age tinyint(…mysql的explain命令可以分析sql的性能其中有一项是key_len(索引的长度)的统计。本文将分析mysql explain中key_len的计算方法。1、创建测试表及数据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)) ENGINEInnoDB DEFAULT CHARSETutf8;INSERT INTO member (id, name, age) VALUES (NULL, fdipzone, 18), (NULL, jim, 19), (NULL, tom, 19);2、查看explainname的字段类型是varchar(20)字符编码是utf8一个字符占用3个字节那么key_len应该是 20*360。mysql explain select * from member where namefdipzone;-------------------------------------------------------------------------------------------------| 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 |-------------------------------------------------------------------------------------------------explain的key_len为63多出了3。name字段是允许NULL把name改为NOT NULL再测试ALTER TABLE member CHANGE name name VARCHAR(20) NOT NULL;mysql explain select * from member where namefdipzone;-------------------------------------------------------------------------------------------------| 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 |-------------------------------------------------------------------------------------------------现在key_len为62比刚才少了1但还是多了2。可以确定字段为NULL会多占用一个字节。name字段类型为varchar属于变长字段把varchar改为char再测试ALTER TABLE member CHANGE name name CHAR(20) NOT NULL;mysql explain select * from member where namefdipzone;-------------------------------------------------------------------------------------------------| 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 |-------------------------------------------------------------------------------------------------改为定长字段后key_len为60与预测的一致。总结使用变长字段需要额外增加2个字节使用NULL需要额外增加1个字节因此对于是索引的字段最好使用定长和NOT NULL定义提高性能。以上这篇浅谈mysql explain中key_len的计算方法就是小编分享给大家的全部内容了希望能给大家一个参考也希望大家多多支持脚本之家。
