Странное поведение limit X offset Y в запросах

262

24 июня 2017, 11:23

Добрый день.

Есть таблица с двумя полями, одно из них ключ. Скрипт создания таблицы и заполнения можно взять здесь

Выполняю запрос, который сравнивает две порции таблицы для пагинации:

select
  *
from
  (
    select distinct id, created_at from aaa order by created_at desc limit 5 offset 0
  ) a,
  (
    select distinct id, created_at from aaa order by created_at desc limit 5 offset 5
  ) b
where a.id = b.id

Логично предположить, что не должно оказаться одинаковых записей в обоих подзапросах?

Однако mysql возвращает три таких записи, а Mariadb возвращает одну.

mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using EditLine wrapper mysql Ver 15.1 Distrib 10.2.6-MariaDB, for osx10.12 (x86_64) using readline 5.1

Если у таблицы удалить первичный ключ, тогда повторяющиеся записи исчезнут

Answer 1

Немного теории: Если порядок следования записей не определен однозначно, SQL имеет право применять любой порядок, который ему нравится. В данном случае вы просите SQL отсортировать данные по полю created_at. При этом в БД есть много записей с одинаковым created_at. Например, указанный order определяет такой порядок сортировки:

Дата                  ID
2017-06-09 15-08-26   4
2017-06-09 15-08-25   3
2017-06-09 15-08-25   2
2017-06-09 15-08-25   1

У трех записей дата создания одинакова, следовательно при сортировке по дате такой порядок так же полностью удовлетворяет условию:

Дата                  ID
2017-06-09 15-08-26   4
2017-06-09 15-08-25   2
2017-06-09 15-08-25   1
2017-06-09 15-08-25   3

А теперь рассмотрим план выполнения запроса (я задал limit 10):

+----+-------------+------------+------+---------------+-------------+---------+------+------+----------------+
| id | select_type | table      | type | possible_keys | key         | key_len | ref  | rows | Extra          |
+----+-------------+------------+------+---------------+-------------+---------+------+------+----------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL        | NULL    | NULL |   10 | NULL           |
|  1 | PRIMARY     | <derived3> | ref  | <auto_key0>   | <auto_key0> | 108     | a.id |    2 | NULL           |
|  3 | DERIVED     | aaa        | ALL  | NULL          | NULL        | NULL    | NULL | 3019 | Using filesort |
|  2 | DERIVED     | aaa        | ALL  | NULL          | NULL        | NULL    | NULL | 3019 | Using filesort |
+----+-------------+------------+------+---------------+-------------+---------+------+------+----------------+

Из него следует, что таблицу из первого подзапроса MySQL берет целиком из области данных и сортирует. По второму подзапросу он принимает решение подобрать подходящие записи по ID из первой таблицы, т.е. фактически заранее выполнить объединение a.id=b.id, таким образом данные с диска он получает не в том порядке как они лежат в области данных, а выдергивая по одной на основании первичного ключа. После чего и эту выборку он сортирует. Но так как изначальный порядок записей был другим, то сортировка в пределах одной даты создания выдает другой порядок следования id. После этого MySQL наконец применяет к полученным данным offset и limit. И фактически оказывается, что лимиты были применены к разным наборам данных (отсортированным в разном порядке), что и влечет за собой одинаковые id на выходе после применения лимита.

В случае отсутствия первичного ключа на таблице у оптимизатора не остается другого выбора как только получать таблицу целиком из области данных. Поэтому оба набора при применении limit оказываются одинаковы и запрос уже не находит одинаковых id.

Показанное в Вашем вопросе отлично демонстрирует, на сколько важно однозначно определять порядок сортировки при использовании операции limit. Добавление поля ID в предложение order by полностью устраняет проблему, SQL в таком случае гарантирует одинаковый порядок записей и следовательно срез получаемый лимитом.