从杀慢查询入手来预防 MySQL 雪崩的办法
作者介绍
王竹峰
去哪儿网数据库总监
擅长数据库开发、数据库管理及维护，一直致力于 MySQL 数据库源码的研究与探索，对数据库原理及实现有深刻的理解。曾就职于达梦数据库，从事多年数据库内核开发工作，后转战人人网，任职高级数据库工程师，目前在去哪儿网负责 MySQL 源码研究与运维、数据库管理和自动化运维平台设计开发及实践工作，是 Inception 开源项目及《MySQL运维内参》的作者， MySQL 方向的 Oracle ACE。
一、背景
慢查询在 MySQL 数据库管理中，已经是再熟悉不过的事情了，只要我们在使用 MySQL，那慢查询就会一直存在下去，因为不管是业务 APP，还是 MySQL，他们的状态都是动态变化的，在这个动态的服务中，可能经常遇到的问题是，某几个指标的变化形成了共振效应，进而导致本来不慢的查询语句变成慢查询，本来可以走二级索引并很快返回的语句变成了全表扫描，这还不止，可能这种影响范围会继续进一步扩大，导致整个实例或者集群被打死，出现一些“被影响”的很慢的查询语句，从而产生了我们通常意义上的“雪崩效应”，最终导致的是由慢查询导致的数据库故障。
但这样的故障，真是由慢查询导致的么？这个我认为不一定，具体原因很难穷尽，但在一个动态变化的环境中，多个因素导致了共振效应，进一步导致雪崩现象，这应该是确定的。面对这样的问题，我们应该怎么解决，或者提前避免呢？可能很多人会认为因为是共振导致的，是不是要去查到具体共振因素，这样问题就解决了？我想说的是，这种解决办法有时候可以解决问题，但通常问题发生之后，共振的场景已经不见了，我们此时见到的只有慢查询，除非有很全面的日志，不然这种方法不具有可操作性。
二、解决办法
对于上面出现的问题，我们不能从原因上解决慢查询，那么是不是可以考虑从结果上去解决呢？结果就是慢查询，也就是说，我们是不是只需要把慢查询消灭了，就可以把相应的雪崩避免了？
答案是肯定的，我们可以想想，如果雪崩出现的时候，也就是雪崩引起的故障出现的时候，我们是咋处理故障的？一般情况下，也是通过不断杀慢查询的方式来解决问题的，让拥堵消失，拥堵消失之后，数据库本身的状态就平静了，业务就可以继续有条不紊的访问了，所以用同样的原理，我们如果能在共振出现之后，第一批慢查询出现的时候，将其杀掉，这样可能就能有效避免后续的雪崩效应，这样的推论是没有问题的。
三、杀慢查询的方式
很多人想到了上面的解决方案，就是将慢查询杀掉，但在慢查询一开始出现的时候，压力还不大，数据库可能可以扛过去，DBA 可能并不会注意到这个数据库“将来”会出现问题，所以并不会选择杀掉，只有恶化了，或者已经出现小面积的雪崩了，才会选择去杀掉慢查询，但此时杀掉的逻辑很简单————只要是查询的，时间大于多少的，可能就会被杀掉了，大不了再多加几个过滤条件而已，比如状态是 statistics，方式是大同小异的，但这样的方式有很多弊端，我列举如下：
上面讲了杀慢查询的综合方法的各种弊端，很明显这是不靠谱的，不负责任的，后患无穷的，不解决问题的，我们时刻要坚决守住这样的底线，不要去做这样的事情，不然肯定是徒劳的，吃力不讨好的。
那还有没有一种办法，在有效避免上述所有问题的同时，还能解决慢查询带来的各种问题呢？答案是有的。
四、解铃还须系铃人
我们做这个事情，需要换一种思路去考虑问题，语句是开发写的，语句是从应用程序访问过来的，那只有应用端或者开发才了解 SQL 语句的情况，包括需要执行多久（SQL 语句的超时时间设置），或者说执行多长时间，就肯定是有问题的，而语句相关的其它参数，他们是不一定能知道的，他们在只知道这样的信息的情况下，如何去杀慢查询呢？有三种办法：
注册制
DBA 开发一个“强大”的系统，用来注册 SQL 语句，指标包括数据库地址，最大执行时间，其它都可以不要，有了这个系统，DBA 可以在每一个数据库上面搞一个 Agent，不断地去访问这个配置库，同时去看 Processlist 中的信息，如果语句和时间都能匹配得上，就杀掉，这种办法当然比上面的办法强多了，至少执行杀的动作是有决策根据的，这种决策根据是建立在业务对自己语句的了解以及对健康度的风险把控之上的，这样就可以有效的避免意外情况相互拥堵导致的数据库雪崩问题，至少在雪崩之前就可以将这个拥堵的状态解决掉。但这种办法也是有问题的，久而久之这个配置库会非常大，因为极限条件下，线上出现的每个 SQL 语句都会出现在这里，这个杀慢查询的 Agent 就没办法良好运行了，并且匹配的是整个 SQL 语句，涉及到模式处理问题，以及很重的字符串比较的问题，效率不能保证。所以，这种办法也是不可行的。
签名制
还有一种更好的办法，就是在 SQL 语句中加上注释，类似这样的形式：
/*!99999 21B2438F55 kill me when query_time > 10 app comments*/ select sleep(10);下面首先讲一下设计细节：
1. 99999 表示的是 MySQL 版本，99999 大于现在所有的 MySQL 版本，所以注释里面的内容就会被 MySQL 忽略，所以这用的是 MySQL 所支持的方式。
2. 后面的 MD5 值，是为了做签名的，主要是为了防止错杀的，一个 MD5 填在这里，如果能碰巧雷同了，那是不是可以买彩票了。所以这个 MD5 值，可以很好地用来给 DBA 做语句识别的功能，而不用去比较整个字符串了，识别到这个值之后，再去解析其它信息，匹配到了，则执行杀的动作。
3. 后面的 "kill me when query_time > 10"，类似是一个协议内容，明确表示这个语句要启用杀慢查询的服务，这里的 10 是可以由业务自己定义，想定义多少都可以，以秒为单位，定义值的选择，需要慎重考虑清楚，可以参考业务正常执行的历史时间，也可以参考业务流程最大容忍的正常时间，大致设置一个值就行，因为当出现异常情况的时候，这个语句需要执行的时间肯定都会比这个大不少，肯定就被杀掉了。当然如果设置太大，导致没有杀掉，也是有问题的，以秒为单位设置的话，杀慢查询是不是及时还要决定于数据库后台杀慢查询程序的执行频率，如果 5 秒一次的话，那就精度是 5 秒，如果是1秒一次的话，精度就是 1 秒，可以自由控制。
4. 后面 “app comments” 部分，业务程序就可以随便写了，Agent 也不会做解析，也可以不写，主要用来做一些注释功能。
下面再说一下这种方式的好处与缺点：
1. 精确：很明显，这种办法是具体到了语句级别，谁想要使用这样的服务，就在语句前面做签名，写上时间，不写的不会被杀掉。因为有签名，遵守了相关协议，业务程序和 DB 之间不存在责任界定不清楚的问题，合作可以很愉快。
2. 常态化：这种办法就可以在数据库本机部署一个 Agent，专门每隔几秒去检查一次数据库执行情况，如果能匹配到慢查询就杀，匹配不到就白跑一次，动作轻量，影响不大，可以有效避免问题的出现。
3. 风险有效控制：当匹配到了需要杀的语句之后，也可以放心地杀掉，因为这是业务根据自己的逻辑及预期设置好的时间，即使被杀了，也是不会有问题的，风险可控，关键是可以避免异常语句引起的问题，因为我们杀慢查询的目标，就是要处理异常情况的慢查询。
4. 业务决定：业务做了自己擅长的事情，DBA 在这个过程中没有任何决策的工作，是一个双赢的局面。
5. 效率高：相比上面的方式，这种方式的配置都在 SQL 语句中，并且只有一个执行时间值，非常容易解析出来，并且大部分情况下，数据库状态都是正常的，并没有什么语句需要杀掉的，所以效率是非常高的。Agent 本身并不需要依赖其它模块，简单易推广。
6. 误杀：这种办法存在的唯一风险是，杀一个语句使用的是 connection id，当匹配到一个需要杀掉的语句之后，在执行 kill 动作时，这个语句正好执行完了，而此时正好这个 connection 执行了一个新的语句，杀掉的时候并不知道是新的语句，此时被杀掉的是新语句，从而导致了误杀，但实际上应该想想，这种概率是非常小的，在匹配到与杀之时，时间差应该是几毫秒，在这几毫秒的窗口内，误杀的概率可以忽略不计，但这是一种潜在风险，需要提前考虑到。
源码制
但很明显，这样的实现方式，门槛非常高，需要修改源码，不断维护源码，很少有人能做到这样，并且我认为，运维和使用 MySQL 的过程中，如果有啥需求，能通过 MySQL 的原生方法就能解决掉的（外围办法），就不要去改源码来解决，因为通过外围办法解决的话，风险度会小很多，并且自由可控，不需要对 MySQL 服务本身做过多干预，解决过程很轻量，易用。
综上所述，杀慢查询还是需要非常谨慎的，提供服务是第一原因，所以既需要保证杀的准确，也需要保证杀的及时，还需要保证不能杀出来问题，所以这事情本身是一个很复杂的问题。
上面推荐的这种解决办法，实际上就是在给一个 SQL 语句设置一个相对合理的超时时间，这是非常容易理解的，大家可以想想，写代码的时候，超时时间不都是随处可见的吗？如果能给 SQL 语句也设置一个超时时间，这样可以更好的保护数据库的稳健运行，那何乐而不为呢？
DBA 是一个服务性质的工种，也非常想替业务解决一些头疼的问题，但解决问题的时候，不能只见树木不见森林，需要站在一定高度去看待问题，需要找到合适的方法才能很好的解决问题，不然有可能就是在创造问题，找到了好的方法，通常就可以达到事半功倍的效果。
把复杂问题简单化，业务去做自己擅长的工作，DBA 也去做自己力所能及的工作，分工明确，合作共赢，长久下去，一定可以建立一个稳定、健康和良好的服务环境。
END