Лучший способ получить количество результатов до того, как был применен ЛИМИТ
При просмотре данных, поступающих из базы данных, вам необходимо знать, сколько страниц потребуется для отображения элементов управления переходом на страницу.
В настоящее время я делаю это, дважды выполняя запрос, один раз завернутый в count(), чтобы определить общие результаты, и второй раз с ограничением, применяемым для получения только тех результатов, которые мне нужны для текущей страницы.
Это кажется неэффективным. Есть ли лучший способ определить, сколько результатов было бы возвращено до того, как LIMIT было применяется?
Я использую PHP и Postgres.
5 answers
Чистый 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. Возможная альтернатива:
Рассмотрим последовательность событий:
-
WHEREпункт (иJOINусловия, но не здесь) фильтруйте соответствующие строки из базовой таблицы(таблиц).(
GROUP BYи агрегатные функции пошли бы сюда.) Оконные функции применяются с учетом всех соответствующих строк (в зависимости от предложения
OVERи спецификации фрейма функции). Простойcount(*) OVER()основан на всех строках.-
ORDER BY(
DISTINCTилиDISTINCT ONпошел бы сюда.) 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 нет. Одна вещь, на которую следует обратить внимание при разбиении на страницы (наиболее распространенная вещь, для которой используется ОГРАНИЧЕНИЕ IMHO): выполнение "СМЕЩЕНИЯ 1000 ПРЕДЕЛ 10" означает, что БД должна извлеките по крайней мере 1010 строк, даже если это даст вам только 10. Более эффективный способ сделать это - запомнить значение строки, по которой вы упорядочиваете предыдущую строку (в данном случае 1000-ю), и переписать запрос следующим образом:"... ГДЕ order_row > значение_о_1000_-го ПРЕДЕЛА 10". Преимущество в том, что "order_row", скорее всего, проиндексирован (если нет, у вас проблема). Недостатком является то, что если между представлениями страниц добавляются новые элементы, это может немного не синхронизироваться (но опять же, это может быть незаметно для посетителей и может привести к значительному увеличению производительности).
Вы могли бы снизить снижение производительности, не выполняя запрос COUNT() каждый раз. Кэшируйте количество страниц, скажем, за 5 минут до повторного запуска запроса. Если вы не видите огромное количество вставок, это должно работать просто отлично.
Поскольку Postgres уже выполняет определенное количество операций кэширования, этот метод не так неэффективен, как кажется. Это определенно не удваивает время выполнения. У нас есть таймеры, встроенные в наш уровень БД, так что я видел доказательства.
Учитывая, что вам нужно знать для подкачки, я бы предложил выполнить полный запрос один раз, записать данные на диск в виде кэша на стороне сервера, а затем передать их через механизм подкачки.
Если вы выполняете запрос COUNT с целью принятия решения о предоставлении данных пользователю или нет (т.Е., Если есть > X записей, верните ошибку), вам нужно придерживаться подхода COUNT.