MySQLで普通の全文検索としては、LIKEをよく使います。
やり方としては、これは一番簡単な方法だ。軽いデータベースだと、これは問題ないですが、
データベースが段々増えると、結構困難な問題になります。それでどうしたらいいでしょうかという話です。
実施環境：
- 環境：CentOS release 6.6 (Final)
- データベース：MySQL 5.7.13
じゃ、始めるよう！
DBに下記のようなテーブルがあります。
CREATE TABLE `article_test` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) DEFAULT NULL COMMENT 'タイトル',
`content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '本文',
PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
まずそのテーブルに300件があります。
mysql> select count(*) from article_test;
+----------+
| count(*) |
+----------+
| 300 |
+----------+
1 row in set (0.00 sec)
今の程度でテキストを検索します。
mysql> select count(*) from article_test where title like '%講談社%' or content like '%講談社%';
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.02 sec)
見た通り、検索時間はあまりかからないです。
でもデータを段々増えます。5万件に上げます。
mysql> select count(*) from article_test;
+----------+
| count(*) |
+----------+
| 50000 |
+----------+
1 row in set (0.25 sec)
その時は、最初の検索はどうでしょうね。再度やってみよう。
mysql> select count(*) from article_test where title like '%講談社%' or content like '%講談社%';
+----------+
| count(*) |
+----------+
| 1170 |
+----------+
1 row in set (5.86 sec)
結構時間がかかったじゃないでしょうか？
何故LIKE '%xxx%'という演算子がそんなに時間がかかりましたか？
実際はLIKEで'%'という開始ワイルドカードを使いますからです。詳細はこちらに参照ください。
運用くMySQLでフルテキストインデックスという機能があります。フルテキストインデックスはMySQL5.5以前MyISAMしかサポートしない。MySQL5.6からInnoDBもサポートします。
日本語で使えるため
- フルテキストインデックスを使えるため、対象文章は単語ごとにデリミタ文字（ ,.）（スペース、コマ、ドット）が必要あり、でも日本語はディフォルトだとそんなデリミタがないです。つまり日本語だとそのまま使えないです。
単語区別ため、NGRAMというファンクションが必要です。
DELIMITER //
CREATE FUNCTION `NGRAM`(`tText` TEXT, `n` INT)
RETURNS text
DETERMINISTIC
BEGIN
DECLARE tResult TEXT;
DECLARE nLength INT;
DECLARE nPosition INT;
DECLARE tPart VARCHAR(16);
IF tText IS NULL THEN
RETURN NULL;
END IF;
SET tResult = '';
SET tText = TRIM(REPLACE(tText, ' ', ''));
SET nLength = CHAR_LENGTH(tText);
SET nPosition = 1;
WHILE nPosition <= nLength DO
SET tPart = TRIM(SUBSTR(tText, nPosition, n));
IF CHAR_LENGTH(tPart) > 0 THEN
SET tResult = CONCAT(tResult, ' ', tPart);
END IF;
SET nPosition = nPosition + 1;会員王六
END WHILE;
RETURN TRIM(tResult);
END//
DELIMITER;
（参考 http://d.hatena.ne.jp/Sikushima/20130610/1370866252 ）。
mysql> select NGRAM('系全国', 2);
+-----------------------+
| NGRAM('系全国', 2) |
+-----------------------+
| 系全 全国 国 |
+-----------------------+
簡単というとNGRAMはスペース区切りという目的です。
NGRAMの計算結果があると、別のコラムが必要です。そのクラムでフルテキストインデックスを使います。
ALTER TABLE article_test ADD COLUMN `bigram` TEXT NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT 'bigram分かち書き';
ALTER TABLE article_test ADD FULLTEXT(bigram);
次はbigramコラムのデータを更新します。
UPDATE article_test SET bigram = CONCAT(NGRAM(title, 2), ' ', NGRAM(SUBSTR(content, 1, 9000), 2));
ここまで、注意点があります。
NGRAMファンクションを使って、データ元のクラム比べるお、データ長さは2~３倍ぐらい増えます。
mysql> select length('系全国分かち書き');
+------------------------------------+
| length('系全国分かち書き') |
+------------------------------------+
| 24 |
+------------------------------------+
mysql> select length(NGRAM('系全国分かち書き', 2));
+----------------------------------------------+
| length(NGRAM('系全国分かち書き', 2)) |
+----------------------------------------------+
| 52 |
+----------------------------------------------+
mysql> select char_length(NGRAM('系全国分かち書き', 2));
+---------------------------------------------------+
| char_length(NGRAM('系全国分かち書き', 2)) |
+---------------------------------------------------+
| 22 |
+---------------------------------------------------+
なので、本文のデータの長さのリミットが必要です。そうしないと、下記エラーが発生可能です。
mysql> UPDATE article_test SET bigram = CONCAT(NGRAM(title, 2), ' ', NGRAM(content, 2));
ERROR 1366 (HY000): Incorrect string value: '\xE3\x81\xA7\xE3\x81\x99' for column 'tResult' at row 1
次は、トリガーを追加します。article_testテーブルのレコードを追加と更新の時に、bigramコラムのデータも更新します。
DELIMITER //
CREATE TRIGGER `trg_article_test_insert` BEFORE INSERT ON `article_test` FOR EACH ROW BEGIN
SET NEW.bigram = CONCAT(NGRAM(NEW.title, 2), ' ', NGRAM(SUBSTR(NEW.content, 1, 9000), 2));
END
//
DELIMITER ;
DELIMITER //
CREATE TRIGGER `trg_article_test_update` BEFORE UPDATE ON `article_test` FOR EACH ROW BEGIN
IF (NEW.title <> OLD.title OR NEW.content <> OLD.content) THEN
SET NEW.bigram = CONCAT(NGRAM(NEW.title, 2), ' ', NGRAM(SUBSTR(content, 1, 9000), 2));
END IF;
END
//
ここまで大分終わりましたが、MySQLのフルテキストインデックスの設定を忘れず再設定が必要です。
mysql> show variables like '%ft%';
+---------------------------------+----------------+
| Variable_name | Value |
+---------------------------------+----------------+
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| innodb_ft_aux_table | |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_enable_diag_print | OFF |
| innodb_ft_enable_stopword | ON |
| innodb_ft_max_token_size | 84 |
| innodb_ft_min_token_size | 3 |
| innodb_ft_num_word_optimize | 2000 |
| innodb_ft_result_cache_limit | 2000000000 |
| innodb_ft_server_stopword_table | |
| innodb_ft_sort_pll_degree | 2 |
| innodb_ft_total_cache_size | 640000000 |
| innodb_ft_user_stopword_table | |
+---------------------------------+----------------+
ディフォルトは：ft_min_word_len=4（MyISAM）,innodb_ft_min_token_size=3(InnoDB）。
検索できるため、全部１に設定します。
# vim /etc/my.cnf
ft_min_word_len=1 #MyISAM
innodb_ft_min_token_size=1 #InnoDB
そして、MySQL再起動したら設定値が有効になります。
]# /etc/init.d/mysqld restart
設定と構築は以上です。
じゃ、結果を試してみましょうか？
mysql> SELECT count(*) FROM article_test WHERE (MATCH (bigram) AGAINST (CONCAT('+', '"', NGRAM('講談社', 2), '"') IN BOOLEAN MODE));
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.03 sec)
速くなりましたね。
参照 (http://qiita.com/ArimaRyunosuke/items/d2b3b94f223cb83c463d)