SQL优化——SQL语句拆分

曾经我也感觉我不会写出执行耗时特别长的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优化还有其他方面的观点，欢迎留言。