最初のステップでは、クエリを最適化して優先順位を付けます。

1. 最適な応答時間が必要なデータベースに関する質問を特定する
2. 頻繁に実行されるデータベースに関する質問を特定する

これら 2 つのカテゴリは、データベース スキーマの設計時に最適化する必要があります。

トランザクションとは、含まれているすべてのステートメントが完全に commit されるか、ロールバックされる論理単位です。トランザクションは、MySQL にアトミック性、整合性、独立性、耐久性（ACID）を付与する機能です。

MySQL のストレージ エンジンである InnoDB では、すべてのユーザー アクティビティがトランザクション内で発生します。デフォルトでは、自動 commit モードが有効になっています。つまり、各 SQL ステートメントは独自に単一のトランザクションを形成します。自動コミットが有効になっている場合に複数ステートメント トランザクションを実行するには、START_TRANSACTION または START_TRANSACTION でトランザクションを明示的に開始し、START_TRANSACTION または START_TRANSACTION を使用して終了します。自動 commit モードが無効になっている場合は、COMMIT または ROLLBACK によってトランザクションが終了し、新しいトランザクションが開始されるまで、セッションは常に開かれています。

トランザクションに関しては、できるだけ短くすることをおすすめします。これは、この記事で詳しく説明しているように、長時間のトランザクションにはいくつかのデメリットがあるためです。

1. 長時間にわたるロック競合により、クエリの速度が低下し、クエリが失敗する可能性がある

• トランザクションの実行中に InnoDB の行レベルのロックが維持される
• ロックの待機時間、ロックのタイムアウト、デッドロックが発生する可能性があり、クエリが遅くなったり、失敗したりする

2. 大量の undo ログによりサーバーのパフォーマンスが低下する

• InnoDB マルチバージョン同時実行制御（MVCC）により、整合性のある読み取りとロールバックを実現するために、変更された行の古いバージョンが undo ログに保存されます。デフォルトの繰り返し可能な読み取り分離レベルでは、先に開始されたトランザクションが完了するまで、undo ログはパージされません。そのため、実行時間が長いトランザクションには undo ログが蓄積されます。これは、SHOW ENGINE INNODB STATUS コマンドライン関数の履歴リストを介して確認およびモニタリングできます。
• 履歴リストが数百万件を超えると、ロールバック セグメントのミューテックスの競合、undo ログの読み取り量の増加、リンクされた undo ログのリストの走査時間の増加により、サーバーのパフォーマンスに悪影響が及びます。また、パージスレッドの処理も増加します。

3. ディスク使用量の増加

• システム テーブルスペースまたは undo テーブルスペースのいずれかでディスクに保存されている undo ログの増加

4. シャットダウン時間が長くなる

• 通常のシャットダウンでは、進行中のトランザクションがロールバックされます。多くの場合、ロールバック時間は要点を把握するまでに要する時間よりも長くなります。そのため、ロールバック中はサーバーのシャットダウンの所要時間が長くなる可能性があります。

5. クラッシュリカバリ時間が長くなる

• クラッシュ リカバリ中に、InnoDB は最後のチェックポイントのトランザクションを繰り返し、commit されていないトランザクションをアンロールします。トランザクションの実行時間が長いと、対応するステップに要する時間が長くなります。

• SELECT クエリ
• 行ロックを保持しない
• undo ログが蓄積される可能性がある
• 以下のクエリの最適化のセクションをご覧ください
• UPDATE/INSERT/DELETE クエリ
• バッチクエリは多数の単一行変更よりも優れたパフォーマンスを発揮する
• 分割してバッチ実行時間を数秒に制限する

• SELECT クエリの分離を検討する
• データベース クエリの間にアプリケーション ロジックがある場合は、トランザクションを分割することを検討する
• ステートメントごとに保持される行ロックの数を見積もる
• 実行順序を評価して行のロックを最小限に抑える
• トランザクション サイズを削減する機会を探す

クエリは、アプリケーション側またはデータベース側でキャプチャできます。

開発手法として、データベース クエリとクエリの実行時間をログに記録することをおすすめします。アプリケーション側のロギングにより、ビジネス コンテキストにおけるクエリの効果と効率を簡単に評価できます。たとえば、ユーザーはすべてのクエリの応答時間をログに記録したり、特定の機能に関する応答時間をログに記録したりできます。これはマルチステートメント トランザクションの合計実行時間を簡単に取得できる方法でもあります。

また、アプリケーション側のロギングで測定されるクエリの応答時間は、ネットワーク時間を含むエンドツーエンドの測定値です。ログに記録されるデータベースに対するクエリ実行時間を補完し、ネットワークとデータベースのどちらに問題があるのかを簡単に特定できるようにします。

