MySQL日本語と全文検索

MySQLで普通の全文検索としては、LIKEをよく使います。
やり方としては、これは一番簡単な方法だ。軽いデータベースだと、これは問題ないですが、
データベースが段々増えると、結構困難な問題になります。それでどうしたらいいでしょうかという話です。

**実施環境**：
- 環境：CentOS release 6.6 (Final)
- データベース：MySQL 5.7.13

じゃ、始めるよう！
DBに下記のようなテーブルがあります。

```sql 
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件があります。

```sql
mysql> select count(*) from article_test;
+----------+
| count(*) |
+----------+
|      300 |
+----------+
1 row in set (0.00 sec) 
```

今の程度でテキストを検索します。

```sql
mysql> select count(*) from article_test where title like '%講談社%' or content like '%講談社%';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.02 sec) 
```

見た通り、検索時間はあまりかからないです。
でもデータを段々増えます。5万件に上げます。

```sql
mysql> select count(*) from article_test;
+----------+
| count(*) |
+----------+
|    50000 |
+----------+
1 row in set (0.25 sec)
```

その時は、最初の検索はどうでしょうね。再度やってみよう。

```sql 
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`というファンクションが必要です。

```sql
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 ）。

```sql 
mysql> select NGRAM('系全国', 2);
+-----------------------+
| NGRAM('系全国', 2)    |
+-----------------------+
| 系全 全国 国          |
+-----------------------+
```

簡単というとNGRAMはスペース区切りという目的です。
NGRAMの計算結果があると、別のコラムが必要です。そのクラムでフルテキストインデックスを使います。

```sql
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```コラムのデータを更新します。

```sql
UPDATE article_test SET bigram = CONCAT(NGRAM(title, 2), '　', NGRAM(SUBSTR(content, 1, 9000), 2)); 
```

ここまで、注意点があります。
NGRAMファンクションを使って、データ元のクラム比べるお、データ長さは2~３倍ぐらい増えます。

```sql
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 |
+---------------------------------------------------+
```

なので、本文のデータの長さのリミットが必要です。そうしないと、下記エラーが発生可能です。

```sql 
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```コラムのデータも更新します。

```sql 
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のフルテキストインデックスの設定を忘れず再設定が必要です。

```sql 
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）。
検索できるため、全部１に設定します。

```sql
# vim /etc/my.cnf
ft_min_word_len=1 #MyISAM
innodb_ft_min_token_size=1 #InnoDB
```

そして、MySQL再起動したら設定値が有効になります。

```mysql 
]# /etc/init.d/mysqld restart
```

設定と構築は以上です。

じゃ、結果を試してみましょうか？

```sql 
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)