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

通常情况下，SQL 慢查询一般只会导致应用服务响应变慢，但在双十一商家大促时，却因为一个慢查询差点把整个网站搞崩溃的情景。

正常情况下，我们都会为表里的高频字段添加索引，如下创表语句：

 1CREATE TABLE `t_order` (
 2  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
 3  `merchant_id` varchar(20) NOT NULL COMMENT '商家id',
 4  `status` tinyint(1) NOT NULL COMMENT '状态(1：成功，2：失败)',
 5  `amount` mediumtext NOT NULL COMMENT '金额',
 6  `name` varchar(50)  NOT NULL COMMENT '订单名称',
 7  `remark` varchar(50) NOT NULL COMMENT '备注描述',
 8  PRIMARY KEY (`id`),
 9  UNIQUE KEY `idx_merchant_id` (`merchant_id`) USING BTREE COMMENT '商家ID索引'
10) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

在数据量小时，执行select id,amount,name,remark from t_order where merchant_id = 23423423 limit 0,10; 语句出现凸显的问题不明显，但在数据量逐渐变大时，整个查询耗时就会变得很长，这是因为传入merchant_id的值是整形，与实际字段类型不匹配，导致索引失效引起全表扫描。

在上述索引使用不当引起的慢查询很普遍，就在去年双十一商家促活时，我写了一个 SQL 慢查询差点把整个系统搞崩溃了。

案发现场

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

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

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


经过分析 dump 文件的内容及代码，发现是程序无法获取数据库连接，大量请求一直在等待，接着再使用 show processlist 命令定位到某个 SQL 查询耗时接近 10s 多，查询的表数据量约在 1500W 左右，而当时双十一促活火爆，请求量高，导致系统服务内存暴涨，出现服务无法响应，进一步恶化为资源挂占。

首先 select * from t_order WHERE merchant_id= 1349865679 limit 0 10; 这个 SQL 看起来似乎用到了索引，可是为什么扫描到行还是这么多呢？

于是我就去看看表结构，期望能从中找到点有价值的东西，通过 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 连接、端口资源耗尽，无法响应请求

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

从上述图可发现，用户请求后在服务器等待了数秒后才向数据库发起 SQL 执行命令，若某一瞬间请求量突增，大量请求则会阻塞在服务器上，同时系统也会分配大量端口创建 TCP 连接，直至整个请求结束后才会释放端口，销毁 TCP 连接。

因此，当堆积大量请求无法及时处理时，则会导致无法为新的请求分配端口或 TCP 连接。

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

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

为什么出现慢查询？

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

1. SQL编写问题
2. 锁竞争激烈
3. 业务实例相互干绕，争用 IO/CPU 资源
4. 服务器硬件配置
5. MYSQL BUG

而本次的问题是属于 SQL 编写导致，其根本原因是索引使用不当，引起全表扫描。

如何优化 SQL 编写导致的慢查询

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

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

1、字段类型转换导致索引失效，如字符串类型的不用引号，数字类型的用引号等，这有可能用不到索引导致全表扫描；

2、MySQL 不支持函数转换，因此索引字段上不能加函数，否则这将用不到索引,

3、不要在索引字段上做计算，对于需要计算的字段，可考虑将计算方法放在“=”后面

4、使用 like 模糊匹配查询，一般禁止使用 % 前导的查询防止索引失效，如like “%Liew”，

5、不要使用 select *，应按需加载需要的字段，查询无用的列在数据传输和解析绑定过程中会增加网络IO，以及CPU的开销；

6、排序请尽量使用升序 ，因为倒序多了文件排序操作，SQL 执行效率变低，MySQL 8 开始支持降序索引解决排序性能问题；

7、or 查询时尽量使用 union 代替，使用 or 可能会导致引擎放弃使用索引而进行全表扫描；

8、最左匹配原则，索引是有顺序的，查询条件中缺失索引列之后的，其他条件都不会走索引，比如(a, b, c)索引，只使用b, c索引，就不会走索引；

9、在 order by / group by 子查询的字段尽量建立索引，减少文件排序；

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

1、尽量规避大事务的 SQL，大事务的 SQL 会影响数据库的并发性能及主从同步；

2、分页语句 limit 的问题；

3、删除表所有记录请用 truncate，不要用 delete，因为truncate执行时不会产生UNDO信息。

4、不让 MySQL 做额外的事情，如计算、转换等；

5、输写 SQL 带明确的字段，以防止后面表变更带来的问题，性能也是比较优的。

6、在InnoDB引擎上使用 select count(*)，会全表扫描统计，而MyISAM内置了一个计数器可直接获取总数；

7、慎用 oder by rand()，因为 rand() 放在 order by 子句中会被执行多次，效率很低。

8、查询语句中只要包含不等式，负向查询一般都不会走索引，如 !=, <>, not in, not like等。

9、删除不再使用或很少使用的索引，当表中原有的索引不再需要时，定期找出并删除这些索引，从而减少索引对更新操作的影响。

脱离业务的设计都是耍流氓，技术的意义在于服务业务。因此，索引的设计需要充分考虑业务的需求与设计原则之间做一些取舍，满足需求是基础。

通过合理设计索引、使用索引避免出现慢查询问题，同时利用监控工具实时监控数据库的连接数、慢查询等，并建立告警机制，如此便能及时发现、定位问题，尽可能减少给客户带来的损失。

避免、发现慢查询的措施

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

explain select * from t_order WHERE merchant_id= '1349865679';


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

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

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