2015年1月19日月曜日

MySQL 5.7 InnoDBで日本語全文検索できるようにした

MySQL 5.7.6 からMecab によるトークナイズが標準搭載されました!
https://dev.mysql.com/doc/refman/5.7/en/fulltext-search-mecab.html



MySQL 5.7 でInnoDB FULLTEXT SEARCH のパーサープラグインを独自に作れるようになってます(従来はMyISAMのみ)。

23.2.4.4 Writing Full-Text Parser Plugins

MySQL 5.7 supports full-text parser plugins with MyISAM. Full-text parser plugins are supported with InnoDB as of MySQL 5.7.3. For introductory information about full-text parser plugins, see Section 23.2.3.2, “Full-Text Parser Plugins”.



さっそく、MyISAM用の全文検索パーサプラグイン mysqlftppc-bigram をInnoDBにも対応してみた。
https://github.com/mitans02/mysqlftppc-bigram

インストール方法

$ git clone https://github.com/mitans02/mysqlftppc-bigram
$ cd mysqlftppc-bigram
$ aclocal && libtoolize --automake && automake --add-missing && automake && autoconf
$ ./configure --with-mysql-config=`which mysql_config` --with-icu-config=`which icu-config`
$ make
$ sudo make install
$ mysql -uroot -p -e "install plugin bigram soname 'libftbigram.so';"

使い方

 

PARSER句でbigramを指定します。InnoDBなので、データ破損が許容できないケースでも使えて安心ですね!パフォーマンスはどうだろ。。。
mysql> CREATE TABLE bi (a TEXT, FULLTEXT(a) WITH PARSER bigram) CHARSET utf8 COLLATE utf8_general_ci ENGINE=InnoDB;
Query OK, 0 rows affected (0.25 sec)

mysql> SET NAMES utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO bi VALUES ("テスト"), ("こんにちは"), ("こんばんわ"), ("おはよう");
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM bi WHERE MATCH(a) AGAINST('こん' IN BOOLEAN MODE);
+-----------------+
| a               |
+-----------------+
| こんにちは      |
| こんばんわ      |
+-----------------+
2 rows in set (0.01 sec)

2015年1月12日月曜日

InnoDBのFULLTEXT SEARCHに変更してハマった


MyISAMとInnoDBでFULLTEXT SEARCHは完全に同じだと思い込んでたのでハマった。
MyISAMのNATURAL LANGUAGEモードの検索では半分以上の行に含まれるワードは検索対象にならない。InnoDBではその仕様はなくなってる。NATURAL LANGUAGEモードを使うケースほとんどないから困らないけど、テストケースが通らなくてハマった。

mysql> CREATE TABLE myisam (a TEXT, FULLTEXT(a)) CHARSET latin1 ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE innodb (a TEXT, FULLTEXT(a)) CHARSET latin1 ENGINE=InnoDB;
Query OK, 0 rows affected (0.24 sec)

mysql> INSERT INTO myisam VALUES('ab cd DE'), ('aa bb DE'), ('cc dd DE');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> INSERT INTO innodb VALUES('ab cd DE'), ('aa bb DE'), ('cc dd DE');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM myisam WHERE MATCH(a) AGAINST('DE' IN NATURAL LANGUAGE MODE);
Empty set (0.00 sec)

mysql> SELECT * FROM innodb WHERE MATCH(a) AGAINST('DE' IN NATURAL LANGUAGE MODE);
+----------+
| a        |
+----------+
| ab cd DE |
| aa bb DE |
| cc dd DE |
+----------+
3 rows in set (0.00 sec)


「50%ルールはMyISAMだけ」とマニュアルにもちゃんと書いてあった。
The 50% threshold can surprise you when you first try full-text searching to see how it works, and makes InnoDB tables more suited to experimentation with full-text searches. If you create a MyISAM table and insert only one or two rows of text into it, every word in the text occurs in at least 50% of the rows. As a result, no search returns any results until the table contains more rows. Users who need to bypass the 50% limitation can build search indexes on InnoDB tables, or use the boolean search mode explained in Section 12.9.2, “Boolean Full-Text Searches”.