《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全面培養人才。
