MySQLが間違ったインデックスを選択する理由と解決策

MySQL では、テーブルに複数のインデックスを指定できますが、ステートメントの実行時に、使用するインデックスは MySQL の実行プログラムによって決定されます。では、実行者がインデックスを選択するための原則は何でしょうか。また、間違ったインデックスが選択されてしまうのでしょうか。

例を見てみましょう:

テーブル Y を作成し、 2 つの共通インデックスを設定し、データを挿入するためのストアド プロシージャを作成します。

MySQL: 5.7.27、分離レベル: RR

テーブル `Y` を作成します (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `a` int(11) デフォルト NULL,
 `b` int(11) デフォルト NULL,
 主キー (`id`)、
 キー `a` (`a`),
 キー `b` (`b`)
)ENGINE=InnoDB;

デリミタ;;
プロシージャ idata() を作成する
始める
 iをintとして宣言します。
 i=1 に設定します。
 i<=100000の間、
   Y (`a`,`b`) に値 (i, i) を挿入します。
  i=i+1 と設定します。
 終了しながら;
終わり;;
区切り文字 ;
idata() を呼び出す。

次の取引を表示します。

セッション B のみで select * from Y where a between 10000 and 20000; を実行すると、インデックス a が間違いなく選択されます。

ただし、セッション A とセッション B を順番にインストールすると、インデックスの選択は次のようになります。

セッション B のシナリオでは、エグゼキュータは a が配置されているインデックスを選択せず​​、主キー インデックスに基づいて完全なテーブル スキャンを選択したことがわかります。

long_query_time を 0 に設定します。
-- スロー クエリ ログを開き、しきい値を 0 に設定します。記録されたログでは、MySQL が a が配置されているインデックスを選択せず​​、時間がかかったことがわかります。

この観点から見ると、MySQL オプティマイザは常に適切なインデックスを選択できるとは限りません。この現象の理由を理解するには、オプティマイザーの選択ロジックから始める必要があります。

オプティマイザ

MySQL のオプティマイザの目的は、ステートメントを最低コストで実行するための最適な実行プランを見つけることです。

オプティマイザーは、インデックスを選択する際に主に次の要素を考慮します。

• スキャンされる行数: スキャンされる行数が少ないほど、ディスク データへのアクセス回数が少なくなり、CPU リソースの消費量も少なくなります。
• 一時テーブルは関係していますか?
• ソート

走査線の数を決定する

インデックスのカーディナリティを計算する

ステートメントを実行する前に、MySQL はスキャンされた行数を正確に計算することはできませんが、数学的統計を通じてレコード数を推定します。この統計はインデックスの「識別」と呼ばれ、インデックスに異なる値が多いほど、識別が高くなります。インデックス内の異なる値の数はカーディナリティと呼ばれます。カーディナリティが大きいほど、インデックスの識別性は向上します。

ここでのカーディナリティはインデックスのカーディナリティですが、カーディナリティは完全に正確ではありません。 MySQL は実際にはサンプリング統計を使用してカーディナリティを取得します。

計算中、N 個のデータ ページが選択され、これらのページ上の異なる値がカウントされて平均値が取得され、その平均値にインデックスのページ数が掛けられ、その結果がインデックスのカーディナリティになります。

MySQL では、インデックスを保存する方法が 2 つあり、innodb_stats_persistent を設定することで切り替えることができます。

• オンの場合: 統計が永続的に保存されることを意味します。デフォルトの N は 20、M は 10 です。
• オフの場合、統計はメモリにのみ保存されます。デフォルトの N は 8、M は 16 です。
  

テーブル内のデータは常に変化しているため、更新された値が 1/M を超えると、インデックス統計が自動的にトリガーされます。

ただし、これはサンプリング統計であるため、基数の値は正確ではないことに注意してください。

走査線数の推定エラー

先ほど見たように、 Select * from Y where a between 10000 and 20000行数の推定値は 100015 であり、これは完全なテーブル スキャンが実行されるため当然の結果です。

次に、 select * from Y force index(a) where a between 10000 and 20000行数の推定値は 37116 となり、これは理解できません。理想的には、10001 行になるはずです (20001 までトラバースする必要があります)。

さらに奇妙なのは、推定行数 37116 は妥当ではないものの、フル テーブル スキャンの 100015 よりはるかに少ないことです。なぜオプティマイザーはフル テーブル スキャンを選択するのでしょうか。

