MySQL :: MySQL 9.0 參考手冊 :: 10.3.11 優化器對產生式資料行索引的使用

版本 9.0

8.4 目前
8.0
5.7

8.0 日文

MySQL 9.0 參考手冊 / ... / 優化器對產生式資料行索引的使用

10.3.11 優化器對產生式資料行索引的使用

MySQL 支援產生式資料行的索引。例如

CREATE TABLE t1 (f1 INT, gc INT AS (f1 + 1) STORED, INDEX (gc));

產生式資料行 gc 被定義為運算式 f1 + 1。該資料行也已建立索引，且優化器可以在執行計畫建構期間將該索引納入考量。在下列查詢中，WHERE 子句參考 gc，且優化器會考量該資料行的索引是否產生更有效率的計畫

SELECT * FROM t1 WHERE gc > 9;

即使在查詢中沒有直接依名稱參考這些資料行的情況下，優化器也可以使用產生式資料行的索引來產生執行計畫。如果 WHERE、ORDER BY 或 GROUP BY 子句參考符合某個已建立索引之產生式資料行定義的運算式，就會發生這種情況。下列查詢沒有直接參考 gc，但確實使用符合 gc 定義的運算式

SELECT * FROM t1 WHERE f1 + 1 > 9;

優化器會辨識出運算式 f1 + 1 符合 gc 的定義，且 gc 已建立索引，因此會在執行計畫建構期間考量該索引。您可以使用 EXPLAIN 來查看此情況

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: 1
     filtered: 100.00
        Extra: Using index condition

實際上，優化器已將運算式 f1 + 1 取代為符合該運算式的產生式資料行名稱。在 SHOW WARNINGS 顯示的擴充 EXPLAIN 資訊中提供的改寫查詢中，也可以看出這一點

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`gc`
         AS `gc` from `test`.`t1` where (`test`.`t1`.`gc` > 9)

下列限制和條件適用於優化器對產生式資料行索引的使用

若要使查詢運算式符合產生式資料行定義，運算式必須完全相同，且必須具有相同的結果類型。例如，如果產生式資料行運算式為 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 比較器會處理引號的移除，但這不會發生在索引查詢中。）例如，不要這樣寫欄位定義：
```
doc_name TEXT AS (JSON_EXTRACT(jdoc, '$.name')) STORED
```
而是這樣寫：
```
doc_name TEXT AS (JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name'))) STORED
```
使用後者的定義，最佳化工具可以偵測到這兩個比較的匹配：
```
... WHERE JSON_EXTRACT(jdoc, '$.name') = 'some_string' ...
... WHERE JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name')) = 'some_string' ...
```
在欄位定義中沒有 JSON_UNQUOTE() 的情況下，最佳化工具僅偵測到第一個比較的匹配。
如果最佳化工具選擇了錯誤的索引，可以使用索引提示來停用它，並強制最佳化工具做出不同的選擇。