13.1.18.8 Secondary Indexes and Generated Columns — MySQL 5.7

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

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

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

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

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

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

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

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

В MySQL 5.7.13 и более ранних версиях InnoDB не позволяет определить внешнее ключевое ограничение с каскадным референциальным действием на базовый столбец индексированного сгенерированного виртуального столбца. Это ограничение снято в MySQL 5.7.14.

Индексация сгенерированного столбца для предоставления индекса 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)

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

Оператор -> поддерживается в MySQL 5.7.9 и более поздних версиях. Оператор ->> поддерживается начиная с MySQL 5.7.13.

При использовании 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-столбцы и косвенная индексация в NDB Cluster

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

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

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

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

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

INSERT INTO jempn (a, c) VALUES
  (NULL, '{"id": "1", "name": "Fred"}'),
  (NULL, '{"id": "2", "name": "Wilma"}'),
  (NULL, '{"id": "3", "name": "Barney"}'),
  (NULL, '{"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
         type: range
possible_keys: i
          key: i
      key_len: 5
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where with pushed condition (`test`.`jempn`.`g` > 2)
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`.`jempn`.`c`,'$.name')) AS `name` from
`test`.`jempn` where (`test`.`jempn`.`g` > 2)
1 row in set (0.00 sec)

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