SQL Server慢查询优化求助：多表关联查询性能低下无索引建议

我遇到一条多表关联SQL查询执行缓慢的问题，已检查相关表的索引配置但未定位到原因，执行计划也未给出任何索引建议。

查询语句 #

SELECT
    e.id, e.coordinate, e.lat AS latitudine, e.lon AS longitudine, e.dataora, e.indirizzo, e.dato, e.precisione_metri, e.precisione_secondi, e.precisione_invalid, e.distanza, e.velocita, es.descrizione AS evento, es.operazione, es.colore_shape, e.dato barcode, es.gestione_euristica, e.id_dispositivo  
FROM eventi_kml_polygon AS ekp
INNER JOIN eventi AS e
    ON e.id=ekp.id_evento
INNER JOIN sistema_eventi AS es
    ON es.evento=e.id_evento
INNER JOIN kml_polygon AS kp
    ON kp.id=ekp.id_kml_polygon
INNER JOIN kml AS k
    ON k.id=kp.id_kml
INNER JOIN waypoint AS w
    ON w.id_kml=k.id
INNER JOIN waypoint_periodi AS wp
    ON (wp.id_waypoint=w.id AND e.dataora BETWEEN wp.dataora_inizio AND wp.dataora_fine)
INNER JOIN modelli AS m
    ON m.id=wp.id_modello
WHERE m.id=224882
    AND es.operazione IN (8,15)

涉及大表数据量 #

• eventi: 12,250,946条
• waypoint_periodi: 650,703条
• eventi_kml_polygon: 1,500,040条
• kml_polygon: 21,870条
• kml: 9,246条

执行计划说明 #

已生成详细执行计划，包含各表的访问方式、关联顺序及数据扫描量等信息。

优化建议 #

• 调整驱动表顺序：将过滤条件最严格的modelli表作为查询起点，依次关联waypoint_periodi、waypoint、kml等表，先缩小数据集范围再关联大表eventi，避免全表扫描大表。
• 创建针对性复合索引：
  • waypoint_periodi：(id_modello, id_waypoint, dataora_inizio, dataora_fine)——覆盖WHERE过滤、JOIN关联及范围查询字段，避免回表。
  • sistema_eventi：(evento, operazione, descrizione, colore_shape, gestione_euristica)——覆盖关联条件、过滤条件及SELECT所需字段，实现索引覆盖。
  • eventi：(id_evento, dataora, id)——覆盖与sistema_eventi的关联、与waypoint_periodi的时间范围关联及与eventi_kml_polygon的关联字段。
  • eventi_kml_polygon：(id_evento, id_kml_polygon)——覆盖两个JOIN关联字段，提升关联效率。
• 检查字段类型一致性：确认所有JOIN关联字段（如e.id与ekp.id_evento、es.evento与e.id_evento等）的数据类型完全匹配，避免隐式转换导致索引失效。
• 拆分查询缩小范围：先查询出符合m.id=224882的所有关联kml_polygonID集合，再以此为条件关联eventi_kml_polygon和eventi，减少大表的扫描范围。

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