• WEB

MySQLで全文検索してみる

  • おさ
    おさ システムちーむ
  • このエントリーをはてなブックマークに追加
MySQLで全文検索してみる

MySQLでは以前より全文検索の機能がありましたが、日本語の検索はあまり得意ではありませんでした。しかし、バージョン5.7.6で日本語パーザとしてMeCabが利用できるようになりました。今回はこのMeCabを使ってMySQLで日本語の全文検索を試してみたいと思います。
また、比較のためMroongaも使用してみます。

環境

  • CentOS 6
  • MySQL 5.7.12
  • Mroonga 6.02

MeCabインストール

まず最初にMeCabをインストールします。MySQL5.7ではすでにライブラリの中にMeCabが含まれているので、いくつか設定をするだけで使用できるようになります。
(cf.[ 13.9.9 MeCab Full-Text Parser Plugin])

my.cnfの修正

MySQLの設定ファイルにmecab_rc_fileの設定を追加します。ファイルの存在場所は環境によって変わるので、それぞれに合ったパスに変更します。
またMeCabパーザを使用するときはinnodb_ft_min_token_sizeを1か2に設定します。

/etc/my.cnf

[mysqld]
loose-mecab-rc-file=/usr/lib64/mysql/mecab/etc/mecabrc
innodb_ft_min_token_size=1

mecabrcファイルの修正

MeCabがどの辞書を使用するかをmecabrcファイルに記述します。UTF-8の辞書を使用するには以下のようにします。これも環境によってパスを変更します。

/usr/lib64/mysql/mecab/etc/mecabrc

dicdir =  /usr/lib64/mysql/mecab/dic/ipadic_utf-8

プラグインのインストール

設定ファイルが修正できたら一度、MySQLを再起動します。その後MySQLに入ってプラグインを追加するコマンドを実行します。

mysql> INSTALL PLUGIN mecab SONAME 'libpluginmecab.so';

SHOW PLUGINS コマンドを実行してmecabプラグインが表示されていれば成功です。

Mroongaインストール

次にMroongaをインストールしていきます。CentOS 6、MySQL5.7バージョンのインストール方法に従ってインストールします。
(cf. [2.5.4. CentOS 6(OracleのMySQL 5.7パッケージを利用)])
MySQL5.7については既にインストール済みですので省略しています。

# groongaリポジトリの追加
sudo yum install -y http://packages.groonga.org/centos/groonga-release-1.1.0-1.noarch.rpm
# Mroongaインストール
sudo yum install -y mysql57-community-mroonga
# MeCabをトークナイザーとしてインストール
sudo yum install -y groonga-tokenizer-mecab

MySQLに入ってMroongaがインストールされたか確認します。

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| Mroonga            | YES     | CJK-ready fulltext search, column store                        | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)

データの用意

検索用のデータとして架空の名簿を作成します。
MeCab用のテーブルとmroonga用のテーブルを用意します。

MeCab用の名簿テーブル

