本文转载自https://www.cnblogs.com/yidengjiagou/p/16594161.html
optimizer trace
optimizer trace 可以帮助我们查看优化器生成执行计划的整个过程，以及做出的各种决策，包括访问表的方法、各种开销计算、各种转换等。
查看optimizer trace配置
show variables like '%optimizer_trace%';
输出参数详解：
optimizer_trace 主配置，enabled的on表示开启，off表示关闭，one_line表示是否展示成一行
optimizer_trace_features 表示优化器的可选特性，包括贪心搜索、范围优化等
optimizer_trace_limit 表示优化器追踪最大显示数目，默认是1条
optimizer_trace_max_mem_size 表示优化器追踪占用的最大容量
optimizer_trace_offset 表示显示的第一个优化器追踪的偏移量
开启optimizer trace
optimizer trace默认是关闭，我们可以使用命令手动开启：SET optimizer_trace="enabled=on";
使用optimizer trace
使用optimizer trace查看优化器的选择过程：SELECT * FROM information_schema.OPTIMIZER_TRACE;
输出结果共有4列：
QUERY 表示我们执行的查询语句
TRACE 优化器生成执行计划的过程（重点关注）
MISSING_BYTES_BEYOND_MAX_MEM_SIZE 优化过程其余的信息会被显示在这一列
INSUFFICIENT_PRIVILEGES 表示是否有权限查看优化过程，0是，1否
输出结果字段含义：
index 索引名称
ranges 查询范围
index_dives_for_eq_ranges 是否用到索引潜水的优化逻辑
rowid_ordered 是否按主键排序
using_mrr 是否使用mrr
index_only 是否使用了覆盖索引
in_memory 使用内存大小
rows 预估扫描行数
cost 预估成本大小，值越小越好
chosen 是否被选择
cause 没有被选择的原因，cost表示成本过高
Profile性能分析工具
Profile性能分析工具可以帮助我们分析SQL性能瓶颈和资源消耗情况。
查看Profile配置
show variables like '%profil%';
have_profiling 表示是否支持profile功能，YES表示支持
profiling 表示是否开启profile功能，ON开启，OFF关闭，默认是关闭状态
profiling_history_size 表示保存最近15条历史数据
开启Profile功能
set profiling=1;
使用Profile
使用show profiles命令查看
输出参数详解：
Query_ID 表示自动分配的查询ID，顺序递增。
Duration 表示SQL语句执行耗时
Query 表示SQL语句内容
然后，我们再使用Query_ID去Profile中查看具体每一步的耗时情况：
show profile for query 1;
可以清楚的看到耗时主要花在创建排序索引（Creating sort index）上面。
再试一条SQL：
select distinct name from user;
这次的耗时主要花在了，创建临时文件、拷贝文件到磁盘、发送数据、删除临时表上面。
由此，可以得知distinct函数会创建临时文件，提醒我们建索引。
我们还可以扩展一下这条分析语句，查看一下cpu和block io的使用情况：
show profile cpu,block io for query 2;
另外，其实所有Profile历史数据都被记录在information_schema.profiling表中，我们也可以查询表得到结果：
select * from information_schema.profiling where Query_ID=2; 
慢查询日志分析工具mysqldumpslow
MySQL 慢查询日志：执行时间超过阈值的SQL会被写入到慢查询日志当中，这样可以帮助我们记录执行时间过长的SQL语句，定位线上慢SQL问题，方便我们进行SQL性能调优。
查看慢查询设置情况：show variables like 'slow_query_log';
开启慢查询日志记录：set global slow_query_log =1;
设置查询阀值：set global long_query_time = 5;
查看查询阀值：show variables like 'long_query_time';
查看是否记录未使用索引的 SQL：show variables like 'log_queries_not_using_indexes';
开启记录未使用索引的 SQL配置：set global log_queries_not_using_indexes = on;
mysqldumpslow分析慢查询日志
语法格式：mysqldumpslow [ OPTS... ] [ LOGS... ]
常用到的格式组合：
-s 表示按照何种方式排序
c 访问次数
l 锁定时间
r 返回记录
t 查询时间
al 平均锁定时间
ar 平均返回记录数
at 平均查询时间
-t 返回前面多少条数据
-g 后边搭配一个正则匹配模式，大小写不敏感
查询返回结果最多的10条SQL：mysqldumpslow -s r -t 10 /usr/local/mysql/data/localhost_slow.log
查询耗时最长的10条SQL：mysqldumpslow -s t -t 10 /usr/local/mysql/data/localhost_slow.log
标签：optimizer,trace,show,mysql,查询,SQL,优化 From： https://www.cnblogs.com/caroline2016/p/17085945.html