MySQLとPostgreSQLと日本語全文検索3：MroongaとPGroongaの導入方法例 #mypgft - 2016-09-29 - ククログ

資料ダウンロード

ククログ

株式会社クリアコード > ククログ > MySQLとPostgreSQLと日本語全文検索3：MroongaとPGroongaの導入方法例 #mypgft

MySQLとPostgreSQLと日本語全文検索3：MroongaとPGroongaの導入方法例 #mypgft

2016年9月29日（肉の日！）に「MySQLとPostgreSQLと日本語全文検索3」というイベントを開催しました。その名の通りMySQLとPostgreSQLでの日本語全文検索についての話題を扱うイベントです。今回もDMM.comラボさんに会場を提供してもらいました。

2月9日に開催した1回目のイベントではMroonga・PGroongaについては次の2つのことについて紹介しました。

  • Mroonga・PGroongaが速いということ

  • Mroonga・PGroongaの使い方

6月9日に開催した2回目のイベントではMroonga・PGroongaについては次の2つのことについて紹介しました。

  • Mroonga・PGroongaのオススメの使い方

  • レプリケーションまわり

今回はMroonga・PGroongaについては次のことについて紹介しました。

関連リンク：

Redmineへの導入方法

Redmineというチケット管理システムへのMroonga・PGroongaの導入方法を説明します。RedmineはRuby on Railsを利用しているのでRuby on Railsを使っているアプリケーションに導入する例ということになります。

Redmineは右上の検索ボックスから全文検索できます。ここから全文検索したときにMroonga・PGroongaを使うようにします。

Redmineの検索ボックス

redmine_full_text_searchプラグインを使うとRedmineでMroongaまたはPGroongaを使って全文検索できるようになります。

このプラグインを使うとRedmineの全文検索が高速になります。たとえば、クリアコードで使っているRedmineには3000件くらいのチケットがありますが、その環境では次のように高速になりました。

プラグイン 時間
なし 467ms
あり 93ms

200万件のチケットがある環境でも約380msで検索できているという報告もあります。

Mroongaを導入する方法

Mroongaはトランザクションに対応していないのでトランザクションが必須のRedmineに組み込む場合はひと工夫必要になります。単純に、ALTER TABLE table ENGINE=Mroonga ADD FULLTEXT INDEX (column)とするわけにはいきません。

ではどうするかというと別途全文検索用のテーブルを作成して元のテーブルとはJOINできるようにします。（他にもレプリケーションしてレプリケーション先をMroongaにするという2回目のイベントで紹介した方法もありますが、プラグインでやるには大掛かりなのでこの方法を使っています。）

マイグレーションファイルでいうと次のようにします。ここではissuesテーブル用の全文検索用のテーブルを作成しています。

def up
  create_table(:fts_issues, # 全文検索用テーブル作成
               id: false, # idは有効・無効どっちでも可
               options: "ENGINE=Mroonga") do |t|
    t.belongs_to :issue, index: true, null: false
    t.string :subject, default: "", null: false
    t.text :description, limit: 65535, null: false
    t.index [:subject, :description], type: "fulltext"
  end
end

全文検索用のテーブルには元のデータをコピーする必要があります。マイグレーション時には既存のデータを一気にコピーします。そのため、本当のマイグレーションの内容は次のようになります。データコピー後にインデックスを追加するようにしているのはそっちの方が速いからです。

def up
  create_table(:fts_issues, # 全文検索用テーブル作成
               id: false, # idは有効・無効どっちでも可
               options: "ENGINE=Mroonga") do |t|
    t.belongs_to :issue, index: true, null: false
    t.string :subject, default: "", null: false
    t.text :description, limit: 65535, null: false
  end
  execute("INSERT INTO " + # データをコピー
            "fts_issues(issue_id, subject, description) " +
            "SELECT id, subject, description FROM issues;")
  add_index(:fts_issues, [:subject, :description],
            type: "fulltext") # 静的インデックス構築（速い）
end

このテーブルのモデルは次のようになります。

class FtsIssue < ActiveRecord::Base
  # 実際はissue_idカラムは主キーではない。
  # 主キーなしのテーブルなので
  # Active Recordをごまかしているだけ。
  self.primary_key = :issue_id
  belongs_to :issue
end

Mroonga導入後に更新されたデータはアプリケーション（Redmine）側でデータをコピーします。Active Recordのafter_saveフックを利用します。Mroongaがトランザクションをサポートしていないため、ロールバックのタイミングによってはデータに不整合が発生することがありますが、再度保存すれば復旧できることとそれほどロールバックは発生しないため、実運用時には問題になることはないでしょう。

class Issue
  # この後にロールバックされることがあるのでカンペキではない
  # 再度同じチケットを更新するかデータを入れ直せば直る
  after_save do |record|
    fts_record = FtsIssue.find_or_initialize_by(issue_id: record.id)
    fts_record.subject     = record.subject
    fts_record.description = record.description
    fts_record.save!
  end
