堆表、聚集表的insert效率问题
此前碰到一个案例，一个堆表有上亿的数据，每天通过job定期的insert的数据还有几十万，直到某一天，insert效率急剧下降……
在具体讨论之前，需要描述一下场景，否则任何结论都具有局限性；
场景：我们有一台大容量存储作为历史数据的归档服务器，明天凌晨，通过JOB从生产环境进行select操作，再insert 到归档服务器中，属于无并发大批量数据写入问题；
关于高并发时堆表、聚集表insert效率的讨论及结论，可以参考高兄《SQL Server 高并发Insert数据解析,实践》一文；
无测试不结论，先上测试过程：
- 准备测试数据，生产表大约生成2300W数据，归档表大约生成1200W数据
--创建归档表,test_heaptb_insert为堆表,test_clustb_insert为聚集表 CREATE TABLE testDB_A.dbo.test_heaptb_insert(intime INT,objectid VARCHAR(20),context VARCHAR(200)) CREATE TABLE testDB_A.dbo.test_clustb_insert(intime INT,objectid VARCHAR(20),context VARCHAR(200)) CREATE INDEX idx_heaptb_intime_objectid ON testDB_A.dbo.test_heaptb_insert(intime,objectid) CREATE CLUSTERED INDEX cluix_clustb_intime_objectid ON testDB_A.dbo.test_clustb_insert(intime,objectid) --创建生产表,聚集表 CREATE TABLE testDB_b.dbo.test_insert(intime INT,objectid VARCHAR(20),context VARCHAR(200)) CREATE CLUSTERED INDEX cluix_testinsert_intime_objectid ON testDB_b.dbo.test_insert(intime,objectid) --生成基础数据 /* intime为int类型，表示形如20150302这样的8位日期数据 objectid为varchar类型，形如objectid_001这样的字符串+数字的格式 两者共同组成聚集表的聚集索引，在数据分布上具有唯一属性（并未创建唯一约束） */ ;WITH objid AS ( SELECT 'objectid_'+ CASE WHEN ROW_NUMBER() OVER (ORDER BY co.name) <10 THEN '00'+CAST(ROW_NUMBER() OVER (ORDER BY co.name) AS VARCHAR) WHEN ROW_NUMBER() OVER (ORDER BY co.name) >=10 AND ROW_NUMBER() OVER (ORDER BY co.name) <100 THEN '0'+CAST(ROW_NUMBER() OVER (ORDER BY co.name) AS VARCHAR) WHEN ROW_NUMBER() OVER (ORDER BY co.name) >=100 THEN CAST(ROW_NUMBER() OVER (ORDER BY co.name) AS VARCHAR) END objectid, REPLICATE('a',200) context FROM sys.columns co ) , intime AS( SELECT REPLACE(CONVERT(VARCHAR(10),DATEADD(dd,-a.cnt1,GETDATE()),120),'-','') intime FROM (SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY co1.name) cnt1 FROM sys.columns CROSS JOIN sys.columns co1 ) a ) INSERT INTO testDB_b.dbo.test_insert SELECT intime.intime,objid.* FROM intime CROSS JOIN objid ORDER BY 1,2 INSERT INTO testDB_b.dbo.test_insert SELECT TOP 1 CAST(REPLACE( CONVERT(VARCHAR(10),DATEADD(dd,-6000,LEFT(CAST(intime AS varchar),4)+'-'+SUBSTRING(CAST(intime AS varchar),5,2)+'-'+RIGHT(CAST(intime AS varchar),2)),120),'-','') AS int) ,objectid,context FROM test_insert ORDER BY 1 INSERT INTO testDB_b.dbo.test_insert SELECT TOP 1 CAST(REPLACE( CONVERT(VARCHAR(10),DATEADD(dd,-12000,LEFT(CAST(intime AS varchar),4)+'-'+SUBSTRING(CAST(intime AS varchar),5,2)+'-'+RIGHT(CAST(intime AS varchar),2)),120),'-','') AS int) ,objectid,context FROM test_insert ORDER BY 1 INSERT INTO testDB_b.dbo.test_insert SELECT TOP 1 CAST(REPLACE( CONVERT(VARCHAR(10),DATEADD(dd,-24000,LEFT(CAST(intime AS varchar),4)+'-'+SUBSTRING(CAST(intime AS varchar),5,2)+'-'+RIGHT(CAST(intime AS varchar),2)),120),'-','') AS int) ,objectid,context FROM test_insert ORDER BY 1 --重建生产表聚集索引 ALTER INDEX cluix_testinsert_intime_objectid ON testDB_b.dbo.test_insert REBUILD --归档表数据初始化 INSERT INTO testDB_A.dbo.test_heaptb_insert SELECT * FROM testdb_b.dbo.test_insert WHERE intime<= 19500101 INSERT INTO testDB_A.dbo.test_clustb_insert SELECT * FROM testdb_b.dbo.test_insert WHERE intime<= 19500101
以下测试过程中，通过以下措施减小外部因素干扰：
1、数据库改为简单模式，并手动扩充足够数据文件、日志文件空间，避免由文件增长导致的磁盘繁忙；
2、每次写入数据前进行checkpoint和清除buffer的操作；
测试一：在有1200W基础数据的情况下，分别向两个表insert，179W、358W、538W条记录，统计执行时间
checkpoint DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE checkpoint SET STATISTICS IO ON SET STATISTICS TIME ON INSERT INTO testDB_A.dbo.test_heaptb_insert SELECT * FROM testdb_b.dbo.test_insert WHERE intime > 19500101 AND intime<= 19600101 SET STATISTICS IO OFF SET STATISTICS TIME OFF checkpoint DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE checkpoint SET STATISTICS IO ON SET STATISTICS TIME ON INSERT INTO testDB_A.dbo.test_heaptb_insert SELECT * FROM testdb_b.dbo.test_insert WHERE intime > 19600101 AND intime<= 19800101 SET STATISTICS IO OFF SET STATISTICS TIME OFF checkpoint DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE checkpoint SET STATISTICS IO ON SET STATISTICS TIME ON INSERT INTO testDB_A.dbo.test_heaptb_insert SELECT * FROM testdb_b.dbo.test_insert WHERE intime > 19800101 AND intime<= 20100101 SET STATISTICS IO OFF SET STATISTICS TIME OFF checkpoint DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE CHECKPOINT SET STATISTICS IO ON SET STATISTICS TIME ON INSERT INTO testDB_A.dbo.test_clustb_insert SELECT * FROM testdb_b.dbo.test_insert WHERE intime > 19500101 AND intime<= 19600101 SET STATISTICS IO OFF SET STATISTICS TIME OFF checkpoint DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE CHECKPOINT SET STATISTICS IO ON SET STATISTICS TIME ON INSERT INTO testDB_A.dbo.test_clustb_insert SELECT * FROM testdb_b.dbo.test_insert WHERE intime > 19600101 AND intime<= 19800101 SET STATISTICS IO OFF SET STATISTICS TIME OFF checkpoint DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE CHECKPOINT SET STATISTICS IO ON SET STATISTICS TIME ON INSERT INTO testDB_A.dbo.test_clustb_insert SELECT * FROM testdb_b.dbo.test_insert WHERE intime > 19800101 AND intime<= 20100101 SET STATISTICS IO OFF SET STATISTICS TIME OFF --表 'test_heaptb_insert'。扫描计数 0，逻辑读取 9512119 次，物理读取 2 次，预读 6671 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 --表 'test_insert'。扫描计数 1，逻辑读取 52953 次，物理读取 15 次，预读 52948 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 -- SQL Server 执行时间: -- CPU 时间 = 18065 毫秒，占用时间 = 84702 毫秒。 --(1793132 行受影响) --表 'test_clustb_insert'。扫描计数 0，逻辑读取 7938622 次，物理读取 1 次，预读 46384 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 --表 'test_insert'。扫描计数 1，逻辑读取 52953 次，物理读取 12 次，预读 52948 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 -- SQL Server 执行时间: -- CPU 时间 = 14336 毫秒，占用时间 = 281425 毫秒。 --(1793132 行受影响) --表 'test_heaptb_insert'。扫描计数 0，逻辑读取 19023872 次，物理读取 0 次，预读 13298 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 --表 'test_insert'。扫描计数 1，逻辑读取 105918 次，物理读取 32 次，预读 105927 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 -- SQL Server 执行时间: -- CPU 时间 = 36426 毫秒，占用时间 = 441212 毫秒。 --(3586755 行受影响) --表 'test_clustb_insert'。扫描计数 0，逻辑读取 15879381 次，物理读取 0 次，预读 92877 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 --表 'test_insert'。扫描计数 1，逻辑读取 105918 次，物理读取 30 次，预读 105927 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 -- SQL Server 执行时间: -- CPU 时间 = 28174 毫秒，占用时间 = 373363 毫秒。 --(3586755 行受影响) --表 'test_heaptb_insert'。扫描计数 0，逻辑读取 28537099 次，物理读取 0 次，预读 19948 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 --表 'test_insert'。扫描计数 1，逻辑读取 158882 次，物理读取 44 次，预读 158889 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 -- SQL Server 执行时间: -- CPU 时间 = 56488 毫秒，占用时间 = 689698 毫秒。 --(5380378 行受影响) --表 'test_clustb_insert'。扫描计数 0，逻辑读取 23820139 次，物理读取 0 次，预读 114519 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 --表 'test_insert'。扫描计数 1，逻辑读取 158882 次，物理读取 80 次，预读 158882 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 -- SQL Server 执行时间: -- CPU 时间 = 44803 毫秒，占用时间 = 294685 毫秒。 --(5380378 行受影响)
从结果可以看出，除第一次179W行写入外，聚集表的insert效率明显高于堆表，且随着insert数据量增加，聚集表的insert效率有明显升高的趋势；
测试二：清空两表的数据，再按照每次定量进行insert，统计多次写入的执行时间
在测试期间，有的童鞋指出，“这个堆表有非聚集索引，在insert的时更新索引的性能损耗也很大，应该先对无索引的堆表进行insert，再添加索引，效率会比较高”；
但事实果真如此么？为此添加一个测试项，下表中的“堆表2”
TRUNCATE TABLE testDB_A.dbo.test_heaptb_insert TRUNCATE TABLE testDB_A.dbo.test_clustb_insert --DROP INDEX idx_heaptb_intime_objid ON testDB_A.dbo.test_heaptb_insert --CREATE INDEX idx_heaptb_intime_objid ON testDB_A.dbo.test_heaptb_insert(intime,objectid) CHECKPOINT DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE CHECKPOINT SET STATISTICS IO ON SET STATISTICS TIME ON INSERT INTO testDB_A.dbo.test_heaptb_insert SELECT * FROM testdb_b.dbo.test_insert WHERE intime > 18600101 AND intime<= 18800101 SET STATISTICS IO OFF SET STATISTICS TIME OFF
从下图可以看出，按照每次358W的数据量进行累计写入，聚集表表现的比较平稳，而有索引的堆表（堆表1），在第一次写入时效率较差，随后效率回升，略优于聚集表；而先对无索引的堆表进行insert，再创建索引的情况（堆表2），貌似符合大部分童鞋的预期；
总的写入时间，无索引堆表+创建索引<有索引堆表<聚集表
尝试加大每批的写入量，得到如下的结果
从上图看，第2~5批次写入数据量基本一致（538W，是上次测试的1.5倍），执行时间上聚集表表现仍较为平稳，但随着累积数据增加，执行时间呈现上升趋势；
堆表1，在第2批的执行时间几乎是后几批的2.5倍，堆表2表现符合预期。
但从整体执行时间看，总记录数较上一次增加200W，总执行时间却下降到701秒；
堆表1，尽管后几批的执行时间低于同批次聚集表，但由于首批538W记录执行时间过长，导致总体执行时间超出聚集表60秒；
堆表2，前面的风光已然不在，尽管写入时间最快，但添加索引时间较长，导致总执行时间最慢；
由于测试机器性能较差，没有再进行更大数据量的测试；但基本可以得出以下结论；
1、尽管“先insert堆表再创建索引”长期被DBA们作为最常见的数据导入手段，但对于更大批次的数据量而言，可能总体执行时间并不一定是最短的；
2、“堆表的写入效率优于聚集表”在大批次数据的环境中并不一定成立；
对于大批次的数据写入，个人建议：
1、尽量分解成小批次执行；
2、如果分解成本较高，可以考虑按照写入数据的特征，创建聚集索引进行优化，在聚集键的选择上，要注意尽量保持唯一性和写入顺序性，如对时间列和有较强筛选性的列做复合聚集索引，尽量避免由于页拆分导致的性能下降；
