董黎的空间-MSSQL中导入千万级数据实践

由于工作需要，接收了一批千万级别的数据，最开始我还是想当然的直接导入到数据库，结果发现似乎没有那么简单，所以来记录一下这次数据处理过程中遇到的各类问题和对应的解决办法，再次复盘一下整体的逻辑，做到温故而知新。

一、现状

本地的项目是已经正常运行中，然后现在获取了六千万条左右的数据需要同步到项目的数据库中，实际上就是一张表（其实单表有接近七千万条数据也不太合理，需要考虑分表，当然这是后话）。数据为csv格式，单表数据量在三千万条左右，项目的数据库是微软的MSSQL。

二、规划

由于原先没有处理过这么大的数据，所以先打算问问AI，帮我分析分析（其实是按照原先导入几十万数据的模式，发现导入了一天，一张表都没有导入完毕后，才后知后觉）。经过AI和我自己的摸索，基本确定了以下步骤：

（一）数据导入

将CSV文件的数据导入到数据库，使用BCP命令

bcp Demo.dbo.SettleRec in "E:\2025.csv" -c -t, -r\n -C 
65001 -S 127.0.0.1 -U sa -P 123 -b 50000 -h "TABLOCK" -m 100

这里需要注意文件的编码（编码不一致会导致中文变成乱码）。将数据导入到一张临时表中

（二）数据整理

由于csv默认使用逗号进行分格，但是数据中部分数据又包含了逗号，导致导入系统的数据，部分数据列拆分的不对，这里由于数据量不大，我就是通过sql来进行位移操作列。但是这里需要注意，请在业务量少的时候来操作，如果允许的话，可以临时将数据库的日志模式调整为简单，因为批量处理千万级的表，产生的日志非常多（如果服务器的硬盘空间不足，需要提前考虑）。

（三）数据合并

由于数据库中已经有了部分数据，所以不能直接把临时表的数据全部插入正式表中，需要做好处理。同时由于累计的记录已经接近七千万条了，每年还会有四千万条左右的的新增记录，所以需要在这里一并考虑分表，计划使用其中的费用发生时间作为分表字段。

1、设置备份模式：选择在维护时段，将数据设置为简单备份模式（防止日志过多）

ALTER DATABASE [你的数据库名] SET RECOVERY SIMPLE;
GO

2、执行分批迁移脚本

-- 【针对 2024 年的分批迁移脚本】
SET NOCOUNT ON;
SET IDENTITY_INSERT ExpenseDetail_2024 ON; -- 如果Id是自增的，必须开启

DECLARE @CurrentMonth DATETIME = '2024-01-01';
DECLARE @NextMonth DATETIME;
DECLARE @RowsAffected INT;

WHILE @CurrentMonth < '2025-01-01'
BEGIN
    SET @NextMonth = DATEADD(MONTH, 1, @CurrentMonth);

    -- 打印进度，方便你在 SSMS 的“消息”栏查看当前跑到哪个月了
    PRINT '正在迁移时间段: ' + CONVERT(VARCHAR(10), @CurrentMonth, 120) + ' 到 ' + CONVERT(VARCHAR(10), @NextMonth, 120);

    -- 按月抽取并插入
    INSERT INTO ExpenseDetail_2024 (Id, ExpenseDate, Amount, /* ...补全其他所有字段... */)
    SELECT Id, ExpenseDate, Amount, /* ...补全其他所有字段... */
    FROM ExpenseDetail_Old
    WHERE ExpenseDate >= @CurrentMonth AND ExpenseDate < @NextMonth;

    SET @RowsAffected = @@ROWCOUNT;
    PRINT '本批次迁移了 ' + CAST(@RowsAffected AS VARCHAR(20)) + ' 条数据。';

    -- 提交一个检查点，强制将内存中的脏页写入磁盘，截断日志，释放空间
    CHECKPOINT; 

    -- 进入下个月
    SET @CurrentMonth = @NextMonth;
END

SET IDENTITY_INSERT ExpenseDetail_2024 OFF;
PRINT '2024年数据迁移完成！';
GO

3、收尾与性能激活

-- 恢复完整模式（如果你们平时需要做增量备份的话）
ALTER DATABASE [你的数据库名] SET RECOVERY FULL;
GO

-- 更新新表的统计信息，让查询引擎知道里面有多少数据、分布情况如何
UPDATE STATISTICS ExpenseDetail_2024 WITH FULLSCAN;
UPDATE STATISTICS ExpenseDetail_2025 WITH FULLSCAN;
-- 其他年份表同理...
GO

有了这次的经历，今后在处理大数据时也会更有经验，继续努力