8.3.10 Optimizer Use of Generated Column Indexes — MySQL 5.7

8.3.10 Использование оптимизатором индексов на сгенерированных столбцах

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 именем сгенерированного столбца, который соответствует выражению. Это также видно в переписанном запросе, доступном в расширенной EXPLAIN информации, отображаемой SHOW WARNINGS:

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() в определении столбца оптимизатор обнаруживает соответствие только для первого из этих сравнений.
Если оптимизатор не выберет желаемый индекс, можно использовать подсказку индекса, чтобы заставить оптимизатор сделать другой выбор.