从杀慢查询入手来预防 MySQL 雪崩的办法

一、背景

慢查询在 MySQL 数据库管理中，已经是再熟悉不过的事情了，只要我们在使用 MySQL，那慢查询就会一直存在下去，因为不管是业务 APP，还是 MySQL，他们的状态都是动态变化的，在这个动态的服务中，可能经常遇到的问题是，某几个指标的变化形成了共振效应，进而导致本来不慢的查询语句变成慢查询，本来可以走二级索引并很快返回的语句变成了全表扫描，这还不止，可能这种影响范围会继续进一步扩大，导致整个实例或者集群被打死，出现一些“被影响”的很慢的查询语句，从而产生了我们通常意义上的“雪崩效应”，最终导致的是由慢查询导致的数据库故障。

但这样的故障，真是由慢查询导致的么？这个我认为不一定，具体原因很难穷尽，但在一个动态变化的环境中，多个因素导致了共振效应，进一步导致雪崩现象，这应该是确定的。面对这样的问题，我们应该怎么解决，或者提前避免呢？可能很多人会认为因为是共振导致的，是不是要去查到具体共振因素，这样问题就解决了？我想说的是，这种解决办法有时候可以解决问题，但通常问题发生之后，共振的场景已经不见了，我们此时见到的只有慢查询，除非有很全面的日志，不然这种方法不具有可操作性。

二、解决办法

对于上面出现的问题，我们不能从原因上解决慢查询，那么是不是可以考虑从结果上去解决呢？结果就是慢查询，也就是说，我们是不是只需要把慢查询消灭了，就可以把相应的雪崩避免了？

答案是肯定的，我们可以想想，如果雪崩出现的时候，也就是雪崩引起的故障出现的时候，我们是咋处理故障的？一般情况下，也是通过不断杀慢查询的方式来解决问题的，让拥堵消失，拥堵消失之后，数据库本身的状态就平静了，业务就可以继续有条不紊的访问了，所以用同样的原理，我们如果能在共振出现之后，第一批慢查询出现的时候，将其杀掉，这样可能就能有效避免后续的雪崩效应，这样的推论是没有问题的。

三、杀慢查询的方式

很多人想到了上面的解决方案，就是将慢查询杀掉，但在慢查询一开始出现的时候，压力还不大，数据库可能可以扛过去，DBA 可能并不会注意到这个数据库“将来”会出现问题，所以并不会选择杀掉，只有恶化了，或者已经出现小面积的雪崩了，才会选择去杀掉慢查询，但此时杀掉的逻辑很简单————只要是查询的，时间大于多少的，可能就会被杀掉了，大不了再多加几个过滤条件而已，比如状态是 statistics，方式是大同小异的，但这样的方式有很多弊端，我列举如下：

1. 很难做到常态化：就像上面说的，在雪崩之前，并不能预测到当前数据库马上要故障了。就自动启动杀慢查询这个动作，因为数据库是一个动态服务，当前的服务水平，和服务能力，需要综合各种指标来判断，包括 CPU、内存、多实例互相影响、IO、并发个数、Buffer Pool的有效性等等，即使是人工去判断，也很难做到，所以常态化程序去决定要不要杀一个慢查询，基本是无稽之谈。这种杀慢查询的方法，只能用来在处理故障的时候使用，而不是常态化。

2. 很难做到准确：这种杀慢查询的方法，其实就是凭借经验值，综合各种指标，去制定杀慢查询的策略，比如当机器 Load，或者 CPU 使用率到达多少的时候，就开始杀了，但请问，在多实例模式下，你如何判断是 3306 导致的 Load 升高，而不是 3307 呢？比如再考虑 IO 的问题，如果发现某一个时间 IO 特别高，然后就开始启动杀慢查询的操作，IO 高了你如何去判断是哪个 SQL 语句导致的 IO 高？判断不出来的话，难道是要全部杀掉吗？IO达到多少的时候要杀？30？50？还是 100？这个由谁来定义，假定定义为 30，那 29 要不要杀呢？如何确定30是有问题的，而 29 是没有问题的？同时业务出故障，还有一个容忍度，有些业务 30 就可能出问题了，但有些业务 load 到达 100 也没问题，那请问用什么逻辑来区分对待不同的数据库呢？因为很明显的是，不同数据库使用相同的判断指标，是非常不明智的。其实这里举了两个指标的例子，想说明的问题是，最终这种方式，可能就是乱杀一通，本身没问题，却制造了不少问题，本身有问题却并没有解决，请问，杀出来问题，谁来负责？

