mysql优化器追踪分析
本篇内容主要讲解“mysql优化器追踪分析”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“mysql优化器追踪分析”吧!
以下 left join语句,d表与s表关联,当where条件在d.deptid上时,s表无法走索引。因此通过开启trace方式做一些追踪。 root@(none) 09:20:20>explain SELECT * FROM SSS.DEPARTMENT d LEFT JOIN ppp.shop s ON d.DEPTID = s.DEPTID WHERE d.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 | Using where | +----+-------------+-------+------------+-------+----------------------------+---------+---------+-------+--------+----------+-------------+
开启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 * from information_schema.optimizer_trace\G; *************************** 1. row *************************** QUERY: SELECT * FROM SSS.DEPARTMENT d LEFT JOIN ppp.shop s ON d.DEPTID = s.DEPTID WHERE d.DEPTID = '00001111' TRACE: { "steps": [ #准备阶段 { "join_preparation": { "select#": 1, "steps": [ { #expanded_query,解析查询语句,"*" 转换成字段,left join on 处转化成on((`SSS`.`d`.`Deptid` = convert(`ppp`.`s`.`Deptid` using utf8mb4)))) "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` left join `ppp`.`shop` `s` on((`SSS`.`d`.`Deptid` = convert(`ppp`.`s`.`Deptid` using utf8mb4)))) where (`SSS`.`d`.`Deptid` = '00001111')" }, { #转化成的nested join语句: "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` left join `ppp`.`shop` `s` on((`SSS`.`d`.`Deptid` = convert(`ppp`.`s`.`Deptid` using utf8mb4))) 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表,因为是left join 我认为指的应该是驱动表的意思 "`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` using utf8mb4)), 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: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.00 sec)
以上优化器的主要步骤:
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 * FROM SSS.DEPARTMENT d LEFT JOIN ppp.shop s ON d.DEPTID = s.DEPTID WHERE s.DEPTID = '00001111'; root@SSS 04:28:39>explain SELECT * FROM SSS.DEPARTMENT d LEFT JOIN ppp.shop s ON d.DEPTID = s.DEPTID WHERE s.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 | Using where | +----+-------------+-------+------------+--------+----------------------------+-----------------+---------+-------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)
追踪优化器过程:
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` using utf8mb4)", "null_rejecting": false }, { "table": "`SSS`.`department` `d`", "field": "Deptid", "equals": "convert(`ppp`.`s`.`Deptid` using utf8mb4)", "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优化器追踪分析”有了更深的了解,不妨来实际操作一番吧!这里是亿速云网站,更多相关内容可以进入相关频道进行查询,关注我们,继续学习!