DockerHubの公式リポジトリにあるMySQLのイメージにはMeCabのパーサーがついておらず、 形態素解析を使った全文検索ができないのでMeCabを組み込んだイメージを作ってみました。
抽出する単語の最小サイズを決めるinnodb_ft_min_token_sizeを1に設定してあります。
使い方
今回はMySQL5.7を使用して、MacのMySQLクライアントから接続して試してみます。gkmr/mysql-mecabのリポジトリを使用するだけでmysqlと変わりなく使用できます。
$ docker pull gkmr/mysql-mecab:5.7
Macで起動しているmysqldとバッティングしないように9999ポートで起動しました（永続化は適宜）
$ docker run -d -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -p 9999:3306 gkmr/mysql-mecab:5.7
mysqlコマンドでMacから接続します。
$ mysql -uroot -h127.0.0.1 --port=9999
データベース作成
mysql> CREATE DATABASE fts;
Query OK, 1 row affected (0.00 sec)
mysql> use fts;
Database changed
テーブル作成
プライマリキーを持ったidカラムと、全文検索対象のcontentというカラムを持ったdocumentsというテーブルを作成します。
この際contentカラムにパーサーとしてmecabを指定します。
mysql> CREATE TABLE documents (id SERIAL PRIMARY KEY, content VARCHAR(255), FULLTEXT(content) WITH PARSER mecab) CHARACTER SET utf8;
Query OK, 0 rows affected (0.08 sec)
データ投入
mysql> INSERT INTO documents (content) VALUES ('すもももももももものうち'), ('おおきなももがどんぶらこ、どんぶらことながれてきました');
Query OK, 1 row affected (0.02 sec)
検索
検索にはMATCH AGAINSTを使います。
mysql> SELECT * FROM documents WHERE MATCH (content) AGAINST ('すもも');
+----+--------------------------------------+
| id | content |
+----+--------------------------------------+
| 1 | すもももももももものうち |
+----+--------------------------------------+
1 row in set (0.01 sec)
続いて、EXPLAINも確認してみます。
mysql> EXPLAIN SELECT * FROM documents WHERE MATCH (content) AGAINST ('すもも');
+----+-------------+-----------+------------+----------+---------------+---------+---------+-------+------+----------+-------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+----------+---------------+---------+---------+-------+------+----------+-------------------------------+
| 1 | SIMPLE | documents | NULL | fulltext | content | content | 0 | const | 1 | 100.00 | Using where; Ft_hints: sorted |
+----+-------------+-----------+------------+----------+---------------+---------+---------+-------+------+----------+-------------------------------+
1 row in set, 1 warning (0.00 sec)
ExtraにFt_hints: sortedというのがついています。
LIKE検索
ちなみに全文検索を使わずLIKE検索をすると
mysql> SELECT * FROM documents WHERE content LIKE '%すもも%';
+----+--------------------------------------+
| id | content |
+----+--------------------------------------+
| 1 | すもももももももものうち |
+----+--------------------------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM documents WHERE content LIKE '%すもも%';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | documents | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
フルテキストインデックスの内容を確認
どのようなインデックスが作られているか確認してみます。
mysql> SET GLOBAL innodb_ft_aux_table = 'fts/documents';
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
+--------------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------------+--------------+-------------+-----------+--------+----------+
| 、 | 3 | 3 | 1 | 3 | 36 |
| うち | 2 | 2 | 1 | 2 | 30 |
| おおきな | 3 | 3 | 1 | 3 | 0 |
| が | 3 | 3 | 1 | 3 | 18 |
| き | 3 | 3 | 1 | 3 | 69 |
| こ | 3 | 3 | 1 | 3 | 33 |
| こと | 3 | 3 | 1 | 3 | 51 |
| すもも | 2 | 2 | 1 | 2 | 0 |
| た | 3 | 3 | 1 | 3 | 78 |
| て | 3 | 3 | 1 | 3 | 66 |
| どん | 3 | 3 | 1 | 3 | 21 |
| どん | 3 | 3 | 1 | 3 | 18 |
| ながれ | 3 | 3 | 1 | 3 | 57 |
| の | 2 | 2 | 1 | 2 | 27 |
| ぶら | 3 | 3 | 1 | 3 | 27 |
| ぶら | 3 | 3 | 1 | 3 | 18 |
| まし | 3 | 3 | 1 | 3 | 72 |
| も | 2 | 2 | 1 | 2 | 9 |
| も | 2 | 2 | 1 | 2 | 9 |
| もも | 2 | 3 | 2 | 2 | 12 |
| もも | 2 | 3 | 2 | 2 | 9 |
| もも | 2 | 3 | 2 | 3 | 12 |
+--------------+--------------+-------------+-----------+--------+----------+
22 rows in set (0.00 sec)
それぞれのカラムの詳細は表 21.25 INNODB_FT_INDEX_CACHE のカラムを参照
形態素解析でのパーサーなので、単語として分割された単位でしか検索できません。これを試してみます。 単語としては登録されていない「おき」を検索してみると
mysql> SELECT * FROM documents WHERE MATCH (content) AGAINST ('おき');
Empty set (0.00 sec)
ちなみに、LIKE検索をしてみるとフルスキャンされるので検索に引っかかります。
mysql> SELECT * FROM documents WHERE content LIKE '%おき%';
+----+-----------------------------------------------------------------------------------+
| id | content |
+----+-----------------------------------------------------------------------------------+
| 2 | おおきなももがどんぶらこ、どんぶらことながれてきました |
+----+-----------------------------------------------------------------------------------+
1 row in set (0.01 sec)