Sphinx - Multi-valued атрибуты и joined поля

В предыдущей статье я показал, как можно использовать Sphinx для индексации базы данных MS SQL Server, а также рассказал про дельта-индексы.

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

Подготовка базы данных

В этой статье мы снова будем использовать базу данных AdventureWorks, которую можно скачать с сайта codeplex.com. На этот раз для индексации нам потребуется информация о заказах, клиентах, и товарах.

Пример запроса для получения информации о заказах и клиентах:

Пример запроса для получения информации о товарах в заказах:

Подготовка файла конфигурации

Для начала создадим индекс order, содержащий информацию о заказах. Позже мы добавим в него multi-valued атрибут и joined поле.

Во-первых, добавим в файл конфигурации блоки searchd и source base:

Во-вторых, добавим в файл конфигурации блоки source order_base и source order:

И в-третьих, добавим в файл конфигурации блок index order:

Выполним индексацию при помощи команды:

c:\sphinx\bin\indexer order --config c:\sphinx\data\config.txt --rotate --print-queries

И, наконец, попробуем сделать какой-нибудь запрос, например:

select * from `order`;

Теперь у нас есть готовый индекс, на котором мы можем рассмотреть работу multi-valued атрибутов и joined-полей.

Multi-valued атрибуты

Обычные атрибуты могут содержать в себе только одно значение. Это может быть текст или число, но в любом случае значение будет только одно. Тем не менее в некоторых случаях бывает удобно хранить в одном атрибуте сразу несколько значений, чтобы выполнять по ним фильтрацию или группировку. Эту задачу как раз и выполняют multi-valued атрибуты.

Определение multi-valued атрибута в файле конфигурации выглядит следующим образом:

sql_attr_multi = ATTR-TYPE ATTR-NAME from SOURCE-TYPE [; QUERY] [; RANGE-QUERY]

Multi-valued атрибуты могут хранить только целочисленные значения. ATTR-TYPE задаёт тип атрибута и может иметь значение uint, bigint или timestamp.

ATTR-NAME задаёт название атрибута.

SOURCE-TYPE задаёт то, каким образом значения этого атрибута будут получены из источника данных, и может иметь значение field, query или ranged-query.

Если при объявлении атрибута используется значение field, то источник данных в основном запросе (опция sql_query в блоке source) должен вернуть для этого атрибута соответствующую колонку, в которой в виде строк будут содержаться значения - целые числа, разделённые запятыми. Такой способ индексации обычно применяться, если объединение нескольких целочисленных значений в одну строку на уровне базы данных не очень затратно.

Если при объявлении атрибута используются значения query или ranged-query, то источник данных в основном запросе не должен возвращать колонку для этого атрибута. Вместо этого Sphinx сделает один (при использовании query) или несколько (при использовании ranged-query) дополнительных запросов, используя QUERY и RANGE-QUERY (которые по назначению напоминают опции sql_query и sql_query_range в блоке source). Эти запросы должны вернуть результат, содержащий две колонки: id документа и значение атрибута. Sphinx самостоятельно сгруппирует полученные значения атрибутов по id документа.

Добавление multi-valued атрибута

Добавим в индекс order multi-valued атрибут product_id, содержащий id товаров в каждом заказе. Для этого в блок source order добавим следующее определение:

Обновим индекс и попробуем сделать следующий запрос:

select * from `order`
where match('@customer_name Jack');

Теперь по каждому заказу мы знаем список товаров этого заказа и можем использовать эту информацию для фильтрации и группировок.

Фильтрация по multi-valued атрибуту

Фильтрация по multi-valued атрибуту в выражении where выполняется точно также, как и фильтрация по обычному целочисленному атрибуту. Единственным нюансом в этом случае является то, что указанному условию должно соответствовать хотя бы одно из значений, содержащихся в атрибуте.

Так, например, мы можем найти заказы, содержащие товар с определённым id:

select * from `order`
where match('@customer_name Jack') and product_id = 758;

Точно также можно найти товары, содержащие одновременно несколько товаров с определёнными id:

select * from `order`
where match('@customer_name Jack') and product_id = 754 and product_id = 758;

