コストベース最適化

オプティマイザはコストベース最適化と呼ばれる方法に基づいて、クエリーをどのように実行するかを決定しています。このプロセスを単純化すると次のようになります。

• それぞれの操作にコストを割り当てる
• 可能性のあるそれぞれの計画にいくつの操作があるかを見積もる
• 値を合計する
• 全体のコストが一番低い計画を選択する

上記が単純化であると述べたのは、オプティマイザは可能性のあるそれぞれの実行計画を徹底的に検索するわけではないからです。例えば、5つのテーブルが結合される場合、各テーブルに5つのインデックスがあるとすると、5! * 5! =14400 を「超える」クエリーの実行パターンがあることになります。

• 各インデックスは1つ以上アクセス方法があります(例えば、インデックススキャン、範囲スキャンやインデックスルックアップです)。その上、各テーブルがテーブルスキャンを利用する可能性もあります。
• INNER JOINクエリーではテーブルはどの順序でも結合可能です(テーブルが指定された順序は関係ありません)
• 結合する際に、複数の結合バッファーの方法あるいはサブクエリーの戦略が利用可能となります。

オプティマイザが考えうる実行計画を全て評価するのは現実的に実行可能ではありません。例えば、最適化が実行より長い時間かかってしまう場合があるでしょう。このような理由からオプティマイザは、ある一定の計画の評価をデフォルトでスキップします¹。クエリー計画を検索する深さを制限するためのoptimizer_search_depthという設定パラメータもありますが、デフォルトでは有効化されていません²。

コスト係数を修正する

それぞれの操作に使われるコストはmysqlシステムデータベース内のserver_costおよびengine_costテーブルを利用して設定可能です。MySQL 8.0でのデフォルト値は次の通りです。

コスト自体はリソース利用量を表す論理的な単位です。1単位にもはや正確な意味はありませんが、起源としては1990年代のハードディスクの1回のランダムIOと遡れます。

ハードウェアが進化したため、全ての構成要素でコストは一定とはみなせません(たとえばストレージのレイテンシーはSSDの出現と共に改善しました)。同様に、ソフトウェアもハードウェアの変化に対してとりくんでいるため(例えば、圧縮のような機能により)、リソース利用量も変化しうるでしょう。コンフィグ可能なコスト係数があれば、このようなケースに対して改善できます。

例4はrow_evaluate_costを5倍にしたため、テーブルスキャンのコストが(インデックスを利用することによってかかる余分なコストと比べて)とても大きくなっていることを示しています。これによってオプティマイザは例2で作成したp(population)インデックスを選択しています。

例4: 行あたりの見積もりが増えることでテーブルスキャンが高コストになる

# コストは0.2から1.0に増える
UPDATE mysql.server_cost SET cost_value=1 WHERE cost_name='row_evaluate_cost';
FLUSH OPTIMIZER_COSTS;

# 新規セッションにて
EXPLAIN FORMAT=JSON
SELECT * FROM Country WHERE continent='Asia' and population > 5000000;
{
   "select_id": 1,
   "cost_info": {          # 行あたりの見積もりが5倍になったため
   "query_cost": "325.01"  # クエリーに対する合計コストが
   },                      # 増加します
   "table": {
   "table_name": "Country",
   "access_type": "range", # rangeアクセスで実行されます
   "possible_keys": [
      "p"
   ],
   "key": "p",
   "used_key_parts": [
      "Population"
   ],
   "key_length": "4",
   "rows_examined_per_scan": 108,
   "rows_produced_per_join": 15,
   "filtered": "14.29",
   "index_condition": "(`world`.`Country`.`Population` > 5000000)",
   "cost_info": {
      "read_cost": "309.58",
      "eval_cost": "15.43",
      "prefix_cost": "325.01",
      "data_read_per_join": "3K"
   },
   "used_columns": [
      "Code",
      "Name",
      "Continent",
      "Region",
      "SurfaceArea",
      "IndepYear",
      "Population",
      "LifeExpectancy",
      "GNP",
      "GNPOld",
      "LocalName",
      "GovernmentForm",
      "HeadOfState",
      "Capital",
      "Code2"
   ],
   "attached_condition": "(`world`.`Country`.`Continent` = 'Asia')"
   }
  }
}

メタデータと統計

例3で示されたように、データの分布は実行計画のコストに影響を与えます。オプティマイザは意思決定プロセスの一部でデータディクショナリと統計を利用します。

メタデータ

統計