Индексация MySQL

#mysql #база_данных

Есть таблица :


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


Есть запрос :

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

Дабы выборка осуществлялась быстрее хочу использовать составной индекс:

CREATE INDEX age_gender ON users(age, gender); или

CREATE INDEX age_gender ON users(gender, age);

Вопрос: 

Почему более правильно использовать 2-й случай?
Почему если age стоит первым, то не проводится индексация по полю gender?
(Понимаю , что загвоздка в том, что age ограничивается диапазоном, но не пойму конкретнее)
    

Ответы

Ответ 1

В mysql индекс представляет собой B-tree(по крайней мере, тот который строите Вы).
В MySQL есть ограничения при использовании B-tree индекса:


при поиске по одному полю может использоваться только самая левая часть ключа(если
индекс по (a,b) нельзя искать по b)
нельзя пропускать столбцы индекса(если индекс по (a,b,c), при поиске по a,c использует
индекс только для a)
индекс будет использовать поля слева направо до первого поиска по диапазону, например
a>10 или a LIKE 'word%' (Это Ваш случай. Если индекс по (age, gender), поиск age <=
29 AND gender = 'male', то первый поиск по дапазону происходит по полю age, а значит
остальная часть индекса не сможет быть использована)


Я описал также случаи, которые не относятся к Вашему вопросу. Надеюсь будут полезны
в будущем. 
Если кто-то вспомнит ещё какие-то правила - пишите в комментах, добавлю сюда.


Ответ 2

Надо просто представить себе индекс. Грубо говоря, индекс - это заранее упорядоченные
значения из базы. gender-age будет выглядеть так

1-20 *
1-20 *
1-22 *
1-29 *
1-40 
2-10
2-20
2-39


А age, gender - так

10-2
20-1 *
20-1 *
20-2
22-1 *
29-1 *
39-2
40-1


Из какого быстрее выбрать - в котором значения идут по порядку, или в котором вразнобой?