mysql小记-分析SQL执行效率

2020/08/20 MySQL

定位慢 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

Search

    Table of Contents