Почему обратное сканирование индекса медленнее, чем сортировка файлов в MySQL 8.0.33? ⇐ MySql

[Anonymous]

У меня есть таблица InnoDB примерно с 3 миллионами строк и следующей структурой:

Код: Выделить всё

CREATE TABLE `test` (
`id` bigint NOT NULL,
`serial_no` varchar(45) COLLATE utf8mb4_bin DEFAULT NULL,
`service_id` int DEFAULT NULL,
`request_time` datetime DEFAULT NULL,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_service_id` (`service_id`) USING BTREE,
KEY `idx_request_time` (`request_time`) USING BTREE,
KEY `idx_service_id_request_time` (`service_id`,`request_time` DESC) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

Я обнаружил значительную разницу в производительности между двумя очень похожими запросами, которые возвращают одни и те же 56 строк. Единственное отличие — это столбец в предложении ORDER BY.
1. Медленный запрос (~24 секунды) – использует индексное и обратное индексное сканирование

Код: Выделить всё

SELECT id
FROM test
WHERE
serial_no LIKE CONCAT('1112344122512312', '%') AND
service_id IN (501, 1200, 1800, ..., 50000) -- ~80 items in the list
ORDER BY request_time DESC
LIMIT 100;

План выполнения (ОБЪЯСНИТЕ):

Код: Выделить всё

id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: index
possible_keys: idx_service_id, idx_service_id_request_time
key: idx_request_time
key_len: 6
rows: 190
filtered: 5.82
Extra: Using where; Backward index scan

2. Более быстрый запрос (~ 4 секунды) — использует полное сканирование и сортировку файлов

Код: Выделить всё

SELECT id
FROM test
WHERE
serial_no LIKE CONCAT('1112344122512312', '%') AND
service_id IN (501, 1200, 1800, ..., 50000) -- Same list as above
ORDER BY create_time DESC -- Changed column
LIMIT 100;

План выполнения (ОБЪЯСНИТЕ):

Код: Выделить всё

id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: ALL
possible_keys: idx_service_id, idx_service_id_request_time
key: NULL
key_len: NULL
rows: 3480583
filtered: 5.82
Extra: Using where; Using filesort

Проблема и мое понимание
Более медленный запрос использует сканирование индекса (

Код: Выделить всё

idx_request_time

) с обратным обходом (для порядка DESC), рассчитанный на проверку только ~190 строк. Более быстрый запрос выполняет полное сканирование таблицы по всем примерно 3,5 миллионам строк и использует сортировку файлов.
Интуитивно понятно, что чтение 190 строк через индекс должно быть намного быстрее, чем сканирование 3,5 миллионов строк и сортировка. Однако здесь верно обратное: разница в производительности составляет 6 раз.
Моя гипотеза:
Моя гипотеза состоит в том, что обратное сканирование индекса заставляет механизм запросов последовательно проходить индекс, проверяя каждую строку на соответствие условиям WHERE, пока не соберется достаточное количество совпадающих строк (в данном случае только 56). Напротив, работает ли подход сортировки файлов, сначала фильтруя строки с условиями WHERE, а затем сортируя полученный набор по create_time? Как я могу получить более подробную информацию о выполнении, например данные профилирования или фактическое количество проверенных строк, чтобы проверить это предположение?
Мои вопросы:

• Правильны ли мои рассуждения о разнице в производительности? Не могли бы вы объяснить мне эту разницу в производительности?
• Как я могу получить более подробную информацию о выполнении (например, данные профилирования, фактические проверенные строки, метрики ввода-вывода), чтобы убедиться в этом?

Среда: MySQL 8.0.33.

Подробнее здесь: https://stackoverflow.com/questions/798 ... sql-8-0-33