Индексы MySQL

В этом учебном пособии вы узнаете, как создавать, удалять и переименовывать Indexes (индексы) в MySQL с помощью синтаксиса и примеров.

Описание

Indexe — это способ настройки производительности, позволяющий быстрее извлекать записи. Индекс создает запись для каждого значения, которое появляется в индексированных столбцах.

Создать индекс

Существует два способа создания индекса. Вы можете создать индекс, когда сначала создаете таблицу, используя оператор CREATE TABLE, или вы можете использовать оператор CREATE INDEX после создания таблицы.

Синтаксис

Синтаксис создания индекса с помощью оператора CREATE TABLE в MySQL:

CREATE TABLE table_name
(
column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],

column_n datatype [ NULL | NOT NULL ],

INDEX index_name [ USING BTREE | HASH ]
(index_col1 [(length)] [ASC | DESC],
index_col2 [(length)] [ASC | DESC],

index_col_n [(length)] [ASC | DESC])
);

ИЛИ
Синтаксис создания индекса с помощью оператора CREATE INDEX в MySQL:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
[ USING BTREE | HASH ]
ON table_name
(index_col1 [(length)] [ASC | DESC],
index_col2 [(length)] [ASC | DESC],

index_col_n [(length)] [ASC | DESC]);

Параметры или аргументы

UNIQUE — необязательный. Модификатор UNIQUE указывает, что комбинация значений в индексированных столбцах должна быть уникальной.

FULLTEXT — необязательный. Модификатор FULLTEXT индексирует весь столбец и не допускает префикса. Таблицы InnoDB и MyISAM поддерживают этот параметр.

SPATIAL — необязательный. Модификатор SPATIAL индексирует весь столбец и не позволяет индексированным столбцам содержать значения NULL. В этой опции поддерживается InnoDB (начиная с MySQL 5.7) и таблиц MyISAM.

index_name — имя для присвоения индексу.

table_name — имя таблицы для создания индекса.

index_col1, index_col2, … index_col_n — столбцы для использования в индексе.

length — необязательный. Если указано, то индексируется только префикс столбца, а не весь столбец. Для не двоичных строковых столбцов это значение — заданное числом символов столбца для индекса. Для двоичных строковых столбцов это значение представляет собой заданное количество байтов столбца для индекса.

ASC — необязательный. Индекс сортируется в порядке возрастания для этого столбца.

DESC — необязательный. Индекс сортируется в порядке убывания для этого столбца.

Пример

Рассмотрим пример создания индекса в MySQL с помощью оператора CREATE TABLE. Этот пример одновременно создаст таблицу, а также индекс.
Например:

В этом примере мы создали таблицу contacts, а также индекс, называемый contacts_idx, который состоит из столбцов last_name и first_name.

Затем мы рассмотрим, как создать таблицу, а затем создать индекс, используя оператор CREATE INDEX.
Например:

В этом примере оператор CREATE TABLE создаст таблицу contacts. Оператор CREATE INDEX создаст индекс, называемый contacts_idx, который состоит из полей last_name и first_name.

Уникальный INDEX

Чтобы создать уникальный индекс в таблице, вам нужно указать ключевое слово UNIQUE при создании индекса. Опять же, это можно сделать с помощью оператора CREATE TABLE или оператора CREATE INDEX.
Например:

ИЛИ

Оба эти примера создавали бы уникальный индекс для полей last_name и first_name, так что комбинация этих полей всегда должна содержать уникальное значение без дубликатов. Это отличный способ обеспечить целостность вашей базы данных, если вам нужны уникальные значения в столбцах, которые не являются частью вашего первичного ключа.

Удалить INDEX

Вы можете удалить индекс в MySQL с помощью оператора DROP INDEX.

Синтаксис

Синтаксис для удаления индекса с помощью оператора DROP INDEX в MySQL:

Параметры или аргументы

index_name — имя индекса для удаления.
table_name — имя таблицы, в которой был создан индекс.

Пример

Рассмотрим пример удаления индекса в MySQL.
Например:

В этом примере мы удалили индекс contacts_idx из таблицы contacts.

Переименование INDEX

Вы можете переименовать индекс в MySQL. В зависимости от вашей версии MySQL существуют два разных синтаксиса.

Синтаксис

Синтаксис для переименования индекса с помощью оператора ALTER TABLE в MySQL 5.6 и более старых версиях:

ALTER TABLE table_name
DROP INDEX index_name,
ADD INDEX new_index_name [ USING BTREE | HASH ]
(index_col1 [(length)] [ASC | DESC],
index_col2 [(length)] [ASC | DESC],

index_col_n [(length)] [ASC | DESC]);

ИЛИ
Синтаксис для переименования индекса в MySQL 5.7 или новее:

ALTER TABLE table_name
RENAME INDEX index_name TO new_index_name;

Параметры или аргументы

index_name — имя индекса для удаления.
table_name — имя таблицы, в которой был создан индекс.
new_index_name – новое имя для индекса.

Пример

Рассмотрим пример того, как переименовать индекс в MySQL. В более старых версиях MySQL вам нужно использовать оператор ALTER TABLE, чтобы сначала удалить старый индекс, а затем воссоздать новый индекс.

Например (MySQL 5.6 и старее):

В этом примере мы переименовали индекс с именем contacts_idx в contacts_new_index. Это было сделано путем удаления старого индекса, а затем добавления нового индекса.

Начиная с MySQL 5.7, вы можете использовать оператор ALTER TABLE с предложением RENAME INDEX, чтобы переименовать индекс.

Например (MySQL 5.7 и новее):

Этот примет также переименовал бы индекс contacts_idx в contacts_new_index. Если вы не знаете, какую версию MySQL вы используете, безопаснее использовать первый синтаксис (MySQL 5.6 и старее) для переименования индекса.