Пользователь
0,0
рейтинг
29 марта 2014 в 20:45

Разработка → Оптимизируем LIMIT offset

Везде, где используется LIMIT offset для больших таблиц, рано или поздно начинаются тормоза. Запросы вида

SELECT * FROM test_table ORDER BY id LIMIT 100000, 30

могут выполнятся очень долго. Например, в моем случае, на одном из сайтов кол-во комментариев перевалило за 200к и постраничная навигация по комментариям начала ощутимо тормозить, а в mysql-slow.log все чаще стали попадать запросы с временем выполнения 3-5сек.

Проблема заключается в том, что используя LIMIT 100000, 30 — mysql вначале пройдется по первым 100000 записям и только потом выберет нужные 30. Избежать этого достаточно просто, достаточно использовать подзапрос вида, который в общем случае выглядит так:
SELECT * FROM test_table JOIN (SELECT id FROM test_table ORDER BY id LIMIT 100000, 30) as b ON b.id = test_table.id

Давайте рассмотрим конкретный пример. В моем случае используется движок DLE и в нем запрос выглядит следующим образом:
SELECT dle_comments.*... FROM dle_comments LEFT JOIN dle_post ON dle_comments.post_id=dle_post.id LEFT JOIN dle_users ON dle_comments.user_id=dle_users.user_id  ORDER BY id desc LIMIT 101000,30

Исправленный запрос выглядит так:
SELECT dle_comments.*... FROM dle_comments LEFT JOIN dle_post ON dle_comments.post_id=dle_post.id LEFT JOIN dle_users ON dle_comments.user_id=dle_users.user_id JOIN (select id FROM dle_comments ORDER BY id desc LIMIT 101000,30 ) as t ON t.id = dle_comments.id

На графике можно увидеть результат такой замены:


Как видно, с использованием JOIN производительность сохраняется на нужном уровне не зависимо от того как далеко забрался пользователь в дебри сайта используя постраничную навигацию.

PS. Фикс для DLE для комментариев (аналогичным образом можно сделать для всех навигаций). В файле comments.class.php
найти
$sql_result = $this->db->query(  $this->query . " LIMIT " . $this->cstart . "," . $this->comments_per_pages );

заменить эту строчку на:
if( $_GET['do'] == "lastcomments" )
    $sql_result = $this->db->query( str_replace("ORDER BY id desc", "JOIN (select id FROM " . PREFIX . "_comments ORDER BY id desc" . " LIMIT " . $this-else
    $sql_result = $this->db->query(  $this->query . " LIMIT " . $this->cstart . "," . $this->comments_per_pages );
>cstart . "," . $this->comments_per_pages .") as t ON t.id = " . PREFIX . "_comments.id",$this->query) );
@Shannon
карма
20,7
рейтинг 0,0
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Реклама

Самое читаемое Разработка