CREATE TABLE `list_mecab` (
 `id` int unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(50) NOT NULL DEFAULT '',
 `tel` varchar(20) NOT NULL DEFAULT '',
 `postcode` char(7) NOT NULL DEFAULT '',
 `address` text,
 PRIMARY KEY(`id`),
 FULLTEXT KEY (`name`,`address`) WITH PARSER `mecab`
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Mroonga用の名簿テーブル

CREATE TABLE `list_mroonga` (
 `id` int unsigned NOT NULL AUTO_INCREMENT,w
 `name` varchar(50) NOT NULL DEFAULT '',
 `tel` varchar(20) NOT NULL DEFAULT '',
 `postcode` char(7) NOT NULL DEFAULT '',
 `address` text,
 PRIMARY KEY(`id`),
 FULLTEXT KEY (`name`,`address`)
) ENGINE=Mroonga DEFAULT CHARSET=utf8mb4;

IDと名前、電話番号、郵便番号、住所だけのシンプルなテーブルです。
名前と住所のカラムに全文検索のインデックスを追加しています。

MeCab用のテーブルでは、通常のInnoDBのテーブルにインデックスを貼るときに、mecabパーザを指定しています。
Mroonga用のテーブルでは、EngineをMroongaに指定するだけです。MroongaでもMeCabをパーザとして使用できますが、今回はデフォルトのパーザで試してみます。

このテーブルに100万件ほどの架空データを両方のテーブルに入力しました。

mysql> select * from list_mroonga;
+----+---------------------+-----------------+----------+--------------------------------------+
| id | name                | tel             | postcode | address                              |
+----+---------------------+-----------------+----------+--------------------------------------+
|  1 | 青渡 恵精           | 070-26067-80649 | 8114151  | 福岡県宗像市城南ケ丘                 |
|  2 | 吾籐 海             | 090-89556-18907 | 8500064  | 長崎県長崎市秋月町                   |
|  3 | 合葉 晶裕           | 080-39779-61707 | 7981323  | 愛媛県北宇和郡鬼北町岩谷             |
|  4 | 永宮 相絵利         | 080-07477-70601 | 0593454  | 北海道浦河郡浦河町野深               |
|  5 | 岩武 江莉菜         | 070-71416-72984 | 4700403  | 愛知県豊田市白川町                   |
|  6 | 秦泉寺 亜紀彦       | 090-14578-31857 | 3891204  | 長野県上水内郡飯綱町倉井             |
|  7 | 戸ノ岡 佐恵         | 080-97680-15845 | 5031532  | 岐阜県不破郡関ケ原町野上             |
・・・

検索してみる

それぞれのテーブルに対して検索を実行してみます。
全文検索をするときは IN BOOLEAN MODE をつけないと自然言語検索になってしまい、似たような文字列にヒットしてしまいます。

mysql> select * from list_mecab where match(name,address) against('+五反田' IN BOOLEAN MODE);
+--------+---------------------+-----------------+----------+--------------------------------------------+
| id     | name                | tel             | postcode | address                                    |
+--------+---------------------+-----------------+----------+--------------------------------------------+
|    968 | 多加谷 勳           | 090-61450-33774 | 3111306  | 茨城県東茨城郡大洗町五反田                 |
|   8927 | 朱林 和輝子         | 080-47669-90787 | 3111306  | 茨城県東茨城郡大洗町五反田                 |
|   9924 | 尼森 永志           | 080-36222-19644 | 5203423  | 滋賀県甲賀市甲賀町五反田                   |
|  17722 | 大墻 輝治           | 080-97326-85765 | 7968010  | 愛媛県八幡浜市五反田                       |
|  20090 | 純丘 范増           | 080-64889-34998 | 6800932  | 鳥取県鳥取市五反田町                       |
|  30373 | 荒崎 四子生         | 090-76593-98902 | 6800932  | 鳥取県鳥取市五反田町                       |
・・・
| 995706 | 相澤 貞芳           | 070-78839-03031 | 9290333  | 石川県河北郡津幡町五反田                   |
| 998610 | 雨沢 直海           | 080-17109-26385 | 5203423  | 滋賀県甲賀市甲賀町五反田                   |
+--------+---------------------+-----------------+----------+--------------------------------------------+
181 rows in set (0.01 sec)


mysql> select * from list_mroonga where match(name,address) against('+五反田' IN BOOLEAN MODE);
+--------+---------------------+-----------------+----------+--------------------------------------------------+
| id     | name                | tel             | postcode | address                                          |
+--------+---------------------+-----------------+----------+--------------------------------------------------+
|    968 | 多加谷 勳           | 090-61450-33774 | 3111306  | 茨城県東茨城郡大洗町五反田                       |
|   2345 | 弦間 八智代         | 090-19766-11406 | 6148071  | 京都府八幡市八幡五反田                           |
|   5730 | 鷹屋 楊慎           | 080-39689-36646 | 5040037  | 岐阜県各務原市那加五反田町                       |
|   8927 | 朱林 和輝子         | 080-47669-90787 | 3111306  | 茨城県東茨城郡大洗町五反田                       |
|   9924 | 尼森 永志           | 080-36222-19644 | 5203423  | 滋賀県甲賀市甲賀町五反田                         |
|  17452 | 穐森 文丈           | 070-54139-99558 | 1410031  | 東京都品川区西五反田                             |
・・・
| 995502 | 池水 新七           | 070-35955-46949 | 5150064  | 三重県松阪市五反田町                             |
| 995706 | 相澤 貞芳           | 070-78839-03031 | 9290333  | 石川県河北郡津幡町五反田                         |
| 998610 | 雨沢 直海           | 080-17109-26385 | 5203423  | 滋賀県甲賀市甲賀町五反田                         |
| 999137 | 熱尾 由起江         | 090-49681-45761 | 5040037  | 岐阜県各務原市那加五反田町                       |
+--------+---------------------+-----------------+----------+--------------------------------------------------+
298 rows in set (0.00 sec)

異なる結果が返ってきました。
ちなみにLIKE検索を実行すると以下のようになりました。

mysql> select * from list_mecab where name LIKE '%五反田%' OR address LIKE '%五反田%';
+--------+---------------------+-----------------+----------+--------------------------------------------------+
| id     | name                | tel             | postcode | address                                          |
+--------+---------------------+-----------------+----------+--------------------------------------------------+
|    968 | 多加谷 勳           | 090-61450-33774 | 3111306  | 茨城県東茨城郡大洗町五反田                       |
|   2345 | 弦間 八智代         | 090-19766-11406 | 6148071  | 京都府八幡市八幡五反田                           |
|   5730 | 鷹屋 楊慎           | 080-39689-36646 | 5040037  | 岐阜県各務原市那加五反田町                       |
|   8927 | 朱林 和輝子         | 080-47669-90787 | 3111306  | 茨城県東茨城郡大洗町五反田                       |
|   9924 | 尼森 永志           | 080-36222-19644 | 5203423  | 滋賀県甲賀市甲賀町五反田                         |
|  17452 | 穐森 文丈           | 070-54139-99558 | 1410031  | 東京都品川区西五反田                             |
・・  ・
| 995502 | 池水 新七           | 070-35955-46949 | 5150064  | 三重県松阪市五反田町                             |
| 995706 | 相澤 貞芳           | 070-78839-03031 | 9290333  | 石川県河北郡津幡町五反田                         |
| 998610 | 雨沢 直海           | 080-17109-26385 | 5203423  | 滋賀県甲賀市甲賀町五反田                         |
| 999137 | 熱尾 由起江         | 090-49681-45761 | 5040037  | 岐阜県各務原市那加五反田町                       |
+--------+---------------------+-----------------+----------+--------------------------------------------------+
298 rows in set (2.46 sec)

インデックスの作成精度によって検索結果が変わってしまいます。
InnoDBにMeCabをパーザとしてインデックスを貼るよりも、Mroongaを使用した場合の方がインデックスの精度が良い感じを受けます。
次回以降で全文検索を使って色々試してみたいと思います。

このエントリーをはてなブックマークに追加

おさが最近書いた記事

WRITERS POSTS もっと見る

他にもこんな記事が読まれています!

  • WEB
  • マーケティング
  • サーバー・ネットワーク
  • ライフスタイル
  • お知らせ