Shannon Mar 29 2014 at 16:45

Оптимизируем LIMIT offset

2 min

84K

MySQL *

+45

Comments 30

SkazochNik Mar 29 2014 at 17:05

Как оказалось боян.

barker Mar 29 2014 at 17:08

Ну, тут не столько боян, просто это весьма известная и насущная проблема, и, соответственно, решений в инете кучу можно найти.
Вот ещё, например: habrahabr.ru/post/44608/

tagir_valeev Mar 29 2014 at 17:28

В боянах тоже польза есть. Я вот не знал, и мне может пригодиться. Спасибо.

Vertexodessa Mar 29 2014 at 17:31

Я слышал, что Oracle && MSSQL сами такие запросы оптимизируют, и проблема есть только в MySQL. Насколько это правда?
P.S. ну и, хотелось бы отметить, что такие запросы обычно — результат неверной разработки пользовательской модели… Ведь что такое LIMIT 100000, 10? это 10000-ная страница выборки, представьте как надо пользователя замучить, чтобы он что-то искал на 10000-ной странице

ingrysty Mar 29 2014 at 19:42

Сокровища пиратов на 10001 странице, но для этого нужно пролистать все с самого начала! ;)

lenar Mar 29 2014 at 19:58

Например, самые новые сообщения в определенной теме форума — на последней странице. А их может быть и 100, и 1000, и, кто знает, может и 10000.

Vertexodessa Mar 29 2014 at 20:41

возможно, есть смысл менять сортировку(при этом, конечно, необходимы некоторые манипуляции с offset)… Или привязаться к другим полям, предварительно их проиндексировав, если есть такая возможность. (SELECT id FROM table WHERE date < '01-01-2014' AND date > '01-03-2014')

(если кто-то подумал что автор предложил 100% решение проблемы — это не совсем так, попробуйте запустить SELECT id FROM table LIMIT 10000000,40 на базе с 20 000 000 записей)

UFO landed and left these words here

macik_spb Mar 31 2014 at 00:11

То рискует не найти нужной ему информации, т.к. в темах с таким количеством комментариев они быстро обновляются и «уходят» вниз.

pihel Mar 30 2014 at 11:39

Oracle rownum считает до сортировки, т.е. там нужно писать так:

select * from (select c.* from comments c ORDER BY id) v WHERE rownum BETWEEN 10000 and 10010

Mysql в этом плане гораздо удобней.
Насчет оптимизации не знаю, нет возможности сейчас проверить план.

akzhan Mar 29 2014 at 19:57

Во многих случаях MySQL способен использовать индексы для сортировок и для LIMIT.

Vertexodessa Mar 29 2014 at 20:29

увы, индексы в данном случае почему-то не сильно помогают… Не далее, чем неделю назад столкнулся с этой проблемой… Решал сходным методом с описаным в статье.

vsespb Mar 29 2014 at 19:58

Результат же будет отличаться если в запросе есть условие (и не только).
Например, если в оригинальном запросе первая запись результата найдётся на миллионной записи БД.
То во втором запросе она вообще не найдётся, т.к. её нет среди первых 100 000.

Vertexodessa Mar 29 2014 at 20:50

devoluk.com/mysql-limit-offset-performance.html
Не согласен с тем, что результаты запроса будут отличаться

vsespb Mar 29 2014 at 21:09

Можете объяснить в чём конкретно ошибка в моих рассуждениях? В обоих статьях нет реальных примеров с where.

UFO landed and left these words here

Vertexodessa Mar 30 2014 at 08:08

обьясню как оно должно выглядеть при наличии WHERE
первый запрос:
SELECT id FROM table WHERE date… LIMIT…
второй запрос:
JOIN SELECT * FROM TABLE WHERE id IN…

vsespb Mar 30 2014 at 07:27

create table test_table ( id int(11), f int(11), key f(f), primary key (id));
insert into test_table set id=1, f = 1;
insert into test_table set id=2, f = 1;
insert into test_table set id=3, f = 1;
insert into test_table set id=4, f = 2;
insert into test_table set id=5, f = 2;

SELECT * FROM test_table WHERE f = 2 ORDER BY id LIMIT 1, 1;

(выдаёт id=5)

SELECT * FROM test_table JOIN (SELECT id FROM test_table ORDER BY id LIMIT 1, 1) as b ON b.id = test_table.id WHERE f = 2 ;

(WHERE только во внешнем запросе: ничего не выдаёт)

Если where будети во внутреннем запросе — бенчмарки будут другие, думаю. К тому-же вместо where может быть JOIN (не LEFT JOIN)

Vertexodessa Mar 30 2014 at 08:11

перенесите WHERE во внутренний запрос и заработает… По поводу бенчмарков — лично тестировал — ускорение при разбивке на два запроса существенное.

vsespb Mar 30 2014 at 08:31

