mysql优化器追踪分析
发表于：2025-01-23 作者：千家信息网编辑
千家信息网最后更新 2025年01月23日，本篇内容主要讲解"mysql优化器追踪分析"，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习"mysql优化器追踪分析"吧!以下 left join语句，d表与
千家信息网最后更新 2025年01月23日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": "(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: 01 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优化器追踪分析"有了更深的了解，不妨来实际操作一番吧！这里是网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
条件
处理
索引
阶段
常量
方式
语句
评估
代价
检查
过程
选择
分析
作用
字段
路径
准备
查询
内容
等式
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
剑网三怀旧服有哪些服务器
常德软件开发高职
网络技术与二级办公
军团要塞非安全服务器
软件开发未来规划方案
广州环境监测软件开发服务
国超服务器
etcd数据库满了
我的世界官方服务器管理
网络安全发展规划正式发布
个人信息查询数据库
阿里云服务器教育邮箱注册
服务器事件10149
互联网网络技术专业介绍
数据库查询事务id
大逃杀怎么换服务器
下列不属于网络安全问题
数据库不同表的字段比较
数据库导入数据的功能
关于国庆前做好网络安全通知
浙江互联网络技术咨询
前端网络技术
网络技术三次握手连接过程
最近新出的网络安全法律
遥志vpn服务器
np330打印服务器蓝牙怎么用
网络安全方向课程设计
学网络安全工程师好吗
网络安全文明上网心得体会
徐州微信小程序软件开发
