machetero
@machetero
gotlib.me

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

Читаю эту статью. Там в 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.
Сори что так много написал, не получилось кратко.
  • Вопрос задан
  • 303 просмотра
Пригласить эксперта
Ответы на вопрос 1
ayazer
@ayazer
Sr. Software Engineer

Так вот, почему сначала в индексе надо сначала указывать 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) можно будет и искать по возрасту не указывая пол
Ответ написан
Ваш ответ на вопрос

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

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