网站开发赚钱吗,招工做哪个网站,怎样制作公众号平台,网站建设需要技术Mysql之 optimizer_trace 相关总结
MySQL官网介绍#xff1a;https://dev.mysql.com/doc/dev/mysql-server/latest/PAGE_OPT_TRACE.html 1. 简介
MySQL优化器可以生成Explain执行计划#xff0c;通过执行计划查看sql是否使用了索引#xff0c;使用了哪种索#xff1b; 但…Mysql之 optimizer_trace 相关总结
MySQL官网介绍https://dev.mysql.com/doc/dev/mysql-server/latest/PAGE_OPT_TRACE.html 1. 简介
MySQL优化器可以生成Explain执行计划通过执行计划查看sql是否使用了索引使用了哪种索 但是有些时候你会发现为什么没想按照我们所想的思路执行 为什么会使用这个索引 为什么没有使用添加的索引
于是MySQL5.6版本之后开始引入 optimizer trace优化器追踪它可以查看优化器生成执行计划的整个过程以及做出的各种决策包括访问表的方法、各种开销计算、各种转换等等帮助我们更好的去优化sql。
另外optimizer_trace的开关默认是关闭的 开启trace工具会影响mysql性能所以只适合临时分析sql使用用完之后最好及时关闭。 2. 使用方法
1. 查看optimizer trace配置
show variables like %optimizer_trace%;查询结果 查询结果字段说明
optimizer_trace 主配置enabled的on表示开启off表示关闭one_line表示是否展示成一行optimizer_trace_features 表示优化器的可选特性包括贪心搜索、范围优化等optimizer_trace_limit 表示优化器追踪最大显示数目默认是1条optimizer_trace_max_mem_size 表示优化器追踪占用的最大容量optimizer_trace_offset 表示显示的第一个优化器追踪的偏移量 2. 开启/关闭 optimizer trace
#开启trace
set session optimizer_traceenabledon,end_markers_in_jsonon;
#关闭trace
set session optimizer_traceenabledoff;3. 执行需要进行分析的SQL语句
select * from test0816 where name a order by remark;4. 使用optimizer trace查看优化器的选择过程
SELECT * FROM information_schema.OPTIMIZER_TRACE;查询结果 查询结果对应字段说明
QUERY 表示我们执行的查询语句TRACE 优化器生成执行计划的过程重点关注MISSING_BYTES_BEYOND_MAX_MEM_SIZE 优化过程其余的信息会被显示在这一列INSUFFICIENT_PRIVILEGES 表示是否有权限查看优化过程0是1否 5. 分析
trace的内容
{steps: [{join_preparation: {select#: 1,steps: [{expanded_query: /* select#1 */ select test0816.id AS id,test0816.name AS name,test0816.age AS age,test0816.remark AS remark,test0816.create_time AS create_time from test0816 where (test0816.name a) order by test0816.remark}] /* steps */} /* join_preparation */},{join_optimization: {select#: 1,steps: [{condition_processing: {condition: WHERE,original_condition: (test0816.name a),steps: [{transformation: equality_propagation,resulting_condition: (test0816.name a)},{transformation: constant_propagation,resulting_condition: (test0816.name a)},{transformation: trivial_condition_removal,resulting_condition: (test0816.name a)}] /* steps */} /* condition_processing */},{substitute_generated_columns: {} /* substitute_generated_columns */},{table_dependencies: [{table: test0816,row_may_be_null: false,map_bit: 0,depends_on_map_bits: [] /* depends_on_map_bits */}] /* table_dependencies */},{ref_optimizer_key_uses: [] /* ref_optimizer_key_uses */},{rows_estimation: [{table: test0816,range_analysis: {table_scan: {rows: 3,cost: 2.65} /* table_scan */,potential_range_indexes: [{index: PRIMARY,usable: false,cause: not_applicable},{index: idx_name_age_remark,usable: true,key_parts: [name,age,remark,id] /* key_parts */}] /* potential_range_indexes */,setup_range_conditions: [] /* setup_range_conditions */,group_index_range: {chosen: false,cause: not_group_by_or_distinct} /* group_index_range */,skip_scan_range: {potential_skip_scan_indexes: [{index: idx_name_age_remark,usable: false,cause: query_references_nonkey_column}] /* potential_skip_scan_indexes */} /* skip_scan_range */,analyzing_range_alternatives: {range_scan_alternatives: [{index: idx_name_age_remark,ranges: [a name] /* ranges */,index_dives_for_eq_ranges: true,rowid_ordered: false,using_mrr: false,index_only: false,in_memory: 1,rows: 3,cost: 1.31,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_age_remark,rows: 3,ranges: [a name] /* ranges */} /* range_access_plan */,rows_for_plan: 3,cost_for_plan: 1.31,chosen: true} /* chosen_range_access_summary */} /* range_analysis */}] /* rows_estimation */},{considered_execution_plans: [{plan_prefix: [] /* plan_prefix */,table: test0816,best_access_path: {considered_access_paths: [{rows_to_scan: 3,access_type: range,range_details: {used_index: idx_name_age_remark} /* range_details */,resulting_rows: 3,cost: 1.61,chosen: true,use_tmp_table: true}] /* considered_access_paths */} /* best_access_path */,condition_filtering_pct: 100,rows_for_plan: 3,cost_for_plan: 1.61,sort_cost: 3,new_cost_for_plan: 4.61,chosen: true}] /* considered_execution_plans */},{attaching_conditions_to_tables: {original_condition: (test0816.name a),attached_conditions_computation: [] /* attached_conditions_computation */,attached_conditions_summary: [{table: test0816,attached: (test0816.name a)}] /* attached_conditions_summary */} /* attaching_conditions_to_tables */},{optimizing_distinct_group_by_order_by: {simplifying_order_by: {original_clause: test0816.remark,items: [{item: test0816.remark}] /* items */,resulting_clause_is_simple: true,resulting_clause: test0816.remark} /* simplifying_order_by */} /* optimizing_distinct_group_by_order_by */},{reconsidering_access_paths_for_index_ordering: {clause: ORDER BY,steps: [] /* steps */,index_order_summary: {table: test0816,index_provides_order: false,order_direction: undefined,index: idx_name_age_remark,plan_changed: false} /* index_order_summary */} /* reconsidering_access_paths_for_index_ordering */},{finalizing_table_conditions: [{table: test0816,original_table_condition: (test0816.name a),final_table_condition : (test0816.name a)}] /* finalizing_table_conditions */},{refine_plan: [{table: test0816,pushed_index_condition: (test0816.name a),table_condition_attached: null}] /* refine_plan */},{considering_tmp_tables: [{adding_sort_to_table: test0816} /* filesort */] /* considering_tmp_tables */}] /* steps */} /* join_optimization */},{join_execution: {select#: 1,steps: [{sorting_table: test0816,filesort_information: [{direction: asc,expression: test0816.remark}] /* filesort_information */,filesort_priority_queue_optimization: {usable: false,cause: not applicable (no LIMIT)} /* filesort_priority_queue_optimization */,filesort_execution: [] /* filesort_execution */,filesort_summary: {memory_available: 262144,key_size: 400,row_size: 1091,max_rows_per_buffer: 15,num_rows_estimate: 15,num_rows_found: 3,num_initial_chunks_spilled_to_disk: 0,peak_memory_used: 32800,sort_algorithm: std::sort,sort_mode: fixed_sort_key, packed_additional_fields} /* filesort_summary */}] /* steps */} /* join_execution */}] /* steps */
}一共是3个阶段
join_preparationsql准备阶段sql格式化join_optimization sql分析优化阶段是分析OPTIMIZER TRACE的重点。这段一般都比较长分很多步需要细看join_execution sql执行阶段
其中的相关关键字解析 //TODO
结论全表扫描的成本低于索引扫描所以MySQL最终选择全表扫描。
