MySQL インデックス使用状況監視スキル (収集する価値あり!)

概要

リレーショナルデータベースでは、インデックスは、データベーステーブル内の 1 つ以上の列の値を並べ替える、独立した物理的なストレージ構造です。これは、テーブル内の 1 つ以上の列の値のコレクションと、これらの値を物理的に識別するテーブル内のデータページへの対応する論理ポインターのリストです。

MySQL はハッシュインデックスと Btree インデックスをサポートしています。 InnoDB と MyISAM は Btree インデックスのみをサポートしますが、Memory および Heap ストレージエンジンはハッシュインデックスと Btree インデックスの両方をサポートできます。

1. 現在のインデックスの使用状況を表示する

次のステートメントを使用して、現在のインデックスの使用状況を照会できます。

Handler_read_first は、インデックスヘッダーが読み取られる回数を表します。この値が高い場合、完全なインデックススキャンが多数行われていることを意味します。
Handler_read_key はインデックスが使用された回数を表します。新しいインデックスを追加すると、Handler_read_key が増加したかどうかを確認できます。増加した場合、SQL がインデックスを使用していることを意味します。
Handler_read_next は、インデックスの次の要素の読み取りを表し、通常は範囲スキャンが実行されます。
Handler_read_prev は、インデックスの前の列の読み取りを表します。これは通常、ORDER BY ... DESC で発生します。
Handler_read_rnd は、固定位置での行の読み取りを表します。この値が高い場合、多数の結果セットがソートされ、完全なテーブルスキャンが実行され、関連するクエリで適切なキーが使用されていないことを意味します。
Handler_read_rnd_next は、多くのテーブルスキャンが実行され、クエリのパフォーマンスが低下していることを示します。

実際、多くのアプリケーションシナリオでは、インデックスが動作しているとき、Handler_read_key の値は非常に高くなります。この値は、行がインデックス値を読み取る回数を表します。値が非常に低い場合、インデックスが頻繁に使用されないため、インデックスを追加することで得られるパフォーマンスの向上は大きくないことを示します。

Handler_read_rnd_next の値が高い場合、クエリが非効率的に実行されており、インデックス作成によって修正する必要があることを意味します。この値は、データファイル内の次の行を読み取る要求の数を意味します。大量のテーブルスキャンが実行されている場合、Handler_read_rnd_next の値が高いことは通常、テーブルが正しくインデックス付けされていないか、書き込まれたクエリがインデックスを活用していないことを示します。

2. インデックスが使用されているかどうかを確認する

選択
 オブジェクトタイプ、
 オブジェクトスキーマ、
 オブジェクト名、
 インデックス名、
 カウントスター、
 カウント_読み取り、
 COUNT_FETCH 
から
 PERFORMANCE_SCHEMA.table_io_waits_summary_by_index_usage;

読み取りとフェッチの数が両方とも 0 の場合は、使用されていないことを意味します。

3. 使用されているインデックスを確認する

関連するSQLを説明し、クエリでどのインデックスタイプが使用されているかを確認します。

+-----+-------+-------+-------+--------+--------+--------+-------+
| ALL | インデックス | 範囲 | ref | eq_ref | const | システム | NULL |
+-----+-------+-------+-------+--------+--------+--------+-------+

最高から最低の順に次のとおりです。

システム > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

システムテーブルにはレコードが 1 つだけ存在し、通常はシステムテーブルにのみ表示されます。
const は、クエリが単一のインデックスクエリを通じて見つかることを意味します。通常、対応するインデックス列は primarykey であるか、または一意の where ステートメントで定数が指定されます。一致するデータは 1 行のみであるため、MYSQL はこのクエリを定数に最適化できるため、非常に高速です。
eq_ref 一意のインデックススキャン。このタイプは通常、複数テーブルの結合クエリで使用されます。前のテーブルから接続された対応する列ごとに、現在のテーブルの対応する列には一意のインデックスがあり、それに一致するデータは最大で 1 行のみです。
ref 非一意インデックススキャン。上記と同じですが、現在のテーブルの対応する列には一意のインデックスがなく、一致するデータの行が複数ある可能性があります。このタイプのクエリは通常、複数テーブルの結合クエリ、一意でないキーまたは主キーでないインデックスに対するクエリ、または左端のプレフィックスルールインデックスを使用するクエリで発生します。
範囲インデックスに対する範囲クエリ。インデックスキーワードの範囲の値を照会します。
インデックス全文インデックススキャン。基本的にすべてと同じで、テキスト全体がスキャンされますが、クエリされたフィールドはインデックスに含まれるため、テーブル内のデータを読み取る必要はなく、インデックスツリー内のフィールドのみを読み取る必要があります。
すべて全文スキャン。インデックスは使用されない為、効率は最も低くなります。

ちなみに、最適化のポイントをいくつか挙げます。

1. 挿入ステートメントを最適化します。

1) テスト値()、()、()、() に挿入してみます。
2) 異なる顧客から複数の行を挿入する場合、INSERT 遅延ステートメントを使用すると、速度を上げることができます。遅延とは、INSERT ステートメントがすぐに実行されることを意味します。実際には、データはメモリキューに配置され、実際にはディスクに書き込まれません。これは、各ステートメントを個別に挿入するよりもはるかに高速です。Low_priority は正反対です。挿入は、他のすべてのユーザーがテーブルの読み取りと書き込みを終了した後に実行されます。
3) インデックスファイルとデータファイルを別のディスクに保存する（テーブル作成ステートメントを使用）
4) バッチ挿入を実行している場合は、bulk_insert_buffer_size 変数値を増やして速度を上げることができますが、これは MyISAM テーブルの場合のみです。
5) テキストファイルからテーブルをロードする場合は、データファイルのロードを使用します。これは通常、挿入を使用するよりも20倍高速です。

2. group by ステートメントを最適化します。

デフォルトでは、MySQL はすべての group by フィールドをソートします。これは order by に似ています。クエリに group by が含まれているが、ユーザーが並べ替えられた結果の使用を避けたい場合は、order by null を指定して並べ替えを抑制できます。

3. order by ステートメントを最適化します。

場合によっては、MySQL はインデックスを使用して ORDER BY 句を満たすことができるため、追加のソートが不要になります。 where 条件と order by は同じインデックスを使用し、order by 順序はインデックス順序と同じで、order by フィールドは昇順または降順になります。

4. ネストされたクエリを最適化する:

MySQL 4.1 ではサブクエリのサポートが開始されましたが、場合によっては、特に結合するパッシブテーブルにインデックスがある場合、サブクエリをより効率的な結合に置き換えることができます。その理由は、MySQL では、論理的に 2 つの手順を必要とするこのクエリを完了するために、メモリ内に一時テーブルを作成する必要がないためです。

最後に、ポイント:

テーブルには最大 16 個のインデックスを設定でき、インデックスの最大長は 256 バイトです。インデックス作成の時間オーバーヘッドは O(1) または O(logN) であるため、インデックスは一般に挿入パフォーマンスに大きな影響を与えません (大量の小さなデータを除く)。ただし、インデックスが多すぎるのも良くありません。結局のところ、更新などの操作ではインデックスの維持が必要になります。

要約する

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

以下もご興味があるかもしれません: