今天看到一条sql，感觉不太好优化，放出来集思广益…
问题sql

EXPLAIN SELECT
	x.id,
	x.version,
	x.order_no1,
	x.order_status,
	x.delivery_status,
	x.delivery_time,
	y.delivery_status AS _delivery_status,
	y.unload_time AS _unload_time 
FROM
	fsl_order_base x
	JOIN (
	SELECT
		( CASE WHEN delivery_qty = qty THEN 2 WHEN delivery_qty < qty THEN 1 ELSE 0 END ) delivery_status,
		order_no1,
		unload_time 
	FROM
		(
		SELECT
			SUM( t2.box_qty ) delivery_qty,
			SUM( t1.qty ) qty,
			t.order_no1,
			MAX( t4.unload_time ) unload_time 
		FROM
			fsl_order_base t
			LEFT JOIN fsl_order_base_line t1 ON t.id = t1.order_base
			LEFT JOIN fsl_order_unit_box t2 ON t2.order_base_line = t1.id
			JOIN (
			SELECT
				id,
				dest,
				shipment,
				order_unit,
				unload,
				unload_time 
			FROM
				fsl_order_movement_unit 
			WHERE
				unload = TRUE 
				AND datediff( now( ), unload_time ) < 30 
			) t4 ON t2.order_unit = t4.order_unit
			JOIN fsl_shipment t6 ON t4.shipment = t6.id 
			AND t6.is_a_shipment = 'Y'
			JOIN fsl_shipment_stop t5 ON t5.id = t4.dest 
			AND t5.location = t1.dest 
		WHERE
			t.order_status IN ( '1', '2' ) 
			AND ( t.delivery_status <> '2' OR t.delivery_time IS NULL ) 
			AND t.project_code = 'FS' 
		GROUP BY
			t.order_no1 
		) a 
	) y ON x.order_no1 = y.order_no1 
	AND x.project_code = 'FS' 
	AND ( x.delivery_status <> 2 OR x.delivery_time IS NULL ) 
	AND ( x.delivery_status <> y.delivery_status OR x.delivery_time <> y.unload_time )

执行计划如下：

大佬们给个优化意见和思路？