MySQL追踪优化器小试
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL追踪优化器小试，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5192字，纯文字阅读大概需要8分钟。
内容图文
查询优化器的信息：SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
关闭：SET optimizer_trace="enabled=off";
默认情况下是关闭的，要使用的时候一定要打开这个优化器。
看一下参数：
![MySQL追踪优化器小试 - 文章图片]()
enabled：打开或者关闭跟踪器
one_line：如果ON的话将会以JOSN的存储方式保存跟踪，但是阅读的话就是比较费劲的，除了能节省空间没啥好处，不过还是建议使用这个方式。
看一下优化器的相关参数，也可以使用mysqld --verbose --help查看：
--optimizer-trace=name 控制优化的跟踪
--optimizer-trace-features=name Enables/disables tracing of selected features of the Optimizer: optimizer_trace_features=option=val[,option=val...], where option is one of {greedy_search, range_optimizer, dynamic_range, repeated_subselect} and val is one of {on, off, default}
--optimizer-trace-limit=# 显示优化追踪器的最大数量
--optimizer-trace-max-mem-size=# 存储优化的痕迹允许的最大尺寸累积
--optimizer-trace-offset=# Offset of first optimizer trace to show; see manual --end-markers-in-json=#
In JSON output ("EXPLAIN FORMAT=JSON" and optimizer trace), if set to 1, repeats the structure‘s key (if it has one) near the closing bracke
而且这个是和前面所说的information_schema.OPTIMIZER_TRACE表是相对应的
SET GLOBAL optimizer_trace="one_line=on";打开追踪优化器
然后做一条操作就能看到具体的追踪优化器的信息了，通过以下的语句进行查询：
select * from information_schema.OPTIMIZER_TRACE\G;
看一下这张表的结构：
![MySQL追踪优化器小试 - 文章图片]()
看一下字段信息：
QUERY ：查询语句
TRACE ：跟踪信息，是以JSON形式存储的
MISSING_BYTES_BEYOND_MAX_MEM_SIZE：
INSUFFICIENT_PRIVILEGES
追踪优化器可以跟踪很多信息，SELECT insert，replace（其值或选择） ; UPDATE / DELETE和multi-table variants;所有EXPLAIN前缀以前的; SET（除非它操纵optimizer_trace系统变量） ;做; DECLARE / CASE / IF / RETURN（存储程序语言元素） ;call。如果这些语句之一被制备并在分开的步骤中执行，preparation 和execution单独跟踪
一般情况下，一个新的跟踪都会覆盖掉以前的跟踪，特别是对于执行的语句，只能在最新的跟踪器中生成，老的跟踪器是不会产生的，这就是覆盖原则。所以我们需要对追踪优化器进行净化。看一下下面的语句：
SET optimizer_trace_offset=<OFFSET>, optimizer_trace_limit=<LIMIT>
通过上面的语句进行净化。optimizer_trace_offset和optimizer_trace_limit的默认值分别为-1和1.这个参数设置指的是什么呢：
1：当用户退出后所有的跟踪信息都会被清除
2：如果OFFSET 是大于0的相同的查询会返回到第一次查询的记录信息，OFFSET 是小于0的话这条记录就会被记录下来。
例如我们将设置以下信息的话：
OFFSET=-1 and LIMIT=1 最后一次查询信息将会被记录下来
OFFSET=-2 and LIMIT=1将会记录下一个到最后的查询信息
OFFSET=-5 and LIMIT=5 将会记录最后五次查询信息
OFFSET=0 and LIMIT=5 只会记录五次信息
OFFSET≥0的时候，内存中只会记录LIMIT调跟踪信息
SET optimizer_trace_offset=-5, optimizer_trace_limit=5;
多个语句执行N次N大于5在进行查询：
SELECT * FROM information_schema.OPTIMIZER_TRACE；
![MySQL追踪优化器小试 - 文章图片]()
可以看到只是记录了最后的五条信息而已。
官网给出了建议的查询语句：SELECT * FROM OPTIMIZER_TRACE LIMIT LIMIT OFFSET OFFSET ;
而且我们还可以通过以下语句监控到使用的内存到底是多少：
show variables like ‘optimizer_trace_max_mem_size‘;
![MySQL追踪优化器小试 - 文章图片]()
而且OPTIMIZER_TRACE表的MISSING_BYTES_BEYOND_MAX_MEM_SIZE这个列还会记录到当前语句丢失了多少内存，而我们通过show variables like ‘optimizer_trace_max_mem_size‘;查找的内存使用信息往往是和真是的偏小一点，真实内存使用往往要高。
OPTIMIZER_TRACE表的INSUFFICIENT_PRIVILEGES是用来查看权限的，当一些复杂的语句，但是查看跟踪的用户缺少权限的时候，这列值往往将显示为1。这就表示其实是没有权限的。
而且要记住一点就是跟踪器的事件如果被记录下来的话，那么也是会被自动记录到 --debug file当中的.
我们知道跟踪器的话是以JOSN存储trace的，但是JOSN的格式是很难阅读的，所以说MySQL有了以下的参数：
end_markers_in_json这个变量：
root@localhost [information_schema]>show variables like ‘end_markers_in_json‘;
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| end_markers_in_json | OFF |
+---------------------+-------+
1 row in set (0.01 sec)
下面设置以下setting @@end_markers_in_json=on;打开参数以后再去查询的话就会方便的多（这个参数是5.7特有的）：
![MySQL追踪优化器小试 - 文章图片]()
或者我们使用EXPLAIN FORMAT=JSON也可以达到相同的结果。
那么问题来了，优化跟踪器有哪些特点呢，跟踪优化器可以避免多次跟踪同一个语句，这样就不会造成跟踪文件的疯狂增长。跟踪优化器主要有以下的特点：
懒查询：这样的话一个JOIN语句有N个表，最多只会产生N阶乘个执行计划
动态范围跟踪：只输出检查范围内的记录，范围内记录只会重新运行一次，但是没有运行过的记录就会重新生成一个计划。
子查询：每个字查询只会运行一次
这些功能我们是可以自己设置的：>show variables like ‘optimizer_trace_features‘;
![MySQL追踪优化器小试 - 文章图片]()
我们可以看一下跟踪的记录信息，可能会有点乱：
![MySQL追踪优化器小试 - 文章图片]()
join-preparation 准备
join-optimization优化对象
join-execution最后执行
以及调用的范围内优化，成本评估，为什么选择在另一个的访问路径，为什么还是选择了另外一个排序方法，显示过的原因。距离显示一切都在发生优化远，但我们计划显示未来更多的信息。由于截图很乱，不知道你们看得到不，体验眼力劲的时候出现了。当然也可以通过前面的方式修改一下格式看一下可能更加的乐观。
我们最好将跟踪记录并且导出到文件，这样看起来更加好看
SELECT TRACE FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE into outfile‘/var/lib/mysql-files/dump.sql‘;
如果想要查看所有人的所有查询记录跟踪信息，一定要记得打开以下的参数：
--maximum-optimizer-trace-max-mem-size=0 --optimizer-trace-max-mem-size=0
MySQL追踪优化器小试
标签：
本文系统来源：http://www.cnblogs.com/shengdimaya/p/5859975.html
内容总结
以上是互联网集市为您收集整理的MySQL追踪优化器小试全部内容，希望文章能够帮你解决MySQL追踪优化器小试所遇到的程序开发问题。
如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
来源：【匿名】