Azure SQL百万级数据表查询优化及等效查询建议求助

你当前用子查询/CTE结合ROW_NUMBER()的方式，在300万+数据量下性能不佳，以下是具体优化思路：

1. 建立针对性复合索引 #

这是最有效的优化手段，直接减少数据库的扫描和排序开销：
创建包含过滤条件、分区字段、排序字段的复合索引：

CREATE INDEX idx_tickets_created_id_execdate ON weekly_tickets (Created_date, ID, ExecDate DESC);

• 作用：数据库可以通过这个索引快速筛选出Created_date >= '2022-01-01'的记录，同时每个ID下的记录已经按ExecDate降序排列，窗口函数无需再额外排序，大幅降低计算开销。
• 如果需要的字段不多，可以扩展为覆盖索引（把查询需要的所有字段都加到索引里），避免回表查询：

  CREATE INDEX idx_tickets_covering ON weekly_tickets (Created_date, ID, ExecDate DESC)
  INCLUDE (Priority, Agent_Name, Urgency, Status, Agent_Group_Name, Country, ...); -- 替换为你实际需要的字段
  

2. 避免使用SELECT *，只查询需要的字段 #

原查询中SELECT *会读取所有字段，增加数据传输和内存占用。明确列出需要的字段，比如你第一个查询里的ID, Priority, Agent_Name...，减少不必要的资源消耗。

3. 替换ROW_NUMBER()为更轻量的聚合关联方式 #

如果同一个ID不会出现多条相同的最新ExecDate记录，可以用聚合+关联的方式替代窗口函数，部分数据库下性能更优：

-- 示例：取每个ID最新ExecDate的记录
SELECT t.ID, t.Priority, t.Agent_Name, ... -- 替换为实际需要的字段
FROM weekly_tickets t
INNER JOIN (
    SELECT ID, MAX(ExecDate) AS latest_execdate
    FROM weekly_tickets
    WHERE Created_date >= '2022-01-01 00:00:00'
    GROUP BY ID
) t_latest ON t.ID = t_latest.ID AND t.ExecDate = t_latest.latest_execdate
WHERE t.Created_date >= '2022-01-01 00:00:00';

• 注意：如果同一个ID存在多条ExecDate等于最大值的记录，这个查询会返回所有匹配的记录；而原ROW_NUMBER()会随机取一条（若要保持一致，可以在子查询里加额外排序字段，或者在关联后再用ROW_NUMBER()去重）。

4. 其他辅助优化 #

• 更新表统计信息：让数据库优化器生成更准确的执行计划，比如MySQL执行ANALYZE TABLE weekly_tickets;，PostgreSQL执行ANALYZE weekly_tickets;。
• 调整数据库内存配置：如果数据库频繁出现磁盘排序（查看执行计划里的Using filesort），可以增大排序缓冲区（如MySQL的sort_buffer_size，PostgreSQL的work_mem），让排序操作在内存中完成。

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