复杂SQL治理实践 | 京东物流技术团队

一、前言

软件在持续的开发和维护过程中，会不断添加新功能和修复旧的缺陷，这往往伴随着代码的快速增长和复杂性的提升。若代码库没有得到良好的管理和重构，就可能积累大量的技术债务，包括不一致的设计、冗余代码、过时的库和框架以及不再使用的功能。这些因素都会导致软件结构的脆弱，增加系统出错的可能性，我们俗称为“代码腐化”，持续性的重构是一种好的解决方案。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