Как работает поиск по комбинированным индексам?

Question

[nikivanz]

Здравствуйте!

Вопрос такой: Допустим, есть табличка test, в ней поля:
id, language_id, english_word, word, word_id

id, конечно, пусть будет PK.
Если я создаю 2 комбинированных индекса:
language_id, english_word
и
word, word_id

А потом ищу по табличке так:

WHERE language_id=...
AND english_word=...
AND word=...
AND word_id=...

Включаются-ли в работы два созданных ранее индекса? Т.е. даёт-ли это прирост?

Знаю, что лучше самому проверить! Просто пока нет времени и надеюсь, что у кого-то здесь уже есть в этом твёрдые знания.

Спасибо за помощь!

Answer 1

[Melkij]

Возможность у планировщика такая есть. bitmap index scan может объединять проход по нескольким индексам. Воспользуется ли планировщик этим планом или предпочтёт что-то другое и, тем более, будет ли это дешевле - зависит от статистики распределения данных, настроек, возможностей железа.

Как решит планировщик в вашей системе - ответит explain. Сколько чего реально стоит в рантайме - ответит explain (analyze,buffers)

Не обратил внимание на второй тег, это про postgresql было.

mysql так же умеет, там называется Index Merge. Будет ли использоваться - опять же на усмотрение планировщика.

Comments

[nikivanz]

Большое спасибо! Очень хороший ответ!

[Александр Филиппенко]

Хороший ответ, от себя добавлю что можно вместо одного или обоих индексов, создать 4-х колоночный, желательно располагать колонки в порядке уменьшения селективности, или ориентируясь на другие запросы которые могут использовать этот индекс.
То есть, поскольку у вас вряд ли много разных language_id, и word_id должно быть больше. Вот word_id лучше поставить первым в таком индексе.

Answer 2

[Rsa97]

Будет использован только один индекс, полностью или частично.
Зависит от селективности индекса по данному условию.

Comments

[nikivanz]

Окей. но разве БД не смогёт сначала заюзать первый индекс, а потом второй?

Ведь поля A, B, C, D (где индексированы A,B и C,D) в условии указаны в необходимом порядке (WHERE A=... and B=... and C=... and D=...).

Т.е. разве БД не обработает это по-умному?

[Rsa97]

nikivanz, Не сможет.
Индексы независимые, поэтому использовать можно только один из них.

Answer 3

[Иван Шумов]

делаешь EXPLAIN ..... и он отвечает на все твои вопросы

Comments

[nikivanz]

Читаем предпоследнюю строчку в вопросе, и не засоряем ответы!

[Иван Шумов]

nikivanz, на твой вопрос невозможно ответить без результатов EXPLAIN. Что тебе не понятно в данном предложении?

[nikivanz]

Иван Шумов, возможно, если иметь понимание, как работают индексы.

[Иван Шумов]

nikivanz, я тебе открою секрет - технически это, наверное, возможно. Но, для этого надо знать какая у тебя база данных (ты две указал), какой версии, знать конфигурацию, иметь на руках живые данные, побывать в исходниках. Почему? По тому что анализатор ведет себя очень специфически исходя из огромного числа параметров. Таких уникумов берут на работу DB архитекторами и их по пальцам в мире пересчитать можно

[nikivanz]

Иван Шумов, А вы знаете ответ хотя бы по одной из базе?

[Иван Шумов]

nikivanz, у меня нет даже структуры данных, поэтому это пальцем в небо тыкать. С данными ответить просто)

[d'Ivan]

Иван Шумов,

Таких уникумов берут на работу DB архитекторами и их по пальцам в мире пересчитать можно

И себя тоже к ним приписал?
Кстати, их больше чем пальцев на руке.

[Иван Шумов]

Роман Мирр, я себя к ним не приписывал. В зависимости от данных ответ может отличаться полностью. А производительность может даже не измениться в определенных случаях.