MySQL パフォーマンスをさらに活用する: クエリの調整
データベースは、あらゆるアプリケーションのパフォーマンスにおいて重要な役割を果たします。MySQL データベースも例外ではありません。したがって、データベースのチューニング、設計、構成でアプリケーションのパフォーマンスを向上させるさまざまな方法を理解することが重要です。最適なパフォーマンスを実現するために MySQL を最適化する方法を以下に示します。
関連記事
概要
クエリの調整
データベースのパフォーマンス調整は、アプリケーションから開始されます。アプリケーションがビジネス要件をデータベース クエリに変換する方法は、アプリケーションの複雑さと効率に大きな違いをもたらします。実際のパフォーマンスの測定値は、各データベース インスタンスがビジネスニーズにどの程度貢献しているかを示します。
スキーマの設計
リレーショナル データベースのエンティティと関係を定義する方法によって、データベース クエリに対する応答がどの程度単純になるか、または複雑になるかが決まります。また、主キーとセカンダリ インデックスの定義方法が重要な役割を果たします。
サーバーの設定
サーバー構成は、システム リソースの使用率を最適化し、最大化する役割を担います。システム リソースは、コア処理ユニット（CPU）のコア数、物理マシンまたは仮想マシン（VM）のメモリ、基盤となるストレージ システム、およびネットワークです。
動的なサーバー調整
データベース ワークロードを実際の状況に適応させるための継続的なモニタリング、最適化、パフォーマンス調整。
この記事では、クエリの調整について説明します。残りのトピックについては、以降の記事で説明します。
多くの場合、データベース クエリを確認してクエリの調整プロセスを開始します。まずは、ビジネス要件がデータベース クエリにどの程度効率的に変換されたかを評価することをおすすめします。クエリに対するビジネスニーズの解釈と処理によって、コストがどの程度になるかが決まります。
クエリの設計
最初のステップでは、クエリを最適化して優先順位を付けます。
- 最適な応答時間が必要なデータベースに関する質問を特定する
- 頻繁に実行されるデータベースに関する質問を特定する
この 2 つのカテゴリは、データベース スキーマの設計時に最適化する必要があります。
トランザクションを短くする
トランザクションは、含まれているすべてのステートメントが完全に commit されるか、ロールバックされる論理単位です。トランザクションは、MySQL のアトミック性、整合性、独立性、耐久性（ACID）を提供する機能です。
InnoDB では、MySQL のストレージ エンジンで、すべてのユーザー アクティビティがトランザクション内で発生します。デフォルトでは、autocommit モードが有効になっています。つまり、各 SQL ステートメントは独自の 1 つのトランザクションを形成します。autocommit が有効になっているときに複数ステートメントのトランザクションを実行するには、START_TRANSACTION または BEGIN を指定してトランザクションを明示的に開始し、COMMIT または ROLLBACK で終了します。autocommit モードが無効になっている場合、COMMIT または ROLLBACK によって終了し、新しいトランザクションが開始されるまで、セッションには常にトランザクションがオープンされています。
トランザクションのベスト プラクティスは、トランザクションをできるだけ短くすることです。これは、この記事で詳しく説明するように、長いトランザクションにはいくつかのデメリットがあるためです。
長いトランザクションのデメリット
- 長時間にわたるロック競合により、クエリの速度が低下し、クエリが失敗する可能性がある
- トランザクションの間、InnoDB の行レベルのロックが保持される
- ロックの待機時間、ロックのタイムアウト、デッドロックが発生する可能性があり、クエリが遅くなったり、失敗したりする
- undo ログによりサーバーのパフォーマンスが低下する
- InnoDB のマルチバージョン同時実行制御（MVCC）により、変更された行の古いバージョンは、一貫した読み取りとロールバックのために undo ログに保存されます。デフォルトの反復読み取り分離レベルでは、完了前にトランザクションが開始されるまで、undo ログはパージされません。そのため、長時間実行トランザクションにより、undo ログが蓄積されます。これは、SHOW ENGINE INNODB STATUS コマンドライン関数の履歴リストを使用して監視、モニタリングできます。
- 履歴リストが数百万件を超えると、ロールバック セグメントのミューテックスの競合、undo ログの読み取りボリュームの増加、undo ログのリンク済みリストの走査時間の増加により、サーバーのパフォーマンスに悪影響があります。また、スレッドをパージするための処理も増えます。
- ディスク使用量の増加
- システム テーブルスペースまたは undo テーブルスペースのいずれかでディスクに保存されている undo ログの増加
- シャットダウン時間が長くなる
- 通常のシャットダウン中に、進行中のトランザクションがロールバックされます。通常、ロールバックにかかる時間は、そのポイントに到達するまでにかかった時間よりも長くなります。そのため、サーバーのシャットダウンがロールバックのために時間がかかることがあります。
- クラッシュリカバリ時間が長くなる
- クラッシュリカバリ中に、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_sent_avg: 読み取りクエリ用。理想的な比率は 1 です。この比率が大きいほど、クエリは非効率です。
- rows_examined_avg/rows_affected_avg: 書き込みクエリ用。理想的な比率は 1 です。この比率が大きいほど、クエリは非効率です。
- 一時テーブルを使用してディスク上の一時テーブルに変換する必要があるクエリはどれか。
- tmp_disk_tables/tmp_tables: tmp_table_size/max_heap_table_size で十分かどうかを確認します
- どのクエリが filesort を使用するか。
- rows_sorted/exec_count、sort_merge_passes/exec_count: 大量の並べ替えを伴い、より大きな sort_buffer_size を使用する可能性のあるクエリを特定します
MySQL Workbench を使用する場合、システムビューに基づくパフォーマンス スキーマ レポートが用意されています。レポートの「高コスト 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 - 確認すべきこと
EXPLAIN コマンドはクエリ実行プランを提供します。8.0.18 以降、EXPLAIN ANALYZE コマンドはステートメントを実行し、実行からの EXPLAIN 出力を生成します。
MySQL Query Insights では、EXPLAIN プランに簡単にアクセスできます。
出力される情報
- [rows] フィールドには、読み取られる行の量が表示されます。
- IO は最も時間がかかる要素です。クエリで大量のデータを読み取る必要がある場合は、低速になる可能性があります。 これを大まかに把握するには、結合されたテーブルの間に「行数」を掛けます。上の例では、858 * 23523 です。t1 の 858 行ごとに t2 から 23,523 行を読み取るのは、最適ではありません。これを考慮すると、最適化は、反復処理ごとに t2 からのデータアクセス量を減らすことです。
- 「type」フィールドは、テーブルの結合タイプを記述します。
- 「index」はインデックスがスキャンされることを意味します。インデックスがテーブルから必要なすべてのデータを満たす場合、Extra フィールドに「Uses index」と表示されます。
- 「range」タイプは、インデックスが使用されるだけでなく、データスキャンを制限する範囲条件も提供されることを意味します。
- 後続のテーブルの結合順序は、前のテーブルの行の組み合わせごとに 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 を使用する
セッションの実行を最適化する
特定のクエリのサーバー構成を最適化するには、すべてのセッションに影響するグローバル値を変更するのではなく、セッション レベルの変数を使用することを強くおすすめします。
よく使用されるセッション値は次のとおりです。
Summary
要約すると、クエリの調整について、次の 3 つの側面を説明しました。
- クエリを作成するときに意識的に判断してください。これらの判断は、クエリのパフォーマンス、全体的なサーバー スループット、サーバーのパフォーマンスを決定づけます。
- アプリケーション側とデータベース側の両方でクエリ実行データを追跡します。アプリケーション側のロギングは重要です。ビジネス上の関心に基づいて構成し、事業運営を反映することができます。
- 最後に、クエリ実行プラン、さまざまなステップに関連するコスト、クエリを最適化する方法を理解するのに役立つツールがいくつかあります。
関連プロダクトとサービス
Google Cloud は、オンプレミスのデータセンターの廃止から SaaS アプリケーションの実行、基幹業務システムの移行まで、お客様のビジネスニーズに合わせて構築されたマネージド MySQL データベースを提供します。
