LoginSignup
41
38

More than 3 years have passed since last update.

@nayuneko

Mroongaの全文検索がいい感じだった

Last updated at Posted at 2019-01-24

tl;dr

  • InnoDBの全文検索自体は遅くない
    • ただしブール全文検索を行い別項目でソートを行うと、とたんに遅くなる
    • LIMITで取得件数を絞ってもあまり変わらない
  • Mroongaには全文検索特化の最適化がありレスポンスが早い！

ことのはじまり

地味に溜めていたテキスト情報が1000万レコードを超え、そろそろLIKE検索も限界なので、MySQL5.7から使えるようになったMeCabプラグインを使い全文検索機能を実装してみました。実装当初はそこまでレスポンスが悪くないと思っていたのですが、それなりのレコード数のあるワードを入力し、ソート条件を指定するとソートキーがたとえPKやインデックスが貼られているカラムでも劇重に！(おそらく１テーブルに使えるインデックスは1つまでというMySQLの制約)
別の方法がないか模索していたところ、Mroongaエンジンの全文検索を使ってみたらいい感じだったのでどれぐらい速度が変わるのか比較してみました。

Mroongaとは

Mroongaは全文検索エンジンであるGroongaをベースとしたMySQLのストレージエンジンです。

MroongaをMySQLに組み込むことによって、InnoDBやMyISAMなどの他のストレージエンジンと同じように使えるようになります。プラグインで提供されているので後からサクッと組み込むことも可能です。
インストールの仕方は公式サイトに記載されていますので、そちらをご覧ください。

2. インストール — Mroonga v8.09 documentation

比較検証してみた

検証環境

  • KAGOYA VPS (CPU：3コア／メモリ：3G／SSD：50G)
  • CentOS 7
  • MySQL 5.7.24
    • innodb_ft_min_token_size=2に設定
  • Mroonga 8.09 (ストレージモードで使用)
  • MeCab 0.996
  • NEologd (MeCab用辞書)

検証内容

  • Twitterから適当にツイートを取得
  • 各ストレージエンジンにFULLTEXTインデックスを張った全文検索用テーブルにツイートを追加
  • 特定のワードで全文検索し、投稿順の降順で並び替え(ORDER BY)、100件ずつ(LIMIT)取得する。
    • これができればページングも容易

まずはテーブル作成

