Mroongan(('note:と'))nPGroonga¶ ↑
: subtitle
導入方法例
: author
須藤功平
: institution
クリアコード
: content-source
MySQLとPostgreSQLと日本語全文検索
: date
2016-09-29
: allotted-time
20m
: theme
groonga
Mroonga・PGroonga¶ ↑
* Mroonga(むるんが) * (('wait'))MySQLに\n 高速日本語全文検索機能を追加する\n プロダクト * PGroonga(ぴーじーるんが) * (('wait'))PostgreSQLに\n 高速日本語全文検索機能を追加する\n プロダクト
高速?¶ ↑
# image # src = http://slide.rabbit-shocker.org/authors/kou/mysql-and-postgresql-and-japanese-full-text-search/mroonga-and-pgroonga.pdf # page = 6 # relative_height = 80
(('tag:center')) (('note:詳細は第1回の資料を参照'))n (('note:slide.rabbit-shocker.org/authors/kou/mysql-and-postgresql-and-japanese-full-text-search/'))
導入方法例¶ ↑
既存システムへの導入方法を紹介
* Redmine * チケット管理システム * Ruby on Railsを使用 * Zulip * チャットツール * Djangoを使用
Redmine¶ ↑
# image # src = images/redmine-full-text-search-form.png # relative_width = 100
全文検索プラグイン¶ ↑
GitHub: (('tag:x-small:okkez/redmine_full_text_search'))
* MySQL・PostgreSQL両方対応 * MySQLのときはMroongaを利用 * PostgreSQLのときはPGroongaを利用
速さ¶ ↑
(('tag:center')) MySQL + Mroongaのケース
# RT delimiter = [|] プラグイン | チケット数 | 時間 なし | 約3000件 | 467ms あり | 約3000件 | 93ms あり | 約200万件 | 380ms
スライドプロパティ¶ ↑
: groonga-product
mroonga
速さ:コメント¶ ↑
# image # src = images/redmine-full-text-search-plugin-report.png # relative_height = 85
(('note:twitter.com/akahane92/status/733832496945594368'))
スライドプロパティ¶ ↑
: groonga-product
mroonga
使いどころ¶ ↑
* Mroonga * (('wait'))速さが欲しい * (('wait'))トランザクションはいらない * PGroonga * (('wait'))機能が欲しい * (('wait'))トランザクションも欲しい
Redmine¶ ↑
* トランザクション必須 * Mroongaを使うときは一工夫必要 * PGroongaはそのままで大丈夫
Redmine+Mroonga:方針¶ ↑
* (('wait'))チケットテーブルは変えない * (('wait'))全文検索用テーブルを別途作成 * (('wait'))全文検索用テーブルから\n チケットテーブルを参照
スライドプロパティ¶ ↑
: groonga-product
mroonga
マイグレーション¶ ↑
# coderay ruby 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
スライドプロパティ¶ ↑
: groonga-product
mroonga
モデル¶ ↑
# coderay ruby class FtsIssue < ActiveRecord::Base # 実際はissue_idカラムは主キーではない。 # 主キーなしのテーブルなので # Active Recordをごまかしているだけ。 self.primary_key = :issue_id belongs_to :issue end
スライドプロパティ¶ ↑
: groonga-product
mroonga
保存¶ ↑
# coderay ruby 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
スライドプロパティ¶ ↑
: groonga-product
mroonga
全文検索¶ ↑
# coderay ruby issue. joins(:fts_issue). where(["MATCH(fts_issues.subject, " + "fts_issues.description) " + "AGAINST (? IN BOOLEAN MODE)", # ↓デフォルトANDで全文検索 "*D+ #{keywords.join(', ')}"])
スライドプロパティ¶ ↑
: groonga-product
mroonga
Redmine+Mroonga:まとめ¶ ↑
* (('wait'))トランザクション必須 * 元テーブルを置き換えない * 全文検索用テーブルを作成 * (('wait'))データ * アプリが複数テーブルに保存 * (('wait'))全文検索 * ((*JOIN*))して((*MATCH AGAINST*))
スライドプロパティ¶ ↑
: groonga-product
mroonga
Redmine+PGroonga:方針¶ ↑
* (('wait'))全文検索用インデックス作成 * (('wait'))インデックスに主キーを含める * 検索スコアーを取得するため
スライドプロパティ¶ ↑
: groonga-product
pgroonga
マイグレーション¶ ↑
# coderay ruby def up enable_extension("pgroonga") add_index(:issues, [:id, :subject, :description], using: "pgroonga") end
スライドプロパティ¶ ↑
: groonga-product
pgroonga
モデル¶ ↑
追加・変更なし
スライドプロパティ¶ ↑
: groonga-product
pgroonga
保存¶ ↑
追加・変更なし
スライドプロパティ¶ ↑
: groonga-product
pgroonga
全文検索¶ ↑
# coderay ruby issue. # 検索対象のカラムごとに # クエリーを指定 where(["subject @@ ? OR " + "description @@ ?", keywords.join(", "), keywords.join(", ")])
スライドプロパティ¶ ↑
: groonga-product
pgroonga
Redmine+PGroonga:まとめ¶ ↑
* (('wait'))インデックス追加のみでOK * トランザクション対応 * データ保存も変更なし * (('wait'))全文検索 # coderay sql カラム1 @@ 'クエリー' OR カラム2 @@ 'クエリー' OR ...
スライドプロパティ¶ ↑
: groonga-product
pgroonga
Redmine:まとめ¶ ↑
* (('wait'))速い! * (('wait'))Mroonga * 全文検索用テーブルで実現 * (('wait'))PGroonga * 全文検索用インデックスで実現
Zulip¶ ↑
# image # src = images/zulip-full-text-search-form.png # relative_height = 95
Zulipの全文検索機能¶ ↑
* (('wait'))現在:textsearch * PostgreSQL標準機能 * 英語のみ対応 * (('wait'))NEW!:PGroonga * オプション(=PGroongaに切替可) * 全言語対応(日本語を含む)
スライドプロパティ¶ ↑
: groonga-product
pgroonga
Zulip+PGroonga:方針¶ ↑
* 書き込み速度を落とさない * チャットは書き込みが遅いと微妙 * インデックスは裏で更新\n (('note:PGroongaならリアルタイム更新でも大丈夫かも'))
(('note:詳細:github.com/zulip/zulip/pull/700/files'))
スライドプロパティ¶ ↑
: groonga-product
pgroonga
マイグレーション¶ ↑
# coderay python 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); """, "...")
スライドプロパティ¶ ↑
: groonga-product
pgroonga
遅延インデックス更新¶ ↑
* メッセージ追加・更新時にログ * 別プロセスでログを監視
スライドプロパティ¶ ↑
: groonga-product
pgroonga
追加・更新時にログ¶ ↑
# coderay sql 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();
スライドプロパティ¶ ↑
: groonga-product
pgroonga
別プロセスに通知¶ ↑
# coderay sql 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();
スライドプロパティ¶ ↑
: groonga-product
pgroonga
インデックス更新プロセス¶ ↑
# coderay python 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)
スライドプロパティ¶ ↑
: groonga-product
pgroonga
インデックス更新¶ ↑
# coderay python 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,))
スライドプロパティ¶ ↑
: groonga-product
pgroonga
全文検索¶ ↑
# coderay python 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)
スライドプロパティ¶ ↑
: groonga-product
pgroonga
ハイライト¶ ↑
# image # src = images/zulip-highlight.png # relative_height = 95
スライドプロパティ¶ ↑
: groonga-product
pgroonga
ハイライト:SQL¶ ↑
# coderay sql SELECT pgroonga.match_positions_byte( rendered_content, pgroonga.query_extract_keywords('クエリー')) AS content_matches, pgroonga.match_positions_byte( subject, pgroonga.query_extract_keywords('クエリー')) AS subject_matches
スライドプロパティ¶ ↑
: groonga-product
pgroonga
ハイライト:SQLAlchemy¶ ↑
# coderay python from sqlalchemy import func def _by_search_pgroonga(self, query, operand): match_positions_byte = func.pgroonga.match_positions_byte query_extract_keywords = func.pgroonga.query_extract_keywords keywords = query_extract_keywords(operand) query = query.column( match_positions_byte(column("rendered_content"), keywords).label("content_matches")) query = query.column( match_positions_byte(column("subject"), keywords).label("subject_matches")) # ...
スライドプロパティ¶ ↑
: groonga-product
pgroonga
ハイライト:Python¶ ↑
# coderay python def highlight_string_bytes_offsets(text, locs): # type: (AnyStr, Iterable[Tuple[int, int]]) -> text_type string = text.encode('utf-8') highlight_start = b'<span class="highlight">' highlight_stop = b'</span>' pos = 0 result = b'' for loc in locs: (offset, length) = loc result += string[pos:offset] result += highlight_start result += string[offset:offset + length] result += highlight_stop pos = offset + length result += string[pos:] return result.decode('utf-8')
スライドプロパティ¶ ↑
: groonga-product
pgroonga
ハイライト:補足¶ ↑
* 通常はハイライト関数で十分 * (({pgroonga.highlight_html})) * ただし(({ts_headline}))では不十分 * HTML出力に使えない
スライドプロパティ¶ ↑
: groonga-product
pgroonga
ハイライト:ts_headline¶ ↑
# coderay sql SELECT ts_headline('english', 'PostgreSQL <is> great!', to_tsquery('PostgreSQL'), 'HighlightAll=TRUE'); -- ts_headline -- ------------------------------- -- <b>PostgreSQL</b> <is> great! -- (1 row) 不正なHTML↑
スライドプロパティ¶ ↑
: groonga-product
pgroonga
ハイライト:pgroonga.highlight_html¶ ↑
# coderay sql SELECT pgroonga.highlight_html( 'PostgreSQL <is> great!', pgroonga.query_extract_keywords('PostgreSQL')); -- highlight_html -- ---------------------------------------- -- <span class="keyword">PostgreSQL</span> -- <is> great! -- ↑ ↑HTMLエスケープされている
スライドプロパティ¶ ↑
: groonga-product
pgroonga
Zulip:まとめ¶ ↑
* (('wait'))全言語対応全文検索 * textsearch(1言語のみ)→\n PGroonga(全言語) * (('wait'))書き込み性能は維持 * 遅延インデックス更新
スライドプロパティ¶ ↑
: groonga-product
pgroonga
まとめ¶ ↑
* (('wait'))Mroonga・PGroongaの\n 導入方法を実例ベースで紹介 * Redmine:チケット管理システム * Zulip:チャットツール * (('wait'))トランザクション必須の場合 * Mroonga:別テーブル作成 * PGroonga:インデックス追加