MySQL で遅いクエリ SQL を見つけて最適化する詳細な例

1. 遅いクエリSQLを見つけて最適化する方法

a. スローログに基づいてスロークエリSQLを見つける

スローログ関連情報を照会するにはSHOW VARIABLES LIKE '%query%'

slow_query_log はデフォルトではオフになっています。使用する場合、オンにする必要があります。

slow_query_log_fileはスローログファイルを記録します

long_query_time のデフォルト値は 10 秒です。実行された SQL 文がこの時間に達するたびに記録されます。

遅いクエリのステータスを表示するにはSHOW STATUS LIKE '%slow_queries%'

Slow_queries は、遅いクエリの数を記録します。SQL ステートメントの実行が遅い場合、この値は 1 になります (このセッションでの遅い SQL ステートメントの数を記録します)。

知らせ：

スロークエリをオンにする方法: SET GLOBAL slow_query_log = ON;

デフォルトの時間を 1 秒に変更します: SET GLOBAL long_query_time = 1;

(設定後はデータベースに再接続する必要があります。追記: ここでのみ変更した場合、データベース サービスを再起動すると、すべての設定が自動的にデフォルト値に戻ります。永続的な変更を行うには、my.ini で変更する必要があります)

b. Explainなどのツールを使用してSQLを分析する

実行する SQL の前に explain を追加します。例: EXPLAIN SELECT menu_name FROM t_sys_menu ORDER BY menu_id DESC;

次に、説明の重要なフィールドを見てみましょう

タイプ：

type の値が最後の 2 つのうちのいずれかであることが判明した場合、証明ステートメントを最適化する必要があります。

余分な：

c. SQLを変更するか、SQLがインデックスを通過するようにする

MySQL クエリ オプティマイザは、特定の状況に基づいて、どのインデックスを使用するかを決定します。必ずしも主キーを使用するわけではありません (説明内のキーは、どのキーが使用されているかを示します)。特定の状況は、特定の状況によって異なります。特定のキーを強制的に使用する場合:

主キーを強制するには、クエリの最後に force index(primary) を追加します。

2. ジョイントインデックスの最左一致原理の原因

左端一致原理とは何かを簡単に説明してください

名前が示すように、左端が最初で、左端から始まる連続したインデックスはすべて一致できます。同時に、範囲クエリ (>、<、between、like) に遭遇すると、マッチングは停止します。

たとえば、b = 2 です。(a, b) の順序でインデックスを作成すると、(a, b) インデックスと一致しません。ただし、クエリ条件が a = 1 かつ b = 2 または a = 1 (または b = 2 かつ b = 1) の場合は、オプティマイザが a と b の順序を自動的に調整するため、正常に機能します。たとえば、a = 1、b = 2、c > 3、d = 4 で、(a、b、c、d) の順序でインデックスが作成された場合、c フィールドは範囲クエリであり、それ以降のフィールドは一致しなくなるため、d はインデックス化されません。

左端一致原則の原則

一番左のマッチング原理は結合インデックス用なので、結合インデックスの原理を理解する必要があります。結合インデックスを理解すると、なぜ左端一致原則が存在するのかがわかります。

インデックスの基になる層は B+ ツリーであることは誰もが知っているので、ジョイント インデックスももちろん B+ ツリーのままですが、ジョイント インデックスのキー値の数は 1 つではなく複数です。 B+ ツリーは 1 つの値に基づいてのみ構築できるため、データベースは結合インデックスの左端のフィールドに基づいて B+ ツリーを構築します。

例: 結合インデックス (a, b) を作成すると、そのインデックス ツリーは次のようになります。

a の値は 1、1、2、2、3、3 の順序になっているのに対し、b の値は 1、2、1、4、1、2 の順序になっていないことがわかります。したがって、クエリ条件 b = 2 では、結合インデックスが最初に a でソートされ、b は順序付けられていないため、インデックスを使用できません。

同時に、a の値が等しい場合、b の値が順番に並べられていることもわかりますが、この順序は相対的です。したがって、範囲クエリに遭遇すると、左端の一致原則は停止し、残りのフィールドはインデックスを使用できません。たとえば、a = 1 かつ b = 2 の場合、a の値が決定されると b は相対的に順序付けられるため、a フィールドと b フィールドの両方でインデックスを使用できます。a>1 かつ b=2 の場合、a フィールドはインデックスに一致できますが、b の値は一致できません。これは、a の値が範囲であり、b がこの範囲内で順序付けられていないためです。

原因：

(col3, col2)のような結合インデックスを検索すると、下方向に検索するB+ツリー構造でもあることがわかります。col2を直接検索すると、34と77を直接見つけることはできません。この共同インデックスはもう必要ありません。

3. インデックスをさらに作成したほうがよいでしょうか?

1. データ量が少ないテーブルでは、インデックスを作成すると追加のインデックス オーバーヘッドが増加するため、インデックスを作成する必要はありません。

2. データの変更にはインデックスのメンテナンスが必要なので、インデックスが増えるとメンテナンスコストも増加します。

3. インデックスが増えると、必要なスペースも増えます。

要約する

これで、MySQL のスロー クエリ SQL の検出と最適化に関するこの記事は終了です。MySQL のスロー クエリ SQL の検出と最適化に関する関連コンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。