我嘞个去,慢查询竟把系统搞崩了

运维 数据库运维
通常情况下,SQL 慢查询一般只会导致应用服务响应变慢,但在去年双十一活动时,我碰到了一个慢查询把整个网站搞崩溃的问题。

[[384119]]

本文转载自微信公众号「码农私房话」,作者GoQeng 。转载本文请联系码农私房话公众号。

通常情况下,SQL 慢查询一般只会导致应用服务响应变慢,但在去年双十一活动时,我碰到了一个慢查询把整个网站搞崩溃的问题。

 

慢查询请求流程

案发现场

在去年双十一晚,我突然收到产品经理的“催魂电话”并告知:整个网站的页面一直在转菊花,无法显示数据,吓得我立马掏出电脑。

 

登录服务器后竟发现服务内存占用率接近 100%,CPU 长期负荷高,我迅速地检查是否代码中出现死循环、大对象内存泄露等问题,但经过排查发现代码是正常。

接着使用 jstatck pid 命令把线程堆栈信息 dump 出来,发现很多业务线程均处于 BLOCKED 状态,同时也用 jstat -gcutil 观察到 FULL GC 相当频繁。

 

jstack 线程堆栈

经分析 dump 文件的内容及代码,发现是线程无法获取数据库连接,大量处于等待状态。

随后使用 show processlist 命令发现某个 SQL 查询耗时接近 10s 多,查询的表数据量约在 1500W 左右。

我迫不及待地分析了慢 SQL select * from t_order where merchant_id= 1349865679 limit 0 10; 从表面看起来似乎用到了索引,可是为什么扫描到行还是这么多呢?

 

我就去看看表结构,期望能从中找到点有价值的东西,通过 show index from t_order;后发现以下有用的信息:

 

从上述结果中看到 merchant_id 索引的离散程度还算大,它的 Cardinality 值接近于 PRIMARY 的值,说明是比较正常的。

既然 merchant_id 索引没问题,那么猜想就是使用姿势不对的问题,我再通过 explain select * from t_order WHERE merchant_id= 1349865679 limit 0,10; 分析运行的SQL,发现确实索引没生效。

 

最后经过耐心地对比代码与 SQL 后,发现 SQL 中 merchant_id 传的是整形,而数据库实际上是字符串,于是我更改字段值为字符串,再次执行 explain select * from t_order WHERE merchant_id= '1349865679' limit 0,10; ,发现索引居然生效了。

 

此时我自信不疑,就是字段类型转换导致的慢查询,MySQL 不会自动帮我们做字段值类型转换,定位出原因后,剩下只需要把字段的值改为字符串就可以了。

慢查询会造成系统奔溃?

1、TCP 连接、端口耗尽,无法响应请求

首先我们先看看请求的流程:


 

从上述图可发现,用户的请求在服务器等待数秒后才向数据库发起执行命令,而当时双十一活动火爆,请求量高,导致数据库连接耗尽,大量请求阻塞在服务器上,同时系统不断地创建 TCP 连接,这些连接直至整个请求结束后才会释放、销毁。因此,当堆积大量请求无法及时处理时,则出现服务无法响应,进一步恶化为资源挂占。

 

2、对象在堆内存无法回收,导致内存不足

相信大家知道,每个用户发出请求、执行逻辑时都需分配 JVM 内存,当前面的请求线程处于阻塞时,后面又越来越多新请求不断申请内存分配,但旧请求中的对象无法回收并释放内存,最终导致内存暴涨、系统响应缓存,进一步演化为系统奔溃,整个请求过程如下:

 

请求时线程申请资源流程

为什么会出现慢查询?

在遇到慢查询的情况时, SQL 编写问题是最常见的因素,但实际上导致慢 SQL 有很多因素,甚至包括硬件和 MySQL 本身的 Bug 等,以下情况都有可能导致慢 SQL 的出现:

  • SQL 编写问题
  • 锁竞争激烈
  • 业务实例相互干绕,争用 IO/CPU 资源
  • 服务器硬件配置
  • MySQL Bug

而本次的问题是属于 SQL 编写导致,其根本原因是索引使用不当,查询时进行全表扫描。

如何优化 SQL 编写的慢查询

针对 SQL 编写导致的慢查询,正确地使用索引就能加快查询速度,避免全表扫描。

 

SQL 全表扫描数据流程

