我的一个低级错误，导致数据库崩溃半小时！！

最新推荐文章于 2022-04-14 21:21:14 发布

互联网架构

最新推荐文章于 2022-04-14 21:21:14 发布

阅读量159

点赞数 1

本文转自公众号：鄙人薛某

原文：https://www.cnblogs.com/yeya/p/13200655.html

一、反常的SQL语句

某周四午休时分，我正在工位上小憩，睡梦中仿佛看到了自己拿着李白在荣耀峡谷里大杀四方的情景，就在我刚拿完五杀准备带领队友推对面水晶的时候，一句慌乱急促的“糟了”把我从睡梦中惊醒。

我眯开朦胧的双眼，才发现刚才的发声来源于我的组长庄哥，看到他在紧张的点开日志系统查看日志，我预感到有什么不妙的事情发生，仔细一问才知道，原来就在我眯眼的期间，线上数据库服务器的CPU被打满，同时触发了生产数据库只读延迟的限定时间并且发出告警，而且告警的过程持续了半个小时。

这让我倒吸了一口凉气，因为我们组做的系统很多都用的是同一个数据库服务器，日用户活跃量有好几十万，如果服务器崩溃了将会使所有的系统服务都不可用，于是我们赶紧通过SQL日志进行问题查找，最后排查出来是因为一张SQL的高量查询没有走索引导致，日志列表显示，这条sql语句的扫描行数达到了上百万，基本就是全表扫描的情况，而且半个小时的时间查询了达上万次，每条SQL查询的耗时都在3000ms以上。

我的天啊，难怪服务器会CPU打满，这么一条耗时的SQL语句查询量这么大，数据库的资源当然是直接就崩溃了，这是当时那条SQL的查询情况：

二、临时处理

看了这条语句，我又倒吸一口凉气，这不就是我写的系统调用的SQL语句吗？完了，这回逃不掉了，真是人在睡梦里，锅从天上来。

当然，因为是我自己写的SQL，所以我一看就知道这条语句是有问题的。

根据我的代码处理，这条sql的调用还少了个重要的参数user_fruit_id，这个参数没有传的话是不应该走这条sql查询的，在我的设计里，该参数是数据表里一个联合索引的最左侧字段，如果该字段没有传值的话，那么索引就不会生效了。

KEY `idx_userfruitid_type` (
  `user_fruit_id`,
  `task_type`,
  `receive_start_time`,
  `receive_end_time`
) USING BTREE

虽然定位到了SQL语句，但是线上的问题刻不容缓，总不可能找出bug改完再上线吧，所以，我们只能做了一个临时处理，就是在原来的表上多加了一个联合索引，其实就是去掉了user_fruit_id 字段，让这些高量的查询都能走新的索引，就像下面这样：

KEY `idx_task_type_receive_start_time` (
 `task_type`,
 `receive_start_time`,
 `receive_end_time`,
 `created_time`
) USING BTREE

加上索引后，sql的扫描行数就大幅度的降低了，重启实例后就又能正常运行了。点击这里获取一份 MySQL 开发军规，关注公众号互联网架构师获取更多 MySQL 系列教程。

三、最左匹配原则

那么为什么最左侧的字段没传索引就不生效了，这是因为MySQL的联合索引是基于“最左匹配原则”匹配的。

我们都知道，索引的底层是B+树结构，联合索引的结构也是B+树，只不过键值数量不是一个，而是多个，构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。

例如我们用两个字段（name，age）这个联合索引来分析，

图片来源于林晓斌老师的《MySQL实战45讲》

当我们在where条件中查找name为“张三”的所有记录的时候，可以快速定位到ID4，并且查出所有包含“张三”的记录，而如果要查找“张三，10”这一条特定的数据，就可以用 name = "张三" and age = 10 获取，因为联合索引的键值对是两个，所以只要前面的name确定的情况下就可以进一步定位到具体的age记录，但是如果你的查询条件只有age的话，那么索引就不会生效，因为没有匹配最左边的字段，后面所有的索引字段都不会生效，所以我之前写的sql语句才会因为少了最左边的user_fruit_id字段而走了全表扫描的查询方式。

正常来说，假设一个联合索引设计成(a,b)这样的结构的话，那么用a and b作为条件，或者a单独作为查询条件都会走索引，这种情况下我们就不要再为a字段单独设计索引了。

但如果查询条件里面只有b的语句，是无法使用(a,b)这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护(a,b)、(b) 这两个索引。关注公众号互联网架构师获取更多 MySQL 系列教程和面试题答案。

四、找出Bug

虽然临时做了处理，但问题并不算解决，很明显是系统出现了bug才会有走这样的查询条件。

因为是我自己写的代码，所以知道是哪条SQL后我就马上定位到了代码里的具体方法，后来才发现是因为我对user_fruit_id字段的判空处理不生效所致。

因为该字段是从调用方传过来的，所以我在方法参数里对该字段做了非空限制的注解，也就是javax包下的@NotNull。

public class GardenUserTaskListReq implements Serializable {

    private static final long serialVersionUID = -9161295541482297498L;

    @ApiModelProperty(notes = "水果id")
    @NotNull(message = "水果id不能为空")
    private Long userFruitId;
    /**以下省略*/
    .....................
}

虽然加上该注解来做非空校验，但我却没有在参数加上另一个注解@Validated，该注解如果没加上的话，那么调用javax包下的校验规则就都不生效，正确的写法是在controller层方法的参数前面加上注解：