百木园一、什么是慢 SQL
什么是慢SQL？顾名思义，运行时间较长的 SQL 语句即为慢 SQL！
那问题来了，多久才算慢呢？
这个慢其实是一个相对值，不同的业务场景下，标准要求是不一样的。
我们都知道，我们每执行一次 SQL，数据库除了会返回执行结果以外，还会返回 SQL 执行耗时，以 MySQL 数据库为例，当我们开启了慢 SQL 监控开关后，默认配置下，当 SQL 的执行时长大于 10 秒，会被记录到慢 SQL 的日志文件中。
当然，这个值还可以重新设置，生产环境慢 SQL 一般会设置为0.1~0.2s。当我们将其设置为0.2s时，当前数据库所有 SQL 的执行时长超过0.2s的都会被视为慢 SQL。
可能有的同学会发出疑问，我们为什么要追踪慢 SQL，有什么意义呢？
二、慢 SQL 危害
这里要从慢 SQL 的危害谈起，以 MySQL 数据库为例，总结起来有以下几点：
- 当出现慢查询，DDL 操作都会被阻塞，也就是说创建表、修改表、删除表、执行数据备份等操作都需要等待，这对实时备份重要数据的系统来说是不可容忍的
- 慢查可能会占用 mysql 的大量内存，严重的时候会导致服务器直接挂掉，整个系统直接瘫痪
- 慢 SQL 的执行时间过长，可能会导致应用的进程因超时被 kill，无法返回结果给到客户端
- 造成数据库幻读、不可重复读的概率更大，假设该慢 SQL 是一个更新操作但因执行时间过长未提交，而另一条 SQL 也在更新数据并且已提交，用户再次查询的时候，看到的数据可能与实际结果不符
- 严重影响用户体验，SQL 的执行时间越长，页面加载数据耗时也就越长
以千万级的订单表为例，未优化的情况下，单表分页查询 10 条数据，耗时：39s
首先不说可能对数据库服务器造成的潜在压力，没有任何一个用户会在页面查询订单查询等待 39 秒！
三、如何定位慢 SQL
说了这么多，我们如何去定位慢 SQL 呢？
3.1、开启慢 SQL 监控
以 MySQL 为例，我们可以通过如下方式，查询是否开启慢 SQL 的监控。
show variables like \'slow_query_log%\';
通过如下命令，开启慢 SQL 监控，执行成功之后，客户端需要重新连接才能生效。
-- 开启慢 SQL 监控
set global slow_query_log = 1;
如果想关闭慢 SQL 监控，将其配置为0就可以了。
-- 关闭慢 SQL 监控
set global slow_query_log = 0;
需要特别注意的是，当服务器重启之后，当前配置会失效！
3.2、配置慢 SQL 阀值
默认的慢 SQL 阀值是10秒，可以通过如下语句查询慢 SQL 的阀值。
-- 查询慢 SQL 的阀值
show variables like \"long_query_time\";
我们可以通过如下方式，将慢 SQL 阀值配置成0.2秒。
-- 修改慢 SQL 的阀值
set global long_query_time = 0.2;
然后，退出客户端，重新连接服务器，就生效了！
与之类似，当服务器重启之后，当前配置会失效！
3.3、永久开启慢 SQL 监控
以上的操作，当服务器不重启会一直有效，但是当服务器一单重启之后，配置就会失效，如果想永久生效，可以通过修改全局配置文件my.cnf使之永久生效。
以 CentOS 为例，打开my.cnf配置文件，添加如下配置变量。
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/ecs-203056-slow.log
long_query_time = 1
重启 mysql 服务器
systemctl restart mysqld
3.4、慢 SQL 监控测试
初始化一张日志表，数据量在 10 万左右就够了，然后我们来执行 SQL，看看是不是被正常抓取到。
很清晰的看到，慢 SQL 已经被抓取记录。
日志内容详解：
- Time：表示客户端查询时间
- root[root]：表示客户端查询用户和IP
- Query_time：表示查询耗时
- Lock_time：表示等待 table lock 的时间，注意InnoDB的行锁等待是不会反应在这里的
- Rows_sent：表示返回了多少行记录（结果集）。
- Rows_examined：表示检查了多少条记录。
除此之外，我们还可以借助mysqldumpslow命令工具，分析慢 SQL 的数据情况，可以通过如下参数进行组合分析
-s 表示按何种方式排序，支持的参数如下
al: 平均锁定时间
ar: 平均返回记录数
at: 平均查询时间
c: 访问次数
l: 锁定时间
r: 返回记录
t: 查询时间
-t NUM 返回前面多少条的数据
-g PATTERN 后边搭配一个正则匹配模式，大小写不敏感
常见的用法如下：
- 查询返回记录集最多的10个 SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/ecs-203056-slow.log
- 查询访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/ecs-203056-slow.log
- 查询按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g \"LEFT JOIN\" /var/lib/mysql/ecs-203056-slow.log
四、慢 SQL 是怎么发生的
面对这种耗时巨长的 SQL，我们不禁会发出一个疑问，它是怎么发生的呢？
这得从 SQL 的执行过程说起，我们先简单的看看下面这个图。
一条 SQL 语句执行时，总结起来大概分为以下几个步骤：
- 1.若查询缓存打开则会优先查询缓存，若命中则直接返回结果给客户端。
- 2.若缓存未命中，此时 MySQL 需要搞清楚这条语句需要做什么，则通过分析器进行词法分析、语法分析。
- 3.搞清楚要做什么之后，MySQL 会通过优化器对 SQL 进行优化，生成一个最优的执行计划
- 4.最后通过执行器与存储引擎提供的接口进行交互，将结果返回给客户端
在 MySQL 执行过程中，优化器可能会对我们即将要执行的 SQL 进行改造，改造思路如下：
- 1.根据搜索条件，找出 SQL 中所有可能使用的索引
- 2.然后计算全表扫描的成本开销
- 3.接着计算使用不同索引执行查询的成本开销
- 4.最后会对比各种执行方案的成本开销，找出开销值最小的那一个
其中影响成本开销值的计算，主要是I/O成本和CPU成本这两个指标。
从I/O成本视角看：
- 当表的数据量越大，需要的 I/O 次数也就越多
- 从磁盘读取数据比从缓存读取数据，I/O 消耗的时间更多
- 全表扫描比通过索引快速查找，I/O 消耗的时间和次数更多
从CPU成本视角看：
- 当 SQL 中有排序、子查询等复杂的操作时，CPU 需要先把数据存到临时表中，再对数据进行加工，需要的 CPU 资源更多
- 全表扫描相比于通过索引快速查找，需要的 CPU 资源也更多
因此我们不难发现，在没有开启缓存的情况下，当表的数据量越大，如果 SQL 又没有走索引，很容易发生查询慢的问题。
五、小结
本文主要围绕慢 SQL 的定位和可能存在的风险进行了简单的介绍，整篇介绍的算是一个入门级的知识，文章内容难免有些理解不到位的地方，欢迎网友留言指出！
由于篇幅的原因，我们会在下篇文章中介绍慢 SQL 的优化思路。
六、参考
1、稀土掘金 - 三个猪皮匠 - 慢SQL优化一点小思路
2、博客园 - 雪山上的蒲公英 - 慢 SQL 分析
3、博客园 - 慢查询的危害
作者：程序员志哥
出处：www.pzblog.cn
资源：微信搜【Java极客技术】关注我，回复 【cccc】有我准备的一线程序必备计算机书籍、大厂面试资料和免费电子书。 希望可以帮助大家提升技术和能力。
来源：https://www.cnblogs.com/dxflqm/p/16590573.html
本站部分图文来源于网络，如有侵权请联系删除。