MySQLクエリ最適化に必須の知識ポイントのまとめ

序文

クエリの最適化は一夜にして達成できるものではありません。対応するツールの使い方を学び、他の人の経験から SQL を最適化し、自分自身を改善する必要があります。

まず、インデックスの利点をまとめましょう。高速なデータ取得、安定したクエリ、サーバーが一時テーブルを作成することを回避するための順次ストレージ、ランダム I/O を順序付けられた I/O に変換することなどです。

ただし、インデックスが標準化された方法で作成されていない場合、余分なスペースが占有され、メモリが浪費され、データの追加、削除、変更のパフォーマンスが低下するという問題が発生します。

したがって、効率的なインデックスは、インデックスデータ構造を理解することによってのみ作成できます。

**この記事のすべての操作はMySQL 8.0.12で実行されています**

1. インデックス仕様を作成する

インデックスの最適化を学習する前に、Alibaba 開発マニュアルに記載されているインデックス作成の仕様をある程度理解しておく必要があります。

主キーインデックス: pk_column_column

ユニークインデックス: uk_column_column

共通インデックス: idx_column_column

2. インデックス失敗の理由

インデックスを作成するときは、どのような状況でインデックスが失敗するかを知っておく必要があります。インデックスが失敗する理由を理解することによってのみ、インデックス作成時に既知のエラーを回避することができます。

1. リーダーは死なない

この古典的なステートメントは、インデックスを作成するときに、左端の原則に従う必要があるという事実を説明しています。

たとえば、テーブル構造はu_id，u_name，u_age，u_sex，u_phone，u_timeです。

idx_user_name_age_sexというインデックスを作成します。

クエリ条件には u_name 列を含める必要があります。

2. インデックス列に対して操作を行わない

インデックス列に対して計算、関数、自動または手動の型変換を実行しないでください。そうしないと、テーブル全体のスキャンが実行されます。つまり、インデックス列に対しては操作を行わないでください。

3. 両側のタイプは同じではありません

たとえば、インデックスidx_user_nameが作成され、名前フィールドの型はvarcharです。

クエリを実行するときは、 where name = kakaを使用します。このクエリメソッドは、インデックスの失敗を直接引き起こします。

正しい使用法はwhere name = "kaka"です。

4. 不適切なlikeクエリはインデックスエラーを引き起こす可能性がある

idx_user_nameというインデックスを作成する

実行文はselect * from user where name like "kaka%";となり、インデックスをヒットすることができます。

実行ステートメントはselect name from user where name like "%kaka";インデックスを使用できます (バージョン 8.0 以上のみ)。

実行ステートメントはselect * from user where name like ''%kaka";これによりインデックスが直接失敗します。

5. 範囲条件の後のインデックスは無効になります

idx_user_name_age_sexというインデックスを作成します

ステートメントselect * from user where name = 'kaka' and age > 11 and sex = 1;を実行します。

上記の SQL ステートメントは、名前と年齢のインデックスのみにヒットし、性別のインデックスは無効になります。

複合インデックスが失敗した場合は、key_len の長さを確認するだけで済みます。

概要: % インデックスは後でコマンドされます。カバーリングインデックスを使用すると、どのクエリメソッドでもインデックスにヒットできます。

上記は、インデックスが失敗する理由についての Kaka のまとめです。多くの記事では MySQL のバージョンが明記されていないため、null またはインデックスが失敗する可能性があるという結論が表示されることがあります。

3. SQL最適化のキラー機能を説明する

SQL ステートメントを記述した後、必ず実行する必要があることの 1 つは、Explain を使用して SQL ステートメントをチェックし、インデックスにヒットするかどうかを確認することです。

次の図は、explain を使用した出力形式を示しています。出力形式については、以下で簡単に説明します。

1. id 列はクエリ ID です。クエリステートメントにサブクエリまたは結合クエリがない場合、この ID は常に 1 になります。

サブクエリまたはユニオンクエリがある場合、この番号は増加します。

2.選択タイプ

最も一般的なタイプは SIMPLE と PRIMARY であり、これらの列のみを知っておく必要があります。

3.表

テーブル名として理解するだけです

4. **タイプ

この列は、SQL ステートメントを最適化するときに注意を払うべき最も重要な列の 1 つです。この列には、クエリが使用するタイプが表示されます。

以下は、最良から最悪の順にランク付けされています。

システム: テーブルにはデータが1行しかありません
const: 最大で 1 つのレコードが一致します。主キーまたは一意のインデックス条件付きクエリでよく使用されます。
eq_ref: 接続で使用されるインデックスが主キーであり、一意である場合に表示されます。
ref: 通常のインデックス=または<=>演算子を比較に使用すると、次のようになります。
fulltext: 全文インデックスを使用する
ref_or_null: ref 型に似ていますが、実際にはあまり使用されない null 値チェックが追加されます。ステートメントはwhere name = 'kaka' and name is null，nameです。
index_merge: クエリステートメントは 2 つ以上のインデックスを使用します。これは、and または or を使用する場合によく発生します。公式ドキュメントでは、この型は ref_or_null の後に配置されています。ただし、多くの場合、読み取られるインデックスが多すぎるため、パフォーマンスは range ほど良くない可能性があります。
unique_subquery: where 内のクエリで使用され、サブクエリを完全に置き換えてより効率的になります。ステートメントは、値 IN (SELECT primary_key FROM single_table WHERE some_expr) です。
index_subquery: サブクエリで返されるフィールドの組み合わせはインデックス（またはインデックスの組み合わせ）ですが、主キーまたは一意のインデックスではありません。
range: インデックス範囲クエリ。通常、=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、IN() などの演算子を使用するクエリで使用されます。
インデックス: インデックスのテーブル全体をスキャンし、インデックスを最初から最後までスキャンします
all: 完全なテーブルスキャン、パフォーマンスが最悪。

