、前言

软件在持续的开发和维护过程中，会不断添加新功能和修复旧的缺陷，这往往伴随着代码的快速增长和复杂性的提升。若代码库没有得到良好的管理和重构，就可能积累大量的技术债务，包括不一致的设计、冗余代码、过时的库和框架以及不再使用的功能。这些因素都会导致软件结构的脆弱，增加系统出错的可能性，我们俗称为“代码腐化”，持续性的重构是一种好的解决方案。SQL也是我们常用的代码语言，虽然SQL本身作为一种标准化的查询语言不会"腐化"，但是使用SQL编写的数据库应用程序、查询和架构确实可能会因时间推移而面临类似于代码腐化的问题。

平台技术部一直坚持做稳定性建设，其中慢SQL就作为一个核心指标在治理。在治理进入深水区时，就会啃到因“SQL腐化”引入的复杂SQL治理这种硬骨头。本文以一个案例为依托来看看怎样像重构Java等高级编程语言一样来重构SQL。

二、JDL路由系统复杂SQL治理案例

路由规划是为保障客户体验，依据产品需求及时效目标，设计物流网络中每个节点的操作时长，然后通过节点互相串联保障全程链通且综合最优，同步输出规划方案并指导运营现场操作，双向校验优化，实现路由规划与实际运营的不断趋合。

简言之，路由系统支持的路由规划就是在做基于物流网络运营的运筹优化，网络是基础。而网络的基础又是线路，必然对线路的操作会“千奇百怪”。

1.问题SQL

select count(*) total_count from (select * from (select a.line_store_goods_id as line_resource_id, a.group_num as group_num, a.approval_erp as approval_erp, a.approval_person as approval_person, a.approval_status as approval_status, a.approval_time as approval_time, a.approval_remark as approval_remark, a.master_slave as master_slave, a.parent_line_code as parent_line_code, a.remarks as remarks, a.operator_time, a.same_stowage as same_stowage, b.start_org_id, b.start_org_name, b.start_province_id, b.start_province_name, b.start_city_id, b.start_city_name, b.start_node_code, b.start_node_name, b.end_org_id, b.end_org_name, b.end_province_id, b.end_province_name, b.end_city_id, b.end_city_name, b.end_node_code, b.end_node_name, b.depart_time, b.arrive_time, b.depart_wave_code, b.arrive_wave_code, b.enable_time, b.disable_time, a.store_enable_time, a.store_disable_time, a.update_name operator_name, b.line_code, b.line_type, b.transport_type, IF(a.store_enable_time > b.enable_time, IF(a.store_enable_time > c.enable_time, a.store_enable_time, c.enable_time), IF(b.enable_time > c.enable_time, b.enable_time, c.enable_time)) as insect_start_time, IF(a.store_disable_time < b.disable_time, IF(a.store_disable_time < c.disable_time, a.store_disable_time, c.disable_time), IF(b.disable_time < c.disable_time, b.disable_time, c.disable_time)) as insect_end_time FROM (select * FROM line_store_goods WHERE yn = 1 and master_slave = 1) a join (select start_org_id, start_org_name, start_province_id, start_province_name, start_city_id, start_city_name, start_node_code, start_node_name, end_org_id, end_org_name, end_province_id, end_province_name, end_city_id, end_city_name, end_node_code, end_node_name, depart_time, arrive_time, depart_wave_code, arrive_wave_code, line_code, line_type, transport_type, min(enable_time) as enable_time, max(disable_time) as disable_time from line_resource where line_code in (select line_code from line_store_goods WHERE yn = 1 ) and yn=1 group by line_code) b ON a.line_code = b.line_code and a.start_node_code = b.start_node_code join (select line_code,start_node_code, min(enable_time) as enable_time, max(disable_time) as disable_time from line_resource WHERE yn = 1 group by line_code) c ON a.parent_line_code = c.line_code and a.start_node_code = c.start_node_code) temp WHERE start_node_code = '311F001' and disable_time > '2023-11-15 00:00:00' and enable_time < disable_time) t_total;

这是一段运行在生产上的复杂SQL案例，通过慢SQL指标统计识别出来。一眼看过去毫无头绪（说明不仅性能差，而且可读性差，那么必然可维护性差），非功能性指标总是存在很强的关联性。

2.开始治理

step1.格式化

对工程人员而言：要重构，格式化很重要，保证一定的可读性。

