Почему не используется индекс для order by?

436
27 июня 2022, 03:20

У меня есть таблица:

CREATE TABLE `player_statistics` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `player_id` INT(11) NOT NULL,
    `statistic_id` INT(11) NOT NULL,
    `value` INT(11) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `statistic_player_value` (`statistic_id`, `player_id`, `value`)
)

Как видно, имеется индекс statistic_player_value, который содержит в себе 3 колонки: statistic_id, player_id, value.

Я тестирую индекс таким запросом:

explain SELECT *
FROM `player_statistics` 
WHERE `statistic_id`=1 AND `player_id` =15
ORDER BY `value` desc
LIMIT 10;

Результат:

+------+-------------+-------------------+------+-----------------------------------------------+------------------------+---------+-------------+------+--------------------------+
| id   | select_type | table             | type | possible_keys                                 | key                    | key_len | ref         | rows | Extra                    |
+------+-------------+-------------------+------+-----------------------------------------------+------------------------+---------+-------------+------+--------------------------+
|    1 | SIMPLE      | player_statistics | ref  | player_id,statistic_id,statistic_player_value | statistic_player_value | 8       | const,const |    1 | Using where; Using index |
+------+-------------+-------------------+------+-----------------------------------------------+------------------------+---------+-------------+------+--------------------------+

Как видно, key_len равен 8, а это значит, что индекс не использовался полностью. Только player_id и statistic_id использовали индекс. Я ожидаю, что key_len должен быть равен 12.

Например, такой запрос выдает key_len 12:

explain SELECT *
FROM `player_statistics` 
WHERE `statistic_id`=1 AND player_id =15 AND `value` = 1

Так в чем проблема, почему order by тогда не использует индекс?

Answer 1

У вас срабатывает составной индекс, так как используете в запросе WHERE с указанием полей

key_len даёт понять используется ли он весь или только какая-то часть

Так как у вас поля INTEGER, которым выделена память 4 байта в базе, и в WHERE вы используется два поля, то в вашем запросе получается 8 при фильтрации по двум полям.

Либо 4 байта - по одному полю.

!! Потому что была произведена фильтрация и мы видим, что использовалось не 12 байтов, а 8 байтов в вашем случае.

Составной индекс может содержать более одного столбца и до 16 столбцов, но их общая длина ограничена 900 байтами.

  • об индексах на хабре
  • о выделенных байтах для типов полей
  • об explain

Оптимизатор запросов MySQL пытается придумать оптимальный план выполнения этого запроса.

Даю подробности: Предистория для скриншотов ниже:

USING WHERE — не означает, что индекс не используется. Это означает, что результат дополнительно проверяется на соблюдение условий. Наличие индекса при вытаскивании данных можно увидеть в столбце key.

Ситуация с единичными полями:

Вопрос: Почему складывается ощющение, что не работает INDEX при ORDER BY? ОТВЕТ dev.mysql.com

Искать на странице строку ORDER BY Execution Plan Information Available

Отвечаю кратко и заранее: Не встретили в Extra column текста Using filesort - значит всё ОК! Если не используется индекc - увидите Using filesort

  • Если заполнено possible_keys и/или как минимум key, а также в Extra нет Using filesort и нет Using temporary, то значит индекс сработал.

Проверка на работу индексов: удалите индекс и сделайте тот же запрос с EXPLAIN и если rows станет на порядок больше, значит до этого индексы работали.

Часть инфы может не выводится с EXPLAIN из-за версии sql, или тип базы данных, или используемых запросов + сам решает оптимизатор, что показать, а что нет

READ ALSO
Как зайти в MySQL Server в Docker container?

Как зайти в MySQL Server в Docker container?

Собрал образ на основании вот этого Dockerfile:https://githubcom/mysql/mysql-docker/tree/mysql-server/8

371
Перегрузка операторов ввода/вывода при наследовании

Перегрузка операторов ввода/вывода при наследовании

Есть базовый класс Computer и производный класс Laptop:

253
Удаление элементов массива указателей на базовый класс

Удаление элементов массива указателей на базовый класс

У меня базовый класс с двумя виртуальными методами и два класса-наследникаВ функции main есть массив указателей на базовый класс, который может...

215