SQL создание индекса (CREATE INDEX команда)

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

Что такое индекс?

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

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

SELECT cust_id, cust_name, address FROM customers　
WHERE cust_name LIKE 'A%';

Чтобы найти таких клиентов, серверу необходимоcustomersТаблица сканируется построчно, и проверяется содержимое столбца с именами.尽管对于只有几行的表来说， это может работать нормально, но представьте себе, если таблица содержит один миллион строк, то выполнение запроса может занять много времени. В этом случае вы можете ускорить обработку, добавив индекс на таблицу.

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

Вы можете создать индекс с помощью следующего оператора CREATE INDEX:

CREATE INDEX index_name ON table_name (column_name);

Например, чтобы создать индекс в таблице клиентовnameДля создания индекса на столбце можно использовать:

CREATE INDEX cust_name_idx ON customers (cust_name);

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

CREATE UNIQUE INDEX cust_name_idx　
ON customers (cust_name);

В MySQL вы можете проверить доступные индексы на определенной таблице, как показано ниже:

mysql> SHOW INDEXES FROM customers \G

Создание многостолбцового индекса

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

CREATE INDEX user_name_idx ON users (first_name, last_name);

Недостатки индексов

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

Ниже приведены некоторые основные принципы, которые можно следовать при создании индексов:

• Создайте индекс для столбцов, которые вы часто используете для поиска данных.

• Не создавайте индекс для столбца, который никогда не используется в качестве ключа поиска.

• Создайте индексные столбцы для улучшения производительности связи.

• Избегайте включения в столбцы слишком большого количества значений NULL.

Точно так же, в таблицах с небольшим количеством данных индекс не требуется, так как сканирование таблицы сервером обычно быстрее, чем сначала проверять индекс.

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

Вы можете использовать следующие строки для удаления индексов, которые больше не нужны.

DROP INDEX index_name ON table_name;

Следующие строки будут удалять изcustomersУдаление индекса таблицыcust_name_idx.

DROP INDEX cust_name_idx ON customers;

Кроме того, еслиУдаление таблицы:Тогда также будут удалены все связанные индексы.