クエリ調整のガイダンス

ワークロード内で問題のあるクエリを見つけたら、各クエリを調整する必要があります。調整に関する以下のガイドラインを参考にすると、ワークロードをより効率的に実行できます。

スキャンする行数を最小限に抑える

一見基本的なことのようですが、クエリの調整に役立つ重要なアドバイスです。EXPLAIN ステートメントを使用して、row 欄で、各 join でオプティマイザーがスキャンする行数を確認します。最適なインデックスを作成することでスキャンする行数を減らし、クエリを再説明して作業を確認します。詳細については、MySQL ドキュメントを参照してください。

パーティションテーブルを使用する場合は、オプティマイザーがパーティションを 1 つ 1 つスキャンする必要をなくすため、必ず、パーティションの削除を有効にする WHERE 句を使用してクエリを実行します。WHERE 句にパーティション化された列の定数が含まれていると、オプティマイザーは探すべきパーティションを把握できるため、クエリをより効率的に行えます。

このアドバイスにはもう 1 つ、データベースの設計に関する側面があります。クエリに含まれるテーブルの数が少ないほどクエリは速く実行されます。データベースの設計を非正規化できれば、オプティマイザーがスキャンする行の数を減らすことができ、クエリのパフォーマンスを速めることができます。

一時テーブルの使用と、ディスク上の一時テーブルの数を最小限に抑える

Aurora MySQL 互換オプティマイザーは、クエリに関する望ましい結果をインデックスから直接得られない場合は、RAM とディスクの両方に一時テーブルを作成します。そのため、ワークロードに適したインデックスを用意することに、調整作業の大半を費やすことになります。ただし、ワークロードの中にはインデックス以外のものに依存しているクエリもあり、オペレーションによっては一時ファイルで実行される場合があります。こうした処理が最小限に抑えられ、ディスク上に作成されるテーブルがごくわずかであれば、このようなことは問題はありません。MySQL は、一時テーブルのサイズが大きくなりすぎてメモリに収容できなくなったとき、ディスクテーブルを作成します。MySQL が、内部の一時テーブルのサイズを確認するときに使用する論理は、tmp_table_size と max-heap-table-size の 2 つの変数値のうちいずれか小さい方です。これらの変数は、ワークロードに基づいて最適な値に調整できるため、一時テーブルの使用を回避できない場合であっても、ディスクへのプッシュはまれにしか行われません。

ファイルのソートを回避する

ワークロードに大量の ORDER BY クエリがある場合、これらを解決する最善の方法は、テーブルで適切なインデックスを使用することです。お使いのマルチカラムインデックスが、ファイル内でソートされないように適切に設計されていることを確認しましょう。前列が定数でスキャンされていない列は、ソートを実行できません (in、>、<、!=、BETWEEN は、右側にある次の列のソートを許可しません)。MySQL でソートを実行する最適な方法は、連続構造の中に、クエリで指定された定数値を含む列を、ソート対象の列の隣接する左側に配置するマルチカラムインデックスを配置することです。最後の手段として、ファイルソートを行わないとクエリの結果が返されない場合は、ソートをアプリケーションに移します。

集計クエリが高い同時実行性で実行されることを回避する

ワークロードには、アプリケーション内の一部の機能を満たすために少数の集計クエリが含まれていることがあります。このユースケースには、細心の注意が必要です。InnoDB エンジンは、オンライントランザクション処理 (OLTP) の適切な負荷に対応するように設計されていますが、group by クエリが高い同時実行性で数回実行されただけでも CPU には非常に重い負荷がかかり、クラスターのパフォーマンスが急速に下がる可能性があります。集計結果のセットが必要になるユースケースを解消するには、group by クエリをまったく行わずに済むように、読み取りの準備が整ったテーブルに、データを事前に集計します。

クエリの同時実行性をテストする

クエリを個別に調整するときは、それらのクエリが Aurora MySQL 互換の、複数の vCPUs で同時に実行されていることに留意します。テスト環境では 1 回につき数ミリ秒でクエリが実行されることもあります。しかしこれがすべてではありません。必ず、本番環境のクラスターで予想されるレベルの同時実行性でクエリをテストし、パフォーマンスのベンチマークを評価するようにします。クエリは、同時実行性の目標を満たしている場合のみ、本番環境にリリースするようにします。テストスクリプトでは、キャッシュから結果を取得することのないよう、必ずオプティマイザー hint sql_no_cache を使用します。同時実行でテストを実行し、結果を評価するには、mysqlslap などのツールを使用します。