MySQL執行計劃裡面的key_len

以前看MySQL的執行計劃，感覺内容有些簡陋，平時分析主要就是看是否全表掃描，索引使用是否合理等。基本上也能分析出很多問題來，但是顯然有時候會有些疑惑，那就是對于複合索引，多列值的情況下，到底啟用了那些索引列，這個時候索引的使用情況就很值得琢磨琢磨了，我們得根據執行計劃裡面的key_len做一個重要的參考。

   我們做一個簡單的測試來說明。

   CREATE TABLE `department` (

`DepartmentID` int(11) DEFAULT NULL,

`DepartmentName` varchar(20) DEFAULT NULL,

KEY `IND_D` (`DepartmentID`),

KEY `IND_DN` (`DepartmentName`)

) ENGINE=InnoDB DEFAULT CHARSET=gbk;

運作語句為：explain select count(*)from department\G

對于這個語句，key_len到底是多少呢？

mysql> explain select count(*)from department\G

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

           id: 1

  select_type: SIMPLE

        table: department

         type: index

possible_keys: NULL

          key: IND_D

      key_len: 5

          ref: NULL

         rows: 1

        Extra: Using index

1 row in set (0.00 sec)

在這個例子裡面，possible_keys,key,Extra你看了可能有些暈，我們看看key_len的值為5，這個值是怎麼算出來的呢，首先表有兩個字段，第一個字段的類型為數值，int的長度為4，因為字段可為null,是以需要一個位元組來存儲，這樣下來就是4+1=5了。由此我們可以看到這個語句是啟用了索引ind_d.

  那我們舉一反三，把語句修改一下，看看key_len的變化。

mysql>  explain select departmentName from department b where departmentName='TEST'\G

        table: b

         type: ref

possible_keys: IND_DN

          key: IND_DN

      key_len: 43

          ref: const

        Extra: Using where; Using index

1 row in set (0.09 sec)

從上面可以看到，key_len為43，這個值是怎麼算出來的呢，我們來掰扯一下，字段2為字元型，長度20，因為是GBK字元集，是以需要乘以2，因為允許字段為NULL，則需要一個位元組，對于變長的類型（在此就是VARCHAR）,key_len還要加2位元組。這樣下來就是20*2+1+2=43

   到了這裡僅僅是個開始，我們需要看看略微複雜的情況，就需要複合索引了。我們就換一個表test_keylen2

create table test_keylen2 (c1 int not null,c2 int not null,c3 int not null);

alter table test_keylen2 add key  idx1(c1, c2, c3);

下面的語句就很實際了，

explain     SELECT *from test_keylen2 WHERE c1=1 AND c2=1 ORDER BY c1\G    

這個語句中，keylen到底是應該為4或者8還是12呢？ 我們就需要驗證一下了。

mysql> explain     SELECT *from test_keylen2 WHERE c1=1 AND c2=1 ORDER BY c1\G     

        table: test_keylen2

possible_keys: idx1

          key: idx1

      key_len: 8

          ref: const,const

1 row in set (0.07 sec)

顯然key_len隻計算了where中涉及的列，因為是數值類型，是以就是4+4=8

那下面的這個語句呢。

explain   SELECT *from test_keylen2 WHERE c1>=1 and c2=2 \G 

我們添加一個範圍，看看這個該如何拆分。

mysql> explain   SELECT *from test_keylen2 WHERE c1>=1 and c2=2 \G  

      key_len: 12

在這裡就不隻是計算where中的列了，而是因為>1的條件直接選擇了3個列來計算。

  對于date類型的處理，有一個很細小的差别。我們再換一個表，含有事件類型的字段，

CREATE TABLE `tmp_users` (

`id` int(11) NOT NULL

AUTO_INCREMENT,

`uid` int(11) NOT NULL,

`l_date` datetime NOT NULL,

`data` varchar(32) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `ind_uidldate` (`uid`,`l_date`)

下面的語句key_len該如何計算呢。

explain select * from tmp_users where uid = 9527 and l_date >= '2012-12-10 10:13:17'\G

這一點出乎我的意料，按照datetime的印象是8個位元組，是以應該是8+4=12，但是這裡卻偏偏是9，這個數字怎麼計算的。

        table: tmp_users

         type: range

possible_keys: ind_uidldate

          key: ind_uidldate

      key_len: 9

        Extra: Using index condition

這裡就涉及到一個技術細節，是在MySQL 5.6中的datetime的存儲差别。在5.6.4以前是8個位元組，之後是5個位元組

是以按照這個算法就是4+5=9