13.1.14 CREATE INDEX Statement — MySQL 5.7

13.1.14 Оператор 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)] [ASC | DESC]

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

index_type:
    USING {BTREE | HASH}

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

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

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

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

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

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

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

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

В следующих разделах описываются различные аспекты оператора 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 байт или 3072 байта, если включен параметр innodb_large_prefix. Для таблиц MyISAM префиксная длина ограничена 1000 байтами. Двигатель NDB не поддерживает префиксы (см. Раздел 21.2.7.6, «Неподдерживаемые или отсутствующие функции в NDB Cluster»).

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

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

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

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

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

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

Индекс 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 нельзя использовать.

END_OF_DOCUMENT_MARKER ```

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

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

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

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

Пространственные индексы на пространственных столбцах (созданные с помощью SPATIAL INDEX) имеют следующие характеристики:

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

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

Разрешены для любого движка, поддерживающего пространственные столбцы, за исключением ARCHIVE.
Столбцы могут быть NULL, если индекс не является первичным ключом.
Для каждого пространственного столбца в индексе, не являющимся SPATIAL, за исключением столбцов типа POINT, должна быть указана длина префикса столбца. (Это то же требование, что и для индексированных столбцов типа BLOB.) Длина префикса указывается в байтах.
Тип индекса для непространственного индекса зависит от движка. В настоящее время используется 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. См. раздел 13.1.18 «Команда CREATE TABLE».

index_type

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

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

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

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

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

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

Индексы 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.

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

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

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

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

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

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

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

Таблица 13.5 Характеристики индексов NDB движка хранения
Класс индекса	Тип индекса	Сохраняет значения NULL	Разрешает несколько значений NULL	Тип сканирования IS NULL	Тип сканирования IS NOT NULL
Первичный ключ	`BTREE`	Нет	Нет	Индекс	Индекс
Уникальный	`BTREE`	Да	Да	Индекс	Индекс
Ключ	`BTREE`	Да	Да	Индекс	Индекс
Первичный ключ	`HASH`	Нет	Нет	Таблица (см. примечание 1)	Таблица (см. примечание 1)
Уникальный	`HASH`	Да	Да	Таблица (см. примечание 1)	Таблица (см. примечание 1)
Ключ	`HASH`	Да	Да	Таблица (см. примечание 1)	Таблица (см. примечание 1)

Примечание к таблице:

1. Если USING HASH указано, это предотвращает создание неявного упорядоченного индекса.

WITH PARSER parser_name

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

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

Порог слияния страниц индекса можно настроить для отдельных индексов, используя предложение 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. Дополнительную информацию см. в разделе 14.8.12 «Настройка порога слияния страниц индекса».

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

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

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