Подтвердить что ты не робот

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

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

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

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

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

4b9b3361

ОТВЕТЫ

Ответ 1

Чистый 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 & lt; = OFFSET + LIMIT. Вопросы для значительно большего full_count.

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

Последовательность событий в запросе SELECT

(0. CTE оцениваются и материализуются отдельно. В Postgres 12 или более поздних версиях планировщик может включить подобные запросы перед началом работы.) Здесь нет.

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

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

(3. Другие выражения списка SELECT оцениваются на основе сгруппированных/агрегированных столбцов.) Здесь нет.

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

  2. ORDER BY

(6. DISTINCT или DISTINCT ON пойдут сюда.) Не здесь.

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

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

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

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

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

GET DIAGNOSTICS integer_var = ROW_COUNT;

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

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

Связанный:

Ответ 2

Как я описал в своем блоге, в 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", скорее всего, проиндексирован (если нет, у вас возникла проблема). Недостатком является то, что если новые элементы добавляются между просмотрами страниц, это может немного нарушиться (но, опять же, это может не наблюдаться посетителями и может значительно повысить производительность).

Ответ 3

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

Ответ 4

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

Ответ 5

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

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