select count(*) total_count from (select * from (select a.line_store_goods_id as line_resource_id, a.group_num as group_num, a.approval_erp as approval_erp, a.approval_person as approval_person, a.approval_status as approval_status, a.approval_time as approval_time, a.approval_remark as approval_remark, a.master_slave as master_slave, a.parent_line_code as parent_line_code, a.remarks as remarks, a.operator_time, a.same_stowage as same_stowage, b.start_org_id, b.start_org_name, b.start_province_id, b.start_province_name, b.start_city_id, b.start_city_name, b.start_node_code, b.start_node_name, b.end_org_id, b.end_org_name, b.end_province_id, b.end_province_name, b.end_city_id, b.end_city_name, b.end_node_code, b.end_node_name, b.depart_time, b.arrive_time, b.depart_wave_code, b.arrive_wave_code, b.enable_time, b.disable_time, a.store_enable_time, a.store_disable_time, a.update_name operator_name, b.line_code, b.line_type, b.transport_type, IF(a.store_enable_time > b.enable_time, IF(a.store_enable_time > c.enable_time, a.store_enable_time, c.enable_time), IF(b.enable_time > c.enable_time, b.enable_time, c.enable_time)) as insect_start_time, IF(a.store_disable_time < b.disable_time, IF(a.store_disable_time < c.disable_time, a.store_disable_time, c.disable_time), IF(b.disable_time < c.disable_time, b.disable_time, c.disable_time)) as insect_end_time FROM (select * FROM line_store_goods WHERE yn = 1 and master_slave = 1) a join (select start_org_id, start_org_name, start_province_id, start_province_name, start_city_id, start_city_name, start_node_code, start_node_name, end_org_id, end_org_name, end_province_id, end_province_name, end_city_id, end_city_name, end_node_code, end_node_name, depart_time, arrive_time, depart_wave_code, arrive_wave_code, line_code, line_type, transport_type, min(enable_time) as enable_time, max(disable_time) as disable_time from line_resource where line_code in (select line_code from line_store_goods WHERE yn = 1 ) and yn=1 group by line_code) b ON a.line_code = b.line_code and a.start_node_code = b.start_node_code join (select line_code,start_node_code, min(enable_time) as enable_time, max(disable_time) as disable_time from line_resource WHERE yn = 1 group by line_code) c ON a.parent_line_code = c.line_code and a.start_node_code = c.start_node_code) temp WHERE start_node_code = '311F001' and disable_time > '2023-11-15 00:00:00' and enable_time < disable_time) t_total;

经过格式化之后，能简单判断出SQL的功能是检索满足某条件的线路数量统计。

注意：格式化作为一个重要的工具可以在任意阶段发生作用。

step2.分层拆解

level0

select count(*) total_count from t_total

level1 - t_total

select * from temp WHERE start_node_code = '311F001' and disable_time > '2023-11-15 00:00:00' and enable_time < disable_time

level2 - temp

select a.line_store_goods_id as line_resource_id, a.group_num as group_num, a.approval_erp as approval_erp, a.approval_person as approval_person, a.approval_status as approval_status, a.approval_time as approval_time, a.approval_remark as approval_remark, a.master_slave as master_slave, a.parent_line_code as parent_line_code, a.remarks as remarks, a.operator_time, a.same_stowage as same_stowage, b.start_org_id, b.start_org_name, b.start_province_id, b.start_province_name, b.start_city_id, b.start_city_name, b.start_node_code, b.start_node_name, b.end_org_id, b.end_org_name, b.end_province_id, b.end_province_name, b.end_city_id, b.end_city_name, b.end_node_code, b.end_node_name, b.depart_time, b.arrive_time, b.depart_wave_code, b.arrive_wave_code, b.enable_time, b.disable_time, a.store_enable_time, a.store_disable_time, a.update_name operator_name, b.line_code, b.line_type, b.transport_type, IF(a.store_enable_time > b.enable_time, IF(a.store_enable_time > c.enable_time, a.store_enable_time, c.enable_time), IF(b.enable_time > c.enable_time, b.enable_time, c.enable_time)) as insect_start_time, IF(a.store_disable_time < b.disable_time, IF(a.store_disable_time < c.disable_time, a.store_disable_time, c.disable_time), IF(b.disable_time < c.disable_time, b.disable_time, c.disable_time)) as insect_end_time FROM join_table

