生产环境有条慢sql需优化，耗时609s,如下：
sum( fsl_unrelease_pack_ob_lines.qty ) AS qty_sum,
sum( fsl_unrelease_pack_ob_lines.box_qty ) AS box_qty_sum,
sum( fsl_unrelease_pack_ob_lines.release_qty ) AS release_qty_sum,
sum( fsl_unrelease_pack_ob_lines.box_qty - fsl_unrelease_pack_ob_lines.release_qty ) AS unrelease_qty_sum,
sum( fsl_unrelease_pack_ob_lines.box_qty - fsl_unrelease_pack_ob_lines.release_qty ) AS add_qty_sum,
sum( fsl_unrelease_pack_ob_lines.weight ) AS weight_sum,
sum( fsl_unrelease_pack_ob_lines.volume ) AS volume_sum
FROM
fsl_order_base_line AS fsl_unrelease_pack_ob_lines
LEFT JOIN fsl_order_base AS ob ON fsl_unrelease_pack_ob_lines.order_base = ob.id
WHERE
ob.model1 = 'pack'
AND ob.project_code = 'DD'
AND fsl_unrelease_pack_ob_lines.domain_name IN ( 'FSL' )
AND fsl_unrelease_pack_ob_lines.origin IN (
SELECT
location
FROM
fsl_zone_part t1
JOIN fsl_location t2 ON t1.zone1 = t2.id
WHERE
t2.NAME = 'xx')
》》 查看执行计划如下：
》》表数据量如下：
》》考虑优化：改写sql+强制索引
这里是考虑小表驱动大表
改写后的sql如下,需强制索引才走对应的执行计划
sum( fsl_unrelease_pack_ob_lines.qty ) AS qty_sum,
sum( fsl_unrelease_pack_ob_lines.box_qty ) AS box_qty_sum,
sum( fsl_unrelease_pack_ob_lines.release_qty ) AS release_qty_sum,
sum( fsl_unrelease_pack_ob_lines.box_qty - fsl_unrelease_pack_ob_lines.release_qty ) AS unrelease_qty_sum,
sum( fsl_unrelease_pack_ob_lines.box_qty - fsl_unrelease_pack_ob_lines.release_qty ) AS add_qty_sum,
sum( fsl_unrelease_pack_ob_lines.weight ) AS weight_sum,
sum( fsl_unrelease_pack_ob_lines.volume ) AS volume_sum
FROM
fsl_zone_part t1 JOIN fsl_location t2 ON t1.zone1 = t2.id
join
fsl_order_base_line AS fsl_unrelease_pack_ob_lines force index(index_obl_origin) on fsl_unrelease_pack_ob_lines.origin=t1.location
LEFT JOIN fsl_order_base AS ob ON fsl_unrelease_pack_ob_lines.order_base = ob.id
WHERE t2.NAME = '华南'
AND ob.model1 = 'pack'
AND ob.project_code = 'DD'
AND fsl_unrelease_pack_ob_lines.domain_name IN ( 'FSL' )
执行计划：
但耗时仍需400s，请问下有进一步优化空间？
222
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
