数据库慢查询竟把系统搞崩了

通常情况下，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 、连接数过多等问题并告警 。

写在最后

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

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

数据库(Database)是按照 数据结构来组织、 存储和管理数据的建立在计算机存储设备上的仓库。
简单来说是本身可视为 电子化的文件柜——存储电子 文件的处所，用户可以对文件中的数据进行新增、截取、更新、删除等操作。