このページは機械翻訳したものです。

8.9.5 オプティマイザコストモデル

実行計画を生成するために、オプティマイザは、クエリーの実行中に発生する様々な操作のコストの見積りに基づくコストモデルを使用します。 オプティマイザには、実行計画に関する決定を下すために、コンパイル済のデフォルトの「「コスト定数」」のセットが用意されています。

オプティマイザには、実行計画の作成時に使用するコスト見積りのデータベースもあります。 これらの見積りは、mysql システムデータベースの server_cost テーブルおよび engine_cost テーブルに格納され、いつでも構成できます。 これらのテーブルの目的は、オプティマイザがクエリー実行計画に到達しようとしたときに使用するコスト見積りを簡単に調整できるようにすることです。

• 原価モデル一般工程

• コストモデルデータベース

• コストモデルデータベースの変更

原価モデル一般工程

構成可能なオプティマイザコストモデルは、次のように動作します:

• サーバーは、起動時にコストモデルテーブルをメモリーに読み取り、実行時にインメモリー値を使用します。 テーブルに指定されている NULL 以外のコスト見積りは、対応するコンパイル済のデフォルトのコスト定数より優先されます。 NULL の見積りは、コンパイル済のデフォルトを使用するようオプティマイザに指示します。

• 実行時に、サーバーはコストテーブルを再度読み取ることができます。 これは、ストレージエンジンが動的にロードされたとき、または FLUSH OPTIMIZER_COSTS ステートメントが実行されたときに発生します。

• 原価テーブルを使用すると、サーバー管理者はテーブルのエントリを変更することで、原価見積を簡単に調整できます。 また、エントリコストを NULL に設定することで、デフォルトに戻すことも簡単です。 オプティマイザはインメモリーのコスト値を使用するため、テーブルに対する変更の後に FLUSH OPTIMIZER_COSTS を実行して有効にする必要があります。

• クライアントセッションの開始時に現在のメモリー内コストの見積りは、そのセッションが終了するまでそのセッション全体に適用されます。 特に、サーバーがコストテーブルを再度読み取る場合、変更された見積りはその後に開始されるセッションにのみ適用されます。 既存のセッションは影響を受けません。

• コストテーブルは、特定のサーバーインスタンスに固有です。 サーバーは、コストテーブルの変更をレプリカにレプリケートしません。

コストモデルデータベース

オプティマイザコストモデルデータベースは、クエリーの実行中に発生する操作のコスト見積り情報を含む、mysql システムデータベース内の次の 2 つのテーブルで構成されます:

• server_cost: 一般的なサーバー操作のオプティマイザコストの見積り

• engine_cost: 特定のストレージエンジンに固有の操作のオプティマイザコストの見積り

server_cost テーブルには、次のカラムが含まれます:

• cost_name

  コストモデルで使用されるコスト見積の名前。 名前では大文字と小文字は区別されません。 このテーブルの読取り時にサーバーがコスト名を認識しない場合、エラーログに警告が書き込まれます。

• cost_value

  コスト見積値。 値が NULL 以外の場合、サーバーはそれをコストとして使用します。 それ以外の場合は、デフォルトの見積り (コンパイルされた値) が使用されます。 DBA は、このカラムを更新することでコスト見積りを変更できます。 サーバーは、このテーブルの読み取り時にコスト値が無効 (正でない) であることを検出すると、エラーログに警告を書き込みます。

  (NULL を指定するエントリの) デフォルトのコスト見積を上書きするには、コストを NULL 以外の値に設定します。 デフォルトに戻すには、値を NULL に設定します。 次に、FLUSH OPTIMIZER_COSTS を実行して、コストテーブルを再度読み取るようにサーバーに指示します。

• last_update

  最後の行更新の時刻。

• comment

  原価見積に関連付けられた摘要コメント。 DBA は、このカラムを使用して、コスト見積り行に特定の値が格納される理由に関する情報を提供できます。

• default_value

  原価見積のデフォルト (コンパイル済) 値。 このカラムは、関連するコスト見積りが変更された場合でもその値を保持する読取り専用の生成カラムです。 実行時にテーブルに追加される行の場合、このカラムの値は NULL です。

server_cost テーブルの主キーは cost_name カラムであるため、コスト見積りに対して複数のエントリを作成することはできません。

サーバーは、server_cost テーブルの次の cost_name 値を認識します:

• disk_temptable_create_cost, disk_temptable_row_cost

  ディスクベースのストレージエンジン (InnoDB または MyISAM) に格納されている内部的に作成された一時テーブルのコスト見積り。 これらの値を大きくすると、内部一時テーブルを使用するコストの見積りが増加し、オプティマイザがクエリー計画を使用しやすくなります。 このようなテーブルの詳細は、セクション8.4.4「MySQL での内部一時テーブルの使用」 を参照してください。

  これらのディスクパラメータのデフォルト値が、対応するメモリーパラメータ (memory_temptable_create_cost、memory_temptable_row_cost) のデフォルト値より大きいほど、ディスクベースのテーブルの処理コストが高くなります。