Cloud SQL MySQL Query Insights

Cloud SQL の Query Insights ツールを使用すると、クエリのキャプチャ、モニタリング、診断を行うことができます。

Query Insights では、実行時間と実行頻度に基づいて、上位のクエリを簡単に見つけることができます。

このツールには、期間、データベース、ユーザー アカウント、クライアント アドレスなどのフィルタ オプションがあり、CPU 使用率、IO およびロック待機の内訳を示すグラフを表示できます。「上位のクエリとタグ」の表には、正規化された実行時間の上位のクエリが一覧表示されます。実行時間のほかに、「スキャンされた平均行数」と「返された平均行数」の統計情報が表示されるため、クエリの効率に関する分析情報が得られます。

このツールの全機能と有効にする方法については、こちらのドキュメントをご覧ください。

パフォーマンス スキーマを使用する

Cloud SQL for MySQL では、メモリが 15 GB 以上の MySQL 8.0.26 以降のバージョンで performance_schema 機能がデフォルトで有効になっています。この機能を有効または無効にするには、インスタンスの再起動が必要です。

performance_schema=ON の場合、クエリ ステートメントの計測がデフォルトで有効になります。sys.statement_analysis テーブルは、正規化されたクエリの集計統計情報を示し、次のような疑問の解決に役立ちます。

• テーブル全体をスキャンするクエリはどれか。
• full_scan/exec_count: クエリがテーブル スキャンを頻繁に実行しているかどうか（多くの場合、非効率）を見つけます
• 実行速度が遅いクエリはどれか。
• avg_latency: 平均クエリ実行時間
• どのクエリが非効率的か。
• rows_examined_avg/rows_examined_avg: 読み取りクエリ用であり、理想的な比率は 1 です。この比率が大きいほど、クエリの効率は低下します。
• rows_examined_avg/rows_examined_avg: 書き込みクエリ用であり、理想的な比率は 1 です。この比率が大きいほど、クエリの効率は低下します。
• 一時テーブルを使用してディスク上の一時テーブルに変換する必要があるクエリはどれか。
• tmp_disk_tables/tmp_disk_tables: tmp_disk_tables または tmp_disk_tables の値で十分かどうかを確認します。
• どのクエリが filesort を使用するか。
• rows_sorted/rows_sorted、rows_sorted/rows_sorted: 大量の並べ替えを伴い、より大きな rows_sorted を使用する可能性のあるクエリを特定します

MySQL Workbench を使用している場合、sys ビューに基づくパフォーマンス スキーマ レポートが作成されます。このレポートには、クエリのパフォーマンスに関する分析情報を示す「高コストの SQL ステートメント」のセクションがあります。

スローログとツールを使用する

スローログには、long_query_time より長く実行されているすべてのクエリがキャプチャされます。また、クエリの実行時間、ロック時間、確認されたデータ行、送信されたデータ行もログに記録されます。実行統計情報が追加されているため、一般的なログを使用するよりもデータベース クエリを分析するのに適しています。

スローログを有効にすることをおすすめします。通常、long_query_time は、調査と最適化を行うクエリをキャプチャするために適切なしきい値に保っておく必要があります。

log_output=FILE

slow_query_log=ON

long_query_time=2

時には、long_query_time=0 を設定して、すべてのクエリを短時間キャプチャし、クエリの量とパフォーマンスの概要を取得することをおすすめします。

クエリの統計情報を表示するために、クエリ シグネチャを抽出してレポートを生成する mysqldumpslow や pt-query-digest などのツールがあります。

クエリ統計情報に関するレポートを生成するサードパーティのモニタリング ツールには、Percona Monitoring and Management、SolarWinds のDatabase Performance Monitor（以前の VividCortex）などがあります。

トランザクションでクエリをキャプチャしたら、次にクエリを最適化します。

EXPLAIN コマンドはクエリ実行プランを提供し、8.0.18 以降では EXPLAIN ANALYZE コマンドはステートメントを実行し、実行からタイミングとともに EXPLAIN 出力を生成します。

MySQL Query Insights を使用すると、EXPLAIN プランに簡単にアクセスできます。

出力で何を探しますか？

