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

Question

[mihavxc]

Подскажите, как правильно расставить индексы в таблице 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`).


или то же самое, но без последнего индекса. Просветите, пожалуйста, как правильней поступить.

Answer 1

[Vampiro]

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

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

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

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

Не бойтесь добавлять слово EXPLAN в тело запроса и смотрите какие индексы он использует. Порой оптимизатор использует fullscan даже при наличии индексов.

Answer 2

[ShouldNotSeeMe]

Если все запросы у Вас ограничиваются теми четырьмя вариантами, то достаточно оставить индексы по B,C,A и A,C,D.

Comments

[ShouldNotSeeMe]

P.S. Ставьте индексы именно в таком порядке.
Тут сказано:
Если данная таблица имеет многостолбцовый индекс, то любой крайний слева префикс этого индекса может использоваться оптимизатором для нахождения строк. Например, если имеется индекс по трем столбцам (col1,col2,col3), то существует потенциальная возможность индексированного поиска по (col1), (col1,col2) и (col1,col2,col3).
Если индекс существует по (col1,col2,col3), то только первый показанный выше запрос использует данный индекс. Второй и третий запросы действительно включают индексированные столбцы, но (col2) и (col2,col3) не являются крайней слева частью префиксов (col1,col2,col3).

Answer 3

[rakot]

ALTER TABLE `table` ADD INDEX ( ` A`) — этот бесполезен, т.к. вместо него можно использовать ALTER TABLE `table` ADD INDEX ( ` A,B,C`) или ALTER TABLE `table` ADD INDEX ( ` A,C,D`), а вообще вы занимаетесь преждевременной оптимизацией.

Answer 4

[Павел Назаров]

Всё вышенаписанное в целом верно и правильно.

Замечу еще одну важную вещь. Важно не только, какие поля используются в 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.

Ну и в-третьих, успехов вам :)

Comments

[Dzen_Marketing]

Дополню — это называется «Покрывающий индекс»

Answer 5

[vadiml]

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

Скан книги есть в инете.

Answer 6

[CKOPOBAPKuH]

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

но вам стоит настроить slowlog и оптимизировать те запросы, которые на самом деле нужно оптимизировать, а не то, чем вы сейчас занимаетесь.

Comments

[novoa]

а по полям полям A, B, С ?

[CKOPOBAPKuH]

novoa: а запрос по полям A,B,C, если он на равенство, покрывает индекс по B,C,A

Answer 7

[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`)
Это правильно, если данных много.
Можно добавлять индексы не сразу, а по мере того, как будешь замечать, что соответствующий запрос тормозит.
Может быть вообще эти индексы не надо будет добавлять.

Comments

[niko83]

иместь сразу
ALTER TABLE `table` ADD INDEX ( ` A`).
ALTER TABLE `table` ADD INDEX ( ` A,B,C`)
избыточно.
достаточно
ALTER TABLE `table` ADD INDEX ( ` A,B,C`)