Как организовать пагинацию, если БД и поисковая машина — это раздельные сервисы?

Доброго всем времени.

Каким образом можно организовать пагинацию запросов, фильтрованных одновременно по полнотекстовому запросу и некоторым аргументам, если поисковая машина для полнотекстового поиска и база данных - это раздельные сервисы? В упор не замечаю решения, хотя такое чувство, что оно совсем рядом.

Подробности:
MySQL-сервер (MariaDB 10.3+), в котором хранятся записи вида Документ с огромным набором параметров, по которым можно их отфильтровать - это и many-to-many отношения (фильтрация по нескольким ID других моделей), и "больше чем/меньше чем" по столбцу, IS TRUE/FALSE, сравнение строк и так далее. У всех Документов есть особенность - каждому из них соответствует текстовое содержимое, по содержимому которого и необходимо одновременно с поиском по параметрам выполнять фильтрацию. Содержимое хранится в Sphinx / Manticore (поисковые машины). Проблема - организация пагинации.

К примеру, необходимо сделать выборку документов по запросу:
атрибут1 IN (1,2,3)
атрибут2 = 'example_string'
атрибут3 IS TRUE
атрибут4 > 500
запрос полнотекстового поиска "укрепляющий эфирный шампунь с экстрактом масел жожоба"

Что ожидается: отфильтрованный по всем параметрам список документов, отсортированный по релевантности, заданной поисковой машиной.

Проблема: пагинация.

В текущем варианте из поисковой машины забираются все айдишники и позже добавляются в запрос базы данных вида WHERE IN (список ID) AND ORDER BY FIELD(id, список ID). Проблема в том, что по любому запросу из поисковой машины находится больше 100 000 документов и при попытке их вернуть поисковая машина выполняет запрос довольно долго (1-2 секунды). После этого еще и сама БД с таким уточнением работает дольше, чем если бы эти данные просто joinились с другой таблицей.

Обратный вариант - брать отфильтрованный список ID из базы данных и формировать условие в поисковой машине WHERE IN (список ID) тоже не вариант - опять низкая скорость обработки (да и кол-во их там переваливает за 100 000 на запрос).

Какие варианты пробовались:
- FEDERATED / FEDERATEDX таблица - не удалось "приготовить", нестабильная работа и на любой чих-ошибку не найти ответ в сети
- SphinxSE таблица - аналогично, отмечен разработчиками как устаревший и неподдерживаемый вариант
- заносить все параметры для фильтрации в индекс поисковой машины - ужасный вариант, так как поисковая машина выполняет фильтрацию по обычным своим полям очень медленно, плюс встает необходимость содержать актуальными две копии данных - в БД и поисковой машине
- PostgreSQL с полнотекстовым поиском - слабый поиск по русским словам и очень малое количество информации по тому, как использовать

Документов в базе в общем около 100 миллионов. Нужна скорость. Почему считаю, что она может быть? Потому что любой сложный запрос со стороны БД выполняется менее чем за 0.15 с, в поисковой машине - тоже около 0.15с. А соединить результаты их фильтрации черт знает как. Пробовался еще вариант "вычислять пересечения ID из двух результатов", но тут поисковая машина становится узким местом - не умеет она быстро отдавать все найденные ID, если их число переваливает за 1000 (кто пользуется Sphinx / Manticore, должен знать эту особенность).

Ограничение: поисковая машина должна быть обязательно Sphinx / Manticore. Остальные - болезненно некачественный поиск по тому содержимому, что есть в документах.
  • Вопрос задан
  • 721 просмотр
Решения вопроса 1
@BorisKorobkov Куратор тега MySQL
Web developer
Все атрибуты надо индексировать / фильтровать поисковой машиной.
sphinxsearch.com/docs/current.html#attributes
Ответ написан
Пригласить эксперта
Ответы на вопрос 3
@rPman
Добавление тысяч идентификаторов в sql запрос вида where in (...) это плохая практика, что получается, если вы сначала заполняете in nmemory таблицу идентификаторами (из полнотекстового запроса внешней базы), а потом уже делаете фильтрацию по вашим атрибутам из sql базы, приджойнив это временную табличку?

зы, пагинацию делать только через - получаете список id всех записей (отфильтрованные и отсортированные как надо), сохранив во временной таблице по сессии пользователя, а затем уже подгружаете данные окнами. Никаких limit для тяжелых запросов!

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

И конечно кеширование (не нужно кешировать совсем уж конечный результат, но промежуточный, создающий наибольшие накладные расходы - имеет смысл), всего и вся, память стоит гораздо дешевле процессорного времени.
Ответ написан
batyrmastyr
@batyrmastyr
1. Попробуйте искать целиком в сфинксе используя распределённые индексы.
index rss {
type = distributed
agent_persistent = 127.0.0.1:9312:rss1
...
agent_persistent = 127.0.0.1:9312:rssN
agent_persistent = 127.0.0.1:9312:rssUpdates
}
В первые N частей закидываете все документы более-менее равномерно их распределив, например, по датам создания или номерам и индексируете редко, раз в сутки или неделю.
В последний rssUpdates выбираете только документы изменившиеся с последней индексации, прописываете sql_query_killlist чтобы исключить из результатов устаревшие копии документов из других индексов.

2.1. Точное число результатов довольно быстро можно узнать так из SHOW META:
"SELECT ... WHERE <условия без сортировки> LIMIT 1; SHOW META like 'total_found';" и смотрите результат второго запроса.

2.2 Дальше делаете "честные запросы" - фильтрацию, сортировки для получения нужной страницы, но с указанием max_matches: "SELECT .. WHERE ... LIMIT ... OPTION max_matches=страница * размер страницы". В типичной ситуации, когда пользователь угомонится на первых страницах, скорость значительно увеличится.

3. В третьем сфинксе появился CREATE INDEX для обычных, не текстовых, столбцов. Может помочь.

4. В Постгресе сейчас есть "обёртка сторонних данных" и через неё можно стучаться, например, к mysql. К Сфинксу стучаться не пробовал, но стоит попробовать схему "данные в постгресе, полнотекстовый индекс из сфинкса подключаем через JOIN".
Ответ написан
ManticoreSearch
@ManticoreSearch
У всех Документов есть особенность - каждому из них соответствует текстовое содержимое, по содержимому которого и необходимо одновременно с поиском по параметрам выполнять фильтрацию

А в Sphinx / Manticore какой функциональности не хватает, чтобы фильтровать сразу сразу и по полнотексту, и по всему остальному? Можно пример?

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

Можно пример запроса по "поисковая машина выполняет фильтрацию по обычным своим полям очень медленно". Не пойму что вы имеете в виду под "обычными своими полями" и почему вдруг получается медленно.
Как по мне - этот вариант наиболее оптимальный в вашем случае. Фильтровать по полнотексту в одном месте и неполнотексту в другом и при этом оперировать сотнями тысяч документов возможно, но не всегда может получиться добиться хорошей производительности, т.к. всё будет упираться в то, что отфильтрованное в одном месте нужно будет передать в другое. А в случае, если делать всё в одном месте это происходит автматически.

плюс встает необходимость содержать актуальными две копии данных - в БД и поисковой машине

Так а сейчас вы разве это уже не делаете? Просто нужно дополнительно с id/full-text field синхронизировать и другие поля, по которым хочется в итоге фильтровать. Если вы имеете в виду, что сложно синхронизировать обновления документов, то для plain индексов есть main+delta для таких случаев (https://play.manticoresearch.com/maindelta/) , для RT - зависит от приложения.
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы