15.1.15 CREATE INDEX Statement — MySQL 8.4

15.1.15 Оператор CREATE INDEX

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
  | ENGINE_ATTRIBUTE [=] 'string'
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

Обычно все индексы таблицы создаются в момент создания самой таблицы с помощью CREATE TABLE. См. Раздел 15.1.20, «Оператор CREATE TABLE». Это особенно важно для таблиц InnoDB, где первичный ключ определяет физическую структуру строк в файле данных. CREATE INDEX позволяет добавлять индексы к существующим таблицам.

CREATE INDEX сопоставляется с оператором ALTER TABLE для создания индексов. См. Раздел 15.1.9, «Оператор ALTER TABLE». CREATE INDEX нельзя использовать для создания PRIMARY KEY; используйте ALTER TABLE вместо этого. Дополнительную информацию об индексах см. в Разделе 10.3.1, «Как MySQL использует индексы».

InnoDB поддерживает вторичные индексы на виртуальных столбцах. Дополнительную информацию см. в Разделе 15.1.20.9, «Вторичные индексы и сгенерированные столбцы».

При включенном параметре innodb_stats_persistent выполните оператор ANALYZE TABLE для таблицы InnoDB после создания индекса в этой таблице.

Спецификация expr для спецификации key_part также может принимать вид (CAST json_expression AS type ARRAY) для создания многозначного индекса для столбца JSON. См. Многозначные индексы.

Спецификация индекса вида (key_part1, key_part2, ...) создает индекс с несколькими ключами. Значения ключа индекса формируются путём конкатенации значений заданных частей ключа. Например, (col1, col2, col3) определяет индекс с несколькими столбцами, ключи индекса включают значения из col1, col2 и col3.

Спецификация key_part может завершаться ASC или DESC, чтобы указать, хранятся ли значения индекса в порядке возрастания или убывания. По умолчанию используется порядок возрастания, если указатель порядка не задан.

ASC и DESC не поддерживаются для HASH индексов, многозначных индексов или SPATIAL индексов.

В следующих разделах описываются различные аспекты оператора CREATE INDEX:

Части ключа с префиксами столбцов

Для строковых столбцов можно создавать индексы, использующие только начальную часть значений столбца, используя синтаксис col_name(length) для указания длины префикса индекса:

Префиксы могут быть указаны для CHAR, VARCHAR, BINARY и VARBINARY частей ключа.
Префиксы обязательны для BLOB и TEXT частей ключа. Кроме того, столбцы BLOB и TEXT могут быть индексированы только для таблиц InnoDB, MyISAM и BLACKHOLE.
Префиксные пределы измеряются в байтах. Однако префиксные длины для спецификаций индексов в операторах CREATE TABLE, ALTER TABLE и CREATE INDEX интерпретируются как количество символов для строковых типов без бинарных данных (CHAR, VARCHAR, TEXT) и количество байтов для бинарных строковых типов (BINARY, VARBINARY, BLOB). Учитывайте это, когда указываете длину префикса для столбца строковых данных без бинарного типа, использующего многобайтовую кодировку.
Поддержка префиксов и длина префиксов (где поддерживается) зависят от движка хранения. Например, длина префикса может достигать 767 байт для таблиц InnoDB, использующих формат строк или . Предельно допустимая длина префикса составляет 3072 байта для таблиц InnoDB, использующих формат строк или . Для таблиц MyISAM предельно допустимая длина префикса составляет 1000 байт. Двигатель хранения NDB не поддерживает префиксы (см. Раздел 25.2.7.6, «Неподдерживаемые или отсутствующие функции в NDB Cluster»).

Если указанный префикс индекса превышает максимальный размер данных типа столбца, CREATE INDEX обрабатывает индекс следующим образом:

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

Показанный здесь оператор создает индекс, используя первые 10 символов столбца name (предполагая, что name имеет строковый тип без бинарных данных):

CREATE INDEX part_of_name ON customer (name(10));

Если имена в столбце обычно отличаются в первых 10 символах, поиск с использованием этого индекса не должен быть значительно медленнее, чем с использованием индекса, созданного из всего столбца name. Кроме того, использование префиксов столбцов для индексов может сделать файл индекса намного меньше, что может сэкономить много места на диске и ускорить операции INSERT.

Функциональные ключевые части

Индекс “обычный” индексирует значения столбцов или префиксы значений столбцов. Например, в следующей таблице запись индекса для заданной t1 строки включает полное значение col1 и префикс значения col2, состоящий из первых 10 символов:

