Пара слов про составные индексы в MySQL

Этот пост является очень краткой выжимкой из оригинальной статьи, которая оказалась полезна исключительно для меня.

Выжимка эта подразумевает, что читатель уже хорошо знаком с MySQL и понимает что такое индексы и зачем они нужны, если нет - то читайте оригинальную статью, там все по полочкам разложено с самого начала.

Устройство составного индекса

Таблица для примера:

id | name   | age | gender
1  | Den    | 29  | male
2  | Alyona | 15  | female
3  | Putin  | 89  | tsar
4  | Petro  | 12  | male 

значения составного индекса будут такими:

age_gender
12male
15female
29male
89tsar 

Очередность колонок в индексе играет большую роль. Обычно колонки, которые используются в условиях WHERE, следует ставить в начало индекса. Колонки из ORDER BY — в конец.

---

Представим, что наш запрос будет использовать поиск по диапазону:

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

Тогда MySQL не сможет использовать полный индекс, т.к. значения gender будут отличаться для разных значений колонки age. В этом случае база данных попытается использовать часть индекса (только age), чтобы выполнить этот запрос сначала будут отфильтрованы все данные, которые подходят под условие age <= 29. Затем, поиск по значению "male" будет произведен без использования индекса.

Селективность индексов

Запрос для примера:

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

Для такого запроса необходимо создать составной индекс. Варианта два: age_gender или gender_age. Подойдут оба. Но работать они будут с разной эффективностью.

Чтобы понять это, рассмотрим уникальность значений каждой колонки и количество соответствующих записей в таблице:

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 обычно содержит около 200 записей.
Любое значение колонки gender — около 6000 записей.

Если колонка age будет идти первой в индексе, тогда MySQL после первой части индекса сократит количество записей до 200. Останется сделать выборку по ним. Если же колонка gender будет идти первой, то количество записей будет сокращено до 6000 после первой части индекса. Т.е. на порядок больше, чем в случае age.

Это значит, что индекс age_gender будет работать лучше, чем gender_age.

На этом выжимке конец, самое полезное со своей колокольни я рассказал, за более подробной информацией добро пожаловать в оригинальную статью.