15.1.20.9 Secondary Indexes and Generated Columns — MySQL 8.4

15.1.20.9 Вторичные индексы и сгенерированные столбцы

InnoDB поддерживает вторичные индексы по виртуальным сгенерированным столбцам. Другие типы индексов не поддерживаются. Вторичный индекс, определённый по виртуальному столбцу, иногда называют “виртуальным индексом”.

Вторичный индекс может быть создан по одному или нескольким виртуальным столбцам или по сочетанию виртуальных и обычных столбцов или хранимых сгенерированных столбцов. Вторичные индексы, включающие виртуальные столбцы, могут быть определены как UNIQUE.

При создании вторичного индекса по виртуальному сгенерированному столбцу значения сгенерированного столбца материализуются в записях индекса. Если индекс является полным (включает все столбцы, получаемые запросом), значения сгенерированного столбца извлекаются из материализованных значений в структуре индекса, вместо вычисления “на лету”.

При использовании вторичного индекса по виртуальному столбцу необходимо учитывать дополнительные расходы на запись, связанные с вычислением значений виртуального столбца при материализации в записях вторичного индекса во время операций INSERT и UPDATE. Даже с дополнительными расходами на запись, вторичные индексы по виртуальным столбцам могут быть предпочтительнее, чем сгенерированные хранимые столбцы, которые материализуются в кластеризованном индексе, что приводит к увеличению таблиц, требующих больше места на диске и памяти. Если вторичный индекс не определён по виртуальному столбцу, существуют дополнительные расходы на чтение, так как значения виртуального столбца должны вычисляться каждый раз при проверке строки столбца.

Значения индексированного виртуального столбца регистрируются с помощью MVCC, чтобы избежать ненужного повторного вычисления значений сгенерированного столбца во время отката или при очистке операции. Длина данных, подлежащих регистрации, ограничена пределом ключа индекса в 767 байт для COMPACT и REDUNDANT форматов строк, и 3072 байта для DYNAMIC и COMPRESSED форматов строк.

Добавление или удаление вторичного индекса по виртуальному столбцу — это операция на месте.

Индексирование сгенерированного столбца для предоставления индекса JSON-столбца

Как отмечалось ранее, столбцы JSON не могут быть индексированы напрямую. Для создания индекса, косвенно ссылающегося на такой столбец, можно определить сгенерированный столбец, который извлекает информацию, которая должна быть проиндексирована, затем создать индекс по сгенерированному столбцу, как показано в этом примере:

mysql> CREATE TABLE jemp (
    ->     c JSON,
    ->     g INT GENERATED ALWAYS AS (c->"$.id"),
    ->     INDEX i (g)
    -> );
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO jemp (c) VALUES
     >   ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'),
     >   ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT c->>"$.name" AS name
     >     FROM jemp WHERE g > 2;
+--------+
| name   |
+--------+
| Barney |
| Betty  |
+--------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT c->>"$.name" AS name
     >    FROM jemp WHERE g > 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: jemp
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select json_unquote(json_extract(`test`.`jemp`.`c`,'$.name'))
AS `name` from `test`.`jemp` where (`test`.`jemp`.`g` > 2)
1 row in set (0.00 sec)

(Мы обернули вывод последнего оператора в этом примере, чтобы он поместился в область просмотра.)

При использовании EXPLAIN на SELECT или другом SQL-запросе, содержащем одну или несколько выражений, использующих оператор -> или ->>, эти выражения переводятся в их эквиваленты, используя JSON_EXTRACT() и (при необходимости) JSON_UNQUOTE(), как показано здесь в выводе от SHOW WARNINGS сразу после этого EXPLAIN оператора:

mysql> EXPLAIN SELECT c->>"$.name"
     > FROM jemp WHERE g > 2 ORDER BY c->"$.name"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: jemp
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select json_unquote(json_extract(`test`.`jemp`.`c`,'$.name')) AS
`c->>"$.name"` from `test`.`jemp` where (`test`.`jemp`.`g` > 2) order by
json_extract(`test`.`jemp`.`c`,'$.name')
1 row in set (0.00 sec)

См. описания операторов -> и ->>, а также функций JSON_EXTRACT() и JSON_UNQUOTE() для дополнительной информации и примеров.

Этот метод также можно использовать для создания индексов, косвенно ссылающихся на столбцы других типов, которые нельзя индексировать напрямую, такие как GEOMETRY столбцы.

Также возможно создать индекс на столбце JSON, используя функцию JSON_VALUE() с выражением, которое можно использовать для оптимизации запросов, использующих это выражение. См. описание этой функции для получения дополнительной информации и примеров.

JSON-столбцы и косвенное индексирование в NDB Cluster

Также можно использовать косвенное индексирование JSON-столбцов в MySQL NDB Cluster, при соблюдении следующих условий:

NDB обрабатывает значение столбца JSON внутри как BLOB. Это означает, что любая NDB таблица, имеющая один или несколько JSON-столбцов, должна иметь первичный ключ, иначе она не может быть записана в двоичный журнал.
СУБД NDB не поддерживает индексирование виртуальных столбцов. Так как по умолчанию для сгенерированных столбцов используется VIRTUAL, вы должны явно указать сгенерированный столбец, к которому следует применить косвенный индекс, как STORED.

Оператор CREATE TABLE, используемый для создания таблицы jempn, показанный здесь, представляет собой версию таблицы jemp, показанной ранее, с изменениями, делающими её совместимой с NDB:

CREATE TABLE jempn (
  a BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  c JSON DEFAULT NULL,
  g INT GENERATED ALWAYS AS (c->"$.id") STORED,
  INDEX i (g)
) ENGINE=NDB;

Мы можем заполнить эту таблицу с помощью следующего оператора INSERT:

INSERT INTO jempn (c) VALUES
  ('{"id": "1", "name": "Fred"}'),
  ('{"id": "2", "name": "Wilma"}'),
  ('{"id": "3", "name": "Barney"}'),
  ('{"id": "4", "name": "Betty"}');

Теперь NDB может использовать индекс i, как показано здесь:

mysql> EXPLAIN SELECT c->>"$.name" AS name
    ->           FROM jempn WHERE g > 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: jempn
   partitions: p0,p1,p2,p3
         type: range
possible_keys: i
          key: i
      key_len: 5
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using pushed condition (`test`.`jempn`.`g` > 2)
1 row in set, 1 warning (0.01 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select
json_unquote(json_extract(`test`.`jempn`.`c`,'$.name')) AS `name` from
`test`.`jempn` where (`test`.`jempn`.`g` > 2)
1 row in set (0.00 sec)

Следует помнить, что хранимый сгенерированный столбец, а также любой индекс на таком столбце, использует DataMemory.