慢SQL优化
MySQL中 long_query_time
参数定义了SQL运行阈值,默认为10s。long_query_time
参数对长连接无效。
定位慢SQL
开启慢查询日志来查看慢SQL,默认情况下MySQL数据库不启动慢查询日志,需要手动将参数设置为:ON。
1 | show variables like 'slow_query_log%'; |
开启慢日志配置,退出后重新连接即可生效
1 | set global slow_query_log=1; |
分析预估执行计划
MySQL提供了一个explain命令, 它可以对select
语句进行分析, 并输出select
执行的详细信息。
重点关注以下几个参数:(其它参数的含义见:MySQL中Explain详解)
索引使用问题,通过 possible_keys(能用到的索引)
和 key(实际用到的索引)
两个字段查看:
- 没有使用索引
- 优化器选择了错误索引
- 没有实现覆盖索引
I/O开销问题,通过rows(执行当前查询要遍历的行数)
和filtered(有效行数/扫描行数比值)
字段来查看:
- 扫描的行数过多
- 返回无用列,且无用列有明显I/O性能开销(比如text、blob、json 等类型)
SQL执行耗时
explain只能分析到SQL的预估执行计划,无法分析到SQL实际执行过程中的耗时
可以通过配置profiling参数,查看SQL执行过程中的耗时
查看状态(默认是关闭状态)
开启(用完以后记得set profiling=0
关闭)
1 | set profiling=1; |
执行SQL语句,然后查看SQL语句性能信息,获取正确的 Query_ID
1 | show profiles; |
根据Query_ID查看指定的SQL语句
1 | show profile for query 3; |
通过命令查看完整列的执行情况,包括CPU,IO信息
1 | show profile all for query 3; |
列名 | 含义 |
---|---|
Status | 执行阶段 |
Duration | 持续时间 |
CPU_user | cpu用户 |
CPU_system | cpu系统 |
Context_voluntary | 上下文主动切换 |
Context_involuntary | 上下文被动切换 |
Block_ops_in | 阻塞的输入操作 |
Block_ops_out | 阻塞的输出操作 |
Messages_sent | 消息发出 |
Messages_received | 消息接收 |
Page_faults_major | 主分页错误 |
Page_faults_minor | 次分页错误 |
Swaps | 交换次数 |
Source_function | 源功能 |
Source_file | 源文件 |
Source_line | 源代码行 |
优化器跟踪
优化器跟踪(Optimizer Trace)是MySQL中的一项功能,它提供了有关查询优化器在执行查询期间所采取的决策和操作的详细信息。通过分析优化器跟踪结果,你可以了解MySQL是如何选择执行计划、优化查询并执行查询的。
查看系统变量信息
1 | show variables like '%optimizer_trace%'; |
在会话级别启用优化器跟踪:
1 | SET optimizer_trace = 'enabled=on'; |
执行SQL语句后,使用以下命令来获取优化器跟踪结果:
1 | select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; |
这将返回一个结果集,其中包含了查询的优化器跟踪信息,包括执行计划、扫描和访问方法、索引选择、成本估算等。可以根据这些信息来了解查询优化器在执行期间的决策和操作。
完成了对优化器跟踪的分析,记得使用以下命令将其禁用,以避免不必要的性能开销:
1 | SET optimizer_trace = 'enabled=off'; |