войти зарегистрироваться

MySQL whois

индекс
202,61

Постраничная навигация с MySQL при большом количестве записей

Рано или поздно многие крупные проекты сталкиваются с проблемами производительности при постраничной навигации по записям. Некоторые из них решают эту проблему ограничением количества доступных для просмотра записей (скажем, не больше 1000). Вполне приемлемое решение. Но в этом случаем могут возникнуть проблемы с индексированием сайта сторонними поисковиками, которые и представляют наибольшую угрозу. В этой статье я хотел бы отказаться от привычной для всех панели навигации вида «1..2..3..4..» в пользу простой «вперед… назад» (будет проще объяснить), но это не проблема реализовать подобное и с первым вариантом.
Более точно определить тему, назвав, какое количество записей считать достаточно большим для появления тормозов, не получится, так как эта цифра для всех разная и сильно зависит от того, насколько быстрые у Вас жесткие диски, сколько памяти, и какая часть Ваших данных уже закеширована в ней и тд. Но если Вы и Ваши сервера ощущают, что n-ная страница при выводе даётся тяжелее первой, и при этом не знаете, что с этим делать – статья для Вас. Но для начала, я хотел бы на пальцах объяснить, почему ОНО работает медленно.

Кстати, тест происходит на виртуальной машинке, работаю я с СУБД под рутом, версия MySQL – 5.0.32.


1 Начнем с данных


Для тестирования создадим небольшую табличку и наполним ее чем-нибудь.

