ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Java [игнор отключен] [закрыт для гостей] / Пейджинг с limit/offset и общее количество элементов
28 сообщений из 28, показаны все 2 страниц
  все  
Пейджинг с limit/offset и общее количество элементов
    #39211785
psyh103
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
psyh103
Гость
Всем привет! Прошу теоретическо-стратегической помощи в довольно конкретном вопросе. Уверен опытные бекенд программисты решали эту задачу тысячи раз, я хочу знать самое оптимальное решение.
Задача :
В таблице десятки миллионов строк (одна таблица уже около 40ГБ и растет)
Нужно по определенному фильтру сделать SELECT (в результате получается несколько тысяч результатов, например 87300), и на клиент передать только 50 результатов. Но при этом сообщить ему (клиенту) также и общее количество результатов (87300).
Т.е. классический пейджинг с limit/offset

Я вижу два варианта решения
1) Вариант : делаю два запроса:
1. COUNT (SELECT..WHERE....) - получаю total
2. SELECT..WHERE....LIMIT=50 - получаю 50 результатов

2) Вариант : выбираю все при помощи hibernate (или чего-то аналогичного) в java List<Results>, беру list.size(), и выбираю 50 первых результатов

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

Правильно ли я понимаю работу памяти во втором случае?
Какой вариант "дешевле" и принято использовать, как наиболее рациональный?
Может есть третий путь?
...
Рейтинг: 0 / 0
09.04.2016, 23:20
| Ответить | Цитировать | Написать  
Пейджинг с limit/offset и общее количество элементов
    #39211788
Фотография Usman
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Usman
Участник
psyh1031) Вариант : делаю два запроса:Можно одним запросом (Oracle):
Код: sql
1.
SELECT COUNT(*) OVER () AS Total, t.* FROM my_table t
...
Рейтинг: 0 / 0
09.04.2016, 23:50
| Ответить | Цитировать | Написать  
Пейджинг с limit/offset и общее количество элементов
    #39211790
Фотография Usman
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Usman
Участник
Пардон, это не совсем то, что нужно
...
Рейтинг: 0 / 0
10.04.2016, 00:03
| Ответить | Цитировать | Написать  
Пейджинг с limit/offset и общее количество элементов
    #39211792
rdm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rdm
Участник
psyh103, hibernate не будет 87300 загружать - Criteria/HQL/Sort/Limit
...
Рейтинг: 0 / 0
10.04.2016, 00:10
| Ответить | Цитировать | Написать  
Пейджинг с limit/offset и общее количество элементов
    #39211793
rdm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rdm
Участник
Разве offset/limit правильный подход? Может лучше primaty key < :lastId / limit?
...
Рейтинг: 0 / 0
10.04.2016, 00:12
| Ответить | Цитировать | Написать  
Пейджинг с limit/offset и общее количество элементов
    #39211812
Фотография Usman
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Usman
Участник
Usman
Код: sql
1.
SELECT COUNT(*) OVER () AS Total, t.* FROM my_table t

Можно использовать как подзапрос:
Код: sql
1.
SELECT t.* FROM (SELECT COUNT(*) OVER () AS Total, t.* FROM my_table t WHERE ...) t WHERE ROWNUM <= 50
...
Рейтинг: 0 / 0
10.04.2016, 01:09
| Ответить | Цитировать | Написать  
Пейджинг с limit/offset и общее количество элементов
    #39211814
psyh103
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
psyh103
Гость
rdmpsyh103, hibernate не будет 87300 загружать - Criteria/HQL/Sort/Limit

А что он тогда в ListArray результатов имеет? ссылки только на строки в таблице что ли?

rdmРазве offset/limit правильный подход? Может лучше primaty key < :lastId / limit?

Да, действительно так выглядит правильней, спасибо за рекомендацию
...
Рейтинг: 0 / 0
10.04.2016, 01:13
| Ответить | Цитировать | Написать  
Пейджинг с limit/offset и общее количество элементов
    #39211815
Фотография Usman
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Usman
Участник
psyh103В таблице десятки миллионов строкЭто что-то вроде архива (т.е. данные в таблицах не подвергается изменениям) ?psyh103Нужно по определенному фильтру сделать SELECTЕсть ли вероятность того, что можно предугадать действия пользователя (хотя бы частично) ?
...
Рейтинг: 0 / 0
10.04.2016, 01:16
| Ответить | Цитировать | Написать  
Пейджинг с limit/offset и общее количество элементов
    #39211817
psyh103
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
psyh103
Гость
UsmanЭто что-то вроде архива (т.е. данные в таблицах не подвергается изменениям)
нет не архив, данные постоянно обновляются

UsmanЕсть ли вероятность того, что можно предугадать действия пользователя (хотя бы частично) ?

Если имеется ввиду набор фильтров, то почти всегда один и тот же фильтр, а вот результаты постоянно изменяются, т.к. в той большой таблице все время меняются данные, которые влияют на выборку по фильтру
...
Рейтинг: 0 / 0
10.04.2016, 01:22
| Ответить | Цитировать | Написать  
Пейджинг с limit/offset и общее количество элементов
    #39211818
psyh103
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
psyh103
Гость
UsmanUsman
Код: sql
1.
SELECT COUNT(*) OVER () AS Total, t.* FROM my_table t

Можно использовать как подзапрос:
Код: sql
1.
SELECT t.* FROM (SELECT COUNT(*) OVER () AS Total, t.* FROM my_table t WHERE ...) t WHERE ROWNUM <= 50



Спасибо, выглядит, как хорошее решение.
...
Рейтинг: 0 / 0
10.04.2016, 01:23
| Ответить | Цитировать | Написать  
Пейджинг с limit/offset и общее количество элементов
    #39211836
вадя
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
вадя
Участник
psyh103,
конкретный вид sql строки будет зависить от используемой базы.
при большом объеме данных count лучше вынести в отдельный запрос, он будет выполняться дольше, чем запрос с выводом 50 записей.
сначала выведутся данные, а с задержкой общее число записей.
...
Рейтинг: 0 / 0
10.04.2016, 04:17
| Ответить | Цитировать | Написать  
Пейджинг с limit/offset и общее количество элементов
    #39211837
вадя
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
вадя
Участник
UsmanUsman
Код: sql
1.
SELECT COUNT(*) OVER () AS Total, t.* FROM my_table t

Можно использовать как подзапрос:
Код: sql
1.
SELECT t.* FROM (SELECT COUNT(*) OVER () AS Total, t.* FROM my_table t WHERE ...) t WHERE ROWNUM <= 50


надеюсь, что ТС тоже оракл, а также, что select count не будет выполняться в каждой строке.
но зачем иметь столбеу с одни числом повторяемым 88
...
Рейтинг: 0 / 0
10.04.2016, 04:25
| Ответить | Цитировать | Написать  
Пейджинг с limit/offset и общее количество элементов
    #39211838
вадя
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
вадя
Участник
Usmanпропущено...
Можно использовать как подзапрос:
Код: sql
1.
SELECT t.* FROM (SELECT COUNT(*) OVER () AS Total, t.* FROM my_table t WHERE ...) t WHERE ROWNUM <= 50


надеюсь, что у ТС тоже оракл, а также, что select count не будет выполняться в каждой строке.
но зачем иметь столбец с одни числом повторяемым 80 0000+ раз?
память надо разумно использовать
...
Рейтинг: 0 / 0
10.04.2016, 04:30
| Ответить | Цитировать | Написать  
Пейджинг с limit/offset и общее количество элементов
    #39211840
вадя
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
вадя
Участник
UsmanUsman
Код: sql
1.
SELECT COUNT(*) OVER () AS Total, t.* FROM my_table t

Можно использовать как подзапрос:
Код: sql
1.
SELECT t.* FROM (SELECT COUNT(*) OVER () AS Total, t.* FROM my_table t WHERE ...) t WHERE ROWNUM <= 50


сначала выполнится запрос отбирающий 80 000+ строк, потом из него выберется 50 строк — это очень долго.
...
Рейтинг: 0 / 0
10.04.2016, 04:45
| Ответить | Цитировать | Написать  
Пейджинг с limit/offset и общее количество элементов
    #39212013
just_vladimir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
just_vladimir
Участник
Очевидно, что рабочий вариант это только первый, как именно его реализовывать - одним запросом, в котором доп.колонкой добавить общее количество или двумя отдельными запросами уже вопрос вкуса и конкретной СУБД, это все будут работающие варианты.
...
Рейтинг: 0 / 0
10.04.2016, 20:25
| Ответить | Цитировать | Написать  
Пейджинг с limit/offset и общее количество элементов
    #39212090
Фотография Usman
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Usman
Участник
just_vladimirОчевидно, что рабочий вариант это только первый+512
...
Рейтинг: 0 / 0
11.04.2016, 01:42
| Ответить | Цитировать | Написать  
Пейджинг с limit/offset и общее количество элементов
    #39212097
вадя
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
вадя
Участник
Usmanjust_vladimirОчевидно, что рабочий вариант это только первый+512
только порядок селектов обратный
...
Рейтинг: 0 / 0
11.04.2016, 03:59
| Ответить | Цитировать | Написать  
Пейджинг с limit/offset и общее количество элементов
    #39212132
Фотография Blazkowicz
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Blazkowicz
Участник
psyh1031) Вариант : делаю два запроса:
1. COUNT (SELECT..WHERE....) - получаю total
2. SELECT..WHERE....LIMIT=50 - получаю 50 результатов

Вполне стандартный подход. Все так делают.

psyh1032) Вариант : выбираю все при помощи hibernate (или чего-то аналогичного) в java List<Results>, беру list.size(), и выбираю 50 первых результатов

В hibernate много такого чего в чем-то аналогичном может и не быть.

psyh103В первом случае меня смущает то, я всегда делаю два запроса подряд к базе (вместо одного)

Ну, если это смущает только вас и не смущает DBA, то, наверное, не страшно.

psyh103Во втором то, что я выгружаю 87300 объектов в оперативную память в JVM (а при этом аналогичный запрос приходит он нескольких сотен пользователей одновременно и для каждого нужно все результаты тянуть в оперативку)

Ну, зачем так сразу. Hibernate может много чего предложить для решения этого вопроса. Например существует такой метод
http://docs.jboss.org/hibernate/orm/5.0/javadocs/org/hibernate/Session.html#createFilter-java.lang.Object-java.lang.String-
Если, я правильно понял, то ему можно скормить ленивую коллекцию и потом загружать ее постраничено через Query
Другая сторона вопроса это кэш второго уровня. Не зачем читать все объекты из БД для каждого пользователя. Можно их сформировать из кэша. Правда, тут есть нюанс, в виде того как работает кеш. Он собирает новые объекты из слепка. Поэтому каждый пользователь расходует память. Но это вполне можно оптимизировать, если иметь, например, список id. В общем, есть над чем подумать.

psyh103[b]Правильно ли я понимаю работу памяти во втором случае?

Да, но есть варианты.

psyh103Какой вариант "дешевле" и принято использовать, как наиболее рациональный?

Два запроса к БД, скорее всего, будут рациональнее. Но если между сервером и БД воткнуть толковый кэш, то будет "еще более рационально". Поэтому я за Hibernate + Paging любым удобным для вас способом (JPQL, HQL, Criteria API, Lazy Collection)
...
Рейтинг: 0 / 0
11.04.2016, 08:24
| Ответить | Цитировать | Написать  
Пейджинг с limit/offset и общее количество элементов
    #39212343
Partisan M
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Partisan M
Участник
BlazkowizcВполне стандартный подход. Все так делают.

Все, кто не понимает, что делает. Вполне стандартная ошибка.

Правильные подходы следующие:

- иметь в таблице поле, подходящее для счётчика записей и делать в цикле выбор диапазонов по этому счётчику. Например, в Oracle уже есть такое поле ROWID.
- в процедурном SQL соответствующей СУБД делать в цикле выборку с использованием курсора. Метод довольно универсальный, поскольку в популярных СУБД курсоры есть. Хотя синтакис зависит от СУБД.

