クエリーオプティマイザのタスクは SQL クエリーを実行するために最適なプランを見つけることです。 「良い」プランと「悪い」プランのパフォーマンスの差は、桁違い (つまり、数秒に対して数時間や数日にまで) になる可能性があるため、MySQL のオプティマイザを含むほとんどのクエリーオプティマイザは、多かれ少なかれ、すべての可能なクエリー評価プランの中から最適なプランを徹底的に探します。 結合クエリーに対して、MySQL オプティマイザによって調査される可能なプランの数は、クエリーで参照されるテーブル数とともに指数関数的に増大します。 少数のテーブル (一般に 7 から 10 未満) の場合、これは問題になりません。 ただし、大きいクエリーが発行されると、クエリーの最適化に費やされる時間がサーバーのパフォーマンスの大きなボトルネックになる可能性があります。
クエリー最適化のより柔軟な方法により、ユーザーはオプティマイザが最適なクエリー評価プランをどの程度徹底的に探すかを制御できます。 一般的な考えは、オプティマイザによって調査されるプランが少ないほど、クエリーのコンパイルに費やす時間も少なくなるということです。 一方、オプティマイザは一部のプランをスキップするため、最適なプランを見逃す可能性もあります。
評価するプランの数に関して、オプティマイザの動作を 2 つのシステム変数を使用して制御できます。
optimizer_prune_level変数は、オプティマイザに、テーブルごとにアクセスされる行数の見積もりに基づいて、特定のプランをスキップするように伝えます。 経験上、この種類の「学習による推測」は最適なプランをめったに見逃すことはなく、クエリーのコンパイル時間を劇的に短縮できます。 デフォルトでこのオプションがオンoptimizer_prune_level=1であるのはこのためです。 ただし、オプティマイザがより適したクエリー計画を見逃したと思う場合は、クエリーのコンパイルにかなりの時間がかかるリスクを伴いますが、このオプションをオフにする (optimizer_prune_level=0) ことができます。 この経験則を使用しても、オプティマイザはまだ指数関数的な数のプランを探索します。optimizer_search_depth変数は、オプティマイザがそれ以上拡張すべきかどうかを評価するために、不完全な各プランの「将来」をどの程度見通すかを伝えます。optimizer_search_depthの値を小さくするほど、クエリーのコンパイル時間が桁違いに少なくなる可能性があります。 たとえば、12、13、またはそれ以上のテーブルのクエリーは、optimizer_search_depthがクエリー内のテーブル数に近い場合、コンパイルに数時間または数日間も容易に必要になることがあります。 同時に、3 か 4 に等しいoptimizer_search_depthでコンパイルされた場合、オプティマイザは同じクエリーで 1 分以内にコンパイルできることがあります。optimizer_search_depthの適切な値が不明な場合、この変数を 0 に設定することで、オプティマイザに自動的に値を決定させることができます。