当前位置：首页 > news >正文

MySQL优化器

news 2025/6/13 18:28:43

优化器

MySQL存储引擎中存在了一个可插拔的优化器OPTIMIZER_TRACE，可以看到内部查询计划的TRACE信息，从而可以知道MySQL内部执行过程

查询优化器状态

show variables like 'optimizer_trace';Variable_name	  Value
optimizer_trace	enabled=off,one_line=off

开启优化器

set session optimizer_trace="enabled=on,one_line=on",end_markers_in_json=on;

查看优化器追踪内存大小

show variables like 'optimizer_trace_max_mem_size';Variable_name	                Value
optimizer_trace_max_mem_size	16384

可以在information_schema数据库中的OPTIMIZER_TRACE查看sql执行情况

SELECT trace FROM information_schema.OPTIMIZER_TRACE;

示例：

 explain select id,sum(cost) from jr_form where id in (
select max(id) from jr_form group by jr_code) group by id;
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

这里注意一下，我在Navicat中执行时如果两个语句分开执行会导致TRACE中内容为

需要选中两条sql语句一块执行才可以

trace结果为

{"steps": [{"join_preparation": {"select#": 1,"steps": [{"join_preparation": {"select#": 2,"steps": [{"expanded_query": "/* select#2 */ select max(`jr_form`.`id`) from `jr_form` group by `jr_form`.`jr_code`"},{"transformation": {"select#": 2,"from": "IN (SELECT)","to": "semijoin","chosen": false}},{"transformation": {"select#": 2,"from": "IN (SELECT)","to": "EXISTS (CORRELATED SELECT)","chosen": true,"evaluating_constant_having_conditions": []}}]}},{"expanded_query": "/* select#1 */ select `jr_form`.`id` AS `id`,sum(`jr_form`.`cost`) AS `sum(cost)` from `jr_form` where <in_optimizer>(`jr_form`.`id`,<exists>(/* select#2 */ select max(`jr_form`.`id`) from `jr_form` group by `jr_form`.`jr_code` having (<cache>(`jr_form`.`id`) = <ref_null_helper>(max(`jr_form`.`id`))))) group by `jr_form`.`id`"}]}},{"join_optimization": {"select#": 1,"steps": [{"condition_processing": {"condition": "WHERE","original_condition": "<in_optimizer>(`jr_form`.`id`,<exists>(/* select#2 */ select max(`jr_form`.`id`) from `jr_form` group by `jr_form`.`jr_code` having (<cache>(`jr_form`.`id`) = <ref_null_helper>(max(`jr_form`.`id`)))))","steps": [{"transformation": "equality_propagation","subselect_evaluation": [],"resulting_condition": "<in_optimizer>(`jr_form`.`id`,<exists>(/* select#2 */ select max(`jr_form`.`id`) from `jr_form` group by `jr_form`.`jr_code` having (<cache>(`jr_form`.`id`) = <ref_null_helper>(max(`jr_form`.`id`)))))"},{"transformation": "constant_propagation","subselect_evaluation": [],"resulting_condition": "<in_optimizer>(`jr_form`.`id`,<exists>(/* select#2 */ select max(`jr_form`.`id`) from `jr_form` group by `jr_form`.`jr_code` having (<cache>(`jr_form`.`id`) = <ref_null_helper>(max(`jr_form`.`id`)))))"},{"transformation": "trivial_condition_removal","subselect_evaluation": [],"resulting_condition": "<in_optimizer>(`jr_form`.`id`,<exists>(/* select#2 */ select max(`jr_form`.`id`) from `jr_form` group by `jr_form`.`jr_code` having (<cache>(`jr_form`.`id`) = <ref_null_helper>(max(`jr_form`.`id`)))))"}]}},{"substitute_generated_columns": {}},{"table_dependencies": [{"table": "`jr_form`","row_may_be_null": false,"map_bit": 0,"depends_on_map_bits": []}]},{"ref_optimizer_key_uses": []},{"rows_estimation": [{"table": "`jr_form`","const_keys_added": {"keys": ["PRIMARY","jr_code"],"cause": "group_by"},"range_analysis": {"table_scan": {"rows": 184,"cost": 44.9},"potential_range_indexes": [{"index": "PRIMARY","usable": true,"key_parts": ["id"]},{"index": "jr_code","usable": true,"key_parts": ["jr_code","id"]}],"setup_range_conditions": [],"group_index_range": {"chosen": false,"cause": "not_applicable_aggregate_function"}}}]},{"considered_execution_plans": [{"plan_prefix": [],"table": "`jr_form`","best_access_path": {"considered_access_paths": [{"rows_to_scan": 184,"access_type": "scan","resulting_rows": 184,"cost": 42.8,"chosen": true,"use_tmp_table": true}]},"condition_filtering_pct": 100,"rows_for_plan": 184,"cost_for_plan": 42.8,"sort_cost": 184,"new_cost_for_plan": 226.8,"chosen": true}]},{"attaching_conditions_to_tables": {"original_condition": "<in_optimizer>(`jr_form`.`id`,<exists>(/* select#2 */ select max(`jr_form`.`id`) from `jr_form` group by `jr_form`.`jr_code` having (<cache>(`jr_form`.`id`) = <ref_null_helper>(max(`jr_form`.`id`)))))","attached_conditions_computation": [],"attached_conditions_summary": [{"table": "`jr_form`","attached": "<in_optimizer>(`jr_form`.`id`,<exists>(/* select#2 */ select max(`jr_form`.`id`) from `jr_form` group by `jr_form`.`jr_code` having (<cache>(`jr_form`.`id`) = <ref_null_helper>(max(`jr_form`.`id`)))))"}]}},{"clause_processing": {"clause": "GROUP BY","original_clause": "`jr_form`.`id`","items": [{"item": "`jr_form`.`id`"}],"resulting_clause_is_simple": true,"resulting_clause": "`jr_form`.`id`"}},{"reconsidering_access_paths_for_index_ordering": {"clause": "GROUP BY","index_order_summary": {"table": "`jr_form`","index_provides_order": true,"order_direction": "asc","index": "PRIMARY","plan_changed": true,"access_type": "index"}}},{"refine_plan": [{"table": "`jr_form`"}]}]}},{"join_optimization": {"select#": 2,"steps": [{"condition_processing": {"condition": "HAVING","original_condition": "(<cache>(`jr_form`.`id`) = <ref_null_helper>(max(`jr_form`.`id`)))","steps": [{"transformation": "constant_propagation","resulting_condition": "(<cache>(`jr_form`.`id`) = <ref_null_helper>(max(`jr_form`.`id`)))"},{"transformation": "trivial_condition_removal","resulting_condition": "(<cache>(`jr_form`.`id`) = <ref_null_helper>(max(`jr_form`.`id`)))"}]}},{"substitute_generated_columns": {}},{"table_dependencies": [{"table": "`jr_form`","row_may_be_null": false,"map_bit": 0,"depends_on_map_bits": []}]},{"rows_estimation": [{"table": "`jr_form`","const_keys_added": {"keys": ["jr_code"],"cause": "group_by"},"range_analysis": {"table_scan": {"rows": 184,"cost": 44.9},"potential_range_indexes": [{"index": "PRIMARY","usable": false,"cause": "not_applicable"},{"index": "jr_code","usable": true,"key_parts": ["jr_code","id"]}],"best_covering_index_scan": {"index": "jr_code","cost": 41.319,"chosen": true},"group_index_range": {"potential_group_range_indexes": [{"index": "jr_code","covering": true,"rows": 152,"cost": 64.8}]},"best_group_range_summary": {"type": "index_group","index": "jr_code","group_attribute": "id","min_aggregate": false,"max_aggregate": true,"distinct_aggregate": false,"rows": 152,"cost": 64.8,"key_parts_used_for_access": ["jr_code"],"ranges": [],"chosen": false,"cause": "cost"}}}]},{"considered_execution_plans": [{"plan_prefix": [],"table": "`jr_form`","best_access_path": {"considered_access_paths": [{"rows_to_scan": 184,"access_type": "scan","resulting_rows": 184,"cost": 42.8,"chosen": true,"use_tmp_table": true}]},"condition_filtering_pct": 100,"rows_for_plan": 184,"cost_for_plan": 42.8,"sort_cost": 184,"new_cost_for_plan": 226.8,"chosen": true}]},{"transformation": {"select#": 2,"from": "IN (SELECT)","to": "materialization","has_nullable_expressions": true,"treat_UNKNOWN_as_FALSE": true,"possible": true}},{"execution_plan_for_potential_materialization": {"surely_same_plan_as_EXISTS": true,"cause": "EXISTS_did_not_change_WHERE","subq_mat_decision": {"parent_fanouts": [{"select#": 1,"subq_attached_to_table": true,"table": "`jr_form`","fanout": 184,"cacheable": true}],"cost_to_create_and_fill_materialized_table": 265.6,"cost_of_one_EXISTS": 226.8,"number_of_subquery_evaluations": 184,"cost_of_materialization": 302.4,"cost_of_EXISTS": 41731,"chosen": true}}},{"transformation": {"select#": 2,"from": "IN (SELECT)","to": "materialization","chosen": true,"unknown_key_1": {"creating_tmp_table": {"tmp_table_info": {"row_length": 9,"key_length": 9,"unique_constraint": false,"location": "memory (heap)","row_limit_estimate": 1864135}}}}},{"attaching_conditions_to_tables": {"original_condition": null,"attached_conditions_computation": [],"attached_conditions_summary": [{"table": "`jr_form`","attached": null}]}},{"clause_processing": {"clause": "GROUP BY","original_clause": "`jr_form`.`jr_code`","items": [{"item": "`jr_form`.`jr_code`"}],"resulting_clause_is_simple": true,"resulting_clause": "`jr_form`.`jr_code`"}},{"reconsidering_access_paths_for_index_ordering": {"clause": "GROUP BY","index_order_summary": {"table": "`jr_form`","index_provides_order": true,"order_direction": "asc","index": "jr_code","plan_changed": false}}},{"refine_plan": [{"table": "`jr_form`"}]}]}},{"join_explain": {"select#": 1,"steps": [{"join_explain": {"select#": 2,"steps": []}}]}}]
}