Rails+MySQL InnoDB FTSで全文検索する

全文検索する方法として、データが少量であればただのLIKE検索で、大規模になるとSolrとかElasticsearchとか専用の全文検索エンジン使うというかんじになるかと思うのだけど、ビックデータというほどでもないけどLIKE検索だとちょっと遅いという場合に、MySQL機能で全文検索するというお手軽な方法があるようなので試してみた。専用の全文検索サーバ立てなくても導入できるのでまずは手軽にMySQLで試してみて、性能足りなければ本格的な全文検索エンジンの導入という方針でもよいんじゃないかと。

MySQLの全文検索は以前はMyISAMだったようなんだけど、MySQL5.6.4からInnoDB FTSという機能が追加されInnoDBでも全文検索できる。ただ日本語対応してないようなので日本語を全文検索したいとか更新性能がとか気になる場合は、Mroongaというプラグインがあるのでそっち使うのがよいそうです。今回の用途では英語で全文検索できれば十分だったので、標準で使えるInnoDB FTSを使ってみてRailsに組み込むところまでのメモ。

手元の環境はRails4.2.0でMySQL5.6.23です。

MySQLの全文検索

まずはMySQL単体で全文検索の機能確認する。

公式のマニュアルは以下にあるんだけど、
http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html

まぁ試しに使ってみるのが早いよねぇということで、テスト用のDBとテーブル作る。

mysql> CREATE DATABASE fulltext_test DEFAULT CHARACTER SET utf8;
mysql> use fulltext_test
mysql> CREATE TABLE test (
  id INT PRIMARY KEY AUTO_INCREMENT,
  message TEXT,
  FULLTEXT INDEX (message)
) ENGINE=InnoDB DEFAULT CHARSET utf8 collate utf8_unicode_ci;

ポイントはFULLTEXT INDEX (message)と書いておけばそれだけでtext型のカラムmessageに検索インデックス作ってくれる。text以外にも以外にもcharとかvarcharとかでも使えるっぽいです。
collate utf8_unicode_ciというのは比較方法をどこまで一致とみなすかという指定で、utf8_unicode_ciだと大雑把な理解として大文字小文字は区別しないぐらい。utf8_general_ciとかするとウムラウト付いてる文字とか似てる文字もマッチさせるらしい。
http://dev.mysql.com/doc/refman/5.6/en/charset-collation-implementations.html

ちなみにデフォルトはutf8_general_ciでデフォルト値はSHOW CHARACTER SETすると確認できる。

mysql> SHOW CHARACTER SET WHERE charset='utf8';
+---------+---------------+-------------------+--------+
| Charset | Description   | Default collation | Maxlen |
+---------+---------------+-------------------+--------+
| utf8    | UTF-8 Unicode | utf8_general_ci   |      3 |
+---------+---------------+-------------------+--------+
1 row in set (0.00 sec)

テスト用のデータを投入

mysql> INSERT INTO test (message) VALUES ('hoge hoge');
mysql> INSERT INTO test (message) VALUES ('hoge fuga');
mysql> INSERT INTO test (message) VALUES ('fuga fuga');
mysql> SELECT * FROM test;
+----+-----------+
| id | message   |
+----+-----------+
|  1 | hoge hoge |
|  2 | hoge fuga |
|  3 | fuga fuga |
+----+-----------+
3 rows in set (0.00 sec)

文字列hogeで検索してみる。MATCHで検索カラムを指定して、AGAINSTで検索したい文字列を+で指定する。

mysql> SELECT * FROM test WHERE MATCH(message) AGAINST('+hoge' IN BOOLEAN MODE);
+----+-----------+
| id | message   |
+----+-----------+
|  1 | hoge hoge |
|  2 | hoge fuga |
+----+-----------+
2 rows in set (0.00 sec)

複数キーワードのAND検索の場合はスペースで区切って+を並べればよい。

mysql> SELECT * FROM test WHERE MATCH(message) AGAINST('+hoge +fuga' IN BOOLEAN MODE);
+----+-----------+
| id | message   |
+----+-----------+
|  2 | hoge fuga |
+----+-----------+
1 row in set (0.00 sec)

ちなみにIN BOOLEAN MODEというのはマッチの条件を厳密に指定する検索モードで、他に例えば検索モードでIN NATURAL LANGUAGE MODEを指定すると自然言語で曖昧な検索になるっぽい。詳しいアルゴリズムは難しそうなのでまだちゃんと調べてないです。実際にデータ投入してみてどんなものが引っかかるか試してみて使いドコロを判断すればよいんじゃないでしょうか。
指定できる検索モードは以下に記載があり。
http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html