Неправильно использование OFFSET... LIMIT и Hibernate. Способ с OFFSET...LIMIT неэффективен, потому что будут считываться все записи с начала и уже потом из них выбираться нужные. Способ с Hibernate неправильгный потому, что в нём Hibernate испольхуется не по назначению. Следовательно, вряд ли можно надеяться на особую эффективность.
...
Рейтинг: 0 / 0
11.04.2016, 11:22
| Ответить | Цитировать | Написать  
Пейджинг с limit/offset и общее количество элементов
    #39212373
just_vladimir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
just_vladimir
Участник
Partisan MСпособ с OFFSET...LIMIT неэффективен, потому что будут считываться все записи с начала и уже потом из них выбираться нужные.
Реквестирую пруфлинк по этому поводу
...
Рейтинг: 0 / 0
11.04.2016, 11:42
| Ответить | Цитировать | Написать  
Пейджинг с limit/offset и общее количество элементов
    #39212404
Фотография Blazkowicz
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Blazkowicz
Участник
Partisan MВсе, кто не понимает, что делает. Вполне стандартная ошибка.

Ой, да.

Partisan M- иметь в таблице поле, подходящее для счётчика записей и делать в цикле выбор диапазонов по этому счётчику. Например, в Oracle уже есть такое поле ROWID.

Ага. Еще один ID, который нужно генерировать правильным образом. Я понимаю, что кроме Oracle других RDBMS не существует. Но все же, у ТС может и MySQL оказаться.

Partisan M- в процедурном SQL соответствующей СУБД делать в цикле выборку с использованием курсора. Метод довольно универсальный, поскольку в популярных СУБД курсоры есть. Хотя синтакис зависит от СУБД.

Курсор. На трехзвенке. Опять, с условием, что у нас только Oracle?

Partisan MНеправильно использование OFFSET... LIMIT и Hibernate. Способ с OFFSET...LIMIT неэффективен, потому что будут считываться все записи с начала и уже потом из них выбираться нужные. Способ с Hibernate неправильгный потому, что в нём Hibernate испольхуется не по назначению. Следовательно, вряд ли можно надеяться на особую эффективность.
Возможно. Если автор им особо и не пользуеются и изучать не хочет, то и приплетать ORM, наверное, смысла нет.
...
Рейтинг: 0 / 0
11.04.2016, 12:01
| Ответить | Цитировать | Написать  
Пейджинг с limit/offset и общее количество элементов
    #39212423
Сергей Арсеньев
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сергей Арсеньев
Участник
Partisan M- в процедурном SQL соответствующей СУБД делать в цикле выборку с использованием курсора.
Содержит плюсы и минусы.
Главный из последних, заключен в сценарии, при котором пользователь почти никогда не переходит на последнюю страницу. А просто меняет условие посмотрев первые одну-две.
...
Рейтинг: 0 / 0
11.04.2016, 12:16
| Ответить | Цитировать | Написать  
Пейджинг с limit/offset и общее количество элементов
    #39212580
lleming
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
lleming
Участник
just_vladimirPartisan MСпособ с OFFSET...LIMIT неэффективен, потому что будут считываться все записи с начала и уже потом из них выбираться нужные.
Реквестирую пруфлинк по этому поводу

если Order by есть то неэффективно. а так вполне себе эффективно. (postgresl)

Limit (cost=1.15..1.73 rows=5 width=857) (actual time=0.042..0.054 rows=5 loops=1)
-> Seq Scan on individual (cost=0.00..228311.98 rows=1979148 width=857) (actual time=0.016..0.039 rows=15 loops=1)
Filter: (pol_id = 1)
Rows Removed by Filter: 9
Total runtime: 0.124 ms

тут правильнее будет сказать что если offset приближается к максимальному количеству строк
Код: plsql
1.
 where pol_id = 1 offset 100000 limit 5


то становится неэффективным тут лучше уже

Код: sql
1.
where id > 100000 limit 5




Но редкий пользователь дошагает до 100000-ой страницы т.к. что в общем случае OFFSET...LIMIT вполне эффективно.
...
Рейтинг: 0 / 0
11.04.2016, 14:39
| Ответить | Цитировать | Написать  
Пейджинг с limit/offset и общее количество элементов
    #39212778
just_vladimir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
just_vladimir
Участник
llemingjust_vladimirпропущено...

Реквестирую пруфлинк по этому поводу