3. 很难做到自动化：自动化有一个程度的问题，这里包括两种，一种是自动决定要不要杀，要杀什么，另一种是需要杀的时候 DBA 启动自动化脚本去杀，这里最关键的问题是决策问题，什么时候启动的问题。很明显，人判断比机器判断靠谱多了，目前 AIDBA 还不具备这样的能力，如果不具备，那就没办法谈自动化了，只能是人工触发，很明显这就是在处理故障的问题，而不是提前避免或者预防故障的问题了。

4. 很难做到统一化：就像上面说到的，不同业务线，不同的机器性能，不同的 SQL 语句，不同的数据量，不同的索引，不同的表大小，一个语句执行多久需要被杀掉？扫描多少行需要被杀掉？Load 达到多少需要被杀掉？并发量达到多少又需要被杀掉呢？相关指标不一而足，但核心问题是，这些指标难道有一个标准吗？达到这个标准就肯定出问题吗？这个谁能定这样的逻辑？谁又敢定呢？假如达到某个标准就肯定出问题，或者误杀了之后，业务自己承担责任，那不同业务肯定具有不同的指标，那么多数据库实例，如何管理这些指标呢？这些都是问题。

5. DBA 不了解 SQL 语句：DBA 在运维过程中的角色，大多数是去做 DB 本身的一些运维工作，比如迁移、风险控制、故障处理、拆分、优化等，但针对 SQL 语句本身，只有业务自己了解其内部逻辑及语句之间的相关逻辑等，一个语句执行多少时间是合理的，超过多少时间是不合理的，这种信息 DBA 是不了解的，这方面没有任何专业度可言，而现在恰恰是要把杀 SQL 语句的决策交给 DBA，这是荒唐的，不可信的，DBA 不可以去做这样的决定，除非数据库此时已经故障了，这是故障处理的范畴。因为 DBA 没有能力去做这个事情，那就不做，因为运维逻辑很简单，DBA 不做没有把握的事情。

6. 业务没办法做决定：上面也讲到了，判断指标包括很多，没有标准能确定一个指标达到了某个值就肯定出问题，或者小于某个值就肯定没问题，这个 DBA 没办法去定义，也没有经验值可用，但这事儿要做的话，DBA 没有办法做到，也没有权利决定是不是杀慢查询，是不是可以把这些参数的决定权交给业务开发自己去定义，比如他关心的某个数据库，如果出现慢查询的话，给出一系列指标，这些指标达到多少的时候就去杀，指标之间的关系是或还是与，或者可以更精细化的定制，系统做得非常精细，或和与可以随便定制，但这样的问题是，业务看到 Load 这个框的时候，应该填多少呢？多少才是没有问题的，或者给一个数据库指标，比如并发量，达到多少就会有问题呢？这个时候我相信，业务是懵的，这是跨领域的，你虽然把决定权交出去了，但他在专业度上面，没办法做这个决定，他怎么会把并发度与故障联系起来呢？或者 Load 与故障联系起来呢？这更是无稽之谈了。

7. 责任界定：这种杀慢查询的方式，上面已经讲得非常清楚了，没有优点只有缺点，花了大力气还惹一身骚，吃力不讨好，做了一个非常强大的系统，最后不知道如何下手，不知道如何去执行，那不是白费力气了么？这种方式存在一个很大的问题是，存在大量的错杀问题，杀错了，出故障了，谁的责任？这意味着出现的局面是各种扯皮，各种推卸责任，之后，可能会进入无休止的调参运动中，这样 DBA /开发就成功地做了一次华丽转身，请叫我们调参工程师。

