MySQL查詢計劃key_len全知道

MySQL查詢計劃key_len全知道

本文首先介紹了MySQL的查詢計劃中ken_len的含義；然後介紹了key_len的計算方法；最後通過一個偽造的例子,來說明如何通過key_len來查看聯合索引有多少列被使用。

key_len的含義

在MySQL中,可以通過explain查看SQL語句所走的路徑,如下所示：

mysql> create table t(a int primary key, b int not null, c int not null, index(b));
Query OK, 0 rows affected (0.01 sec)
mysql> explain select b from t ;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra      |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t    | index | NULL          | b    | 4      | NULL |    1 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

其中,key_len表示使用的索引長度,是以位元組為單位。在上面的例子中,由於int型佔用4個位元組,而索引中只包含了1列,所以,key_len是4。

下面是聯合索引的情況：

mysql> alter table t add index ix(b, c);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select b, c from t ;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra      |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t    | index | NULL          | ix  | 8      | NULL |    1 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

聯合索引ix包含了2列,並且都使用到了,所以,這裡ken_len是8。

到這裡,我們已經可以理解key_len的含義了,似乎已經沒有什麼可講的了,但是,MySQL中key_len的計算還有很多需要注意的地方。

例如,我們將b這一列的NOT NULL約束去掉,然後ken_len就和我們預期不一樣了,如下所示：

mysql> alter table t modify b int;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> explain select b from t;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra      |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t    | index | NULL          | b    | 5      | NULL |    1 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
 

MySQL中key_len計算規則

MySQL中,key_len的計算規則如下：
1.如果列可以為空白,則在資料類型佔用位元組的基礎上加1,如int型,不可為空key_len為4,可以為空白key_len為5
2.如果列是變長的,則在資料列所佔位元組的基數上再加2,如varbinary(10),不可為空,則key_len為10  + 2 ,可以為空白則key_len為10+2+1
3.如果是字元型,則還需要考慮字元集,如某列的定義是varchar(10),且是utf8,不可為空,則key_len為10 * 3 + 2,可以為空白則key_len為10*3+2+1

此外,decimal列的計算方法與上面一樣,如果可以為空白,則在資料類型佔用位元組的基礎上加1,但是,decimal本身所佔用位元組數,計算就比較複雜。

根據官方文檔可以知道,decimal定義為decimal(M,D),其中,M是總的位元,D是小數點後保留的位元。小數點前與小數點後的數字分開儲存,且以9位元為1組,用4個位元組儲存,如果低於9位元,需要的位元組數如下：

Leftover Digits Number of Bytes
-----------------------------
|0              |0          |
|1-2            |1          |
|3-4            |2          |
|5-6            |3          |
|7-9            |4          |
-----------------------------

例如：
•decimal(20,6)=> 小數點左邊14位,小數點右邊6位 => 小數點左邊分組為5 + 9,需要3個位元組+4個位元組儲存,小數點一個分組,需要3個位元組儲存 => 總共需要10個位元組
•decimal(18,9)=> 小數點左邊9位元,小數點右邊9位元 => 分別使用4個位元組儲存 => 共需要 8個位元組
•decimal(18,2)=> 小數點左邊16位元,小數點右邊2位元 => 分組為7 + 9,需要8個位元組儲存,小數點右邊1個位元組儲存 => 共需要9個位元組

通過key_len分析聯合索引

如下所示,我們定義了一個表t,表t包含a、b、c、d共4列：

mysql> show create table t\G
*************************** 1. row ***************************
      Table: t
Create Table: CREATE TABLE `t` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `ix_x` (`b`,`d`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

現在要執行SQL語句如下：

select a from t where b = 5 and d = 10 order by c;

假設我們有一個索引ix_x(b,d,c),通過explain得到如下輸出：

mysql> explain select a from t where b = 5 and d = 10 order by c;
+----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref        | rows | Extra                    |
+----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+
|  1 | SIMPLE      | t    | ref  | ix_x          | ix_x | 10      | const,const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+
1 row in set (0.00 sec)

可以看到,查詢語句使用了聯合索引中的b和d兩列來過濾資料。

如果我們定義的聯合索引不是`ix_x(b,d,c)`,而是`ix_x(b, c, d)`,通過explain得到的輸入如下：

mysql> alter table t drop index ix_x;
mysql> alter table t add index ix_x(b, c, d);
mysql> explain select a from t where b = 5 and d = 10 order by c;
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | t    | ref  | ix_x          | ix_x | 5      | const |    2 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

key_len為5,也就是說,只用到了聯合索引中的第一列,可以看到,雖然聯合索引包含了我們要查詢的所有列,但是,由於定義的順序問題,SQL語句並不能夠充分利用索引。

本文永久更新連結地址：