• key_compare_cost

  レコードキーを比較するコスト。 この値を増やすと、多数のキーを比較するクエリー計画のコストが高くなります。 たとえば、filesort を実行するクエリー計画は、インデックスを使用したソートを回避するクエリー計画よりも比較的コストが高くなります。

• memory_temptable_create_cost, memory_temptable_row_cost

  MEMORY ストレージエンジンに格納されている内部的に作成された一時テーブルのコスト見積り。 これらの値を大きくすると、内部一時テーブルを使用するコストの見積りが増加し、オプティマイザがクエリー計画を使用しやすくなります。 このようなテーブルの詳細は、セクション8.4.4「MySQL での内部一時テーブルの使用」 を参照してください。

  これらのメモリーパラメータのデフォルト値が、対応するディスクパラメータ (disk_temptable_create_cost、disk_temptable_row_cost) のデフォルト値より小さいほど、メモリーベースのテーブルの処理コストは低くなります。

• row_evaluate_cost

  レコード条件を評価するコスト。 この値を増やすと、多数の行を調査するクエリー計画が、調査する行数が少ないクエリー計画よりもコストが高くなります。 たとえば、読取り行数が少ないレンジスキャンよりも、テーブルスキャンの方が比較的コストが高くなります。

engine_cost テーブルには、次のカラムが含まれます:

• engine_name

  このコスト見積りが適用されるストレージエンジンの名前。 名前では大文字と小文字は区別されません。 値が default の場合は、独自の名前付きエントリを持たないすべてのストレージエンジンに適用されます。 このテーブルの読取り時にサーバーがエンジン名を認識しない場合、エラーログに警告が書き込まれます。

• device_type

  この原価見積が適用される設備タイプ。 このカラムは、ハードディスクドライブとソリッドステートドライブなど、ストレージデバイスタイプごとに異なるコスト見積りを指定するためのものです。 現在、この情報は使用されず、許可される値は 0 のみです。

• cost_name

  server_cost テーブルと同じです。

• cost_value

  server_cost テーブルと同じです。

• last_update

  server_cost テーブルと同じです。

• comment

  server_cost テーブルと同じです。

• default_value

  原価見積のデフォルト (コンパイル済) 値。 このカラムは、関連するコスト見積りが変更された場合でもその値を保持する読取り専用の生成カラムです。 実行時にテーブルに追加される行の場合、このカラムの値は NULL ですが、行の cost_name 値が元の行のいずれかと同じ場合、default_value カラムの値はその行と同じになります。

engine_cost テーブルの主キーは (cost_name, engine_name, device_type) カラムで構成されるタプルであるため、これらのカラムの値の組合せに対して複数のエントリを作成することはできません。

サーバーは、engine_cost テーブルの次の cost_name 値を認識します:

• io_block_read_cost

  ディスクからインデックスまたはデータブロックを読み取るコスト。 この値を増やすと、多くのディスクブロックを読み取るクエリー計画が、読み取るディスクブロックが少ないクエリー計画よりもコストが高くなります。 たとえば、読取りブロック数が少ないレンジスキャンよりも、テーブルスキャンの方が比較的コストが高くなります。

• memory_block_read_cost

  io_block_read_cost と似ていますが、インメモリーデータベースバッファからインデックスまたはデータブロックを読み取るコストを表します。

io_block_read_cost と memory_block_read_cost の値が異なる場合、同じクエリーの 2 つの実行間で実行計画が変わる可能性があります。 メモリーアクセスのコストがディスクアクセスのコストよりも低いとします。 その場合、データがメモリー内にあるため、データがバッファプールに読み取られる前のサーバー起動時に、クエリーの実行後とは異なる計画が得られることがあります。

コストモデルデータベースの変更

コストモデルパラメータをデフォルトから変更する DBA の場合は、値を二重または停止して効果を測定してください。

io_block_read_cost および memory_block_read_cost パラメータを変更すると、結果が得られる価値が高くなる可能性があります。 これらのパラメータ値を使用すると、データアクセス方法のコストモデルで、様々なソースからの情報の読取りコスト (ディスクからの情報の読取りコストとメモリーバッファにすでに存在する情報の読取りコスト) を考慮できます。 たとえば、io_block_read_cost を memory_block_read_cost より大きい値に設定すると、メモリーにすでに保持されている情報を読み取るクエリー計画が、ディスクから読み取る必要がある計画よりもオプティマイザによって優先されます。

次の例では、io_block_read_cost のデフォルト値を変更する方法を示します:

UPDATE mysql.engine_cost
  SET cost_value = 2.0
  WHERE cost_name = 'io_block_read_cost';
FLUSH OPTIMIZER_COSTS;

次の例では、InnoDB ストレージエンジンについてのみ io_block_read_cost の値を変更する方法を示します:

INSERT INTO mysql.engine_cost
  VALUES ('InnoDB', 0, 'io_block_read_cost', 3.0,
  CURRENT_TIMESTAMP, 'Using a slower disk for InnoDB');
FLUSH OPTIMIZER_COSTS;