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

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

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

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

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

トランザクションのベスト プラクティスは、できる限り短くすることです。これは、長い取引には、この記事で詳述されているようにいくつかの欠点があるためです。

1. 長時間にわたるロック競合により、クエリの速度が低下し、クエリが失敗する可能性がある
   • InnoDB の行レベルのロックはトランザクションの間保持されます。
   • ロックの待機時間、ロックのタイムアウト、デッドロックが発生する可能性があり、クエリが遅くなったり、失敗したりする
2. undo ログによりサーバーのパフォーマンスが低下する
   • InnoDB のマルチバージョン同時実行制御（MVCC）により、変更された行の古いバージョンは、読み取りとロールバックの整合性を保つため、元に戻すログに保存されます。デフォルトの反復可能な読み取り分離レベルでは、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 以降のバージョンで 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 クエリの分析情報では、EXPLAIN プランに簡単にアクセスできます。

出力で何を探しますか？

• 「rows」 フィールドには、読み取られる行の量が表示されます。
• IO は最も時間のかかる部分です。クエリで大量のデータを読み取る必要がある場合は、処理速度が遅くなる可能性があります。大まかに把握するには、結合したテーブルの「行」を掛けます。上記の例では、858 * 23523 となります。t1 の 858 行ごとに t2 から 23523 行を読み取ることは、最適とは言えません。そのため、反復処理ごとに 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 ステートメントは、実行ステージごとのクエリ実行時間を提供します。これは有益な情報になることもあります。

クエリ実行プランを理解した後は、複数の方法でそれに影響を与えて最適化できます。

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

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

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

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

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