当前位置:首页 > 公众号精选 > 架构师社区
[导读]线上数据库服务器的 CPU 被打满,同时触发了生产数据库只读延迟的限定时间并且发出告警,而且告警的过程持续了半个小时。

来源:鄙人薛某  作者:很懒的程序员

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

▍反常的 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 的联合索引是基于“最左匹配原则”匹配的。

我们都知道,索引的底层是 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) 这两个索引。

▍找出 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 层方法的参数前面加上注解:

除此之外,因为 user_fruit_id 这个字段是另一张表的主键,我在代码里也没有对这张表是否存在这个 id 做查询判断。
这样一来,无论调用方传什么值过来都会直接触发 SQL 查询,并且在不跑索引的情况下直接走全表扫描。

不得不说,这真是个低级错误,说真的,我对这个原因真是感到嘀笑皆非,再怎么说也工作几年了,怎么还犯一些新手级别的错误呢,这脸打得真是让我相当惭愧。

▍总结

虽然是低级错误,但造成的后果也算挺严重了,这次事件也让我更加的警醒,在以后的开发工作中必须要遵守该有的原则,大概有这么几点:

①不能相信调用端。重要的参数都要先做验证,即使是非空值也需要做验证,不符合条件的就要直接返回或抛异常,不能参与业务 SQL 的查询,否则频繁的访问也会对服务造成负担。

②SQL 语句要先做性能查询。对于数据量大的表,建好索引后,所有的 SQL 查询语句要用 explain 检测性能,并且根据结果来进一步优化索引。

③代码必须要 Review。之前我没有放太大的精力在代码的 Review 上,虽说跟迭代排期的紧凑也有关系,但不管怎么说,Bug 确实是我的疏忽造成的,尤其是像空值这种细小的错误在 Java 里可以说家常便饭。

千里之堤毁于蚁穴,有时一个小 Bug 很容易就引发整个系统的崩盘,这一次的问题也让我更加深刻的认识到了 Review 代码的重要性,不管业务开发的工作量有多麻烦,这一步操作绝对不能忽视。


免责声明:本文内容由21ic获得授权后发布,版权归原作者所有,本平台仅提供信息存储服务。文章仅代表作者个人观点,不代表本平台立场,如有问题,请联系我们,谢谢!

本站声明: 本文章由作者或相关机构授权发布,目的在于传递更多信息,并不代表本站赞同其观点,本站亦不保证或承诺内容真实性等。需要转载请联系该专栏作者,如若文章内容侵犯您的权益,请及时联系本站删除。
换一批
延伸阅读

科睿唯安《全球创新Top100》以该公司拥有的专利数据库为基础,根据“成功率”、“地域投资”、“影响力”、“稀缺性”四个评价维度对知识产权进行分析,评选出全球百强创新企业和机构。该评选从2012年开始每年实施一次,202...

关键字: 数据库 5G 人工智能

北京——2024年2月26日 日前,领先的IT市场研究和咨询公司IDC发布《IDC MarketScape:全球云计算和以应用为中心的市场供应商评估》1报告,亚马逊位列“领导者”类别。报告指出,“亚马逊云科技Market...

关键字: 云计算 数据库 存储

北京——2023年12月27日 在2023亚马逊云科技re:Invent全球大会上,亚马逊云科技表彰了在过去一年中,利用亚马逊云科技的技术和服务在推动客户创新及解决方案构建方面做出杰出贡献的卓越合作伙伴。8家亚马逊云科技...

关键字: 数智化转型 机器学习 数据库

北京——2023年12月7日 亚马逊云科技在2023 re:Invent全球大会上,宣布推出三项新的无服务器服务创新,涵盖了数据库和分析领域,使客户能够更快速、更轻松地扩展他们的数据基础设施,以支持最具挑战性的应用场景。...

关键字: 服务器 人工智能 数据库

北京——2023年12月4日 亚马逊云科技在2023 re:Invent全球大会上,宣布推出一系列新的集成解决方案,旨在帮助客户迅速而轻松地连接和分析数据,而无需构建和管理复杂的提取、转换和加载(ETL)数据管道。通过全...

关键字: 数据存储 数据库 人工智能

北京——2023年11月13日 亚马逊云科技携手凯捷(Capgemini)中国共同发布《汽车行业可持续发展白皮书》(以下简称《白皮书》),并助力凯捷中国推出碳排放管理平台,致力于以数智化方式帮助车企解决在实践可持续发展过...

关键字: 数据库 机器学习 电动汽车

11月1日,由开放原子开源基金会主办,电子科技大学承办,深圳市腾讯计算机系统有限公司提供战略合作支持的2023年开放原子校源行(成都站)活动在电子科技大学圆满举行。本次活动以“聚缘于校,开源共行”为主题,将四川省在信息技...

关键字: 开源模式 操作系统 数据库

11月1日,2023杭州云栖大会上,阿里云瑶池数据库宣布已全面实现Serverless化,并接入通义等大模型能力,大幅提升数据库一站式及智能化水平。同时,PolarDB Always On系列推出3大重磅升级,首个数据智...

关键字: Serverless AI 阿里云 数据库

北京2023年9月8日 /美通社/ -- 日前,"云融数智 创领未来"2023浪潮云海云图生态峰会产品技术伙伴专场在北京成功举办。来自开源基金会、云安全、PaaS、数据库服务、容灾备份等领域的头部领先...

关键字: 云安全 私有云 数据库 NI

北京2023年9月7日 /美通社/ -- 在欧盟推出碳边境调节机制(CBAM)的背景下,为了更好的提升中国相关工业企业在碳排放管理方面的能力,推进中国与欧盟相关机制的国际合作,2023年9月3日于北京,DEKRA德凯受国...

关键字: 供应链 可持续发展 ISO 数据库
关闭
关闭