SQL查询优化：获取薪资低于指定额的货车司机及最新位置

需求 #

编写SQL查询，获取薪资低于指定金额（1000.00）的货车司机列表，同时显示其当前所属trip的最新位置信息。

涉及表结构 #

• Pay表：主键为driver_id，需筛选pay_status='Y'的记录，获取字段driver_id、total_pay
• trip表：主键为id，需筛选status='P'的记录，获取字段id（trip_id）、driver、location
• trip_position表：无直接标识最新位置的字段，需按trip_date降序筛选每个trip的最新位置记录，涉及字段trip_id、location、trip_date

原查询及问题 #

最初的查询会返回司机的所有位置记录，而非仅最新位置：

select pay.driver_id, sum(pay.total_pay), trip.id, trip.location, trip_date
from pay 
join trip on trip.driver=pay.driver_id and trip.status='P'
join trip_position on trip.id=trip_position.trip_id 
where pay.pay_status='Y' 
group by driver_id, trip.id, trip.location, trip.date 
having sum(pay.total_pay) < 1000.00 
order by driver_id

原查询输出示例：

driver_id  total_pay  trip_id   trip.location   trip_date
bob        976        1234567   1st street      2023-04-11
bob        976        1234567   2nd street      2023-04-10
bob        976        1234567   3rd street      2023-04-09
fred       300        3344556   4th street      2023-04-11
fred       300        3344556   5th street      2023-04-10

修改后查询及性能问题 #

修改后的查询逻辑上能筛选出最新位置，但运行20分钟仍未完成，被迫终止：

select pay.driver_id, sum(pay.total_pay), trip.id, trip.location, trip_date
from pay 
join trip on trip.driver=pay.driver_id and trip.status='P'
join trip_position on trip.id=trip_position.trip_id 
     and trip_position.trip_date=( 
         select top 1 trip_date 
         from trip_position 
         where trip.id=trip_position.trip_id 
         order by trip_date desc
     )
where pay.pay_status='Y' 
group by driver_id, trip.id, trip.location, trip.date 
having sum(pay.total_pay) < 1000.00 
order by driver_id

期望输出 #

每个司机仅显示一条记录，包含总薪资、当前trip_id及最新位置：

driver_id  total_pay  trip_id   trip.location   trip_date
bob        976        1234567   1st street      2023-04-11
fred       300        3344556   4th street      2023-04-11

优化方案 #

优化后的查询语句 #

使用CTE结合窗口函数一次性计算每个trip的最新位置，避免重复子查询带来的性能损耗：

-- 预计算每个trip的最新位置记录
with latest_trip_pos as (
    select 
        trip_id, 
        location, 
        trip_date,
        row_number() over(partition by trip_id order by trip_date desc) as rn
    from trip_position
)
select 
    p.driver_id, 
    sum(p.total_pay) as total_pay, 
    t.id as trip_id, 
    ltp.location, 
    ltp.trip_date
from pay p
join trip t on t.driver = p.driver_id and t.status = 'P'
join latest_trip_pos ltp on ltp.trip_id = t.id and ltp.rn = 1
where p.pay_status = 'Y'
group by p.driver_id, t.id, ltp.location, ltp.trip_date
having sum(p.total_pay) < 1000.00
order by p.driver_id

性能优化建议 #

• 索引优化：
  • 给trip_position表创建(trip_id, trip_date desc)的联合索引，加速窗口函数的分组和排序
  • 给trip表创建(driver, status)的联合索引，提升关联效率
  • 确保Pay表的pay_status字段有索引，加快筛选速度
• 窗口函数row_number()仅对trip_position表扫描一次，相比原查询中每条记录都执行一次子查询，能大幅减少IO开销

内容的提问来源于stack exchange，提问作者john_davis