LoginSignup
21
14

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@vuvanly(ブバン リー)

MySQL日本語と全文検索

Last updated at Posted at 2016-08-12

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)

21
14
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
21
14

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?