Как правильно расставить индексы в БД?

Подскажите, как правильно расставить индексы в таблице mysql.


Имеется таблица с полями A,B,C,D,E.

И несколько запросов, которые делают выборку по:

— полю A

— полям A, B, С

— полям A, С, D

— полям В, С,


Раньше я считал, что для каждого поля, по которому идет выборка достаточно сделать:

ALTER TABLE `table` ADD INDEX ( ` A`).


А теперь меня терзают сомнения может нужно делать

ALTER TABLE `table` ADD INDEX ( ` A`).

ALTER TABLE `table` ADD INDEX ( ` A,B,C`)

ALTER TABLE `table` ADD INDEX ( ` A,C,D`).

ALTER TABLE `table` ADD INDEX ( `B,C`).


или то же самое, но без последнего индекса. Просветите, пожалуйста, как правильней поступить.
  • Вопрос задан
  • 19454 просмотра
Пригласить эксперта
Ответы на вопрос 7
@Vampiro
В общем случае, принципы построения индексов достаточно просты: индекс (кроме первичного) строится для ускорения запросов. Причем если есть WHERE по двум полям, то будет взят индекс, содержащий оба этих поля (если есть) и не важно сколько их там еще.

Другими словами, один индекс по полям «A, B, C» будет более «полезен» для запроса, содержащего некие условия по полям A и B, чем два отдельных индекса по полям «A» и «B».

Фактически, индексы A,B,C и A,C,D будут одинаковы в части поиска по полю «А». При поиске по полям А,B будет взят первый индекс. При поиске по полям A, C — второй.

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

Не бойтесь добавлять слово EXPLAN в тело запроса и смотрите какие индексы он использует. Порой оптимизатор использует fullscan даже при наличии индексов.
Ответ написан
Комментировать
@ShouldNotSeeMe
Если все запросы у Вас ограничиваются теми четырьмя вариантами, то достаточно оставить индексы по B,C,A и A,C,D.
Ответ написан
rakot
@rakot
ALTER TABLE `table` ADD INDEX ( ` A`) — этот бесполезен, т.к. вместо него можно использовать ALTER TABLE `table` ADD INDEX ( ` A,B,C`) или ALTER TABLE `table` ADD INDEX ( ` A,C,D`), а вообще вы занимаетесь преждевременной оптимизацией.
Ответ написан
Комментировать
Всё вышенаписанное в целом верно и правильно.

Замечу еще одну важную вещь. Важно не только, какие поля используются в WHERE-условии, но и какие поля вы выбираете.
Например, если у вас запрос
SELECT t.C FROM `table` t WHERE t.A > <..> AND t.B < <...>
то очевидный вариант для индекса по колонкам (A, B) проиграет менее очевидному варианту по колонкам (A, B, C). Поясню — при наличии индекса (A, B) сначала вам придётся найти по индексу строки, которые удовлетворяют условиям, а потом найти среди данных самой таблицы значение t.C для этих строк. Во втором же случае для вычисления t.C можно будет не идти в таблицу, а взять значение из индекса — такая вещь называется Index Only Scan (хотя, для разных СУБД название может быть разным, конечно). Её умеют MySQL/MSSQL/Oracle вроде бы, и будет уметь PostgreSQL с версии 9.2.

Какова мораль? Во-первых, использовать индексы надо с умом, и необходимо знать, какой индекс лучше и чем.
Во-вторых, всё же вы занимаетесь преждевременной оптимизацией. То есть примерно правильные индексы создать можно, но не факт, что они будут оптимальными для ваших запросов. Выше я привёл пример, когда вроде бы правильный индекс проиграет более специфичному для конкретного запроса индексу. Поэтому, когда БД выйдет на приличные объемы, все сложные/тяжёлые запросы по-хорошему надо будет посмотреть через EXPLAIN.

Ну и в-третьих, успехов вам :)
Ответ написан
@vadiml
Несколько лет назад вышла книга «Oracle для профессионалов», автор Том Кайт.
Глава по индексам там универсальна для большинства баз.

Скан книги есть в инете.
Ответ написан
Комментировать
CKOPOBAPKuH
@CKOPOBAPKuH
те наборы, которые вы указали, покрывают всего 2 индекса:
ALTER TABLE `table` ADD INDEX ( ` A,C,D`).
ALTER TABLE `table` ADD INDEX ( `B,C,A`).

но вам стоит настроить slowlog и оптимизировать те запросы, которые на самом деле нужно оптимизировать, а не то, чем вы сейчас занимаетесь.
Ответ написан
@hexen
ALTER TABLE `table` ADD INDEX ( ` A`).
ALTER TABLE `table` ADD INDEX ( ` A,B,C`)
ALTER TABLE `table` ADD INDEX ( ` A,C,D`).
ALTER TABLE `table` ADD INDEX ( `B,C`)
Это правильно, если данных много.
Можно добавлять индексы не сразу, а по мере того, как будешь замечать, что соответствующий запрос тормозит.
Может быть вообще эти индексы не надо будет добавлять.
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы