はじめに

MySQL は優れた汎用のデータベースですが、頻繁に検索を行うアプリケーションの場合には専用の検索ユーティリティーを使用することを検討した方がうまくいくかもしれません。この記事では、よく知られた全文検索パッケージである Sphinx について検討を行います。Sphinx は MySQL に代わる検索ツールとして、全文検索以外の検索の場合にも速度を高めることができます。この記事では、MySQL から Sphinx に変更する場合の長所と短所、さらには注意事項について説明します。また実際にテストを行って、汎用ツールとして Sphinx を使用するには何が必要かについて検討します。

MySQL か Sphinx か？

なぜ MySQL の代わりに Sphinx を使用することを検討する必要があるのでしょう。例えば書店の検索アプリケーションを考えてみてください。ユーザーは、タイトル、著者、状態 (新本か古本か)、版 (初版かそれ以外か)、表紙の種類 (ハードカバーかペーパーバックか)、出版社、出版年、著者のサインの有無、価格、等々を指定することによって本を検索します。MySQL では通常、複数のインデックスを使うことはできません (例外としてインデックス結合最適化がありますが、これについては「参考文献」を参照してください)。そのため、考えられるすべての検索を最適化するためには膨大な数の複合インデックス (複数カラムのインデックス) を使用するしかありませんが、それは適切な方法ではありません。

一方、Sphinx は MySQL に統合するのに適しているのと同時に、Sphinx 単独で動作することも可能な検索エンジンです。Sphinx には優れたインデックス機能があるため、検索のパフォーマンスにも優れており、また SQL (Structured Query Language: 構造化問い合わせ言語) ベースの SphinxQL 言語を使用してクエリーを実行することができます。さらに、Sphinx は分散検索機能を使用することで、何テラバイトにもなる何十億もの文書を扱うことができ、適切にスケールアップを行います。

Sphinx では、文書 (単純にデータベース・テーブルまたはビュー内のレコード)、テキスト・フィールド (Sphinx はテキスト・フィールドにインデックスを付け、全文検索を行います)、属性 (結果のフィルタリング、ソート、グループ化に使用されるテキスト以外の値) を扱います。効率を高めるために、属性は RAM (Random Access Memory) に保持されます。実際に必要な RAM のサイズを計算するための公式については「参考文献」に挙げた Sphinx のドキュメントを参照してください。

クエリーを処理するために、Sphinx は特殊なインデックス・ファイルを使用します。インデックスを付ける手順を実行するには、そのためのデータ・ソースを定義した後、indexer プログラムを実行する必要があります。もう 1 つの方法として、リアルタイム・インデックス・ファイルを使用する方法があります。このインデックス・ファイルはオンザフライで更新することができますが、欠点として効率が多少下がります。これらの詳細については後ほど説明します。

サンプル・データの問題

Sphinx と MySQL の検索速度をテストするために、(サイズの面で) このテストに合いそうなデータ・セットを探したところ、約 300 万件の給与のレコードを持つテスト・データベースを見つけました。これだけのデータがあれば、このテストには十分のようです (このデータの入手方法については「参考文献」を参照してください)。このデータベースのスキーマは単純で、従業員のデータとして従業員の肩書き、部門、年俸などが含まれています。今回の検索の例では、employees と salaries という 2 つの最も大きなテーブルに対して処理を行います。

私はデータをインストールした後、salaries テーブルの細部に 2 ヶ所ほど修正が必要なことに気付きました。1 つ目は、to_date フィールドには特別なマーカーとして 9999-01-01 が含まれている場合があることです。私はそれを 2038-01-01 で置き換えました。UNIX のタイムスタンプは 2038年を超えられないからです。2 つ目は、Sphinx ではすべてのレコードが 1 つのフィールド ID キーを持つ必要があります。そのため、私はオート・インクリメントが設定された salaries_id フィールドを salaries テーブルに追加しました。リスト 1 を見てください。

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 に示すクエリーを考えてみてください。

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年 9月生まれで 1998年またはそれ以降に雇用された人を検索します。MySQL は約 0.19 秒で 38 件のレコードを検出しました (もちろん、皆さんが検索した場合の結果は異なる可能性があります)。2 番目の検索は employees テーブルと salaries テーブルを結合し、2000年またはそれ以降に年収が 120,000 ドルを超えた Yucel という名前の男性従業員を検索します。MySQL は約 0.15 秒で 5 件のレコードを取得しました。

これらの検索を Sphinx を使用して実行するためには、Sphinx をセットアップし、データにインデックスを付け、デーモンを実行する必要があります。これについては、次のセクションで説明します。

Sphinx を構成する