level3 - join_table

(select * FROM line_store_goods WHERE yn = 1 and master_slave = 1) a join (select start_org_id, start_org_name, start_province_id, start_province_name, start_city_id, start_city_name, start_node_code, start_node_name, end_org_id, end_org_name, end_province_id, end_province_name, end_city_id, end_city_name, end_node_code, end_node_name, depart_time, arrive_time, depart_wave_code, arrive_wave_code, line_code, line_type, transport_type, min(enable_time) as enable_time, max(disable_time) as disable_time from line_resource where line_code in (select line_code from line_store_goods WHERE yn = 1 ) and yn=1 group by line_code) b ON a.line_code = b.line_code and a.start_node_code = b.start_node_code join (select line_code,start_node_code, min(enable_time) as enable_time, max(disable_time) as disable_time from line_resource WHERE yn = 1 group by line_code) c ON a.parent_line_code = c.line_code and a.start_node_code = c.start_node_code

level4 - a,b,c

select * FROM line_store_goods WHERE yn = 1 and master_slave = 1select start_org_id, start_org_name, start_province_id, start_province_name, start_city_id, start_city_name, start_node_code, start_node_name, end_org_id, end_org_name, end_province_id, end_province_name, end_city_id, end_city_name, end_node_code, end_node_name, depart_time, arrive_time, depart_wave_code, arrive_wave_code, line_code, line_type, transport_type, min(enable_time) as enable_time, max(disable_time) as disable_time from line_resource where line_code in (select line_code from line_store_goods WHERE yn = 1 ) and yn=1 group by line_code

select line_code,start_node_code, min(enable_time) as enable_time, max(disable_time) as disable_time from line_resource WHERE yn = 1 group by line_code

step3.重构

对于Java程序员而言，《重构 - 改善既有代码的设计》一书应该不陌生。重构的核心在设计原则（“道”&“法”）；但是工具包（“术”）同样重要，指导具体落地。

工具包准备：

层级合并：减少临时表个数条件下推：减少检索行数&临时表大小join优化：减少检索行数&临时表大小子查询删除：减少临时表个数子查询与join的相互转换：减少检索行数

重构1 - 层级合并

level0 & level1

如下两个SQL执行效果一致，但是性能表现会有很大差异。

select count(*) total_count from (select * from temp where a = "1")select count(*) from temp where a = "1"

第二种方式的性能表现会更好一些。原因如下：

1）减少查询计算开销：在第二种方式中，直接对表进行 count(*) 统计，不需要额外的子查询和临时表操作，可以减少计算的开销。

2）减少内存占用：第一种方式需要在内存中创建一个临时表来存储子查询的结果，而第二种方式直接对原表进行统计，不需要额外的内存占用。

3）减少磁盘 IO：第二种方式可以直接利用表的索引进行 count(*) 统计，而第一种方式可能需要额外的磁盘 IO 来处理子查询和临时表的操作。

因此，一般情况下，推荐使用第二种方式来进行 count()统计，以获得更好的性能表现。当然，在实际情况中，也需要根据具体的业务场景和数据量来综合考虑，有时候使用子查询的方式也是必要的，但总体来说，直接对原表进行 count() 统计会更高效。

重构2 - 条件下推

start_node_code = '311F001' 直接下推至level4

SQL的执行是流程化的，从执行层视角看，涉及时空资源消耗最关键的有两类：1-时间（行记录扫描）、2-空间（临时表）。

简化来看，问题SQL的执行过程是子查询形成临时表，而后基于临时表做各种形式的计算（过滤、联合）。

通过条件下推，可以将过滤动作尽可能前置，减少后续过程临时表的大小。

重构3 - join优化

按个人喜好进行格式化：

条件下推：

剥离冗余字段，冗余字段在SQL优化过程中是一个影响易读性的干扰信息，剥离冗余字段给工程人员一个干净的画板来尽情施为。

删除无效条件。join的on条件中start_node_code条件因为条件下推已经不再是有效条件。注意，此处为了行文方便做了一定的简化，理论上之前的剥离冗余字段理论上需要包含start_node_code字段查询，在此步骤之后变为冗余字段后被剥离

