Sphinx - Индексация базы данных и дельта-индексы

В предыдущей статье я кратко рассказал про основные возможности Sphinx. Пришло время применить эти знания на практике.

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

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

Прежде чем перейти к созданию индексов нам потребуются данные, которые мы могли бы индексировать. Чтобы не тратить время на создание собственной базы данных, я решил воспользоваться готовой базой AdventureWorks, которую можно скачать с сайта codeplex.com.

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

В этой статье мы будем индексировать таблицу Production.Product (Товар), а именно колонки ProductID (Идентификатор), Name (Название) и ListPrice (Цена). При этом индексировать будем только те товары, которые имеют флаг FinishedGoodsFlag (Полностью описанный товар). Также нам понадобится колонка ModifiedDate (Дата изменения).

Чтобы было более понятно, приведу пример запроса:

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

Первым делом добавим в файл конфигурации блок searchd. Я уже описывал его в самой первой статье:

Далее при помощи блока source нам необходимо описать источник данных. Воспользуемся тем, что эти блоки можно наследовать и разобьём один блок на несколько.

Добавим в файл конфигурации блок source base, который будет отвечать за подключение к базе данных:

В этом блоке мы указываем, что собираемся использовать базу данных MS SQL Server (опция type). Также указываем название сервера и базы данных (опции sql_host и sql_db). Чтобы подключиться к базе данных можно либо указать логин и пароль пользователя (опции sql_user и sql_pass), либо использовать Windows-аутентификацию (опция mssql_winauth).

Следующим шагом добавим в файл конфигурации блок source product_base, в котором будут перечислены поля и атрибуты, которые мы собираемся индексировать:

При использовании баз данных в качестве источника в блоке source необходимо явно перечислить все поля и атрибуты, которые мы собираемся индексировать. При этом необходимо указать их тип, название и дополнительные параметры, если они есть.

В нашем случае мы объявляем поле и атрибут name при помощи опции sql_field_string (эта опция сразу объявляет поле и атрибут с одним и тем же названием, поэтому отдельно объявлять атрибут не требуется) и атрибут price при помощи опции sql_attr_float. Также любой источник данных неявно содержит целочисленный атрибут id, который не требуется объявлять. Более подробный список опций для объявления полей и атрибутов можно найти в документации.

Пример 1: Простая индексация

В самом простом случае для создания индекса достаточно одного sql-запроса. Sphinx выполнит этот запрос и, получив результаты его выполнения, построит индекс.

Добавим в файл конфигурации блок source product, содержащий sql-запрос:

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

Далее, добавим в файл конфигурации блок index product:

В этом блоке мы указываем источник данных (опция source), который будет использоваться при индексации, и путь (опция path), по которому будут сохраняться файлы индекса.

Чтобы выполнить индексацию, нам потребуется утилита indexer.exe, которая входит в состав дистрибутива Sphinx (её можно найти в папке bin вместе с утилитой searchd.exe).

Запустим командную строку от имени администратора и выполним следующую команду (вообще, для всех команд лучше создавать bat-файлы):

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

В этой команде мы указываем утилите indexer.exe индекс, который нужно обновить (в данном случае индекс product), и файл конфигурации, который при этом нужно использовать. Опцию --rotate необходимо использовать в тех случаях, когда обновляемый индекс должен оставаться доступен во время индексации. В этом случае Sphinx фактически создаст ещё один индекс, а потом просто заменит старый индекс на новый. Опция --print-queries не является обязательной и нужна, чтобы все sql-запросы к базе данных выводились на экран. После выполнения этой команды мы должны увидеть нечто подобное:

Мы успешно создали первый индекс. Теперь мы можем подключиться к Sphinx и выполнить какой-нибудь запрос, например:

select * from product;

Пример 2: Индексация по частям

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

К счастью, Sphinx поддерживает индексацию по частям, при которой вместо одного sql-запроса, сразу возвращающего все строки из базы данных, делается несколько, возвращающих строки с идентификаторами в определённом интервале.

Изменим блок source product, следующим образом:

Во-первых, мы при помощи опции sql_query_range определяем sql-запрос, который будет возвращать минимальный и максимальный идентификаторы строк, которые мы собираемся индексировать.

Во-вторых, мы изменяем запрос в опции sql_query таким образом, чтобы он возвращал только строки, идентификаторы которых находятся в определённом интервале, который можно задать при помощи макросов $start и $end.

Также мы используем две дополнительные опции: sql_range_step, которая задаёт максимальное количество строк, которое может быть получено из базы данных за один запрос, и sql_ranged_throttle, которая задаёт задержку в миллисекундах после каждого запроса.

Теперь, снова запустив индексацию той же командой, мы должны увидеть следующий результат:

Как мы видим, Sphinx выполнил три запроса к базе данных, сначала получив строки с идентификаторами от 680 до 829, потом от 830 до 979, и потом от 980 до 999. При этом после каждого запроса должна была происходить секундная задержка.

Пример 3: Дельта-индексы

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