Комментарии (30)

  • +1
    Как оказалось боян.
    • +4
      Ну, тут не столько боян, просто это весьма известная и насущная проблема, и, соответственно, решений в инете кучу можно найти.
      Вот ещё, например: habrahabr.ru/post/44608/
    • +12
      В боянах тоже польза есть. Я вот не знал, и мне может пригодиться. Спасибо.
  • +9
    Я слышал, что Oracle && MSSQL сами такие запросы оптимизируют, и проблема есть только в MySQL. Насколько это правда?
    P.S. ну и, хотелось бы отметить, что такие запросы обычно — результат неверной разработки пользовательской модели… Ведь что такое LIMIT 100000, 10? это 10000-ная страница выборки, представьте как надо пользователя замучить, чтобы он что-то искал на 10000-ной странице
    • 0
      Сокровища пиратов на 10001 странице, но для этого нужно пролистать все с самого начала! ;)
    • +1
      Например, самые новые сообщения в определенной теме форума — на последней странице. А их может быть и 100, и 1000, и, кто знает, может и 10000.
      • +4
        возможно, есть смысл менять сортировку(при этом, конечно, необходимы некоторые манипуляции с 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 записей)
    • +2
      А если он пришел на страницу из поисковика?
      • 0
        То рискует не найти нужной ему информации, т.к. в темах с таким количеством комментариев они быстро обновляются и «уходят» вниз.
    • –2
      Oracle rownum считает до сортировки, т.е. там нужно писать так:
      select * from (select c.* from comments c ORDER BY id) v WHERE rownum BETWEEN 10000 and 10010
      

      Mysql в этом плане гораздо удобней.
      Насчет оптимизации не знаю, нет возможности сейчас проверить план.
  • 0
    Во многих случаях MySQL способен использовать индексы для сортировок и для LIMIT.
    • 0
      увы, индексы в данном случае почему-то не сильно помогают… Не далее, чем неделю назад столкнулся с этой проблемой… Решал сходным методом с описаным в статье.
  • +3
    Результат же будет отличаться если в запросе есть условие (и не только).
    Например, если в оригинальном запросе первая запись результата найдётся на миллионной записи БД.
    То во втором запросе она вообще не найдётся, т.к. её нет среди первых 100 000.
    • –1
      devoluk.com/mysql-limit-offset-performance.html
      Не согласен с тем, что результаты запроса будут отличаться
      • 0
        Можете объяснить в чём конкретно ошибка в моих рассуждениях? В обоих статьях нет реальных примеров с where.
        • 0
          WHERE имеет больший приоритет и исполняется раньше чем LIMIT. То есть лимитирование идет только по уже отобранным записям и этим довольно часто пользуются.
          • НЛО прилетело и опубликовало эту надпись здесь
            • 0
              обьясню как оно должно выглядеть при наличии WHERE
              первый запрос:
              SELECT id FROM table WHERE date… LIMIT…
              второй запрос:
              JOIN SELECT * FROM TABLE WHERE id IN…
      • +1
        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)
        • 0
          перенесите WHERE во внутренний запрос и заработает… По поводу бенчмарков — лично тестировал — ускорение при разбивке на два запроса существенное.
          • +3
            Судя по этой статье explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/ Вы правы, при этом ускорении происходит не из-за того что внутренний запрос простой и планировщику легче понять какой индекс использовать, а из-за того что mysql меньше обращается в саму таблицу, обходится только индексами (что не очевидно для меня из данного поста), так же ускорение будет только если условие во внутреннем запросе можно вычислить только с использованием индексов.
  • –3
    А можно Вас попросить произвести запрос с явным указанием колонок, то есть использовать не *, а явное перечисление, так как я думаю, что вы заставляете MySQL производить сначала полное перечисление всех записей для выделение столбцов, а потом уже непосредственно выборку.
  • 0
    мм, если комментов очень много, наверное, стоит создать таблицу вида

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

    по запросу в нее получать список ID всем комментов, которые нужны…
    так как таки читают комменты обычно намного больше, чем удаляют. да и построение пагинатора не потребует каунтов и беспокойства самой таблицы постов
  • +1
    Проблема заключается в том, что используя 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.
  • 0
    Решал такую проблему для сильно более плохого случая.
    Решение простое — записываем номера страниц в комменты, а потом выбираем данные по этому ключу.
    По факту — просто храним некую метаинформацию о топике с номерами первых ID на странице N, или чуть чуть сложнее.
    Четко, красиво, стабильно.
    • 0
      а как быть в случае удаления коммента? шерстить все последующие на предмет обновления номера страницы? Есть какое-то решение кроме запрета удаления по прошествию Х минут?
      • 0
        Усложняем задачу — у меня древообразные комментарии.
        Посему добавляем команды на «сдвиг» и «массовые» апдейты бд.
        Фактически это нормально не решается средствами SQL, как минимум хранимками, так как требует анализа данных и кучи запросов как на выборку, так и на апдейт.
        Просто есть ряд задач, которые не надо решать «адекамическим» путем — можно и внешних мозгов на другом языке дописать.
      • +1
        бэкенд-скриптом пересчитывать…
      • 0
        >а как быть в случае удаления коммента?

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

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

Только зарегистрированные пользователи могут оставлять комментарии. Войдите, пожалуйста.