曾经我也感觉我不会写出执行耗时特别长的SQL，直到前几天......
1、原SQL
这个SQL实际上的需求就是：根据“条件”去给done_status字段赋值，但是这个条件太复杂了。我们看到，大的方面，就是多个case（order_status取值0-11），但是有的在case的里面进行了嵌套，最深的时候嵌套了5层case。这也是执行特别耗时的原因所在。
update
super4s_order.base_order bo,
super4s_order.procurement_order po,
super4s_finance.finance_order fo
set
bo.done_status = (
case
when po.order_status = 0 then 'PROCUREMENT_ORDER_CREATED'
when po.order_status = 1 then 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAIT_APPROVE'
when po.order_status in (2,3) then 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAIT_APPROVE,PROCUREMENT_TRANSACTION_CLOSE'
when po.order_status in (11,5,10) then
case
when po.purchase_type = 2 then
case
when po.apply_pass_time is null then 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAITING_FOR_DELIVERY'
else 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAIT_APPROVE,PROCUREMENT_WAITING_FOR_DELIVERY'
end
else
case
when po.apply_pass_time is null then 'PROCUREMENT_ORDER_CREATED'
else 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAIT_APPROVE'
end
end
when po.order_status in (6,9) then
case
when po.purchase_type = 2 then
case
when po.apply_pass_time is null then 'PROCUREMENT_ORDER_CREATED'
else 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAIT_APPROVE'
end
else
case
when po.apply_pass_time is null then 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_PENDING_PAYMENT'
else 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAIT_APPROVE,PROCUREMENT_PENDING_PAYMENT'
end
end
when po.order_status = 7 then
case
when po.purchase_type = 2 then
case
when po.apply_pass_time is null
then 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAITING_FOR_DELIVERY,PROCUREMENT_PENDING_PAYMENT,PROCUREMENT_TRANSACTION_COMPLETE'
else 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAIT_APPROVE,PROCUREMENT_WAITING_FOR_DELIVERY,PROCUREMENT_PENDING_PAYMENT,PROCUREMENT_TRANSACTION_COMPLETE'
end
else
case
when po.apply_pass_time is null
then 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_PENDING_PAYMENT,PROCUREMENT_WAITING_FOR_DELIVERY,PROCUREMENT_TRANSACTION_COMPLETE'
else 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAIT_APPROVE,PROCUREMENT_PENDING_PAYMENT,PROCUREMENT_WAITING_FOR_DELIVERY,PROCUREMENT_TRANSACTION_COMPLETE'
end
end
when po.order_status = 8 then
case
when po.purchase_type =2 then
case
when po.apply_pass_time is null then
case
when po.in_stock_time is null then 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_TRANSACTION_CLOSE'
else
case
when bo.finance_code is not null && (select count(*) from super4s_finance.finance_order where status in (12,22) and finance_code = bo.finance_code) =1
then 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAITING_FOR_DELIVERY,PROCUREMENT_PENDING_PAYMENT,PROCUREMENT_TRANSACTION_CLOSE'
else 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAITING_FOR_DELIVERY,PROCUREMENT_TRANSACTION_CLOSE'
end
end
else
case
when po.in_stock_time is null then 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAIT_APPROVE,PROCUREMENT_TRANSACTION_CLOSE'
else
case
when bo.finance_code is not null && (select count(*) from super4s_finance.finance_order where status in (12,22) and finance_code = bo.finance_code) =1
then 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAIT_APPROVE,PROCUREMENT_WAITING_FOR_DELIVERY,PROCUREMENT_PENDING_PAYMENT,PROCUREMENT_TRANSACTION_CLOSE'
else 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAIT_APPROVE,PROCUREMENT_WAITING_FOR_DELIVERY,PROCUREMENT_TRANSACTION_CLOSE'
end
end
end
else
case
when po.apply_pass_time is null then
case
when bo.finance_code is not null && (select count(*) from super4s_finance.finance_order where status in (12,22) and finance_code = bo.finance_code) =1
then
case
when po.in_stock_time is null
then 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_PENDING_PAYMENT,PROCUREMENT_TRANSACTION_CLOSE'
else 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_PENDING_PAYMENT,PROCUREMENT_WAITING_FOR_DELIVERY,PROCUREMENT_TRANSACTION_CLOSE'
end
else 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_TRANSACTION_CLOSE'
end
else
case
when bo.finance_code is not null && (select count(*) from super4s_finance.finance_order where status in (12,22) and finance_code = bo.finance_code) =1
then
case
when po.in_stock_time is null
then 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAIT_APPROVE,PROCUREMENT_PENDING_PAYMENT,PROCUREMENT_TRANSACTION_CLOSE'
else 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAIT_APPROVE,PROCUREMENT_PENDING_PAYMENT,PROCUREMENT_WAITING_FOR_DELIVERY,PROCUREMENT_TRANSACTION_CLOSE'
end
else 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAIT_APPROVE,PROCUREMENT_TRANSACTION_CLOSE'
end
end
end
end
)
where
bo.order_type = 1
and
bo.base_order_code = po.base_order_code;
2、优化知道思想
- 尽量使SQL短而小，更趋于原子化。如果一个sql特别耗时，达到一定时间后，会被系统kill掉；另外，大sql执行时，后面的sql处于阻塞状态，这样会占用很多的系统资源；
- 减少case嵌套的深度。
3、优化后的SQL
因为是根据procurement_order.order_status的不同值(0-11)来给base_order.done_status赋值。所以我们首先是根据order_status对procurement_order的数据量做了统计。对于某一状态，如果数据量相对比较少，我们就不拆分sql，只有对数据量大的sql我们进行拆分。
-- 1.1.1、base_order采购单状态进度条——非完成、关闭状态
update
super4s_order.base_order bo,
super4s_order.procurement_order po
set
bo.done_status = (
case
when po.order_status = 0 then 'PROCUREMENT_ORDER_CREATED'
when po.order_status = 1 then 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAIT_APPROVE'
when po.order_status in (2,3) then 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAIT_APPROVE,PROCUREMENT_TRANSACTION_CLOSE'
when po.order_status in (11,5,10) then
case
when po.purchase_type = 2 then
case
when po.apply_pass_time is null then 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAITING_FOR_DELIVERY'
else 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAIT_APPROVE,PROCUREMENT_WAITING_FOR_DELIVERY'
end
else
case
when po.apply_pass_time is null then 'PROCUREMENT_ORDER_CREATED'
else 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAIT_APPROVE'
end
end
when po.order_status in (6,9) then
case
when po.purchase_type = 2 then
case
when po.apply_pass_time is null then 'PROCUREMENT_ORDER_CREATED'
else 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAIT_APPROVE'
end
else
case
when po.apply_pass_time is null then 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_PENDING_PAYMENT'
else 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAIT_APPROVE,PROCUREMENT_PENDING_PAYMENT'
end
end
end
)
where
bo.order_type = 1
and
bo.base_order_code = po.base_order_code;
-- 1.1.2、base_order采购单状态进度条——状态
update
super4s_order.base_order bo,
super4s_order.procurement_order po
set
bo.done_status = (
case
when po.purchase_type = 2 then
case
when po.apply_pass_time is null
then 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAITING_FOR_DELIVERY,PROCUREMENT_PENDING_PAYMENT,PROCUREMENT_TRANSACTION_COMPLETE'
else 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAIT_APPROVE,PROCUREMENT_WAITING_FOR_DELIVERY,PROCUREMENT_PENDING_PAYMENT,PROCUREMENT_TRANSACTION_COMPLETE'
end
when po.purchase_type = 5 then 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_TRANSACTION_COMPLETE'
else
case
when po.apply_pass_time is null
then 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_PENDING_PAYMENT,PROCUREMENT_WAITING_FOR_DELIVERY,PROCUREMENT_TRANSACTION_COMPLETE'
else 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAIT_APPROVE,PROCUREMENT_PENDING_PAYMENT,PROCUREMENT_WAITING_FOR_DELIVERY,PROCUREMENT_TRANSACTION_COMPLETE'
end
end
)
where
bo.order_type = 1
and
po.order_status = 7
and
bo.base_order_code = po.base_order_code;
-- 1.1.3、base_order采购单状态进度条——关闭状态&寄售
update
super4s_order.base_order bo,
super4s_order.procurement_order po
set
bo.done_status = (
case
when po.apply_pass_time is null then
case
when po.in_stock_time is null then 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_TRANSACTION_CLOSE'
else
case
when bo.finance_code is not null && (select count(1) from super4s_finance.finance_order where finance_code = bo.finance_code and status in (12,22)) =1
then 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAITING_FOR_DELIVERY,PROCUREMENT_PENDING_PAYMENT,PROCUREMENT_TRANSACTION_CLOSE'
else 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAITING_FOR_DELIVERY,PROCUREMENT_TRANSACTION_CLOSE'
end
end
else
case
when po.in_stock_time is null then 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAIT_APPROVE,PROCUREMENT_TRANSACTION_CLOSE'
else
case
when bo.finance_code is not null && (select count(1) from super4s_finance.finance_order where finance_code = bo.finance_code and status in (12,22)) =1
then 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAIT_APPROVE,PROCUREMENT_WAITING_FOR_DELIVERY,PROCUREMENT_PENDING_PAYMENT,PROCUREMENT_TRANSACTION_CLOSE'
else 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAIT_APPROVE,PROCUREMENT_WAITING_FOR_DELIVERY,PROCUREMENT_TRANSACTION_CLOSE'
end
end
end
)
where
bo.order_type = 1
and
po.order_status = 8
and
po.purchase_type =2
and
bo.base_order_code = po.base_order_code;
-- 1.1.4、base_order采购单状态进度条——关闭状态&非寄售
update
super4s_order.base_order bo,
super4s_order.procurement_order po
set
bo.done_status = (
case
when po.apply_pass_time is null then
case
when bo.finance_code is not null && (select count(1) from super4s_finance.finance_order where finance_code = bo.finance_code and status in (12,22)) =1
then
case
when po.in_stock_time is null
then 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_PENDING_PAYMENT,PROCUREMENT_TRANSACTION_CLOSE'
else 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_PENDING_PAYMENT,PROCUREMENT_WAITING_FOR_DELIVERY,PROCUREMENT_TRANSACTION_CLOSE'
end
else 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_TRANSACTION_CLOSE'
end
else
case
when bo.finance_code is not null && (select count(1) from super4s_finance.finance_order where finance_code = bo.finance_code and status in (12,22)) =1
then
case
when po.in_stock_time is null
then 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAIT_APPROVE,PROCUREMENT_PENDING_PAYMENT,PROCUREMENT_TRANSACTION_CLOSE'
else 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAIT_APPROVE,PROCUREMENT_PENDING_PAYMENT,PROCUREMENT_WAITING_FOR_DELIVERY,PROCUREMENT_TRANSACTION_CLOSE'
end
else 'PROCUREMENT_ORDER_CREATED,PROCUREMENT_WAIT_APPROVE,PROCUREMENT_TRANSACTION_CLOSE'
end
end
)
where
bo.order_type = 1
and
po.order_status = 8
and
po.purchase_type != 2
and
bo.base_order_code = po.base_order_code;
4、成果
优化前，35万的数据执行了35分钟；优化后，5秒内执行完毕了！如果各位对这个sql优化还有其他方面的观点，欢迎留言。