最近、MYSQL の中文検索の罠に何度も引っかかりましたので、MYSQL の中文検索に関するいくつかの知識をまとめた具体的な記事を書いてみます。
あいまい検索#
MYSQL を使用していると、あいまい検索のニーズが出てくることがあります。例えば、次のようなテーブルがあります。
create table if not exists `user`
(
`id` bigint(20) not null auto_increment,
`name` varchar(255) not null,
`age` int not null,
`update_time` timestamp not null,
`create_time` timestamp not null,
index (`name`),
primary key (`id`)
) engine = InnoDB
charset = 'utf8mb4';
今、name に対してあいまい一致のニーズがあるとします。例えば、name に 草 が含まれているものを一致させたいと考えます。そこで、みんなで考えて、次のような SQL を書きました。
select * from user where name like '%草%'
さて、このコードを嬉々としてオンラインにしたところ、オンラインが壊れました。なぜでしょう？それは MYSQL の罠です。MYSQL の like クエリには次のような 2 つの制限があります。
- プレフィックス一致 ' 草 %' とサフィックス一致 '% 草 ' のみがインデックスを使用し、任意一致は使用しません。
- インデックスを使用できない場合、MYSQL は全表をスキャンしてデータを検索します。
テーブルのデータ規模が非常に大きい場合、全表スキャンは必然的に大きなコストをもたらします。
しかし、実際の作業ではこのような任意一致のニーズが多くあります。では、どうすればよいのでしょうか？全文検索を試してみることができるかもしれません。
全文検索#
簡単に全文検索について話す#
全文検索はもうあまり馴染みがないものではありません。簡単に言うと、テキストデータの中から一組のキーワードを使って一致する項目を探すことです。現在、業界で比較的主流な全文検索のソリューションには次のようなものがあります。
- 全文検索をサポートするリレーショナルデータベース
- Apache Lucene
- Apache Lucene に基づく ElasticSearch
- Apache Solr
後者の 2 つは現在の業界の主要なソリューションであり、多くの全文検索のニーズは ES または Solr を使用して実現されることが考えられます。しかし、この方法にはコストが伴います。以下のような現実的な問題があります。
- ES/Solr はデータ量が多い場合の運用問題、クラスターの HA をどう確保するかはチームの実力を試す問題です。
- MYSQL や他のデータソースからデータをリアルタイム / オフラインで ETL して Search Engine に移行する方法。
- 新たな学習とコードベースのメンテナンスコスト。
- 新たな依存関係を追加した後、システム全体の HA をどう確保するか。
技術的な意思決定では、選択肢の ROI を評価して意思決定を支援する必要があります。比較的簡単な検索シナリオに直面している場合、ES/Solr を選択することによるコストは ROI を相対的に低くします。したがって、いくつかの簡単なシナリオでは、データベース自体の能力を利用してニーズを満たすことを望むかもしれません。
幸いなことに、MySQL 5.5 以降、一定の全文検索機能がサポートされています。
MySQL の全文検索#
MYSQL の全文検索の前提は、テーブルに Full Text Index を作成する必要があります。
alter table `user`
ADD FULLTEXT INDEX name_index (`name`);
全文インデックスは、CHAR/VARCHAR/TEXT 型のフィールドにのみ有効です。
次に、2 つのデータを挿入します。
insert into `user` (name, age, createTime, updateTime)
values ('Jeff.S.Wang', 18, current_timestamp, current_timestamp);
insert into `user` (name, age, createTime, updateTime)
values ('Jeff.Li', 18, current_timestamp, current_timestamp);
さて、MYSQL がどのように全文検索を行うか見てみましょう。
まず、公式の定義によれば、
MATCH (col1,col2,...) AGAINST (expr [search_modifier])
search_modifier は選択した一致モードで、MYSQL には 4 種類あります。
- IN NATURAL LANGUAGE MODE 自然言語モード
- IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION 自然言語拡張モード
- IN BOOLEAN MODE 論理モード
- WITH QUERY EXPANSION 拡張モード
私たちがよく使うのは 自然言語モード と 論理モード です。
まず 自然言語モード について話しましょう。非常に簡単です。名前の通り、MYSQL は一致させるキーワードを直接計算し、対応する値を返します。ここで公式の説明を引用します。
デフォルトまたは IN NATURAL LANGUAGE MODE 修飾子を使用すると、MATCH () 関数はテキストコレクションに対して文字列の自然言語検索を実行します。コレクションは、FULLTEXT インデックスに含まれる 1 つ以上の列のセットです。検索文字列は AGAINST () に引数として与えられます。テーブル内の各行に対して、MATCH () は関連性値を返します。つまり、検索文字列とその行の列にあるテキストとの類似度を測定します。
SQL を書いてみましょう。
select *
from `user`
where MATCH(name) AGAINST('Jeff' IN NATURAL LANGUAGE MODE)
すると、次のような結果が得られます。
| id | name | age | updateTime | createTime |
|---|---|---|---|---|
| 1 | Jeff Li | 18 | 2020-03-01 15:38:07 | 2020-03-01 15:38:07 |
| 2 | Jeff.S.Wang | 18 | 2020-03-01 15:42:28 | 2020-03-01 15:42:28 |
次に、ユーザーの姓を Wang に一致させてみましょう。次のような SQL を書きました。
select *
from `user`
where MATCH(name) AGAINST('Jeff' IN NATURAL LANGUAGE MODE)
次のような結果が得られます。
| id | name | age | updateTime | createTime |
|---|---|---|---|---|
| 2 | Jeff.S.Wang | 18 | 2020-03-01 15:42:28 | 2020-03-01 15:42:28 |
次に、姓が Li のユーザーを検索してみましょう。次のような SQL を書きました。
select *
from `user`
where MATCH(name) AGAINST('Li' IN NATURAL LANGUAGE MODE)
しかし、何の結果も得られませんでした？？？？WTF？なぜ？
理由は分詞の粒度にあります。新しいデータを入力するとき、MySQL はインデックスフィールドのデータを一定の分詞基準長で分詞し、検索のために保存します。これには 4 つのパラメータが分詞の長さを制御します。
- innodb_ft_min_token_size
- innodb_ft_max_token_size
- ft_min_word_len MyISAM エンジンに対して同様の効果があります。
- ft_max_word_len
InnoDB の場合、デフォルトの innodb_ft_min_token_size の値は 3 です。言い換えれば、以前に入力したデータでは、保存された分詞後の単位は次のようになります。
- Jeff
- Wang
したがって、2 回目の検索では結果が得られませんでした。MySQL のパラメータを変更した後、再度実行してみましょう。
select *
from `user`
where MATCH(name) AGAINST('Li' IN NATURAL LANGUAGE MODE)
それでもまだダメです？？？？
公式ドキュメントを確認したところ、次のような記述がありました。
一部の変数の変更には、テーブル内の FULLTEXT インデックスを再構築する必要があります。このセクションの後半に手順が記載されています。
インデックスの分詞粒度も含まれているため、インデックスを削除 / 再構築する必要があります。再度実行します（ちょっと面倒ですが…）。
select *
from `user`
where MATCH(name) AGAINST('Li' IN NATURAL LANGUAGE MODE)
さて、正常に結果が返されました。
| id | name | age | updateTime | createTime |
|---|---|---|---|---|
| 1 | Jeff Li | 18 | 2020-03-01 15:38:07 | 2020-03-01 15:38:07 |
次に、別の一致モードである BOOLEAN MODE について話しましょう。
論理モードでは、いくつかの演算子を使用してデータを検索できます。いくつかの一般的な例を挙げますので、残りは MYSQL の公式ドキュメントを確認してください。
- AGAINST ('Jeff Li' IN BOOLEAN MODE) は、Jeff または Li が存在することを意味します。
- AGAINST ('+Jeff' IN BOOLEAN MODE) は、Jeff が必ず存在することを意味します。
- AGAINST ('+Jeff -Li' IN BOOLEAN MODE) は、Jeff が存在し、Li は存在しないことを意味します。
これらの SQL を実行してみましょう。
select *
from `user`
where MATCH(name) AGAINST('Jeff Li' IN BOOLEAN MODE)
結果は次の通りです。
| id | name | age | updateTime | createTime |
|---|---|---|---|---|
| 1 | Jeff Li | 18 | 2020-03-01 15:38:07 | 2020-03-01 15:38:07 |
| 2 | Jeff.S.Wang | 18 | 2020-03-01 15:42:28 | 2020-03-01 15:42:28 |
select *
from `user`
where MATCH(name) AGAINST('+Jeff' IN BOOLEAN MODE)
結果は次の通りです。
| id | name | age | updateTime | createTime |
|---|---|---|---|---|
| 1 | Jeff Li | 18 | 2020-03-01 15:38:07 | 2020-03-01 15:38:07 |
| 2 | Jeff.S.Wang | 18 | 2020-03-01 15:42:28 | 2020-03-01 15:42:28 |
select *
from `user`
where MATCH(name) AGAINST('+Jeff -Li' IN BOOLEAN MODE)
結果は次の通りです。
| id | name | age | updateTime | createTime |
|---|---|---|---|---|
| 2 | Jeff.S.Wang | 18 | 2020-03-01 15:42:28 | 2020-03-01 15:42:28 |
さて、今度は中国語の検索ニーズがありますので、まずデータを挿入します。
insert into `user` (name, age, createTime, updateTime)
values ('奥特曼', 18, current_timestamp, current_timestamp);
今、姓が 奥 のユーザーを検索します。以前のガイドに従って、次のような SQL を書きました。
select *
from `user`
where MATCH(name) AGAINST('+奥' IN BOOLEAN MODE)
すると、また結果が得られませんでした？？？なぜ？？？
実は、以前に述べた問題、分詞 に関係しています。MySQL のデフォルトの分詞エンジンは英語の分詞のみをサポートし、中国語の分詞はサポートしていません。したがって、分詞がなければ検索もできません。どうすればよいのでしょうか？
MySQL 5.7 以降、MySQL は ngram コンポーネントを提供して中国語の分詞を支援します。使用は非常に簡単です。
alter table `user`
add fulltext index name_index (`name`) with parser ngram;
ここで注意すべき点がいくつかあります。
- ngram は中国語だけでなく、公式ドキュメントによれば、韓国語や日本語もサポートされています。
- 1 つのフィールドには 1 つの全文インデックスしか作成できないため、既存の全文インデックスを削除する必要があります。
また、デフォルトの分詞と同様に、ngram も分詞粒度の制限を受けますが、ngram の設定パラメータは次のとおりです。
- ngram_token_size
必要に応じて設定します。
まとめ#
全文検索は日常の開発において非常に一般的なニーズです。私たちのインフラが外部コンポーネントを安心して使用できない場合、データベースが提供する能力を利用することは良い選択かもしれません。しかし、まだ多くの罠があり、多くのパラメータを最適化する必要があります。。ちなみに、阿里云の RDS 設定は本当に使いにくいです（小声で愚痴）。
さて。。私の先延ばし癖は本当に治らないですね。。しかも、ここ数日歯が痛くて本当に困っています、うううううう。