MySQL – хорошая СУБД общего назначения, но если приложение выполняет интенсивный поиск, то его производительность можно повысить, используя вместо нее Sphinx. Хотя Sphinx – это инструмент полнотекстового поиска, он может повысить быстродействие приложения, даже работая с неполнотекстовыми запросами. В этой статье показано, как настроить Sphinx для решения этой задачи, и приведены примеры запросов, способы измерения времени их выполнения, а также некоторые компромиссы, на которые приходится идти при замене с учетом необходимости систематического использования Sphinx в решениях общего назначения.

Source: Ускорение поиска в базах данных MySQL с помощью Sphinx

Введение

MySQL – хорошая СУБД общего назначения, но для приложений с интенсивным поиском данных можно получить лучшие результаты, применяя специализированные инструменты. В этой статье рассматривается известный пакет для полнотекстового поиска Sphinx в качестве замены MySQL, что повышает быстродействие даже в операциях неполнотекстового поиска. В статье анализируются компромиссы и содержатся предостережения на случай такой замены, а также некоторые специальные тесты и требования, которые нужно выполнять при использовании Sphinx в качестве инструмента общего назначения.

MySQL или Sphinx?

Зачем заменять MySQL на Sphinx? Рассмотрим, например, поисковое приложение книготорговца. Пользователь может искать книги по названию, автору, состоянию (новая или букинистическая), изданию (первое или последующие), обложке (жесткая или мягкая), издательству, году издания, наличию автографа, цене и т.п. В MySQL, как правило, не используется более одного индекса (исключение составляет оптимизация со слиянием индексов;см. раздел Ресурсы), так что единственным способом оптимизировать все возможные виды поиска было бы введение чрезмерного числа многостолбцовых индексов, что не есть хорошо.

Sphinx представляет собой поисковую систему, которая хорошо интегрируется с MySQL и работает в автономном режиме. Она обеспечивает высокую производительность индексации и поиска, позволяя делать запросы с помощью SphinxQL, языка на основе Structured Query Language (SQL). Наконец, Sphinx хорошо масштабируется, позволяя работать с миллиардами документов, охватывающих терабайты данных с возможностью распределенного поиска.

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

Для обработки запросов Sphinx использует специальные индексные файлы. Для процедуры индексирования нужно определить источники данных, а затем запустить программу indexer. Другая возможность — использование индексных файлов реального времени, которые можно динамически обновлять ценой некоторого снижения эффективности. Ниже мы рассмотрим это подробнее.

Пример задачи

Чтобы сравнить скорость поиска Sphinx и MySQL, я взял несколько интересных (по размерам) наборов данных и подобрал опытную базу данных примерно с 3 млн записей по зарплате, которая представляется подходящей для наших целей. (Ссылка на эти данные есть в разделе Ресурсы.) Схема проста: есть сотрудники, которые работают в разных отделах на разных должностях и получают разную годовую зарплату. Пример работает с двумя самыми крупными таблицами: employees и salaries.

После установки данных я обнаружил, что необходимо исправить пару деталей в таблице salaries. Во-первых, полеto_date иногда содержало специальный маркер 9999-01-01. Я заменил его на 2038-01-01, потому что метки времени UNIX не должны выходить за пределы 2038 года. Кроме того, Sphinx требует, чтобы каждая запись имела ключ идентификатора поля, поэтому в таблицу salaries пришлось добавить автоинкрементное поле salaries_id. (См.листинг 1).
Листинг 1. Исправление некоторых деталей в таблице salaries

ALTER TABLE salaries
  DROP PRIMARY KEY,
  ADD UNIQUE emp_from (emp_no, from_date) ;
ALTER TABLE salaries
  ADD salaries_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ;
UPDATE salaries
  SET to_date="2038-01-01" WHERE to_date="9999-01-01";

 

Теперь рассмотрим запросы, показанные в листинге 2.
Листинг 2. Несколько простых тестов для сравнения MySQL и Sphinx

SELECT *
  FROM employees.employees
  WHERE birth_date BETWEEN '1960-09-01' AND '1960-09-30'
    AND hire_date >= '1998-01-01';
38 rows in set (0.19 sec)