CREATE TABLE items (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
height INT UNSIGNED NOT NULL DEFAULT 0,
width INT UNSIGNED NOT NULL DEFAULT 0,
price DECIMAL(10,2) NOT NULL DEFAULT 0.0,
title VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;


Небольшим скриптом на PHP был сгенерирован INSERT 100000 записей. Данные такого вида:
— порядок полей в INSERT:
height, width, price, title
— шаблон для полей:
$val_tmpl = "\t(%d, %d, %f, 'Item %d')";
— тестовые значения ($i = 1..100000):
sprintf($val_tmpl, rand(0, 120), rand(0, 220), 10 * rand(0, $i) / $i, $i);

Заносим это все в нашу БД. И можно начинать…

2 Обычный метод постраничного вывода


Все, кто уже знает, чем плохи COUNT(*) и LIMIT… OFFSET, могут пропустить эту часть.

Прежде, чем рисовать навигатор, мы делаем SELECT COUNT(*) … WHERE (условия_выборки). Многие, почему-то уверены, что даже если у нас миллионы записей, но условия_выборки позволяют использовать индекс, то такой запрос отработает очень быстро. Проведем эксперимент. Выберем количество записей, у которых height больше 100. Для начала посмотрим, что будет, если индекса по полю height нет.

FLUSH STATUS;

SELECT SQL_NO_CACHE COUNT(*) FROM items WHERE height>100;
+----------+
| count(*) |
+----------+
| 16405 |
+----------+
1 row in set (0.09 sec)

SHOW STATUS LIKE ‘handler%’;

Последняя команда покажет, сколько же, и каких действий пришлось сделать СУБД, чтобы выполнить наш запрос. Так как индекса у нас нет, MySQL пришлось читать данные прямо из таблицы, поэтому нас интересует строка:
…
| Handler_read_rnd_next | 100001 |
…

То есть, MySQL пришлось сделать 100001 операцию перехода к следующей записи, чтобы найти все, соответствующие запросу.

Везде ниже перед каждым SELECT подразумевается выполнение FLUSH STATUS, а после: SHOW STATUS LIKE ‘handler%’.

Чем нам может помочь индекс.

ALTER TABLE items ADD INDEX height_idx (height);

SELECT SQL_NO_CACHE COUNT(*) FROM items FORCE INDEX(height_idx) WHERE height>100;
+----------+
| count(*) |
+----------+
| 16405 |
+----------+
1 row in set (0.04 sec)

В данном случае использовался индекс, поэтому Handler_read_rnd_next будет равен 0, а вот
…
| Handler_read_next | 16405 |
…
То есть индекс позволяет изначально посчитать только те записи, которые нужны, НО ему все равно требуется пробежаться по ним всем. Нет никакой магии, MySQL нигде не хранит количество записей, соответствующих запросу. Поэтому, если у вас миллионы записей, соответствующих условиям запроса, COUNT будет работать очень медленно.

Второй момент. LIMIT … OFFSET. Тот же эксперимент. Попросим нам 5 записей.

SELECT SQL_NO_CACHE * FROM items FORCE INDEX(height_idx) WHERE height>100 LIMIT 5;
…
5 rows in set (0.00 sec)
…
| Handler_read_next | 4 |
…
Вроде все логично. А теперь попросим вернуть другие 5 записей, начиная с 16401.

SELECT SQL_NO_CACHE * FROM items FORCE INDEX(height_idx) WHERE height>100 LIMIT 16400, 5;
…
5 rows in set (0.13 sec)

Видим, что время выборки значительно возросло. Смотрим статус:
…
| Handler_read_next | 16404 |
…
То есть, MySQL, прочитал все 16405, а только потом просто откинул все ненужные.
Как быть?

3 Как быть


Итак. От нас требуется вывести 10 записей, а так же нарисовать меню навигации. Мы поняли, что, чтобы дойти до записи, с которой необходимо начать отдавать нам результаты, MySQL тратит много лишних действий. Единственное, как это избежать – сразу перейти к нужной, изменив условия выборки.
Рассмотрим все на простом примере: пусть записи выдаются отсортированные по id. В этом случае нам нужно вместе со ссылкой передать id записи, на которой мы остановились. А остановимся мы на записи с id=10. То есть, в параметрах ссылки на следующую страницу нам нужно передать 10. Соответственно, для второй страницы запрос будет выглядеть так:

SELECT SQL_NO_CACHE id FROM items WHERE id>10 ORDER BY id LIMIT 10;

Кстати, в обоих случаях Handler_read_next будет равен 9. То есть, прыгнули на первую соответствующую запросу запись (благодаря индексу) и сделали 9 переходов на следующую. Самое главное, что какое бы число вместо 10 в условие мы не подставили – мы всегда в результате SHOW STATUS увидим одно и то же, и время выполнения такого запроса уже не будет зависеть от того, где мы находимся, а будет зависеть только от того, сколько и чего мы выбираем.
Надеюсь, смысл Вам понятен. Давайте тогда решим, что делать с меню навигации, а потом перейдем к более сложной ситуации. Пусть в url мы используем ключевые слова next, previous и last. В каких случаях показывать ссылки «вперед», «назад» и «последняя»?
Каждый раз, когда нам приходит next (запрос следующей страницы), мы выбираем не 10 записей, а 11, начиная с id, переданного в параметрах запроса. Если нам вернулось 11 записей, то следует показать ссылку вперед с id 10-й записи, а 11-ю откинуть. Если вернулось меньше 11 записей, то ссылку вперед показывать не надо. При этом мы всегда (всегда, когда пришло next) показываем ссылку назад (previous) с id первой записи из выборки. Ссылки «в начало» и «последняя» всегда показываются вместе с «назад» и «вперед» соответственно. То есть, если мы решили показывать «назад», то должны показать и «в начало».
Каждый раз, когда нам приходит previous (запрос на предыдущую страницу), мы выбираем 11 записей, у которых id меньше указанного в запросе, отсортированные в обратном порядке. То же самое: если вернулось 11 записей, то ссылку «назад» показываем. Ссылку вперед показываем всегда.
Надеюсь, понятно написал…
Что если нам пришел запрос «last»? Просто показать 10 записей, начиная с самой последней. То есть:

SELECT id FROM items ORDER BY id DESC LIMIT 10;

Думаете, у кого-нибудь из пользователей хватит сил промотать несколько сотен, а то и тысяч страниц, для того, чтобы обвинить Вас во лжи, обнаружив в итоге на первой странице не 10 записей? Даже если и хватит, то Вы можете ответить, что он слишком долго мотал…

Предыдущий пример был прост тем, что id – уникален. А что если требуется сортировка по полю, значения которого могут повторяться? Например, height в нашем случае. Простым запросом было выяснено, что в таблице каждое значения height встречается примерно 800 раз. Просто передать последний выведенный height в параметрах запроса уже мало. Поможет нам всё тот же id. От нас просят отсортировать записи по высоте, но это ведь не мешает нам отсортировать их потом еще и по id?
Для этого нам понадобится новый индекс:

ALTER TABLE items ADD KEY height_id_idx (height, id);

Запрос для первой страницы будет такой:

SELECT SQL_NO_CACHE id, height FROM items ORDER BY height, id LIMIT 10;

В моих результатах у последней записи height=0, id=1174. Так и надо передать следующей странице. Например, next_0_1174 или next/0/1074 – как Вам удобнее.
Теперь нам нужно выбрать записи, у которых либо height больше 0, либо height=0, а id>1174 (именно для этого мы и сделали дополнительную сортировку).
То есть:

SELECT SQL_NO_CACHE * FROM items WHERE (height>0) OR (height=0 AND id>1174) ORDER BY height, id LIMIT 10;

Надеюсь, пояснять, почему так, не нужно. Статус по-прежнему показывает всего 9 шагов вперед.
Таким образом, мы можем добавлять и другие сортировки. Например, если мы хотим вывести все записи, отсортированные по цене и высоте, запрос будет таким:

SELECT SQL_NO_CACHE * FROM items WHERE (price>5) OR (price=5 AND height>0) AND (price=5 AND height=0 AND id>1174) ORDER BY price, height, id LIMIT 10;

Остается только передавать все необходимые данные, правильно их обрабатывать и подставлять в запрос. И про индекс не забудьте.

4 Что делать с количеством результатов


Как быть, если мы хотим показать пользователям, сколько же результатов найдено? Так как речь идет о больших числах, то вряд ли нас кто-то будет проверять. Тот же гугл может выдать, что нашел 1000000 страниц, соответствующих запросу, но больше 1000 вы не увидите. Мы тоже можем выдать количество результатов лишь примерно. Где его взять и как оценить? Помните, мы выполняли запрос:

SELECT SQL_NO_CACHE COUNT(*) FROM items FORCE INDEX(height_idx) WHERE height>100;

А давайте сделаем так:
EXPLAIN SELECT SQL_NO_CACHE * FROM items FORCE INDEX(height_idx) WHERE height>100;

В результате получим что-то такое:
+----+-------------+-------+-------+---------------+------------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------------+---------+------+-------+-------------+
| 1 | SIMPLE | items | range | height_idx | height_idx | 4 | NULL | 22616 | Using where |
+----+-------------+-------+-------+---------------+------------+---------+------+-------+-------------+

Столбец rows как раз показывает оценочное число записей, которые нужно просмотреть. 22616 и 16405 – разница совсем не велика. Можно округлить до ~20000, да и ладно. Сойдёт. Только помните, что если используете, например, подзапросы и/или объединения, то EXPLAIN вернет несколько строк. Их все надо прочитать и перемножить значения rows.

Заключение


Данная проблема уже освещалась вскользь на хабре, но не так подробно.
Статья получилась больше, чем ожидал, хотя текст и разбавлен вставками запросов и результатов. В данный момент некуда выложить использованный для генерации данных скрипт + sql файлы. В общем… На заключение сил не осталось:)
+131
14 ноября 2008, 14:43
AHDREN

