Комментарии 30
Как оказалось боян.
Ну, тут не столько боян, просто это весьма известная и насущная проблема, и, соответственно, решений в инете кучу можно найти.
Вот ещё, например: habrahabr.ru/post/44608/
Вот ещё, например: habrahabr.ru/post/44608/
В боянах тоже польза есть. Я вот не знал, и мне может пригодиться. Спасибо.
Я слышал, что Oracle && MSSQL сами такие запросы оптимизируют, и проблема есть только в MySQL. Насколько это правда?
P.S. ну и, хотелось бы отметить, что такие запросы обычно — результат неверной разработки пользовательской модели… Ведь что такое LIMIT 100000, 10? это 10000-ная страница выборки, представьте как надо пользователя замучить, чтобы он что-то искал на 10000-ной странице
P.S. ну и, хотелось бы отметить, что такие запросы обычно — результат неверной разработки пользовательской модели… Ведь что такое LIMIT 100000, 10? это 10000-ная страница выборки, представьте как надо пользователя замучить, чтобы он что-то искал на 10000-ной странице
Сокровища пиратов на 10001 странице, но для этого нужно пролистать все с самого начала! ;)
Например, самые новые сообщения в определенной теме форума — на последней странице. А их может быть и 100, и 1000, и, кто знает, может и 10000.
возможно, есть смысл менять сортировку(при этом, конечно, необходимы некоторые манипуляции с 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 записей)
(если кто-то подумал что автор предложил 100% решение проблемы — это не совсем так, попробуйте запустить SELECT id FROM table LIMIT 10000000,40 на базе с 20 000 000 записей)
НЛО прилетело и опубликовало эту надпись здесь
Oracle rownum считает до сортировки, т.е. там нужно писать так:
Mysql в этом плане гораздо удобней.
Насчет оптимизации не знаю, нет возможности сейчас проверить план.
select * from (select c.* from comments c ORDER BY id) v WHERE rownum BETWEEN 10000 and 10010
Mysql в этом плане гораздо удобней.
Насчет оптимизации не знаю, нет возможности сейчас проверить план.
Во многих случаях MySQL способен использовать индексы для сортировок и для LIMIT.
Результат же будет отличаться если в запросе есть условие (и не только).
Например, если в оригинальном запросе первая запись результата найдётся на миллионной записи БД.
То во втором запросе она вообще не найдётся, т.к. её нет среди первых 100 000.
Например, если в оригинальном запросе первая запись результата найдётся на миллионной записи БД.
То во втором запросе она вообще не найдётся, т.к. её нет среди первых 100 000.
devoluk.com/mysql-limit-offset-performance.html
Не согласен с тем, что результаты запроса будут отличаться
Не согласен с тем, что результаты запроса будут отличаться
Можете объяснить в чём конкретно ошибка в моих рассуждениях? В обоих статьях нет реальных примеров с where.
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)
перенесите WHERE во внутренний запрос и заработает… По поводу бенчмарков — лично тестировал — ускорение при разбивке на два запроса существенное.
Судя по этой статье explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/ Вы правы, при этом ускорении происходит не из-за того что внутренний запрос простой и планировщику легче понять какой индекс использовать, а из-за того что mysql меньше обращается в саму таблицу, обходится только индексами (что не очевидно для меня из данного поста), так же ускорение будет только если условие во внутреннем запросе можно вычислить только с использованием индексов.
А можно Вас попросить произвести запрос с явным указанием колонок, то есть использовать не *, а явное перечисление, так как я думаю, что вы заставляете MySQL производить сначала полное перечисление всех записей для выделение столбцов, а потом уже непосредственно выборку.
мм, если комментов очень много, наверное, стоит создать таблицу вида
[pk] theme_id
[pk] page_id
[pk+uq] comment_id
по запросу в нее получать список ID всем комментов, которые нужны…
так как таки читают комменты обычно намного больше, чем удаляют. да и построение пагинатора не потребует каунтов и беспокойства самой таблицы постов
[pk] theme_id
[pk] page_id
[pk+uq] comment_id
по запросу в нее получать список ID всем комментов, которые нужны…
так как таки читают комменты обычно намного больше, чем удаляют. да и построение пагинатора не потребует каунтов и беспокойства самой таблицы постов
Проблема заключается в том, что используя 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.
Решал такую проблему для сильно более плохого случая.
Решение простое — записываем номера страниц в комменты, а потом выбираем данные по этому ключу.
По факту — просто храним некую метаинформацию о топике с номерами первых ID на странице N, или чуть чуть сложнее.
Четко, красиво, стабильно.
Решение простое — записываем номера страниц в комменты, а потом выбираем данные по этому ключу.
По факту — просто храним некую метаинформацию о топике с номерами первых ID на странице N, или чуть чуть сложнее.
Четко, красиво, стабильно.
а как быть в случае удаления коммента? шерстить все последующие на предмет обновления номера страницы? Есть какое-то решение кроме запрета удаления по прошествию Х минут?
Усложняем задачу — у меня древообразные комментарии.
Посему добавляем команды на «сдвиг» и «массовые» апдейты бд.
Фактически это нормально не решается средствами SQL, как минимум хранимками, так как требует анализа данных и кучи запросов как на выборку, так и на апдейт.
Просто есть ряд задач, которые не надо решать «адекамическим» путем — можно и внешних мозгов на другом языке дописать.
Посему добавляем команды на «сдвиг» и «массовые» апдейты бд.
Фактически это нормально не решается средствами SQL, как минимум хранимками, так как требует анализа данных и кучи запросов как на выборку, так и на апдейт.
Просто есть ряд задач, которые не надо решать «адекамическим» путем — можно и внешних мозгов на другом языке дописать.
бэкенд-скриптом пересчитывать…
>а как быть в случае удаления коммента?
Пересчёт номеров страниц одноразовая операция и выполняется относительно быстро. Удаление — событие нечастое, можно подождать и с пересчётом.
У меня на форуме бывает под 15 тыс. ответов в теме. До 600 страниц на топик. И это при том, что я стараюсь жёстко разделять тему на новые темы в случае её роста, так бы и многие тысячи страниц были у некоторых (а ля iXBT-style, когда найти что-то потом нереально). При реальном использовании основная масса народа постоянно пасётся именно на последних страницах, т.е. львиная масса запросов именно в духе… LIMIT 10000, 25. Когда сидит хотя бы человек 200 в онлайне, нагрузка получается очень большая. Особенно, учитывая то, что сортировка не по одному ID, а по дате сообщения, приоритету сортировки, с учётом пометки «удалено» (физическое удаление в моём случае не практикуется) и ещё что-то. Индексы получаются сложные и большие. Пришлось вводит административные ограничения «200 страниц темы — создавайте новую тему» и т.п. Параметр «страницы темы» не вводил, так как тоже опасался проблемы пересчёта. Потом составил не особенно сложный запрос пересчёта номеров страниц и ввёл соответствующую сущность. Выборки стали выполняться мгновенно.
Пересчёт номеров страниц одноразовая операция и выполняется относительно быстро. Удаление — событие нечастое, можно подождать и с пересчётом.
У меня на форуме бывает под 15 тыс. ответов в теме. До 600 страниц на топик. И это при том, что я стараюсь жёстко разделять тему на новые темы в случае её роста, так бы и многие тысячи страниц были у некоторых (а ля iXBT-style, когда найти что-то потом нереально). При реальном использовании основная масса народа постоянно пасётся именно на последних страницах, т.е. львиная масса запросов именно в духе… LIMIT 10000, 25. Когда сидит хотя бы человек 200 в онлайне, нагрузка получается очень большая. Особенно, учитывая то, что сортировка не по одному ID, а по дате сообщения, приоритету сортировки, с учётом пометки «удалено» (физическое удаление в моём случае не практикуется) и ещё что-то. Индексы получаются сложные и большие. Пришлось вводит административные ограничения «200 страниц темы — создавайте новую тему» и т.п. Параметр «страницы темы» не вводил, так как тоже опасался проблемы пересчёта. Потом составил не особенно сложный запрос пересчёта номеров страниц и ввёл соответствующую сущность. Выборки стали выполняться мгновенно.
Я в некоторых местах перестал использовать limit-offset. Дело в том, что он не подходит для часто обновляемых данных. О своём подходе в блоге написал — plutov.by/post/cursor_pagination
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Оптимизируем LIMIT offset