目次
とある日
それはそうと optimizer trace 便利だから、みんな使おう。
— Nayuta Yanagisawa (@NayutaYanagisaw) 2021年11月6日
そういえば、ちゃんと使ったことないなと思ったので一度ちゃんと調べてみる。
↓過去に一度だけ触ったことがある。
optimizer_trace有効化
optimizer_traceシステム変数は、デフォルトは無効化されているので有効化します。
mysql> show variables like '$optimizer%'; Empty set (0.00 sec) mysql> show variables like '%optimizer_trace%'\G; *************************** 1. row *************************** Variable_name: optimizer_trace Value: enabled=off,one_line=off *************************** 2. row *************************** Variable_name: optimizer_trace_features Value: greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on *************************** 3. row *************************** Variable_name: optimizer_trace_limit Value: 1 *************************** 4. row *************************** Variable_name: optimizer_trace_max_mem_size Value: 1048576 *************************** 5. row *************************** Variable_name: optimizer_trace_offset Value: -1 5 rows in set (0.01 sec) ERROR: No query specified mysql> SET optimizer_trace='enabled=on'; Query OK, 0 rows affected (0.00 sec)
使い方
SQLを実行してINFORMATION_SCHEMA.OPTIMIZER_TRACEを参照するだけ。
実行するSQLはEXPALINをつけても結果は変わらない。
mysql> select * from city where id between 1 and 3306 limit 2\G ; *************************** 1. row *************************** ID: 1 Name: Kabul CountryCode: AFG District: Kabol Info: {"Population": 1780000} *************************** 2. row *************************** ID: 2 Name: Qandahar CountryCode: AFG District: Qandahar Info: {"Population": 237500} 2 rows in set (0.00 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G *************************** 1. row *************************** QUERY: select * from city where id between 1 and 3306 TRACE: { "steps": [
今回はoptimizer_traceの詳しい解説などはしないので参考になりそうな記事を下記においておきます。
optimizer_trace検証
丁度いい議題があったのでそれを参考にさせていただきます。
なるほど、ありがとうございます。
— foresta (@foresta3_t) 2021年11月3日
その場合このexplain/query結果の差が疑問です。
where id in (x,y)とwhere id between x and yでexplain上は同じtype rangeにも関わらず明らかにrowsが異なっていて、実際の実行時間も全然違います。 pic.twitter.com/2xVEhYcuM2
EXPALINの結果でtypeが同じrangeなのでどんな動作になっているのか的な話だと思う...。
使うテーブルは、MySQL公式が出しているworld_xを使用します。
【SQL】MySQL公式サンプルデータベースを使う - Qiita
MySQL :: MySQL 8.0 リファレンスマニュアル :: 20.3.2 world_x データベースのダウンロードおよびインポート
比較クエリ
確かにどちらもtype range になっている。
IN
mysql> select * from city where city.id in (1,3306); mysql> explain select * from city where city.id in (1,3306)\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: city partitions: NULL type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 2 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)
BETWEEN
mysql> select * from city where id between 1 and 3306; mysql> explain select * from city where id between 1 and 3306\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: city partitions: NULL type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 2039 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)
optimizer_trace実行結果
IN
mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G *************************** 1. row *************************** QUERY: explain select * from city where city.id in (1,3306) TRACE: { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "IN_uses_bisection": true }, { "expanded_query": "/* select#1 */ select `city`.`ID` AS `ID`,`city`.`Name` AS `Name`,`city`.`CountryCode` AS `CountryCode`,`city`.`District` AS `District`,`city`.`Info` AS `Info` from `city` where (`city`.`ID` in (1,3306))" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`city`.`ID` in (1,3306))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`city`.`ID` in (1,3306))" }, { "transformation": "constant_propagation", "resulting_condition": "(`city`.`ID` in (1,3306))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`city`.`ID` in (1,3306))" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`city`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`city`", "range_analysis": { "table_scan": { "rows": 4079, "cost": 418 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": [ "ID" ] } ], "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "query_references_nonkey_column" } ] }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "1 <= ID <= 1", "3306 <= ID <= 3306" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "in_memory": 1, "rows": 2, "cost": 0.71, "chosen": true } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "PRIMARY", "rows": 2, "ranges": [ "1 <= ID <= 1", "3306 <= ID <= 3306" ] }, "rows_for_plan": 2, "cost_for_plan": 0.71, "chosen": true } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`city`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 2, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "range", "range_details": { "used_index": "PRIMARY" }, "resulting_rows": 2, "cost": 0.91, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 2, "cost_for_plan": 0.91, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`city`.`ID` in (1,3306))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`city`", "attached": "(`city`.`ID` in (1,3306))" } ] } }, { "finalizing_table_conditions": [ { "table": "`city`", "original_table_condition": "(`city`.`ID` in (1,3306))", "final_table_condition ": "(`city`.`ID` in (1,3306))" } ] }, { "refine_plan": [ { "table": "`city`" } ] } ] } }, { "join_explain": { "select#": 1, "steps": [ ] } } ] } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.00 sec)
以下必要な部分だけ抜粋。
"range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "1 <= ID <= 1", "3306 <= ID <= 3306" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "in_memory": 1, "rows": 2, "cost": 0.71, "chosen": true }
where city.id in (1,3306) は内部では、["1 <= ID <= 1","3306 <= ID <= 3306"]として変換されているようです。
BETWEEN
mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G *************************** 1. row *************************** QUERY: explain select * from city where id between 1 and 3306 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`.`Info` AS `Info` from `city` where (`city`.`ID` between 1 and 3306)" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`city`.`ID` between 1 and 3306)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`city`.`ID` between 1 and 3306)" }, { "transformation": "constant_propagation", "resulting_condition": "(`city`.`ID` between 1 and 3306)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`city`.`ID` between 1 and 3306)" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`city`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`city`", "range_analysis": { "table_scan": { "rows": 4079, "cost": 418 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": [ "ID" ] } ], "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "query_references_nonkey_column" } ] }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "1 <= ID <= 3306" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "in_memory": 1, "rows": 2039, "cost": 204.61, "chosen": true } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "PRIMARY", "rows": 2039, "ranges": [ "1 <= ID <= 3306" ] }, "rows_for_plan": 2039, "cost_for_plan": 204.61, "chosen": true } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`city`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 2039, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "range", "range_details": { "used_index": "PRIMARY" }, "resulting_rows": 2039, "cost": 408.51, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 2039, "cost_for_plan": 408.51, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`city`.`ID` between 1 and 3306)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`city`", "attached": "(`city`.`ID` between 1 and 3306)" } ] } }, { "finalizing_table_conditions": [ { "table": "`city`", "original_table_condition": "(`city`.`ID` between 1 and 3306)", "final_table_condition ": "(`city`.`ID` between 1 and 3306)" } ] }, { "refine_plan": [ { "table": "`city`" } ] } ] } }, { "join_explain": { "select#": 1, "steps": [ ] } } ] } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.00 sec)
以下必要な部分だけ抜粋。
"range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "1 <= ID <= 3306" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "in_memory": 1, "rows": 2039, "cost": 204.61, "chosen": true }
BETWEENはわりと直感的な変換がされている模様。
〆
EXPALINでは表面的な内容しか把握できませんが、optimizer_traceを使えばより詳しくオプティマイザがクエリがどう解釈しているかが見て取れます。
ただ、使いこなすにはかなり骨が折れそうです。