然而在编写 SQL 时需要注意与索引相关的一些规则:

  • 字段类型转换导致索引失效,如字符串类型的不用引号,数字类型的用引号等,这可能使索引失效导致全表扫描;
  • MySQL 不支持函数转换,因此索引字段上不能加函数,否则这将用不到索引;
  • 不在索引字段上做计算,对于需要计算的字段,可考虑将计算方法放在“=”后面;
  • like 模糊查询,一般禁止使用 % 前导,防止索引失效,如 like %liew;
  • 不使用 select *,应按需加载需要的字段,查询无用的列在数据传输和解析绑定过程中会增加网络IO及CPU的开销;
  • 排序请尽量使用升序 ,因为倒序多了文件排序操作,执行效率变低,而 MySQL 8 开始支持降序索引解决排序性能问题;
  • 尽量使用 union 代替 or,使用 or 可能会导致放弃使用索引而全表扫描;
  • 最左匹配原则,索引是有顺序的,查询条件中缺失索引列之后的,其他条件都不会走索引,比如(a, b, c)索引,只使用b, c索引,就不会走索引;
  • 在 order by / group by 子查询的字段尽量建立索引,减少文件排序;

除了上述索引使用规则外,在编写 SQL 时还需要特别注意一下几点:

  1. 尽量规避大事务的 SQL,大事务的 SQL 会影响数据库的并发性能及主从同步;
  2. 删除表所有记录请使用 truncate,而不用 delete,因为 truncate 执行时不会生成 UNDO 信息;
  3. 在 InnoDB 引擎上请谨慎使用 select count(*) 语句,该统计可能会全表扫描数据,而 MyISAM内置了一个计数器可直接获取总数;
  4. 慎用 oder by rand(),因为 rand() 放在 order by 子句中会被执行多次,效率很低;
  5. 负向查询一般都不会走索引,如 !=, <>, not in, not like等;
  6. 删除不再使用或很少使用的索引,从而减少索引对更新操作的影响;

避免、发现慢查询的措施

针对 SQL 编写导致的慢查询,正确地使用索引能加快查询速度,避免全表扫描。

在工作中,每个公司使用 MySQL 的版本可能都大不相同,总会存在一些莫名其妙、不确定的问题,因此为了验证索引的有效性,推荐把主要的 SQL 都通过 explain 命令查看一下执行计划,是否会用到索引。

  1. explain select * from t_order WHERE merchant_id= '1349865679' limit 0 , 10; 

 

然而 explain 工具分析的结果只是 MySQL 评估反馈的执行计划,最终还是依赖 MySQL 执行引擎会根据一定算法落地:

 

SQL 执行流程

因此有可能 explain 分析的结果显示索引生效,但实际执行 SQL 语句时却是全表扫描。

这时候就需要开启 MySQL 的慢查询功能,再通过监控工具 Zabbix 或Grafana辅助及时发现慢查询 SQL 、连接数过多等问题并告警 。

写在最后

慢查询的破坏力很大,轻则出现系统响应缓慢,重则导致系统瘫痪、无法使用。

 

因此在日常开发中,我们需合理地设计、使用索引,避免出现慢查询,同时利用工具实时监控数据库的连接数、慢查询语句等,并建立告警机制,以便能主动地及时发现、定位问题,尽可能减少给客户带来的损失。

 

责任编辑:武晓燕 来源: 码农私房话
相关推荐

2022-10-25 17:53:09

Java线程池

2022-03-01 20:33:50

服务web项目

2023-03-06 08:59:18

AMD显卡驱动

2021-08-23 08:01:38

微信IM系统

2024-11-11 14:57:56

JWTSession微服务

2020-08-10 11:20:59

索引MySQL数据库

2021-02-20 08:06:37

CTO灰度系统

2021-02-20 11:34:43

Linux内核指针

2024-03-25 07:30:03

MySQL数据库SQL日志

2023-02-16 10:26:58

谷歌AI

2020-05-02 15:10:53

AI 王者荣耀人工智能

2022-10-12 17:42:54

MySQL数据表磁盘

2017-04-01 19:00:25

MySQL慢查询

2021-04-07 10:38:43

MySQL数据库命令

2024-02-05 22:48:32

系统代码

2010-07-09 08:46:34

SQL Server查

2020-10-14 10:29:58

人工智能

2019-04-28 11:48:54

Python面试工程师

2020-08-14 09:11:29

RedisQPS数据库

2020-03-29 08:56:07

文件系统磁盘Java
点赞
收藏

51CTO技术栈公众号