Индексы MySQL — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном пособии вы узнаете, как создавать, удалять и переименовывать 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:

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

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. Этот пример одновременно создаст таблицу, а также индекс.
Например:

CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
  last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25),
  birthday DATE,
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id),
  INDEX contacts_idx (last_name, first_name)
);

CREATE TABLE contacts

( contact_id INT(11) NOT NULL AUTO_INCREMENT,

last_name VARCHAR(30) NOT NULL,

first_name VARCHAR(25),

birthday DATE,

CONSTRAINT contacts_pk PRIMARY KEY (contact_id),

INDEX contacts_idx (last_name, first_name)

);

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

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

CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
  last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25),
  birthday DATE,
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

CREATE INDEX contacts_idx
  ON contacts (last_name, first_name);

CREATE TABLE contacts

( contact_id INT(11) NOT NULL AUTO_INCREMENT,

last_name VARCHAR(30) NOT NULL,

first_name VARCHAR(25),

birthday DATE,

CONSTRAINT contacts_pk PRIMARY KEY (contact_id)

);

CREATE INDEX contacts_idx

ON contacts (last_name, first_name);

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

Уникальный INDEX

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

CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
  last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25),
  birthday DATE,
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id),
  UNIQUE INDEX contacts_idx (last_name, first_name)
);

CREATE TABLE contacts

( contact_id INT(11) NOT NULL AUTO_INCREMENT,

last_name VARCHAR(30) NOT NULL,

first_name VARCHAR(25),

birthday DATE,

CONSTRAINT contacts_pk PRIMARY KEY (contact_id),

UNIQUE INDEX contacts_idx (last_name, first_name)

);

ИЛИ

CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
  last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25),
  birthday DATE,
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

CREATE UNIQUE INDEX contacts_idx
  ON contacts (last_name, first_name);

CREATE TABLE contacts

( contact_id INT(11) NOT NULL AUTO_INCREMENT,

last_name VARCHAR(30) NOT NULL,

first_name VARCHAR(25),

birthday DATE,

CONSTRAINT contacts_pk PRIMARY KEY (contact_id)

);

CREATE UNIQUE INDEX contacts_idx

ON contacts (last_name, first_name);

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

Удалить INDEX

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

Синтаксис

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

DROP INDEX index_name
  ON table_name;

1 2	DROP INDEX index_name ON table_name;

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

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

Пример

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

DROP INDEX contacts_idx
  ON contacts;

1 2	DROP INDEX contacts_idx ON contacts;

В этом примере мы удалили индекс 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 и старее):

ALTER TABLE contacts
  DROP INDEX contacts_idx,
  ADD INDEX contacts_new_index (last_name, first_name);

ALTER TABLE contacts

DROP INDEX contacts_idx,

ADD INDEX contacts_new_index (last_name, first_name);

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

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

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

ALTER TABLE contacts
  RENAME INDEX contacts_idx TO contacts_new_index;

1 2	ALTER TABLE contacts RENAME INDEX contacts_idx TO contacts_new_index;

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