end

全文検索時は全文検索用のテーブルをJOINしてMATCH AGAINSTを使います。

issue.
  joins(:fts_issue).
  where(["MATCH(fts_issues.subject, " +
               "fts_issues.description) " +
          "AGAINST (? IN BOOLEAN MODE)",
         # ↓デフォルトANDで全文検索
         "*D+ #{keywords.join(', ')}"])

この説明はわかりやすさのために実際の実装を単純化しています。詳細が知りたい方は実装を確認してください。

PGroongaを導入する方法

PGroongaはトランザクションに対応しているので別途全文検索用のテーブルを作成する必要はありません。既存のテーブルに全文検索用のインデックスを作成します。

マイグレーションファイルでいうと次のようにします。ここではissuesテーブルに全文検索用のインデックスを作成しています。enable_extension("pgroonga")はPGroongaを使えるようにするためのSQLです。

def up
  enable_extension("pgroonga")
  add_index(:issues,
            [:id, :subject, :description],
            using: "pgroonga")
end

あとは検索時に全文検索条件をつけるだけです。

issue.
  # 検索対象のカラムごとに
  # クエリーを指定
  where(["subject @@ ? OR " +
         "description @@ ?",
         keywords.join(", "),
         keywords.join(", ")])

この説明もわかりやすさのために実際の実装を単純化しています。詳細が知りたい方は実装を確認してください。

Zulipへの導入方法

ZulipというチャットツールへのPGroongaの導入方法を説明します。ZulipはPostgreSQLを使っているので、導入するのはPGroongaだけです。ZulipはDjangoを使っているのでDjangoを使っているアプリケーションに導入する例ということになります。

Zulipは上部の検索ボックスから全文検索できます。ここから全文検索したときにPGroongaを使うようにします。

Zulipの検索ボックス

Zulipはチャットツールです。チャットツールなので小さなテキストの書き込みが頻繁に発生する傾向があります。各書き込みは十分速く完了する必要があります。書き込みが遅いとユーザーの不満が溜まりやすいからです。

Zulipは書き込みをできるだけ速くするためにインデックスの更新を遅延させています。インデックスの更新はデータの追加よりも重い処理なので、その処理を後回しにしているということです。（PGroongaは検索だけでなく更新も速いので遅延させずにリアルタイムで更新しても十分速いかもしれません。アプリケーションの要件次第でどのような実装にするか検討する必要があります。）

Zulipは、インデックスの更新を遅延させるため、カラムの値を直接全文検索対象にせずに、別途全文検索用のカラム（zulip_message.search_pgroongaカラム）を用意しています。その全文検索用のカラムの更新を後回しにすることでインデックスの更新を遅延させています。

マイグレーションファイルでいうと次のようにします。最初のALTER ROLEはPGroongaが提供する@@という全文検索用の演算子の優先順位を調整するためのものです。本質ではないのでここでは気にしなくて構いません。

migrations.RunSQL("""
ALTER ROLE zulip SET search_path
  TO zulip,public,pgroonga,pg_catalog;
ALTER TABLE zerver_message
  ADD COLUMN search_pgroonga text;
UPDATE zerver_message SET search_pgroonga =
  subject || ' ' || rendered_content;
CREATE INDEX pgrn_index ON zerver_message
  USING pgroonga(search_pgroonga);
""", "...")

全文検索対象のカラム（zerver_message.subjectカラムとzerver_message.rendered_contentカラム）が更新されたらそのレコードのIDをログテーブル（fts_update_logテーブル）に追加します。Zulipは次のトリガーでこれを実現しています。

CREATE FUNCTION append_to_fts_update_log()
  RETURNS trigger
  LANGUAGE plpgsql AS $$
    BEGIN
      INSERT INTO fts_update_log (message_id) VALUES (NEW.id);
      RETURN NEW;
    END
  $$;
CREATE TRIGGER update_fts_index_async
  BEFORE INSERT OR UPDATE OF
    subject, rendered_content ON zerver_message
  FOR EACH ROW
    EXECUTE PROCEDURE append_to_fts_update_log();

全文検索対象のカラムのインデックスは別プロセスで更新します。別プロセスで更新するためには、全文検索対象のカラムが更新されたことをその別プロセスが知らなければいけません。これを実現するためにはポーリングする方法と更新した側から通知を受け取る方法があります。PostgreSQLにはLISTEN/NOTIFYという通知の仕組みがあるので、Zulipはこれらを利用して通知を受け取る方法を実現しています。

更新した側は次のトリガーで更新したことを（fts_update_logチャネルに）通知します。このトリガーはログテーブル（fts_update_logテーブル）にレコードが追加されたら呼ばれるようになっているので、レコードが追加されるごとに通知しているということです。

CREATE FUNCTION do_notify_fts_update_log()
  RETURNS trigger
  LANGUAGE plpgsql AS $$
    BEGIN
      NOTIFY fts_update_log;
      RETURN NEW;
    END
  $$;
