Azure SQL设计咨询：小时级3000万行快照数据的存储与优化

一、旧快照的高效删除方案 #

• 分区表+Drop分区（最优方案）：
  你的last_updated_timestamp和schedule_day都是递增字段，且仅需保留20天数据，推荐按天对表进行分区（分区键选schedule_day更直观，匹配作业仅插入当天及未来3天数据的特性）。

  • 预创建分区：提前建好未来7天的分区，覆盖作业插入的当天+未来3天需求，避免插入时因分区不存在报错。
  • 清理旧数据：每天定时执行ALTER TABLE our_table DROP PARTITION partition_name;，直接移除20天前的分区。这种物理级删除无需扫描全表，执行速度极快，也不会导致事务日志暴涨。

• 批量Delete（非分区场景备选）：
  如果暂时无法改成分区表，绝对不要直接执行全表Delete，而是按store_id或dep_id分批次删除，每次处理一小部分数据：

  DELETE TOP (100000) FROM our_table
  WHERE last_updated_timestamp < DATEADD(DAY, -20, GETDATE());
  

  循环执行直到删除完毕，能减少锁表时间，避免影响报表查询。

二、删除操作是否会导致索引碎片？ #

• 若用Drop分区：完全不会产生索引碎片。分区是物理独立的存储单元，移除整个分区不会影响其他分区的索引结构，剩余分区的聚簇索引依然保持连续。
• 若用Delete语句：会产生一定程度的索引碎片。你的聚簇索引是(store_id, dep_id, schedule_day, last_updated_timestamp)，旧快照的last_updated_timestamp较小，分散在不同store_id+dep_id+schedule_day分组的索引中，删除后会在索引页留下空隙，长期积累会降低查询性能。这种情况需要定期维护索引：

  -- 重组碎片率5%-30%的索引
  ALTER INDEX ALL ON our_table REORGANIZE;
  -- 重建碎片率>30%的索引
  ALTER INDEX ALL ON our_table REBUILD;
  

三、结合业务特性的设计优化建议 #

1. 聚簇索引微调：
   你现有的(store_id, dep_id, schedule_day, last_updated_timestamp)聚簇索引已经完美适配99%的查询场景——先通过store_id+dep_id+schedule_day快速定位分组，再取该分组下最大last_updated_timestamp的行。无需调整顺序，因为同一快照的last_updated_timestamp完全相同，索引末尾的排序不会额外消耗资源，且聚簇索引直接返回全量数据，无需回表。

2. 即时清理冗余快照：
   既然新快照生成后旧快照立即失效，除了按时间保留20天，还可以在插入新快照后，直接清理同一store_id+dep_id+schedule_day下的旧快照：

   DECLARE @new_timestamp DATETIME = (SELECT MAX(last_updated_timestamp) FROM our_table WHERE ...); -- 新快照时间戳
   DELETE FROM our_table
   WHERE store_id = ? AND dep_id = ? AND schedule_day = ?
     AND last_updated_timestamp < @new_timestamp;
   

   这能大幅减少表内数据量，降低存储成本和查询扫描范围。如果用分区表，在对应schedule_day分区内执行此操作效率更高。

3. 插入性能优化：
   针对每小时3000万行的插入量，重点优化这几点：

   • 用批量插入（如BULK INSERT、SQLBulkCopy）替代单条插入，减少日志写入和网络开销。
   • 聚簇索引填充因子设为100%：因为数据仅插入不更新，无需预留页空间，能减少页分裂概率，提升插入速度。
   • 按schedule_day分区后，插入会定向到对应分区，避免跨分区写入，进一步提升性能。

4. 分区自动化管理：
   编写定时脚本，自动创建未来3天的分区、删除20天前的分区，无需人工干预。例如用SQL Server Agent（SQL Server）或Cron（MySQL）执行维护任务。

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