MySQL InnoDBだけで全文検索 を試してみる

mysql

　sh2さんの人気エントリーMySQL InnoDBだけで全文検索で実運用環境で導入を検討したときのメモなどを紹介します。上記記事と合わせて何かの参考になれば幸いです。

　sh2さんの記事では実験エントリーなので、インデックス作成用のトリガーを設定してから初期データを流し込んでいます。実運用環境では、既にテーブルが存在している場合がほとんどだと思いますので、まずは、対象となるテーブルに対して転置インデックスを作れるようにカーソルを使ったストアドプロシージャを作成します。

DROP PROCEDURE IF EXISTS timeline_create_bigram;
DELIMITER //
CREATE PROCEDURE timeline_create_bigram()
BEGIN
 DECLARE done          INT DEFAULT 0;
 DECLARE c_id          INT;
 DECLARE c_create_at   INT;
 DECLARE c_screen_name VARCHAR(15);
 DECLARE c_text        VARCHAR(140);
 DECLARE text_length   INT;
 DECLARE text_index    INT;
 DECLARE c_timeline CURSOR FOR SELECT id,create_at,screen_name,text FROM timeline;
 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

 OPEN c_timeline;

 REPEAT
  FETCH c_timeline INTO c_id , c_create_at , c_screen_name , c_text;
  IF NOT done THEN
    SET text_length = CHAR_LENGTH(c_text);
    SET text_index = 1;
    WHILE text_index < text_length DO
      INSERT IGNORE INTO timeline_i (bigram,id)
             VALUES (SUBSTRING(c_text, text_index, 2),c_id);
      SET text_index = text_index + 1;
    END WHILE;
  END IF;
 UNTIL done END REPEAT;
 CLOSE c_timeline;
END;
//
DELIMITER ;

検索方法を改良

　元エントリーの『なかにし』さんのコメントを参考に、キーワード先頭と末尾で検索するように改良したものを採用しました。

SELECT SQL_NO_CACHE t . *
FROM timeline t
INNER JOIN timeline_i i1 ON t.id = i1.id
INNER JOIN timeline_i i2 ON t.id = i2.id
WHERE
i1.bigram =  '味噌' AND
i2.bigram =  'メン' AND
t.message_body LIKE  '%味噌ラーメン%'

　ちなみに全部指定したら逆に遅くなりました。

SELECT SQL_NO_CACHE t . *
FROM timeline t
INNER JOIN timeline_i i1 ON t.id = i1.id
INNER JOIN timeline_i i2 ON t.id = i2.id
INNER JOIN timeline_i i3 ON t.id = i3.id
INNER JOIN timeline_i i4 ON t.id = i4.id
INNER JOIN timeline_i i5 ON t.id = i5.id
WHERE
i1.bigram =  '味噌' AND
i2.bigram =  '噌ラ' AND
i3.bigram =  'ラー' AND
i4.bigram =  'ーメ' AND
i5.bigram =  'メン' AND
t.message_body LIKE  '%味噌ラーメン%'

課題など

  • インデックス化にかかる時間

　元記事の通りとんでもなく時間が掛かります。それなりのデータ量だった為インデックスの作成に2日くらい掛かりました。インデックス作成の間中DBに負荷が掛かることになるので、実運用環境だと導入方法は考える必要があります。後述

  • デイスク使用量

　インデックステーブルだけで元テーブルの4倍になりました。最近のディスク単価からすれば、こちらはトレードオフしてもよいところですが、思わぬところでディスクが圧迫されるので注意が必要です。後述

  • リプリケーション環境

　たいていの場合、大規模サービスではリプリケーションをしているかと思いますが、これが思わぬ事態を引き起こします。元エントリーの転置インデックスは2文字毎に文字を切り出しています。そのためそのレコード数は数千万レコードに達します。そうなるとbin-logがえらいディスク容量を消費します。インデックス自体のディスク容量どころではなく、下手をするとDBサーバのディスクを食らい尽くす事になるので注意しましょう。尚、このレベルになるとbin-logのパージも小分けにしないと、パージ時にDBに極端な機能低下が発生します。また、スレーブサーバーへのリレーログの転送もネットワーク帯域を消耗するので注意が必要です。

　手元に検証結果がないので曖昧な記憶を頼りにエントリーを書いています。なにかの参考になれば幸いです。そのうち、再度ちゃんとした検証はしたいですね。