8.2.1.17 LIMIT Query Optimization — MySQL 5.7

8.2.1.17 Оптимизация запросов LIMIT

Если вам нужно только определённое число строк из набора результатов, используйте в запросе предложение LIMIT, а не извлечение всего набора результатов и удаление лишних данных.

MySQL иногда оптимизирует запрос, содержащий предложение LIMIT row_count и не содержащий предложения HAVING:

Если вы выбираете всего несколько строк с LIMIT, MySQL в некоторых случаях использует индексы, когда обычно предпочитал бы полное сканирование таблицы.
Если вы объединяете LIMIT row_count с ORDER BY, MySQL прекращает сортировку, как только найдёт первые row_count строк отсортированного результата, а не сортирует весь результат. Если сортировка выполняется с использованием индекса, это очень быстро. Если необходимо выполнить filesort, выбираются все строки, которые соответствуют запросу без предложения LIMIT, и большинство или все из них сортируются, прежде чем найдутся первые row_count строк. После того, как начальные строки будут найдены, MySQL не сортирует оставшуюся часть набора результатов.

Одним из проявлений этого поведения является то, что запрос ORDER BY с предложением LIMIT и без него может возвращать строки в разном порядке, как описано позже в этом разделе.
Если вы объединяете LIMIT row_count с DISTINCT, MySQL прекращает выполнение, как только найдёт row_count уникальных строк.
В некоторых случаях запрос GROUP BY может быть обработан путём чтения индекса в порядке (или выполнением сортировки по индексу), а затем вычисления сводных данных до тех пор, пока значение индекса не изменится. В этом случае LIMIT row_count не вычисляет какие-либо ненужные значения GROUP BY.
Как только MySQL отправит требуемое количество строк клиенту, он прерывает запрос, если вы не используете SQL_CALC_FOUND_ROWS. В этом случае количество строк можно получить с помощью SELECT FOUND_ROWS(). См. Раздел 12.15, “Функции информации”.
LIMIT 0 быстро возвращает пустой набор. Это может быть полезно для проверки правильности запроса. Его также можно использовать для получения типов столбцов результата в приложениях, которые используют API MySQL, предоставляющий метаданные набора результатов. С помощью программы командной строки клиента MySQL mysql вы можете использовать опцию --column-type-info для отображения типов столбцов результата.
Если сервер использует временные таблицы для обработки запроса, он использует предложение LIMIT row_count для расчёта требуемого объёма памяти.
Если индекс не используется для ORDER BY, но также присутствует предложение LIMIT, оптимизатор может избежать использования объединённого файла и отсортировать строки в памяти с помощью операции filesort в памяти.

Если несколько строк имеют одинаковые значения в столбцах ORDER BY, сервер свободен возвращать эти строки в любом порядке и может делать это по-разному в зависимости от общего плана выполнения. Другими словами, порядок сортировки этих строк не гарантирован для столбцов без упорядочения.

Одним из факторов, влияющих на план выполнения, является LIMIT, поэтому запрос ORDER BY с предложением LIMIT и без него может возвращать строки в разном порядке. Рассмотрим этот запрос, который отсортирован по столбцу category, но не гарантирует порядок для столбцов id и rating:

mysql> SELECT * FROM ratings ORDER BY category;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

Включение LIMIT может повлиять на порядок строк внутри каждого значения category. Например, вот допустимый результат запроса:

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  4 |        2 |    3.5 |
|  3 |        2 |    3.7 |
|  6 |        2 |    3.5 |
+----+----------+--------+

В каждом случае строки сортируются по столбцу ORDER BY, что и требуется стандартом SQL.

Если важно гарантировать одинаковый порядок строк с предложением LIMIT и без него, включите дополнительные столбцы в предложение ORDER BY, чтобы сделать порядок детерминированным. Например, если значения id уникальны, вы можете заставить строки для данного значения category появляться в порядке id, отсортировав их так:

mysql> SELECT * FROM ratings ORDER BY category, id;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
+----+----------+--------+

Для запроса с предложением ORDER BY или GROUP BY и предложением LIMIT оптимизатор по умолчанию пытается выбрать упорядоченный индекс, если это ускорит выполнение запроса. До MySQL 5.7.33 такого поведения изменить нельзя было, даже в тех случаях, когда использование другой оптимизации могло быть быстрее. Начиная с MySQL 5.7.33, можно отключить эту оптимизацию, установив флаг optimizer_switch переменной системы prefer_ordering_index в значение off.

Пример: Сначала создадим и заполним таблицу t, как показано здесь:

# Create and populate a table t:

mysql> CREATE TABLE t (
    ->     id1 BIGINT NOT NULL,
    ->     id2 BIGINT NOT NULL,
    ->     c1 VARCHAR(50) NOT NULL,
    ->     c2 VARCHAR(50) NOT NULL,
    ->  PRIMARY KEY (id1),
    ->  INDEX i (id2, c1)
    -> );

# [Insert some rows into table t - not shown]

Проверьте, что флаг prefer_ordering_index включён:

mysql> SELECT @@optimizer_switch LIKE '%prefer_ordering_index=on%';
+------------------------------------------------------+
| @@optimizer_switch LIKE '%prefer_ordering_index=on%' |
+------------------------------------------------------+
|                                                    1 |
+------------------------------------------------------+

Поскольку следующий запрос имеет предложение LIMIT, мы ожидаем, что он будет использовать упорядоченный индекс, если это возможно. В этом случае, как видно из вывода EXPLAIN, он использует первичный ключ таблицы.

mysql> EXPLAIN SELECT c2 FROM t
    ->     WHERE id2 > 3
    ->     ORDER BY id1 ASC LIMIT 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: index
possible_keys: i
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 2
     filtered: 70.00
        Extra: Using where

Теперь отключим флаг prefer_ordering_index и повторно выполним тот же запрос; на этот раз он использует индекс i (который включает столбец id2, используемый в предложении WHERE), и filesort:

mysql> SET optimizer_switch = "prefer_ordering_index=off";

mysql> EXPLAIN SELECT c2 FROM t
    ->     WHERE id2 > 3
    ->     ORDER BY id1 ASC LIMIT 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 8
          ref: NULL
         rows: 14
     filtered: 100.00
        Extra: Using index condition; Using filesort

См. также Раздел 8.9.2, “Переключаемые оптимизации”.