オプティマイザトレースとは
EXPLAIN を実行することでオプティマイザの判断を知ることができるが、EXPLAIN で分かるのはオプティマイザが最終的に選択した実行計画についてのみであり、その過程でどのような最適化やコスト計算、あるいは実行計画の比較が行われたかはわからない。
オプティマイザトレースはこの過程についても表示することができる機能となる。
MySQL のオプティマイザトレースについては以下の公式ドキュメントに記載がある。
https://dev.mysql.com/doc/internals/en/optimizer-tracing.html
オプティマイザトレースの使い方
オプティマイザトレースは以下のように使用する。
1. オプティマイザトレースを有効化する
mysql> SET optimizer_trace='enabled=on';
Query OK, 0 rows affected (0.00 sec) 2. 解析したいクエリを実行する
mysql> EXPLAIN SELECT City.Name, Country.Code, CountryLanguage.Language FROM City JOIN Country ON City.CountryCode = Country.Code AND City.Id = Country.Capital JOIN CountryLanguage ON CountryLanguage.CountryCode = City.CountryCode;
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------+------+----------+-------------+
| 1 | SIMPLE | Country | NULL | ALL | PRIMARY | NULL | NULL | NULL | 239 | 100.00 | Using where |
| 1 | SIMPLE | City | NULL | eq_ref | PRIMARY,CountryCode | PRIMARY | 4 | world.Country.Capital | 1 | 5.00 | Using where |
| 1 | SIMPLE | CountryLanguage | NULL | ref | PRIMARY,CountryCode | CountryCode | 9 | world.Country.Code | 4 | 100.00 | Using index |
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------+------+----------+-------------+
3 rows in set (0.00 sec) 3. トレースを表示する
mysql> SELECT * FROM information_schema.optimizer_trace \G; オプティマイザトレースの詳細
上で実行したクエリに対するオプティマイザトレースを順に見ていく。
QUERY: EXPLAIN SELECT City.Name, Country.Code, CountryLanguage.Language FROM City JOIN Country ON City.CountryCode = Country.Code AND City.Id = Country.Capital JOIN CountryLanguage ON CountryLanguage.CountryCode = City.CountryCode
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `City`.`Name` AS `Name`,`Country`.`Code` AS `Code`,`CountryLanguage`.`Language` AS `Language` from ((`City` join `Country` on(((`City`.`CountryCode` = `Country`.`Code`) and (`City`.`ID` = `Country`.`Capital`)))) join `CountryLanguage` on((`CountryLanguage`.`CountryCode` = `City`.`CountryCode`)))"
},
{
"transformations_to_nested_joins": {
"transformations": [
"JOIN_condition_to_WHERE",
"parenthesis_removal"
],
"expanded_query": "/* select#1 */ select `City`.`Name` AS `Name`,`Country`.`Code` AS `Code`,`CountryLanguage`.`Language` AS `Language` from `City` join `Country` join `CountryLanguage` where ((`CountryLanguage`.`CountryCode` = `City`.`CountryCode`) and (`City`.`CountryCode` = `Country`.`Code`) and (`City`.`ID` = `Country`.`Capital`))"
}
}
] まず、最初の部分より入力されたクエリがどのようなものだったかがわかる。select# というのは EXPLAIN における id と同等であり、SELECT に割り当てられた一意の番号である。
join_preparation の transformations_to_nested_joins では、クエリの実行計画を探す前に、クエリを完全な形に展開し、JOIN の ON 句にある条件を WHERE 句へと移動している (等価変換)。
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`CountryLanguage`.`CountryCode` = `City`.`CountryCode`) and (`City`.`CountryCode` = `Country`.`Code`) and (`City`.`ID` = `Country`.`Capital`))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(multiple equal(`CountryLanguage`.`CountryCode`, `City`.`CountryCode`, `Country`.`Code`) and multiple equal(`City`.`ID`, `Country`.`Capital`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(multiple equal(`CountryLanguage`.`CountryCode`, `City`.`CountryCode`, `Country`.`Code`) and multiple equal(`City`.`ID`, `Country`.`Capital`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(multiple equal(`CountryLanguage`.`CountryCode`, `City`.`CountryCode`, `Country`.`Code`) and multiple equal(`City`.`ID`, `Country`.`Capital`))"
}
]
}
}, join_optimization には最適化に関する豊富な情報が含まれている。
まず最初のステップは WHERE 句の変換となる。WHERE 句の条件から無駄を省きオプティマイザが扱いやすい形式に変える。具体的には 3 つのアルゴリズムが適用されている。それぞれ equality_propagation (等価比較による推移率)、constant_propagation (定数に対する等価比較による推移率)、trivial_condition_removal (自明な条件の削除) となる。
上記において、CountryLanguage.CountryCode = City.CountryCode = Country.Code が全て同一であることが判明したため、multiple equal にまとめられている。
なお、定数に対する等価比較や自明な検索条件は含まれないため、他の２つのアルゴリズムによる変換は行われていない。traivial_condition_removal では WHERE … AND 1=1 のようなクエリで無駄な部分が省略されることになる。
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`City`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
},
{
"table": "`Country`",
"row_may_be_null": false,
"map_bit": 1,
"depends_on_map_bits": [
]
},
{
"table": "`CountryLanguage`",
"row_may_be_null": false,
"map_bit": 2,
"depends_on_map_bits": [
]
}
]
}, substitute_generated_columuns は MySQL 5.7 から追加された生成カラムに対する処理となる。
tagle_dependencies ではテーブルの依存関係が示されるが、OUTER JOIN で意味がある項目となり、今回は INNER JOIN なので意味のある情報はない。
OUTER JOIN の場合は depends_on_map_bits に依存するテーブルの map_bit が表示される。テーブル間に依存関係があった場合に JOIN の順序が制限され、先に依存されているテーブルへアクセスするような実行計画しか採用できない。どう制限されるかは depends_on_map_bits を見れば分かるようになっている。
{
"ref_optimizer_key_uses": [
{
"table": "`City`",
"field": "ID",
"equals": "`Country`.`Capital`",
"null_rejecting": true
},
{
"table": "`City`",
"field": "CountryCode",
"equals": "`Country`.`Code`",
"null_rejecting": false
},
{
"table": "`City`",
"field": "CountryCode",
"equals": "`CountryLanguage`.`CountryCode`",
"null_rejecting": false
},
{
"table": "`City`",
"field": "ID",
"equals": "`Country`.`Capital`",
"null_rejecting": true
},
{
"table": "`Country`",
"field": "Code",
"equals": "`CountryLanguage`.`CountryCode`",
"null_rejecting": false
},
{
"table": "`Country`",
"field": "Code",
"equals": "`City`.`CountryCode`",
"null_rejecting": false
},
{
"table": "`CountryLanguage`",
"field": "CountryCode",
"equals": "`Country`.`Code`",
"null_rejecting": false
},
{
"table": "`CountryLanguage`",
"field": "CountryCode",
"equals": "`City`.`CountryCode`",
"null_rejecting": false
},
{
"table": "`CountryLanguage`",
"field": "CountryCode",
"equals": "`Country`.`Code`",
"null_rejecting": false
},
{
"table": "`CountryLanguage`",
"field": "CountryCode",
"equals": "`City`.`CountryCode`",
"null_rejecting": false
}
]
}, ref_optimizer_key_uses ではこのクエリで利用可能なインデックスの一覧が並ぶことになる。
null_rejecting はこのキーと比較される対象のカラムが NULL になる可能性があるとき、追加の最適化を行うフラグとなる。City.ID に対する null_rejecting が true になっているのは、このキーの比較対象である Country.Capital が NULL になる可能性があるためである。
null_rejecting が true の場合、オプティマイザは暗黙的に比較対象のカラムに対して IS NOT NULL を検索条件に追加する。これは City.ID インデックスを使った検索は City.ID が NULL にならないため、Country.Capital が NULL の場合はマッチしないため、SELECT の結果は変わらないためである。これにより無駄な行アクセスを省くことができる。
{
"rows_estimation": [
{
"table": "`City`",
"table_scan": {
"rows": 4046,
"cost": 25
}
},
{
"table": "`Country`",
"table_scan": {
"rows": 239,
"cost": 7
}
},
{
"table": "`CountryLanguage`",
"table_scan": {
"rows": 984,
"cost": 6
}
}
]
}, rows_estimation にはアクセスされるテーブルの行数が表示されている。また、テーブルスキャンを実行したときのコストも分かるようになっている。
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`Country`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"rows_to_scan": 239,
"access_type": "scan",
"resulting_rows": 239,
"cost": 54.8,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 239,
"cost_for_plan": 54.8,
"rest_of_plan": [
{
"plan_prefix": [
"`Country`"
],
"table": "`CountryLanguage`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"rows": 4.2232,
"cost": 459.21,
"chosen": true
},
{
"access_type": "ref",
"index": "CountryCode",
"rows": 4.2232,
"cost": 451,
"chosen": true
},
{
"access_type": "scan",
"chosen": false,
"cause": "covering_index_better_than_full_scan"
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 1009.3,
"cost_for_plan": 505.8,
"rest_of_plan": [
{
"plan_prefix": [
"`Country`",
"`CountryLanguage`"
],
"table": "`City`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 440.87,
"chosen": true,
"cause": "clustered_pk_chosen_by_heuristics"
},
{
"rows_to_scan": 4046,
"access_type": "scan",
"using_join_cache": true,
"buffers_needed": 1,
"resulting_rows": 4046,
"cost": 816793,
"chosen": false
}
]
},
"condition_filtering_pct": 5,
"rows_for_plan": 50.467,
"cost_for_plan": 946.67,
"chosen": true
}
]
},
{
"plan_prefix": [
"`Country`"
],
"table": "`City`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 286.8,
"chosen": true,
"cause": "clustered_pk_chosen_by_heuristics"
},
{
"rows_to_scan": 4046,
"access_type": "scan",
"using_join_cache": true,
"buffers_needed": 1,
"resulting_rows": 4046,
"cost": 193424,
"chosen": false
}
]
},
"condition_filtering_pct": 5,
"rows_for_plan": 11.95,
"cost_for_plan": 341.6,
"rest_of_plan": [
{
"plan_prefix": [
"`Country`",
"`City`"
],
"table": "`CountryLanguage`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"rows": 4.2232,
"cost": 22.96,
"chosen": true
},
{
"access_type": "ref",
"index": "CountryCode",
"rows": 4.2232,
"cost": 22.55,
"chosen": true
},
{
"access_type": "scan",
"chosen": false,
"cause": "covering_index_better_than_full_scan"
}
]
},
"added_to_eq_ref_extension": false
},
{
"plan_prefix": [
"`Country`",
"`City`"
],
"table": "`CountryLanguage`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"rows": 4.2232,
"cost": 22.96,
"chosen": true
},
{
"access_type": "ref",
"index": "CountryCode",
"rows": 4.2232,
"cost": 22.55,
"chosen": true
},
{
"access_type": "scan",
"chosen": false,
"cause": "covering_index_better_than_full_scan"
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 50.467,
"cost_for_plan": 364.15,
"chosen": true
}
]
}
]
}, considered_execution_plans はオプティマイザが検討した実行計画が表示される箇所となる。実行計画の表現はツリー構造でネスとした形となり、ツリーの根は最初にアクセスされるテーブルとなっている。
plan_prefix とは、これまでにアクセスされたテーブルを表す項目となる。最初にアクセスされるテーブルの plan_prefix は空となる。
table は現在検討しているテーブルを示す。ここでは Country となる。
base_access_path は最も効率的なアクセスタイプを探すために、どのようなアクセスタイプを検討したか表示する項目となる。Country テーブルに対しては PRIMARY キーによる検索とテーブルスキャンが検討されており、テーブルスキャンが選択されたことがわかる (“chosen”: true) 。
condition_filtering_pct は、このテーブルから行データをフェッチしたあとに適用される WHERE 句の条件で、どの程度行の絞り込みが行われるかという概算となる。Country テーブルでは 100% となっているので、絞り込みが行われないことがわかる。
row_for_plan と cost_for_plan はそれぞれこの実行計画によってアクセスされるであろうこのテーブルの行数とそのコストを表す。
rest_of_plan は、それ以降のテーブルアクセスに対して同じような情報が入れ子状に格納されている。１つ目には CountryLanguage テーブルにアクセスするものとなっており、plan_prefix に Country テーブルが表示されていることより、Country テーブルを駆動表にして CountryLanguage テーブルを JOIN した場合のものであることが分かる。
cost_of_plan は 505.8 となっているが、これは Country テーブルにアクセスされた分のコストも含む。Country テーブルのアクセスコストが 54.8 で、CountryLanguage におけるコストが 451 のため、505.8 となっている。なお、City の cost は 440.87 となるため、最終的なコストは 946.78 となる。
最初に表示されている実行計画は Country -> CountryLanguage -> City という順で JOIN するものだが、Country テーブルを最初に JOIN する実行計画として、Country -> City -> CountryLanguage という順で JOIN するものも表示されており、こちらのコストは 364.15 となる。
{
"plan_prefix": [
],
"table": "`CountryLanguage`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"access_type": "ref",
"index": "CountryCode",
"usable": false,
"chosen": false
},
{
"rows_to_scan": 984,
"access_type": "scan",
"resulting_rows": 984,
"cost": 202.8,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 984,
"cost_for_plan": 202.8,
"rest_of_plan": [
{
"plan_prefix": [
"`CountryLanguage`"
],
"table": "`Country`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 1180.8,
"chosen": true,
"cause": "clustered_pk_chosen_by_heuristics"
},
{
"rows_to_scan": 239,
"access_type": "scan",
"using_join_cache": true,
"buffers_needed": 1,
"resulting_rows": 239,
"cost": 47045,
"chosen": false
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 984,
"cost_for_plan": 1383.6,
"pruned_by_cost": true
}, 上記は CountryLanguage テーブルに最初にアクセスする実行計画となるが、pruned_by_cost が true となっている。これは計算されたコストが既に 364.15 を上回っているため、計算が打ち切られたことを示す。Country -> City -> CountryLanguage の順番で JOIN するのが一番コストが低いことがわかる。