[MySQL 5.6] 初识5.6的optimizer trace
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了[MySQL 5.6] 初识5.6的optimizer trace，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含8736字，纯文字阅读大概需要13分钟。
内容图文
在MySQL5.6中，支持将执行的SQL的查询计划树记录下来，目前来看，即使对于非常简单的查询，也会打印出冗长的查询计划，看起来似乎不是很可读，不过对于一个经验丰富，对查询计划的生成过程比较了解的DBA而言，这是一个优化SQL的宝藏，因为暴露了大量的内部产生查询计划的信息给用户，这意味着，我们可以对开销较大的部分进行优化。
新参数optimizer_trace可以控制是否为执行的SQL生成查询计划树，默认关闭，我们也建议关闭，因为它会产生额外的性能开销(dimitrik的评测：http://dimitrik.free.fr/blog/archives/2012/01/mysql-performance-overhead-of-optimizer-tracing-in-mysql-56.html)。 我在自己的机器上使用sysbench测试，64个并发,select.lua，纯内存操作，QPS从112,000下降到88,000。 这是session级别的参数，如果需要是，可以在session级别打开，线程只能看到当前会话的查询计划，无法看到其他会话的。 使用也很简单： 打开optimizer_trace
mysql> set session optimizer_trace=’enabled=on‘;
Query OK, 0 rows affected (0.00 sec)
<执行你的SQL> (例如，这里执行select * from sbtest1 order by k limit 3;) 然后查询information_schema.optimizer_trace表，输出如下
| | select * from sbtest1 order by k limit 3 | {
“steps”: [ { “join_preparation”: { “select#”: 1, “steps”: [ { “expanded_query”: “/* select#1 */ select `sbtest1`.`id` AS `id`,`sbtest1`.`k` AS `k`,`sbtest1`.`c` AS `c`, `sbtest1`.`pad` AS `pad` from `sbtest1` order by `sbtest1`.`k` limit 3″ } ] } }, { “join_optimization”: { “select#”: 1, “steps”: [ { “table_dependencies”: [ { “table”: “`sbtest1`”, “row_may_be_null”: false, “map_bit”: 0, “depends_on_map_bits”: [ ] } ] }, { “rows_estimation”: [ { “table”: “`sbtest1`”, “table_scan”: { “rows”: 986400, “cost”: 13741 } } ] }, { “considered_execution_plans”: [ { “plan_prefix”: [ ], “table”: “`sbtest1`”, “best_access_path”: { “considered_access_paths”: [ { “access_type”: “scan”, “rows”: 986400, “cost”: 211021, “chosen”: true } ] }, “cost_for_plan”: 211021, “rows_for_plan”: 986400, “chosen”: true } ] }, { “attaching_conditions_to_tables”: { “original_condition”: null, “attached_conditions_computation”: [ ], “attached_conditions_summary”: [ { “table”: “`sbtest1`”, “attached”: null } ] } }, { “clause_processing”: { “clause”: “ORDER BY”, “original_clause”: “`sbtest1`.`k`”, “items”: [ { “item”: “`sbtest1`.`k`” } ], “resulting_clause_is_simple”: true, “resulting_clause”: “`sbtest1`.`k`” } }, { “refine_plan”: [ { “table”: “`sbtest1`”, “access_type”: “table_scan” } ] }, { “reconsidering_access_paths_for_index_ordering”: { “clause”: “ORDER BY”, “index_order_summary”: { “table”: “`sbtest1`”, “index_provides_order”: true, “order_direction”: “asc”, “index”: “k”, “plan_changed”: true, “access_type”: “index_scan” } } } ] } }, { “join_execution”: { “select#”: 1, “steps”: [ ] } } ] } |
###############################################################
主要分为三个部分 join_preparation：SQL的准备阶段，sql被格式化 对应函数 JOIN::prepare 例如 * 被扩展开来 join_optimization：SQL优化阶段 对应函数JOIN::optimize join_execution：SQL执行阶段 对应函数：JOIN::exec |
optimizer_trace有两个字段：
“enabled=on,one_line=off” ,可以通过set 进行字符串更新，前者表示打开optimizer_trace，后者表示打印的查询计划是否以一行显示，还是以json树的形式显示 我们可以在session级别来设这这个参数。 默认optimizer_trace_limit值为1，因此只会保存一条记录。这个设置需要重连session才能生效，另外一个变量optimizer_trace_offset通常与之配合使用，默认值为-1 例如，offset=-1, limit=1将显示最近一次trace offset=-2,limit=1将显示最近的前一个trace。offset=-5,limit=5 将最近的5次trace打印出来
总的来说： 当offset大于0时，则会显示老的从offset开始的limit个trace，也就是说，新的trace没有记下来。当offset小于0时，则会显示最新的-offset开始的limit个trace，也就是说，只显示新的trace
注意重设变量会导致trace被清空
另外由于trace数据是存储在内存中的，因此还需要设置optimizer_trace_max_mem_size来限制内存的使用量，否则意外的设置可能导致内存爆掉。这是session级别，不应该设置的过大 optimizer_trace_limit和optimizer_trace_offset也影响占用内存大小，但不应该超过OPTIMIZER_TRACE_MAX_MEM_SIZE 另外，还有个参数optimizer_trace_features，可以控制打印到查询计划树的项，默认情况下是全部打开的，如下：mysql> show variables like ‘optimizer_trace_features‘;
+————————–+—————————————————————————-+ | Variable_name | Value | +————————–+—————————————————————————-+ | optimizer_trace_features | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on | +————————–+—————————————————————————-+ 1 row in set (0.00 sec)
如果你不关心某些查询计划选项，可以将其关闭掉，只打印你关注的，这样可以减小查询计划树的输出，让其更可读一点。 greedy_search：对于有N个表的join操作，可能产生N的阶乘的查询计划路径； range_optimizer：range优化 dynamic_range：dynamic range optimizer（也就是”range checked for each row”，每个外部列会执行一次range optimizer）；如果关闭该选项的话，只有第一次调用JOIN_TAB::SQL_SELECT才被跟踪 repeated_subselect：子查询，如果关闭的话，只有第一次调用Item_subselect 才被跟踪 —————————————————- TODO PLAN：增加阈值（读取的行数，或者执行的时间），超过阈值时，自动将trace导入到某个文件中，这样可以便于在线debug 参考： http://dev.mysql.com/doc/internals/en/optimizer-features-to-trace.html http://dev.mysql.com/doc/internals/en/system-variables-controlling-trace.html http://guilhembichot.blogspot.com/2011/09/optimizer-tracing-how-to-configure-it.html
http://jorgenloland.blogspot.com/2011/10/optimizer-tracing-query-execution-plan.html
原创文章，转载请注明： 转载自Simple Life
本文链接地址: [MySQL 5.6] 初识5.6的optimizer trace
文章的脚注信息由WordPress的wp-posturl插件自动生成
[MySQL 5.6] 初识5.6的optimizer trace
标签：
本文系统来源：http://www.cnblogs.com/zengkefu/p/5628125.html
内容总结
以上是互联网集市为您收集整理的[MySQL 5.6] 初识5.6的optimizer trace全部内容，希望文章能够帮你解决[MySQL 5.6] 初识5.6的optimizer trace所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】