Sphinx は、リスト 3 に示すように sphinx.conf ファイル内の定義に従ってデータ・ソースにインデックスを付けます。date フィールドに対して型変換を行っていることに注意してください。この記事では主な必須パラメーターを抜粋していますが、設定対象となるパラメーターは他にもたくさんあることを認識する必要があります。

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 はさまざまなタイプのファイルを扱うことができます (テキスト、HTML (Hypertext Markup Language)、XML (Extensible Markup Language) など)。しかしこの例では MySQL のファイルしか使用していないため、type=mysql としています。sql_host、sql_user、sql_pass、sql_db というパラメーターは、データベースへのアクセス方法と、使用するスキーマを定義し、sql_query パラメーターはインデックスを付ける対象となるデータを取得する SQL 文を定義しています。基本的に、この場合の唯一の制約は、最初のフィールドが符号なしの正の整数による一意の ID 番号でなければならない、ということだけです。先ほど、salaries テーブルに salaries_id フィールドを追加したのはそのためです。テキスト・フィールドは 32 個まで指定することができ、属性はいくらでも指定することができます。Sphinx は ID (最初のフィールド) と属性を除き、すべての列に全文インデックスを付けることができます。

表 1 を見るとわかるように、Sphinx はいくつかのタイプの属性をサポートしていますが、MySQL に使用できるデータ型をすべてサポートしているわけではありません。Sphinx 特有の属性タイプもいくつかありますが、単に MySQL の置き換えだけを検討する場合にはそれらは無関係のため、この表には含めてありません。

構成ファイルのインデックス部分はソースの属性を記述します (リスト 4)。

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 (rt は、リアルタイム (real time) の略です) などがあります。これらのインデックスはオンザフライで更新することができます。source= という行はデータ・ソースとインデックスとを関連付けています。実際には数個のデータ・ソースを 1 つのインデックスに結合することができますが、この例ではそうしていません。path= という行はインデックス・ファイルの名前と格納場所を定義しています。charset_type= という行は、対象が sbcs (Single Byte Character Set: 1 バイト文字セット) であるか utf-8 (Universal Character Set (UCS) Transformation Format-8 bit) であるかを指定します。最後に、preopen=1 は検索デーモンに対し、最初のクエリーが到着するのを待たず、ロードと同時にすべてのインデックス・ファイルを開くように指示しています。

