全文検索
N-gramとMeCab
パーサーはN-gramとMeCabがあり、デフォルトではN-gramになっている。
N-gram
N-gramでは、N=1はユニグラム(unigram)、N=2はバイグラム(bigram)、N=3はトライグラム(trigram)と呼ばれる。
例えば「徹底入門」という文字列の場合、バイグラムだと「徹底」「底入」「入門」と2文字ずつ索引付けされる。
ただし、この手法はMeCabに比べると「検索ノイズの発生」「インデックスサイズの肥大化」というデメリットがある。 「検索ノイズの発生」は、例えば「東京都」が「東京」「京都」で索引付けされるため「京都」での検索結果に含まれてしまう。
N-gramの使用方法
インデックス作成時にWITH PARSER ngramを追加するだけ。CREATE TABLE hoge1(
FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
body TEXT,
FULLTEXT INDEX idx_fulltext_body(body) WITH PARSER ngram);
MeCab
MeCabの使用方法
MeCabは辞書からキーワードを抽出するため、N-gramよりインデックスサイズが小さくなる。
ただし、辞書に登録されていない単語は、検索結果に含まれない。
- my.cnfの編集
innodb_ft_min_token_size = 2
loose-mecab-rc-file=$MYSQL_HOME/lib/mecab/etc/mecabrc
※ innodb_ft_min_token_sizeはデフォルトが3だが、MeCab使用時は1または2が推奨されている
※ mecab_rc_fileはMeCabのファイルの指定。
※ loose接頭辞を付けると、認識できない設定の場合でも起動時にエラーにならない。 - mecabrcの編集
dicdir = /path/to/mysql/lib/mecab/lib/mecab/dic/ipadic_euc-jpをdicdir = /usr/local/mysql/lib/mecab/dic/ipadic_utf-8に変更 - MySQLサーバを起動
- MeCabプラグインのインストール
INSTALL PLUGIN mecab SONAME 'libpluginmecab.so';
※SHOW PLUGINS;でMeCabプラグインが有効化になっているかどうか確認可能 - インデックス作成時に
WITH PARSER mecabを追加する
全文検索関連のキーワード
転置インデックス(Inverted Index)
全文検索では、単語の位置情報を格納する「転置インデックス」が作成される。
転置インデックスには「単語」「その単語を含む文書」をリストとして保持する「レコード単位転置インデックス」と、「文書中の中で単語が表れる位置」も保持する「単語単位転置インデックス」がある。
MySQLの全文検索では、単語単位位置インデックスが仕様されている。
インデックスを作成する際に、行と列を「転置」するから「転置インデックス」と呼ぶ。
例えば、「文書1」に「MySQL」「全文」「検索」、「文書2」に「MySQL」「検索」、という単語があるという情報を保持する場合、キーワードを列ではなく行として持つことで、キーワードを含む文書を1行で取得することができる。
| 文書1 | 文書2 | |
|---|---|---|
| MySQL | 1 | 1 |
| 全文 | 1 | 0 |
| 検索 | 1 | 1 |
ストップワード
転置インデックスのサイズ肥大化を防ぐため、英語の場合はa, the, is, was,at, ofなどは対象外にしている。
これらのワードをストップワードと呼ぶ。
トークンサイズ
インデックスのサイズを小さくするために、キーワード(=トークン)を抽出するときに、最小・最大文字数を指定することができる。
日本語の場合、最小文字数は1または2に設定する。
パーサを使用しない全文検索
スペースを区切り文字としてパースするため、日本語では機能しない。
- テーブル作成
CREATE TABLE hoge1(
FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
body TEXT,
FULLTEXT INDEX idx_fulltext_body(body))
FTS_DOC_ID列を含めなかった場合、自動的に非表示のFTS_DOC_ID列が作成されるが、明示的に作成した方がパフォーマンスが良い。
FULLTEXT INDEXを作成すると、内部ではFTS_DOC_ID列にFTS_DOC_ID_INDEXというインデックスが自動生成される。
ただし、テーブル定義には表示されない。
（SHOW CREATE TABLE hoge1では表示されない。SELECT * FROM INFORMATION_SCHEMA.INNODB_INDEXESでは表示される）
※ 2つの列での複合インデックスの場合CREATE TABLE hoge2(
FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title varchar(200), body TEXT,
FULLTEXT INDEX idx_fulltext_title_body(title, body))
FULLTEXTの複合インデックスの場合、検索時に列を完全に一致させる必要がある。
上記の例の場合、MATCH(title, body)でなければならない。 - データ登録
INSERT INTO hoge1(body) VALUES('When I find myself in times of trouble'); - 検索実行
SELECT文では、WHERE句にMATCH()句とAGAINST()句を指定する。SELECT * FROM hoge1 WHERE MATCH(body) AGAINST('+myself -hogehoge' IN BOOLEAN MODE);
ブール検索では以下の演算子がサポートされている。- 「+」 AND
- 「-」 NOT
- 「()」 グループ化。+(aaa bbb)とすると「aaaまたはbbb」が1つのAND条件となる。
- 「*」ワイルドカード
3つのモード
検索には3つのモードがあり、一般的にはブール検索が使われる。デフォルトは自然言語検索。
- 自然言語検索(NATURAL LANGUAGE MODE)
OR検索しかできない。
関連性の高い文書が上位になるようにソートされる。 - ブール検索(BOOLEAN MODE)
任意の演算子を指定できるため、OR, AND, NOT, グルーピング,ワイルドカードなど柔軟な検索が可能。 - クエリー拡張検索
内部で2回検索を実行する特殊な検索。
転置インデックスの確認
転置インデックスの内容を確認したい場合は、SET GLOBAL innodb_ft_aux_table=DB名/テーブル名;の後にSELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;で検索する。
INNODB_FT_INDEX_TABLEにはデータが即時反映されないため、最新の情報を確認する場合はSET GLOBAL innodb_optimize_fulltext_only=ON;の後にOPTIMIZE TABLE テーブル名;を実行する。
SET GLOBAL innodb_optimize_fulltext_only=ON;OPTIMIZE TABLE テーブル名;SET GLOBAL innodb_ft_aux_table='DB名/テーブル名';SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
全文検索の注意事項
メモリ使用量が増加する
- データ更新時、更新内容はメモリ上にキャッシュされ、キャッシュがいっぱいになったらディスクにフラッシュされる。
- テーブル毎のキャッシュサイズはシステム変数innodb_ft_cache_sizeであり、デフォルトは8MBである。
- サーバ全体でのキャッシュサイズはシステム変数innodb_ft_total_cache_sizeであり、デフォルトは640MBである。
データ削除はすぐには反映されない
データ削除時は、IDをDELETEDテーブル(システムテーブル)に記録だけし、インデックスの更新を遅延して実行される。
即座に反映したい場合は、SET GLOBAL innodb_optimize_fulltext_only=ON;の後にOPTIMIZE TABLE テーブル名;を実行する。
以下のシステム変数を変更した場合、ALTER TABLEのDROP INDEXとADD INDEXによるインデックス再作成が必要
- innodb_ft_min_token_size
- innodb_ft_max_token_size
- innodb_ft_server_stop_word_table
- innodb_ft_user_stopword_table
- innodb_ft_enable_stopword
- ngram_tokne_size
パラメータチューニング
パラメータを変更したところで劇的に良くなることはない。
最も重要なパラメータはinnodb_buffer_pool_sizeである。SELECTのときにキャッシュとして使われるだけでなく、InnoDBの全ての操作はバッファプールを使うため、あらゆる速度に影響を及ぼす。
文字コード
Charset
MySQL8.0からデフォルトのCharsetがutf8mb4になった。
utf8(utfmb3)は最大3バイトなので、絵文字は使えない。
何も考えずにutf8mb4にしておけば問題ない。
character_ser_server変数の値が、データベースのCharsetのデフォルトである。
テーブルのCharsetのデフォルトはデータベースのCharsetである。
つまり、character_ser_serverがutfmb4になっていれば、charasetは何も設定しなくてよい。
Collation
文字列を比較やソートするときにCollationが影響する。
Collationの指定は、データベースやテーブルのCharaset指定時にCHARSET xxx COLLATE xxx;とする。
デフォルトはutf8mb4_0900_ai_ciとなっている。
ひらがなとカタカナを区別する場合はutf8mb4_ja_0900_as_cs_ks、区別しない場合はutf8mb4_ja_0900_as_cs、にする。
utf8mb4_ja_0900_as_csにしておけばOKだと思う。
日本語環境の主なCollation
utf8mb4_0900_ai_ci
- 「は」「ば」を区別しない
- 「あ」「ぁ」を区別しない
- 全角/半角を区別しない
- ひらがな/カタカナを区別しない
utf8mb4_0900_as_ci
- 「は」「ば」を区別する
- 「あ」「ぁ」を区別しない
- 全角/半角を区別しない
- ひらがな/カタカナを区別しない
utf8mb4_0900_as_cs
- 「は」「ば」を区別する
- 「あ」「ぁ」を区別する
- 全角/半角を区別する
- ひらがな/カタカナを区別する
utf8mb4_ja_0900_as_cs
- 「は」「ば」を区別する
- 「あ」「ぁ」を区別する
- 全角/半角を区別しない
- ひらがな/カタカナを区別しない
utf8mb4_ja_0900_as_cs_ks
- 「は」「ば」を区別する
- 「あ」「ぁ」を区別する
- 全角/半角を区別しない
- ひらがな/カタカナを区別する
utf8mb4_general_ci
- MySQL5.7でcharasetをutfmb4にした際のデフォルトcollation
- MySQL8.0では使わないと思う
- 「あ」「ぁ」を区別しない
- ひらがな/カタカナを区別する
- 最大3バイトなので絵文字が使えない
utf8mb4_unicode_ci
- MySQL8.0では使わないと思う
- 「は」「ば」を区別しない
- 「あ」「ぁ」を区別しない
- 全角/半角を区別しない
- ひらがな/カタカナを区別しない
Collation名の意味
「Charset名_言語名_nucodeバージョン_ai/as_ci/cs_ks」となっている
- 言語名
指定がないものもある - nucodeバージョン
unicode: 4.0.0
unicode_520: 5.2.0
0900: 9.0.0 - ai/as
アクセントの違いを区別するかどうか。
指定がないものもある。 - ci/cs
大文字小文字の違い、その他の形の違い、を区別するかどうか。 - ks
ひらがなとカタカナの違いを区別するかどうか。
言語名がjsの場合のみ。
コメント