mysql explain 中key_len的計算

今天丁原問我mysql執行計劃中的key_len是怎麼計算得到的，當時還沒有注意，在高性能的那本書講到過這個值的計算，但是自己看執行計劃的時候一直都沒有太在意這個值，更不用說深讨這個值的計算了：

ken_len表示索引使用的位元組數，根據這個值，就可以判斷索引使用情況，特别是在組合索引的時候，判斷所有的索引字段都被查詢用到。

在檢視官方文檔的時候，也沒有發現詳細的key_len的計算介紹，後來做了一些測試，在咨詢了丁奇關于變長資料類型的值計算的時候，突然想到innodb 行的格式，在這裡的計算中有點類似，總結一下需要考慮到以下一些情況：

(1).索引字段的附加資訊：可以分為變長和定長資料類型讨論，當索引字段為定長資料類型，比如char，int，datetime，需要有是否為空的标記，這個标記需要占用1個位元組；對于變長資料類型，比如：varchar，除了是否為空的标記外，還需要有長度資訊，需要占用2個位元組；

(備注：當字段定義為非空的時候，是否為空的标記将不占用位元組)

(2).同時還需要考慮表所使用的字元集，不同的字元集，gbk編碼的為一個字元2個位元組，utf8編碼的一個字元3個位元組;

先看定長資料類型的一個例子(編碼為gbk)：

root@test 07:32:39>create table test_char(id int not null ,name_1 char(20),name_2 char(20),

-> primary key(id),key ind_name(name_1,name_2))engine=innodb charset=gbk;

root@test 07:33:55>insert into test_char values(1,’xuancan’,’taobaodba’);

root@test 07:34:55>explain select * from test_char where name_1=’xuancan’\g;

*************************** 1. row ***************************

id: 1

select_type: simple

table: test_char

type: ref

possible_keys: ind_name

key: ind_name

key_len: 41

ref: const

rows: 1

extra: using where; using index

key_len=41=20*2+1（備注：由于name_1為空，isnull的标記被打上，需要計算1個位元組）

root@test 07:35:31>explain select * from test_char where name_1=’xuancan’ and name_2=’taobaodba’\g;

key_len: 82

ref: const,const

key_len=82=20*2+20*2+1+1（備注：由于name_1,name_2兩列被使用到，但兩列都為為空，需要計算2個位元組）

變長資料類型（gbk編碼）：

root@test 08:30:51>create table test_varchar(id int not null ,name_1 varchar(20),name_2 varchar(20),

root@test 08:37:51>insert into test_varchar values(1,’xuancan’,’taobaodba’);

root@test 08:38:14>explain select * from test_varchar where name_1=’xuancan’\g;

table: test_varchar

key_len: 43

key_len=43=20*2+1+2（備注：由于為name_1字段定義為空，是以需要計算1,；同時由于是變長字段varchar，是以需要加上2）

root@test 08:38:46>alter table test_varchar modify column name_1 varchar(20) not null;

query ok, 1 row affected (0.52 sec)

records: 1 duplicates: 0 warnings: 0

root@test 08:42:11>explain select * from test_varchar where name_1=’xuancan’\g;

key_len: 42

key_len=42=20*2+2(備注由于name_1字段修改為not null之後，isnull的标記鎖占用的位元組釋放掉，但是變長字段長度所占用的2個位元組沒有釋放)；

上面是測試gbk編碼的測試，同時也可以測試一下其他編碼的key_len計算。