mysql> SELECT * FROM test WHERE MATCH(message) AGAINST('hoge fuga' IN NATURAL LANGUAGE MODE);
+----+-----------+
| id | message   |
+----+-----------+
|  1 | hoge hoge |
|  2 | hoge fuga |
|  3 | fuga fuga |
+----+-----------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM test WHERE MATCH(message) AGAINST('hoge hoge' IN NATURAL LANGUAGE MODE);
+----+-----------+
| id | message   |
+----+-----------+
|  1 | hoge hoge |
|  2 | hoge fuga |
+----+-----------+
2 rows in set (0.00 sec)

Railsに組み込む

MySQL単体で全文検索の使い方がなんとなくわかってきたところで、Railsへの組み込みをしてみる。
マイグレーションの書き方ググるとFULLTEXT INDEX対応していないので生のSQLをマイグレーションに書けばよいという情報が散見されるのだけど、stack over flowでtypeにfulltext指定できるよという記載があったので試してみた。
full-text mysql search in rails - Stack Overflow

元のcreate tableしたテーブル構造こんなかんじです。messageのカラムを全文検索したい。

class CreateCommits < ActiveRecord::Migration
  def change
    create_table :commits do |t|
      t.string :repo_full_name
      t.string :sha
      t.text :message

      t.timestamps null: false
    end
  end
end

全文検索インデックス追加用に新しいマイグレーションファイルを生成する。

$ bundle exec rails g migration AddFulltextIndexToCommit

マイグレーションの中身はこんなかんじで書く。例はcommitsテーブルのmessageカラムにfulltext indexを貼りたい場合。各自テーブル名やカラム名は読み替えてください。

class AddFulltextIndexToCommit < ActiveRecord::Migration
  def change
    add_index :commits, :message, type: :fulltext
  end
end

マイグレーション実行する。

$ bundle exec rake db:migrate

実際にMySQL上のテーブルとインデックス定義を見てみる。

mysql> SHOW CREATE TABLE commits;

CREATE TABLE `commits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `repo_full_name` varchar(255) DEFAULT NULL,
  `sha` varchar(255) DEFAULT NULL,
  `message` text,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `index_commits_on_message` (`message`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8

mysql> SHOW INDEX FROM commits;
+---------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name                 | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| commits |          0 | PRIMARY                  |            1 | id          | A         |        1000 |     NULL | NULL   |      | BTREE      |         |               |
| commits |          1 | index_commits_on_message |            1 | message     | NULL      |        1000 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+---------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

messageカラムにFULLTEXTインデックスがちゃんとできたよう。

mysql> SHOW TABLE STATUS FROM db_development WHERE name='commits';
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name    | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| commits | InnoDB |      10 | Compact    | 1000 |            212 |      212992 |               0 |        49152 |         0 |           1001 | 2015-02-06 07:46:26 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

collationがutf8_general_ciになってる。まぁ今回は検索対象英文でそこまで比較順序に熱いこだわりないのでどっちでもいいや。。。問題あってから考える。
ちなみにcollationを指定したい場合は、config/database.ymlでデフォルトのcollationが指定できるらしいです。
ActiveRecordでデフォルトの照合順序を変更する - Qiita

一応生のSQLでちゃんと検索できるかも確認しておく。

mysql> SELECT COUNT(*) FROM commits WHERE MATCH(message) AGAINST('+fix' IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
|      125 |
+----------+
1 row in set (0.00 sec)

ちゃんと検索ヒットしてるのでMySQL側はこれで問題なさそう。

次にRails側のクエリ投げる方法を修正する。
まず、コントローラからモデルに検索キーワードを渡す。

class CommitsController < ApplicationController
  (略)
  def search
    @keyword = params[:keyword]
    @commits = Commit.search_message(@keyword).paginate(page: params[:page])
  end
end

モデル側で受け取った検索キーを空白区切りで分解して若干加工して、whereに渡す。

class Commit < ActiveRecord::Base
  def self.search_message(keyword)
    against_key = keyword.split.map { |key| key.insert(0,'+') }.join(' ')
    where("match(message) against (? in boolean mode)", "#{against_key}")
  end
end

文字列加工が分かりにくいけど、例えば"fix merge"という文字列を渡された場合は

WHERE (match(message) against ('+fix +merge' in boolean mode)

となるように加工してる。

準備できたら実際にRailsアプリ上から検索してみてログで発行されてるSQL文確認する。

SELECT  `commits`.* FROM `commits` WHERE (match(message) against ('+fix +merge' in boolean mode)) LIMIT 30 OFFSET 0

paginateしてるのでLIMITがついてるけど、matchとagainstが想定通りのSQL文になってる。

性能計測はデータ準備できてからあとでやるけど、まぁ機能的にはこれでRailsから全文検索できるようになった。MySQL標準範囲でこの程度のアプリ修正だけで対応できるのでお手軽に全文検索したいときによいんじゃないかと思う。

（追記）簡単に性能測ってみた
MySQL InnoDB FTSで簡単に全文検索の性能測ってみた - 城陽人の本棚

参考