删除无效子查询。此时从上往下看，表a和表b存在一个奇怪的现象 - 使用了两个类似功能（子查询和join），两者的功能完全一致。题外话：此案例作为反面教材真心不错。涉及两者的优劣决策，个人做取舍的两个点是性能和可读性。在此案例中功能实现场景特别简单，join的可读性明显更好，在条件限定后扫描行数基本一致，但子查询多一个临时表；综合考量会删除子查询。

合并冗余join。继续从上往下看，表b和表c看起来一模一样。再次重复题外话：此案例作为反面教材真心不错。

等价条件替换，再次删除冗余字段。

经过优化后的join语句，可读性发生了很大的变化 - 简单的双表关联查询。

step4.结果的理论验证

select count(*) from ( (select line_code FROM line_store_goods WHERE yn = 1 and parent_line_code = line_code and master_slave = 1 and start_node_code = '311F001') a join (select line_code, min(enable_time) as enable_time, max(disable_time) as disable_time from line_resource where yn=1 and start_node_code = '311F001' group by line_code) b ON a.line_code = b.line_code) where disable_time > '2023-11-15 00:00:00' and enable_time < disable_time

重构后的SQL具备良好的可读性，基于此很容易反推出SQL的业务功能。基于此与其理论应用场景做是否匹配的理论判断很重要。有的时候生产上的SQL不一定是正确的，因为部分场景下可用性并不完全等价于正确性。

step5.索引优化

大量索引优化的文章可参考，此处不再赘述。

step6.结果的测试验证

与代码重构一样，测试通过永远是变更的正确性保证。较为特殊的是SQL改造后功能测试和性能测试都是必要的。

3.效果对比

三、写在最后

重构的原则具备普适性，但是工具包每个人都有自己用得顺手的一套，没必要完全趋同。

另外，上面的技术能不用就不用，好的前置设计胜过事后的十八般武艺。

作者丨京东物流 崔立群

来源丨京东云开发者社区 自猿其说Tech （cnblogs.com/Jcloud/p/17994104）

dbaplus社群欢迎广大技术人员投稿，投稿邮箱：editor@dbaplus.cn

社死！刚入职就把数据库删了，事故原因尴尬又奇葩……

2024-09-27 10:07·dbaplus社群

一、offer收割机，就职新公司

5年前的就业环境非常好，当时面试了很多家公司，收到了很多 offer。最终我决定入职一家互联网教育公司，新的公司福利非常好，各种零食随便吃，据说还能正点下班，一切都超出我的期望，“可算让我找着神仙公司了”，我的心里一阵窃喜。

在熟悉环境之后，我趁着上厕所的时候，顺便去旁边的零食摊挑了点零食。接下来的一天里，我专注地配置开发环境、阅读新人文档，当然我也不忘兼顾手边的零食。

入职几天后 ，领导给安排了一个小需求，我和同事沟通完技术方案后，就开始开发了。

二、单元测试有点奇怪

完成开发后，我决定写个单元测试验证下，在研究单元测试代码后，我发现这种单测写法和我之前的写法不太一样。

这家公司的单测好像没有启动整个项目，仅加载了部分类，而且不能访问测试环境数据库~ 于是我决定按照前东家写单测的方式重新写单元测试。

于是我新增了一个单测基类，在单测中启动整个SpringBoot，直接访问测试环境数据库。然而也并不是很顺利，启动阶段总是会遇到各种异常报错，需要一个一个排查…… 所幸项目排期不紧张，还有充足时间。

我做梦也没有想到，此刻，已经铸成大错。

三、故障现场

我身边的工位旁慢慢地聚集了越来越多的人，本来我还在安安静静的调试单元测试，注意力不自觉的被吸引了过去。

“测试环境为什么这么多异常，访问不通啊。到处都是 500 报错”，不知道谁在说话。

“嗯，我们还在排查，稍等一下”，我旁边的同事一边认真排查日志，一边轻声回复道。

“为什么数据库报的异常是， 查不到数据呢？” ，同事在小声嘀咕，然后打开 命令行，立即登上 MySQL。

我亲眼看着他在操作，奇怪的是数据库表里的数据全部被删掉了，其他的几个表数据也都被删除了。

简直太奇怪了，此刻的我还处于吃瓜心态。

有一个瞬间我在考虑，是否和我执行的单元测试有关系？但我很快就否决掉了这个想法，因为我只是在调试单元测试，我没有删数据库啊，单测里也不可能删库啊。我还在笑话自己 胡思乱想……

很快 DBA 就抱着电脑过来，指着电脑说，你们看这些日志，确实有人把这些表删除了。