SELECT *
  FROM employees.employees eee JOIN employees.salaries sss
  ON sss.emp_no=eee.emp_no
  WHERE eee.first_name='Yucel'
    AND sss.salary>120000
    AND sss.from_date >= '2000-01-01';
5 rows in set (0.15 sec)

 

Первый поиск охватывает только таблицу employees и выбирает людей, родившихся в сентябре 1960 года и поступивших на работу не ранее 1998 года. MySQL находит 38 записей примерно за 0,19 с. (Конечно, ваши результаты могут отличаться.) Во втором случае поиск производится по двум таблицам employees и salaries, и выбираются мужчины по имени Yucel, которые, начиная с 2000 года, зарабатывали более 120 000. MySQL находит пять записей примерно за 0,15 с.

Чтобы выполнить эти примеры поиска с применением Sphinx, нужно установить его, проиндексировать данные и запустить демон. Эти задачи рассматриваются в следующем разделе.

Настройка Sphinx

Sphinx индексирует источники данных в соответствии с определениями, содержащимися в файле sphinx.conf, который показан в листинге 3. Обратите внимание на преобразование типов полей даты. В этой статье рассматриваются только самые необходимые параметры, но нужно иметь в виду, что существует много других параметров конфигурации, которые можно использовать.
Листинг 3. Определение источников данных для Sphinx

source employeesSource
{
  type = mysql
  sql_host = localhost
  sql_user = mysqluser
  sql_pass = mysqlpass
  sql_db = employees
  sql_query= SELECT \
    emp_no as id, \
    TO_DAYS(birth_date) AS birth_date_td, \
    first_name, \
    last_name, \
    gender, \
    TO_DAYS(hire_date) AS hire_date_td \
    FROM employees
  sql_attr_uint = birth_date_td
  sql_field_string = first_name
  sql_field_string = last_name
  sql_field_string = gender
  sql_attr_uint = hire_date_td
}

source employeesSalariesSource
{
  type = mysql
  sql_host = localhost
  sql_user = mysqluser
  sql_pass = mysqlpass
  sql_db = employees
  sql_query= SELECT \
    ss.salaries_id AS id, \
    ee.emp_no AS emp_no, \
    TO_DAYS(ee.birth_date) AS birth_date_td, \
    ee.first_name AS first_name, \
    ee.last_name AS last_name, \
    ee.gender AS gender, \
    TO_DAYS(ee.hire_date) AS hire_date_td, \
    ss.salary AS salary, \
    UNIX_TIMESTAMP(ss.from_date) AS from_date_ts, \
    UNIX_TIMESTAMP(ss.to_date) AS to_date_ts \
    FROM employees ee JOIN salaries ss \
    ON ss.emp_no=ee.emp_no
  sql_attr_uint = emp_no
  sql_attr_timestamp = birth_date_ts
  sql_field_string = first_name
  sql_field_string = last_name
  sql_field_string = gender
  sql_attr_uint = hire_date_td
  sql_attr_uint = salary
  sql_attr_timestamp = from_date_ts
  sql_attr_timestamp = to_date_ts
}

 

Конфигурационный файл делится на разделы. В разделе источников определяются источники данных. Sphinx может работать со многими типами файлов, включая текстовые файлы и файлы в форматах Hypertext Markup Language (HTML) и Extensible Markup Language (XML). Однако в этом примере используется только MySQL и, следовательно, type=mysql. Параметры sql_hostsql_usersql_pass и sql_db определяют, как получить доступ к базе данных и какую схему использовать. Параметр sql_query обеспечивает SQL-запрос, который извлекает данные, подлежащие индексированию. По сути, единственным ограничением здесь является то, что первое поле должно быть уникальным идентификатором в формате натурального числа без знака. Поэтому мне пришлось добавить поле salaries_id к таблице salaries (см. выше). Можно задать до 32 атрибутов в виде текстовых полей и произвольных чисел. Sphinx создает полнотекстовые индексы для всех столбцов, кроме ID (первое поле) и атрибутов.

