慢SQL优化一点小思路
什么是慢SQL？
顾名思义，运行时间较长的SQL即为慢SQL，多久算慢，如何定义？
SQL运行快慢是一个相对的概念，不同的业务场景下要求不同，慢SQL的标准也就不同。
MySQL中long_query_time参数定义了SQL运行阈值，默认为10s，可通过设置该阈值来调整基准。long_query_time 参数对长连接无效。
// session级别设置阈值：
set session long_query_time = 1;
// 全局级别设置阈值
set global long_query_time = 1;
参考：生产环境慢SQL一般设置为0.1~0.2s，但实际上会关注的慢sql一般>1s。
慢SQL是怎么发生的？
sql查询为什么会慢？我们先看一下sql的执行过程：
如上图所示，一条SQL语句执行时，分为以下几步：
- 若查询缓存打开则会优先查询缓存，若命中则直接直接返回结果给客户端。
- 若缓存未命中，此时MySQL需要搞清楚这条语句需要做什么，则通过分析器进行词法分析、语法分析。
- 搞清楚要做什么之后，MySQL需要通过优化器进行优化执行计划。
- 最后通过执行器与存储引擎提供的接口进行交互，将结果返回给客户端。
MySQL执行过程中，优化器通过成本计算预估出执行效率最高的方式，以便执行器以最高效率执行SQL。 基本的成本预估维度为：I/O成本+CPU成本。
- I/O成本：从磁盘读取数据到内存的开销，其成本常数为1.0。
- CPU成本：从磁盘读到数据后放到内存中处理数据的开销，其成本常数为0.2。 怎么计算成本？
- 根据搜索条件，找出所有可能使用的索引，也就是explain的possible_keys。
- 计算全表扫描的开销。
- 计算使用不同索引执行查询的开销。
- 对比各种执行方案的开销，开销最小的那一个。
全表扫描成本计算：I/O成本+CPU成本
- I/O成本 = 页数 * 1.0(成本常数) + 1.1(微调数)
- CPU成本 = 数据行数 * 0.2(成本常数) + 1.0(微调数) 使用索引查询成本计算：I/O成本+CPU成本+回表I/O成本+回表CPU成本
- I/O成本 = 扫描区间 * 1.0(成本常数)
- CPU成本 = 数据行数 * 0.2(成本常数) + 0.01(微调数)
- 回表I/O成本 = 扫描区间 * 1.0(成本常数)
- 回表CPU成本 = 数据行数 * 0.2(成本常数) 注：mysql规定，当读取索引扫描的时候，每当读取一个扫描区间或者范围区间的IO成本，和读取一个页面的IO成本，是一样的，都是1.0。
基于以上两个维度我们可以得到影响SQL执行效率的关键在于I/O开销和CPU开销。哪些因素会影响到这两点呢？大致可总结以下几点：
- I/O成本
- 数据量：数据量越大需要的I/O次数越多。
- 数据从哪读取：从缓存读取还是从磁盘读取；是否通过索引快速查找；
- CPU成本
- 数据处理方式：排序、子查询等，需要先把数据取到临时表中，再对数据进行加工。
怎么找到慢SQL？
影响MySQL各种类型的活动都会被记录在日志文件当中，常见的日志文件包括：
- 错误日志（error log）：记录MySQL启动、运行、关闭时的问题。
- 二进制日志（binlog）：记录对MySQL数据库执行更改的所有操作。
- 慢查询日志（slow query log）：记录运行时间超过long_query_time阈值的所有SQL语句。
- 查询日志（log）：记录了所有对MySQL数据库请求的信息。
我们可以通过慢查询日志来查看慢SQL，默认情况下MySQL数据库不启动慢查询日志，需要手动将参数设置为：ON。
慢日志配置操作：
- 查看当前慢日志配置：
show variables like 'slow_query_log%'如上图所示：
- slow_quer_log：开关为ON，即打开状态。
- slow_query_log_file：慢日志文件写入地址。
- slow_query_log_always_write_time：定义了日志在查询结束后多久开始写。
- 开启慢日志配置：
set global slow_query_log=1;退出后重新连接即可生效。
怎么优化慢SQL？
常见分析思路
explain
MySQL提供了一个explain命令, 它可以对select语句进行分析, 并输出select执行的详细信息。
使用方法
explain select * from user where id > 1;
如上图所示：
| 列名 | 含义 |
|---|---|
| select_type | select子句类型 |
| partitions | 匹配的分区 |
| type | 访问类型，即怎么找数据行的方式(ALL, index, range, ref, eq_ref, const, system, NULL) |
| possible_keys | 能使用的索引 |
| key | 预测使用的索引 |
| key_len | 索引使用的字节数 |
| ref | 连接匹配条件 |
| rows | 估算出所查到的数据行数 |
| filtered | 通过条件过滤出的行数所占百分比估计值，1~100，100表示没有做任何过滤 |
| Extra | 该列包含MySQL解决查询的详细信息 |
通过explain命令，我们能分析出一些慢SQL的常见原因：
- 索引使用问题，通过
possible_keys(能用到的索引)和key(实际用到的索引)两个字段查看：- 没有使用索引
- 优化器选择了错误索引
- 没有实现覆盖索引
- I/O开销问题，通过
rows(执行当前查询要遍历的行数)和filtered(有效行数/扫描行数比值)字段来查看：- 扫描的行数过多
- 返回无用列且无用列有明显I/O性能开销(比如text、blob、json 等类型）
profile
explain只能分析到SQL的预估执行计划，无法分析到SQL实际执行过程中的耗时，可以通过配置profiling参数来进行SQL执行分析。开启参数后，后续执行的SQL语句都会记录其资源开销，包括IO，上下文切换，CPU，Memory等等，我们可以根据这些开销进一步分析当前慢SQL的瓶颈再进一步进行优化。
使用方法
- 查看系统变量，判断该功能是否开启 :
show variables like '%profile%'; - 设置系统变量，开启开关:
set profiling=1; - 执行SQL语句。
- 查看profiles:
show profiles;
- 查看指定id的SQL语句开销详细信息:
show profile for query id;
- 关闭开关:
set profiling=0
我们可以通过查看SQL语句执行时每一个阶段的耗时，也可以通过show profile all for query id;命令查看完整列的执行情况。
| 列名 | 含义 |
|---|---|
| "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
profile只能查看到SQL的执行耗时，但是无法看到SQL真正执行的过程信息。Optimizer Trace是一个跟踪功能，它可以跟踪执行语句的解析优化执行的全过程，可以开启该功能进行执行语句的分析。 诚如上述所说，explain只能判断出SQL预估的执行计划，预估时根据成本模型进行成本计算进而比较出理论最优执行计划，但在实际过程中，预估不代表完全正确，因此我们需要通过追踪来看到它在执行过程中的每一环是否真正准确。
使用方法
- 查看系统变量信息:
show variables like '%optimizer_trace%'; - 打开optimizer trace开关:
set optimizer_trace="enabled=on"; - 执行要SQL语句。
- 查看INFORMATION_SCHEMA.OPTIMIZER_TRACE表中跟踪结果:
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
可查看分析其执行树：
- join_preparation：准备阶段
- join_optimization：分析阶段
- join_execution：执行阶段
- 关闭开关:
set optimizer_trace="enabled=off";