Справочное руководство по MySQL

5.2.7 Как MySQL оптимизирует ORDER BY

В некоторых случаях MySQL может использовать индекс, чтобы выполнить запрос ORDER BY или GROUP BY без выполнения дополнительной сортировки.

Индекс может также использоваться и тогда, когда предложение ORDER BY не соответствует индексу в точности, если все неиспользуемые части индекса и все столбцы, не указанные в ORDER BY - константы в выражении WHERE. Следующие запросы будут использовать индекс, чтобы выполнить ORDER BY / GROUP BY.

SELECT * FROM t1 ORDER BY key_part1,key_part2,...
SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2
SELECT * FROM t1 WHERE key_part1=constant GROUP BY key_part2
SELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 DESC
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC,key_part2 DESC

Ниже приведены некоторые случаи, когда MySQL не может использовать индексы, чтобы выполнить ORDER BY (обратите внимание, что MySQL тем не менее будет использовать индексы, чтобы найти строки, соответствующие выражению WHERE):

• Сортировка ORDER BY делается по нескольким ключам: SELECT * FROM t1 ORDER BY key1,key2
• Сортировка ORDER BY делается, при использовании непоследовательных частей ключа: SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2
• Смешиваются ASC и DESC. SELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 ASC
• Для выборки строк и для сортировки ORDER BY используются разные ключи: SELECT * FROM t1 WHERE key2=constant ORDER BY key1
• Связываются несколько таблиц, и столбцы, по которым делается сортировка ORDER BY, относятся не только к первой неконстантной (const) таблице, используемой для выборки строк (это первая таблица в выводе EXPLAIN, в которой не используется константный, const, метод выборки строк).
• Имеются различные выражения ORDER BY и GROUP BY.
• Используемый индекс таблицы имеет такой тип, который не обеспечивает сортированного хранения строк (как индекс HASH в таблицах HEAP).
• Столбцы индекса могут содержать значения NULL, и используется ORDER BY ... DESC. Это объясняется тем, что в SQL значения NULL всегда сортируются в первую очередь, независимо от того, используется DESC или нет.

В тех случаях, когда MySQL должен сортировать результат, он использует следующий алгоритм:

• Считываются все строки согласно ключу или путем сканирования таблицы. Строки, которые не соответствует предложению WHERE, пропускаются.
• Ключ сортировки сохраняется в буфере сортировки (размера sort_buffer)
• Когда буфер заполняется, содержимое буфера сортируется алгоритмом qsort, результаты сохраняются во временном файле. Сохраняется указатель на отсортированный блок (в том случае, когда все строки умещаются в буфере сортировки, временный файл не создается).
• Вышеупомянутое действие повторяется, пока не будут считаны все строки.
• Делается мультислияние до MERGEBUFF (7) областей в один блок в другом временном файле. Это действие повторяется, пока все блоки из первого файла не окажутся во втором файле.
• Предыдущий пункт повторяется, пока не останется менее MERGEBUFF2 (15) блоков.
• При последнем мультислиянии в результирующий файл записывается только указатель на строку (последняя часть ключа сортировки).
• Теперь код в файле 'sql/records.cc' будет использоваться для чтения данных в отсортированном порядке, с использованием указателей на строки из результирующего файла. Чтобы оптимизировать этот процесс, мы считываем большой блок указателей на строки, сортируем их, и затем считываем строки в отсортированном порядке в буфер строк (record_rnd_buffer).

При помощи команды EXPLAIN SELECT ... ORDER BY можно проверить, может ли MySQL использовать индексы для выполнения запроса. Если в столбце extra содержится значение Using filesort, то MySQL не может использовать индексы для выполнения сортировки ORDER BY. See section 5.2.1 Синтаксис оператора EXPLAIN (получение информации о SELECT).

Чтобы сортировка ORDER BY выполнялась с большей скоростью, нужно сначала посмотреть, можно ли заставить MySQL использовать индексы взамен дополнительной фазы сортировки. Если это невозможно, то можно сделать следующее:

• Увеличить значение переменной sort_buffer.
• Увеличить значение переменной record_rnd_buffer.
• Изменить переменную tmpdir, чтобы она указывала на выделенный диск с большим количеством свободного пространства.