前回のエントリでは、MySQL 5.7におけるオプティマイザの改良点や新機能についてのスライドを紹介した。MySQL 5.7のオプティマイザの良し悪しは、ぜひみなさんの手で確かめて頂きたい。ところで、オプティマイザといえばひとつ前のバージョンである、MySQL 5.6で追加されたオプティマイザトレースという機能がとても便利だ。使いこなせばクエリチューニングの強い味方になるので、ぜひまだ使ったことがないという方は、一度試してみて欲しい。本ブログではまだ紹介していなかったので、今日はその使い方と見方を紹介しようと思う。
オプティマイザトレースとは
一体何か。ひとことで表せば、オプティマイザがどのような実行計画を検討・比較し、どの実行計画を選択したかということを、詳細に表示してしまう機能だ。マニュアルは以下のページにある。
MySQL :: MySQL Internals Manual :: 8 Tracing the Optimizer
オプティマイザとレースのマニュアルは、MySQLインターナルマニュアルのコンテンツになっている。インターナルマニュアルなので、エキスパート向けの機能という扱いだ。まあ確かに初心者が見ても中身はサッパリ分からないだろう。本家のリファレンスマニュアルに含まれて居ないということは、もしかするとマイナーバージョンで機能変更等がされる可能性があるのかも知れない。なので、本記事はそういうことがあるかも知れないという前提でもオプティマイザトレースを使ってみようという勇者向けだと言えるかも知れない。
ちなみに、MySQL 5.6と5.7では表示する内容が少し違っていいるが、基本的にそれほどは変わらない。
使い方
オプティマイザトレースの使い方自体は至ってシンプルだ。というかたった3つのステップでトレースを見ることができる。- オプティマイザトレースを有効化する
- 解析したいクエリを実行する
- トレースを表示する
mysql> SET optimizer_trace="enabled=on"; mysql> SELECT ...以下略 ; mysql> SELECT * FROM information_schema.optimizer_trace\G
オプティマイザトレースはメモリ上に情報が蓄えられるので、複雑なクエリを解析するときにはメモリの上限に達してしまうかも知れない。そういう場合には、前もってメモリサイズを増やしておくと良いだろう。デフォルトは16KBまでしかメモリを使わないようになっている。
mysql> SET optimizer_trace_max_mem_size = 1048576; -- 1MiB
使い方は至って簡単だ。問題は出力されたトレースをどうやって見るかということだ。せっかくのトレースも、見方が分からなければ宝の持ち腐れである。
サンプルとトレースの見方
まずは最もシンプルなクエリ（DUALからの意味のないSELECT）を用いて、トレースの基本的な構造を見てみよう。mysql> select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql> select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
QUERY: select 1
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select 1 AS `1`"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
基本的な構造は、見ての通りJSON形式である。このようにトップオブジェクトにstepsという名前のメンバーがあり、その値としてjoin_preparation、join_optimization、join_executionという名前の3つのメンバーを持つ形となっている。
実は、join_preparationとjoin_executionには目下のところ大した情報は蓄えられて居ない。せいぜいが、クエリをより実態が解りやすい形に書き換える程度である。というわけで、最も価値のある情報は、join_optimizationに入る形になっている。オプティマイザトレースなので、ここがメインディッシュというのはある意味ごく自然だと言える。
次はもう少し複雑な例を見てみよう。MySQL 5.6.27上の出力である。
mysql> select * from City join Country on City.countrycode = Country.Code and City.id = Country.capital join CountryLanguage on CountryLanguage.countrycode = City.countrycode;
・・・結果略・・・
983 rows in set (0.00 sec)
mysql> select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
QUERY: select * 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`.`ID` AS `ID`,`City`.`Name` AS `Name`,`City`.`CountryCode` AS `CountryCode`,`City`.`District` AS `District`,`City`.`Population` AS `Population`,`Country`.`Code` AS `Code`,`Country`.`Name` AS `Name`,`Country`.`Continent` AS `Continent`,`Country`.`Region` AS `Region`,`Country`.`SurfaceArea` AS `SurfaceArea`,`Country`.`IndepYear` AS `IndepYear`,`Country`.`Population` AS `Population`,`Country`.`LifeExpectancy` AS `LifeExpectancy`,`Country`.`GNP` AS `GNP`,`Country`.`GNPOld` AS `GNPOld`,`Country`.`LocalName` AS `LocalName`,`Country`.`GovernmentForm` AS `GovernmentForm`,`Country`.`HeadOfState` AS `HeadOfState`,`Country`.`Capital` AS `Capital`,`Country`.`Code2` AS `Code2`,`CountryLanguage`.`CountryCode` AS `CountryCode`,`CountryLanguage`.`Language` AS `Language`,`CountryLanguage`.`IsOfficial` AS `IsOfficial`,`CountryLanguage`.`Percentage` AS `Percentage` from ((`City` join `Country` on(((`City`.`CountryCode` = `Country`.`Code`) and (`City`.`ID` = `Country`.`Capital`)))) join `CountryLanguage` on((`CountryLanguage`.`CountryCode` = `City`.`CountryCode`)))"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"transformations_to_nested_joins": {
"transformations": [
"JOIN_condition_to_WHERE",
"parenthesis_removal"
],
"expanded_query": "/* select#1 */ select `City`.`ID` AS `ID`,`City`.`Name` AS `Name`,`City`.`CountryCode` AS `CountryCode`,`City`.`District` AS `District`,`City`.`Population` AS `Population`,`Country`.`Code` AS `Code`,`Country`.`Name` AS `Name`,`Country`.`Continent` AS `Continent`,`Country`.`Region` AS `Region`,`Country`.`SurfaceArea` AS `SurfaceArea`,`Country`.`IndepYear` AS `IndepYear`,`Country`.`Population` AS `Population`,`Country`.`LifeExpectancy` AS `LifeExpectancy`,`Country`.`GNP` AS `GNP`,`Country`.`GNPOld` AS `GNPOld`,`Country`.`LocalName` AS `LocalName`,`Country`.`GovernmentForm` AS `GovernmentForm`,`Country`.`HeadOfState` AS `HeadOfState`,`Country`.`Capital` AS `Capital`,`Country`.`Code2` AS `Code2`,`CountryLanguage`.`CountryCode` AS `CountryCode`,`CountryLanguage`.`Language` AS `Language`,`CountryLanguage`.`IsOfficial` AS `IsOfficial`,`CountryLanguage`.`Percentage` AS `Percentage` from `City` join `Country` join `CountryLanguage` where ((`CountryLanguage`.`CountryCode` = `City`.`CountryCode`) and (`City`.`CountryCode` = `Country`.`Code`) and (`City`.`ID` = `Country`.`Capital`))"
}
},
{
"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`))"
}
]
}
},
{
"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": [
]
}
]
},
{
"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
}
]
},
{
"rows_estimation": [
{
"table": "`City`",
"table_scan": {
"rows": 4188,
"cost": 25
}
},
{
"table": "`Country`",
"table_scan": {
"rows": 239,
"cost": 6
}
},
{
"table": "`CountryLanguage`",
"table_scan": {
"rows": 984,
"cost": 6
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`Country`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"access_type": "scan",
"rows": 239,
"cost": 53.8,
"chosen": true
}
]
},
"cost_for_plan": 53.8,
"rows_for_plan": 239,
"rest_of_plan": [
{
"plan_prefix": [
"`Country`"
],
"table": "`CountryLanguage`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"rows": 1,
"cost": 239.2,
"chosen": true
},
{
"access_type": "ref",
"index": "CountryCode",
"rows": 1,
"cost": 239.2,
"chosen": false
},
{
"access_type": "scan",
"using_join_cache": true,
"rows": 738,
"cost": 35333,
"chosen": false
}
]
},
"cost_for_plan": 340.6,
"rows_for_plan": 239,
"rest_of_plan": [
{
"plan_prefix": [
"`Country`",
"`CountryLanguage`"
],
"table": "`City`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"rows": 1,
"cost": 239.2,
"chosen": true
},
{
"access_type": "ref",
"index": "CountryCode",
"rows": 1,
"cost": 239.2,
"chosen": false
},
{
"access_type": "scan",
"using_join_cache": true,
"rows": 3141,
"cost": 150381,
"chosen": false
}
]
},
"added_to_eq_ref_extension": true,
"cost_for_plan": 627.4,
"rows_for_plan": 239,
"chosen": true
}
]
}
]
},
{
"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
},
{
"access_type": "scan",
"rows": 984,
"cost": 202.8,
"chosen": true
}
]
},
"cost_for_plan": 202.8,
"rows_for_plan": 984,
"rest_of_plan": [
{
"plan_prefix": [
"`CountryLanguage`"
],
"table": "`Country`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"rows": 1,
"cost": 984.2,
"chosen": true
},
{
"access_type": "scan",
"using_join_cache": true,
"rows": 180,
"cost": 35443,
"chosen": false
}
]
},
"cost_for_plan": 1383.6,
"rows_for_plan": 984,
"pruned_by_cost": true
},
{
"plan_prefix": [
"`CountryLanguage`"
],
"table": "`City`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"access_type": "ref",
"index": "CountryCode",
"rows": 1,
"cost": 984.2,
"chosen": true
},
{
"access_type": "scan",
"using_join_cache": true,
"rows": 3141,
"cost": 618387,
"chosen": false
}
]
},
"cost_for_plan": 1383.6,
"rows_for_plan": 984,
"pruned_by_cost": true
}
]
},
{
"plan_prefix": [
],
"table": "`City`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"access_type": "ref",
"index": "CountryCode",
"usable": false,
"chosen": false
},
{
"access_type": "scan",
"rows": 4188,
"cost": 862.6,
"chosen": true
}
]
},
"cost_for_plan": 862.6,
"rows_for_plan": 4188,
"pruned_by_cost": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`City`.`ID` = `Country`.`Capital`) and (`CountryLanguage`.`CountryCode` = `Country`.`Code`) and (`City`.`CountryCode` = `Country`.`Code`))",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`Country`",
"attached": "(`Country`.`Capital` is not null)"
},
{
"table": "`CountryLanguage`",
"attached": null
},
{
"table": "`City`",
"attached": "(`City`.`CountryCode` = `Country`.`Code`)"
}
]
}
},
{
"refine_plan": [
{
"table": "`Country`",
"access_type": "table_scan"
},
{
"table": "`CountryLanguage`"
},
{
"table": "`City`"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
join_optimizationの中身を見ていこう。基本的には値はstepsという名前のメンバーを持つオブジェクトになっており、stepsの値は配列である。
"select#": 1の部分は、EXPLAINのidフィールドと同じである。MySQLでは、SELECTの単位はJOINである。JOINはいくつのテーブルを結合しても、idは変わらない。サブクエリやUNIONではidが変化する。idの詳しい意味については過去に書いた「MySQLのEXPLAINを徹底解説!!」というエントリを参照して欲しい。
次のtransformations_to_nested_joinsでは、JOINのON句の条件がWHERE句へと移動されている。これは文法上等価な変換であり、実行計画を探索する前に施される処理ということになる。ちなみに、MySQL 5.7ではtransformations_to_nested_joinsは、join_optimizationではなく、join_preparationへと移動されている。
condition_processingではオプティマイザが実行する各種の等価変換が記述される。上記の例では、equality_propagation（等価比較に対する推移律）を適用した結果、`CountryLanguage`.`CountryCode`, `City`.`CountryCode`, `Country`.`Code`という3つのカラムはすべて同じ値であることが判明したため、同じグループにまとめられている。constant_propagationは、推移律の対象となる値に、定数が含まれている場合に適用される最適化である。trivial_condition_removalでは、WHERE 1=1のような不要な条件が省かれる。
table_dependenciesでは、テーブルの依存関係が示される。これはOUTER JOINで意味がある項目であり、今回はINNER JOINなので何も意味のある情報は含まれていない。OUTER JOINの場合には、depends_on_map_bitsに依存するテーブルのmap_bitが表示される。
ref_optimizer_key_usesは、利用可能なインデックスの情報、rows_estimationはテーブルごとの行数の見積もりだ。
そして、オプティマイザトレースで最も重要な項目が、considered_execution_plansだ。値は配列になっていて、それぞれがJOINの順序を変えた場合のコストなどが表示される。配列の要素のオブジェクトは入れ子になっていて、次のような構造になっている。
{
"plan_prefix": [
],
"table": "`Country`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"access_type": "scan",
"rows": 239,
"cost": 53.8,
"chosen": true
}
]
},
"cost_for_plan": 53.8,
"rows_for_plan": 239,
"rest_of_plan": [
下位の実行計画
]
plan_prefixは、既にアクセスされたテーブルである。駆動表といえば解りやすいだろうか。この実行計画では、Countryテーブルが最初にアクセスされるため、plan_prefixは空となっている。tableには現在アクセスしているテーブルが、best_access_pathにはこのテーブルへアクセスするのに使われる実行計画に関する情報がまとめられている。値は配列になっていて、要素は次のようなメンバーを持つオブジェクトである。
access_typeは、レコードアクセスタイプとも呼ばれ、対象のテーブルに対してどのような方法でアクセスするかを示す。この例では、ref（ユニークでないインデックスを用いた等価比較）とscan（テーブルスキャン）が表示されている。アクセスタイプはEXPLAINと同じなので、た「MySQLのEXPLAINを徹底解説!!」を参照して欲しい。それぞれのアクセスタイプが使用可能か、使用可能な場合コストはいくつか、オプティマイザによって選択されたかといった情報が含まれている。この実行計画では、Countryテーブルをスキャンするものが選択されている。その結果、このテーブルへアクセスするコストが53.8であるということが分かった。
最後の要素、rest_of_planは、このテーブルの後にアクセスされるテーブルの情報である。rest_of_planの各要素も、同じ構造を持ったオブジェクトになっている。rest_of_planのplan_prefixには、これまでアクセスしたテーブルが登場することになる。最終的なクエリのコストは、rest_of_planのネストが一番深いところのものを見て判定する。例えば一つ目の実行計画では、ネストの一番深いところのコストは627.4となっており、これが実行計画全体のコストとなる。
considered_execution_plansの、2つ目以降の実行計画は、rest_of_planでpruned_by_costがtrueになっている。これは、コストの計算が途中であるにも関わらず、既に判明している他の実行計画よりもコストがかかることが判明してしまったため、途中でコストの計算を切り上げたことを意味する。例えば、2つ目の実行計画（CountryLanguageからJOINするもの）は、2つ目のテーブルをJOINしたところで、良い方でもコストは1383.6となっており、一つ目の627.4よりもコストが大きい。これ以上JOINをするとさらにコストは増えるため、この実行計画には将来性はないことが分かる。要するに、pruned_by_costがtrueな実行計画は、選択されなかったということになる。ちなみに、rest_of_planも配列になっていて、今回のように3つ以上のテーブルをJOINする場合、2つ目にアクセスするテーブルは2通りあるため、rest_of_planには2通りの実行計画が含まれることになる。
attaching_conditions_to_tablesは、それぞれのテーブルにどのような比較の条件が適用されるかを示している。アクセスタイプによるものだけでなく、インデックスが適用できない条件などがあれば、ここに表示される。
refine_planは、最終的にJOINがどのような順序で実行されるかを簡素に示したものだ。
まとめ
オプティマイザトレースは、まだ若干複雑ではあるが、EXPLAINだけでは分からないコストの情報が含まれており、使いこなせば強力な解析ツールになり得るものだ。JOINの順序が思うように行かない場合、わざわざSTRAIGHT_JOINなどのヒントを指定して、JOINの順序を入れ替えてからEXPLAINで解析するといったような手間は、もはや必要ない。オプティマイザトレースを使えば、オプティマイザが検討した実行計画の一覧が見られるからだ。エキスパートの読者諸氏は、ぜひ現場でもオプティマイザトレースを活用してみて欲しい。今回はJOINだけのクエリをサンプルとしてお見せしたが、当然ながらサブクエリ等があれば様相は変わってくる。サブクエリを含んだものに関しては、機会を改めて紹介したいと思う。
0 コメント:
コメントを投稿