CREATE TABLE t1 (
  col1 VARCHAR(10),
  col2 VARCHAR(20),
  INDEX (col1, col2(10))
);

Функциональные ключевые части, индексирующие значения выражений, также могут использоваться вместо значений столбцов или префиксов столбцов. Использование функциональных ключевых частей позволяет индексировать значения, не хранящиеся непосредственно в таблице. Примеры:

CREATE TABLE t1 (col1 INT, col2 INT, INDEX func_index ((ABS(col1))));
CREATE INDEX idx1 ON t1 ((col1 + col2));
CREATE INDEX idx2 ON t1 ((col1 + col2), (col1 - col2), col1);
ALTER TABLE t1 ADD INDEX ((col1 * 40) DESC);

Индекс с несколькими ключевыми частями может сочетать нефункциональные и функциональные ключевые части.

ASC и DESC поддерживаются для функциональных ключевых частей.

Функциональные ключевые части должны соответствовать следующим правилам. Ошибка возникает, если определение ключевой части содержит недопустимые конструкции.

В определениях индексов выражения следует заключать в скобки, чтобы отличать их от столбцов или префиксов столбцов. Например, это разрешено; выражения заключены в скобки:
```
INDEX ((col1 + col2), (col3 - col4))
```
Это приводит к ошибке; выражения не заключены в скобки:
```
INDEX (col1 + col2, col3 - col4)
```
Функциональная ключевая часть не может состоять только из имени столбца. Например, это недопустимо:
```
INDEX ((col1), (col2))
```
Вместо этого запишите ключевые части как нефункциональные ключевые части без скобок:
```
INDEX (col1, col2)
```
Выражение функциональной ключевой части не может ссылаться на префиксы столбцов. Для обходного решения см. обсуждение SUBSTRING() и CAST() в этой секции.
Функциональные ключевые части не допускаются в спецификациях внешних ключей.

Для CREATE TABLE ... LIKE целевая таблица сохраняет функциональные ключевые части из исходной таблицы.

Функциональные индексы реализуются как скрытые виртуальные генерируемые столбцы, что влечет за собой следующие последствия:

Каждая функциональная ключевая часть учитывается в лимите на общее количество столбцов таблицы; см. Раздел 10.4.7, «Ограничения на количество столбцов таблицы и размер строки».
Функциональные ключевые части наследуют все ограничения, применяемые к генерируемым столбцам. Примеры:
- Для функциональных ключевых частей разрешены только функции, разрешенные для генерируемых столбцов.
- Подзапросы, параметры, переменные, хранимые функции и загружаемые функции запрещены.
Дополнительную информацию об applicable ограничениях см. в Раздел 15.1.20.8, «CREATE TABLE и генерируемые столбцы» и Раздел 15.1.9.2, «ALTER TABLE и генерируемые столбцы».
Сам виртуальный генерируемый столбец не требует хранения. Сам индекс занимает место хранения, как и любой другой индекс.

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

SPATIAL и FULLTEXT индексы не могут иметь функциональные ключевые части.

Если таблица не содержит первичного ключа, InnoDB автоматически повышает первый UNIQUE NOT NULL индекс до первичного ключа. Это не поддерживается для UNIQUE NOT NULL индексов, содержащих функциональные ключевые части.

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

Чтобы удалить столбец, на который ссылается функциональная ключевая часть, необходимо сначала удалить индекс. В противном случае произойдет ошибка.

Хотя нефункциональные ключевые части поддерживают указание длины префикса, это невозможно для функциональных ключевых частей. Решением является использование SUBSTRING() (или CAST(), как описано далее в этой секции). Для использования функции SUBSTRING() в запросе, содержащем функциональную ключевую часть, в WHERE разделе должен содержаться SUBSTRING() с теми же аргументами. В следующем примере только второй SELECT может использовать индекс, поскольку это единственный запрос, в котором аргументы функции SUBSTRING() совпадают с спецификацией индекса:

CREATE TABLE tbl (
  col1 LONGTEXT,
  INDEX idx1 ((SUBSTRING(col1, 1, 10)))
);
SELECT * FROM tbl WHERE SUBSTRING(col1, 1, 9) = '123456789';
SELECT * FROM tbl WHERE SUBSTRING(col1, 1, 10) = '1234567890';

Функциональные ключевые части позволяют индексировать значения, которые иначе невозможно индексировать, такие как значения JSON. Однако это необходимо делать правильно, чтобы достичь желаемого эффекта. Например, этот синтаксис не работает:

CREATE TABLE employees (
  data JSON,
  INDEX ((data->>'$.name'))
);

Синтаксис не работает, потому что:

Оператор ->> преобразуется в JSON_UNQUOTE(JSON_EXTRACT(...)).
JSON_UNQUOTE() возвращает значение с типом данных LONGTEXT, и скрытому генерируемому столбцу таким образом назначается тот же тип данных.
MySQL не может индексировать столбцы LONGTEXT, указанные без длины префикса в ключевой части, и длины префиксов не допускаются в функциональных ключевых частях.

Чтобы индексировать столбец JSON, можно попробовать использовать функцию CAST() следующим образом:

CREATE TABLE employees (
  data JSON,
  INDEX ((CAST(data->>'$.name' AS CHAR(30))))
);

Скрытому генерируемому столбцу назначается тип данных VARCHAR(30), который можно индексировать. Но этот подход создает новую проблему при попытке использовать индекс:

CAST() возвращает строку с сортировкой utf8mb4_0900_ai_ci (сортировка по умолчанию сервера).
JSON_UNQUOTE() возвращает строку с сортировкой utf8mb4_bin (закодировано).

В результате возникает несоответствие сортировки между индексируемым выражением в предыдущем определении таблицы и выражением WHERE в следующем запросе:

SELECT * FROM employees WHERE data->>'$.name' = 'James';

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

Решение 1. Назначьте индексируемому выражению ту же сортировку, что и JSON_UNQUOTE():

CREATE TABLE employees (
  data JSON,
  INDEX idx ((CAST(data->>"$.name" AS CHAR(30)) COLLATE utf8mb4_bin))
);
INSERT INTO employees VALUES
  ('{ "name": "james", "salary": 9000 }'),
  ('{ "name": "James", "salary": 10000 }'),
  ('{ "name": "Mary", "salary": 12000 }'),
  ('{ "name": "Peter", "salary": 8000 }');
SELECT * FROM employees WHERE data->>'$.name' = 'James';

Оператор ->> эквивалентен JSON_UNQUOTE(JSON_EXTRACT(...)), и JSON_UNQUOTE() возвращает строку с сортировкой utf8mb4_bin. Таким образом, сравнение чувствительно к регистру, и только одна строка совпадает:

+------------------------------------+
| data                               |
+------------------------------------+
| {"name": "James", "salary": 10000} |
+------------------------------------+

Решение 2. Укажите полное выражение в запросе:

CREATE TABLE employees (
  data JSON,
  INDEX idx ((CAST(data->>"$.name" AS CHAR(30))))
);
INSERT INTO employees VALUES
  ('{ "name": "james", "salary": 9000 }'),
  ('{ "name": "James", "salary": 10000 }'),
  ('{ "name": "Mary", "salary": 12000 }'),
  ('{ "name": "Peter", "salary": 8000 }');
SELECT * FROM employees WHERE CAST(data->>'$.name' AS CHAR(30)) = 'James';

CAST() возвращает строку с сортировкой utf8mb4_0900_ai_ci, поэтому сравнение нечувствительно к регистру, и две строки совпадают:

+------------------------------------+
| data                               |
+------------------------------------+
| {"name": "james", "salary": 9000}  |
| {"name": "James", "salary": 10000} |
+------------------------------------+