Как видно из таблицы 1, Sphinx поддерживает несколько типов атрибутов, но не все возможные типы данных MySQL. Есть также некоторые типы атрибутов, специфические для Sphinx, но они не нужны при его использовании в качестве простой замены MySQL, поэтому я их здесь не рассматриваю.
Таблица 1. Sphinx допускает всего несколько типов атрибутов

Атрибут Описание
sql_attr_uint иsql_attr_bigint 32-разрядное без знака и 64-разрядное со знаком целые числа. Эти два типа можно использовать для всех целочисленных полей базы данных, а также возможно, в качестве замены поля DATE.
sql_attr_float 32-битные значения с плавающей точкой. Этот тип атрибутов можно использовать для хранения географических координат. Также отметим, что если требуется более высокая точность, то решения нет; поля округляются примерно до семи десятичных знаков.
sql_attr_bool Булево значение (один бит), подобное значениям tinyint в MySQL.
sql_attr_timestamp Метка времени UNIX, которую можно представить значениями даты/времени с 1970-01-01 по 2038-01-19. В Sphinx нельзя напрямую использовать типы столбцов DATE и DATETIME. Их нужно преобразовать в метки времени с помощью функции UNIX_TIMESTAMP(). Если требуются только даты, можно использовать функцию TO_DAYS() для преобразования поля DATE в целое число.
sql_attr_string иsql_field_string Строки (естественно!), но первая служит только для извлечения данных, а вторая индексируется как полный текст.

 

Раздел индекса файлов конфигурации описывает атрибуты источников (листинг 4).
Листинг 4. Определения индексов Sphinx

index employeesIndex
{
  type = plain
  source = employeesSource
  path = /home/fkereki/bin/sphinx/var/data/sphinxFilesEI
  charset_type = utf-8
  preopen = 1
}

index employeesSalariesIndex
{
  type = plain
  source = employeesSalariesSource
  path = /home/fkereki/bin/sphinx/var/data/sphinxFilesESI
  charset_type = utf-8
  preopen = 1
}

 

Sphinx использует индексные файлы, отдельные от тех, которые используются MySQL. Строка type=plain означает, что используются стандартные индексные файлы Sphinx. Другие варианты: distributed (если есть индексные файлы, распределенные по нескольким узлам сети) и rt (что означает реального времени). Эти индексы можно изменять динамически. Строка source= связывает источник данных с индексом. На самом деле можно объединить несколько источников данных в один индекс, но в данном примере это не делается. Строка path= определяет имя индексного файла и место его хранения. Строка charset_type= указывает, будете ли вы работать с набором символов Single Byte Character Set(sbcs) или с Universal Character Set (UCS) Transformation Format-8 bit (UTF-8). Наконец, preopen=1предписывает демону поиска открыть все индексные файлы после загрузки, не дожидаясь поступления первых запросов.

Последние разделы файла конфигурации связаны с приложениями indexer и searchd (листинг 5).
Листинг 5. Параметры демонов индексирования и поиска

indexer
{
  mem_limit = 1024M
}

searchd
{
  listen = 127.0.0.1:9306:mysql41
  log = /home/fkereki/bin/sphinx/var/log/searchd.log
  query_log = /home/fkereki/bin/sphinx/var/log/query.log
  pid_file = /home/fkereki/bin/sphinx/var/log/searchd.pid
}

 

Нужно отвести достаточно памяти для работы indexer, определив объем оперативной памяти mem_limit. Определенияsearchd самоочевидны, за исключением listen=. Этот параметр можно использовать для указания того, по какому IP-адресу и порту обращаться к двоичному сетевому протоколу SphinxQL. Если нужно взаимодействовать с протоколом напрямую, можно использовать стандартный клиент MySQL, указав mysql -P 9306, и выполнять запросы, даже не запуская программу MySQL mysqld на своем компьютере.

Теперь, когда все настроено, можно просто проиндексировать данные и запустить демон поиска (листинг 6). Параметр --all означает, что будут созданы все индексные файлы.
Листинг 6. Индексирование данных и выполнение демона поиска 

