MySQL index — создание и удаление индексов

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

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

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

Цикл по принципам работы с MySQL, более ранние материалы:

MySQL index и оптимизация запросов

Индексы может создаваться для данных любых типов, поддерживаемых MySQL.

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

Индексы лучше не использовать для наборов данных, которые часто обновляются. MySQL при обновлении пересоздает индекс, что может сильно замедлять систему. Индексы применимы и эффективны там, где очень много тяжелых SELECT-ов и мало UPDATE-ов.

INDEX можно создать сразу при создании таблицы или сделать это позже.

Сразу при создании таблицы это может быть так (резонно ожидаем самое большое количество запросов на выборку с ограничением по цене, поэтому индекс создаем для колонки PRICE):

CREATE TABLE REAL_ESTATE (type VARCHAR(20), city VARCHAR(20), floorspace INT, district VARCHAR(20), street VARCHAR(20), rentorsale VARCHAR(20), PRICE VARCHAR (20), INDEX (PRICE));

Другой способ создания индексов применим к уже существующим таблицам

CREATE INDEX PRICE on PEOPLE(PRICE);

Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

Созданные для таблицы индексы можно легко посмотреть — PEOPLE в примере — имя таблицы

SHOW INDEXES FROM PEOPLE;

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

DROP INDEX PRICE on PEOPLE;

Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

UNIQUE MySQL INDEX

Уникальный индекс не допускает повторения значений в проиндексированных колонках, т.е. при выборке будет игнорироваться каждое второе и последующее совпадение

CREATE UNIQUE INDEX PRICE on PEOPLE(PRICE);

Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

Сейчас если просмотреть индексы в поле Non_unique будет другое значение

SHOW INDEXES FROM PEOPLE;

Порядок в проиндексированной колонке по умолчанию возрастающий. Можно изменить его добавив DESC после ее имени — для примера команда выглядела бы так:

CREATE UNIQUE INDEX PRICE on PEOPLE(PRICE DESC);

Составные индексы MySQL

Для одного запроса может быть использован только один индекс. Если требуется иметь отсортированные значения в двух колонках индекс нужно создавать один, но делать его составным.

Вновь удалим наш индекс для таблицы PEOPLE и создадим новый — в этот раз составной.

CREATE INDEX PRICE_AND_CITY on PEOPLE(PRICE, CITY);

Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

Для запроса сразу выполним EXPLAIN

EXPLAIN SELECT PRICE, CITY FROM PEOPLE WHERE CITY = 'Yekaterinburg' AND PRICE < 30000;

Видно, что значение key_len — 46, это длина в байтах используемого составного индекса. Если бы индекс для этого запроса не подходил использовалась бы только его часть и длина была бы меньше.

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

Если в запросе используется ORDER BY и таких запросов ожидается много лучше указывать колонки для сортировки во вторую очередь.

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

Это лучше понять на примере:

SELECT CITY, COUNT(*) FROM PEOPLE GROUP BY CITY;

SELECT PRICE, COUNT(*) FROM PEOPLE GROUP BY PRICE;

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

За счет MySQL INDEX в этом случае будут сначала отфильтрованы значения по первой части и получено небольшое количество результатов. По ним поиск будет производить значительно легче и быстрее.

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

Общие рекомендации по использованию индексов

Создавать индексы только для медленных частых запросов
Использовать их там, где много SELECT-ов и мало UPDATE-ов
Периодически проверять логи медленных запросов
Удалять неиспользуемые индексы и обновлять существующие
Не использовать MySQL INDEX-ы совсем если нет большой нагрузки

Используется ли индекс для запроса и насколько оптимально используется можно вычислить при помощи EXPLAIN