комментарии (80)

    • Azy
    • Azy
    • 14 ноября 2008, 14:51
    • #
      • +2
    Спасибо. Отлично разжевано.
    • да не за что:) Давно собирался написать, все руки не доходили…
    • Azy
    • Azy
    • 14 ноября 2008, 14:52
    • #
      • 0
    Я так понял индекс height, id нужен для условия (height=0 AND id>1174)?
    • Имено. Ну и для ORDER height, id
    • Индекс то нужен, причем несколько, в том числе составные. И в MySQL 5 они даже будут использоваться
      используя index merge алгоритм. Но все же лкчше такие запросы (с такими условиями)
      WHERE (price>5) OR (price=5 AND height>0) AND (price=5 AND height=0 AND id>1174)
      не писать, а переписывать их на запросы с UNION ALL, хоть и букаф больше, но работают значительно быстрей, притом если у вас есть общий лимит, скажем вывести 10 записей, то просто нужно добавить лимит в каждый из UNION-ов и в итоге, у вас получается датасет максимум из 30 записей (для 3х юнионов) который впоследствии сортируется и из него достаются 10; а не как в приведенном случае огромный датасет, который будет сортироваться используя огромтую темпорари тейбл. Вы можете проверить это с помощью все той же команды SHOW STATUS и посмотреть кол-во записей и чтений.

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

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

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

          Вместо этого запроса
          SELECT SQL_NO_CACHE * FROM items WHERE (price>5) OR (price=5 AND height>0) OR (price=5 AND height=0 AND id>1174) ORDER BY price, height, id LIMIT 10;

          Предлагается использовать нечто подобное

          select
          *
          FROM
          (
          SELECT
          *
          FROM
          items
          WHERE price>5
          LIMIT 10

          UNION ALL

          SELECT
          *
          FROM
          items
          WHERE price=5 AND height=0 AND id>1174
          LIMIT 10

          UNION ALL

          SELECT
          *
          FROM
          items
          WHERE price=5 AND height>0
          LIMIT 10
          ) as table
          LIMIT 10
          • Ваш запрос пролетает мимо querycache в отличии от первого запроса.
            • и почему это он пролетает? ничего подобного, с чего Вы решили?

              С директивой SQL_NO_CACHE шансов пролететь у первого запроса неймоверно больше :-)))
              • Оказывается я до сих пор был в преступном неведении.
                Ваша правда — попадает!
                Ретируюсь :)
                • я, честно-говоря, не могу понять, что Вас сбило с толку и привело к таким мыслям, что может не попасть. В запросе нет недетерминированных ф-ий типа RAND, CURRENT_TIME, NOW() и т.д.
                  Это обычный запрос, состоящий из нескольких запросов, результаты которых, кстати, закешированы не будут, я имею в виду каждый по отдельности. Но результат общего запроса в кеш попадет
                  • Про функции я вкурсе.
                    Но до сих пор я считал что наличие подзапроса безусловно исключает весь запрос из кэша. Я избегал их как только мог :)
                    оказывается зря. спасибо еще раз вам.
      • Вы знаете, специально не стал писать здесь про index merge, так как, если честно мне редко удается его добиться… Пробовал даже (x AND y) OR z = (x OR z) AND (y OR z), но говорят, что mysql пока не очень умно с ним работает…

        Что касается UNION — проверил конкретно на своих примерах — работает медленнее, поэтому и не стал про это писать:)
        • Да, индекс мердж пока оставляет желать лучшего.

          А вы попробуйте не UNION, а UNION ALL

          UNION это алиас для UNION DESTINCT, т.е. что UNION, что UNION ALL обе конструкции используют временные таблицы, только для UNION эта временная таблица имеет еще и UNIQUE KEY, из-за чего она медленные работает на вставках.

          поэтому используйте UNION ALL и в последующих запросах исключайте с помощью фильтров записи, которые попали в предыдущие. и будет вам счастье.

          Можете просмтореть мои статьи я там об этом писал.

          Или можем обсудить и посмотреть на ваши данные и ваши запросы, если хотите :-)
          • предлагаю переместить эту дискуссию в ЛС, а потом сюда отписаться о результатах:)
            ПС. ориентировочно — в понедельник
            • Да, без проблем :-)
              стучите — поговорим
      • думаю автор предполагал, что мы сделаем индекс и по price, height, id
        тогда никаких тормозов с этим запросом. и работать будет все же быстрее union all ;)

        но всему этому обратная сторона медали… 100к записей рандомных у меня весят около 5 мб, а индексы в сумме 10 мб. да и вставка записей будет с каждым разом медленнее и медленне… потому… ваш вариант имхо предпочтительей.
        • Вы правы, предполагается, что индекс по price, height, id есть. Но даже если Вы перепишете запрос через UNION ALL, то он все-равно понадобится, если Вы захотите отсортировать записи по height, price и при этом ни height, ни price не являются уникальным полем.

          Что касается размера индексов и времени вставки… Тут все сильно зависит от проекта. Обычно все же вставки происходят намного реже. Вы вставляете статью 1 раз, а прочитать ее могут, скажем 1000 человек. Даже если селект будет делаться не все 1000 раз благодаря кэшу, то выигрыш все-равно очевиден. А по поводу того, что с каждым разом вставки будут все медленнее — очень спорный вопрос…
      • позвольте уточнить, что запрос с UNION правомерен только в случае первых N записей а если же я делаю limit 1000,20 то результаты будут совсем разные!
        так что он не верен… Или я что то не догоняю?
  • честно говоря. я думаю, что можно использовать вот такой простой запрос

    • отправилос :(

      select * from table where id in (select id from table where height>100 LIMIT 100000,10)

      логику он не сильно усложняет, и скорость не сильно падает, а делает тоже самое, чем иметь геморой со всякими «взять 11 элементов, показать 10 и тп». по крайней мере я бы посмотрел в сторону этого запроса на предмет его скорости.
      ведь выбрать пусть даже миллион ID = int11 и отсортировать его — полная херня.
      • Вы, что!

        Мы же про мускул говорим! А в мускуле вложенные запросы почти всегда зло.
        Такие

        select * from table where id in (select id from table where height>100 LIMIT 100000,10)

        всегда зло!

        Мускул не выносит вложенные запросы наверх. Поэтому этот страх и ужас select id from table where height>100 LIMIT 100000,10 выполнится многократно.

        Насколько я понимаю Вы хотели использоваться covering index таким образом.

        Ну и делали бы себе джоин

        Select
        *
        From
        table
        JOIN
        (
        select id from table where height>100 LIMIT 100000,10
        )as t
        USING (id)
        • и вы наивно полагаете, что ваш запрос значительно лучше моего?

          может вы с mysql40 работаете? прекрасно он с такими запросами справляется, уверен что будет 0. можете попробовать.
          • Я не склонен полагать… Я в этом абсолютно уверен.

            ха ха, нет 3.23 :-))
            • не очень охотно вам верю, но если вы окажетесь правы, то когда у меня и правда в таких запросах возникнут проблемы со скоростью, буду вспоминать о вас с почестью ))))

              а по сути странно, если они работают по разному, ибо идея то одна и та же. тут еще может быть разница скорости в хранилищах innodb vs myisam. у мускуля такое бывает, что на одном выше скорость, на другом ниже.
      • Если Ваши данные не закэшированы в памяти, то будет сильный тормоз из-за того, что mysql будет бежать с самой первой записи до 1000000
        • 1 падение скорости до перезапуска сервера — не большая потеря в сравнении со скоростью разработки и костыляния кода.
        • Заметьте, я не говорл, что это хороший вариант, я просто сказал, что тот запрос плох.

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

          вариант SELECT list, of, fields, MAX(id)
          …
          where id > $Id
          …
          LIMIT 11

          когда нужно 10

          Вполне имеет право на жизнь, я сам использовал такой способ.
      • Вы знаете, все-же этот способ не очень хорош тем, что скорость его выполнения зависит от того, на которой странице Вы находитесь. Если Вы сделаете WHERE id>1000000 ORDER BY id LIMIT 10, то этот запрос по скорости не будет зависеть от того, какое число будет в id>XXX.
        • в этом я пожалуй могу согласиться, что мой метод зависит от колва записей, но не так явно как простой селект. скажем прямо, что если мне придется столкнуться с 100 000 000 значений, я думаю что я буду искать еще более интересные варианты, может конечно и ваш использую, но мой вариант для меня более расширяемый и он обладает более простой логикой в отличие от вашего, не надо запоминать айди и тп. итого: он проще для понимания, а для 10 000 — 100 000 падение скорости ~ 0.000.
          • 100 млн мускль не потянет. да и зачем ему столько скармливать. эффективнее в таком случае разбивать на таблички по 0,5—1 млн.
            • У меня тянет.
              • ладно, не буду спорить.
                мой случай: базу статистики с уймой полей, когда на 4млн полей база занимает уже гектар, мускулю стаёт плохо искать. добавлять ещё хуже.
        • На самом деле, как я уже писал, все зависит от данных и частоты обновлений.

          Мы как-то кешировали первые 10 страниц и хранили их айдишникив мемкеше, т.е. для первых 10 страниц с постраничным расположением статей.

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

          Пути господни неисповедимы :-) Для задач высокой нагрузки любое решение правильно, которое работает.
      • mysql> select * from items where id in (select id from items where height>100 LIMIT 90000,10);
        ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
        • 4.1?
          • 5.0.67
          • все тот же 5.0.32, который использовался при написании статьи
      • select * from table AS a where EXISTS (select 1 from table where id=a.id AND height>100 LIMIT 100000,10)
  • Потрясающе. Воистину, оптимизировать можно бесконечно.
  • Автору спасибо. Сам сталкивался с такой задачей — решили используя трюк с выборкой «11 записей вместо 10» что позволяет знать, а есть ли следующая страница.

    Кстати помимо этого когда юзер начинает листать мы читаем не «11» а «21» запись, т.е. 2 страницы сразу… ведь если юзер начал листать — то врядли остановится ))… размер «кеша» можно и увеличивать если мы видим что юзер настырный… но это больше в сторону javascrit-а а не mysql… и подходит не столько сайтам сколько приложениям
    • Именно. Я упомянул в самом начале, что используя предложенную технологию можно и не отказываться от стандартного меню навигации — все зависит от Вашей фантазии и умения работы с кэшем:)
  • Замечательно! Уяснил для себя одну неочевидную вещь.
  • Интересно будет ли эта проблема решена когда либо на стороне сервера.
    Чтобы лимит работал так как должен и не приходилось бы шаманить.
    • А как он должен? В чем он сейчас работает не правильно?
      • Тем что умные мальчики прочитавшие толстенькие книжечки не знают что ЗАСАДА
        помню начинал один сайт, работал он хорошо и быстренько, а через пару лет началось…

        Эх, прям как в жизни — мечты и ожидания разбились о быт :)
        • Ну и все таки, что неправильного в поведении limit?
          • При увеличении офсета начинает тормозить.
            А вообще интересно — почему во многих SQL диалектах такого оператора как LIMIT просто нет…
            • Умненьким мальчикам стоит подумать, ЧТО происходит при использовании offset.
              • Если бы это было бы сразу понятно об этом не создавали бы топики.
                Без ковыряния статистики так сразу и не поймёшь что за грабля.
            • а вы попробуйте предложите не тормозящий вариант limit'а. имхо лимит делает то, чем он и называется, не больше не меньше, а в кривых руках даже молоток гвозди гнет.

              ничего личного.
              • SELECT MIN,MAX,COUNT from atable

                Берем два запроса

                SELECT * FROM atable WHERE 1 LIMIT offset,numrows — будем тормозить и читать лишнее

                SELECT * FROM atable WHERE id>(MIN+((MAX-MIN)/COUNT)*offset LIMIT numrows — не будем

                Я так понимаю никто лимиты по НЕ ключам не делает. Осталось эту вот «офсетную» информацию в индексе хранить.

                • а вы хотя бы понимаете что такое ключи и как они работают? создается впечатление что нет.

                  и плюс при неполной заселенности поля айди второй вариант может вернуть результат не равный первой.
                • Я так понимаю никто лимиты по НЕ ключам не делает

                  Не будьте так категоричны.

                  Осталось эту вот «офсетную» информацию в индексе хранить.

                  Вы действительно не представляете что такое индексы.
                  Если хранить порядковый номера ключа в индексе, при в ставке в значения с минимальным ключом необходимо перебабашить все узлы индекса, а это непозволительная роскошь.
            • почему во многих SQL диалектах такого оператора как LIMIT просто нет

              Именно limit нет, но есть FIRST SKIP (кажется это по стандарту), вроде бы в MS SQL есть TOP (могу ошибаться)
  • Очень интересно, как для меня больше это можно применить к MS SQL так как там блага(или для кого зла) LIKE нет, а TOP указывает только на количество строк которое нужно выбрать.
  • отлично! вот за это «WHERE id>10 ORDER BY id LIMIT 10;» огромное спасибо
  • А что делать в случае, если пользователь хочет перейти на последнюю страницу?
    • отправить на 404 )))))))))
    • Мне больше интересно что нужно делать когда пользователь хочет перейти на страницу в середине.

      Про последнюю страницу есть в статье:
      SELECT id FROM items ORDER BY id DESC LIMIT 10;
      • Ну да, на любую страницу, которая не является следующей… ведь в этом случае ID не получится высчитать…
        • а вот тут то вам и поможет МОЙ метод :)

          habrahabr.ru/blogs/mysql/44608/#comment_1120949
          или его спорная подификация
          habrahabr.ru/blogs/mysql/44608/#comment_1121015

          а то автор задел только вершину айсберга.
        • Вы не совсем правильно поняли…
          С одной стороны, надо было бы получить количество записей, и взять остаток от деления на количество записей на странице. То есть, если у нас получилось 1234567 записей, а выводим мы по 10, то на последней странице должно быть 7 записей начиная с 1234561-й.

          Так как COUNT делать медленно, то мы можем просто показать последние 10 записей — никто даже не заметит подвоха:) Делается это через SELECT… ORDER BY id DESC LIMIT 10. В этом случае MySQL просто прочитает первые 10 записей с конца нашей таблицы, что не составит особого труда.
    • twi
    • twi
    • 14 ноября 2008, 15:59
    • #
      • –2
    я не настолько крут, поэтому использую SQL_CALC_FOUND_ROWS
    • Если у Вас нет проблем с производительностью — то этого вполне достаточно. Единственный момент, лучше все-таки делать два запроса — один, который делать COUNT (и его резальтат кэшировать) + запрос, который собственно выбирает данные (который использует LIMIT… OFFSET). Если вы будете использовать SQL_CALC_FOUND_ROWS в КАЖДОМ запросе, то это будет практически равносильно выполению SELECT без LIMIT.
  • Спасибо, что написали эту статью.

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

    Еще было бы узнать, как автор предлагает бороться со случаями, когда сортировка идет не по уникальному полю. Тема reverse_id не раскрыта ;)
    • эм… совсем без уникального поля не обойтись… А пример с сортировкой по неуникальному полю есть в статье, но при этом уникальное поле тоже используется:)
      • упс… прочитал по диагонали. Ваш вариант уйдет в filesort на сортировке.
        • неа… Специально все запросы через EXPLAIN прогонял. Вы про какой запрос конкретно?
  • Отличный топик. Беру на вооружение. Надо будет посмотреть, какие запросы выполняет Zend_Paginator.

    Кстати, если все запросы и результаты оформить тегами <code> и <pre>, будет просто великолепно.
    • Aux
    • Aux
    • 14 ноября 2008, 16:27
    • #
      • –2
    Не могу, к сожалению, проплюсовать — пост отменный! Спасибо! На некоторые вещи я не обращал внимания.
  • «перемножить значения rows.»? может суммировать? или я не внимателен?
    • именно перемножить
  • Ну вот наконец-то эту проблему разжевали. а то я когда--то думал, что отсутствие в других БД LIMIT offset, limit — это недостаток. счас понимаю, что это способ заставить разработчика писать правильно)
  • а как решать данную проблему если реально необходима навигация типа
    ..5 6 7 8…
    для таблиц с большим объемом данных?
    • Пожалуй, самый распространенный способ предложен в этом комментарии habrahabr.ru/blogs/mysql/44608/#comment_1121189 и habrahabr.ru/blogs/mysql/44608/#comment_1120939. То есть, первым запросом вы выбираете ТОЛЬКО данные, необходимые для пэйджинга (id и height, например) сразу для 10-ти страниц + 1 (101 запись, например) и кладете все это в кэш. Мотаете так же как обычно, только для каждой циферки в меню подставляете нужные параметры для id и height.
    • Q2W
    • Q2W
    • 15 ноября 2008, 01:20
    • #
      • 0
    Хмм, а в постгресе лимит нормально работает? В смысле не читает каждую запись, которую надо поскипать?

    И какие есть альтернативы limit'у не в мускле? И как они работают?
  • хорошая статья… для огромных проектов-- очень полезно… а в реале… в разделе обычных сайтов редко бывает более 200-300 страниц… а вот если что-то народного пользования и с огромным количеством документов… :)
  • Есть вот такая идея.

    Таблица новостей:
    CREATE TABLE `news` (
    `id` int(10) unsigned NOT NULL auto_increment,
    `dt` int(10) unsigned NOT NULL,
    `text` text NOT NULL,
    PRIMARY KEY  (`id`),
    KEY `dt` (`dt`)
);

    Вспомогательная таблица:
    CREATE TABLE `news_order` (
    `num` int(10) unsigned NOT NULL auto_increment,
    `id` int(11) NOT NULL,
    PRIMARY KEY  (`num`),
    KEY `num_id` (`num`,`id`)
);

    Раз в несколько минут пересоздаём вспомогательную таблицу:
    TRUNCATE `news_order`;
INSERT INTO `news_order` SELECT NULL, `id` FROM `news` ORDER BY `id` DESC;

    Страницу с новостями выбираем так:
    SELECT `news`.`id`, `news`.`dt`, `news`.`text`
FROM (
    SELECT `id`
    FROM `news_order`
    WHERE `num` > $from AND `num` < $till
    ORDER BY `num`
) AS `no`
INNER JOIN `news` ON `news`.`id` = `no`.`id`

    Если возвращает пустой результат — делаем такую (медленную только в маленькой доле запросов) выборку:
    SELECT `id`, `dt`, `text`
FROM `news`
LIMIT $from, $count

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