8. 数据库核心是服务：最后一个问题，其实是 DB 的核心作用，作为 DBA 或者了解 DBA 的人都知道，操作系统、手机系统、APP 等系统出了名的运维三板斧之一是：重启，重启是很凑效的。但数据库不是这样，数据库是管理数据的，重启导致的问题可能更大，所以数据库的运维思路是尽可能让他活着，尽可能让他好好地活着，这样的目的只有一个，就是更好的服务业务，所以不管任何时候，我们第一思路是提供服务，而不是宁愿错杀，也要在某种情况下就进入拒绝服务的状态，这种思路是有问题的，不符合数据库运维思路的。

上面讲了杀慢查询的综合方法的各种弊端，很明显这是不靠谱的，不负责任的，后患无穷的，不解决问题的，我们时刻要坚决守住这样的底线，不要去做这样的事情，不然肯定是徒劳的，吃力不讨好的。

那还有没有一种办法，在有效避免上述所有问题的同时，还能解决慢查询带来的各种问题呢？答案是有的。

四、解铃还须系铃人

我们做这个事情，需要换一种思路去考虑问题，语句是开发写的，语句是从应用程序访问过来的，那只有应用端或者开发才了解 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 执行了一个新的语句，杀掉的时候并不知道是新的语句，此时被杀掉的是新语句，从而导致了误杀，但实际上应该想想，这种概率是非常小的，在匹配到与杀之时，时间差应该是几毫秒，在这几毫秒的窗口内，误杀的概率可以忽略不计，但这是一种潜在风险，需要提前考虑到。

7. 推广度：这种方法是有接入门槛的，但实际上门槛高度有限，如果所有业务都能接入的话，数据库整体运行就会很流畅，异常问题都会提前发现与避免，不会再出现大的雪崩效应，当然这个结论，还需要时间验证，并且还需要业务填的时间相对合理才行。

• 源码制

还有一种办法可以实现这种功能，就是通过修改 MySQL 源代码来实现，其实业内已经有一些这样的团队做了这样的事情，但最基本的逻辑还是没变的，需要业务开发自己在 SQL 语句中设置超时时间值，Mysql 服务执行的时候通过解析语句发现设置了这个值，就会在执行的过程中不断检查已经执行的时间，如果超过所设置的时间了，就会将其杀掉，从而实现了这样的 SQL 语句执行超时的机制。

但很明显，这样的实现方式，门槛非常高，需要修改源码，不断维护源码，很少有人能做到这样，并且我认为，运维和使用 MySQL 的过程中，如果有啥需求，能通过 MySQL 的原生方法就能解决掉的（外围办法），就不要去改源码来解决，因为通过外围办法解决的话，风险度会小很多，并且自由可控，不需要对 MySQL 服务本身做过多干预，解决过程很轻量，易用。

五、总结

综上所述，杀慢查询还是需要非常谨慎的，提供服务是第一原因，所以既需要保证杀的准确，也需要保证杀的及时，还需要保证不能杀出来问题，所以这事情本身是一个很复杂的问题。

上面推荐的这种解决办法，实际上就是在给一个 SQL 语句设置一个相对合理的超时时间，这是非常容易理解的，大家可以想想，写代码的时候，超时时间不都是随处可见的吗？如果能给 SQL 语句也设置一个超时时间，这样可以更好的保护数据库的稳健运行，那何乐而不为呢？

DBA 是一个服务性质的工种，也非常想替业务解决一些头疼的问题，但解决问题的时候，不能只见树木不见森林，需要站在一定高度去看待问题，需要找到合适的方法才能很好的解决问题，不然有可能就是在创造问题，找到了好的方法，通常就可以达到事半功倍的效果。

把复杂问题简单化，业务去做自己擅长的工作，DBA 也去做自己力所能及的工作，分工明确，合作共赢，长久下去，一定可以建立一个稳定、健康和良好的服务环境。

END

欢迎加入大数据资料交流分享群，与志同道合的大数据朋友一起进步！