Судя по этой статье explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/ Вы правы, при этом ускорении происходит не из-за того что внутренний запрос простой и планировщику легче понять какой индекс использовать, а из-за того что mysql меньше обращается в саму таблицу, обходится только индексами (что не очевидно для меня из данного поста), так же ускорение будет только если условие во внутреннем запросе можно вычислить только с использованием индексов.

vit1251 Mar 30 2014 at 10:28

А можно Вас попросить произвести запрос с явным указанием колонок, то есть использовать не *, а явное перечисление, так как я думаю, что вы заставляете MySQL производить сначала полное перечисление всех записей для выделение столбцов, а потом уже непосредственно выборку.

VladimirAndreev Mar 30 2014 at 11:38

мм, если комментов очень много, наверное, стоит создать таблицу вида

[pk] theme_id
[pk] page_id
[pk+uq] comment_id

по запросу в нее получать список ID всем комментов, которые нужны…
так как таки читают комменты обычно намного больше, чем удаляют. да и построение пагинатора не потребует каунтов и беспокойства самой таблицы постов

bat Mar 30 2014 at 14:50

Проблема заключается в том, что используя LIMIT 100000, 30 — mysql вначале пройдется по первым 100000 записям и только потом выберет нужные 30.

Не совсем так. В общем случае, будут выбраны все записи, попадающие под условие where (тут его нет), отсортированы, затем будут отброшены первые 100 тыс записей и выданы следующие 30. Скорее всего, в данном случае сортировки не будет, так как сортировка по индексному полю, но все равно в пустую будут прочитаны 100тыс записей и для них будут выполнены соединения, отсюда и линейный рост времени выполнения.

Избежать этого достаточно просто

Да просто, сортировка и лимит будут выполнены только за счет индекса, но это применимо только к этому запросу. Мне он кажется весьма странным — постраничная лента всех комментариев в базе (мне не понятно зачем это). Если в этот запрос добавить доп условие, например, ограничение по посту, то указанный хак перестанет быть таким эффективным, поскольку в запросе вида
select id from comments where post_id = 123 order by id limit 10000,30
индекс может быть использован либо для сортировки либо ограничения выборки по post_id.

kashey Mar 30 2014 at 16:23

Решал такую проблему для сильно более плохого случая.
Решение простое — записываем номера страниц в комменты, а потом выбираем данные по этому ключу.
По факту — просто храним некую метаинформацию о топике с номерами первых ID на странице N, или чуть чуть сложнее.
Четко, красиво, стабильно.

Vertexodessa Mar 31 2014 at 07:48

а как быть в случае удаления коммента? шерстить все последующие на предмет обновления номера страницы? Есть какое-то решение кроме запрета удаления по прошествию Х минут?

kashey Mar 31 2014 at 07:56

Усложняем задачу — у меня древообразные комментарии.
Посему добавляем команды на «сдвиг» и «массовые» апдейты бд.
Фактически это нормально не решается средствами SQL, как минимум хранимками, так как требует анализа данных и кучи запросов как на выборку, так и на апдейт.
Просто есть ряд задач, которые не надо решать «адекамическим» путем — можно и внешних мозгов на другом языке дописать.

VladimirAndreev Mar 31 2014 at 18:20

бэкенд-скриптом пересчитывать…

Bal Apr 2 2014 at 08:19

>а как быть в случае удаления коммента?

Пересчёт номеров страниц одноразовая операция и выполняется относительно быстро. Удаление — событие нечастое, можно подождать и с пересчётом.

У меня на форуме бывает под 15 тыс. ответов в теме. До 600 страниц на топик. И это при том, что я стараюсь жёстко разделять тему на новые темы в случае её роста, так бы и многие тысячи страниц были у некоторых (а ля iXBT-style, когда найти что-то потом нереально). При реальном использовании основная масса народа постоянно пасётся именно на последних страницах, т.е. львиная масса запросов именно в духе… LIMIT 10000, 25. Когда сидит хотя бы человек 200 в онлайне, нагрузка получается очень большая. Особенно, учитывая то, что сортировка не по одному ID, а по дате сообщения, приоритету сортировки, с учётом пометки «удалено» (физическое удаление в моём случае не практикуется) и ещё что-то. Индексы получаются сложные и большие. Пришлось вводит административные ограничения «200 страниц темы — создавайте новую тему» и т.п. Параметр «страницы темы» не вводил, так как тоже опасался проблемы пересчёта. Потом составил не особенно сложный запрос пересчёта номеров страниц и ввёл соответствующую сущность. Выборки стали выполняться мгновенно.

plutov Jul 22 2014 at 13:25

Я в некоторых местах перестал использовать limit-offset. Дело в том, что он не подходит для часто обновляемых данных. О своём подходе в блоге написал — plutov.by/post/cursor_pagination