Такой запрос на первый взгляд может показаться странным, поскольку обычные целочисленные атрибуты не могут быть одновременно равны двум различным значениям, но для multi-valued атрибутов это нормально.

Если же мы хотим найти заказы, содержащие хотя бы один из товаров с определённым id, то можем воспользоваться оператором in.

select * from `order`
where match('@customer_name Jack') and product_id in (754, 770);

Группировка по multi-valued атрибуту

Более интересной особенностью multi-valued атрибутов является группировка. Попробуем выполнить группировку по атрибуту product_id:

select * from `order`
where match('@customer_name Jack') and product_id = 758 group by product_id;

На первый взгляд, мы получили немного странные результаты, поскольку некоторые строки просто дублируются несколько раз, но на самом деле всё достаточно просто. При группировке по multi-valued атрибуту Sphinx создаёт одну группу на каждое уникальное значение этого атрибута во всех найденных документах. При этом к каждой группе добавляются атрибуты её первого документа.

Таким образом, при группировке по атрибуту product_id, мы получаем уже не заказы, а товары этих заказов. При этом в каждом товаре оказываются сгруппированы те заказы, которые содержат этот товар.

Воспользуемся функцией groupby, чтобы получить id товара, по которому сгруппированы заказы и функцией group_concat, чтобы получить список id этих заказов:

select groupby(), group_concat(id) from `order`
where match('@customer_name Jack') and product_id = 758 group by product_id;

Joined поля

Joined поля, также как и обычные поля, позволяют выполнять полнотекстовый поиск документов по значению поля. В этом они практически ни чем не отличаются от обычных полей. Основное отличие заключается в том, что при индексации данные из источника получаются не в основном запросе, а в одном или нескольких дополнительных запросах (аналогично multi-valued атрибутам). При этом для каждого документа в дополнительном запросе может быть получено несколько значений, которые объединяются в одно поле.

Определение joined поля в файле конфигурации выглядит следующим образом:

sql_joined_field = FIELD-NAME from SOURCE-TYPE ; QUERY [; RANGE-QUERY]

FIELD-NAME задаёт название поля.

SOURCE-TYPE задаёт то, каким образом значения этого поля будут получены из источника данных, и может иметь значение query, ranged-query или payload-query.

Использование значений query или ranged-query полностью аналогично использованию этих же значений в multi-valued атрибутах, т.е. источник данных в дополнительных запросах должен возвращать две колонки: id документа и значение поля. Единственным отличием от multi-valued атрибутов является то, что id документов должны быть обязательно упорядочены по возрастанию.

Использование значения payload-query позволяет для каждой пары id документа и значения поля также указать в виде целого числа её полезность (payload), которая будет учитываться в сортировке результатов. Это число должно возвращаться третьей колонкой в дополнительных запросах.

Добавление joined поля

Используя payload-query, добавим в индекс order joined поле product_name, содержащее названия товаров в каждом заказе. В качестве показателя полезности будем использовать количество каждого товара в заказе. Для этого в блок source order добавим следующее определение:

Обновим индекс и попробуем поискать заказы по названию товаров:

select * from `order`
where match('@customer_name Alex @product_name Sport');

Показатель полезности (payload)

Рассмотрим отдельно первые два заказа. С помощью запроса к базе данных узнаем количество каждого из товаров в этих заказах:

Как мы видим, в каждом заказе есть только один товар со словом «Sport», причём в количестве одной штуки. Поскольку это количество является показателем полезности, оба заказа имеют одинаковый вес при сортировке результатов.

Попробуем увеличить вес второго заказа, увеличив в нём количество товаров:

Теперь обновим индекс и повторим предыдущий запрос:

select * from `order`
where match('@customer_name Alex @product_name Sport');

Как мы видим, заказ, который раньше был на втором месте теперь поднялся на первое.

Заключение

В этой статье я рассказал про использование multi-valued атрибутов и joined полей, закончив тем самым рассказывать про индексацию баз данных.

В следующей статье я постараюсь немного рассказать про использование в качестве источника данных xml-файлов.

Скачать материалы к этой статье (скрипты и файлы конфигурации)