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: 0mysql> 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的計算規則如下：
根據官方文檔可以知道,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: tCreate 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=utf81 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語句并不能夠充分利用索引。
新聞熱點
疑難解答
