MySQL 生成列索引

MySQL支援在生成列上建立索引，例如:

mysql> create table t1(f1 int,gc int as (f1+1) stored,index(gc));
Query OK, 0 rows affected (0.11 sec)
mysql> insert into t1(f1) values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0
mysql> select * from t1;
+------+------+
| f1   | gc   |
+------+------+
|    1 |    2 |
|    2 |    3 |
|    3 |    4 |
|    4 |    5 |
|    5 |    6 |
|    6 |    7 |
|    7 |    8 |
|    8 |    9 |
|    9 |   10 |
|   10 |   11 |
+------+------+
10 rows in set (0.00 sec)

生成列gc，它的定義為表示式f1+1。這個列gc也建立了索引因此在生成執行計劃時最佳化器可以使用這個索引。下面的查詢where條件引用了列gc並且最佳化器會考慮使用這個索引是否可以生成更有效的執行計劃:

mysql> explain select * from t1 where gc>9\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: gc
          key: gc
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index condition

最佳化器可以在生成的列上使用索引來生成執行計劃，即使在查詢中沒有按名稱直接引用這些列的情況下也是如此。如果where，order by 或group by子句引用的表示式與某些被索引的生成列相匹配就會出現這種情況。下面的查詢沒有直接引用生成列gc，但使用的表示式與生成列gc的定義匹配:

mysql> explain select * from t1 where f1+1>9\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: gc
          key: gc
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index condition

最佳化器識別出了表示式f1+1與生成列gc相匹配並且gc列建立了索引，所以最佳化器在生成執行計劃時考慮使用了該索引。

實際上，最佳化器已經用與表示式匹配的生成列的名稱gc替換了表示式f1 + 1。這在執行show warnings命令所顯示的擴充套件解釋資訊中可以很明顯地看到重寫查詢語句確實用生成列替換了表示式。

mysql> show warnings\G;
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `mysql`.`t1`.`f1` AS `f1`,`mysql`.`t1`.`gc` AS `gc` from `mysql`.`t1` where (`mysql`.`t1`.`gc` > 9)
1 row in set (0.00 sec)

最佳化器使用生成列索引有以下限制和條件:
.對於一個查詢語句的表示式是否成生成列定義相匹配，那麼表示式必須完全相同並且有相同的結果型別。例如，如果生成列表示式是f1+1，如果查詢使用1+f1或者如查f1+1(一個整數表示式)與字元進行比較而不是數字那麼最佳化器將不會認為是匹配的。

.對於這些操作:=,< ,<=,>,>=,between和in最佳化器處理是，對於不是between和in之外的其它運算子而言，任何一個運算元都可以被匹配的生成列所替換。對於between和in運算子，只有第一個引數可以被匹配的生成列替換，並且其它的引數必須要有相同的結果型別。between和in運算子目前還不支援對JSON的呼叫。

.生成列必須定義為至少包含一個函式呼叫或前一項中提到的一個運算子的表示式。表示式不能只是簡單的引用其它列。例如，gc int as (f1) stored，這個生成列的定義只是簡單的引用了一個列，因此在生成列gc上的索引不會被最佳化器考慮。

.為了比較字串與呼叫JSON函式返回帶引號的字串的被索引的生成列，JSON_UNQUOTE()函式需要在生成列定義中用來刪除函式返回值的引號(對於直接比較字串與函式值，JSON比較器控制程式碼會刪了引號，但在索引查詢時不會發生)。

.如果最佳化器未能選擇所需的索引，則可以使用索引提示強制最佳化器做出不同的選擇。