Имейте в виду, что хотя оптимизатор поддерживает автоматическое удаление CAST() с индексированными генерируемыми столбцами, следующий подход не работает, потому что он дает разные результаты с индексом и без него (Bug#27337092):

mysql> CREATE TABLE employees (
         data JSON,
         generated_col VARCHAR(30) AS (CAST(data->>'$.name' AS CHAR(30)))
       );
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> INSERT INTO employees (data)
       VALUES ('{"name": "james"}'), ('{"name": "James"}');
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> SELECT * FROM employees WHERE data->>'$.name' = 'James';
+-------------------+---------------+
| data              | generated_col |
+-------------------+---------------+
| {"name": "James"} | James         |
+-------------------+---------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE employees ADD INDEX idx (generated_col);
Query OK, 0 rows affected, 1 warning (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> SELECT * FROM employees WHERE data->>'$.name' = 'James';
+-------------------+---------------+
| data              | generated_col |
+-------------------+---------------+
| {"name": "james"} | james         |
| {"name": "James"} | James         |
+-------------------+---------------+
2 rows in set (0.01 sec)

Уникальные индексы

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

Если таблица имеет индекс PRIMARY KEY или UNIQUE NOT NULL, состоящий из одного столбца целого типа, вы можете использовать _rowid для ссылки на индексированный столбец в операторах SELECT, как показано ниже:

_rowid ссылается на столбец PRIMARY KEY, если существует индекс PRIMARY KEY, состоящий из одного целочисленного столбца. Если индекс PRIMARY KEY существует, но не состоит из одного целочисленного столбца, _rowid нельзя использовать.
В противном случае, _rowid ссылается на столбец в первом индексе UNIQUE NOT NULL, если этот индекс состоит из одного целочисленного столбца. Если первый индекс UNIQUE NOT NULL не состоит из одного целочисленного столбца, _rowid нельзя использовать.

Индексы полнотекстового поиска

Индексы FULLTEXT поддерживаются только для таблиц InnoDB и MyISAM и могут включать только столбцы CHAR, VARCHAR и TEXT. Индексация всегда происходит по всему столбцу; индексация префикса столбца не поддерживается, и любая указанная длина префикса игнорируется. Подробности работы см. в Разделе 14.9, «Функции полнотекстового поиска».

Многозначные индексы

InnoDB поддерживает многозначные индексы. Многозначный индекс — это вторичный индекс, определённый по столбцу, хранящему массив значений. Индекс «обычного» типа имеет по одной записи индекса на запись данных (1:1). Многозначный индекс может иметь несколько записей индекса на одну запись данных (N:1). Многозначные индексы предназначены для индексирования JSON массивов. Например, многозначный индекс, определённый по массиву почтовых индексов в следующем JSON-документе, создаёт запись индекса для каждого почтового индекса, при этом каждая запись индекса ссылается на одну и ту же запись данных.

{
    "user":"Bob",
    "user_id":31,
    "zipcode":[94477,94536]
}

Создание многозначных индексов

Вы можете создать многозначный индекс в операторе CREATE TABLE, ALTER TABLE или CREATE INDEX. Это требует использования CAST(... AS ... ARRAY) в определении индекса, которое преобразует скалярные значения одного типа в массиве JSON в массив типа данных SQL. Затем прозрачно генерируется виртуальный столбец со значениями в массиве типа данных SQL; наконец, создаётся функциональный индекс (также называемый виртуальным индексом) по виртуальному столбцу значений из массива типа данных SQL. Именно функциональный индекс, определённый по виртуальному столбцу значений из массива типа данных SQL, образует многозначный индекс.

Примеры в следующем списке показывают три различных способа создания многозначного индекса zips на массиве $.zipcode в столбце JSON custinfo в таблице с именем customers. В каждом случае массив JSON преобразуется в массив типа данных SQL целых чисел UNSIGNED.

Только CREATE TABLE:

CREATE TABLE customers (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    custinfo JSON,
    INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) )
    );

CREATE TABLE плюс ALTER TABLE:

CREATE TABLE customers (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    custinfo JSON
    );

ALTER TABLE customers ADD INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

CREATE TABLE плюс CREATE INDEX:

CREATE TABLE customers (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    custinfo JSON
    );