構成ファイルの最後のスタンザは indexer アプリケーションと searchd アプリケーションに関係しています (リスト 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 によって RAM のサイズを指定する必要があります。searchd の定義はほとんど自明ですが、listen= には説明が必要かもしれません。listen= パラメーターでは、SphinxQL バイナリー・ネットワーク・プロトコルを使用できる IP アドレスとポート番号を指定します。このプロトコルを直接操作したい場合には、標準的な MySQL クライアントを使用することができます。そのためには「mysql -P 9306」と入力してクエリーを実行すればよく、マシン上で MySQL の mysqld プログラムを実行する必要もありません。

これですべての設定が終わったので、データにインデックスを付けて検索デーモンの実行を開始することができます (リスト 6)。--all パラメーターはすべてのインデックス・ファイルを生成するという意味です。

~/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

リスト 6 によってデータにインデックスが付けられ、必要なデーモンが実行されている状態になったので、いくつかの検索を実行する準備ができたことになります。

検索を実行する

Sphinx にクエリーを実行する方法として望ましいのは SphinxQL を使用する方法です。MySQL で使用できる言語であれば、どの言語も SphinxQL で使用することができます。この例では PHP を使用しており、そのコードをリスト 7 に示します。

$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() 関数に相当する関数がないため、私は独自に関数を作成しました。mktime が UNIX_TIMESTAMP への変換を行うことに注意してください。do_time 関数は指定されたクエリーを指定のサーバーで実行して時間を測定します。

SphinxQL のクエリーは以下の点で MySQL のクエリーと異なります。

• MATCH を使用してテキスト・フィールドに対するクエリーを実行します。
• すべての日付をタイムスタンプまたは整数に変換する必要があります。この例では単に SphinxQL の多様性を示すために両方の方法を使用しました。
• Sphinx は完全なレコードを返すことも ID フィールドのみを返すこともできます (ID フィールドのみを返した方が効率的です)。もちろん、ID フィールドのみを返す場合には MySQL を使用して残りの情報を取得する必要があります。
• AND 演算子と OR 演算子の優先度は同じです。そのため、使い方に注意する必要があり、必要に応じて括弧を使う必要があります。
• MySQL の数値関数、文字列関数、その他の関数がすべて Sphinx にも用意されているわけではありません。

2 つのテストを実行するだけでは完全な概念検証とは言えませんが、リスト 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 には、差分インデックス・ファイルとライブ・インデックス更新という 2 つのソリューションが用意されています。

よくある例として、大規模なデータ・セットに少数の新しいレコードが時々追加される場合があります。ここでは、一旦作成されたレコードは変更されないものとします。「メインと差分」という仕組みを実装することで、ほぼリアルタイムでインデックスを更新することができます。考え方としては、古い固定データに対して 1 つのインデックスを持ち、新しいデータに対して別のインデックスを持つようにします。新しいデータのインデックスはデータのサイズが小さいため、すぐに作成することができます。そして後は、両方のインデックス・ファイルに対してクエリーを実行し、両方の結果を統合したものを結果として使用するだけです。インデックス結合を使用してメイン・インデックスを再作成する方法として、前回のインデックスと差分インデックスとを結合する方法を考えてみてください (詳細については「参考文献」に挙げた Sphinx のドキュメントを参照）。

では、古いデータが変更される可能性がある場合や、真にリアルタイムの更新が必要な場合はどうなるのでしょう。その答えが Sphinx のリアルタイム (rt) インデックス・ファイルです。リアルタイム・インデックス・ファイルを使用すると、INSERT、REPLACE、DELETE コマンドによってインデックス・ファイルをリアルタイムで変更できるからです。メイン・テーブルを更新する場合には必ず、その更新に対応してインデックスの更新も実行し、MySQL のデータと Sphinx のデータの間に差がないように注意する必要があります。念のため、Sphinx のドキュメントのセクション 4.2 にある注意事項と制限事項をよく調べてください (「参考文献」を参照)。

最終的な評価

どのような結論を導き出せるのでしょう。何と言っても、MySQL の代わりに Sphinx を使用することで大幅にパフォーマンスが向上するというメリットが得られます。Sphinx は静的なテーブルの検索に非常に優れています。しかしテーブルが頻繁に更新される場合には、type=plain と指定して標準のインデックス・ファイルを使用することはできません。差分ファイルを実装するか、あるいはリアルタイムでインデックスを更新するように変更する必要があり、どちらの方法もパフォーマンスが犠牲になります。最後に、Sphinx を効率的に使用するためには、必要なすべてのソースとインデックス・ファイルを事前に定義する必要があるため、少しばかり計画が必要です。もちろん、計画が必要なことは欠点ではなく、単なる常識にすぎません。

Sphinx で MySQL を置き換えることは簡単ではありませんが、検討の余地がないほど複雑なわけでもありません。高速の検索が必要な場合には、たとえ全文検索を行わない場合であっても、MySQL から Sphinx への変更を検討してみる価値があります。

参考文献

学ぶために

• Sphinx のドキュメント: Sphinx の機能について学んでください。
  
  
• 「PHP でカスタム検索エンジンを作成する」(developerWorks、2007年7月): 全文検索について調べてください。
  
  
• MySQL: Index merge optimization: この方法について学んでください。
  
  
• developerWorks on Twitter: Twitter で developerWorks の最新ニュースをフォローしてください。
  
  
• developerWorks の Open source ゾーン: オープンソース技術を使用した開発や、IBM 製品でオープンソース技術を使用するためのハウ・ツー情報やツール、プロジェクトの更新情報など、豊富な情報が用意されています。
  
  
• 関心のあるイベント: IBM オープンソース開発者にとって関心のある、今後開催される会議や業界展示会、ウェブキャスト、その他のイベントについて調べてみてください。
  
  
• developerWorks podcasts: ソフトウェア開発者のための興味深いインタビューや議論を聞いてください。
  
  
• developerWorks On demand demos: IBM とオープンソース技術、製品機能について学ぶために、無料のデモをご覧ください。
  
  

製品や技術を入手するために

• Sphinx: 最新バージョンを入手し、この記事で紹介したコードを試してください。
  
  
• この記事で使用したサンプル・データベース: The Data Charmer の Web サイトからダウンロードしてください。
  
  
• IBM ソフトウェア製品を評価する: 試用版のダウンロードからクラウドでホストされる製品に至るまで、特に開発者のために用意されたソフトウェアを使用して皆さんの次期オープンソース開発プロジェクトを革新することができます。
  
  

議論するために

• developerWorks コミュニティー: 開発者向けのブログ、フォーラム、グループ、ウィキなどを利用しながら他の developerWorks ユーザーとやり取りしてください。developerWorks のコミュニティー、Real world open source グループの構築を支援してください。
  
  

著者について

Federico Kereki はウルグアイのシステム・エンジニアであり、システム開発、コンサルティング、大学での指導に 20 年以上の経験があります。彼は 10 年以上にわたってオープンソース・ソフトウェアを扱ってきており、特に Linux の優れたセキュリティーを気に入っています。彼は最近、オープンソース・ツール GWT に関する書籍『Essential GWT』を執筆しました。

この記事を評価する

コメント

上に戻る

目次

• はじめに
• MySQL か Sphinx か？
• サンプル・データの問題
• Sphinx を構成する
• 検索を実行する
• インデックス・ファイルを更新する
• 最終的な評価
• 参考文献
• 著者について
• コメント