- ◼️ はじめに
- ◼️ 前提条件
- ◼️ データ挿入に関して
- ◼️ 100万レコードでLIKE検索(前後の部分一致)と全文検索の比較
- 全文検索
- ◼️ EXPLAINで実行計画を確認
- ◼️ EXPLAIN ANALYZEを確認
- ◼️ リソース使用状況確認
- ◼️ INNODB_FT_INDEX_TABLEを確認
- ◼️ テストデータを修正
- ◼️ まとめ
- ◼️ 最後に
◼️ はじめに
こんにちは！株式会社iimonでフロントエンジニアをしているあめくです！ 本記事はアドベントカレンダー16日目の記事となります！
最近データベースに興味を持ち、MySQLで全文検索機能があるらしく実際に試してみました。 いろいろな記事を読むと、LIKE文よりも検索速度が速いと書かれており、これは試してみたい！と思い立ちました。
しかし、実際にやってみると思ったほどの実行速度が出ませんでした…。 そこで、なぜ期待通りの速度が出なかったのかを深掘りし自分なりに考察してみました。
今回はその結果を備忘録としてまとめてみます！
◼️ 前提条件
マシン環境
- MacBookPro
- チップ: M3
- メモリ: 36GB
- OS: Sonoma(バージョン14.6.1)
データベースについて
- DB: MySQL 9.0.1
バージョン確認 - 対象レコード数: 100万レコード
レコード数 - カラム数:11項目
※ 不動産サービスらしく物件に関連しそうな項目を設定(ユーザのID(user_id)と店舗のID(store_id)、物件名(name)と住所に関するカラムを設定)
カラム - インデックス
- IDはPrimarykey
- store_idとupdated_atに対して複合インデックス
- nameに対してFULLTEXTインデックス
インデックス(一部抜粋)
- その他設定
ngram_token_size
◼️ データ挿入に関して
まずは検索対象となるテストデータが必要だったため、Djangoでseederを作成し100万レコードのテストデータを挿入しました！
Djangoやseederの詳しい説明は今回の趣旨とは外れるため、割愛させていただきます！
今回の設定では、name カラムに対してFULLTEXTインデックスを作成しました。
また店舗ごとに物件を振り分けるかつ更新日でソートを行うことを想定し、店舗IDと更新日に対して複合インデックスも設定しています。
name のデータには、よく使われそうな名称として、タワービル マンション アパート ハイツ レジデンス を用意し
また、丸の内 銀座 西新宿 みなとみらい 砂子 横山 の値をランダムに組み合わせて設定しています。
作成されたデータに関してですが、store_idを1または2の値を割り振っており、 store_id=1 で 500,915件 store_id=2 で 499,085件のデータが作成されてます。
◼️ 100万レコードでLIKE検索(前後の部分一致)と全文検索の比較
※ 実行するSQLにはキャッシュを利用しないように SQL_NO_CACHE をつけてます。
※ 追記: ドキュメントを確認するとmysql8.0からクエリーキャッシュの機能は削除されてるため、 SQL_NO_CACHE は非推奨とのことです...
LIKE検索
SQL:
mysql> SELECT * FROM property WHERE store_id = 1 AND name LIKE '%みなとみらいアパ%' ORDER BY updated_at DESC LIMIT 100;
速度:
全文検索
SQL:
※ 今回は部分一位を比較したかったため BOOLEAN MODE を使用してます。
mysql> SELECT *
FROM property
WHERE store_id = 1
AND MATCH(name) AGAINST ('みなとみらいアパ' IN BOOLEAN MODE)
ORDER BY updated_at DESC
LIMIT 100;
速度:
...え！？全文検索って速いはずでは！？ 疑念が湧いてきたため、ここから自分なりに調査を始めてみることにしました。
ちなみに、上記SQLで取得できる件数のカウントはどちらも 6,512件となります。
◼️ EXPLAINで実行計画を確認
※ EXPLAINとは MySQL がステートメントを実行する方法に関する情報を提供します とドキュメントに記載されてます。
※ 上記の各SQL文の先頭に EXPLAIN を付けて実行することで、実行計画を確認できます。
LIKE検索のEXPLAIN結果
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | row | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | property | NULL | index | index_store_id_updated_at | PRIMARY | 8 | NULL | 200 | 5.56 | Using where |
全文検索のEXPLAIN結果
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | row | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | property | NULL | fulltext | index_store_id_updated_at,idx_name | idx_name | 0 | const | 1 | 50 | Using where; Ft_hints: no_ranking; Using filesort |
...わからない。
全文検索の方はFULLTEXTインデックスは使用されてそうだが、rowが1...何もわからない...
ここで調べてると EXPLAIN ANALYZE でより詳細な内容が確認できるそうなので試してみました。
別途 EXPLAIN FORMAT=JSON でより詳細なコスト確認ができるので実際のパフォーマンス確認ではこちらも試した方が良いかと思います！
今回はより良い情報を得られなかったため割愛します。
◼️ EXPLAIN ANALYZEを確認
LIKE検索のEXPLAIN ANALYZE結果
-> Limit: 100 row(s) (cost=5.02 rows=11.1) (actual time=0.315..24.5 rows=100 loops=1)
-> Filter: ((property.store_id = 1) and (property.`name` like '%みなとみらいアパ%')) (cost=5.02 rows=11.1) (actual time=0.314..24.5 rows=100 loops=1)
-> Index scan on property using PRIMARY (cost=5.02 rows=200) (actual time=0.159..22.2 rows=15173 loops=1)
LIKE文ではワイルドカード（%）が先頭にあるためインデックスが効かず、多くの行をスキャンしていることがわかりますね。
全文検索のEXPLAIN ANALYZE結果
-> Limit: 100 row(s) (cost=0.855 rows=1) (actual time=48.3..48.4 rows=100 loops=1)
-> Sort row IDs: property.updated_at DESC, limit input to 100 row(s) per chunk (cost=0.855 rows=1) (actual time=48.3..48.4 rows=100 loops=1)
-> Filter: ((property.store_id = 1) and (match property.`name` against ('みなとみらいアパ' in boolean mode))) (cost=0.855 rows=1) (actual time=1.84..47.9 rows=6512 loops=1)
-> Full-text index search on property using idx_name (name='みなとみらいアパ') (cost=0.855 rows=1) (actual time=1.84..47.1 rows=13004 loops=1)
Full-text index検索で 13,004行 を処理し、その後フィルタリングして 6,512行 に絞っていることが確認できますね。
今回はLIKE検索が24.5ms、全文検索が48.4msと、わずかに全文検索の方が遅い結果となりました。
しかし、実際の全文検索の実行時間が約3秒かかっており今回の結果と乖離があるため、他の要因でパフォーマンスが低下している可能性を考えました。
そこで、どのようにリソースの使用状況を確認できるか調べたところ、PROFILEを使用することで詳細なリソース消費状況を確認できることがわかりました！
◼️ リソース使用状況確認
PROFILEを使用するにはprofiling を 1 または ON に設定してプロファイリングを有効する必要があります！
ドキュメント
mysql> SET profiling = 1;
プロファイリングを有効にした後に対象のクエリを実行し下記のクエリを実行して対象のプロファイリングを確認することができます！
mysql> SHOW PROFILES; // プロファイリングを確認 mysql> SHOW PROFILE FOR QUERY 1; // 対象のプロファイリング番号を指定して詳細内容を確認することが可能(今回は1を確認した場合)
全文検索のクエリのプロファイリングを確認
FULLTEXT initialization でほぼほぼ時間を取られていることが確認できました！！
ドキュメント を確認すると 自然言語全文検索を実行する準備 を行なってるみたいですね！
要するに...何やってるかわからない。。。(わかる方いたら教えて😭)
全文検索の準備で何かしら時間がかかってることはわかったが詳細がわからない。
困り果てながら他の方にも何か良い調べ方ないか確認したところ、CTOの森さんがFULLTEXTインデックスでインデックスされた内容って見れるんじゃないかなという助言をいただきました！
早速調べたら確認できそうでした！
◼️ INNODB_FT_INDEX_TABLEを確認
はじめに下記の設定を行う必要があります！
mysql> SET GLOBAL innodb_optimize_fulltext_only=ON; mysql> OPTIMIZE TABLE corporate_nums; mysql> SET GLOBAL innodb_ft_aux_table = 'test/property'; // 'DB名/TBL名` を設定する
下記のクエリで2-gramで分けられたwordとそれらに振られるID・出現頻度など確認できました！
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE LIMIT 5;
ここで今回検索条件に設定した文言をもとに登録されたワード みなとみらいアパ の みな の件数がどれくらいあるかを確認してみたところ、
65,846件あることがわかりました。
SQL:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE where word = 'みな';
実行結果:
ここからは私の考察になりますが、◼️ データ挿入に関して の箇所で name のデータをランダムに設定していると述べましたが、その組み合わせパターンがあまりにも少なく、結果的にデータが偏ってしまっている可能性があります。
この偏りにより、2-gramで生成されたインデックスが過剰に膨れ上がり、検索に時間がかかっているのではないかと考えました。
そこで、name のデータをより偏りの少ない内容に変更し、再度全文検索の実行を試みました！
(個人的には、この転置インデックスを確認できたことにとても興奮しました！！！！！)
◼️ テストデータを修正
今回、テストデータを作成する際に name の値があまりにも偏っていたため、Pythonのダミーデータ生成ライブラリである faker を使用してデータを作成しました。
（fakerの存在は知っていたので、最初からこれを使えばよかったと少し後悔しています…）
件数は最初の前提条件と同じで100万レコード挿入しました！
最初に作成したレコード内容の一部
新たに作成したレコード内容の一部
新たに作成したレコードのnameを確認するといい感じにnameが設定されてますね。 ここで再度全文検索を試したいと思います。
LIKE検索
SQL:
mysql> SELECT * FROM property WHERE store_id = 1 AND name LIKE '%京町パー%' ORDER BY updated_at DESC LIMIT 100;
全文検索
SQL:
mysql> SELECT *
FROM property
WHERE store_id = 1
AND MATCH(name) AGAINST('京町パー' IN BOOLEAN MODE)
ORDER BY updated_at DESC
LIMIT 100;
全文検索はLIKE検索より若干遅いものの、その差はかなり縮まってきました！
今回、LIKE検索では store_id にインデックスが適用されており、取得件数を絞り込むことでフルスキャン対象の件数が減少しているため、全文検索より速い結果になったと考えられます。
また、 新たに作成したレコード内容 の name を使用して様々なケースを試してみたところ、検索するキーワードによってLIKE検索が速い場合もあれば、全文検索が優れている場合もあることが分かりました。
今後はこのあたりをさらに深掘りして調査を進めたいと思います！
◼️ まとめ
MySQLの全文検索が速いと聞いて試してみましたが、今回は自分のテストデータが適切でなかったため無駄に回りくどく調査を進めてしまいました。
それでも調査を通じてMySQLの使い方について多くの知見を得ることができました。
(テストデータに関しては適当なデータを作成するのではなく、本番環境に近いデータを使ってテストすることが非常に重要だと感じました！！)
特に今回試したのは100万件のレコードでしたが、これが更に大規模なデータに対して試すと、異なる結果が得られる可能性が高いと感じています。
実際に全文検索を試してみて、他の方の記事を読んでいるだけでは得られなかった知識や経験を積むことができたことはとてもよかったです！
今回はLIKE検索と全文検索の間に大きな差がなかったものの、次は他にボトルネックとなっている箇所がないかを調査したいと思いました！
また、ストップワードの設定やインデックスの見直しといった全文検索特有の調整を行い、さらに性能を向上させる方法を試してみたいと考えています。
さらに、運用を想定して、後からインデックスを追加した場合のパフォーマンスの変化やインデックス構築のコストについても検証することで、新たな発見が得られそうだと感じました。
(こんな方法で確認できるよとかアドバイスがあればぜひじゃんじゃんコメントください！)
◼️ 最後に
現在弊社ではエンジニアを募集しています！
この記事を読んで少しでも興味を持ってくださった方は、ぜひカジュアル面談でお話ししましょう！
iimon採用サイト / Wantedly / Green
最後まで読んでいただきありがとうございました！
次のアドベントカレンダーの記事は最近とてもブイブイ言わせてるこのお方！！！たくふぉんさんです！
どんなイケイケな記事が飛び出すのか今から楽しみですね！
