15.1.15 CREATE INDEX Statement — MySQL 9.2

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.21, «Оператор 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.21.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). Учтите это, когда указываете длину префикса для небинарного столбца строки, использующего многобайтовую кодировку символов.

Поддержка префиксов и длина префиксов (где поддерживается) зависят от движка хранения. Например, для таблиц InnoDB с форматом строк или длина префикса может достигать 767 байтов. Предельная длина префикса для таблиц InnoDB с форматом строк или составляет 3072 байта. Для таблиц 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, «Пределы числа столбцов таблицы и размера строки».
Функциональные ключевые части наследуют все ограничения, применимые к сгенерированным столбцам. Примеры:
- Для функциональных ключевых частей допускаются только функции, разрешенные для сгенерированных столбцов.
- Подзапросы, параметры, переменные, хранимые функции и загружаемые функции не допускаются.
Дополнительную информацию о применимых ограничениях см. в Разделе 15.1.21.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() со сгенерированными индексируемыми столбцами, следующий подход не работает, потому что он дает другой результат с индексом и без него (Ошибка #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, оно рассматривается как 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.21, «Оператор 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

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. Для получения дополнительной информации см. Раздел 25.6.12, «Онлайн-операции с ALTER TABLE в NDB Cluster».