Идея заключается в том, чтобы вместо одного индекса создать два: основной и дельта-индекс. Основной индекс будет содержать в себе все данные и обновляться будет либо очень редко, либо вообще не будет обновляется, а вместо этого будет периодически объединяться с дельта-индексом. Дельта-индекс же будет содержать в себе только те данные, которые изменялись с момента последнего обновления основного индекса. Поскольку дельта-индекс обычно значительно меньше основного, его обновление можно производить гораздо чаще, а выполняться оно будет значительно быстрее.

Шаг 1: Создание таблицы для отслеживания обновлений индексов

Поскольку при обновлении дельта-индекса необходимо будет знать, когда в последний раз обновлялся основной индекс, нам потребуется таблица ProductIndexLog, в которую будут записываться дата и время каждого обновления индексов.

Создадим в базе данных таблицу ProductIndexLog при помощи следующего скрипта:

Шаг 2: Настройка источников данных в файле конфигурации

Удалим из файла конфигурации блок source product и добавим вместо него два других блока: source product_main и source product_delta:

Мы создаём два новых источника данных: product_main и product_delta. Каждый из этих источников имеет свой собственный запрос в опции sql_query. Источник product_main будет получать из базы данных все записи, а источник product_delta только те, ModifiedDate которых больше последней даты обновления основного индекса в таблице ProductIndexLog.

Кроме этого, в таблицу ProductIndexLog нам необходимо сохранять информацию о том, что основной или дельта-индекс обновились. Помимо опции sql_query, блок source может иметь ещё три опции, которые могут содержать sql-запросы: sql_query_pre, sql_query_post и sql_query_post_index. Они позволяют выполнить дополнительные sql-запросы до и после выполнения основного запроса и после полного обновления индекса. Мы используем опцию sql_query_post_index, чтобы после каждой индексации добавлять новую запись в таблицу ProductIndexLog.

Шаг 3: Настройка индексов в файле конфигурации

Заменим в файле конфигурации существующий блок index product на три блока: index products_main, index product_delta и index product:

Тут мы создаём два индекса, использующих источники данных product_main и product_delta, а также один распределённый индекс product, который просто ссылается на два других.

Шаг 4: Обновление основного индекса

Запустим командную строку от имени администратора и выполним команду для обновления основного индекса:

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

Теперь подключимся к Sphinx и попробуем найти товары по слову «men»:

select * from product where match('men');

Мы нашли семь товаров. Тот же самый результат мы получим, если вместо индекса product будем искать в индексе product_main. В индексе же product_delta результатов по такому запросу не будет, поскольку его мы ещё не обновляли.

Шаг 5: Обновление дельта индекса

Перед тем, как обновлять дельта-индекс, нам необходимо изменить данные в базе.

Первым четырём товарам изменим название и немного поднимем цену при помощи следующего скрипта (не забудем обновить колонку с датой изменения):

Последние три товара вообще удалим из индекса, присвоив колонке FinishedGoodsFlag значение 0:

После того, как данные в базе изменены, снова запустим командную строку от имени администратора и выполним команду для обновления дельта-индекса:

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

Теперь ещё раз подключимся к Sphinx и повторим поиск товаров по слову «men»:

select * from product where match('men');

Мы получили немного не тот результат, которого хотели добиться. Товары, которые мы обновили в базе, обновились и в индексе. Но товары, которые мы хотели удалить из индекса всё равно в нём остались.

Попробуем снова поискать товары, но уже по фразе «men sports»:

select * from product where match('men sports');

Теперь мы получили совершенно не тот результат, которого ожидали. Несмотря на то, что мы изменили товары в базе и обновили дельта-индекс, Sphinx нашёл и вернул старые значения.

Шаг 6: Kill-lists

В последних запросах мы получили неправильные результаты. Дело в том, что когда Sphinx ищет документы в распределённом индексе, он по сути ищет в каждом индексе, на который тот ссылается. После того, как результаты получены, они объединяются в том же порядке, в котором определены локальные индексы внутри распределённого.

В нашем случае, Sphinx сначала ищет в индексе product_main, а потом в индексе product_delta, после чего объединяет результаты.

Получается, что когда мы ищем товары по слову «men», то в основном индексе находятся всё те же семь товаров. Четыре из них (у которых мы обновляли название и цену) также находятся и в дельта индексе. Они тоже добавляются к результатам поиска и по атрибуту id заменяют собой ранее найденные товары. Но при этом другие три товара, которые нашлись только в основном индексе, из результатов поиска не удаляются.

Похожая ситуация происходит и в случае поиска по фразе «men sports». Старые товары находятся в основном индексе, но не находятся в дельта-индексе, т.к. не соответствуют поисковой фразе (мы же изменили им название). В итоге, результаты поиска полностью состоят из товаров, которые находятся в основном индексе.

Чтобы исправить ситуацию, добавим в блок source product_delta опцию sql_query_killlist:

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

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

select * from product where match('men');

select * from product where match('men sports');

Теперь всё работает так, как мы и хотели.

Заключение

В этой статье я рассказал про создание индексов с использованием в качестве источника базы данных MS SQL Server, а также рассказал про дельта-индексы и kill-list`ы.

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

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