Коли MySQL сортує через filesort і як індекс допомагає ORDER BY з LIMIT?

MySQL отримує відсортований результат двома способами:

1. читає індекс по порядку - рядки вже йдуть відсортованими, а з LIMIT можна зупинитися після N рядків;
2. filesort - читає всі відповідні рядки й сортує їх окремо. Попри назву, це не обов'язково диск: сортування йде в пам'яті (sort_buffer_size), і лише якщо дані не поміщаються - у тимчасових файлах.

Типовий запит стрічки:

SELECT * FROM posts
WHERE author_id = 5
ORDER BY published_at DESC
LIMIT 20;

• з індексом (author_id): знаходимо всі 50 000 постів автора, сортуємо їх усі (Using filesort), віддаємо 20;
• з індексом (author_id, published_at): йдемо по індексу в потрібному порядку, читаємо 20 записів і зупиняємося. Різниця може бути в тисячі разів.

Коли індекс може дати порядок:

• колонки ORDER BY - це продовження колонок з рівністю в WHERE: WHERE a = ? ORDER BY b, c з індексом (a, b, c);
• або ORDER BY за лівим префіксом індексу без WHERE на інших колонках;
• напрямки сортування узгоджені: або всі однакові (індекс читається вперед чи назад), або збігаються з напрямками в індексі (DESC-індекси).

Коли filesort неминучий:

WHERE a > 10 ORDER BY b            -- діапазон по a, сортування по b
WHERE a IN (1, 2) ORDER BY b       -- кілька значень a - кілька відсортованих шматків
ORDER BY a, b DESC                 -- різні напрямки при індексі (a, b) з однаковими
ORDER BY LOWER(name)               -- вираз
ORDER BY t1.a, t2.b                -- колонки з різних таблиць

Як зробити filesort дешевшим, якщо його не уникнути:

• вибирати менше колонок: MySQL сортує кортежі з потрібними колонками, і SELECT * з TEXT-полями роздуває буфер;
• LIMIT з filesort використовує пріоритетну чергу - зберігає лише N найкращих рядків, а не сортує все;
• для великих сортувань - збільшити sort_buffer_size для сесії, а не глобально (він виділяється на кожне з'єднання).

Діагностика: Using filesort в EXPLAIN і лічильники Sort_merge_passes (злиття тимчасових файлів - сортування не вмістилося в пам'ять) та Sort_scan / Sort_range у SHOW GLOBAL STATUS.

Пагінація через OFFSET навіть з індексом читає й відкидає всі пропущені рядки. Для глибоких сторінок краще курсорна пагінація (WHERE published_at < ? ORDER BY published_at DESC LIMIT 20, у Laravel - cursorPaginate()).

Докладніше в документації: Оптимізація ORDER BY