Объясните поведение MySQL, связанное с ORDER BY и LIMIT

Question

[romashka_sky]

В таблице `products` поле `position` для всех строк равно 0. Общее количество записей - 2694. При выборке строк из БД с использованием limit и offset где-то после 837-й строки наблюдается "реверс" в таком смысле, что выборка первых 100(например) записей пересекается с выборкой последних 100

Два запроса:

SELECT * 
FROM  `products` 
WHERE 1 
ORDER BY  `position` DESC 
LIMIT 800 , 38

и

SELECT * 
FROM  `products` 
WHERE 1 
ORDER BY  `position` DESC 
LIMIT 800 , 37

отличаются первыми 37 строками, хотя по логике(по интуиции) не должны.

Проясните, пожалуйста, с чем связано такое поведение?

Answer 1

[Finesse]

Одно из основных положений теории баз данных гласит, что если не задана сортировка, то данные могут выдаваться в абсолютно любом порядке. У вас сортировка идёт по полю, для которого значение всех записей совпадает, и данные не сортируются.

Чтобы этого избежать, добавьте ещё одну сортировку по первичному ключу или любому другому полю с уникальными значениями, например (не знаю названия ключевого поля в вашей таблице, замените `id` на него):

SELECT * 
FROM  `products`
ORDER BY `position` DESC, `id` ASC
LIMIT 800 , 38

Answer 2

[Владлен Грачев]

В таблице `products` поле `position` для всех строк равно 0

Вы сортируете по одинаковому полю? А потом ожидаете какого-то «логичного» поведения? Если все действительно так, как вы говорите, то сортировка в запросе отсутствует по сути. Сортировка по-умолчанию в общем случае не определена, поэтому даже случайно отсортированные выборки, меняющиеся от запроса к запросу, могут считаться нормальным поведением.

Comments

[romashka_sky]

Просто я считал, что MySQL должна сначала провести выборку по условию(WHERE), потом сортировать данные(ORDER), потом вырезать(OFFSET, LIMIT). На деле же видно, что это не так или результат сортировки ЗАВИСИТ от параметров LIMIT и OFFSET

[Владлен Грачев]

Другие параметры пробовались? LIMIT 801 OFFSET 36, LIMIT 700 OFFSET 40, LIMIT 900 OFFSET 40 и т.д. Зависимость точно есть? Вполне вероятно, что порядок-таки «случайный».
В любом случае, неопределенность порядка результатов в данной ситуации является нормой.

[romashka_sky]

Да, пробовал. Все выборки, которые должны содержать запись с id>838 (LIMIT 900 OFFSET 40) начинаются с id=1; для остальных выборок (LIMIT 801 OFFSET 36, LIMIT 700 OFFSET 40) - начинаются с id=2694

[romashka_sky]

Видимо, всё дело в некой "фрагментации" базы, потому что с копией данной таблицы запросы работают адекватно.

Answer 3

[svd71]

А что вернет

SELECT *
FROM `products`
WHERE 1
ORDER BY `position` DESC
LIMIT 800  OFFSET 38

и

SELECT *
FROM `products`
WHERE 1
ORDER BY `position` DESC
LIMIT 800 OFFSET 37

?

Comments

[romashka_sky]

поведение аналогичное, в первом случае данные берутся с "начала", во втором - с "конца"

Answer 4

[CloudMonster]

Условие правильно пишите!

Comments

[romashka_sky]

Да правильно же написано:)

[Finesse]

Строку WHERE 1 можно убрать вообще, результат от этого не изменится