SQL Server性能优化——审计表重构

哇，50亿条数据的审计表加上200多个触发器同步写入，这绝对是个让人头大的性能瓶颈。我来分享一些实战过的优化和重构思路，分几个维度给你拆解：

这是当前最紧急的问题，毕竟200个触发器同步写入直接影响主业务的响应速度：

• 简化触发器逻辑，批量写入：很多触发器会犯“逐行处理”的错误，一定要改成批量插入。比如把原来的DECLARE @xxx ... INSERT INTO AuditTable VALUES(...)改成INSERT INTO AuditTable (TableName, ColumnName, ...) SELECT '原表名', inserted.ColumnName, ... FROM inserted，这样不管原操作是单条还是批量，触发器都能一次处理，减少日志生成和锁竞争。另外，触发器里别额外加事务——它本身就运行在原业务的事务里，多事务只会加重负担。
• 砍掉冗余索引，优化索引策略：你现在的非聚集索引太多了（TableName、ColumnName、Control、BeforeValue、CompanyCode全是非聚集），50亿条数据下，每个索引插入时都要维护，这会把写入速度拖垮。先做个查询分析：哪些索引是实际查询必须的？比如BeforeValue是varchar(500)，做非聚集索引完全没必要——索引键太长，维护成本极高，除非你每天都要精确匹配这个字段，否则立刻删掉。然后把多个常用查询的列组合成覆盖索引，比如如果经常按CompanyCode + TableName + DateChanged查变更记录，就建：

  CREATE NONCLUSTERED INDEX IX_Audit_Company_Table_Date 
  ON AuditTable(CompanyCode, TableName, DateChanged)
  INCLUDE(ColumnName, Control, BeforeValue, AfterValue, ChangedBy)
  

  这样既满足查询需求，又减少了索引数量。另外，如果主键Sequence是自增的，确保它是聚集索引——顺序插入不会产生页分裂，比随机插入快太多。
• 立刻改成分区表：50亿条数据不分区简直是灾难。按DateChanged分区是最合理的（比如按季度/月），这样写入时只操作当前分区，查询时可以只扫描指定分区，归档旧数据时直接拆分分区就行（不用做大量DELETE操作，避免生成巨量日志）。创建分区函数和分区方案的示例：

  -- 创建分区函数（按年分区）
  CREATE PARTITION FUNCTION PF_Audit_Date(date)
  AS RANGE RIGHT FOR VALUES ('2022-01-01', '2023-01-01', '2024-01-01')
  -- 创建分区方案
  CREATE PARTITION SCHEME PS_Audit_Date
  AS PARTITION PF_Audit_Date ALL TO ([PRIMARY])
  -- 把现有表切换到分区表（如果是新表直接用分区方案创建）
  CREATE CLUSTERED INDEX IX_Audit_Sequence 
  ON AuditTable(Sequence)
  WITH (DROP_EXISTING = ON) ON PS_Audit_Date(DateChanged)
  

• 降低日志开销：如果审计数据允许一定的容错（当然审计一般要求可靠，这个要谨慎评估），可以把数据库恢复模式改成批量日志恢复模式，在批量插入时会减少日志生成。另外，插入时用INSERT ... WITH (TABLOCK)触发大容量日志模式，进一步降低日志量。

写入问题解决后，查询慢的问题也要跟进：

• 强制分区消除：用分区表后，查询时一定要带DateChanged的范围条件，让SQL Server只扫描需要的分区，比如SELECT * FROM AuditTable WHERE DateChanged BETWEEN '2023-01-01' AND '2023-12-31' AND CompanyCode='001'，避免全表扫描。
• 覆盖索引优先：刚才提到的覆盖索引要优先用，避免“书签查找”——也就是查询时先找索引，再回表取数据的开销。如果经常查某个表的字段变更，就建针对性的覆盖索引。
• **别用SELECT ***：只查需要的列，减少数据传输和IO开销，尤其是大字段BeforeValue和AfterValue，不需要的时候别选。
• 旧分区用列存储索引：如果归档的旧审计数据很少更新，只做查询，可以在旧分区上创建聚集列存储索引——列存储的压缩比能到10:1甚至更高，查询大批次数据时性能比行存储好太多。注意：活跃分区还是用行存储，因为列存储写入性能不如行存储。

如果想从根源上解决触发器写入的性能问题，就得重构架构：

• 异步写入审计数据：把触发器同步写入改成异步——触发器里不直接插审计表，而是把审计数据写入SQL Server的Service Broker（内置消息队列，不用额外组件），或者外部的Kafka/RabbitMQ，然后用后台服务消费队列，批量插入审计表。这样原业务的触发器执行时间极短，完全不影响主业务性能。
• 拆分审计表：别把所有表的审计数据都塞一张表里，按业务模块或者TableName拆分，比如分成Audit_Orders、Audit_Users等表，每个表的数据量减少，索引维护和查询的开销都会降低。如果公司数量多，也可以按CompanyCode拆分，但要注意维护成本。
• 定期归档旧数据：审计数据一般不需要实时访问，比如超过3年的旧数据可以归档到廉价存储（比如本地归档硬盘），或者单独的历史数据库。用分区表的话，归档超级简单：把旧分区切换到一个历史表，备份历史表到归档存储，再清空历史表就行，全程几乎无日志生成。
• 改用专业审计工具：如果条件允许，换掉触发器方案，用SQL Server自带的SQL Server Audit功能，或者第三方审计工具。这些工具基于数据库日志做审计，不需要触发器，性能好太多，还能覆盖更多场景（比如DDL操作、登录审计等），避免触发器带来的性能开销。

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