CREATE INDEX zips ON customers ( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

Многозначный индекс также может быть определён как часть составного индекса. Этот пример показывает составной индекс, включающий две части с единственными значениями (для столбцов id и modified) и одну многозначную часть (для столбца custinfo):

CREATE TABLE customers (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    custinfo JSON
    );

ALTER TABLE customers ADD INDEX comp(id, modified,
    (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

В составном индексе может быть только одна многозначная часть ключа. Многозначная часть ключа может быть упорядочена произвольно относительно других частей ключа. Другими словами, приведенный выше оператор ALTER TABLE мог использовать comp(id, (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY), modified)) (или любое другое упорядочение) и оставался бы корректным.

Использование многозначных индексов

Оптимизатор использует многозначный индекс для извлечения записей, когда в предложении WHERE указаны следующие функции:

Это можно продемонстрировать, создав и заполнив таблицу customers с помощью следующих операторов CREATE TABLE и INSERT:

mysql> CREATE TABLE customers (
    ->     id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->     custinfo JSON
    ->     );
Query OK, 0 rows affected (0.51 sec)

mysql> INSERT INTO customers VALUES
    ->     (NULL, NOW(), '{"user":"Jack","user_id":37,"zipcode":[94582,94536]}'),
    ->     (NULL, NOW(), '{"user":"Jill","user_id":22,"zipcode":[94568,94507,94582]}'),
    ->     (NULL, NOW(), '{"user":"Bob","user_id":31,"zipcode":[94477,94507]}'),
    ->     (NULL, NOW(), '{"user":"Mary","user_id":72,"zipcode":[94536]}'),
    ->     (NULL, NOW(), '{"user":"Ted","user_id":56,"zipcode":[94507,94582]}');
Query OK, 5 rows affected (0.07 sec)
Records: 5  Duplicates: 0  Warnings: 0

Сначала выполним три запроса к таблице customers, каждый с использованием MEMBER OF(), JSON_CONTAINS() и JSON_OVERLAPS(), а результат каждого запроса показан здесь:

mysql> SELECT * FROM customers
    ->     WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
+----+---------------------+-------------------------------------------------------------------+
| id | modified            | custinfo                                                          |
+----+---------------------+-------------------------------------------------------------------+
|  2 | 2019-06-29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
|  3 | 2019-06-29 22:23:12 | {"user": "Bob", "user_id": 31, "zipcode": [94477, 94507]}         |
|  5 | 2019-06-29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
+----+---------------------+-------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM customers
    ->     WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+---------------------+-------------------------------------------------------------------+
| id | modified            | custinfo                                                          |
+----+---------------------+-------------------------------------------------------------------+
|  2 | 2019-06-29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
|  5 | 2019-06-29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
+----+---------------------+-------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM customers
    ->     WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+---------------------+-------------------------------------------------------------------+
| id | modified            | custinfo                                                          |
+----+---------------------+-------------------------------------------------------------------+
|  1 | 2019-06-29 22:23:12 | {"user": "Jack", "user_id": 37, "zipcode": [94582, 94536]}        |
|  2 | 2019-06-29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
|  3 | 2019-06-29 22:23:12 | {"user": "Bob", "user_id": 31, "zipcode": [94477, 94507]}         |
|  5 | 2019-06-29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
+----+---------------------+-------------------------------------------------------------------+
4 rows in set (0.00 sec)

Далее выполним оператор EXPLAIN для каждого из предыдущих трёх запросов:

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

Ни один из трёх только что показанных запросов не может использовать никакие ключи. Для решения этой проблемы можно добавить многозначный индекс на массив zipcode в столбце JSON (custinfo), как показано ниже:

mysql> ALTER TABLE customers
    ->     ADD INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

При повторном выполнении предыдущих операторов EXPLAIN можно заметить, что запросы теперь могут (и используют) индекс zips, который только что был создан:

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ref  | zips          | zips | 9       | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | range | zips          | zips | 9       | NULL |    6 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | range | zips          | zips | 9       | NULL |    6 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

Многозначный индекс может быть определён как уникальный ключ. Если он определён как уникальный ключ, попытка вставки значения, уже присутствующего в многозначном индексе, приводит к ошибке дублирования ключа. Если дублированные значения уже присутствуют, попытка добавить уникальный многозначный индекс завершается неудачно, как показано ниже:

mysql> ALTER TABLE customers DROP INDEX zips;
Query OK, 0 rows affected (0.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE customers
    ->     ADD UNIQUE INDEX zips((CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)));
ERROR 1062 (23000): Duplicate entry '[94507, ' for key 'customers.zips'
mysql> ALTER TABLE customers
    ->     ADD INDEX zips((CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)));
Query OK, 0 rows affected (0.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

Характеристики многозначных индексов

Многозначные индексы имеют следующие дополнительные характеристики:

Операции DML, влияющие на многозначные индексы, обрабатываются так же, как и операции DML, влияющие на обычный индекс, с единственной разницей в том, что для одной записи кластеризованного индекса может быть выполнено несколько операций вставки или обновления.
Нулевые значения и многозначные индексы:
- Если многозначная часть ключа имеет пустой массив, записи в индекс не добавляются, и запись данных недоступна при сканировании индекса.
- Если генерация многозначной части ключа возвращает значение NULL, в многозначный индекс добавляется одна запись, содержащая NULL. Если часть ключа определена как NOT NULL, выдаётся ошибка.
- Если типизированный столбец массива установлен в NULL, движок хранения сохраняет одну запись, содержащую NULL, которая указывает на запись данных.
- В индексированных массивах не допускаются JSON значения null. Если возвращённое значение является NULL, оно обрабатывается как JSON null, и выдаётся ошибка Недопустимое значение JSON.
Поскольку многозначные индексы являются виртуальными индексами по виртуальным столбцам, они должны соответствовать тем же правилам, что и вторичные индексы по виртуальным генерируемым столбцам.
Записи индекса не добавляются для пустых массивов.

Ограничения и запреты на многозначные индексы

Многозначные индексы ограничены следующими условиями:

В многозначном индексе разрешена только одна многозначная часть ключа. Однако выражение CAST(... AS ... ARRAY) может ссылаться на несколько массивов внутри JSON документа, как показано здесь:
```
CAST(data->'$.arr[*][*]' AS UNSIGNED ARRAY)
```
В этом случае все значения, соответствующие JSON-выражению, хранятся в индексе как единый плоский массив.
Индекс с многозначной частью ключа не поддерживает упорядочение и, следовательно, не может использоваться в качестве первичного ключа. По той же причине многозначный индекс не может быть определён с использованием ключевых слов ASC или DESC.
Многозначный индекс не может быть охватывающим индексом.
Максимальное количество значений на запись для многозначного индекса определяется объёмом данных, который может быть сохранён в одной странице журнала отмены, равным 65221 байтам (64К минус 315 байтов накладных расходов), что означает, что максимальная общая длина значений ключей также составляет 65221 байт. Максимальное количество ключей зависит от различных факторов, что препятствует определению конкретного предела. Тесты показали, что многозначный индекс может допускать до 1604 целых ключей на запись, например. При достижении предела выдаётся ошибка, подобная следующей: Ошибка 3905 (HY000): Превышено максимальное количество значений на запись для многозначного индекса 'idx' на 1 значение(ей).
Единственный тип выражения, разрешённый в многозначной части ключа, — это выражение JSON. Выражение не обязательно должно ссылаться на существующий элемент в JSON-документе, вставленном в индексированный столбец, но само должно быть синтаксически корректным.
Поскольку записи индекса для одной и той же записи кластеризованного индекса распределены по всему многозначному индексу, многозначный индекс не поддерживает сканирование по диапазону или сканирование только по индексу.
Многозначные индексы не разрешены в спецификациях внешних ключей.
Префиксы индексов не могут быть определены для многозначных индексов.
Многозначные индексы не могут быть определены на данных, преобразованных в тип BINARY (см. описание функции CAST()).
Онлайн-создание многозначного индекса не поддерживается, что означает, что операция использует ALGORITHM=COPY. См. Требования к производительности и пространству.
Наборы символов и сортировки, кроме следующих двух комбинаций набора символов и сортировки, не поддерживаются для многозначных индексов:
1. Набор символов binary с сортировкой по умолчанию binary
2. Набор символов utf8mb4 с сортировкой по умолчанию utf8mb4_0900_as_cs.
Как и в случае других индексов по столбцам таблиц InnoDB, многозначный индекс не может быть создан с USING HASH; попытка сделать это приводит к предупреждению: Этот движок хранения не поддерживает алгоритм индекса HASH, вместо этого был использован движок хранения по умолчанию. (USING BTREE поддерживается как обычно).

Пространственные индексы

Хранилища MyISAM, InnoDB, NDB и ARCHIVE поддерживают пространственные столбцы, такие как POINT и GEOMETRY. (Раздел 13.4, «Пространственные типы данных» описывает пространственные типы данных.) Однако поддержка пространственной индексации столбцов различается в разных хранилищах. Пространственные и непространственные индексы для пространственных столбцов доступны в соответствии со следующими правилами.

Пространственные индексы на пространственных столбцах имеют следующие характеристики:

Доступны только для таблиц InnoDB и MyISAM. Указание SPATIAL INDEX для других хранилищ приводит к ошибке.
Индекс на пространственном столбце обязательно должен быть SPATIAL индексом. Ключевое слово SPATIAL, таким образом, является необязательным, но подразумевается при создании индекса на пространственном столбце.
Доступны только для отдельных пространственных столбцов. Пространственный индекс не может быть создан для нескольких пространственных столбцов.
Индексируемые столбцы должны быть NOT NULL.
Длины префиксов столбцов запрещены. Полная ширина каждого столбца индексируется.
Не разрешается для первичного ключа или уникального индекса.

Непространственные индексы на пространственных столбцах (созданные с использованием INDEX, UNIQUE или PRIMARY KEY) имеют следующие характеристики:

Разрешены для любого хранилища, поддерживающего пространственные столбцы, кроме ARCHIVE.
Столбцы могут быть NULL, если индекс не является первичным ключом.
Тип индекса для непространственного индекса зависит от хранилища. В настоящее время используется B-дерево.
Разрешается для столбца, который может иметь NULL значения только для таблиц InnoDB, MyISAM и MEMORY.

Параметры индекса

После ключевых частей можно указать параметры индекса. Значение index_option может быть любым из следующих:

KEY_BLOCK_SIZE [=] value

Для таблиц MyISAM, KEY_BLOCK_SIZE необязательно указывает размер в байтах для использования блоков ключей индекса. Это значение обрабатывается как подсказка; если необходимо, может быть использован другой размер. Значение KEY_BLOCK_SIZE, указанное для отдельного определения индекса, переопределяет значение KEY_BLOCK_SIZE на уровне таблицы.

KEY_BLOCK_SIZE не поддерживается на уровне индекса для таблиц InnoDB. См. Раздел 15.1.20, «Оператор CREATE TABLE».

index_type

Некоторые движки хранения позволяют указать тип индекса при создании индекса. Например:

CREATE TABLE lookup (id INT) ENGINE = MEMORY;
CREATE INDEX id_index ON lookup (id) USING BTREE;

Таблица 15.1, «Типы индексов по движку хранения» показывает допустимые значения типа индекса, поддерживаемые различными движками хранения. Там, где перечислены несколько типов индексов, первый является значением по умолчанию, если спецификатор типа индекса не задан. Движки хранения, не указанные в таблице, не поддерживают предложение index_type в определениях индексов.

Таблица 15.1 Типы индексов по движку хранения

Таблица 15.1 Типы индексов по движку хранения
Движок хранения	Допустимые типы индексов
`InnoDB`	`BTREE`
`MyISAM`	`BTREE`
`MEMORY`/`HEAP`	`HASH`, `BTREE`
`NDB`	`HASH`, `BTREE` (см. примечание в тексте)

Предложение index_type не может использоваться для спецификаций FULLTEXT INDEX. Реализация полнотекстовых индексов зависит от движка хранения. Пространственные индексы реализуются как индексы R-дерева.

Если вы укажете тип индекса, который недействителен для данного движка хранения, но доступен другой тип индекса, который движок может использовать без влияния на результаты запроса, движок использует доступный тип. Парсер распознает RTREE как имя типа. Это разрешено только для индексов SPATIAL.

Индексы BTREE реализуются движком хранения NDB как индексы T-дерева.

Примечание

Для индексов по столбцам таблицы NDB, опция USING может быть указана только для уникального индекса или первичного ключа. USING HASH предотвращает создание упорядоченного индекса; в противном случае создание уникального индекса или первичного ключа по столбцу таблицы NDB автоматически приводит к созданию как упорядоченного индекса, так и хеш-индекса, каждый из которых индексирует один и тот же набор столбцов.

Для уникальных индексов, включающих один или несколько столбцов NULL таблицы NDB, хеш-индекс может быть использован только для поиска буквальных значений, что означает, что условия IS [NOT] NULL требуют полного сканирования таблицы. Одним из решений является обеспечение того, чтобы уникальный индекс, использующий один или несколько столбцов NULL в такой таблице, всегда создавался таким образом, чтобы он включал упорядоченный индекс; то есть избегайте использования USING HASH при создании индекса.

Если вы укажете тип индекса, который недействителен для данного движка хранения, но доступен другой тип индекса, который движок может использовать без влияния на результаты запроса, движок использует доступный тип. Парсер распознает RTREE как имя типа, но в настоящее время это нельзя указать для какого-либо движка хранения.

Примечание

Использование опции index_type перед предложением ON tbl_name устарело; ожидается, что поддержка использования опции в этом положении будет удалена в будущей версии MySQL. Если опция index_type задана как в более раннем, так и в более позднем положениях, применяется последняя опция.

TYPE type_name распознается как синоним для USING type_name. Однако, USING является предпочтительной формой.

Следующие таблицы показывают характеристики индексов для движков хранения, которые поддерживают опцию index_type.

Таблица 15.2 Характеристики индексов движка хранения InnoDB

Таблица 15.2 Характеристики индексов движка хранения InnoDB
Класс индекса	Тип индекса	Сохраняет значения NULL	Разрешает несколько значений NULL	Тип сканирования IS NULL	Тип сканирования IS NOT NULL
Первичный ключ	`BTREE`	Нет	Нет	Н/Д	Н/Д
Уникальный	`BTREE`	Да	Да	Индекс	Индекс
Ключ	`BTREE`	Да	Да	Индекс	Индекс
`FULLTEXT`	Н/Д	Да	Да	Таблица	Таблица
`SPATIAL`	Н/Д	Нет	Нет	Н/Д	Н/Д

Таблица 15.3 Характеристики индексов движка хранения MyISAM

Таблица 15.3 Характеристики индексов движка хранения MyISAM
Класс индекса	Тип индекса	Сохраняет значения NULL	Разрешает несколько значений NULL	Тип сканирования IS NULL	Тип сканирования IS NOT NULL
Первичный ключ	`BTREE`	Нет	Нет	Н/Д	Н/Д
Уникальный	`BTREE`	Да	Да	Индекс	Индекс
Ключ	`BTREE`	Да	Да	Индекс	Индекс
`FULLTEXT`	Н/Д	Да	Да	Таблица	Таблица
`SPATIAL`	Н/Д	Нет	Нет	Н/Д	Н/Д

Таблица 15.4 Характеристики индексов движка хранения MEMORY

Таблица 15.4 Характеристики индексов движка хранения MEMORY
Класс индекса	Тип индекса	Сохраняет значения NULL	Разрешает несколько значений NULL	Тип сканирования IS NULL	Тип сканирования IS NOT NULL

WITH PARSER parser_name

Этот параметр можно использовать только с FULLTEXT индексами. Он ассоциирует плагин анализатора с индексом, если операции полнотекстового индексирования и поиска требуют специальной обработки. InnoDB и MyISAM поддерживают плагины анализатора полнотекстового поиска. Если у вас есть таблица MyISAM с ассоциированным плагином анализатора полнотекстового поиска, вы можете преобразовать таблицу в InnoDB, используя ALTER TABLE. Дополнительную информацию см. в Плагины анализатора полнотекстового поиска и Создание плагинов анализатора полнотекстового поиска.
COMMENT 'string'

Определения индексов могут включать необязательный комментарий длиной до 1024 символов.

MERGE_THRESHOLD для страниц индексов можно настроить для отдельных индексов, используя клаузу index_option COMMENT оператора CREATE INDEX. Например:
```
CREATE TABLE t1 (id INT);
CREATE INDEX id_index ON t1 (id) COMMENT 'MERGE_THRESHOLD=40';
```
Если процент заполнения страницы индекса для страницы индекса опускается ниже значения MERGE_THRESHOLD при удалении строки или при укорочении строки операцией обновления, InnoDB пытается слить страницу индекса с соседней страницей индекса. Значение по умолчанию для MERGE_THRESHOLD — 50, что является ранее жёстко заданным значением.

MERGE_THRESHOLD также можно определить на уровне индекса и таблицы с помощью операторов CREATE TABLE и ALTER TABLE. Дополнительную информацию см. в Разделе 17.8.11, «Настройка порога слияния страниц индексов».
VISIBLE, INVISIBLE

Укажите видимость индекса. Индексы видимы по умолчанию. Невидимый индекс не используется оптимизатором. Указание видимости индекса применяется к индексам, отличным от первичных ключей (явных или неявных). Дополнительную информацию см. в Разделе 10.3.12, «Невидимые индексы».
ENGINE_ATTRIBUTE и SECONDARY_ENGINE_ATTRIBUTE используются для указания атрибутов индекса для первичных и вторичных движков хранения. Параметры зарезервированы для будущего использования.

Присвоенное этому параметру значение — строковая константа, содержащая допустимый JSON-документ или пустая строка (''). Некорректный JSON отклоняется.
```
CREATE INDEX i1 ON t1 (c1) ENGINE_ATTRIBUTE='{"key":"value"}';
```
Значения ENGINE_ATTRIBUTE и SECONDARY_ENGINE_ATTRIBUTE могут быть повторены без ошибки. В этом случае используется последнее указанное значение.

Значения ENGINE_ATTRIBUTE и SECONDARY_ENGINE_ATTRIBUTE не проверяются сервером и не очищаются при изменении движка хранения таблицы.

Параметры копирования и блокировки таблиц

Клаузы ALGORITHM и LOCK могут быть заданы для влияния на метод копирования таблицы и уровень параллельности при чтении и записи в таблицу во время изменения её индексов. Они имеют то же значение, что и для оператора ALTER TABLE. Дополнительную информацию см. в Разделе 15.1.9, «Оператор ALTER TABLE»

NDB Cluster поддерживает онлайн-операции, используя тот же синтаксис ALGORITHM=INPLACE, что и со стандартным MySQL Server. Дополнительную информацию см. в Разделе 25.6.12, «Онлайн-операции с ALTER TABLE в NDB Cluster».