Алгоритм сортировки строк Filesort в MySQL

Когда SQL-запрос содержит условия сортировки ORDER BY или группировки GROUP BY, а подходящий индекс для извлечения строк в нужном порядке отсутствует, СУБД использует алгоритм Filesort. Для выполнения сортировки в оперативной памяти каждому клиентскому соединению динамически выделяется буфер, размер которого контролируется системной переменной sort_buffer_size. Если объем сортируемых данных превышает размер этого буфера (либо если размер одной строки превышает максимальный лимит сортировки max_sort_length), запрос аварийно прерывается:

«ERROR 1038 (HY000): Out of sort memory, consider increasing server sort buffer size»

Бизнес-риски

Сбой работы каталогов интернет-магазинов при использовании сортировок (по цене, новизне, популярности), невозможность выгрузки отсортированных реестров и отчетов.

Сравнение стратегий Filesort в памяти и на диске

Регламент устранения ошибки Out of sort memory

Сценарий 1: Безопасное увеличение sort_buffer_size в сессии или глобально

По умолчанию sort_buffer_size обычно составляет от 256K до 2M. Увеличьте его до безопасных пределов:

# 1. Проверка текущего значения sort_buffer_size:
SHOW VARIABLES LIKE 'sort_buffer_size';

# 2. Динамическое увеличение для глобальной конфигурации (например, до 4M или 8M):
SET GLOBAL sort_buffer_size = 4 * 1024 * 1024;

# 3. Персистентное сохранение в my.cnf:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
sort_buffer_size = 4M

# 4. Перезапуск службы (при необходимости):
sudo systemctl reload mysql

Сценарий 2: Оптимизация запроса с помощью добавления индекса

Вместо бездумного наращивания буферов памяти устраните причину Filesort добавлением покрывающего индекса:

-- Пример проблемного запроса:
SELECT id, title, price FROM products WHERE category_id = 15 ORDER BY price DESC;

-- Анализ плана выполнения запроса:
EXPLAIN SELECT id, title, price FROM products WHERE category_id = 15 ORDER BY price DESC;
-- Если в поле 'Extra' написано 'Using filesort' -> индекс отсутствует!

-- Создание композитного индекса (фильтрация + сортировка):
ALTER TABLE `products` ADD INDEX `idx_category_price` (`category_id`, `price`);

-- После добавления индекса в EXPLAIN исчезнет 'Using filesort', а ошибка 1038 полностью пропадет!

Сценарий 3: Ограничение выборки тяжелых текстовых полей (TEXT/BLOB)

Если в запросе выбираются широкие текстовые поля, алгоритм сортировки резервирует под них максимальный объем памяти:

-- НЕПРАВИЛЬНО (Выбирает длинные описания, забивая sort buffer):
SELECT * FROM blog_posts ORDER BY created_at DESC LIMIT 20;

-- ПРАВИЛЬНО (Выбираем только необходимые легкие поля):
SELECT id, title, created_at FROM blog_posts ORDER BY created_at DESC LIMIT 20;

Типовые ошибки администраторов

• Установка sort_buffer_size = 1G в глобальной конфигурации: Память sort_buffer_size выделяется каждым потоком индивидуально при выполнении сортировки. Если 100 пользователей одновременно запустят поиск, MySQL попытается выделить 100 Гб RAM и будет немедленно уничтожен демоном OOM-Killer.
• Игнорирование параметра max_sort_length: При сортировке полей типа TEXT сервер учитывает только первые max_sort_length байт (по умолчанию 1024).