mysql优化器追踪分析
短信预约 -IT技能 免费直播动态提醒
本篇内容主要讲解“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 `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"
] ,
"expanded_query": " 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')"
}
}
]
}
},#准备阶段结束
{
#优化阶段：
"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')"
}
]
}
},#结束，因为这里已经够简化了，所以三次处理后都是同样的。
{
#替代产生的字段
"substitute_generated_columns": {
}
},
{
#表依赖关系检查
"table_dependencies": [
{
"table": "`SSS`.`department` `d`", ------表d
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
},
{
"table": "`ppp`.`shop` `s`", --------表s
"row_may_be_null": true,
"map_bit": 1,
"depends_on_map_bits": [
0
]
}
]
}, #表依赖关系检查结束
{#找出可使用索引的字段：
"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
}
]
},
{#评估每个表单表访问行数及相应代价。
"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
}
}
]
},
{#评估执行计划，这里考虑两表连接
"considered_execution_plans": [
{
"plan_prefix": [------------------执行计划的前缀，这里是d表，因为是left join 我认为指的应该是驱动表的意思
"`SSS`.`department` `d`"
] ,
"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-------选中
}
]
} ,
"condition_filtering_pct": 100,条件过滤率100%，指的是这里与上一个表进行行过滤的行数
"rows_for_plan": 978662,------执行计划的扫描行数978662
"cost_for_plan": 203841,-------执行计划的cost203841
"chosen": true---------选中
}
]
},
{#检查带常量表的条件
"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_summary": [
{
"table": "`ppp`.`shop` `s`",
"attached": "<if>(is_not_null_compl(s), ('00001111' = convert(`ppp`.`s`.`Deptid` using utf8mb4)), true)"
}
]
}
},
{
"refine_plan": [
{
"table": "`ppp`.`shop` `s`"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"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
}
]
},
{
"rows_estimation": [
{
"table": "`SSS`.`department` `d`",
"table_scan": {
"rows": 911858,
"cost": 7212
}
},
{
"table": "`ppp`.`shop` `s`",
"range_analysis": {
"table_scan": {
"rows": 959814,
"cost": 200074
} ,
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "IND_SHOP_DEPTID",
"usable": true,
"key_parts": [
"Deptid",
"Shopid"
]
},
{
"index": "IND_SHOP_DOMAIN",
"usable": false,
"cause": "not_applicable"
}
] ,
"setup_range_conditions": [
] ,
"group_index_range": {
"chosen": false,
"cause": "not_single_table"
} ,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "IND_SHOP_DEPTID",
"ranges": [
"00001111 <= Deptid <= 00001111"
] ,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 1,
"cost": 2.21,
"chosen": true
}
] ,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
} ,
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "IND_SHOP_DEPTID",
"rows": 1,
"ranges": [
"00001111 <= Deptid <= 00001111"
]
} ,
"rows_for_plan": 1,
"cost_for_plan": 2.21,
"chosen": true
}
}
}
]
},
{
"considered_execution_plans": [
{
"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"
} ,
"chosen": false,
"cause": "heuristic_index_cheaper"
}
]
} ,
"condition_filtering_pct": 100,
"rows_for_plan": 1,
"cost_for_plan": 1.2,
"rest_of_plan": [
{
"plan_prefix": [
"`ppp`.`shop` `s`"
] ,
"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"
}
]
} ,
"added_to_eq_ref_extension": true,
"condition_filtering_pct": 100,
"rows_for_plan": 1,
"cost_for_plan": 2.4,
"chosen": true
}
]
}
] 到此，相信大家对“mysql优化器追踪分析”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/34.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
