《Mysql應(yīng)用淺談mysql explain中key_len的計算方法》要點(diǎn)：
本文介紹了Mysql應(yīng)用淺談mysql explain中key_len的計算方法，希望對您有用。如果有疑問，可以聯(lián)系我們。
MYSQL入門mysql的explain命令可以分析sql的性能,其中有一項(xiàng)是key_len(索引的長度)的統(tǒng)計.本文將分析mysql explain中key_len的計算辦法.
MYSQL入門1、創(chuàng)建測試表及數(shù)據(jù)
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個字節(jié),那么key_len應(yīng)該是 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入門現(xiàn)在key_len為62,比剛才少了1,但還是多了2.可以確定,字段為NULL會多占用一個字節(jié).
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,與預(yù)測的一致.
MYSQL入門總結(jié)：使用變長字段需要額外增加2個字節(jié),使用NULL需要額外增加1個字節(jié),因此對于是索引的字段,最好使用定長和NOT NULL定義,提高性能.
MYSQL入門以上這篇淺談mysql explain中key_len的計算辦法就是小編分享給大家的全部內(nèi)容了,希望能給大家一個參考,也希望大家多多支持維易PHP.
維易PHP培訓(xùn)學(xué)院每天發(fā)布《Mysql應(yīng)用淺談mysql explain中key_len的計算方法》等實(shí)戰(zhàn)技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培養(yǎng)人才。
轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/8057.html