~/bin/sphinx/etc> ../bin/indexer --all
Sphinx 2.0.1-beta (r2792)
Copyright (c) 2001-2011, Andrew Aksyonoff
Copyright (c) 2008-2011, Sphinx Technologies Inc (http://sphinxsearch.com)

using config file './sphinx.conf'...
indexing index 'employeesSalariesIndex'...
collected 2844047 docs, 40.9 MB
sorted 8.5 Mhits, 100.0% done
total 2844047 docs, 40877736 bytes
total 24.842 sec, 1645464 bytes/sec, 114482.32 docs/sec
indexing index 'employeesIndex'...
collected 300024 docs, 4.3 MB
sorted 0.9 Mhits, 100.0% done
total 300024 docs, 4311224 bytes
total 0.914 sec, 4714945 bytes/sec, 328119.56 docs/sec
total 7 reads, 0.274 sec, 19695.2 kb/call avg, 39.2 msec/call avg
total 110 writes, 0.528 sec, 2351.0 kb/call avg, 4.8 msec/call avg

~/bin/sphinx/etc> ../bin/searchd
Sphinx 2.0.1-beta (r2792)
Copyright (c) 2001-2011, Andrew Aksyonoff
Copyright (c) 2008-2011, Sphinx Technologies Inc (http://sphinxsearch.com)

using config file './sphinx.conf'...
listening on 127.0.0.1:9306
precaching index 'employeesSalariesIndex'
precaching index 'employeesIndex'
precached 2 indexes in 0.124 sec

 

Когда данные индексированы и нужный демон запущен, можно приступать к поиску.

Выполнение поиска

Предпочтительным способом запросов Sphinx является использование SphinxQL. Любой язык, который работает с MySQL, работает и с SphinxQL. В этом примере используется PHP, и соответствующий код показан в листинге 7.
Листинг 7. PHP-программа для запросов MySQL и Sphinx с измерением быстродействия

$bd0 = mysql_connect('localhost:3306', 'mysqluser','mysqlpass');
$bd1 = mysql_connect('localhost:9306');

echo "FIRST TEST ... SINGLE TABLE\n\n";

$td1 = to_days("1960-09-01");
$td2 = to_days("1960-09-30");
$td3 = to_days("1998-01-01");

do_time("test 1 - MySQL ", "SELECT * FROM employees.employees ".
  "WHERE birth_date BETWEEN '1960-09-01' AND '1960-09-30' ".
  "AND hire_date >= '1998-01-01'", $bd0);

do_time("test 1 - Sphinx", "SELECT * FROM employeesIndex ".
  "WHERE birth_date_td BETWEEN {$td1} AND {$td2} ".
  "AND hire_date_td >= {$td3} LIMIT 0,10000 ", $bd1);

echo "\nSECOND TEST ... JOIN\n\n";

$ts1 = mktime(0,0,0,1,1,2000);

do_time("test 2 - MySQL ", "SELECT * ".
  "FROM employees.employees eee JOIN employees.salaries sss ".
  "ON sss.emp_no=eee.emp_no ".
  "WHERE eee.first_name='Yucel' ".
  "AND sss.salary>120000 ".
  "AND sss.from_date >= '2000-01-01'", $bd0);

do_time("test 2 - Sphinx", "SELECT * FROM employeesSalariesIndex ".
  "WHERE MATCH('@first_name Yucel') ".
  "AND salary>120000 ".
  "AND from_date_ts>={$ts1} limit 0,10000 ", $bd1);
function to_days($date) {
  return 719528 + floor(strtotime($date)/(60*60*24));
}
function do_time($description, $sentence, $bd) {
  $m0 = microtime(true);
  $res= @mysql_query($sentence, $bd);
  $m1 = microtime(true);
  $nr = mysql_num_rows($res);
  echo $description." ".$nr." rows in ".
    sprintf("%6.4f", $m1-$m0)." secs\n";
}

 

Согласно спецификации, приведенной в листинге 5, запросы SphinxQL должны направляться в порт 9306. Ввиду отсутствия PHP-эквивалента функции MySQL TO_DAYS() я написал свой собственный. Обратите внимание, что для преобразований UNIX_TIMESTAMP используется mktime. Функция do_time выполняет данный запрос на указанном сервере и измеряет время его выполнения.

SphinxQL-запросы отличаются от запросов MySQL следующими особенностями:

  • для обращения к текстовым полям можно использовать MATCH;
  • все даты нужно преобразовать либо в метки времени, либо в целые числа. В этом примере для разнообразия используются оба способа;
  • Sphinx может возвращать полные записи или только поля ID, что более эффективно. Конечно, в последнем случае для получения остальной информации придется использовать MySQL;
  • операторы AND и OR имеют одинаковый приоритет, так что будьте осторожны и в случае необходимости используйте скобки;
  • Sphinx обеспечивает не все числовые, строковые и другие функции MySQL.

Запуск всего пары тестов не является строгим доказательством концепции, но результаты, показанные в листинге 8, дают основание полагать, что замена MySQL на Sphinx может оказаться полезной.
Листинг 8. Результаты сравнения MySQL и Sphinx

~/bin/sphinx/etc> php test.php
FIRST TEST ... SINGLE TABLE

test 1 - MySQL  38 rows in 0.1912 secs
test 1 - Sphinx 38 rows in 0.0157 secs

SECOND TEST ... JOIN

test 2 - MySQL  5 rows in 0.1532 secs
test 2 - Sphinx 5 rows in 0.0020 secs

 

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

Редактирование индексных файлов

Что произойдет, если изменить исходные данные? Придется обновить индексные файлы, иначе поиск начнет давать неверные результаты. Можно переиндексировать все после каждого обновления, но очевидно, что это слишком затратно! Sphinx предлагает два решения: индексные дельта-файлы и динамическое обновление индекса.

Часто имеется большой набор данных, в который время от времени добавляется небольшое количество новых записей. Для примера предположим, что после того, как записи введены, они остаются неизмененными. Реализовав схему «основные данные + дельта», индекс можно обновлять почти в режиме реального времени. Идея в том, чтобы иметь один индекс для старых, фиксированных данных, а другой ― для новых, который благодаря меньшим размерам создается быстро. Тогда достаточно просто обращаться к двум индексным файлам и объединять результаты. Рассмотрите возможность слияния индексов для воссоздания основного индекса путем соединения предыдущего индекса с дельта-индексом (см. документацию по Sphinx в разделе Ресурсы.

А что, если старые данные могут изменяться или если действительно нужны обновления в режиме реального времени? Решением служат индексные файлы RT Sphinx, поскольку они позволяют выполнять команды INSERTREPLACE и DELETEнад индексными файлами в режиме реального времени. Всякий раз, когда изменяется основная таблица, нужно выполнять соответствующие обновления индекса, чтобы гарантировать отсутствие различий между данными MySQL и Sphinx. На всякий случай прочтите об оговорках и ограничениях в разделе 4.2 документации по Sphinx (см. разделРесурсы).

Заключительная оценка

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

Замена MySQL на Sphinx не тривиальна, но и не настолько сложна, чтобы исключить этот вариант. Если требуется высокая скорость поиска, стоит рассмотреть возможность замены MySQL на Sphinx, даже если вы не выполняете полнотекстовой поиск.

 

Ресурсы

Научиться

Получить продукты и технологии

  • Sphinx: загрузите последнюю версию и попробуйте код примеров из этой статьи.
  • Образец базы данных, используемой в данной статье: загрузите его с Web-сайта The Data Charmer.
  • Оцените программные продукты IBM: рационализируйте свой следующий проект разработки ПО с открытым исходным кодом с помощью программного обеспечения, специально предназначенного для разработчиков, от загрузки ознакомительных версий до продуктов, размещаемых в облаке.

Обсудить

  • Сообщество developerWorks: общайтесь с другими пользователями developerWorks, участвуя в блогах, форумах, группах и вики разработчиков. Примите участие в создании группы Real world open source в сообществе developerWorks.

Об авторе

Photo of Federico KerekiФедерико Кереки (Federico Kereki) – системный инженер из Уругвая, за плечами которого более 20 лет опыта системной разработки, консалтинга и преподавания в университетах. В настоящее время он работает с широким кругом технологий, в частности SOA, GWT, Ajax, PHP и, разумеется, FLOSS. Связаться с Федерико можно по адресу fkereki@gmail.com.

Комментарии запрещены.