15.1.21.9 Secondary Indexes and Generated Columns — MySQL 9.2

15.1.21.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.