SQL Server 高并发Insert数据解析,实践
在现实的生产环境中,有可能遇到高并发insert的应用.在此应用时由于堆表(Heap)和聚集表的结构不同导致在高并发的情形下insert效率不尽相同.接下来我会简单的以测试用例来简要说明.并举例说明如果提高聚集表下高并发插入效率.
在测试前我们先简单了解下堆表和聚集表都是如何完成插入操作的.
关于堆表和聚集表的介绍:SQL Server 索引知识-结构,实现
堆表Insert
方式1 a获得第一个IAM页
b 获取与之相关的PFS页,从中找到第一个能容纳Insert数据行大小的数据页
c 如果没有找到相应数据页则转到下一个IAM页然后重复b操作
d 如果到最后的IAM页还是没有找到可容纳的数据页则分配新的扩展区(extent)
e Insert指定行
方式2 a 获取所有IAM页
b 获取与之相关的PFS页(s)找到能容纳数据行(s)的数据页(s)
c 如果没有相关数据页,或者没有足够的相关数据页,分配新扩展区(extent)
d 插入相应的行(s)
聚集表Insert
由于聚集表本身的特性,插入数据的时的数据行必须在叶子节点的特定位置.
a 获取root页
b 通过B-tree需找到指定插入行的数据页的位置
c 如果此时数据页中空间可以容纳此行,则insert,如果不能则分配新数据页或者页分裂.然后insert
注:页分裂相对于页内的DML操作(insert,update)消耗巨大,页分裂的频率上升会明显影响实例的性能
测试用例
测试工具: sqlquerystress
测试环境:sql2008R2,3台不同服务器上分别安装运行sqlquerystress,100 threads/server
2000 times/thread
注意：如要模拟高并发需多台机器共同执行,单台即便多threads测试,测试结果也不能合理反应高并发情况.
测试脚本
我们在相应的机器上sqlquerystress中分两次(堆表,聚集表)分别运行insert into tempdb.dbo.tx(str1) select 'aa'测试
然后比较堆表,聚集表相关的性能指标.(Batch requests/sec, elapsed time,wait stats)
堆表
create table t1 ( id int identity(1,1), str1 char(5) ) go DBCC SQLPERF ("sys.dm_os_wait_stats", CLEAR)with NO_INFOMSGS -----run the sqlquerystress at three servers -----100 threads /per server -----2000 times /thread select * from sys.dm_os_wait_stats order by waiting_tasks_count desc
聚集表
checkpoint dbcc dropcleanbuffers create table t2 ( id int identity(1,1) primary key,---clustered index default str1 char(5) ) DBCC SQLPERF ("sys.dm_os_wait_stats", CLEAR)with NO_INFOMSGS -----run the sqlquerystress at three servers -----100 threads /per server -----2000 times /thread select * from sys.dm_os_wait_stats order by waiting_tasks_count desc
测试结果
执行时的吞吐量Batch requests/sec
可以看出在我们的测试环境下堆表的吞吐量是聚集表的2倍以上 图1-1
图1-1
执行时间 elapsed time
可以看出在我们的测试环境中执行时间堆表为45s,聚集表为85s 图1-2
图1-2
等待事件sys.dm_os_wait_stats
可以看出我们的测试环境中,相比堆表中我们的聚集表操作产生了大量的等待 图1-3
图1-3
由上面的实例我们可以看出,由于聚集表和堆表的insert方式差异,导致了在高并发下聚集表的insert效率低于堆表,在实际的项目中可能由于要求使用聚集表的情况下有大量并发插入请求,此时聚集表的insert操作就有可能出现瓶颈.此时我们可以根据sql server的一些知识来解决此瓶颈.
瓶颈分析
由于聚集表的数据组织特性,insert操作时数据只能逐行按序插入.图1-4
我们来看下聚集表数据页数据的具体情况.
code
dbcc traceon(3604) dbcc ind(tempdb,t2,1) -----find a datapage pageid 114 dbcc page('tempdb',1,114,3) WITH TABLERESULTS-----view the datapage 114
图1-4
解决思路
看了数据页的结构,数据行id 1,2,3…也就是顺序插入时大量并发只能集中在一个数据页中排队插入.看到这我们应该已经有了相应的思路,能否聚集表中同时往多个数据页中插入数据?
改变不了数据页的结构,我们可以改变数据的组织结构---分区.关于分区我就不做介绍了,如果想了解我的朋友宋沄剑的Blog里有篇优秀的文章介绍分区表T-SQL查询进阶--理解SQL SERVER中的分区表
哈希分区.Sql Server中不提供哈希分区,(虽然2014中内存数据库中已经提供了hash index.)
我们可以以其他方式变向地实现sql server中的hash分区.
这里我采用id/2取模的形式实现奇偶分区(odd-even)
创建完成后我们再执行上面的insert测试.
code
use tempdb CREATE PARTITION FUNCTION f_hash (int) AS RANGE LEFT FOR VALUES (0,1)----------Dim PARTITION FUNC CREATE PARTITION SCHEME OE_f_hash AS PARTITION f_hash all TO ([PRIMARY])-------Dim PARTITION architecture create table t3 ( id int identity (1,1), str1 varchar(2), hashid as id%2 PERSISTED ------hashid odd/even PERSISTED )-----create common table CREATE CLUSTERED INDEX [inx_1] ON t3 ( [hashid] ASC, [id] ASC )ON [OE_f_hash]([hashid])-------Dim PARTITION table DBCC SQLPERF ("sys.dm_os_wait_stats", CLEAR)with NO_INFOMSGS -----run the sqlquerystress at three servers -----100 threads /per server -----2000 times /thread select * from sys.dm_os_wait_stats order by waiting_tasks_count desc
我们先来看下奇偶分区后的数据页数据行的组织结构
code
dbcc ind(tempdb,t3,1) -----find a datapage pageid xx partition number (1,2) dbcc page('tempdb',1,94,3) WITH TABLERESULTS---view the datapage xx
分区1(偶数区)的数据页 图2-1
图2-1
分区2(奇数区)数据页 图2-2
图2-2
由上面截图中可以看出分区后的id按奇偶不同在不同的分区数据页中组织.这时实际就可以进行同时往多个数据页中insert数据(2个,奇偶)
这时我们再看下奇偶分区的实现下截取与堆表,聚集表相同的相关性能计数截图2-3,2-4,2-5
图2-3
图2-4
图2-5
可以看出无论从吞吐量Batch Requests/sec,还是执行时间,还是等待事件中相关计数都有了明显的改善.由于上面的哈希分区只是奇偶分区,如果按照其他hash分区(如除3取模),性能可能还会有一定的提升.感兴趣的朋友可以自行测试.但也应注意,由于聚集表和堆表的insert的实现方式不同 ,高并发下堆表(无索引)都具有一定优势.
