Игорь Чакрыгин Игорь Чакрыгин

У любой задачи существует по крайней мере одно очевидное и невероятно простое для понимания неправильное решение

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), которые играют особую роль в группировках.

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

57 комментариев

  1. Здарова, автор!
    Я делаю как вы, но у меня выскакивает ошибка -

    WARNING: attribute 'name' not found - IGNORING
    ERROR: index 'order': No fields in schema - will not index.

    ---------- Это в файле config.txt

    source base
    {
    type = mssql
    sql_host = localhost
    sql_db = MySearch
    sql_user = search
    sql_pass = 1111
    }

    source order_base : base
    {
    sql_field_string = name
    }

    source order : order_base
    {
    sql_query = \
    select \
    sName AS 'name' \
    from MySearch.dbo.tOrder
    }

    index order
    {
    source = order
    path = c:/sphinx/data/index/order
    }

    ----------

    Как решить?

    ОтветитьУдалить
    Ответы
    1. Здарова!

      Первой колонкой в sql_query должен быть целочисленный id документа.
      sql_query = select id, sName AS 'name' from MySearch.dbo.tOrder

      Скорее всего из-за этого.

      Удалить
    2. Спасибо за оперативность.
      Теперь сам запрос не проходит (не возвращает OK), возвр. FAIL.
      Запрос такой теперь
      source order : order_base
      {
      sql_query = \
      select \
      idOrder AS 'id' \
      sName AS 'name' \
      from MySearch.dbo.tOrder
      }

      а здесь я id не пишу, т.к., как я понял он автоматом его подставляет
      source order_base : base
      {
      sql_field_string = name
      }
      ---------------------------

      ERROR: index 'order': sql_query: [Microsoft][SQL Server Native Client 10.0][SQL Server]=хяЁртш..... "sName". <DSN=odbc://search:***@localhost:0/MySearch

      Удалить
    3. Ну а запятые между колонками кто будет расставлять? =)
      idOrder AS 'id', \
      sName AS 'name' \

      sql_query должен быть корректным sql запросом, т.е. он должен нормально выполняться в Sql Server Managment Studio. Только при переносе в конфиг сфинкса в местах переноса строк добавляется "\"

      Удалить
    4. :)) в самом деле.
      Вроде все прошло успешно, в папке index какие-то файлы появились, тока в конце пару предупреждений возвращает -
      WARNING: failed to scanf pid from pid_file 'c:/sphinx/data/searchd.pid'.
      WARNING: indices NOT rotated
      --------
      Файл searchd.pid есть, и на своем месте, он пустой

      Удалить
    5. Предупреждения, потому что нужно индексацию запускать с правами администратора. Иначе каждый раз придётся вручную перезапускать Сфинкс.

      Удалить
    6. так, вроде я с правами админа..
      вот еще проверил, я админ, может еще чето надо включить, подключить?

      Удалить
    7. Я обычно создаю bat-файл с командой и запускаю его правой кнопкой мыши -> "Запуск от имени администратора".

      Удалить
    8. а где его нужно создать и для чего он?
      вообще есть такие вопросы:
      второй вопрос, вот я пишу на РНР, и хотелось бы запускать индексацию раз в сутки ч/з cron (в этом проблемы нет), вопрос в том, что можно ли такое сделать? ну чтобы каждый раз не писать имена таблиц в congig.txt.
      3-ий - ну вот есть у меня индекс, как я понял, и как использовать все это(?), теперь если я введу поисковую фразу поиск будет автоматом переходить из mssql в sphinx?
      4-ый - поля, по которым строится индекс должен быть fulltext или это неважно?

      Удалить
    9. Через что ты запускаешь индексацию? Через консоль? Тогда запусти консоль с правами администратора правой кнопкой мыши -> "Запуск от имени администратора".
      А bat-файлы - это та же командная строка, только команды пишешь в файл и можешь запускать повторно в Windows. Поищи в интернете, уж про это там точно написано.

      2. Да, создаёшь bat-файл и ставишь в cron его запуск с нужной периодичностью. А в этот файл пишешь команды для индексации.

      3. Для mysql на mysql.com наверняка есть коннектор для PHP. Нужно его скачать и подключить к своему коду. Поиск автоматом никуда переходить не будет. Sphinx - это по сути как ещё одна БД в приложении. Можно искать в БД через like, а можно в Sphinx через match. Как в коде напишешь, так и будет.

      4. Нет не должны. fulltext - это для полнотекстового поиска по БД. Sphinx сам по себе движок полнотекстового поиска.

      Удалить
    10. Спасибо, помог! ч/з .bat ваще четка )
      Смари, по 3-му пункту непонятно, как сделать, чтобы поисковая фраза введенная в форму (форма и скрипт уже готовы, но они ищут пока просто в БД MS SQL) искалась Сфинксом?
      То что написал ничего ни о чем мне говорит, т.к., впервые сталкиваюсь с этой машиной. Может у тя ссылка, где все енто объясняется. Или, не намереваешься ли ты про эту статью написать? А так в консоли все ок, а как на сайте чтобы работало, тоже ведь отдельный вопрос.
      разъясни пожалста этот момент

      Удалить
    11. Про подключение к Сфинксу уже писал статью, только для .NET, может поможет. Вобщем-то там всё просто. Подключаемся Сфинксу, как к обычной БД и делаем запросы.

      Удалить
  2. млиин..., не могу протестировать поиск по полученным индексам.
    Игорь, хелп. Покажи пожалста, как хотя бы в консоли протестировать поиск, какие команды надо ввести.
    Ищу в сети, много умных статей, а тупо как искать не пишут (кстати и настройка сфинкса по разному у разных авторов).
    --------
    Пробую на пхп, вот так:

    include('c:\sphinx\api\sphinxapi.php');
    // Создадим объект - клиент сфинкса и подключимся к нашей службе
    $cl = new SphinxClient();
    $cl->SetServer( "localhost", 9306 );

    // Собственно поиск
    $cl->SetMatchMode( SPH_MATCH_ANY ); // ищем хотя бы 1 слово из поисковой фразы
    $result = $cl->Query("контракты"); // поисковый запрос

    // обработка результатов запроса
    if ( $result === false ) {
    echo "Query failed: " . $cl->GetLastError() . ".\n"; // выводим ошибку если произошла
    }
    else {
    if ( $cl->GetLastWarning() ) {
    echo "WARNING: " . $cl->GetLastWarning(); // выводим предупреждение если оно было
    }

    if ( ! empty($result["matches"]) ) { // если есть результаты поиска - обрабатываем их
    foreach ( $result["matches"] as $product => $info ) {
    echo $product . "
    "; // просто выводим id найденных товаров
    }
    }
    }
    exit;
    --------
    Возвращает ошибку: Query failed: failed to send client protocol version.
    По всей видимости он не может подключиться к сфинксу, тока не пойму почему, вроде пути верные. Пробовал подключиться залив доки сфинкса в сайт, та же история
    ------
    Че делать, как искать?

    ОтветитьУдалить
    Ответы
    1. поменял порт с 9306 на 3306, ответ поменялся на такой -
      Query failed: failed to read searchd response (status=2613, ver=11829, len=775042304, read=92).
      не понятка все равно

      Удалить
    2. Подключение через консоль я в предыдущей статье описывал, где "Установка и настройка"

      В моих примерах я подключался через SphinxQL.
      Для этого в конфиге в searchd указывал только протокол mysql:
      listen = 127.0.0.1:9306:mysql41

      Ты же подключаешься через SphinxAPI.
      Для этого нужно указывать просто:
      listen = 127.0.0.1:9304

      И подключаться к порту 9304. http://sphinxsearch.com/docs/2.1.1/conf-listen.html

      Но SphinxAPI содержит меньше возможностей, чем SphinxQL, поэтому лучше использовать второй.
      Почитай это: http://habrahabr.ru/company/sphinx/blog/61222/
      Там чётко написано: Теперь их вдобавок к этому можно делать через любой клиент для MySQL, включая всем привычный клиент в командной строке, PHP-шные вызовы mysql_connect() и mysql_query(), Perl DBI, и т.д. При этом он совместим со всеми клиентами, начиная от MySQL 4.1 и по настоящий момент.

      Т.е. ты к Sphinx будешь подключаться как к обычному mysql через mysql_connect и mysql_query. В php это же всё встроено!

      Удалить
    3. бб...., что то не так..
      Игорь, у СУБД - MS SQL SERVER, может для него другие настройки, порты нужны?
      В этой статье ты приводишь пример запроса в консоли mysql, в SQL SERVER 2008 я не нашел командной строки.
      Не мог бы показать пример запроса в обычной командной строке. Индекс строится нормально по инструкции, которую ты привел, а вот поискать не могу))
      много статей в сети, ну как выглядит физически запрос для SQL SERVER нет (ОС WINDOWS XP)

      Удалить
    4. Блин, забудь про MS SQL SERVER.
      Ты его уже проиндексировал, с ним тебе ничего делать теперь не нужно. Все данные из него уже перекочевали в Sphinx, когда ты запустил индексацию. Индексация - это такой процесс, когда данные берутся из БД MS SQL Server и переносятся в Sphinx (Считай, что в его собственную БД).

      Теперь тебе нужно подключиться к Sphinx, чтобы эти данные запросить.

      Ты же используешь php? Ты когда-нибудь подключался из php к mysql? Считай, что Sphinx - это БД mysql (И ещё раз, это никакого отношения к MS SQL Server не имеет) и подключайся к нему как к MySql. Sphinx ЭМУЛИРУЕТ MySql. Поэтому, чтобы к нему подключиться нужно считать, что Sphinx - это MySQL. Поэтому я в статье и показываю, что к Sphinx можно подключаться через консольный клиент для MySQL. Попробуй это сделать.

      Если тебе нужно подключиться из php, то нужно использовать функции mysql_connect и mysql_query, потому-что, ещё раз =), Sphinx ЭМУЛИРУЕТ MySql и к нему нужно подключаться как к этой БД =)

      Надеюсь теперь понятно =)

      Удалить
    5. > Блин, забудь про MS SQL SERVER.
      - Забыл :)

      > Теперь тебе нужно подключиться к Sphinx, чтобы эти данные запросить.
      - есть такое, подключился

      > Дальше интересно, -
      Если в командной строке я пишу такое - c:\sphinx\bin\search --config c:\sphinx\conf.txt computer -- то выводится одна запись, в которой есть слово computer
      А если в консоли mysql сделать выборку - select idOrder, sName from MySearch.tOrder limit 5; -- то говорит, что для заданного пользователя операция недоступна (Select command denied to user ''@'localhost' for table torder).
      Хотя конфиг у меня тот же, что и при индексировании (и как теперь обращаться к эмулированному MySQL не пойму я. Я не знаю как эта БД называется, сохранила ли она названия таблиц из старой БД. где можно проверить эти данные?):
      ------

      searchd
      {
      listen = 127.0.0.1:9304
      pid_file = c:/sphinx/data/searchd.pid
      log = c:/sphinx/data/log/log.txt
      query_log = c:/sphinx/data/log/query_log.txt
      binlog_path = c:/sphinx/data/binlog/
      }

      source base
      {
      type = mssql
      sql_host = localhost
      sql_db = MySearch
      sql_user = search
      sql_pass = 1111

      sql_port = 9304
      }

      source order_base : base
      {
      sql_field_string = name
      }

      source order : order_base
      {
      sql_query = \
      select \
      idOrder AS 'id', \
      sName AS 'name' \
      from PoiskTorgov.dbo.tOrder
      }

      index order
      {
      source = order
      path = c:/sphinx/data/index/order
      }

      ------

      Удалить
    6. >> есть такое, подключился
      Нет, ты ещё никуда не подключился, иначе бы ты мог делать запросы =)

      В searchd сделай две строки:
      listen = 127.0.0.1:9304
      listen = 127.0.0.1:9306:mysql41

      Первая (9304) для SphinxAPI, вторая (9306) для SphinxQL.

      Не надо в командной строке писать такое:
      c:\sphinx\bin\search --config c:\sphinx\conf.txt computer

      Вместо этого сделай всё как описано тут: http://chakrygin.ru/2013/03/sphinx-install.html
      Смотри начиная с "Шаг 5: Первый запрос" - это и есть подключение к Sphinx, который эмулирует MySQL. При этом там нет никаких БД, только хост (127.0.0.1) и порт (9306). Sphinx - это и есть одна БД. Индексы - это таблицы этой БД. Атрибуты индекса - колонки БД.

      Вообще рекомендую ещё разок внимательно перечитать ту статью и следующую за ней.

      Удалить
    7. > В searchd сделай две строки:
      - сделал. теперь searchd в config.txt выглядит так:
      searchd
      {
      listen = 127.0.0.1:9304
      listen = 127.0.0.1:9306:mysql41
      pid_file = c:/sphinx/data/searchd.pid
      log = c:/sphinx/data/log/log.txt
      query_log = c:/sphinx/data/log/query_log.txt
      binlog_path = c:/sphinx/data/binlog/
      }

      > Вместо этого сделай всё как описано тут: http://chakrygin.ru/2013/03/sphinx-install.html
      - сделал, все прошло ок, прямо как у тебя.

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

      Я делаю так:
      - 1) запускаю sphinx ( Панель управления - Администрирование - Службы ), нажимаю запустить (перезапустить)
      - 2) Захожу сюда C:\WebServers\usr\local\mysql-5.5\bin, тут запускаю mysql.exe (входит в пакет Денвера). Запускается консоль mysql, пишу команду - select * from order (у меня же индекс называется - order), а в ответ:
      - You have an error... near 'order' at line 1

      Пробую сделать запрос в командной строке предварительно написав такое -
      c:\mysql\bin\mysql -h 127.0.0.1 -P 9306

      здесь уже ответ - sphinxql: syntax error, unexpected ORDER, expecting IDENT near 'order'

      А запрос show status; проходит нормально и там и тут...
      ---------
      че за фигня, запарился, куда копать?

      Удалить
    8. Отлично чувак! Ты на правильном пути! =)

      C:\WebServers\usr\local\mysql-5.5\bin - это скорее всего не то. Если это денвер, то ты скорее всего подключаешься к MySQL, который в него в ходит. Ты же нигде там не указываешь порт 9306.

      Второе правильно. Ошибку выдаёт, потому что order - это ключевое слово в Sphinx и тебе надо брать его в кавычки:

      select * from `order`;

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

      И ещё, если у тебя в индексе есть кирилица, то нужно:
      1. В конфиге у этих индексов ставить utf-8, как у меня.
      2. Подключаться через командную строку такой командой:
      C:\mysql\bin\mysql.exe -h 127.0.0.1 -P 9306 --default-character-set=utf8

      Удалить
  3. Добрый день.

    Я реализовал описанный в статье пример, за одним исключением - я подключился к своей MSSQL базе к табличке streets, которая имеет поля ID и Name.
    В поле Name содержатся названия улиц на русском языке. Когда я сделал в mysql запрос select * from streets, то получил в результате вместо названий улиц псевдографику.
    Подскажите что и где нужно докрутить, чтобы результат возвращался русскими буквами? Привожу ниже описание источника из конфига.

    source base
    {
    type = mssql
    sql_host = host
    sql_db = mybase
    sql_user = user
    sql_pass = password
    mssql_unicode = 1
    }
    source streets : base
    {
    sql_field_string = name
    sql_query = \
    select id, name\
    from dbo.streets
    }
    index streets
    {
    source = streets
    path = c:/sphinx/data/index/streets
    charset_type = utf-8
    }

    ОтветитьУдалить
    Ответы
    1. Добрый день.

      1. В конфиге у этих индексов ставить utf-8, как у меня.
      2. При подключении через консоль указывать кодировку в параметрах командной строки:
      C:\mysql\bin\mysql.exe -h 127.0.0.1 -P 9306 --default-character-set=utf8
      3. При подключении из кода, например из C#, указывать кодировку в строке подключения:
      Server=127.0.0.1;Port=9306;Character Set=utf8

      Удалить
    2. Спасибо, помогла рекомендация №2, но правда при =utf8 вывело нечитаемые слова - русские буквы со значками вопроса. Попробовал заменить на =cp1251 и вуаля mysql вывел русские названия.

      Удалить
    3. Это как бы не отдельные рекомендации, а последовательные пункты =)

      Есть подозрение, что у тебя в SQL Server строки хранятся не в юникоде.
      Какие типы данных ты используешь: varchar и text или nvarchar и ntext?

      Если первое, то наверное надо убрать mssql_unicode, тогда должно работать с utf8.
      Я не уверен, честно говоря. Я тестировал именно с nvarchar, mssql_unicode=1 и =utf8.

      Удалить
  4. Заработало (правда криво немного)) ). Игорь ты красава! Спасибо за терпение и помощь:)
    Даже в РНР запрос нормально отработал, тока почему-то при обновлении страницы снова показывает Query failed, через несколько обновлений опять выводит результат на экран. не знаешь из-за чего может быть?
    Работает в командной строке после этой команды - c:\mysql\bin\mysql -h 127.0.0.1 -P 9306
    Тока с кодировкой проблема, как и у товарища Konstantin S, пробовал добавить кодировки и в конфиг и в командной строке, не помогает.
    какие у меня кодировки и настройки -
    - В БД MS SQL у меня все в кодировке cp1251,
    - поля имеют тип (nchar, nvarchar),
    - в конфиге прописал строки mssql_unicode = 1 и charset_type = utf-8
    Проверил индексный файл order.sps, так там все в крякозябах, может из-за этого? может на этапе формирования индекса как-нибудь правильно кодировку настроить? только как?

    ОтветитьУдалить
    Ответы
    1. может кроме Query failed что-то пишет?
      Если у тебя поля имеют тип nchar, nvarchar, что это юникод, а не cp1251

      попробуй подключаться командами:
      C:\mysql\bin\mysql.exe -h 127.0.0.1 -P 9306 --default-character-set=utf8
      C:\mysql\bin\mysql.exe -h 127.0.0.1 -P 9306 --default-character-set=cp1251

      Удалить
    2. > может кроме Query failed что-то пишет?
      - да, конечно - Query failed: failed to send client protocol version.
      Соединяюсь со Сфинксом так:
      include('c:\sphinx\api\sphinxapi.php');
      $cl = new SphinxClient();
      $cl->SetServer( "localhost", 9304);
      протокол 9306 также выдает ошибку (нормальный результат даже ни разу не выдает).

      > попробуй подключаться командами: ...
      - сделал и так, и так. не помогает. Кроме этого, Как уже писал в файле order.sps сплошь были крякозябы, я сделал индекс заново, убрав из конфига строки mssql_unicode = 1 и charset_type = utf-8,
      после чего в файл записались русские слова, но при выводе та жа х.., тока крякозябы поменяли вид. Есть еще команды для выравнивания кодировки.
      У тя же тоже говоришь юникод был, как ты сделал, по пункто напиши пожалста

      Удалить
    3. Даже не знаю, SphinxAPI я не пользовался. Тебе придётся погуглить =)
      Но из php ты можешь тоже SphinxQL использовать. Поищи, как из php к mysql подключаться, только подключайся не к mysql, а к sphinx.

      Не-не, charset_type = utf-8 нужно оставить.
      Попробуй убрать только mssql_unicode.
      Какие типы данных ты в MS SQL используешь: varchar и text или nvarchar и ntext?

      Удалить
    4. > Какие типы данных ты в MS SQL используешь: varchar и text или nvarchar и ntext?
      - nvarchar и ntext.
      В консоли чет кодировка никак не работает. в РНР все ок, кроме
      моросни sphinxapi.
      На счет настройки SphinxQL, на хабре нашел такое - / Как уже сконфигурировать
      и попробовать? Нужно добавить буквально одну строчку с указанием
      адреса интерфейса, порта и собственно имени протокола в sphinx.conf:
      listen = localhost:3307:mysql41 /
      Теперь пойми какой протокол использовать ( 3307, 1433, 9304, 9306, 9312... )?
      Ну я без всяких настроек, тупо подключился ч/з порт 9306 к сфинксу (или не к сфинксу:)) указал в соединении имя БД из MS SQL, его же имя пользователя и пароль, вроде подсоединился, если это был бы не сфинкс, то наверное ругался бы на ф-ии mysqli_.. ). так вот? вроде работает без всяких срывов как в sphinxapi. Вопрос - я на самом деле так подключаюсь к сфинксу или все таки это -
      $dbSphinx = mysqli_connect('127.0.0.1','search','1111','MySearch',9306); - соединение с MS SQL? чето запутался))

      Удалить
    5. >> В консоли чет кодировка никак не работает.
      Попробуй с mysql.com скачать самую свежую версию mysql и mysql.exe использовать оттуда.
      Больше не знаю, что посоветовать. С последним сфинксом и mysql у меня, и не только у меня, всё работает =)

      >> Теперь пойми какой протокол использовать

      Это не протокол! Протокол это mysql41 или, если ничего нет, то считается sphinx.
      3307, 1433, 9304, 9306, 9312 - это всё порты на которых поднимается Sphinx-сервис.
      Какой порт ты назначишь в конфиге в блоке searchd, к такому порту тебе и нужно подключаться. А назначить можно любой порт, хоть 13666, если нравится.

      >> казал в соединении имя БД из MS SQL, его же имя пользователя и пароль.

      Ничего этого не надо использовать. Нужен только ip, порт и кодировка. Всё остальное выбрось. У сфинкса нет никаких БД, пользователей итп.

      >> я на самом деле так подключаюсь к сфинксу

      Похоже не то.

      Удалить
    6. > Это не протокол!
      - я же говорю - чето запутался))

      > Похоже не то.
      - да, точно, убрал лишнее.

      ------------

      Щас перешел на дельта-индексы, пару вопросов есть:
      1) Где ты пишешь команды для создания и обновления таблиц? Когда обновляешь, нужно стирать команды на создание?
      2) Для чего нужно чтобы в дельта-индексе были записи из основного индекса? Какой смысл тогда копирования, в добавок его масса увеличивается, а ведь он как создается для того, что в нем будут новые индексы и соответственно будет легким.
      3) У меня индекс весит 11,6Мб, а сама база ~76Мб. Почему так много весит индекс только по одному полю одной таблицы? Что же будет когда я по всем нужный таблицам построю индекс? Есть какой-нибудь метод (какая-нить команда может), что пресовались данные, или что там надо чтобы индекс так страшно не разрастался?
      4) Нужен общий совет:
      Смотри, у меня БД очень большая (5-15Тб (терра)) (тестирую пока на маленькой копии). Есть 4 основные таблицы (Заказы, Организации, Документы. Документы - очень большая и увесистая таблица связанная с табл. Заказов. И Лоты - также связаны с Заказами).
      Как посоветуешь построить индекс, сколько их сделать (основных и дельта), с каким шагом, на каждую таблицу нужно ли свою таблицу для дельта-индексов создавать(?). Ну чисто твое виденье

      Удалить
    7. 1. Пишу в Sql Server Managment Studio, потом переношу в конфиг сфинкса.

      2. Смысл такой, что основной индекс редко обновляется, поэтому содержит устаревшие данные. Дельта содержит только обновленные данные. поэтому когда ты запрашиваешь данные из основного+дельта, то обновленные из дельты имеют приоритет перед основным, т.к. они более новые.

      А устаревшие данные из основного индекса невозможно удалить. Только полностью переиндексировать, а это может занимать много часов на больших объёмах данных.

      Дельта индексы нужны, чтобы можно было все изменения отображать в поиске почти в реальном времени. Если тебя не беспокоит, что данные в индексе будут отличаться от БД, например, в течении суток, то дельта индексы можно не использовать.

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

      Но ты уверен, что ничего лишнего в индекс не загоняешь? =)

      4. Индексировать скорее нужно то, где ты хочешь искать по разным поисковым запросам.
      Например, поиск товаров или документов по названию и описанию. Экспериментируй =)

      Удалить
    8. > 1. Пишу в Sql Server Managment Studio, потом переношу в конфиг сфинкса.
      - Я наверное не правильно спросил. Мне нужно таблицы создавать в основной БД тоже или тока в Сфинксе? И что переводишь в конфиг сфинкса - саму таблицу, или индекс?

      > Если тебя не беспокоит, что данные в индексе будут отличаться от БД, например, в течении суток
      - Я как раз хочу включать индексирование 1 раз в сутки (ночью). Но как же не использовать дельта-индексы(?), мне каждый день перелапатывать гигабайты инфы из-за пару десятков тысяч строк?

      > Но ты уверен, что ничего лишнего в индекс не загоняешь? =)
      - Да вроде ничего лишнего не беру, вот команда -
      c:\sphinx\bin\indexer order --config c:\sphinx\data\config.txt --rotate --print-queries
      вот запрос -
      sql_query = \
      select \
      idOrder AS 'id', \
      sName AS 'name' \
      from MySearch.dbo.tOrder
      Названия (sName) просто достаточно длинные.

      > Например, поиск товаров или документов по названию и описанию.
      - Ну допустим я ищу фразу по заказам. Сначала я смотрю в названиях, потом Лотах, а после в Документах. Т.е. отдельно по Лотам и Документам ничего не ищется

      > Экспериментируй =)
      - Это конечно, просто хочется в голове была ясная картина)

      Удалить
    9. >> Мне нужно таблицы создавать в основной БД тоже или тока в Сфинксе?

      В сфинксе нет таблиц, только индексы.
      В БД нет индексов, только таблицы.
      Как ты собрался создавать таблицы в сфинксе?
      Я переношу в конфиг сфинкса запросы. См. sql_query, sql_query_post, sql_query_post_index.

      >> мне каждый день перелапатывать гигабайты инфы из-за пару десятков тысяч строк?

      Тогда, наверное, стоит подумать о дельта-индексах. =) Попробуй повторить пример из статьи, я думаю, тебе станет понятней.

      >> Ну допустим я ищу фразу по заказам. Сначала я смотрю в названиях, потом Лотах, а после в Документах. Т.е. отдельно по Лотам и Документам ничего не ищется

      Значит тебе нужен ОДИН индекс с заказами. В КАЖДОМ документе этого индекса должна содержаться ВСЯ информация, по которой ты хочешь искать ЭТОТ конкретный заказ: Названия всех лотов, их описание, документы и т.п.

      >> Это конечно, просто хочется в голове была ясная картина)

      О, это только начало =)

      Удалить
    10. мм.., я так и не понял.. Мне в БД MS SQL создать дополнительную таблицу для отслеживания обновлений индексов? Если да, то как эта таблица (НЕ индекс в сфинксе) будет обновляться?

      > Значит тебе нужен ОДИН индекс с заказами....
      - ок, тогда такой вопрос - очень важно не терять связь м/у заказами, его документами и лотами (документы и лоты - это тоже таблицы). Документы и Лоты связаны с Заказами через родительское id (например, - parent_id).
      Так вот, если я допустим фразу нашел в документе (а в названии самого заказа и названии лота не нашел), то при выводе в браузер, должен выведен ЗАКАЗ, с которым связан данный документ. Можно ли такое сделать на сфинксе? Если да нужны ли дополнительные какие-нибудь операции с таблицами, индексами?

      Удалить
    11. Таблица - это один из вариантов отслеживания индексаций.
      Её будет обновлять Sphinx через sql_query_post_index.

      Если индекс по заказам, то Sphinx и верёнт тебе id заказов, к которым прикреплены документы в которых у тебя совпали какие-то поля.

      Удалить
  5. Добрый день!
    Все получилось, теперь вопросы о связи фильтра по таблицам MS SQL и полнотекстового поиска в Сфинксе.
    Есть большая форма с множеством пунктов, до Сфинкса я строил запрос проверяя участие пункта в фильтре, в том числе и поисковой строки. Теперь же получается я слова ищу как бы в отдельной БД, а сам фильтр по основной БД MSSQL.
    Вопросы:
    - 1) Что первее делать, поиск фразы в Сфинксе и найденные фильтровать (ну например, по региону, дате, сумме...) или, наоборот? И чесна говоря незнаю как их соединить:)
    - 2) Или можно (а главное, правильно ли?) добавлять поля из БД, по которым полнотекстового поиска не будет (по дате например, слова не поишешь), зато фильтровать прямо в запросе к индексам Сфинкса?
    -----------------------------------------------
    >>> Если индекс по заказам, то Sphinx и верёнт тебе id заказов, к которым прикреплены документы в которых у тебя совпали какие-то поля.
    - а нужно ли использовать join'ы и т.п, как написано в этой статье http://chakrygin.ru/2013/04/multi-valued-attrs-and-joined-fields.html ??

    ОтветитьУдалить
    Ответы
    1. Можно всё выгружать в сфинкс и прямо там фильтровать по дополнительным критериям, помимо полнотекстового поиска. При этом можно получать от сфинкса только id, а потом по этим id обращаться в бд и получать остальные данные.
      ---
      Можно использовать. А можно выгружать всё в xml и индексировать не БД, а xml. Но об этом я ещё отдельно напишу пост.

      Удалить
  6. А можно ли как-нибудь сделать так, чтобы Сфинкс искал по корням слов.
    Например, я ищу "контракты на выполнение" - все ок. Если я введу - "контрак на выполн", то ничего не выводится, точнее выводится те записи. где есть "на".
    Я на Хабре нашел такую команду - morphology = stem_ru, пересоздал индекс, но ничего не изменилось.
    Может ты сталкивался в работе с этой задачей?
    И заметил, что Сфинкс по умолчанию возвращает 20 строк, можно ли это как-то регулировать?

    ОтветитьУдалить
    Ответы
    1. Сталкивался. Это как раз оно и есть. Стеммер будет отбрасывать окончания, а не искать по корню. Т.е. когда ты будешь искать "контракты", он будет обрезать до "контракт".

      Да, есть оператор limit. Вообще почитай документацию, много интересного найдёшь =)

      Удалить
    2. > Вообще почитай документацию, много интересного найдёшь =)
      - Как раз про стеммы почитал там, но нихрена не понял (для кого создают эти документации, неужели нельзя написать: Скачать тут, установить так, запустить так), целое сочинение написали, а толку.
      Вобщем нужен совет как и куда прикрутить этот стеммер, откуда скачать. надо ли переустонавливать индекс?

      Удалить
    3. Просто в конфиге прописать morphology = stem_ru, пересоздать индекс и перезапустить Sphinx.

      Удалить
  7. Даров!
    Вопрос о SphinxAPI / SphinxQL / SphinxSE
    Вот ты хвалишь SphinxQL, в сети находил статьи (на том же хабре), что SphinxSE хорошая вешчь, а я вот пользую апи, ну потому что наверное, во-первых - удобно (можно настроить поиск по точному совпадению или наоборот, вес задать...), во вторых - не понимаю плюсы 2-х других вариантов.
    Разговоров много, что те лучше, а что конкретно они делают, ну кроме того, что они могут использоваться в тех языках, для которых апи сфинкса не существует?
    Как использовать тот же SphinxQL? Как подключить?
    Если его использовать мне придется все ф-ии из SphinxAPI с нуля писать?
    И как считаешь что лучше SphinxQL или SphinxSE? или это не сравнимые вещи?

    ОтветитьУдалить
    Ответы
    1. SphinxSE можно использовать только вместе с MySQL. Эта штука подключается к БД как плагин и через неё, вроде бы, можно искать в Sphinx прямо из MySQL.

      >> можно настроить поиск по точному совпадению или наоборот, вес задать...
      Всё это можно делать из SphinxQL. Более того, SphinxQL наоборот поддерживает некоторые функции (особенно новые), которые SphinxAPI делать не умеет. Какие точно - не помню, нужно в документации рыться.

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

      >> Как использовать тот же SphinxQL? Как подключить?
      Уже несколько раз написал выше. Нужно подключиться к Sphinx, как к БД MySQL, указав только хост и порт (и кодировку, если надо), без логинов и паролей. А дальше выполнять обычные запросы.

      >> Если его использовать мне придется все ф-ии из SphinxAPI с нуля писать?
      Вообще ничего не надо писать. Подключился, выполнил запрос, получил результаты.

      >> И как считаешь что лучше SphinxQL или SphinxSE?
      Это разные вещи.

      Удалить
  8. Большая благодарность за такой подробный материал, у Вас так доходчиво объяснено. Ваше терпение и Талант на редкость, огромное спс

    ОтветитьУдалить
  9. Вы могли бы прояснить мне(жуткий новичок), почему отличается кол-во в 1 и во 2 случае

    1. Если через MуSQL сделать запрос

    SELECT COUNT(*) FROM `catalog_catalog` WHERE (`name` LIKE '%Therm%') \
    AND `price` > 0 AND `num` > 0 ORDER BY price;
    То результат 87

    2. запрос через Sphinx:
    SELECT * from catalog_catalog_idx WHERE MATCH('@name Therm');
    То результат 3

    Вообще можно настроить чтобы sphinx выдавал такой же результат как и первый?

    sphinx же в данном случае ищет Therm Therms а то чтобы в результат входил и ThermalTake он его пропускает, а вот mysql его подхватывает, в какую сторону хоть копать

    ОтветитьУдалить
  10. >> почему отличается кол-во в 1 и во 2 случае

    Потому что из БД возвращаются все строки, где в колонке name есть подстрока "Therm". Это может быть "Thermal", "Thermometer" и даже "ABCTherm".

    Сфинкс так не работает. Он ищет не подстроки, а слова в разных словоформах. Т.е. он по запросу "Печенька" может найти "Печеньки", "Печеньку", "Печенькой" итп. Но не найдёт эти слова по слову "Печень", потому что это разные слова, а не разные формы одного и того же слова.

    >> Вообще можно настроить чтобы sphinx выдавал такой же результат как и первый?



    Теоретически можно, но работать такой поиск будет не быстрее чем поиск по БД. Сфинкс лучше использовать именно для полнотекстового поиска. Например по запросу "Вишнёвые печеньки" можно найти текст "Печенька с вишнёвым вареньем - это здорово!" несмотря на то, что слова в других словоформах и переставлены местами. MySql так искать не умеет.

    ОтветитьУдалить
  11. спасибо прояснили, то что очень нужно было, тогда наверное мне Sphinx не очень подойдет (, не скажете может мне лучше mongodb использовать? чтобы поиск не нагружал основную базу?

    ОтветитьУдалить
  12. Смотря какой поиск нужен. Если, например, поиск товаров в интернет магазине, то Сфинкс - это самое то. Он даёт наиболее релевантные ответы на запросы пользователя.
    Если нужен поиск по подстрокам, то можно индексы в БД настроить попробовать.

    ОтветитьУдалить
  13. Да в том то и дело что для магазина, но проблема в том что придет пользователь и захочет поискать "1GB" у меня mysql выдает 250 запросов, а sphinx 204, все-таки где-то утечка, хотя я в sphinx sql_query настроил такой же как и у mysql, за исключением LIKE =(, вот и думаю что с этим делать, оставить как есть или ....?

    ОтветитьУдалить
  14. Надо смотреть конкретно, что именно Сфинкс не находит. Если что, его и настроить можно.
    Если он не найдёт "101GB" по запросу "1GB", то это правильно с точки зрения пользователя.
    К тому же если пользователь будет искать "Флешки 1GB", а в БД товар называется "1GB Флешка", то будет наоборот. MySql товар не найдёт, а Сфинкс найдёт. Так что всё компенсируется =) Я уже не говорю про намного большую скорость поиска.

    ОтветитьУдалить
  15. Спс после вашего объяснения стало ясно как днем, буду пробовать, уже только наверное завтра, а вот запросы mysql типа
    SELECT id FROM catalog_catalog WHERE (`name` LIKE '%1GB%'') AND (`name` LIKE'%видеокарта%' ) AND `price` > 0 AND `num` > 0, (но зато не влияет расположение слов)
    это уже наверное будет дольше обрабатываться чем в sphinx

    ОтветитьУдалить
  16. Добрый день,

    Делаю все по инструкции:) добавил sql_query_post_index для дельта индексирования с использованием доп таблици, как указано в статье. но по сле запуска indexer product_main --config /etc/sphinxsearch/sphinx.conf --rotate --print-queries получаю ID = 0 в таблице логов? что делаю не так? и что должно быть в колонке ID, id продукта?

    ОтветитьУдалить
  17. Это просто id записи в таблице. (primary key) Он должен генерироваться в БД. Поэтому там стоит "identity"

    ОтветитьУдалить
  18. Игорь , добрый день. Делаю все по вашему посту, но к сожалению не могу приконектиться к sql server.
    Подскажите, что делать. Вылезает вот такая ошибка, при запуске батника.
    using config file 'c:\sphinx\data\config.txt'...

    indexing index 'product'...

    SQL-CONNECT: FAIL

    ERROR: index 'product': sql_connect: [Microsoft][SQL Server Native Client 11.0][

    SQL Server]Login failed for user 'Daniel-Pc\─рэш¤ы№'. (DSN=odbc://:***@localhost

    :0/AdventureWorks).

    total 0 docs, 0 bytes

    total 0.023 sec, 0 bytes/sec, 0.00 docs/sec

    total 0 reads, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg

    total 0 writes, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg

    ОтветитьУдалить

© Игорь Чакрыгин. Все права защищены при помощи чёрной магии. Технологии Blogger.