最近、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 して検索エンジンに移行する方法。
- 新たな学習とコードベースのメンテナンスコスト。
- 新たな依存関係を追加した後、システム全体の 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 に一致させることを試みます。つまり、姓が 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 設定は本当に使いにくいです（小声で愚痴）。
さて。。私の先延ばし癖は本当に治らないですね。。それに、この 2 日間、歯が痛くて本当に困っています、うううううう。