10.2.1.19 LIMIT Query Optimization — MySQL 8.4

10.2.1.19 Оптимизация запросов 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(). См. Раздел 14.15, «Функции информации».
LIMIT 0 быстро возвращает пустой набор. Это может быть полезно для проверки корректности запроса. Также это может быть использовано для получения типов столбцов результатов в приложениях, использующих API 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, оптимизатор по умолчанию пытается выбрать упорядоченный индекс, если это ускорит выполнение запроса. В случаях, когда использование другой оптимизации может быть быстрее, можно отключить эту оптимизацию, установив флаг 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

См. также Раздел 10.9.2, «Переключаемые оптимизации».