前回のエントリでは、MySQL 5.7におけるオプティマイザの改良点や新機能についてのスライドを紹介した。MySQL 5.7のオプティマイザの良し悪しは、ぜひみなさんの手で確かめて頂きたい。ところで、オプティマイザといえばひとつ前のバージョンである、MySQL 5.6で追加されたオプティマイザトレースという機能がとても便利だ。使いこなせばクエリチューニングの強い味方になるので、ぜひまだ使ったことがないという方は、一度試してみて欲しい。本ブログではまだ紹介していなかったので、今日はその使い方と見方を紹介しようと思う。
オプティマイザトレースとは
一体何か。ひとことで表せば、オプティマイザがどのような実行計画を検討・比較し、どの実行計画を選択したかということを、詳細に表示してしまう機能だ。マニュアルは以下のページにある。
MySQL :: MySQL Internals Manual :: 8 Tracing the Optimizer
オプティマイザとレースのマニュアルは、MySQLインターナルマニュアルのコンテンツになっている。インターナルマニュアルなので、エキスパート向けの機能という扱いだ。まあ確かに初心者が見ても中身はサッパリ分からないだろう。本家のリファレンスマニュアルに含まれて居ないということは、もしかするとマイナーバージョンで機能変更等がされる可能性があるのかも知れない。なので、本記事はそういうことがあるかも知れないという前提でもオプティマイザトレースを使ってみようという勇者向けだと言えるかも知れない。
ちなみに、MySQL 5.6と5.7では表示する内容が少し違っていいるが、基本的にそれほどは変わらない。
使い方
オプティマイザトレースの使い方自体は至ってシンプルだ。というかたった3つのステップでトレースを見ることができる。- オプティマイザトレースを有効化する
- 解析したいクエリを実行する
- トレースを表示する
1 2 3 | mysql> SET optimizer_trace="enabled=on";mysql> SELECT ...以下略 ;mysql> SELECT * FROM information_schema.optimizer_trace\G |
オプティマイザトレースはメモリ上に情報が蓄えられるので、複雑なクエリを解析するときにはメモリの上限に達してしまうかも知れない。そういう場合には、前もってメモリサイズを増やしておくと良いだろう。デフォルトは16KBまでしかメモリを使わないようになっている。
1 | mysql> SET optimizer_trace_max_mem_size = 1048576; -- 1MiB |
使い方は至って簡単だ。問題は出力されたトレースをどうやって見るかということだ。せっかくのトレースも、見方が分からなければ宝の持ち腐れである。
サンプルとトレースの見方
まずは最もシンプルなクエリ（DUALからの意味のないSELECT）を用いて、トレースの基本的な構造を見てみよう。1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | 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: 01 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上の出力である。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 | 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: 01 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の順序を変えた場合のコストなどが表示される。配列の要素のオブジェクトは入れ子になっていて、次のような構造になっている。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | { "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 コメント:
コメントを投稿