本篇内容主要讲解“mysql优化器追踪分析”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“mysql优化器追踪分析”吧!

以下leftjoin语句，d表与s表关联，当where条件在d.deptid上时，s表无法走索引。因此通过开启trace方式做一些追踪。root@(none)09:20:20>explainSELECT*FROMSSS.DEPARTMENTdLEFTJOINppp.shopsONd.DEPTID=s.DEPTIDWHEREd.DEPTID='00001111';+----+-------------+-------+------------+-------+----------------------------+---------+---------+-------+--------+----------+-------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-------+------------+-------+----------------------------+---------+---------+-------+--------+----------+-------------+|1|SIMPLE|d|NULL|const|PRIMARY,INDEX_DEPARTMENT_5|PRIMARY|130|const|1|100.00|NULL||1|SIMPLE|s|NULL|ALL|NULL|NULL|NULL|NULL|978629|100.00|Usingwhere|+----+-------------+-------+------------+-------+----------------------------+---------+---------+-------+--------+----------+-------------+

开启optimizer_trace：

set optimizer_trace='enabled=on';

set optimizer_trace_max_mem_size=1000000;

set end_markers_in_json=on;

执行语句

select * from information_schema.optimizer_trace\G;

root@(none)09:39:58>select*frominformation_schema.optimizer_trace\G;***************************1.row***************************QUERY:SELECT*FROMSSS.DEPARTMENTdLEFTJOINppp.shopsONd.DEPTID=s.DEPTIDWHEREd.DEPTID='00001111'TRACE:{"steps":[#准备阶段{"join_preparation":{"select#":1,"steps":[{#expanded_query，解析查询语句，"*"转换成字段，leftjoinon处转化成on((`SSS`.`d`.`Deptid`=convert(`ppp`.`s`.`Deptid`usingutf8mb4))))"expanded_query":"/*select#1*/select`SSS`.`d`.`Organid`AS`Organid`,。。。`s`.`Status`AS`Status`,`ppp`.`s`.`Stylecategoryid`AS`Stylecategoryid`,`ppp`.`s`.`Turnontime`AS`Turnontime`from(`SSS`.`department``d`leftjoin`ppp`.`shop``s`on((`SSS`.`d`.`Deptid`=convert(`ppp`.`s`.`Deptid`usingutf8mb4))))where(`SSS`.`d`.`Deptid`='00001111')"},{#转化成的nestedjoin语句："transformations_to_nested_joins":{"transformations":["parenthesis_removal"]/*transformations*/,"expanded_query":"/*select#1*/select`SSS`.`d`.`Organid`。。。`SSS`.`d`.`Guidecode`AS`Guidecode`,`SSS`.`d`.`Createdate`AS`Createdate`,`SSS`.`d`.`Plateformuser`AS`Plateformuser`,`SSS`.`d`.`Plateformdept`AS`Plateformdept`,`SSS`.`d`.`Agentuser`AS`Agentuser`,`SSS`.`d`.`Agentdept`AS`Agentdept`,`SSS`.`d`.`Shopstatus`AS`Shopstatus`,`SSS`.`d`.`Deptshortname`AS`Deptshortname`,`SSS`.`d`.`Storetype`AS`Storetype`,`SSS`.`d`.`Depttype`AS`Depttype`,`ppp`.`s`.`Shopid`AS`Shopid`,`ppp`.`s`.`Objectid`AS`Objectid`,`ppp`.`s`.`Shopname`AS`Shopname`,`ppp`Tel`,`ppp`.`s`.`Introduce`AS`Introduce`,`ppp`.`s`.`Industry`AS`Industry`,`ppp`.`s`.`Address`AS`Address`,`ppp`.`s`.`Shop360image`AS`Shop360image`,`ppp`.`s`.`Domain`AS`Domain`,`ppp`.`s`.`Organid`AS`Organid`,`ppp`.`s`.`Deptid`AS`Deptid`,`ppp`.`s`.`Brandids`AS`Brandids`,`ppp`.`s`.`Extdata`AS`Extdata`,`ppp`.`s`.`Ranking`AS`Ranking`,`ppp`.`s`.`Isdelete`AS`Isdelete`,`ppp`.`s`.`District`AS`District`,`ppp`.`s`.`City`AS`City`,`ppp`.`s`.`Province`AS`Province`,`ppp`.`s`.`Phone`AS`Phone`,`ppp`.`s`.`Watermarkimage`AS`Watermarkimage`,`ppp`.`s`.`Drawingimage`AS`Drawingimage`,`ppp`.`s`.`Contactuser`AS`Contactuser`,`ppp`.`s`.`Panoloadingimage`AS`Panoloadingimage`,`ppp`.`s`.`Lngandlat`AS`Lngandlat`,`ppp`.`s`.`Createtime`AS`Createtime`,`ppp`.`s`.`Shoptype`AS`Shoptype`,`ppp`.`s`.`Status`AS`Status`,`ppp`.`s`.`Stylecategoryid`AS`Stylecategoryid`,`ppp`.`s`.`Turnontime`AS`Turnontime`from`SSS`.`department``d`leftjoin`ppp`.`shop``s`on((`SSS`.`d`.`Deptid`=convert(`ppp`.`s`.`Deptid`usingutf8mb4)))where(`SSS`.`d`.`Deptid`='00001111')"}/*transformations_to_nested_joins*/}]/*steps*/}/*join_preparation*/},#准备阶段结束{#优化阶段："join_optimization":{"select#":1,"steps":[{#处理where条件部分，化简条件："condition_processing":{"condition":"WHERE","original_condition":"(`SSS`.`d`.`Deptid`='00001111')",---原始条件"steps":[{"transformation":"equality_propagation",----等式处理"resulting_condition":"(`SSS`.`d`.`Deptid`='00001111')"},{"transformation":"constant_propagation",-----常量处理"resulting_condition":"(`SSS`.`d`.`Deptid`='00001111')"},{"transformation":"trivial_condition_removal",----去除多余无关的条件处理"resulting_condition":"(`SSS`.`d`.`Deptid`='00001111')"}]/*steps*/}/*condition_processing*/},#结束，因为这里已经够简化了，所以三次处理后都是同样的。{#替代产生的字段"substitute_generated_columns":{}/*substitute_generated_columns*/},{#表依赖关系检查"table_dependencies":[{"table":"`SSS`.`department``d`",------表d"row_may_be_null":false,"map_bit":0,"depends_on_map_bits":[]/*depends_on_map_bits*/},{"table":"`ppp`.`shop``s`",--------表s"row_may_be_null":true,"map_bit":1,"depends_on_map_bits":[0]/*depends_on_map_bits*/}]/*table_dependencies*/},#表依赖关系检查结束{#找出可使用索引的字段："ref_optimizer_key_uses":[{"table":"`SSS`.`department``d`","field":"Deptid",----------可用的是Deptid"equals":"'00001111'","null_rejecting":false---},{"table":"`SSS`.`department``d`","field":"Deptid","equals":"'00001111'","null_rejecting":false}]/*ref_optimizer_key_uses*/},{#评估每个表单表访问行数及相应代价。"rows_estimation":[{"table":"`SSS`.`department``d`","rows":1,---返回1行"cost":1,---代价为1"table_type":"const",---d表使用的方式是const，即根据主键索引获取"empty":false},{"table":"`ppp`.`shop``s`","table_scan":{-------s表直接使用全表扫描"rows":978662,------扫描978662行"cost":8109------代价为8109}/*table_scan*/}]/*rows_estimation*/},{#评估执行计划，这里考虑两表连接"considered_execution_plans":[{"plan_prefix":[------------------执行计划的前缀，这里是d表，因为是leftjoin我认为指的应该是驱动表的意思"`SSS`.`department``d`"]/*plan_prefix*/,"table":"`ppp`.`shop``s`","best_access_path":{-------最优访问路径"considered_access_paths":[考虑的访问路径{"rows_to_scan":978662,---扫描978662行"access_type":"scan",--------全表扫描的方式"resulting_rows":978662,"cost":203841,----------使用代价"chosen":true-------选中}]/*considered_access_paths*/}/*best_access_path*/,"condition_filtering_pct":100,条件过滤率100%，指的是这里与上一个表进行行过滤的行数"rows_for_plan":978662,------执行计划的扫描行数978662"cost_for_plan":203841,-------执行计划的cost203841"chosen":true---------选中}]/*considered_execution_plans*/},{#检查带常量表的条件"condition_on_constant_tables":"('00001111'='00001111')","condition_value":true},{#将常量条件作用到表，这里主要是将d表的中的deptid条件作用到s表的deptid"attaching_conditions_to_tables":{"original_condition":"('00001111'='00001111')","attached_conditions_computation":[]/*attached_conditions_computation*/,"attached_conditions_summary":[{"table":"`ppp`.`shop``s`","attached":"<if>(is_not_null_compl(s),('00001111'=convert(`ppp`.`s`.`Deptid`usingutf8mb4)),true)"}]/*attached_conditions_summary*/}/*attaching_conditions_to_tables*/},{"refine_plan":[{"table":"`ppp`.`shop``s`"}]/*refine_plan*/}]/*steps*/}/*join_optimization*/},{"join_execution":{"select#":1,"steps":[]/*steps*/}/*join_execution*/}]/*steps*/}MISSING_BYTES_BEYOND_MAX_MEM_SIZE:0INSUFFICIENT_PRIVILEGES:01rowinset(0.00sec)

以上优化器的主要步骤：

1.join_preparation ：准备阶段，包查询语句转换，转换成嵌套循环语句等

expanded_query

transformations_to_nested_joins

2.join_optimization ：优化阶段，包括以下主要阶段

condition_processing ：处理where条件部分，主要包括等式处理、常量处理、多余条件处理

table_dependencies ：表依赖检查

ref_optimizer_key_uses ：评估可用的索引

rows_estimation ：评估访问单表的方式，及扫描的行数与代价

considered_execution_plans ：评估最终可使用的执行计划

condition_on_constant_tables :检查带常量表的条件

attaching_conditions_to_tables ：将常量条件作用到表

refine_plan 改进计划，不理解

3.join_execution ：执行阶段

通过以上可以看错，当优化器一开始对优化器进行评估时就直接选择了全表扫描的方式，即是说此时优化器直接忽视了s表已有的索引IND_SHOP_DEPTID。

我们将以下的d.DEPTID = '00001111' 换成s.DEPTID = '00001111',发现其可以选择了索引，此时s表看起来做了驱动表。

SELECT*FROMSSS.DEPARTMENTdLEFTJOINppp.shopsONd.DEPTID=s.DEPTIDWHEREs.DEPTID='00001111';root@SSS04:28:39>explainSELECT*FROMSSS.DEPARTMENTdLEFTJOINppp.shopsONd.DEPTID=s.DEPTIDWHEREs.DEPTID='00001111';+----+-------------+-------+------------+--------+----------------------------+-----------------+---------+-------+------+----------+-------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-------+------------+--------+----------------------------+-----------------+---------+-------+------+----------+-------------+|1|SIMPLE|s|NULL|ref|IND_SHOP_DEPTID|IND_SHOP_DEPTID|99|const|1|100.00|NULL||1|SIMPLE|d|NULL|eq_ref|PRIMARY,INDEX_DEPARTMENT_5|PRIMARY|130|func|1|100.00|Usingwhere|+----+-------------+-------+------------+--------+----------------------------+-----------------+---------+-------+------+----------+-------------+2rowsinset,1warning(0.00sec)

追踪优化器过程：

1.在ref_optimizer_key_uses 过程找到s表可以通过"'00001111'"走索引，并且通过"Deptid"等值访问

2.在rows_estimation过程中s表选择IND_SHOP_DEPTID的cost最低。

3.在considered_execution_plans过程选择IND_SHOP_DEPTID的访问路径，并访问方式是ref。

{"ref_optimizer_key_uses":[{"table":"`SSS`.`department``d`","field":"Deptid","equals":"convert(`ppp`.`s`.`Deptid`usingutf8mb4)","null_rejecting":false},{"table":"`SSS`.`department``d`","field":"Deptid","equals":"convert(`ppp`.`s`.`Deptid`usingutf8mb4)","null_rejecting":false},{"table":"`ppp`.`shop``s`","field":"Deptid","equals":"'00001111'","null_rejecting":false}]/*ref_optimizer_key_uses*/},{"rows_estimation":[{"table":"`SSS`.`department``d`","table_scan":{"rows":911858,"cost":7212}/*table_scan*/},{"table":"`ppp`.`shop``s`","range_analysis":{"table_scan":{"rows":959814,"cost":200074}/*table_scan*/,"potential_range_indexes":[{"index":"PRIMARY","usable":false,"cause":"not_applicable"},{"index":"IND_SHOP_DEPTID","usable":true,"key_parts":["Deptid","Shopid"]/*key_parts*/},{"index":"IND_SHOP_DOMAIN","usable":false,"cause":"not_applicable"}]/*potential_range_indexes*/,"setup_range_conditions":[]/*setup_range_conditions*/,"group_index_range":{"chosen":false,"cause":"not_single_table"}/*group_index_range*/,"analyzing_range_alternatives":{"range_scan_alternatives":[{"index":"IND_SHOP_DEPTID","ranges":["00001111<=Deptid<=00001111"]/*ranges*/,"index_dives_for_eq_ranges":true,"rowid_ordered":true,"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":"IND_SHOP_DEPTID","rows":1,"ranges":["00001111<=Deptid<=00001111"]/*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":"`ppp`.`shop``s`","best_access_path":{"considered_access_paths":[{"access_type":"ref","index":"IND_SHOP_DEPTID","rows":1,"cost":1.2,"chosen":true},{"access_type":"range","range_details":{"used_index":"IND_SHOP_DEPTID"}/*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,"rest_of_plan":[{"plan_prefix":["`ppp`.`shop``s`"]/*plan_prefix*/,"table":"`SSS`.`department``d`","best_access_path":{"considered_access_paths":[{"access_type":"eq_ref","index":"PRIMARY","rows":1,"cost":1.2,"chosen":true,"cause":"clustered_pk_chosen_by_heuristics"},{"access_type":"scan","cost":189584,"rows":911858,"chosen":false,"cause":"cost"}]/*considered_access_paths*/}/*best_access_path*/,"added_to_eq_ref_extension":true,"condition_filtering_pct":100,"rows_for_plan":1,"cost_for_plan":2.4,"chosen":true}]/*rest_of_plan*/}]/*considered_execution_plans*/

到此，相信大家对“mysql优化器追踪分析”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！