• 「rows」 フィールドには、読み取られる行の量が表示されます。
• IO は最も時間のかかる部分ですクエリで大量のデータを読み取る必要がある場合は、処理が遅くなる可能性があります。この状態を大まかに把握するには、結合されたテーブル間で「行数」を乗算します。上記の例では、858 * 23,523 になります。t1 の 858 行ごとに t2 の 23,523 行を読み取るのは、最適ではないと思われます。これを踏まえて、最適化では各イテレーションの t2 からのデータアクセスの量を減らすことになります。
• 「type」フィールドは、テーブルの結合タイプを記述します。
• 「index」タイプはインデックスがスキャンされることを表します。テーブルに必要なすべてのデータがインデックスに含まれている場合、[Extra] フィールドには 「Uses index」 と表示されます。
• 「range」タイプは、インデックスが使用されるだけでなく、データスキャンを制限する範囲条件も提供されることを意味します。
• 結合順の後続のテーブルの場合、「eq_ref」タイプは、前のテーブルの行の組み合わせごとに 1 行がこのテーブルから読み込まれることを意味します。これは最も効率的です。
• 「ref」タイプは、インデックス照合が 1:1 ではなく 1:m であることを意味します。前のテーブルの行の組み合わせごとに、このテーブルから複数の行が読み取られます。
• 避けるべきタイプは「ALL」です。これは、前のテーブルの行の組み合わせごとにテーブルのフルスキャンが行われることを表します。
• 「key」フィールドには、実際に使用されているインデックスが示されます。
• 使用するインデックスは、古い可能性があるインデックス カーディナリティに基づいて選択されます。そのため、最もデータを絞り込むことのできるインデックスが使用されていることを確認することが重要です。  
• 「key_len」フィールドは、鍵長をバイト単位で示します。
• 複数列インデックスの場合、key_len はインデックスの使用されている部分を示します。たとえば、インデックスに（col1、col2、col3）があり、クエリ条件が「col1 = n and col2 like ‘%string%’」である場合、インデックスのフィルタリングには col1 のみが使用されます。クエリを「col1 = n and col2 like ‘string%’」に変更できる場合は、（col1、col2）の両方がインデックスのフィルタリングに使用されます。この小さな変更で、クエリのパフォーマンスに大幅な違いが生じる可能性があります。
• 「Extra」フィールドには、クエリプランに関する追加情報が含まれます。
• 「Using temporary」は、内部一時テーブルが作成され、それによってディスク上の一時テーブルが生成される可能性があることを意味します。
• 「Using filesort」とは、並べ替えがインデックスを利用できず、並べ替えバッファと一時ディスク ファイルが必要になることを意味します。
• 「Uses index」は、このテーブルから必要なすべてのデータがインデックスに含まれており、データ行の読み取りは不要であることを意味します。

セッション ステータス変数を使用して、クエリ実行の詳細情報を取得できます。

まず、セッション変数を消去してから、クエリを実行してカウンタを確認します。たとえば、Handler_* ステータスはデータアクセス パターンと行数を示します。Created_* は、一時テーブルまたはディスク上の一時テーブルが作成された場合に表示されます。Sort_* は、並べ替えのマージパスの数と並べ替えられた行数を表示します。その他のセッション変数については、こちらのドキュメントをご覧ください。

SHOW PROFILE ステートメントは、実行ステージごとのクエリ実行時間を提供します。これは有益な情報になることもあります。

クエリ実行プランを理解したら、プランに影響を与えて、最適化する方法は複数あります。

• インデックス定義を追加または更新する
• より良いフィルタリングのためには、データアクセスを削減
• 並べ替えには filesort を使用しない
• オフの場合、インデックスの統計情報を更新する
• ANALYZE TABLE <tbl>;
• その後、EXPLAIN プランの出力を再確認する
• Index Hint を使用する
• filtering、join または order by / group by に使用する特定のインデックスを提案または強制するため
• STRAIGHT_JOIN を使用してテーブルの結合順序を定義する
• Optimizer Hint を使用する

特定のクエリに合わせてサーバー構成を最適化するには、すべてのセッションに影響するグローバル値を変更するのではなく、セッション レベルの変数を使用することを強くおすすめします。

よく使用されるセッション値は次のとおりです。

まとめると、クエリの調整について、次の 3 つの側面について説明しました。

• クエリを作成する際は意識的に決定してください。これらの決定事項が、クエリ パフォーマンス、全般的なサーバー スループット、サーバー パフォーマンスを左右する要因となります。
• アプリケーション側とデータベース側の両方でクエリ実行データを追跡します。アプリケーション側のロギングは重要です。ビジネス上の関心に基づいて構成し、事業運営を反映させることができます。
• 最後に、クエリ実行プラン、さまざまなステップに関連する費用、クエリを最適化する方法を理解するのに役立つツールがいくつかあります。