SQL查询优化案例-优快云博客

本文介绍了一个实际场景中的SQL查询优化案例。通过调整SQL语句结构和去除不必要的子查询，将查询时间从74.031秒大幅缩短到0.359秒。对比了优化前后SQL的具体差异，并说明了数据量增大时查询效率的重要性。

主要随着数据的增加，用到in的，消耗时间几何增长。由于在前期测试评估不到位，没有查看具体的代码。测试版数据量跟线上的数据差距很大，一般很难看出。对于涉及多表复杂查询的功能需要特别留意。

优化前线上74.031秒，测试版0.031秒。随着数据量增加而增加。

select *

  from (select "Extent1".*,
               row_number() OVER(ORDER BY ID desc) as "row_number"
          from (select *
                  from (select s.id,
                               s.shipment_no,
                               s.order_no,
                               s.destination,
                               s.estimated_ship_date,
                               s.orig_warehouse_id
                          from ship_shipment s
                         Where is_deleted = 0
                           and not exists
                         (select sst.id
                                  from ship_shipment sst
                                  left join ship_picklist_item spi
                                    on spi.shipment_id = sst.id
                                  left join ship_picklist sp
                                    on sp.id = spi.picklist_id
                                 inner join (select te.type,
                                                   ti.work_effort_id,
                                                   ti.bill_id_to
                                              from trsf_work_effort te
                                             inner join trsf_work_effort_item ti
                                                on ti.work_effort_id = te.id
                                               and te.type = 13
                                             where ti.is_deleted = 0) t
                                    on t.bill_id_to = spi.id
                                 where spi.is_deleted = 0
                                   and sp.pick_type = 3
                                   and sst.id = s.id)
                           and not exists
                         (select pi.shipment_id
                                  from ship_picklist_item pi
                                  left join ship_picklist spp
                                    on spp.id = pi.picklist_id
                                  left join trsf_work_effort twe
                                    on twe.id = pi.work_effort_id
                                 where spp.pick_type = 3
                                   and twe.status != 3
                                   and pi.is_deleted = 0
                                   and pi.shipment_id = s.id
                                 group by pi.shipment_id)) ss
                 Where 1 = 1
                   and id in
                       (select i.shipment_id
                          from ship_picklist_item i
                         inner join ship_picklist sp
                            on sp.id = i.picklist_id
                           and sp.pick_type = 3
                         where i.is_deleted = 0
                         group by i.shipment_id
                        having sum(nvl(i.quantity_picked, 0)) = sum(nvl(i.quantity_sorted, 0)) and sum(nvl(i.quantity_picked, 0)) > 0)
                   and ss.ORIG_WAREHOUSE_ID = 170) "Extent1") "Extent2"
 where "row_number" > 0
   and rownum <= 20

优化后：线上0.359秒测试版：0.047秒

select *

  from (select s.id,
               s.shipment_no,
               s.order_no,
               s.destination,
               s.estimated_ship_date,
               s.orig_warehouse_id
          from ship_shipment s
         Where is_deleted = 0 
           and not exists
         (select sst.id
                  from ship_shipment sst
                  left join ship_picklist_item spi
                    on spi.shipment_id = sst.id
                  left join ship_picklist sp
                    on sp.id = spi.picklist_id
                 inner join (select te.type, ti.work_effort_id, ti.bill_id_to
                              from trsf_work_effort te
                             inner join trsf_work_effort_item ti
                                on ti.work_effort_id = te.id
                               and te.type = 13
                             where ti.is_deleted = 0) t
                    on t.bill_id_to = spi.id
                 where spi.is_deleted = 0
                   and sp.pick_type = 3
                   and sst.id = s.id)
           and not exists (select pi.shipment_id
                  from ship_picklist_item pi
                  left join ship_picklist spp
                    on spp.id = pi.picklist_id
                  left join trsf_work_effort twe
                    on twe.id = pi.work_effort_id
                 where spp.pick_type = 3
                   and twe.status != 3
                   and pi.is_deleted = 0
                   and pi.shipment_id = s.id
                 group by pi.shipment_id)
                 ) ss,
(select i.shipment_id
          from ship_picklist_item i 
         inner join ship_picklist sp
            on sp.id = i.picklist_id
           and sp.pick_type = 3
         where i.is_deleted = 0 
         group by i.shipment_id
        having sum(nvl(i.quantity_picked, 0)) = sum(nvl(i.quantity_sorted, 0)) 
        and sum(nvl(i.quantity_picked, 0)) > 0) spi 
        where ss.id = spi.shipment_id and ss.orig_warehouse_id = 1