mysql小记-分析SQL执行效率

定位慢 SQL

慢查询日志

• MySQL 的慢查询日志用来记录在 MySQL 中响应时间超过参数 long_query_time（单位秒，默认值 10）设置的值并且扫描记录数不小于 min_examined_row_limit（默认值 0）的语句
• 默认情况下，也不会记录查询时间不超过 long_query_time 但是不使用索引的语句，可通过配置 log_queries_not_using_indexes = on 让不使用索引的 SQL 都被记录到慢查询日志中
• 开启慢查询日志

set global slow_query_log = on;

• 设置慢查询时间阀值
  • 线上业务一般建议把 long_query_time 设置为 1 秒，如果某个业务的 MySQL 要求比较高的 QPS，可设置慢查询为 0.1 秒
  • 一般测试环境建议 long_query_time 设置的阀值比生产环境的小，比如生产环境是 1 秒，则测试环境建议配置成 0.5 秒
  • 某些重要业务测试环境 long_query_time 可以设置为 0，以便记录所有语句。重点关注 Rows_examined（语句执行期间从存储引擎读取的行数），提前优化

set global long_query_time = 1;

• 获取慢查询日志的路径

show global variables like "datadir";

• 获取慢查询日志的文件名

show global variables like "slow_query_log_file";

通过 show processlist

• show processlist 命令判断正在执行的慢查询

explain 分析慢查询

• 在查询语句前面加上 explain 运行

show profile 分析慢查询

• 通过配置参数 profiling = 1 来启用 SQL 分析,该参数开启后，后续执行的 SQL 语句都将记录其资源开销，如 IO、上下文切换、CPU、Memory等
• 判断当前 MySQL 是否支持 profile

select @@have_profiling;

• 查看 profiling 是否关闭

select @@profiling;

• 开启 profile,set 时没加 global，只对当前 session 有效

set profiling=1;

• 执行 SQL 语句
• show profiles 语句确定执行过的 SQL 的 query id
• 查询 SQL 执行详情

show profile for query 1;

trace 分析 SQL 优化器

• 使用 trace 查看优化器如何选择执行计划
• 开启该功能，会对 MySQL 性能有所影响，因此只建议分析问题时临时开启
• 开启 trace，设置格式为 JSON

set session optimizer_trace="enabled=on",end_markers_in_json=on;

• 执行sql
• 分析trace

SELECT * FROM information_schema.OPTIMIZER_TRACE

相关链接

• 个人博客原文首发