otpimzer trace功能的作用和优化的大致阶段
- 1.这个功能可以让我们方便的查看优化器生成执行计划的整个过程
- 2.prepare阶段
- 3.optimize阶段
- 4.execute阶段
- 5.基于成本的优化主要集中在optimize阶段
- 6.单表查询来说，我们主要关注optimize阶段的"rows_estimation"这个过程，这个过程深入分析了对单表查询的各种执行方案的成本
- 7.对于多表连接查询来说，我们更多需要关注"considered_execution_plans"这个过程，这个过程里会写明各种不同的连接方式所对应的成本
作者：简书徐小耳
链接：https://www.jianshu.com/p/161f634f5db3
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
(apple) > set optimizer_trace='enabled=on'; Query OK, 0 rows affected (0.00 sec) Fri Sep 4 16:24:44 2020(apple) > set optimizer_trace_max_mem_size=1000000; Query OK, 0 rows affected (0.00 sec) Fri Sep 4 16:24:53 2020(apple) > set end_markers_in_json=on; Query OK, 0 rows affected (0.00 sec) Fri Sep 4 16:25:03 2020(apple) > select * from information_schema.optimizer_traceG; *************************** 1. row *************************** QUERY: set end_markers_in_json=on TRACE: { "steps": [ ] } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.00 sec) ERROR: No query specified Fri Sep 4 16:25:09 2020(apple) > select * from user where name='guolicheng'; +----+------------+--------------------+---------+------+ | id | name | ids | address | inno | +----+------------+--------------------+---------+------+ | 1 | guolicheng | 422802199909136854 | 1 | | +----+------------+--------------------+---------+------+ 1 row in set (0.00 sec) Fri Sep 4 16:25:45 2020(apple) > select * from information_schema.optimizer_traceG; *************************** 1. row *************************** QUERY: select * from user where name='guolicheng' TRACE: { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `user`.`id` AS `id`,`user`.`name` AS `name`,`user`.`ids` AS `ids`,`user`.`address` AS `address`,`user`.`inno` AS `inno`
from `user` where (`user`.`name` = 'guolicheng')" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`user`.`name` = 'guolicheng')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`user`.`name` = 'guolicheng')" }, { "transformation": "constant_propagation", "resulting_condition": "(`user`.`name` = 'guolicheng')" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`user`.`name` = 'guolicheng')" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`user`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ { "table": "`user`", "field": "name", "equals": "'guolicheng'", "null_rejecting": false } ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`user`", "range_analysis": { "table_scan": { "rows": 4, "cost": 3.9 } /* table_scan */, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "uk_ids", "usable": false, "cause": "not_applicable" }, { "index": "idx_name", "usable": true, "key_parts": [ "name", "id" ] /* key_parts */ }, { "index": "idx_inno_address", "usable": false, "cause": "not_applicable" } ] /* potential_range_indexes */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "idx_name", "ranges": [ "guolicheng <= name <= guolicheng" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 1, "cost": 2.21, "chosen": true } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "idx_name", "rows": 1, "ranges": [ "guolicheng <= name <= guolicheng" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 1, "cost_for_plan": 2.21, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`user`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "idx_name", "rows": 1, "cost": 1.2, "chosen": true }, { "access_type": "range", "range_details": { "used_index": "idx_name" } /* range_details */, "chosen": false, "cause": "heuristic_index_cheaper" } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 1, "cost_for_plan": 1.2, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "(`user`.`name` = 'guolicheng')", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`user`", "attached": "(`user`.`name` = 'guolicheng')" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`user`" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.00 sec) ERROR: No query specified Fri Sep 4 16:25:48 2020