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

Question

[Глеб Жуков]

Читаю эту статью. Там в 7 пункте написано как выбирать индексы например для такого запроса:

SELECT * FROM users WHERE age = 29 AND gender = 'male'

Там написано что нужно выбирать порядок колонок в индексе сначала age, потом gender
CREATE INDEX age_gender ON users(age,gender);
Потому что любое значение колонки age обычно содержит около 200 записей.
А любое значение колонки gender — около 6000 записей.

mysql> select age, count(*) from users group by age;
+------+----------+
| age  | count(*) |
+------+----------+
|   15 |      160 |
|   16 |      250 |
|        ...      |
|   76 |      210 |
|   85 |      230 |
+------+----------+
68 rows in set (0.00 sec)

mysql> select gender, count(*) from users group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| female |     8740 |
| male   |     4500 |
+--------+----------+
2 rows in set (0.00 sec)

Выше в статье было написано что индексы это колонки где значения упорядочены, и потом когда СУБД использует индекс она бинарным поиском находит нужные первичные ключи в этих упорядоченных колонках.
Так вот, почему сначала в индексе надо сначала указывать age, если можно указать сначала gender и отсечь за одну операцию ~6000 строк (это примерно пол таблицы) и потом в этой половине искать уже по age что будет уже в 2 раза быстрее чем если строить индекс age_gender.
Сори что так много написал, не получилось кратко.

Answer 1

[ayazer]

Так вот, почему сначала в индексе надо сначала указывать age, если можно указать сначала gender и отсечь за одну операцию ~6000 строк (это примерно пол таблицы) и потом в этой половине искать уже по age что будет уже в 2 раза быстрее чем если строить индекс age_gender.

а если сначала отфильтровать по age - то выйдет СРАЗУ откинуть не половину таблицы, а все кроме тех 200 записей. и потом используя gender - фильтровать не пол таблицы, а только этих 200 записей (а не 6000).

т.е.
gender, age -> по первому индексу получаем 6-8к записей, по ним фильтруем используя второй индекс
age, gender -> по первому индексу получаем 200 записей, по ним фильтруем используя второй индекс (или в случае с low cardinality индексами может оказаться что быстрее просто просмотреть все записи)

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

Comments

[Глеб Жуков]

Если индекс по gender_age то вторая колонка в индексе тоже будет упорядочена. Эти ~6000 записей age внутри gender_age тоже будут упорядочены. Это как ORDER BY gender,age

[Глеб Жуков]

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

За эту инфу спасибо.

[ayazer]

Глеб Жуков. упорядочена. но относительно gender. именно по этой причине индекс (gender, age) нельзя использовать для поиска только по age. в итоге нужно будет делать доп. проверки для всех 6к записей что не отфильтровались по первому ключу. а зачем это делать если можно сразу отфильтровать все кроме 200 записей? Именно в этом примере разница и не важна, но когда кол-во записей перевалит за пару терабайт - начнет болеть.

насколько я помню explain analyze во всех деталях показывает как работает оптимайзер.