フルテキストインデックス
とは
テキストベースのカラム (CHAR, VARCHAR, TEXT 等) に作成できるインデックス
CREATE TABLE の一部、あるいは後から ALTER TABLE, CREATE INDEX で追加できる
単語のリスト、及び単語ごとに、単語が出現するドキュメントのリストが格納される
単語ごとの位置情報も、バイトオフセットとして格納される\
5.7.6 からは、全文検索パーサーとして Ngram パーサーが組み込まれるようになり、すぐに使えるようになった 検索
MATCH ... AGAINST ... 構文を利用して検索できる。
code:sql
自然言語検索
検索文字列は、自然言語におけるフレーズとして解釈される
ストップワードリストが適用される
特別な演算子は存在しない
Boolean 全文検索
特別なクエリー言語のルールを使用して、検索文字列が解釈される
チューニング
いくつかユーザがカスタムでチューニング可能な項目がある
デフォルトでうまく動作するようになっているので、何を意味するのかわからなければ設定値は変えない方が良い
ほとんどの設定項目は、サーバの再起動が必要になる
単語の最大長、最小長
InnoDB の場合は innodb_ft_min_token_size, innodb_ft_max_token_size MyISAM の場合は ft_min_token_size, ft_max_token_size N-gram パーサーを利用している場合はこれらは効力を持たないので、 ngram_token_size を利用する
中国語や日本語、韓国語で検索する場合は 1 を設定しておくのが良い、とのこと。
Minimum length of words that are stored in an InnoDB FULLTEXT index. Increasing this value reduces the size of the index, thus speeding up queries, by omitting common words that are unlikely to be significant in a search context, such as the English words “a” and “to”. For content using a CJK (Chinese, Japanese, Korean) character set, specify a value of 1.
自然言語検索の閾値
MyISAM の場合に設定可能。全レコードの 50% 以上に含まれる言葉は無視される。この閾値を変更できる storage/myisam/ftdefs.h に設定を書くらしい
boolean 全文検索演算子の変更
パーサー
ngram, mecab パーサーは、MySQL 5.7.6 からビルドイン。
N-gram
MySQL の全文パーサーは、デフォルトだと delimiter として空白文字を利用する
一方、日本語等 (CJK: Chinese, Japanese, Korean) は、これではダメ
この問題を解決するために、N-gram パーサーが用意されている
Amazon Aurora/RDS と全文検索
5.7 は 2019/1 現在 5.7.12 で、ビルドインでパーサーは入っていない
ngram パーサーのプラグインは入っている
MeCab は入っていないし、プラグインを入れることもできない
code:sql
/* ngram が有効になっていることがわかる */
mysql> show plugins;
+----------------------------+----------+--------------------+-------------+-------------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+-------------+-------------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_SCHEMA_HISTORY | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES_HISTORY | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS_HISTORY | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES_HISTORY | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS_HISTORY | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
| AWSAuthenticationPlugin | ACTIVE | AUTHENTICATION | aws_auth.so | PROPRIETARY |
+----------------------------+----------+--------------------+-------------+-------------+
50 rows in set (0.02 sec)
Ngram Parser を利用する場合は ngram_token_size の設定が必要らしいが、これも 5.7.6 からなので Aurora にはない
code:sql
mysql> select @@ngram_token_size;
+--------------------+
| @@ngram_token_size |
+--------------------+
| 2 |
+--------------------+
1 row in set (0.02 sec)
キャッシュ
実行中を含んだ全文検索クエリ結果のキャッシュ上限
キャッシュ上限を超えてしまうと、ERROR 188 (HY000): FTS query exceeds result cache limit というエラーが出る
区切り文字が短くなるほど、クエリの実行に必要なキャッシュは大きくなる
上限が 4GB
複数人で同時実行する場合、メモリ消費量に注意しなくてはならない
アレヤコレヤ
あとで読む