CREATE TRIGGER fts_update_log_notify
  AFTER INSERT ON fts_update_log
  FOR EACH STATEMENT
    EXECUTE PROCEDURE do_notify_fts_update_log();

通知を受け取るプロセスはPythonで実装されています。単純化すると次のようになっています。（詳細はpuppet/zulip/files/postgresql/process_fts_updatesを参照。）（fts_update_logチャネルに）通知がきたら全文検索用カラムを更新する（update_fts_columns(cursor)を実行する）ということを繰り返しています。

  import psycopg2
  conn = psycopg2.connect("user=zulip")
  cursor = conn.cursor
  cursor.execute("LISTEN fts_update_log;")
  while True:
      if select.select([conn], [], [], 30) != ([], [], []):
          conn.poll()
          while conn.notifies:
              conn.notifies.pop()
              update_fts_columns(cursor)

全文検索用カラムの更新（update_fts_columnsの実装）は次のようになっています。ログテーブル（fts_update_logテーブル）から更新されたレコードのIDを取得してきて各レコードごとに全文検索用カラムを更新しています。最後に処理したレコードのIDをログテーブルから削除します。

def update_fts_columns(cursor):
    cursor.execute("SELECT id, message_id FROM fts_update_log;")
    ids = []
    for (id, message_id) in cursor.fetchall():
        cursor.execute("UPDATE zerver_message SET "
                       "search_pgroonga = "
                       "subject || ' ' || rendered_content "
                       "WHERE id = %s", (message_id,))
        ids.append(id)
    cursor.execute("DELETE FROM fts_update_log "
                   "WHERE id = ANY(%s)", (ids,))

このようにしてインデックスの更新を遅延し、書き込み時の処理時間を短くしています。書き込み時のレスポンスが大事なチャットツールならではの工夫です。

インデックスが更新できたらあとは全文検索するだけです。全文検索は次のようにWHERE search_pgroonga @@ 'クエリー'を追加するだけです。

from sqlalchemy.sql import column
def _by_search_pgroonga(self, query, operand):
    # WHERE search_pgroonga @@ 'クエリー'
    target = column("search_pgroonga")
    condition = target.op("@@")(operand)
    return query.where(condition)

全文検索してヒットしたキーワードがどこにあるかを見つけやすくするために、Zulipはキーワードハイライト機能を実現しています。以下は「problem」というキーワードをハイライトしている様子です。

Zulipのキーワードハイライト機能

PostgreSQLには標準でts_headline()関数というキーワードハイライト機能がありますが、ZulipのようにHTMLで結果を取得したい場合には使えません。これはts_headline()関数はHTMLエスケープ機能を提供していないからです。HTMLエスケープ機能がないと次のように不正なHTMLができあがってしまいます。

SELECT  ts_headline('english',
                    'PostgreSQL <is> great!',
                    to_tsquery('PostgreSQL'),
                    'HighlightAll=TRUE');
--           ts_headline          
-- -------------------------------
--  <b>PostgreSQL</b> <is> great!
-- (1 row)  不正なHTML↑

そのため、ZulipではPostgreSQLにキーワード出現位置を返す関数を追加して、Zulip側でキーワードハイライト機能を実現しています。PGroongaを使っている場合はpgroonga.match_positions_byte()関数とpgroonga.query_extract_keywords()関数を利用してこの機能を実現しています。

なお、PGroongaはHTMLエスケープ機能付きのハイライト関数pgroonga.highlight_html()を提供しているため、Zulipのようにアプリケーション側でハイライト機能の一部を実装する必要はありません。Zulipではすでに実装されていたためPGroongaを使った場合でもpgroonga.highlight_html()関数を使わずにハイライト機能を実現しています。

この説明はわかりやすさのために実際の実装を単純化しています。詳細が知りたい方は実装を確認してください。

まとめ

MySQLとPostgreSQLと日本語全文検索3で、実例をもとにMroonga・PGroongaの導入方法を紹介しました。たとえMySQL・PostgreSQLレベルで日本語全文検索できても、実際にアプリケーションで使えるようにならないとユーザーに日本語全文検索を提供できません。そのため、このような導入方法の紹介にしました。

アプリケーションごとになにを大事にするかは変わるので、この事例をそのまま適用できるわけではありませんが、Mroonga・PGroongaを導入する際には参考にしてください。

関連情報

現在クリアコードではソフトウェア開発者を募集しています！ フリーソフトウェア開発での開発の仕方をベースに、成果や知識を公開し共有しながら業務に携われます。

クリアコードのYouTubeチャンネルです。メンバーの登壇動画や、開発プロダクトに関する動画を公開しています。

Groongaについて開発者に質問できる「グルカイ！」ほぼ毎週火曜日12:15-12:45　Groongaチャンネルで開催中です。 Groongaについて知りたい人、ぜひご参加ください。