Почему оптимизатор mysql не использует полный индекс?

Question

Я пытаюсь понять, почему MySQL не использует полный индекс для ответа на запрос. Позволь мне объяснить. Я использую базу данных IMDB через MySQL версии 5.1.73-1 (Debian). Я создал и проиндексировал "itid_inf25_mid_ndx" в таблице movie_info_idx со столбцами (info_type_id, info(25), movie_id). Столбцы info_type_id и movie_id являются целыми числами (НЕ NULL), а информация имеет тип TEXT, поэтому каждая запись индекса занимает 4+27+4 = 35 байт. Вывод для предложения:

EXPLAIN
SELECT movie_id
FROM movie_info_idx
WHERE info_type_id = 101
AND info > "9";

показывает эти значения:

select_type = SIMPLE; table = movie_info_idx; тип = диапазон; возможные_ключи = itid_inf25_mid_ndx; ключ = itid_inf25_mid_ndx; key_len = 31; ref = NULL; ряды = 841; Extra = "Использование где"

Столбец key_len и отсутствие индекса использования в дополнительном столбце информируют о том, что из индекса используются только столбцы (info_type_id,info(25)), которые в сумме составляют 4+27 = 31 байт. Интересно, почему оптимизатор не использует столбец movie_id из индекса для доступа к movie_id в предложении SELECT? Похоже, что оптимизатор получит доступ к базовой таблице movie_info_idx, чтобы принять значение movie_id, которое я хочу перечислить. Зачем?.

Спасибо заранее за ваш ответ.

Answer 1

Как только MySQL использует запрос для "сканирования диапазона" (соответствует более чем одному значению), он, как правило, больше не будет использовать последний столбец.

Причина этого заключается в том, что многостолбцовые индексы представляют собой дерево деревьев. Для сканирования индекса по последнему столбцу (movie_id) необходимо выполнить поиск в дереве индекса для каждого соответствующего значения столбца диапазона (информация). Как правило, это неэффективно, и поэтому MySQL не будет этого делать.

Чтобы улучшить ситуацию, поместите столбец, который, как ожидается, будет последним сканированием диапазона, поэтому закажите его как (info_type_id, movie_id, info)

Дополнительная информация: https://dev.mysql.com/doc/refman/5.6/en/multiple-column-indexes.html