☰

MySQL は WHERE IN 句でインデックスを使用しませんか? 質問する

tag-icon • tag-icon sql mysql ruby-on-rails database optimization
MySQL は WHERE IN 句でインデックスを使用しませんか? 質問する

Rails アプリ内のデータベース クエリの一部を最適化しようとしているのですが、行き詰まっているものがいくつかあります。それらはすべてINin theWHERE句を使用しており、適切なインデックスが配置されているように見えても、すべてテーブル全体のスキャンを実行しています。

例えば：

SELECT `user_metrics`.* FROM `user_metrics` WHERE (`user_metrics`.user_id IN (N,N,N,N,N,N,N,N,N,N,N,N))

完全なテーブルスキャンを実行し、EXPLAIN次のように出力します。

select_type: simple
type: all
extra: using where
possible_keys: index_user_metrics_on_user_id  (which is an index on the user_id column)
key: (none)
key_length: (none)
ref: (none)
rows: 208

ステートメントが使用されるときにインデックスは使用されないのでしょうINか、それとも何か別の方法で行う必要があるのでしょうか? ここでのクエリは Rails によって生成されるため、関係の定義方法を再検討できますが、まずは DB レベルでの潜在的な修正から始めようと思いました。

ベストアンサー1

見るMySQL のインデックスの使用方法。

MySQLがまだ実行しているかどうかも検証します完全なテーブルスキャンテーブルに 2000 行程度を追加した後user_metrics。小さなテーブルでは、インデックスによるアクセスは実際にはテーブル スキャンよりもコストがかかり (I/O の観点から)、MySQL のオプティマイザはこれを考慮する場合があります。

前回の投稿とは反対に、MySQLもコストベースのオプティマイザーを使用するANALYZEこれは非常に良いニュースです。つまり、データベース内のデータ量が十分であると思われるときに少なくとも1回は実行すれば、代表将来の日常的な使用について。

コストベースのオプティマイザ（Oracle、Postgresなど）を扱う場合は、定期的に実行する必要があります。ANALYZEさまざまなテーブルのサイズが 10 ～ 15% 以上増加すると、さまざまなテーブルでこの処理が実行されます (Postgres はデフォルトでこれを自動的に実行しますが、他の RDBMS ではこの処理を DBA、つまりユーザーに任せます)。統計分析により、ANALYZEさまざまな実行プランの候補を選択する際に、オプティマイザがどの程度の I/O (およびソートに必要な CPU などのその他の関連リソース) が必要になるかをより正確に把握できるようになります。実行に失敗すると、ANALYZE非常に貧弱で、場合によっては悲惨な計画決定につながる可能性があります (たとえば、数ミリ秒のクエリが、場合によっては数時間かかるなど)。不適切なネストループオンJOINs。

を実行した後もパフォーマンスが不十分な場合はANALYZE、通常、ヒント（ など）を使用して問題を回避できますFORCE INDEXが、他の場合にはMySQLのバグに遭遇した可能性があります（例：年上の方、Rails の を使用すると、問題が発生する可能性がありましたnested_set。

今、RailsアプリなのでActiveRecord、生成されたクエリを引き続き使用するのではなく、ヒントを使用してカスタム クエリを発行するのは面倒です (また、 の目的に反します) ActiveRecord。

Railsアプリケーションで言及したように全て SELECTクエリは Postgres に切り替えた後 100 ミリ秒未満に短縮されましたが、MySQL 5.1 では、インデックスが使用可能であっても、内部テーブル スキャンによるネストされたループが原因で、生成される複雑なActiveRecord結合の一部に 15 秒以上かかることがありました。完璧なオプティマイザーは存在しないため、オプションについて知っておく必要があります。クエリ プランの最適化以外に注意すべき潜在的なパフォーマンスの問題は、ロックです。ただし、これは問題の範囲外です。

おすすめ記事