堆表、聚集表的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），貌似符合大部分童鞋的预期；

总的写入时间，无索引堆表+创建索引<有索引堆表<聚集表

123

 

 

 

 

 

 

 

 

尝试加大每批的写入量，得到如下的结果

456

 

 

 

 

 

 

 

 

从上图看，第2~5批次写入数据量基本一致（538W，是上次测试的1.5倍），执行时间上聚集表表现仍较为平稳，但随着累积数据增加，执行时间呈现上升趋势；

堆表1，在第2批的执行时间几乎是后几批的2.5倍，堆表2表现符合预期。

但从整体执行时间看，总记录数较上一次增加200W，总执行时间却下降到701秒；

      堆表1，尽管后几批的执行时间低于同批次聚集表，但由于首批538W记录执行时间过长，导致总体执行时间超出聚集表60秒；

      堆表2，前面的风光已然不在，尽管写入时间最快，但添加索引时间较长，导致总执行时间最慢；

 

由于测试机器性能较差，没有再进行更大数据量的测试；但基本可以得出以下结论；

1、尽管“先insert堆表再创建索引”长期被DBA们作为最常见的数据导入手段，但对于更大批次的数据量而言，可能总体执行时间并不一定是最短的；

2、“堆表的写入效率优于聚集表”在大批次数据的环境中并不一定成立；

 

对于大批次的数据写入，个人建议：

1、尽量分解成小批次执行；

2、如果分解成本较高，可以考虑按照写入数据的特征，创建聚集索引进行优化，在聚集键的选择上，要注意尽量保持唯一性和写入顺序性，如对时间列和有较强筛选性的列做复合聚集索引，尽量避免由于页拆分导致的性能下降；

posted @   我是大菠萝  Views(1111)  Comments(3)  Edit  收藏  举报
编辑推荐：
· AI与.NET技术实操系列：基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
阅读排行：
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型，支持深度思考和联网搜索！
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 25岁的心里话
· ollama系列01：轻松3步本地部署deepseek，普通电脑可用
· 按钮权限的设计及实现
点击右上角即可分享
微信分享提示