啥？我写的一条SQL让公司网站瘫痪了...

作者：微信小助手

发布时间：2020-10-24T00:11:33

“

一条慢查询会造成什么后果？之前我一直觉得不就是返回数据会慢一些么，用户体验变差？

图片来自 Pexels

其实远远不止，我经历过几次线上事故，有一次就是由一条 SQL 慢查询导致的。

那次是一条 SQL 查询耗时达到 2-3 秒「没有命中索引，导致全表扫描」，由于是高频查询，并发一起来很快就把 DB 线程池打满了，导致大量查询请求堆积，DB 服务器 CPU 长时间 100%+，大量请求 timeout...

最终系统崩溃，老板登场！可见，团队如果对慢查询不引起足够的重视，风险是很大的。

经过那次事故我们老板就说了： 谁的代码再出现类似事故，开发和部门领导一起走人，吓得一大堆领导心发慌，赶紧招了两位 DBA 同事🙂🙂🙂。

慢查询，顾名思义，执行很慢的查询。有多慢？超过 long_query_time 参数设定的时间阈值（默认 10s），就被认为是慢的，是需要优化的。慢查询被记录在慢查询日志里。

慢查询日志默认是不开启的，如果你需要优化 SQL 语句，就可以开启这个功能，它可以让你很容易地知道哪些语句是需要优化的（想想一个 SQL 要 10s 就可怕）。好了，下面我们就一起来看看怎么处理慢查询。

慢查询配置

开启慢查询

MySQL 支持通过以下方式开启慢查询：

输入命令开启慢查询（临时），在 MySQL 服务重启后会自动关闭。
配置 my.cnf（Windows 是 my.ini）系统文件开启，修改配置文件是持久化开启慢查询的方式。

方式一：通过命令开启慢查询

步骤 1： 查询 slow_query_log 查看是否已开启慢查询日志：

show variables like '%slow_query_log%';

mysql> show variables like '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_query_log      | OFF                               |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
2 rows in set (0.01 sec)

步骤 2： 开启慢查询命令：

set global slow_query_log='ON';

步骤 3：指定记录慢查询日志 SQL 执行时间得阈值（long_query_time 单位：秒，默认 10 秒）。

如下我设置成了 1 秒，执行时间超过 1 秒的 SQL 将记录到慢查询日志中：

set global long_query_time=1;

步骤 4： 查询 “慢查询日志文件存放位置”。

show variables like '%slow_query_log_file%';

mysql> show variables like '%slow_query_log_file%';
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
1 row in set (0.01 sec)

slow_query_log_file 指定慢查询日志的存储路径及文件（默认和数据文件放一起）。

步骤 5：核对慢查询开启状态，需要退出当前 MySQL 终端，重新登录即可刷新。

配置了慢查询后，它会记录以下符合条件的 SQL：

查询语句
数据修改语句
已经回滚的 SQL

方式二：通过配置 my.cnf（Windows 是 my.ini）系统文件开启（版本：MySQL 5.5 及以上）。

在 my.cnf 文件的 [mysqld] 下增加如下配置开启慢查询，如下图：

# 开启慢查询功能
slow_query_log=ON
# 指定记录慢查询日志SQL执行时间得阈值
long_query_time=1
# 选填，默认数据文件路径
# slow_query_log_file=/var/lib/mysql/localhost-slow.log

重启数据库后即持久化开启慢查询，查询验证如下：

mysql> show variables like '%_query_%';
+------------------------------+-----------------------------------+
| Variable_name                | Value                             |
+------------------------------+-----------------------------------+
| have_query_cache             | YES                               |
| long_query_time              | 1.000000                          |
| slow_query_log               | ON                                |
| slow_query_log_file          | /var/lib/mysql/localhost-slow.log |
+------------------------------+-----------------------------------+
6 rows in set (0.01 sec)

慢查询日志介绍

如上图，是执行时间超过 1 秒的 SQL 语句（测试）：

第一行：记录时间。
第二行：用户名、用户的 IP 信息、线程 ID 号。
第三行：执行花费的时间【单位：秒】、执行获得锁的时间、获得的结果行数、扫描的数据行数。
第四行：这 SQL 执行的时间戳。
第五行：具体的 SQL 语句。

Explain 分析慢查询 SQL

分析 MySQL 慢查询日志，利用 Explain 关键字可以模拟优化器执行 SQL 查询语句，来分析 SQL 慢查询语句。

下面我们的测试表是一张 137w 数据的 app 信息表，我们来举例分析一下。

SQL 示例如下：

-- 1.185s
SELECT * from vio_basic_domain_info where app_name like '%翻译%' ;

这是一条普通的模糊查询语句，查询耗时：1.185s，查到了 148 条数据。

我们用 Explain 分析结果如下表，根据表信息可知：该 SQL 没有用到字段 app_name 上的索引，查询类型是全表扫描，扫描行数 137w。