-- ツイート情報テーブル
CREATE TABLE statuses (
  status_id bigint(20) UNSIGNED NOT NULL PRIMARY KEY,
  tweet text NOT NULL,
  user_id bigint(20) UNSIGNED NOT NULL,
  created_at datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 全文検索用テーブル (Mroonga)
CREATE TABLE tweets (
  status_id bigint(20) UNSIGNED NOT NULL PRIMARY KEY,
  tweet text NOT NULL
) ENGINE=mroonga DEFAULT CHARSET=utf8mb4;

-- 全文検索用テーブル (InnoDB)
CREATE TABLE tweet_i (
  status_id bigint(20) UNSIGNED NOT NULL PRIMARY KEY,
  tweet text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tweetsテーブルにTwitterから取得したそのままのツイートデータを格納します。
全文検索用のテーブルとしてtweets(Mroonga)とtweet_i(InnoDB)を用意しました。
全文検索用のテーブルにツイートを追加する前にNEologd推奨のノーマライズ処理に加え、TwitterのメンションやハッシュタグやURLなどのエンティティ情報を取り除いたテキストを全文検索用テーブルのtweetカラムに設定します。

TwitterではstatusIDでツイートをユニーク判定できるので、それぞれのテーブルのPKに設定します。(StatusID=投稿順なのでソートにも使用します)

全文検索用のインデックス作成

-- Mroongaでの全文検索インデックス
ALTER TABLE tweets ADD FULLTEXT INDEX idx_tweets_fulltext_mecab (tweet) COMMENT 'parser "TokenMecab"';
-- InnoDBでの全文検索インデックス
ALTER TABLE tweet_i ADD FULLTEXT INDEX idx_tweet_i_fulltext_mecab (tweet) WITH PARSER mecab;

Mroongaではコメントでパーサを指定します。

これで準備は整ったので、適当にツイートを集めてガンガンテーブルに突っ込んでください！

まずはSELECT COUNT(*)

mysql > SELECT COUNT(status_id) FROM tweets;
+----------+
| COUNT(*) |
+----------+
| 10086028 |
+----------+
1 row in set (0.00 sec)

mysql > SELECT COUNT(status_id) FROM tweet_i;
+----------+
| COUNT(*) |
+----------+
| 10086028 |
+----------+
1 row in set (5.81 sec)

はい、1000万レコードほど用意しました！

すでにCOUNT(*)でかなりの差が出てます。
InnoDBのCOUNT(*)はテーブルスキャンが走るのですごく遅いんですね…MyISAMだと早いんですけどね。
漢(オトコ)のコンピュータ道: InnoDBでCOUNT()を扱う際の注意事項あれこれ。

1000万レコードで全文検索の検証を行います。

行カウント最適化

Mroongaの行カウントはインデックスのみで処理が行えるように最適化されています。
1. 最適化 — Mroonga v8.09 documentation
行カウントの最適化が行われたかどうかはMroonga_count_skipステータス変数を参照すると確認できます。

全レコードの1.5%程度に該当するワードで検索してみます。(例では「ほげほげ」となっています)

> SHOW STATUS LIKE 'Mroonga_count_skip';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Mroonga_count_skip | 30038 |
+--------------------+-------+

mysql > SELECT COUNT(*) FROM tweets WHERE MATCH(text) AGAINST('ほげほげ' IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
|   157772 |
+----------+
1 row in set (0.06 sec)

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Mroonga_count_skip | 30039 |
+--------------------+-------+
1 row in set (0.03 sec)

mysql > SELECT COUNT(*) FROM tweet_i WHERE MATCH(text) AGAINST('ほげほげ' IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
|   157778 |
+----------+
1 row in set (0.22 sec)

Mroonga_count_skipがカウントアップしているので最適化がちゃんと使われているようです！
カウント数が一致しないのは、おそらくノーマライザの精度のせいだと思われます。

しかし、このレスポンスタイムは誤差範囲なような気もするのでヒット数が多い「から」というワード(全レコードの9%程度)を入れて検索してみます。

mysql > SELECT COUNT(*) FROM tweets WHERE MATCH(text) AGAINST('から' IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
|   904492 |
+----------+
1 row in set (0.36 sec)

mysql > SELECT COUNT(*) FROM tweet_i WHERE MATCH(text) AGAINST('から' IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
|   904104 |
+----------+
1 row in set (1.75 sec)

なんとその差は5倍近く！
これは体感で差がわかるぐらい縮まりましたね。

ソート条件なしに全文検索

ORDER BY句をつけずに検索してみます。

SELECT * FROM `tweets` WHERE MATCH(text) AGAINST('ほげほげ' IN BOOLEAN MODE);
157774 rows in set (2.67 sec)

SELECT * FROM `tweet_i` WHERE MATCH(text) AGAINST('ほげほげ' IN BOOLEAN MODE)
157780 rows in set (12.54 sec)
Engine time(sec)
Mroonga 2.67
InnoDB 12.54

既にそこそこ差が出ています。

ORDER BY LIMIT最適化

全文検索インデックスキー以外のカラムでソートを行い、フェッチする件数を絞り込むクエリを投げるとInnoDBだと劇重ですが、Mroongaを利用すると最適化が働いて早くなります。

1. 最適化 — Mroonga v8.09 documentation

ORDER BY LIMIT最適化が適用される条件は以下の通りです。(公式サイトよりストレージモードのみ抜粋)

  • WHERE節にある条件が1つのMATCH AGAINSTと0個以上の算術演算だけのとき
  • JOINがない
  • GROUP BYがない
  • SQL_CALC_FOUND_ROWSがない
  • LIMITがある
  • ORDER BY節にはカラムまたはWHERE節で使っているものと同じMATCH AGAINSTしかない

どのぐらい早くなるか比較してみましょう。
ORDER BY LIMITの最適化が行われたかどうかはMroonga_fast_order_limitステータス変数を参照すると確認できます。

mysql > SHOW STATUS LIKE 'Mroonga_fast_order_limit';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Mroonga_fast_order_limit | 109   |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql > SELECT * FROM tweets WHERE MATCH(text) AGAINST('ほげほげ' IN BOOLEAN MODE) ORDER BY status_id DESC LIMIT 100;
+-----------+----------+
| status_id | text     |
+-----------+----------+
| XXXXXXXXX | ほげほげ１  |
.......................
| YYYYYYYYY | ほげほげ２  |
+-----------+----------+
100 rows in set (0.20 sec)

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Mroonga_fast_order_limit | 110   |
+--------------------------+-------+
1 row in set (0.01 sec)

mysql > SELECT * FROM tweet_i WHERE MATCH(text) AGAINST('ほげほげ' IN BOOLEAN MODE) ORDER BY status_id DESC LIMIT 100;
+-----------+----------+
| status_id | text     |
+-----------+----------+
| XXXXXXXXX | ほげほげ１  |
.......................
| YYYYYYYYY | ほげほげ２  |
+-----------+----------+
100 rows in set (9.18 sec)
Engine time(sec)
Mroonga 0.20
InnoDB 9.18

は、早い！
Mroonga_fast_order_limitがカウントアップしているので、ORDER BY LIMIT最適化もちゃんと働いているようです。

結合してみる

このままだと使い勝手が悪いので別テーブルと結合してみます。
MySQLって異なるストレージエンジンでの結合できるんですね。
それが問題ないのかアンチパターンかどうかは不明ですが:thinking:

全文検索用テーブルでワードの絞り込みを行い、statusesテーブルとPKで結合し、statusesの内容を表示されます。

まずは普通にINNER JOINで結合してみます。

mysql > SHOW STATUS LIKE 'Mroonga_fast_order_limit';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Mroonga_fast_order_limit | 111   |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql > SELECT s.* FROM statuses s INNER JOIN tweets t ON s.status_id = t.status_id WHERE MATCH(t.text) AGAINST('ほげほげ' IN BOOLEAN MODE) ORDER BY t.status_id DESC LIMIT 100;
+-----------+--------+--------+
| status_id | text   |user_id |
+-----------+--------+--------+
| XXXXXXXXX | ほげほげ |        |
...............................
| YYYYYYYYY | ふがふが |        |
+-----------+--------+--------+
100 rows in set (2.63 sec)

mysql > SHOW STATUS LIKE 'Mroonga_fast_order_limit';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Mroonga_fast_order_limit | 111   |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql > SELECT s.* FROM statuses s INNER JOIN tweet_i t ON s.status_id = t.status_id WHERE MATCH(t.text) AGAINST('ほげほげ' IN BOOLEAN MODE) ORDER BY t.status_id DESC LIMIT 100;
+-----------+--------+--------+
| status_id | text   |user_id |
+-----------+--------+--------+
| XXXXXXXXX | ほげほげ |        |
...............................
| YYYYYYYYY | ふがふが |        |
+-----------+--------+--------+
100 rows in set (8.79 sec)
Engine time(sec)
Mroonga 2.63
InnoDB 8.79

結合するとかなり遅くなりますね。Mroonga_fast_order_limitがカウントアップされていないため、ORDER BY LIMIT最適化も使用されていません。(JOINがあるので当然ですが)

サブクエリで絞った後、結合

それではサブクエリを使ってみたらどうでしょうか？
サブクエリでORDER BY LIMIT最適化が効くようにSQLを書き直してみます。

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Mroonga_fast_order_limit | 111   |
+--------------------------+-------+
1 row in set (0.01 sec)

mysql > SELECT s.*
FROM statuses s
INNER JOIN (
  SELECT
    status_id
  FROM
    tweets
  WHERE
    MATCH(text) AGAINST('ほげほげ' IN BOOLEAN MODE)
  ORDER BY
    status_id DESC
  LIMIT 100
) t
ON s.status_id = t.status_id
ORDER BY s.status_id DESC;
+-----------+--------+--------+
| status_id | text   |user_id |
+-----------+--------+--------+
| XXXXXXXXX | ほげほげ |        |
...............................
| YYYYYYYYY | ふがふが |        |
+-----------+--------+--------+
100 rows in set (0.11 sec)

mysql > SHOW STATUS LIKE 'Mroonga_fast_order_limit';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Mroonga_fast_order_limit | 112   |
+--------------------------+-------+
1 row in set (0.01 sec)

mysql > SELECT s.*
FROM statuses s
INNER JOIN (
  SELECT
    status_id
  FROM
    tweet_i
  WHERE
    MATCH(text) AGAINST('ほげほげ' IN BOOLEAN MODE)
  ORDER BY
    status_id DESC
  LIMIT 100
) t
ON s.status_id = t.status_id
ORDER BY s.status_id DESC;
+-----------+--------+--------+
| status_id | text   |user_id |
+-----------+--------+--------+
| XXXXXXXXX | ほげほげ |        |
...............................
| YYYYYYYYY | ふがふが |        |
+-----------+--------+--------+
100 rows in set (7.71 sec)
Engine time(sec)
Mroonga 0.11
InnoDB 7.71

おお、早くなってる！ちゃんとORDER BY LIMIT最適化も使われてます。

WHERE句に条件を加える

WHERE句でstatus_idによるページング処理をしてみます。
status_idの降順で並んでいるため、１ページ目の最後のstatus_idがわかれば２ページ目はその値未満となるので以下のようなSQLになります。

mysql > SHOW STATUS LIKE 'Mroonga_fast_order_limit';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Mroonga_fast_order_limit | 114   |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql > SELECT s.*
FROM statuses s
INNER JOIN (
  SELECT
    status_id
  FROM
    tweets
  WHERE
    MATCH(text) AGAINST('ほげほげ' IN BOOLEAN MODE)
    AND status_id < YYYYYYYYY
  ORDER BY
    status_id DESC
  LIMIT 100
) t
ON s.status_id = t.status_id
ORDER BY s.status_id DESC;
+-----------+--------+--------+
| status_id | text   |user_id |
+-----------+--------+--------+
| ZZZZZZZZZ | ほげほげ |        |
...............................
| 000000000 | ふがふが |        |
+-----------+--------+--------+
100 rows in set (0.11 sec)

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Mroonga_fast_order_limit | 115   |
+--------------------------+-------+
1 row in set (0.01 sec)

mysql > SELECT s.*
FROM statuses s
INNER JOIN (
  SELECT
    status_id
  FROM
    tweet_i
  WHERE
    MATCH(text) AGAINST('ほげほげ' IN BOOLEAN MODE)
    AND status_id < YYYYYYYYY
  ORDER BY
    status_id DESC
  LIMIT 100
) t
ON s.status_id = t.status_id
ORDER BY s.status_id DESC;
+-----------+--------+--------+
| status_id | text   |user_id |
+-----------+--------+--------+
| ZZZZZZZZZ | ほげほげ |        |
...............................
| 000000000 | ふがふが |        |
+-----------+--------+--------+
100 rows in set (6.90 sec)
Engine time(sec)
Mroonga 0.11
InnoDB 6.90

WHERE句で条件指定しても全然早い！

そのほか

同義語展開の使い勝手が良かった

mroonga_query_expandというユーザ定義関数を使うとテーブルに登録されているワードを展開し、OR条件としてクエリを発行してくれます！

例：「アイドルマスター」と検索した時に「アイドルマスター」と省略語である「アイマス」で引っ掛けたい。

CREATE TABLE synonyms (
  id int PRIMARY KEY auto_increment,
  term varchar(255) NOT NULL,
  synonym varchar(255) NOT NULL,
  INDEX term (term) USING BTREE COMMENT 'normalizer "NormalizerAuto"'
) ENGINE=Mroonga DEFAULT CHARSET=utf8mb4;

termカラムには検索に使いたいワード、synonymには同義語としてOR条件に加えたいワードを入力します。
※IDカラムはなくても問題ないですが、管理上不便なので追加しています。

INSERT INTO synonyms (term, synonym) VALUES
('アイドルマスター', 'アイドルマスター'), -- 自分自身も登録しないとダメみたい
('アイドルマスター', 'アイマス');

SELECT s.* FROM statuses s
INNER JOIN (SELECT status_id FROM tweets 
WHERE MATCH (text) AGAINST(
  -- synonymsテーブルを参照し、((アイドルマスター) OR (アイマス))に展開してくれる
  mroonga_query_expand('synonyms', 'term', 'synonym', 'アイドルマスター')
  IN BOOLEAN MODE
) ORDER BY status_id DESC LIMIT 100) t
ON t.status_id = s.status_id
ORDER BY s.status_id DESC;
+-----------+--------------+--------+
| status_id | text         |user_id |
+-----------+--------------+--------+
| @@@@@@@@@ | アイマス       |        |
.....................................
| 765765765 | アイドルマスター |        |
+-----------+--------------+--------+

同義語をテーブルで管理できるのは便利でいいですね。

Mroongaの注意点

個人的に気になったことを。

容量はそれなりに食う

速度が早くなったトレードオフとしてストレージを結構使うようです。

Engine size (GB)
Mroonga 2.7
InnoDB 1.5

倍ぐらい使ってますね。。

NULLの設定ができない

今回は全文検索用のテーブルに切り出したので特に問題にはなりませんでしたが、NULLの扱いが他のストレージと異なるので注意してください。
NULL許容カラムにNULLをINSERTやUPDATEしてもNULLにならなくて、これで半日ぐらいハマった…

6. 制限事項 — Mroonga v8.09 documentation

参考資料

MariaDBとMroongaで作る全言語対応超高速全文検索システム

41
38
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
41
38