# Aurora MySQL データベースのクエリパフォーマンスのトラブルシューティング
MySQL は、クエリプランの評価方法に影響するシステム変数、切り替え可能な最適化、オプティマイザとインデックスのヒント、オプティマイザのコストモデルを通じて[クエリオプティマイザの制御](https://dev.mysql.com/doc/refman/8.0/en/controlling-optimizer.html)を提供します。これらのデータポイントは、さまざまな MySQL 環境を比較するときだけでなく、以前のクエリ実行プランを現在の実行プランと比較したり、任意の時点の MySQL クエリの全体的な実行状況を把握したりするのに役立ちます。
クエリのパフォーマンスは、実行プラン、テーブルスキーマとサイズ、統計、リソース、インデックス、パラメータ設定など、さまざまな要因に左右されます。クエリの調整には、ボトルネックの特定と実行パスの最適化が必要です。
+ クエリの実行プランを見つけ、クエリが適切なインデックスを使用しているかどうかを確認します。`EXPLAIN` を使用し、各プランの詳細を確認することで、クエリを最適化できます。
+ Aurora MySQL バージョン 3 (MySQL 8.0 コミュニティエディションと互換性あり) は `EXPLAIN ANALYZE` ステートメントを使用します。`EXPLAIN ANALYZE` ステートメントは、MySQL がクエリのどこで時間を費やしているのか、またその理由を示すプロファイリングツールです。`EXPLAIN ANALYZE` を使用すると、Aurora MySQL はクエリを計画、準備、実行しつつ、行をカウントし、実行プランのさまざまなポイントで費やされた時間を測定します。クエリが完了すると、`EXPLAIN ANALYZE` は、クエリ結果の代わりにプランとその測定値を印刷します。
+ `ANALYZE` ステートメントを使用してスキーマの統計情報を最新の状態に維持してください。クエリオプティマイザは、統計が古いと不適切な実行プランを選択することがあります。これにより、テーブルとインデックスの両方のカーディナリティの推定が不正確になり、クエリのパフォーマンスが低下する可能性があります。[innodb\_table\_stats](https://dev.mysql.com/doc/refman/8.0/en/innodb-persistent-stats.html#innodb-persistent-stats-tables) テーブルの `last_update` 列には、スキーマ統計が最後に更新された時刻が表示されます。これは「古くなっている」ことを示す良い指標です。
+ データの分布の歪みなど、テーブルのカーディナリティに考慮されていないその他の問題が発生することもあります。詳細については、MySQL ドキュメントの「[InnoDB テーブルの ANALYZE TABLE の複雑度の推定](https://dev.mysql.com/doc/refman/8.0/en/innodb-analyze-table-complexity.html)」と「[MySQL のヒストグラム統計](https://dev.mysql.com/blog-archive/histogram-statistics-in-mysql/)」を参照してください。
## クエリにかかった時間の確認
クエリにかかった時間を決定する方法は次のとおりです。
+ [プロファイリング](https://dev.mysql.com/doc/refman/8.0/en/show-profile.html)
+ [パフォーマンススキーマ](https://dev.mysql.com/doc/refman/8.0/en/performance-schema.html)
+ [クエリオプティマイザ](https://dev.mysql.com/doc/refman/8.0/en/controlling-optimizer.html)
**プロファイリング**
デフォルトでは、プロファイリングは無効です。プロファイリングを有効にし、スロークエリを実行してプロファイルを確認します。
```
SET profiling = 1;
{{Run your query.}}
SHOW PROFILE;
```
1. 最も多くの時間が費やされているステージを特定します。MySQL ドキュメントの「[一般的なスレッド状態](https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html)」によると、`SELECT` ステートメントの行の読み取りと処理は、多くの場合、特定のクエリの存続期間中で最も長い実行状態です。`EXPLAIN` ステートメントを使用すると、MySQL がこのクエリを実行する方法を理解できます。
1. スロークエリログを確認して `rows_examined` と `rows_sent` を評価し、各環境でワークロードが類似していることを確認します。詳細については、「[Aurora MySQL データベースのログ記録](aurora-mysql-troubleshooting-logging.md)」を参照してください。
1. 特定されたクエリの一部であるテーブルに対して、以下のコマンドを実行します。
```
SHOW TABLE STATUS\G;
```
1. 各環境でクエリを実行する前と後に、次の出力をキャプチャします。
```
SHOW GLOBAL STATUS;
```
1. 各環境で以下のコマンドを実行して、このサンプルクエリのパフォーマンスに影響する他のクエリ/セッションがないかどうかを確認します。
```
SHOW FULL PROCESSLIST;
SHOW ENGINE INNODB STATUS\G;
```
サーバー上のリソースがビジー状態になると、クエリを含むサーバー上の他のすべての操作に影響することがあります。また、クエリの実行時に定期的に情報をキャプチャしたり、有用な間隔で情報をキャプチャする `cron` ジョブを設定することもできます。
**Performance Schema**
パフォーマンススキーマは、パフォーマンスへの影響を最小限に抑えながら、サーバーのランタイムパフォーマンスに関する有用な情報を提供します。これは DB インスタンスに関するスキーマ情報を提供する `information_schema` とは異なります。詳細については、「[Aurora MySQL における Performance Insights のPerformance Schema の概要](USER_PerfInsights.EnableMySQL.md)」を参照してください。
**クエリオプティマイザトレース**
特定の[クエリプランが実行対象として選択された](https://dev.mysql.com/doc/refman/8.0/en/execution-plan-information.html)理由を理解するために、MySQL クエリオプティマイザにアクセスするように `optimizer_trace` をセットアップできます。
オプティマイザトレースを実行すると、オプティマイザが使用できるすべてのパスとその選択に関する詳細な情報が表示されます。
```
SET SESSION OPTIMIZER_TRACE="enabled=on";
SET optimizer_trace_offset=-5, optimizer_trace_limit=5;
-- Run your query.
SELECT * FROM table WHERE x = 1 AND y = 'A';
-- After the query completes:
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET SESSION OPTIMIZER_TRACE="enabled=off";
```
## クエリオプティマイザ設定の確認
Aurora MySQL バージョン 3 (MySQL 8.0 コミュニティエディションと互換性あり) には、Aurora MySQL バージョン 2 (MySQL 5.7 コミュニティエディションと互換性あり) と比較して、オプティマイザ関連の多くの変更があります。`optimizer_switch` にカスタム値がある場合は、デフォルトの違いを確認して、ワークロードに最適な `optimizer_switch` 値を設定することをお勧めします。また、Aurora MySQL バージョン 3 で使用できるオプションをテストして、クエリがどのように実行されるかを調べることをお勧めします。
**注記**
Aurora MySQL バージョン 3 では、[innodb\_stats\_persistent\_sample\_pages](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_stats_persistent_sample_pages) パラメータのコミュニティデフォルト値である 20 を使用しています。
`optimizer_switch` 値を表示するには、次のコマンドを使用できます。
```
SELECT @@optimizer_switch\G;
```
次の表は、Aurora MySQL バージョン 2 および 3 のデフォルトの `optimizer_switch` 値を示しています。
| 設定 | Aurora MySQL バージョン 2 | Aurora MySQL バージョン 3 |
| --- | --- | --- |
| batched\_key\_access | 化 | 化 |
| block\_nested\_loop | オン | オン |
| condition\_fanout\_filter | オン | オン |
| derived\_condition\_pushdown | – | オン |
| derived\_merge | オン | オン |
| duplicateweedout | オン | オン |
| engine\_condition\_pushdown | オン | オン |
| firstmatch | オン | オン |
| hash\_join | 化 | オン |
| hash\_join\_cost\_based | オン | – |
| hypergraph\_optimizer | – | 化 |
| index\_condition\_pushdown | オン | オン |
| index\_merge | オン | オン |
| index\_merge\_intersection | オン | オン |
| index\_merge\_sort\_union | オン | オン |
| index\_merge\_union | オン | オン |
| loosescan | オン | オン |
| materialization | オン | オン |
| mrr | オン | オン |
| mrr\_cost\_based | オン | オン |
| prefer\_ordering\_index | オン | オン |
| semijoin | オン | オン |
| skip\_scan | – | オン |
| subquery\_materialization\_cost\_based | オン | オン |
| subquery\_to\_derived | – | 化 |
| use\_index\_extensions | オン | オン |
| use\_invisible\_indexes | – | 化 |
詳細については、MySQL ドキュメントの「[切り替え可能な最適化 (MySQL 5.7)](https://dev.mysql.com/doc/refman/5.7/en/switchable-optimizations.html)」と「[切り替え可能な最適化 (MySQL 8.0)](https://dev.mysql.com/doc/refman/8.0/en/switchable-optimizations.html)」を参照してください。