Важен ли порядок столбцов в индексе Mysql при поиске Where?

Question

[yuriy2]

Добрый день! Изучаю индексы. В литературе и интернете пишут, что при наличии многоколоночного индекса важен порядок в Where (левая часть индекса), иначе индекс может быть не использован вообще.
Пример, есть индекс abc.

Везде пишут такие примеры:
Select a from table where a=10, b= 20 - будет использовать индекс.
Select a from table where a=10 - будет использовать индекс.
Select a from table where b= 20 - НЕ будет использовать индекс.
Select a from table where a=10, с= 30 - будет использовать индекс но только по полю а.
Select a from table where b= 20, с=30 - НЕ будет использовать индекс.

Провожу эксперимент:

create table ind (
`id` int(3) not null auto_increment,
`a` varchar(32) not null,
`b` varchar(32) not null,
`c` varchar(32) not null,
primary key(`id`));


Создаем индекс

create index idx_abc on ind(a,b,c);


Запрос:

explain select a from ind where c='test4'\G;

В результате explain поясняет, что использован индекс для запроса, хотя по идее не должен

id: 1
select_type: SIMPLE
table: ind
partitions: NULL
type: index
possible_keys: NULL
key: idx_abc
key_len: 294
ref: NULL
rows: 7
filtered: 14.29
Extra: Using where; Using index

Вопрос: важен ли порядок столбцов в индексе при поиске при поиске Where?

Answer 1

[Alex McArrow]

Здесь create index idx_abc on ind(a,b,c); вы создаете составной индекс
Здесь ваш запрос explain select a from ind where c='test4' использует часть индекса => индекс используется

Comments

[Rsa97]

Неправильно. Часть составного индекса используется, только если она совпадает с началом ключа.
В вашем примере WHERE a=1 будет использовать индекс, WHERE a=1 and b=2 тоже, а WHERE c='text' - не будет.

[yuriy2]

Спасибо за ответ, получается, это неверно?

Везде пишут такие примеры:
Select a from table where a=10, b= 20 - будет использовать индекс.
Select a from table where a=10 - будет использовать индекс.
Select a from table where b= 20 - НЕ будет использовать индекс.
Select a from table where a=10, с= 30 - будет использовать индекс но только по полю а.
Select a from table where b= 20, с=30 - НЕ будет использовать индекс.

[Alex McArrow]

yuriy2, думаю надо еще учитывать версию Мускула. Алгоритмы построения и использования индексов меняются (не часто, но есть такое)

[Alex McArrow]

В вашем примере WHERE a=1 будет использовать индекс, WHERE a=1 and b=2 тоже, а WHERE c='text' - не будет.

Rsa97, explain утверждает обратное.

[yuriy2]

Я так понимаю explain можно трактовать так
"Using where;" - видимо, когда эта строчка есть, то индекс при поиске в Where не используется.
А "Using index" - показывается потому, что есть "Select a", где a - самая левая часть индеса

[Alex McArrow]

yuriy2, если просто "Using Index" - то используются только индексы (что гораздо производительнее)
если "Using Where: Using Index" - то используются индексы для выборку пула данных (не точных) и последующий поиск по ним Where что гораздо медленнее.
Я так понял этот момент
Rsa97 - подправьте если ошибся.

[yuriy2]

Alex McArrow, кажется, коллективно разобрались :)
Ведь выборка как работает в Mysql: сначала идет выборка всех строк, а затем пользователю отдаются те, которые подходят под условие.
В данном случае Mysql ищет данные в индексе "Select a" (а - самая левая часть индекса), выбирает их, а затем применяет Where, где индекс не работает (с - самая правая часть)

[Rsa97]

yuriy2, Если я правильно понимаю документацию, то Using where присутствует всегда, кроме случаев, когда в запросе явно указано, что нужно взять все строки (то есть отсутствуют WHERE, JOIN, HAVING и т.п.).
Об использовании составного индекса здесь прямо говорится, что используется только непрерывная левая часть индекса.
Эврика! Здесь говорится, что в случае покрывающего индекса (а здесь именно такой случай) записи индекса используются для ускорения доступа к данным при переборе, так как записи индекса, в общем случае, занимают меньше места и расположены компактнее, чем записи самой таблицы.

Answer 2

[Rsa97]

В вашем примере речь идёт, скорее, не о порядке сравнений во WHERE, а о порядке колонок в индексе.
При поиске используется только начальная часть составного индекса. То есть индекс (a, b, c) можно использовать при поиске по a, ab или abc. Поиск по b, c или bc использовать такой составной индекс не будет.

Обратите внимание на строчку 'rows' в ответе EXPLAIN'a, подозреваю, что у вас в таблице всего семь строк, а значит запрос просмотрел их все, не воспользовавшись индексом.

Эврика! Здесь говорится, что в случае покрывающего индекса (а здесь именно такой случай) записи индекса используются для ускорения доступа к данным при переборе, так как записи индекса, в общем случае, занимают меньше места и расположены компактнее, чем записи самой таблицы.