Оптимизируем 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) );
    
    Метки:
    Поделиться публикацией
    Похожие публикации
    Комментарии 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

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