MySQLインデックスの詳細な分析

序文

インデックスの選択はオプティマイザ段階の作業であることはわかっていますが、オプティマイザは万能ではないため、間違ったインデックスを選択して使用してしまう可能性があります。一般に、オプティマイザーはインデックスを選択する際に、スキャンする行数、ソートするかどうか、一時テーブルを使用するかどうかなどの要素を考慮します。

explainを使用してSQLを分析する

explain は優れたセルフテスト コマンドです。explain を頻繁に使用すると、より合理的な SQL ステートメントを記述し、より合理的なインデックスを確立できるようになります。

mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
+----+-------------+--------+-----------+---------+--------+---------------+-------+--------+--------+---------+------------------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+--------+-----------+---------+--------+---------------+-------+--------+--------+---------+------------------------------------+
| 1 | SIMPLE | t | NULL | range | a,b | b | 5 | NULL | 50223 | 1.00 | インデックス条件の使用; where の使用 |
+----+-------------+--------+-----------+---------+--------+---------------+-------+--------+--------+---------+------------------------------------+
セットに 1 行、警告 1 回 (0.01 秒)

で：

テーブル フィールド: どのテーブルに関するものかを示します。
型フィールド: system、const、eq_reg、ref、range、index、all。一般的に言えば、範囲レベル以上に達するはずです。

system、const: 照会された変数は、id=1 などの定数に変換できます。id は主キーまたは一意のキーです。
eq_ref: インデックスにアクセスし、単一行のデータを返します。通常は結合時に表示されます。クエリに使用されるインデックスは、主キーまたは一意キーです。
ref: インデックスにアクセスし、特定の値（複数行の場合もある）を返します。通常は = が使用されている場合に発生します。
範囲: インデックスを使用して、範囲内の行情報を返します。たとえば、>、<、between などを使用します。
インデックス: インデックスの順序でテーブル全体をスキャンします。インデックスがあり、ソートは必要ありませんが、テーブル全体をスキャンする必要があります。
すべて: テーブル全体のスキャン

キー フィールド: 実際に使用されるインデックス。

key_len フィールド: 使用されるインデックスの長さ (精度を失わずに短いほど良い)。

ref フィールド: インデックスのどの列が使用されているかを示します。

行フィールド: MySQL が取得に必要であると見なすデータの行数。

追加フィールド: クエリの追加情報。主に次のものが含まれます。

インデックスの使用: インデックスの使用
where の使用: where 条件が使用される
一時テーブルの使用: 現在のクエリを処理するために一時テーブルを使用する
ファイルソートの使用: 順序フィールドにインデックスがない場合など、追加のソートを使用する
各レコードの範囲がチェックされました(インデックス マップ:N): 使用可能なインデックスがありません
グループ化にインデックスを使用する: テーブル名を使用すると、実際のテーブルをクエリせずに、グループ化に必要なすべてのデータをインデックスで見つけることができます。

一般的に、temporary の使用や filesort の使用に遭遇した場合、インデックスが使用されていないため、それらを最適化する方法を見つける必要があります。

MySQL は取得する行数をどのように計算しますか?

実際には、MySQL によってカウントされるスキャンされた行の数は正確な値ではなく、大きく外れている場合もあります。スキャンされた行の数は、インデックスのカーディナリティに基づいて計算されます。

MySQL では、インデックスのカーディナリティはサンプリング統計によって取得されます。システムはデフォルトで N 個のデータ ページを選択し、データ ページ上のさまざまな値の平均を計算し、それをインデックス ページの数で乗算してカーディナリティを取得します。さらに、変更されたデータ行の数が 1/M を超えると、MySQL はインデックス統計をやり直す操作をトリガーします。

MySQL では、インデックス統計を保存する方法が 2 つあり、innodb_stats_persistent パラメータを設定することで選択できます。

オンに設定すると、統計は永続的に保存されます。このとき、デフォルトの N は 20、M は 10 です。

オフに設定すると、統計はメモリにのみ保存されます。このとき、デフォルトの N は 8、M は 16 です。

一般的に、カーディナリティ統計から得られるデータと実際の行数に大きな違いはありません。ただし、データの削除と追加が頻繁に行われるデータテーブルの場合、データテーブルに 100,000 件のレコードがあるのに、カーディナリティ統計では 200,000 件と表示されることがあります。これは MVCC が原因である可能性があります。MySQL の InnoDB トランザクション サポートでは複数のデータ バージョンを維持する必要があるため、一部のトランザクションがまだ終了しておらず、削除されたデータを長期間使用しているため、削除されたデータ領域を解放できず、新しく追加されたデータによって新しい領域が開かれている可能性があります。この場合、カーディナリティ統計のデータ ページ数が正しくなく、大きなエラーが発生する可能性があります。

これを修正する良い方法は、インデックス情報を再計算するために使用される analyze table table name コマンドを実行することです。

間違ったインデックスを選択した場合はどうすればよいですか?

必要なインデックスを正しく作成すると、ほとんどの場合、オプティマイザは間違ったインデックスを選択しません。インデックスが間違って選択される状況に遭遇した場合はどうすればよいでしょうか。

1. 特定のインデックスの使用を強制するには、force index を使用します。

2. 考え方を変えて、必要なインデックスを使用できるように SQL ステートメントを最適化します。

3. より適切なインデックスを作成するか、誤って使用された不合理なインデックスを削除します。 (場合によっては、このインデックスが実際には冗長で最適ではないため、オプティマイザーがたまたまそれを使用することもあります)。

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。