если Order by есть то неэффективно. а так вполне себе эффективно. (postgresl)

Limit (cost=1.15..1.73 rows=5 width=857) (actual time=0.042..0.054 rows=5 loops=1)
-> Seq Scan on individual (cost=0.00..228311.98 rows=1979148 width=857) (actual time=0.016..0.039 rows=15 loops=1)
Filter: (pol_id = 1)
Rows Removed by Filter: 9
Total runtime: 0.124 ms

тут правильнее будет сказать что если offset приближается к максимальному количеству строк
Код: plsql
1.
 where pol_id = 1 offset 100000 limit 5


то становится неэффективным тут лучше уже

Код: sql
1.
where id > 100000 limit 5




Но редкий пользователь дошагает до 100000-ой страницы т.к. что в общем случае OFFSET...LIMIT вполне эффективно.
Ну Вы как бы подтверждаете обратное, что у limit/offset все хорошо, а то некоторые голословно кидаются в него фекалиями, называя ошибкой его использование.
...
Рейтинг: 0 / 0
11.04.2016, 16:08
| Ответить | Цитировать | Написать  
Пейджинг с limit/offset и общее количество элементов
    #39212851
вадя
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
вадя
Участник
Код: sql
1.
where id > 100000 limit 5


это становится хорошо только в одном случае - если сортировак по id
...
Рейтинг: 0 / 0
11.04.2016, 17:00
| Ответить | Цитировать | Написать  
Пейджинг с limit/offset и общее количество элементов
    #39212948
lleming
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
lleming
Участник
вадя
Код: sql
1.
where id > 100000 limit 5


это становится хорошо только в одном случае - если сортировак по id

Код: plsql
1.
where [id|surname|name|order field name with index ] >|<|= ${value}



как то так (сверху имелось ввиду раз Order by явно не было указано то id по дефолту primary key и соотвественно с индексом)
...
Рейтинг: 0 / 0
11.04.2016, 18:26
| Ответить | Цитировать | Написать  
Пейджинг с limit/offset и общее количество элементов
    #39213108
psyh103
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
psyh103
Гость
Спасибо всем, кто отписался!

У меня postgres.
Сортировка, конечно же не по id, поэтому, к сожалению, id > 100000 - не катит
С кешами я опыта большого не имею, но скажу, что для каждого юзера результаты будут разные, так как он передает хоть и одинаковый формат фильтра, но параметры в этом наборе у каждого индивидуальные, потому то, что вернулось одному, практически никогда не вернется другому, за невероятно редким исключением.

Честно говоря offset я бы вообще не рассматривал в моей задаче, я скорей делаю запрос по фильтру с эксклюдом перечня id-шников, которые получены в результате предыдущего запроса.
Меня интересовало как total получить наиболее оптимально.
Отдельным sql - запросом без лимита, каким-то одним сложным запросом с подзапросом или хибернетом ; раз я его уже использую) получив коллекцию - ее размер и отобрав нужные элементы.

Хотя я с удовольствием почитал и всю остальную инфу.
...
Рейтинг: 0 / 0
11.04.2016, 23:41
| Ответить | Цитировать | Написать  
Пейджинг с limit/offset и общее количество элементов
    #39213149
psyh103
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
psyh103
Гость
psyh103Спасибо всем, кто отписался!
Сортировка, конечно же не по id, поэтому, к сожалению, id > 100000 - не катит


Суть в том, что сортировка у меня по удаленности (расстояние между запрашивающим юзером и юзерами-строками таблицы) они все время перемещаются их координаты все время меняются и соответственно не подходит ни офсет ни id, только exclude тех, кого уже получил ранее
...
Рейтинг: 0 / 0
12.04.2016, 03:12
| Ответить | Цитировать | Написать  
28 сообщений из 28, показаны все 2 страниц
  все  
Форумы / Java [игнор отключен] [закрыт для гостей] / Пейджинг с limit/offset и общее количество элементов
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=59&tid=2124170]:
 0ms
get settings:
 5ms
get forum list:
 19ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 147ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 80ms
get tp. blocked users:
 2ms
others: 211ms
total:  484ms
созд. / загр.: 484ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]