5.可能なキー

この列には、使用できる可能性のあるインデックスが表示されます。

6. **キー

オプティマイザがpossible_keysからヒットしたインデックス

7.キーの長さ

クエリに使用されるインデックスの長さ (バイト数)。key_len は、where 条件で使用されるインデックスの長さのみを計算します。インデックスがソートやグループ化に使用されている場合でも、key_len では計算されません。

8.参照

定数等値クエリを使用している場合は、ここに const が表示されます。

結合クエリの場合、駆動テーブルの実行プランには、駆動テーブルの関連フィールドが表示されます。

条件で式または関数が使用されている場合、または条件列で内部の暗黙的な変換が発生した場合は、func として表示される場合があります。

9. **行

これは、スキャンする必要がある行数の MySQL の推定値です (正確な値ではありません)。

この値は SQL の効率を直接示します。原則として、行数が少ないほど効率が高くなります。

10.フィルタリング

この列は、ストレージエンジンによって返されたデータがサーバーレベルでフィルター処理された後に、クエリを満たすレコードの数の割合を示します。これはパーセンテージであり、特定のレコード数ではないことに注意してください。

11. **追加

ほとんどの場合、次のような状況が発生します。

インデックスの使用: カバーリングインデックスが使用され、クエリ列はすべてインデックスフィールドになります。
where の使用: where ステートメントの使用
一時テーブルの使用: クエリ結果を並べ替えるときに一時テーブルが使用されます。
ファイルソートの使用: 外部インデックスを使用してデータをソートする
インデックス条件の使用: インデックスプッシュダウンが使用されます。インデックスプッシュダウンの詳細については、Kakaの以前の記事MySQL Indexを参照してください。

12. まとめ

上記は Explain のすべての列の説明です。通常の開発プロセスでは、通常、タイプ、キー、行、および追加の列のみに注意を払います。

型の最適化の目標は少なくとも範囲レベルに到達する必要があり、要件は ref レベルです。consts が可能な場合は、それが最適です。
key はクエリに使用されるインデックスです。この列が空の場合、インデックスが作成されていないか、インデックスが無効です。
rows は、この SQL ステートメントによってスキャンされる行数です。少ないほど良いです。
extra: この列は拡張列です。一時テーブルやファイルの並べ替えが発生する場合は、最適化が必要です。

4. SQL最適化のキラー：遅いクエリ

前述のように、explain を直接使用して、SQL ステートメントが妥当かどうかを分析できます。次に、遅いクエリについて説明します。

スロークエリがオンになっているかどうかを確認する

インデックスを使用しないSQL文がログに記録されているかどうかを確認する

低速クエリを有効にし、インデックスを使用しないSQL文を記録する

グローバル log_queries_not_using_idnexes='on' を設定します。

グローバル log_queries_not_using_indexes='on' を設定します。

上記の2つの設定がオンになっているかどうかを確認します

自分で制御できる低速クエリ時間を設定します。通常は 1 秒で十分です。set set globle long_query_time=1;

時刻が変更されていない場合は、クライアントを再接続するだけです。

低速クエリの保存場所を表示する

次に、インデックスを実行しないステートメントを実行すると、このログにこのステートメントが表示されます。

上の図では、主にQuery_timeとSQL文の内容に注目します。

上記は、スロークエリを使用してプロジェクト内で問題のある SQL ステートメントを表示する方法についてです。

5. 最適化手法

ここでは、よく使用される SQL ステートメントの最適化ソリューションについて説明します。上記の 2 つのツールは、モンスターと戦うためにうまく活用する必要があります。

select *の使用は禁止されています。どのフィールドを照会する必要があるか
フィールドはインデックスを設定します
グループ化フィールドと順序フィールドのインデックスを設定する
オフセットと制限ページングを放棄し、遅延関連付けを使用してページングを実装します（データ量が大きくない場合は必要ありません）
ページングを記述する場合、カウントが0のときは、ページングステートメントの実行を避けるために直接戻ります。
テーブルの戻りを回避するためにカバーインデックスを使用する
複合インデックスを作成する場合、最も識別力の高いインデックスが左端に配置されます。
データの行数をカウントするには、複雑な言葉を使う代わりに count(*) を使用します。
in と exists に関しては、クエリ対象の 2 つのテーブルのサイズが同じであれば、パフォーマンスの違いは無視できます。サブクエリテーブルが大きい場合は existing を使用し、それ以外の場合は in を使用します。
データ行をクエリするときに制限 1 を追加する
適切なデータ型を選択してください。データ型が小さいほど良いです。
ユニオンクエリは最大 3 つのテーブルを結合し、結合するフィールドのデータ型は一貫している必要があります。
in 演算を回避できる場合は回避するようにしてください。回避できない場合は、in 要素の数を 1000 以内に抑えます。
データの更新が頻繁に行われ、差別化が低い列は、インデックス作成には適していません。
explain の型は少なくとも範囲で、ref である必要があります。
結合指数は左端の原理を満たす

VI. 結論

これで、MySQL クエリ最適化の必須知識ポイントに関するこの記事は終了です。MySQL クエリ最適化に関するより関連性の高いコンテンツについては、123WORDPRESS.COM の過去の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

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