В некоторых случаях 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
).
В тех случаях, когда 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 Раздел 5.2.1, «Синтаксис оператора EXPLAIN
(получение информации о SELECT
)».
Чтобы сортировка ORDER BY
выполнялась с большей скоростью, нужно сначала
посмотреть, можно ли заставить MySQL использовать индексы взамен
дополнительной фазы сортировки. Если это невозможно, то можно сделать
следующее:
Увеличить значение переменной
sort_buffer
.Увеличить значение переменной
record_rnd_buffer
.-
Изменить переменную
tmpdir
, чтобы она указывала на выделенный диск с большим количеством свободного пространства. Начиная с MySQL 4.1, вtmpdir
могут быть указаны несколько путей, разделенных двоеточием:
(точкой с запятой на Windows;
). Эти пути будут использованы в ротации.Внимание: Эти пути должны находится на разных физических дисках, не на разных разделах одного и того же диска.
MySQL по умолчанию сортирует все GROUP BY x,y[,...]
запросы так, как
если бы вы указали ORDER BY x,y[,...]
. MySQL будет оптимизировать любой
ORDER BY
как сказано выше, без всяких потерь производительности. Если,
как в некоторых случаях, вы не хотите иметь результат отсортированным, вы
можете указать ORDER BY NULL
:
INSERT INTO foo SELECT a,COUNT(*) FROM bar GROUP BY a ORDER BY NULL;