Лучший способ получить счетчик результатов до применения LIMIT

При просмотре данных, поступающих из БД, необходимо знать, сколько страниц будет отображаться для элементов управления переходом страницы.

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

Это кажется неэффективным. Есть ли лучший способ определить, сколько результатов было бы возвращено до LIMIT был применен?

Я использую PHP и Postgres.

Источник

5 ответов

Решение

Чистый SQL

С 2008 года все изменилось. Вы можете использовать оконную функцию, чтобы получить полный счет и ограниченный результат в одном запросе. (Представлено в PostgreSQL 8.4 в 2009 г.).

SELECT foo
     , count(*) OVER() AS full_count
FROM   bar
WHERE  <some condition>
ORDER  BY <some col>
LIMIT  <pagesize>
OFFSET <offset>

Обратите внимание, что это может быть значительно дороже, чем без общего количества. Все строки должны быть подсчитаны, и возможный ярлык, извлекающий только верхние строки из совпадающего индекса, может больше не помогать.
Не имеет большого значения с маленькими столиками или full_count <= OFFSET + LIMIT, Вопросы для значительно большего full_count,

Угловой корпус: когда OFFSET равно как минимум количеству строк базового запроса, строка не возвращается. Таким образом, вы также не получите full_count, Возможная альтернатива:

Рассмотрим последовательность событий:

  1. WHERE пункт (и JOIN условия, но не здесь) фильтровать подходящие строки из базовой таблицы (таблиц).

    (GROUP BY и агрегатные функции будут идти сюда.)

  2. Оконные функции применяются с учетом всех соответствующих строк (в зависимости от OVER пункт и спецификация фрейма функции). Простой count(*) OVER() основан на всех строках.

  3. ORDER BY

    (DISTINCT или же DISTINCT ON пошел бы сюда.)

  4. LIMIT / OFFSET применяются на основе установленного порядка, чтобы выбрать строки для возврата.

LIMIT / OFFSET становится все более неэффективным с ростом числа строк в таблице. Рассмотрите альтернативные подходы, если вам нужна лучшая производительность:

Альтернативы, чтобы получить окончательный счет

Существуют совершенно разные подходы, чтобы получить количество затронутых строк (не полный счет до OFFSET & LIMIT были применены). Postgres имеет внутреннюю учетную информацию о количестве строк, затронутых последней командой SQL. Некоторые клиенты могут получить доступ к этой информации или сами считать строки (например, psql).

Например, вы можете получить количество затронутых строк в plpgsql сразу после выполнения команды SQL с помощью:

GET DIAGNOSTICS integer_var = ROW_COUNT;

Подробности в руководстве.

Или вы можете использовать pg_num_rows в PHP. Или аналогичные функции в других клиентах.

Связанные с:

Источник

Как я описываю в своем блоге, MySQL имеет функцию под названием SQL_CALC_FOUND_ROWS. Это устраняет необходимость выполнять запрос дважды, но ему все равно необходимо выполнить запрос целиком, даже если предложение limit позволило бы остановить его раньше.

Насколько я знаю, для PostgreSQL подобной функции нет. Одна вещь, на которую следует обращать внимание при разбивке на страницы (наиболее распространенная вещь, для которой LIMIT используется IMHO): выполнение "OFFSET 1000 LIMIT 10" означает, что БД должна извлечь по крайней мере 1010 строк, даже если она дает вам только 10. Более эффективный способ сделать это - запомнить значение строки, по которой вы упорядочиваете предыдущую строку (в данном случае 1000-ую), и переписать запрос следующим образом: "... WHERE order_row > value_of_1000_th LIMIT 10". Преимущество заключается в том, что "order_row", скорее всего, проиндексирован (если нет, у вас возникла проблема). Недостатком является то, что если новые элементы добавляются между просмотрами страниц, это может немного нарушиться (но, опять же, это может не наблюдаться посетителями и может значительно повысить производительность).

Источник

Вы можете уменьшить потери производительности, не выполняя каждый раз запрос COUNT(). Кэшируйте количество страниц, скажем, за 5 минут до повторного запуска запроса. Если вы не видите огромное количество INSERT, это должно работать просто отлично.

Источник

Поскольку Postgres уже выполняет определенное количество операций кэширования, этот тип методов не так неэффективен, как кажется. Это определенно не удваивает время выполнения. У нас есть таймеры, встроенные в наш уровень БД, поэтому я видел доказательства.

Источник

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

Если вы выполняете запрос COUNT с целью решить, предоставлять ли данные пользователю или нет (т. Е. Если> X записей, возвращать ошибку), вам необходимо придерживаться подхода COUNT.

Источник
Другие вопросы по тегам