Как правильно пользоваться индексами в MySQL?

Question

[WebDev]

В самом простом случае, бывают 2 вида индексов - одиночные и составные.
Например, для запроса
SELECT * FROM `users` WHERE `age` > 20
Нам понадобится индекс для поля age.

А вот в таком запросе

SELECT * FROM `users` WHERE `age` > 20 AND `gender` = 'male'

Нам нужен составной индекс age_gender, потому что MySQL использует только 1 индекс на запрос, соответственно, если у нас будут 2 отдельных индекса для age и для gender, то использован будет только 1.

Как с этим жить, если проект в разработке, запросы разные и появляются новые.
Например есть запрос

SELECT * FROM `users` WHERE `age` > 20 AND `gender` = 'male' ORDER BY `name`

И для него я создам индекс age_gender_name

А есть такой же запрос, но сортировка по рейтингу

SELECT * FROM `users` WHERE `age` > 20 AND `gender` = 'male' ORDER BY `rating`

Или же порядок полей может меняться.
Создавать индексы под все варианты запросов?
Как правильно делать?

Answer 1

[shagguboy]

>Нам нужен составной индекс age_gender, потому что MySQL использует только 1 индекс >на запрос

в 5.7 появился мерж индексов.

запросы могут использовать первые поля составных индексов.

SELECT * FROM `users` WHERE `age` > 20 AND `gender` = 'male' ORDER BY `name`
SELECT * FROM `users` WHERE `age` > 20 AND `gender` = 'male' ORDER BY `rating`

эти два запроса могут использовать индекс (age, gender, rating)

второй запрос может использовать индекс (age, gender) но только для фильтрации сортировка будет на диске (using filesort) некритично для небольших выборок.

Answer 2

[Максим Федоров]

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

Comments

[Николай]

"включите лог медленных запросов в MySQL" - Подскажите, это как? :)

[Максим Федоров]

ruhighload.com/post/%D0%9A%D0%B0%D0%BA+%D0%B2%D0%B...

[Николай]

Максим Федоров: благодарю)