В этом запросе индекс по (key_part1, key_part2) позволяет оптимизатору избежать сортировки:

SELECT * FROM t1
  ORDER BY key_part1, key_part2;

Однако, запрос использует SELECT *, который может выбрать больше столбцов, чем key_part1 и key_part2. В этом случае сканирование всего индекса и поиск строк таблицы для нахождения столбцов, не входящих в индекс, может быть дороже, чем сканирование таблицы и сортировка результатов. Если это так, оптимизатор, вероятно, не использует индекс. Если SELECT * выбирает только столбцы индекса, индекс используется и сортировка избегается.

Если t1 — это таблица InnoDB, первичный ключ таблицы неявно входит в индекс, и индекс может быть использован для решения ORDER BY для этого запроса:

SELECT pk, key_part1, key_part2 FROM t1
  ORDER BY key_part1, key_part2;

В этом запросе key_part1 является константой, поэтому все строки, доступные через индекс, находятся в порядке key_part2, и индекс по (key_part1, key_part2) избегает сортировки, если условие WHERE достаточно селективно, чтобы сделать сканирование диапазона по индексу дешевле, чем сканирование таблицы:

SELECT * FROM t1
  WHERE key_part1 = constant
  ORDER BY key_part2;

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

SELECT * FROM t1
  ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1
  WHERE key_part1 = constant
  ORDER BY key_part2 DESC;

Два столбца в ORDER BY могут сортироваться в одном направлении (оба ASC или оба DESC) или в противоположных направлениях (один ASC, другой DESC). Условием использования индекса является то, что индекс должен иметь ту же однородность, но не обязательно то же фактическое направление.

Если запрос смешивает ASC и DESC, оптимизатор может использовать индекс по столбцам, если индекс также использует соответствующие смешанные возрастающие и убывающие столбцы:

SELECT * FROM t1
  ORDER BY key_part1 DESC, key_part2 ASC;

Оптимизатор может использовать индекс по (key_part1, key_part2), если key_part1 убывающий, а key_part2 возрастающий. Он также может использовать индекс по этим столбцам (с обратным сканированием), если key_part1 возрастающий, а key_part2 убывающий. См. раздел 10.3.13 «Убывающие индексы».

В следующих двух запросах key_part1 сравнивается с константой. Индекс используется, если условие WHERE достаточно селективно, чтобы сделать сканирование диапазона по индексу дешевле, чем сканирование таблицы:

SELECT * FROM t1
  WHERE key_part1 > constant
  ORDER BY key_part1 ASC;

SELECT * FROM t1
  WHERE key_part1 < constant
  ORDER BY key_part1 DESC;

В следующем запросе ORDER BY не называет key_part1, но все выбранные строки имеют постоянное значение key_part1, поэтому индекс все еще может быть использован:

SELECT * FROM t1
  WHERE key_part1 = constant1 AND key_part2 > constant2
  ORDER BY key_part2;

Запрос использует ORDER BY по разным индексам:

SELECT * FROM t1 ORDER BY key1, key2;

Запрос использует ORDER BY по несмежным частям индекса:

SELECT * FROM t1 WHERE key2=constant ORDER BY key1_part1, key1_part3;

Индекс, используемый для извлечения строк, отличается от того, который используется в ORDER BY:

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

Запрос использует ORDER BY с выражением, которое включает термины, помимо имени столбца индекса:

SELECT * FROM t1 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key;

Запрос объединяет много таблиц, и столбцы в ORDER BY не все из первой неконстантной таблицы, которая используется для получения строк. (Это первая таблица в выводе EXPLAIN, у которой нет типа соединения const).

В запросе есть разные ORDER BY и GROUP BY выражения.

Существует индекс только на префиксе столбца, указанного в условии ORDER BY. В этом случае индекс не может полностью разрешить порядок сортировки. Например, если индексированы только первые 10 байтов столбца CHAR(20), индекс не может различать значения после 10-го байта, и требуется filesort.

Индекс не хранит строки в порядке. Например, это верно для индекса HASH в таблице MEMORY.

Увеличьте значение переменной sort_buffer_size. В идеале, значение должно быть достаточно большим, чтобы весь результирующий набор поместился в буфер сортировки (чтобы избежать записи на диск и объединения проходов).

Учитывайте, что размер значений столбцов, хранящихся в буфере сортировки, зависит от значения переменной max_sort_length. Например, если кортежи хранят значения длинных строковых столбцов, и вы увеличиваете значение max_sort_length, размер кортежей буфера сортировки также увеличивается и может потребовать увеличения значения sort_buffer_size.

Для отслеживания количества объединительных проходов (для объединения временных файлов) проверьте переменную состояния Sort_merge_passes.

Увеличьте значение переменной read_rnd_buffer_size, чтобы считывать больше строк за раз.

Измените переменную tmpdir так, чтобы она указывала на файловую систему с большим количеством свободного места. Значение переменной может содержать несколько путей, которые используются по круговому принципу; вы можете использовать эту функцию, чтобы распределить нагрузку по нескольким каталогам. Разделяйте пути двоеточием (:) в Unix и точкой с запятой (;) в Windows. Пути должны указывать на каталоги в файловых системах, расположенных на разных физических дисках, а не на разных разделах одного диска.

Если столбец Extra вывода EXPLAIN не содержит Using filesort, индекс используется, и операция filesort не выполняется.

Если столбец Extra вывода EXPLAIN содержит Using filesort, индекс не используется, и выполняется операция filesort.

<sort_key, rowid>: Это указывает, что кортежи буфера сортировки являются парами, которые содержат значение ключа сортировки и идентификатор строки исходной строки таблицы. Кортежи сортируются по значению ключа сортировки, а идентификатор строки используется для считывания строки из таблицы.

<sort_key, additional_fields>: Это указывает, что кортежи буфера сортировки содержат значение ключа сортировки и столбцы, на которые ссылается запрос. Кортежи сортируются по значению ключа сортировки, а значения столбцов считываются непосредственно из кортежа.

<sort_key, packed_additional_fields>: Подобно предыдущему варианту, но дополнительные столбцы упакованы плотно друг к другу вместо использования кодирования с фиксированной длиной.