MySQL InnoDB的索引擴充套件

索引擴充套件,InnoDB透過將主鍵列附加到每個輔助索引中來自動擴充套件該索引。建立如下表結構:

mysql> CREATE TABLE t1 (
    -> i1 INT NOT NULL DEFAULT 0,
    -> i2 INT NOT NULL DEFAULT 0,
    -> d DATE DEFAULT NULL,
    -> PRIMARY KEY (i1, i2),
    -> INDEX k_d (d)
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.14 sec)

表t1在列(i1,i2)上定義了主鍵。同時也在列(d)上定義了一個輔助索引，但InnoDB擴充套件了這個索引並且將它視為(d,i1,i2)來處理。

在決定如何使用以及是否使用該索引時，最佳化器會考慮擴充套件輔助索引的主鍵列。這可以產生更高效的查詢執行計劃和更好的效能。

最佳化器可以使用擴充套件的二級索引來進行ref、range和index_merge索引訪問，進行鬆散索引掃描，進行連線和排序最佳化，以及進行MIN()/MAX()最佳化。

下面的示例將顯示最佳化器是否使用擴充套件輔助索引來影響執行計劃 向表t1插入以下資料:

mysql> INSERT INTO t1 VALUES (1, 1, '1998-01-01'), (1, 2, '1999-01-01'), (1, 3, '2000-01-01'), (1, 4, '2001-01-01'),
    ->(1, 5, '2002-01-01'), (2, 1, '1998-01-01'), (2, 2, '1999-01-01'), (2, 3, '2000-01-01'),  (2, 4, '2001-01-01'),
    ->(2, 5, '2002-01-01'), (3, 1, '1998-01-01'), (3, 2, '1999-01-01'), (3, 3, '2000-01-01'), (3, 4, '2001-01-01'),
    ->(3, 5, '2002-01-01'), (4, 1, '1998-01-01'), (4, 2, '1999-01-01'), (4, 3, '2000-01-01'), (4, 4, '2001-01-01'),
    ->(4, 5, '2002-01-01'), (5, 1, '1998-01-01'), (5, 2, '1999-01-01'),  (5, 3, '2000-01-01'), (5, 4, '2001-01-01'),
    ->(5, 5, '2002-01-01');
Query OK, 25 rows affected (0.05 sec)
Records: 25  Duplicates: 0  Warnings: 0

假設執行下面的查詢:

SET optimizer_switch = 'use_index_extensions=off';
explain select count(*) from t1 where i1=3 and d= '2000-01-01' ;

在這種情況下，最佳化器不能使用主鍵，因為主鍵包含列(i1、i2)，並且查詢沒有引用i2。相反，最佳化器可以使用列(d)上的輔助索引k_d，執行計劃取決於是否使用擴充套件索引。

當最佳化器不考慮索引擴充套件時，它將索引k_d僅視為(d)

mysql> SET optimizer_switch = 'use_index_extensions=off';
Query OK, 0 rows affected (0.00 sec)
mysql> explain select count(*) from t1 where i1=3 and d= '2000-01-01' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: PRIMARY,k_d
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 5
     filtered: 20.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

當最佳化器考慮到索引擴充套件時，它將k_d視為(d, i1, i2)。在這種情況下，它可以使用最左邊的索引字首(d, i1)來生成更好的執行計劃

mysql> SET optimizer_switch = 'use_index_extensions=on';
Query OK, 0 rows affected (0.00 sec)
mysql> explain select count(*) from t1 where i1=3 and d= '2000-01-01' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: PRIMARY,k_d
          key: k_d
      key_len: 8
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

在這兩種情況下，key表示最佳化器將使用輔助索引k_d，但是EXPLAIN輸出顯示了使用擴充套件索引所帶來的這些改進:
.key_len從4位元組變成了8位元組，指示鍵查詢使用了列d和i1，不僅僅是d。
.ref的值從const變成了const,const,因為鍵查詢使用兩個鍵的列而不是一個。
.rows:從5減到1，指示InnoDB將會檢查更少的行來生成查詢結果。
.Extra值從Using where;Using index變成了Using index。這意味著查詢記錄只需要使用索引而不用查詢資料行記錄。

可以使用show status來檢視最佳化器在使用與不使用擴充套件索引時的差異:

mysql> flush table t1;
Query OK, 0 rows affected (0.01 sec)
mysql> flush status;
Query OK, 0 rows affected (0.03 sec)

上面的flush table和flush status語句用來清除表的快取和清除狀資料統計資料。

不使用索引擴充套件時show status產生的結果如下:

mysql> SET optimizer_switch = 'use_index_extensions=off';
Query OK, 0 rows affected (0.01 sec)
mysql> select count(*) from t1 where i1=3 and d= '2000-01-01';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)
mysql> show status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 5     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.00 sec)

使用索引擴充套件時，show status產生的結果如下，其中handler_read_next的值從5減到1，指示使用這個索引更有效率:

mysql> flush table t1;
Query OK, 0 rows affected (0.01 sec)
mysql> flush status
    -> ;
Query OK, 0 rows affected (0.02 sec)
mysql> SET optimizer_switch = 'use_index_extensions=on';
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from t1 where i1=3 and d= '2000-01-01';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)
mysql> show status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 1     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.01 sec)

系統變數optimizer_switch的use_index_extensions標誌允許最佳化器在決定如何使用InnoDB表的輔助索引時使不使用主鍵列。預設情況下，use_index_extensions是啟用的。為了檢查禁用索引擴充套件是否可以提高效能可以執行以下語句:

mysql> SET optimizer_switch = 'use_index_extensions=off';
Query OK, 0 rows affected (0.01 sec)