FAQ系列 | index extensions特性介紹

0、導讀

本文介紹MySQL的index extensions特性，以及如何利用這個特性實作SQL查詢優化。

1、什麼是index extensions

index extensions是MySQL 5.6.9之後的新特性，關于這個特性，手冊中的解釋是這樣的：InnoDB automatically extends each secondary index by appending the primary key columns to it（出處詳見手冊 8.2.1.7 Use of Index Extensions，原文連結：https://dev.mysql.com/doc/refman/5.6/en/index-extensions.html ）。簡言之就是，InnoDB引擎表中，會把主鍵所有列值附加存儲在輔助索引中。

假設有這樣一個表：

CREATE TABLE t(

a int not null,

b int not null,

c int not null,

d int not null,

PRIMARY KEY(a, b),

KEY i_c(c)

) ENGINE=InnoDB;

意思是，該表中的輔助索引 i_c 的索引鍵值，實際上也同時存儲了主鍵中的兩個列值，也就是說，i_c 的索引資料結構中，實際上存儲的列是：c、a、b 三列的值。

我們可通過 innodb_table_monitor 檢視驗證下：

TABLE: name test/t, id 681, flags 1, columns 7, indexes 2, appr.rows 0

 COLUMNS: a: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; b: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; c: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; d: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;

 INDEX: name PRIMARY, id 1159, fields 2/6, uniq 2, type 3

  root page 3, appr.key vals 0, leaf pages 1, size pages 1

  FIELDS:  a b DB_TRX_ID DB_ROLL_PTR c d

 INDEX: name i_c, id 1160, fields 1/3, uniq 3, type 0

  root page 4, appr.key vals 0, leaf pages 1, size pages 1

  FIELDS:  c a b

可見，确實是如此。我們順便也看到 PRIMARY KEY 裡包含了所有的列值，以及 DB_TRX_ID、DB_ROLL_PTR 等額外屬性（InnoDB引擎獨有特性，用于實作InnoDB的事務）。

2、怎麼利用index extensions

事實上，輔助索引實際也存儲主鍵值的特性，在InnoDB引擎中一直都是如此，隻是從5.6.9版本開始後，在計算執行計劃時，查詢優化器（optimizer）才能識别到這個特性，并且利用這個特性。而在5.6.9以前，雖然這個特性也存在，但并不被查詢優化器識别，也就無法被利用了。

這個特性可适用于 ref, range, and index_merge 等多種索引通路方式，在稀松索引掃描（loose index scan）、聯接（join）、排序以及MIN()/MAX()等場景下。

我們來看看這個特性怎麼被優化器識别并利用的，假設上述測試表中的測試資料有：

SELECT * FROM t;

+—-+—-+—-+—-+

| a | b | c | d |

| 1 | 2 | 4 | 2 |

| 1 | 3 | 2 | 2 |

| 1 | 4 | 9 | 2 |

| 1 | 5 | 9 | 2 |

| 1 | 6 | 8 | 2 |

| 2 | 2 | 9 | 2 |

| 3 | 2 | 8 | 2 |

| 4 | 2 | 6 | 2 |

| 5 | 2 | 6 | 2 |

| 6 | 2 | 1 | 2 |

MySQL版本：5.6.21-70.0-log Percona Server (GPL), Release 70.0, Revision 688。

假設有下面的查詢，看下它的執行計劃：

mysql> DESC SELECT a,b,c FROM t WHERE a = 1 AND c = 9\G

          id: 1

 select_type: SIMPLE

       table: t

        type: ref

possible_keys: PRIMARY,i_c

         key: i_c

     key_len: 8

         ref: const,const

        rows: 2

       Extra: Using index

在5.6.9以前的版本（或者修改優化器開關，關閉 index extensions 特性。如果用5.6.9以後的版本測試，還請記得）：

mysql> DESC SELECT a,b,c FROM t WHERE a = 1 AND c = 9\G

     key_len: 4

         ref: const

        rows: 3

       Extra: Using where; Using index

可執行下面的指令關閉 index extensions 特性：

mysql> SET optimizer_switch = ‘use_index_extensions=off’;

這兩個執行計劃的差別在于：

• 前者的key_len是8而後者是4，預示着可以用到的索引不僅是i_c這個索引，還有主鍵索引；
• 前者的ref列值是const,const，而後者隻有const，預示着前者用到了2個索引部分，而後者隻有一個；
• 前者評估的rows為2，而後者評估的rows為3，因為前者效率更高；
• 後者的Extra列中多了Using Where，表示後者還需要從結果中再次過濾資料，而不能像前者那樣直接利用索引取得結果。

我們還可以根據觀察STATUS中的Handler_read_%值差異來對比兩個SQL的實際執行代價（執行FLUSH STATUS後，執行查詢SQL，再執行SHOW STATUS LIKE ‘Handler_read_%’ 檢視）：

• 後者的代價是 Handler_read_next = 3；
• 前者的代價是 Handler_read_next = 2；
• 如果資料量更大的話，這個內插補點也會随之增大。

由此可見，前者的效率确實要比後者來的更高。

3、後記

我們應該經常關注新版本的新特性，利用這些新特性提升SQL效率 ：）