新昌县建设局网站,iis7 网站权限设置,中国空间雷达卫星,达州注册公司优化器 MySQL存储引擎中存在了一个可插拔的优化器OPTIMIZER_TRACE#xff0c;可以看到内部查询计划的TRACE信息#xff0c;从而可以知道MySQL内部执行过程 查询优化器状态 show variables like optimizer_trace;Variable_name Valueoptimizer_trace enabledoff,one_lineoff… 优化器 MySQL存储引擎中存在了一个可插拔的优化器OPTIMIZER_TRACE可以看到内部查询计划的TRACE信息从而可以知道MySQL内部执行过程 查询优化器状态 show variables like optimizer_trace;Variable_name Valueoptimizer_trace enabledoff,one_lineoff 开启优化器 set session optimizer_traceenabledon,one_lineon,end_markers_in_jsonon; 查看优化器追踪内存大小 show variables like optimizer_trace_max_mem_size;Variable_name Valueoptimizer_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中内容为 错误的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: [] } } ] } } ]} 内容很长之后在详细说明 https://zhhll.icu/2021/数据库/关系型数据库/MySQL/进阶/25.优化器/ 本文由 mdnice 多平台发布
