慢SQL,压垮团队的最后一根稻草!

网友投稿 719 2023-04-25

慢SQL,压垮团队的最后一根稻草!

慢SQL,压垮团队的最后一根稻草!

在实际的业务系统开发中,虽然我们会严抓代码质量,但是慢 SQL 的检测却常常容易被忽视,今天我们就一起来总结一下关于慢 SQL 可能存在的系统运行风险。

一、什么是慢 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 = ONslow_query_log_file = /var/lib/mysql/ecs-203056-slow.loglong_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 语句执行时,总结起来大概分为以下几个步骤:

若查询缓存打开则会优先查询缓存,若命中则直接返回结果给客户端。若缓存未命中,此时 MySQL 需要搞清楚这条语句需要做什么,则通过分析器进行词法分析、语法分析。搞清楚要做什么之后,MySQL 会通过优化器对 SQL 进行优化,生成一个最优的执行计划。最后通过执行器与存储引擎提供的接口进行交互,将结果返回给客户端。

在 MySQL 执行过程中,优化器可能会对我们即将要执行的 SQL 进行改造,改造思路如下:

根据搜索条件,找出 SQL 中所有可能使用的索引。然后计算全表扫描的成本开销。接着计算使用不同索引执行查询的成本开销。最后会对比各种执行方案的成本开销,找出开销值最小的那一个。其中影响成本开销值的计算,主要是I/O成本和CPU成本这两个指标。

从I/O成本视角看:

当表的数据量越大,需要的 I/O 次数也就越多。从磁盘读取数据比从缓存读取数据,I/O 消耗的时间更多。全表扫描比通过索引快速查找,I/O 消耗的时间和次数更多。

从CPU成本视角看:

当 SQL 中有排序、子查询等复杂的操作时,CPU 需要先把数据存到临时表中,再对数据进行加工,需要的 CPU 资源更多。全表扫描相比于通过索引快速查找,需要的 CPU 资源也更多。

因此我们不难发现,在没有开启缓存的情况下,当表的数据量越大,如果 SQL 又没有走索引,很容易发生查询慢的问题。

五、小结

本文主要围绕慢 SQL 的定位和可能存在的风险进行了简单的介绍,整篇介绍的算是一个入门级的知识,文章内容难免有些理解不到位的地方,欢迎网友留言指出!

由于篇幅的原因,我们会在下篇文章中介绍慢 SQL 的优化思路。

六、参考

1、稀土掘金 -  三个猪皮匠  - 慢SQL优化一点小思路

2、博客园 - 雪山上的蒲公英 - 慢 SQL 分析

3、博客园 - 慢查询的危害

版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。

上一篇:Redo 日志如何在关键时刻拯救数据库?
下一篇:基于图数据库的元数据血缘关系分析技术研究与实践
相关文章