大きな数値を処理するための MySQL 'IN' 演算子

Question

私は奇妙な動作を観察しているので、理解しようとしています。

MySQL バージョン: 5.7.33 次のようなクエリがあります:

リーリー

a_table には、time、guid に主キーがあり、guid

にインデックスがあります。 上で書いたクエリは非常に優れたパフォーマンスを持っています。説明プランによれば、インデックス条件を使用;場所を使用;MRR を使用

しています。

in 句の値の数を増やすと、パフォーマンスに大きな影響が生じます。

いくつかの訓練の後、大まかな数字がわかりました。 ~14500 未満の値の場合、解釈スキームは上記と同じです。これを超える数量の場合、説明プラン のみが where を使用し、クエリの実行に永遠に時間がかかります。

言い換えると、たとえば、in 句に 14,000 個の値を入力した場合、Explain Plan には予期される 14,000 行が含まれることになります。ただし、in 句に 15,000 個の値を入れると、説明の行数は 221200324 行になります。テーブル全体にそれほど多くの行はありません。

この動作を理解して、これを修正する方法があるかどうかを知りたいと考えています。

＃＃＃ありがとう＃＃＃

Answer 1

理解してください範囲の最適化のためにメモリを制限する。

IN() の述語に多数の値がある場合、クエリ最適化ステップ中により多くのメモリが使用されます。これは場合によっては問題になると考えられているため、MySQL の最新バージョンでは最大メモリ制限 (デフォルトは 8MB) が設定されています。

オプティマイザが制限を超えるメモリが必要であると判断し、クエリ内に最適化対象となる条件が他にない場合、オプティマイザは最適化を諦め、テーブル スキャンに頼ります。テーブル統計は、実際にはテーブルに約 2 億 2,100 万行あることを示していると推測します (ただし、テーブル統計は不正確な推定です)。

特定の値のリストに必要なメモリ量の正確な計算式を知っているとは言えませんが、観察した動作に基づいて、14,000 個の項目を考慮すると、各項目は実質的に平均約 600 バイトであると推測できます。しかしそれ以上は無効です。

range_optimizer_max_mem_size = 0 を設定すると、メモリ制限を無効にすることができます。これにより、メモリをオーバーコミットするリスクが生じますが、オプティマイザが「諦める」ことは避けられます。以前の仕事ですべての MySQL インスタンスにこの値を設定したのは、クエリ内で膨大な値のリストを作成しないように開発者を教育できなかったためです。