如何优化Azure SQL DB中无主键带时间戳表的Delete操作？

针对你描述的场景——表B每日插入5000万条同TSP值的数据，需删除TSP早于前一天的旧数据（单次删除量约5000万行），以下是几种实用的优化方案：

1. 分区表（最优解） #

因为你的数据按TSP日期天然分组，按TSP日期创建分区表是效率最高的方案，删除旧数据时无需逐行删除，直接通过元数据操作截断分区，几乎瞬间完成。

实现步骤： #

• 创建分区函数，按TSP的日期范围划分分区：

  CREATE PARTITION FUNCTION pf_TSP_Date (datetime2)
  AS RANGE RIGHT FOR VALUES ('2022-12-19', '2022-12-20', '2022-12-21'); -- 可根据实际日期动态扩展
  

• 创建分区方案，将分区函数绑定到文件组（Azure SQL DB可使用默认文件组）：

  CREATE PARTITION SCHEME ps_TSP_Date
  AS PARTITION pf_TSP_Date
  ALL TO ([PRIMARY]);
  

• 重建表B（或修改现有表），将TSP列指定为分区键：

  CREATE TABLE B (
      -- 你的业务列定义
      TSP datetime2 NOT NULL
  ) ON ps_TSP_Date(TSP);
  

• 删除旧数据时，直接切换并截断对应分区（更安全）：

  -- 确定要删除的分区编号
  DECLARE @PartitionNum INT = $PARTITION.pf_TSP_Date(DATEADD(day, -1, CAST(GETDATE() AS DATE)));
  -- 将旧分区数据切换到临时表
  ALTER TABLE B SWITCH PARTITION @PartitionNum TO B_Old;
  -- 截断临时表完成删除
  TRUNCATE TABLE B_Old;
  

2. 分批删除 #

一次性删除5000万行会产生海量事务日志，导致锁表时间过长、资源占用过高。改为小批量循环删除，每次删除少量数据（如10万行），降低日志压力和锁竞争。

示例代码：

SET NOCOUNT ON;
DECLARE @BatchSize INT = 100000;
DECLARE @CutoffDate datetime2 = DATEADD(day, -1, CAST(GETDATE() AS DATE));

WHILE 1=1
BEGIN
    DELETE TOP(@BatchSize) 
    FROM B 
    WHERE TSP < @CutoffDate;

    IF @@ROWCOUNT = 0
        BREAK;
    
    -- 可选：每次删除后短暂等待，减少资源占用
    WAITFOR DELAY '00:00:01';
END

如果TSP列有非聚集索引，小批量删除时优化器会更倾向于使用索引查找，避免全表扫描，提升单批次删除效率。

3. 调整索引策略 #

你之前创建的非聚集索引效果不佳，核心原因是删除行数占比过高（约33%）：SQL Server优化器会认为全表扫描的IO成本低于“索引查找+回表删除”的成本，因此选择跳过索引。可以尝试以下调整：

• 将TSP设为聚集索引的前缀列：聚集索引的叶节点就是数据本身，删除时无需回表，直接通过聚集索引定位并删除数据。且每日插入同TSP的5000万行属于顺序插入，不会引发页分裂，对插入性能影响较小。
• 创建覆盖非聚集索引：包含表B的所有列，这样删除时无需回表，直接通过索引完成操作（注意：索引体积会较大，需权衡插入和删除的性能）：

  CREATE NONCLUSTERED INDEX IX_B_TSP_Covering
  ON B(TSP)
  INCLUDE (/* 表B的所有其他业务列 */);
  

为什么原索引效果不明显？ #

当删除的行数占表总数据量的30%以上时，SQL Server优化器通常会选择全表扫描而非索引查找。因为索引查找需要先定位到索引中的符合条件行，再通过书签（聚集索引键）回表找到数据行，这个过程的IO总量可能比直接扫描全表更高，优化器会自动选择成本更低的执行计划。

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