"有 IP 吗，定位下是谁删除的， 另外线上环境有问题吗？”，旁边的大组长过来和 DBA 说。

“嗯，我找到ip 了，我找运维看下，这个ip是谁的”。DBA 回复道。

四、庭审现场

当 DBA 找到我的时候，我感到无辜和无助，我懵逼了，我寻思我啥也没干啊，我怎么可能删库呢。（他们知道我刚入职，我现在怀疑：那一刻他们可能会怀疑 我是友商派过来的卧底、间谍，执行删库的秘密任务）

经过一系列的掰扯和分析，最终定位 确实是我新增的单元测试把数据库删了。

五、故障原因

需要明确的是，原单元测试执行时不会删除数据库；测试环境启动时也不会删除数据库。

只要在单元测试中连接测试数据库，就会删除掉数据库的所有数据。为什么呢？

1、为什么单元测试删除了所有数据？

原单元测试 使用的是 H2 内存数据库，即Java 开发的嵌入式(内存级别)数据库，它本身只是一个类库，也就是只有一个 jar 文件，可以直接嵌入到项目中。H2数据库又被称为内存数据库，因为它支持在内存中创建数据库和表。所以如果我们使用H2数据库的内存模式，那么我们创建的数据库和表都只是保存在内存中，一旦应用重启，那么内存中的数据库和表就不存在了。 所以非常适合用来做单元测试。

H2 数据库在启动阶段，需要执行用户指定的 SQL 脚本，脚本中一般包含表创建语句，用来构建需要使用的表。

但是我司的 SQL 脚本除了创建表语句，还包含了删除表语句。即在创建表之前先删除表。为什么呢？据他们说，是因为这个 SQL 脚本可能会重复执行，当重复执行时创建表语句 会报错。所以他们在创建表之前，先尝试删除表。这样确保 SQL 脚本可重复执行。（ 其实可以用 Create if not exists ）

故障的原因就是：测试数据库执行了这个删表再建表的 SQL 脚本，导致所有数据都被清除了。

2、为什么测试数据库会执行这条 SQL 脚本呢？

1） 我新建的单元测试把H2 内存数据库换成了测试数据库。

2） spring.data.initialize=默认值为 true；默认情况下，会自动执行 sql 脚本。

所以测试数据库 执行了 SQL 脚本。

3、为什么在测试环境正常启动时，没有问题，不会删除所有数据呢？

只有单测引入测试数据库才会出问题，在测试环境正常启动项目是没问题的。

当编译项目时，测试目录下的文件、代码和正式代码编译后的结果不会放到一起。因为 SQL脚本被放在了 测试目录下， 所以正式代码在测试环境启动时，不会执行到这个 SQL脚本，自然不会有问题。

六、深刻教训

最终数据被修复了，DBA有测试数据库的备份，然而快照并非实时的，不可避免地还是丢失了一部分数据。

所幸的是出问题的是测试环境，并非线上环境。否则，我会不会被起诉，也未可知。

后续的改进措施包括：

收回了数据库账户的部分权限，只有管理账户才可以修改数据库表结构。代码中执行 DML语句的账户不允许执行 DDL 语句。DBA 盘点测试数据库的快照能力，确保快照间隔足够短，另外新增一个调研课题：删库后如何快速恢复，参照下其他公司的方案。所有的项目 spring.data.initialize 全部声明为 false。不自动执行 SQL 脚本SQL脚本一律不许出现 删除表的语句。SQL不能重复执行的问题，想其他办法解决。另外的一个项目急需人手，把新来的那谁 调到其他项目上

这可能是程序员们在技术上越来越保守的原因……不经意的一个调整可能引发无法承受的滔天巨浪

作者丨五阳

来源丨网址：https://juejin.cn/post/7412490391935893541

*本文首发于稀土掘金，dbaplus社群已取得作者授权，进行原创发布。欢迎广大技术人员投稿，投稿邮箱：editor@dbaplus.cn

活动推荐

为了和大家一起探索AI相关技术在大数据、数据资产管理、数据库、运维等领域的最佳落地方式，挖掘由此激发的软件发展和技术进步，第九届DAMS中国数据智能管理峰会将于2024年11月29日在上海举办，携手一众产学研界技术领跑单位，带来新思路、重实践、可落地的全日干货盛宴。

活动详情：