MES 大库归档实战：255GB 日志表如何瘦身

背景

公司 MES 系统（OrBitMOM 二开）的数据库跑在 SQL Server 上，用了两年多，库文件一路涨到 1.1TB，恢复模式还是 SIMPLE。最离谱的是 CommonAPI_Log 这张接口日志表，单表 255GB，而且每天还在以 2.5GB 的速度增长。后果很直观：工站查询响应变慢、备份时间逼近业务窗口、连 SSMS 打开表结构都要卡几秒。本文记录这次的归档瘦身全过程。

解决思路

大库优化不能上来就加索引，顺序应该是：先摸清家底 → 砍掉冗余数据 → 再谈索引与维护。日志类数据的特点是只写不读、量大、有明确的时间边界，天然适合归档；而索引优化的前提是表结构稳定、数据量可控。

第一步：摸清空间占用

用 sp_spaceused 逐表看太慢，直接查分区统计，一次拿到 TOP 10 大表：

SELECT TOP 10
    t.name AS TableName,
    SUM(p.rows) AS RowCount,
    CAST(SUM(a.total_pages) * 8 / 1024.0 / 1024.0 AS DECIMAL(12,2)) AS SizeGB
FROM sys.tables t
JOIN sys.partitions p ON t.object_id = p.object_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE p.index_id IN (0, 1)
GROUP BY t.name
ORDER BY SizeGB DESC;

结果不出所料：CommonAPI_Log 独占 255GB，第二名才 40GB。日志表就是第一目标。

第二步：分批归档，别一次性 DELETE

最忌讳的写法是 DELETE FROM CommonAPI_Log WHERE CreateTime < '2026-01-01'——几亿行数据一条语句删，事务日志瞬间撑爆（SIMPLE 模式下照样会爆），还会长时间持有锁，把产线工站的写入全部堵死。正确姿势是小批量循环删除：

SET NOCOUNT ON;
DECLARE @BatchSize INT = 50000, @Deleted INT = 1;
WHILE @Deleted > 0
BEGIN
    DELETE TOP (@BatchSize) FROM CommonAPI_Log
    WHERE CreateTime < '2026-01-01';
    SET @Deleted = @@ROWCOUNT;
    WAITFOR DELAY '00:00:02';  -- 给其他会话喘息时间
END

更稳妥的方案是先归档后删除：用 INSERT INTO ArchiveDB.dbo.CommonAPI_Log SELECT ... 把旧数据搬到历史库，业务方确认无查询需求后再执行上述分批删除。归档库和主库分离，主库立刻轻装上阵。

第三步：索引体检与整理

删完数据只是止损，查询慢还要查索引。先看缺失索引（SQL Server 自己统计的"后悔药"）：

SELECT migs.avg_user_impact, mid.statement,
       mid.equality_columns, mid.inequality_columns, mid.included_columns
FROM sys.dm_db_missing_index_details mid
JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
WHERE migs.avg_user_impact > 50
ORDER BY migs.avg_user_impact DESC;

再查碎片率，超过 30% 的索引考虑重建，5%~30% 之间重组即可：

SELECT OBJECT_NAME(ps.object_id) AS TableName,
       i.name AS IndexName,
       ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE ps.avg_fragmentation_in_percent > 5
ORDER BY ps.avg_fragmentation_in_percent DESC;

第四步：用 SQL Agent 维护计划兜底

手工跑一次解决不了日增 2.5GB 的问题。我给 CommonAPI_Log 加了按月份分区，并建了 SQL Agent 作业：每月初把上上月分区 SWITCH 到归档文件组、自动执行索引重组、周末做一次完整性检查。从此归档是常态，不再需要人肉值班。

踩坑记录

SIMPLE 模式≠随便删：大批量 DELETE 产生的日志在事务提交前不会截断，2 亿行的删除照样把日志文件撑大，必须分批。
归档窗口选夜班：白天删日志会阻塞工站写入，MES 报"检测结果上报失败"，被产线追着问了一下午。后来固定在凌晨 2~4 点跑。
sa 裸奔风险：排查时发现应用连接串里是 sa + 弱密码，顺手在维护窗口改了专用账号并回收了 sa 的外网访问权限。
索引不是越多越好：日志表加了一堆索引后写入变慢，后来只保留 CreateTime + 查询列的复合索引，写放大立刻降下来。

总结

大库瘦身的套路可以固化为四步：空间摸底 → 分批归档 → 索引体检 → 维护计划自动化。核心心法是"先减量、再优化"，数据量降下来之后，很多慢查询不需要动 SQL 就自己变快了。这次折腾完，1.1TB 的库降到约 700GB，CommonAPI_Log 稳定在 30GB 以内，工站查询从秒级回到毫秒级，备份时间也缩短了一半以上。

背景