10.3.11 Optimizer Use of Generated Column Indexes — MySQL 8.4

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