SQL查询性能优化求助：改写后记录不符且性能未提升

我正在优化一段执行耗时过长的SQL查询，尝试过用CTE改写，但要么性能没改善，要么返回的记录数和原查询不一致。

原查询 #

SELECT 
  DISTINCT A_OMSCHRIJVING, 
  LokaalAll.LK_CODE, 
  LokaalAll.LK_KAMER_FK 
FROM 
  Lokaal LokaalOri 
  inner join lokaal LokaalAll on (
    LokaalAll.LK_KAMER_FK = LokaalOri.LK_KAMER_FK
  ) 
  inner join agendalesdata on (ALD_LOKAAL_FK = LokaalAll.LK_ID) 
  Inner join agendapunt on (APU_agendalesdata_FK = ALD_ID) 
  inner join agendaitems on (AIT_AGENDAPUNT_FK = APU_ID) 
  inner join agenda on (
    AIT_AGENDA_FK = A_ID 
    and A_TYPEAGENDA = 6
  ) 
WHERE 
  (
    LokaalOri.LK_ID in (
      11, 13, 15, 16, 180, 183, 184, 185, 186, 
      189, 190, 191, 192, 195, 196, 198, 199, 
      200, 202, 206, 210, 211, 212, 213, 278, 
      282, 286, 287, 290, 291, 293, 298, 302, 
      303, 309, 310, 346, 367, 368, 382, 387, 
      540, 542, 543, 549, 551, 554, 555
    )
  )  
  AND (APU_TOT >= '2023-04-27 14:45:00') 
  AND (APU_VAN < '2023-04-27 15:35:00') 
ORDER BY 
 AIT_ID

性能瓶颈分析 #

我认为性能问题出在这段自连接逻辑上：

Lokaal LokaalOri 
inner join lokaal LokaalAll on (
  LokaalAll.LK_KAMER_FK = LokaalOri.LK_KAMER_FK
) 

尝试的改写查询 #

WITH t1 as ( select
  LK_ID, 
  LK_CODE, 
  LK_KAMER_FK 
FROM 
  Lokaal
  WHERE 
  (
    LK_ID in (
      11, 13, 15, 16, 180, 183, 184, 185, 186, 
      189, 190, 191, 192, 195, 196, 198, 199, 
      200, 202, 206, 210, 211, 212, 213, 278, 
      282, 286, 287, 290, 291, 293, 298, 302, 
      303, 309, 310, 346, 367, 368, 382, 387, 
      540, 542, 543, 549, 551, 554, 555
    )
  ) 
)
 select  A_OMSCHRIJVING, 
  t1.LK_CODE, 
  t1.LK_KAMER_FK from t1
  inner join lokaal LokaalAll on (
    LokaalAll.LK_KAMER_FK = t1.LK_KAMER_FK
  ) 
    inner join agendalesdata on (ALD_LOKAAL_FK = t1.LK_ID) 
      Inner join agendapunt on (APU_agendalesdata_FK = ALD_ID)     
  inner join agendaitems on (AIT_AGENDAPUNT_FK = APU_ID) 
  inner join agenda on (
    AIT_AGENDA_FK = A_ID 
    and A_TYPEAGENDA = 6
  ) 
  WHERE 

   (APU_TOT >= '2023-04-27 14:45:00') 
  AND (APU_VAN < '2023-04-27 15:35:00')  
ORDER BY 
  AIT_ID

但这个改写后的查询返回的记录数和原查询不一致。注：不强制使用CTE。

优化方案及结果修正 #

问题根源 #

改写查询和原查询的核心差异在于：

• 原查询中agendalesdata关联的是LokaalAll.LK_ID，也就是所有和目标LK_KAMER_FK关联的Lokaal记录
• 改写查询中agendalesdata关联的是t1.LK_ID，仅为初始筛选的Lokaal记录
  这种关联范围的差异直接导致了记录数不一致。

优化后的查询 #

先筛选出符合条件的LK_KAMER_FK集合，再关联相关表，保留原查询逻辑的同时提升性能：

SELECT 
  DISTINCT a.A_OMSCHRIJVING, 
  l.LK_CODE, 
  l.LK_KAMER_FK 
FROM 
  -- 先获取目标LK_KAMER_FK集合，减少后续关联数据量
  (SELECT DISTINCT LK_KAMER_FK FROM Lokaal WHERE LK_ID IN (
      11, 13, 15, 16, 180, 183, 184, 185, 186, 
      189, 190, 191, 192, 195, 196, 198, 199, 
      200, 202, 206, 210, 211, 212, 213, 278, 
      282, 286, 287, 290, 291, 293, 298, 302, 
      303, 309, 310, 346, 367, 368, 382, 387, 
      540, 542, 543, 549, 551, 554, 555
  )) AS target_kamer
  -- 关联所有同属该KAMER的Lokaal记录，和原查询逻辑一致
  INNER JOIN Lokaal l ON l.LK_KAMER_FK = target_kamer.LK_KAMER_FK
  -- 关联日程数据，使用l.LK_ID匹配原查询逻辑
  INNER JOIN agendalesdata ald ON ald.ALD_LOKAAL_FK = l.LK_ID
  INNER JOIN agendapunt apu ON apu.APU_agendalesdata_FK = ald.ALD_ID
  INNER JOIN agendaitems ait ON ait.AIT_AGENDAPUNT_FK = apu.APU_ID
  INNER JOIN agenda a ON ait.AIT_AGENDA_FK = a.A_ID AND a.A_TYPEAGENDA = 6
WHERE 
  apu.APU_TOT >= '2023-04-27 14:45:00' 
  AND apu.APU_VAN < '2023-04-27 15:35:00'
ORDER BY 
  ait.AIT_ID

额外性能建议 #

1. 给以下字段创建复合索引，覆盖筛选和关联逻辑：
   • Lokaal(LK_ID, LK_KAMER_FK)
   • Lokaal(LK_KAMER_FK, LK_ID, LK_CODE)
   • agendalesdata(ALD_LOKAAL_FK, ALD_ID)
   • agendapunt(APU_agendalesdata_FK, APU_ID, APU_VAN, APU_TOT)
   • agendaitems(AIT_AGENDAPUNT_FK, AIT_ID, AIT_AGENDA_FK)
   • agenda(A_ID, A_TYPEAGENDA, A_OMSCHRIJVING)
2. 如果DISTINCT是因为多表关联产生重复数据，可以考虑先在子查询中对日程数据去重，再关联Lokaal表，减少后续处理的数据量。

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