Spec-Zone .ru
спецификации, руководства, описания, API
|
В некоторых случаях MySQL может использовать индексирование, чтобы удовлетворить ORDER
BY
пункт, не делая дополнительной сортировки.
Индексирование может также использоваться даже если ORDER BY
не соответствует
индексирование точно, пока все неиспользованные части индексирования и всего дополнительного ORDER BY
столбцы являются константами в WHERE
пункт.
Следующие запросы используют индексирование, чтобы решить ORDER BY
часть:
SELECT * FROM t1 ORDER BYkey_part1
,key_part2
,... ;SELECT * FROM t1 WHEREkey_part1
=constant
ORDER BYkey_part2
;SELECT * FROM t1 ORDER BYkey_part1
DESC,key_part2
DESC;SELECT * FROM t1 WHEREkey_part1
= 1 ORDER BYkey_part1
DESC,key_part2
DESC;SELECT * FROM t1 WHEREkey_part1
>constant
ORDER BYkey_part1
ASC;SELECT * FROM t1 WHEREkey_part1
<constant
ORDER BYkey_part1
DESC;SELECT * FROM t1 WHEREkey_part1
=constant1
ANDkey_part2
>constant2
ORDER BYkey_part2
;
В некоторых случаях MySQL не может использовать, индексирует, чтобы
решить ORDER BY
, хотя это все еще использует, индексирует, чтобы найти строки,
которые соответствуют WHERE
пункт. Эти случаи включают следующее:
Вы используете ORDER BY
на различных ключах:
SELECT * FROM t1 ORDER BYkey1
,key2
;
Вы используете ORDER BY
на непоследовательных частях
ключа:
SELECT * FROM t1 WHEREkey2
=constant
ORDER BYkey_part2
;
Вы смешиваетесь ASC
и DESC
:
SELECT * FROM t1 ORDER BYkey_part1
DESC,key_part2
ASC;
Ключ, используемый, чтобы выбрать строки, не является тем же самым как тем,
используемым в ORDER BY
:
SELECT * FROM t1 WHEREkey2
=constant
ORDER BYkey1
;
Вы используете ORDER BY
с выражением, которое включает
сроки кроме ключевого имени столбца:
SELECT * FROM t1 ORDER BY ABS(key
);SELECT * FROM t1 ORDER BY -key
;
Вы присоединяетесь ко многим таблицам, и столбцам в ORDER
BY
не все от первой непостоянной таблицы, которая используется, чтобы получить строки. (Это -
первая таблица в EXPLAIN
вывод, у которого нет a const
тип соединения.)
Вы имеете отличающийся ORDER BY
и GROUP BY
выражения.
Вы индексируете только префикс столбца, названного в ORDER
BY
пункт. В этом случае индексирование не может использоваться, чтобы полностью разрешить порядок
сортировки. Например, если у Вас есть a CHAR(20)
столбец, но индексируют только первые 10 байтов,
индексирование не может отличить значения мимо 10-ого байта и a filesort
будет необходим.
Тип таблицы индексирует используемый, не хранит строки в порядке. Например, это -
истина для a HASH
индексируйте в a MEMORY
таблица.
На доступность индексирования для того, чтобы сортировать можно влиять при помощи псевдонимов столбца.
Предположите что столбец t1.a
индексируется. В этом операторе имя столбца в списке
выборки a
. Это обращается к t1.a
, так для ссылки на
a
в ORDER BY
, индексирование может использоваться:
SELECT a FROM t1 ORDER BY a;
В этом операторе имя столбца в списке выборки также a
, но это - имя псевдонима. Это
обращается к ABS(a)
, так для ссылки на a
в ORDER BY
, индексирование не может использоваться:
SELECT ABS(a) AS a FROM t1 ORDER BY a;
В следующем операторе, ORDER BY
обращается к имени, которое не является именем
столбца в списке выборки. Но есть столбец в t1
именованный a
, так ORDER BY
использование, что, и индексирование
может использоваться. (Получающийся порядок сортировки может абсолютно отличаться от порядка на ABS(a)
, конечно.)
SELECT ABS(a) AS b FROM t1 ORDER BY a;
По умолчанию, виды MySQL все GROUP BY
запросы, как будто Вы определили col1
, col2
, ...ORDER BY
в запросе также. Если Вы включаете col1
, col2
, ...ORDER BY
пункт явно, который содержит тот же самый список столбцов, MySQL,
оптимизирует это далеко без любого штрафа скорости, хотя сортировка все еще происходит. Если запрос включает
GROUP BY
но Вы хотите избежать издержек сортировки результата, можно подавить
сортировку, определяя ORDER BY NULL
. Например:
INSERT INTO fooSELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;
Доверие неявному GROUP BY
сортировка в MySQL 5.7 осуждается. Чтобы достигнуть
определенного порядка сортировки сгруппированных результатов, предпочтительно использовать явное ORDER BY
пункт. GROUP BY
сортировка является
расширением MySQL, которое может измениться в будущем выпуске; например, чтобы позволить оптимизатору
упорядочить группировки любым способом это считает самым эффективным и избегать издержек сортировки.
С EXPLAIN
SELECT ... ORDER BY
, можно проверить, может ли MySQL использовать, индексирует, чтобы разрешить
запрос. Это не может, если Вы видите Using filesort
в Extra
столбец. См. Раздел
8.8.1, "Оптимизируя Запросы с EXPLAIN
". Филезорт использует
формат хранения строки фиксированной длины, подобный используемому MEMORY
механизм хранения. Типы переменной длины такой как VARCHAR
сохранены, используя фиксированную длину.
MySQL имеет два filesort
алгоритмы для сортировки и получения результатов. Исходный
метод использует только ORDER BY
столбцы. Измененный метод использует не только
ORDER BY
столбцы, но все столбцы используются в запросе.
Оптимизатор выбирает который filesort
алгоритм, чтобы использовать. Это обычно
использует измененный алгоритм кроме тех случаев, когда BLOB
или TEXT
столбцы включаются, когда это использует исходный алгоритм.
Оригинал filesort
алгоритм работает следующим образом:
Считайте все строки согласно ключу или табличным сканированием. Строки, которые не
соответствуют WHERE
пункт пропускается.
Для каждой строки сохраните пару значений в буфере (ключ сортировки и указатель
строки). Размер буфера является значением sort_buffer_size
системная переменная.
Когда буфер становится полным, выполняет qsort (quicksort) на этом и хранит результат во временном файле. Сохраните указатель на сортированный блок. (Если все пары, в которых вписываются буфер вида, никакой временный файл не создается.)
Повторите предыдущие шаги, пока все строки не были считаны.
Сделайте мультислияние до MERGEBUFF
(7) области к
одному блоку в другом временном файле. Повторитесь, пока все блоки от первого файла не находятся во
втором файле.
Повторите следующий, пока нет меньше чем MERGEBUFF2
(15) блоки уехали.
На последнем мультислиянии только указатель на строку (последняя часть ключа сортировки) пишется файлу результата.
Считайте строки в сортированном порядке при использовании указателей строки в файле
результата. Чтобы оптимизировать это, мы читаем в большом блоке указателей строки, сортируем их, и
используем их, чтобы считать строки в сортированном порядке в буфер строк. Размер буфера является
значением read_rnd_buffer_size
системная переменная. Код для этого шага
находится в sql/records.cc
исходный файл.
Одна проблема с этим подходом состоит в том, что он читает строки дважды: Одно время, оценивая WHERE
пункт, и снова после сортировки парных значений. И даже если к строкам получили
доступ последовательно в первый раз когда (например, если сканирование таблицы делается), во второй раз, когда к
ним получают доступ в произвольном порядке. (Ключи сортировки упорядочиваются, но позиции строки не.)
Измененный filesort
алгоритм включает оптимизацию так, что, он записывает не только
значение ключа сортировки и позицию строки, но также и столбцы, требуемые для запроса. Это избегает читать
строки дважды. Измененный filesort
алгоритм работает как это:
Считайте строки, которые соответствуют WHERE
пункт.
Для каждой строки запишите кортеж значений, состоящих из значения ключа сортировки и позиции строки, и также столбцов, требуемых для запроса.
Сортируйте кортежи значением ключа сортировки
Получите строки в сортированном порядке, но считайте необходимые столбцы непосредственно из сортированных кортежей, а не получая доступ к таблице во второй раз.
Используя измененный filesort
алгоритм, кортежи более длинны чем пары, используемые
в исходном методе, и меньше из них помещается в буфер вида (размером которого дают sort_buffer_size
). В результате для дополнительного ввода-вывода возможно
сделать измененный подход медленнее, не быстрее. Чтобы избежать замедления, оптимизация используется, только
если полный размер дополнительных столбцов в кортеже вида не превышает значение max_length_for_sort_data
системная переменная. (Признак установки значения
этой переменной слишком высоко является комбинацией высокого дискового действия и низкого действия ЦП.)
Для того медленных, запросов, для который filesort
не используется, попытайтесь
понизиться max_length_for_sort_data
к значению, которое является соответствующим, чтобы
инициировать a filesort
.
Если Вы хотите увеличиться ORDER BY
скорость, проверьте, можно ли добраться, MySQL,
чтобы использовать индексирует, а не дополнительная фаза сортировки. Если это не возможно, можно попробовать
следующие стратегии:
Увеличьте размер sort_buffer_size
переменная.
Увеличьте размер read_rnd_buffer_size
переменная.
Используйте меньше RAM на строку, объявляя столбцы, только столь же большие, как
они должны содержать значения, сохраненные в них. Например, CHAR(16)
лучше
чем CHAR(200)
если значения никогда не превышают 16 символов.
Изменение tmpdir
указать на выделенную файловую систему с большим количеством
свободного пространства. Кроме того, эта опция принимает несколько путей, которые используются круговым
способом, таким образом, можно использовать эту функцию, чтобы распространить загрузку через несколько
каталогов. Пути должны быть разделены символами двоеточия (":
") на Unix и символах точки с
запятой (";
") на Windows. Пути должны быть для каталогов в
файловых системах, которые располагаются на различных физических дисках,
не различных разделах на том же самом диске.
Если индексирование не используется для ORDER BY
но a LIMIT
пункт также присутствует, оптимизатор может быть в состоянии избегать
использования файла слияния и сортировать строки в памяти. Для получения дополнительной информации см. Раздел
8.2.1.3, "Оптимизируя LIMIT
Запросы".