まず 2 番目の質問を見てみましょう。100015 を選択する理由は、インデックス a を使用する場合、インデックス a をスキャンするだけでなく、テーブルに戻る必要があるためです。オプティマイザーは、主キー インデックスのクエリ コストも考慮する必要があるため、完全なテーブル スキャンが選択されます。

ここで、最初の質問、つまり正しい行数が得られない理由をもう一度考えてみましょう。これは一貫性ビューに関連しています。まず、セッション A では一貫性ビューが有効になっていますが、送信されていません。後続のセッションで Y テーブルがクリアされた後、同じデータが再作成されます。この時点で、各データ行には 2 つのバージョンがあります。古いバージョンは削除前のデータであり、新しいバージョンは削除済みとしてマークされたデータです。したがって、インデックス a には実際にはデータのコピーが 2 つ存在します。この結果、行数の推定値に誤差が生じます。

MySQL は、インデックスやデータ ファイルからレコードを実際に削除するのではなく、レコードを削除対象としてマークすることによってレコードを削除します。また、一貫性のある読み取りを保証するため、削除スペースを削除したり、挿入スペースを追加したりすることはできません。その結果、統計情報が不正確になります。

間違ったインデックスを選択した場合の解決策

行数が正しく見積もられていない場合は、次の方法を使用できます。

EXPLAIN で計算された行数が推定行数と大きく異なる場合は、analyze table を使用してインデックス情報を再計算できます。

オプティマイザーに判断を任せずに、直接使用するインデックスを指定するには、force index を使用します。しかし、武力を使うと、次のような問題も生じる可能性があります。

• データベースを移行する場合、構文はサポートされていません
• 間違ったインデックスを選択することは頻繁には起こらないため、変更は容易ではなく、あまり便利ではありません。コードは、実稼働環境で問題が発生した後にのみ変更する必要がありますが、再テストしてオンラインで展開する必要もあります。

SQL文を最適化し、オプティマイザが正しいインデックスを使用するように誘導する

同様の例を見てみましょう。

まずはこの文を見てみましょう。

SQL select * from Y where a between 1 and 1000 and b between5000 100000 order by b limit 1;

この文を実行するときに、インデックス a またはインデックス b を選択できます。各インデックスは B+ ツリーに対応することがわかります。ここで得られるのは a と b の交差なので、インデックス a を選択した場合は行 1 - 10001 をトラバースする必要があります。インデックス b を選択するには、行 50000 - 100001 を走査する必要があります。理論的には、インデックスとして a が選択される必要がありますが、オプティマイザはインデックスとして b を選択します。

ここでインデックスとして b が選択されるのは、オプティマイザが後続のorder byステートメントを参照するためです。ソートが必要であり、B+ ツリー自体が順序付けされているため、ソート処理は省略され、インデックスとして b が選択されます。

しかし、実際の実行時間から見ると、インデックス a の実行時間の方が短いため、MySQL は再び間違ったインデックスを選択しました。

上記のステートメントのorder by b limit order by b,a limit 1変更できます。この場合、a インデックスと b インデックスの両方をソートする必要があるため、スキャンされた行数がエグゼキュータの主な参照条件となり、正しいインデックスの選択を導きます。

これを実行する前提は、実行の論理結果が一貫していることを保証することです。たとえば、limit 1 を使用すると、 order by b,aとorder by bの結果は一貫しています。ただし、limit 100 を使用すると、そうならない可能性があります。

もう一つの変化がある

select * from (select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 100)alias limit 1;

これで、オプティマイザーが適切なインデックスを選択したことがわかります。その理由は、制限 100 により、オプティマイザはインデックス b を使用するコストの方が高いと判断し、インデックス a を選択するからです。実際、制限 100 はオプティマイザに選択をさせるために使用されます。

インデックスの調整

より適切で優れたインデックスを見つけられますか。あるいは、インデックスの原則を使用して不要なインデックスを削除できますか。

要約する

これで、MySQL がインデックスを選択するときに間違いを犯す可能性があることがわかりました。オプティマイザがインデックスを選択する際の主な原則は、スキャンする行数、一時テーブルの有無、およびソートの 3 つです。スキャンされる行数は主にカーディナリティに関連し、カーディナリティ統計は統計的サンプリングによって決定されるため、推定される行数は不正確になる可能性があります。

スキャンされた行数が正しくない場合は、 analyze tableを使用してテーブル情報を再計算したり、 force indexを使用して指定されたインデックスを強制したり、 sqlのセマンティクスを手動で変更してオプティマイザーが正しい選択を行うように誘導したりすることができます。

上記は、MySQL が間違ったインデックスを選択した理由と詳細な解決策です。MySQL インデックスの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。