sqlserver 使用 service broker 代替触发器

我们在 sqlserver触发器根据columns_updated内容生成动态更新列 中，进行了初步探讨，但是在实际应用中发现一个很尴尬的问题，那就是批量更新数据时，效率感人

比如表A，有20个字段，500万行数据，其中有nvarchar(max)字段5个，当执行 update 表A set .... 没有 where 时，select * into #tb from inserted 几乎要把数据库弄崩溃

那怎么办呢？于是，我们尝试使用 service broker 来代替触发器，看看是否可行

关于 service broker 的设置本文就不细说了，只约定两个存储过程，其他设置自行百度

1、sync_send，用触发器触发调用，将触发内容发送到消息队列

CREATE proc [dbo].[sync_send]@message xmlasset nocount ondeclare @handle uniqueidentifierbegin dialog conversation @handlefrom service sync_svcto service N'sync_svc'on contract sync_msg_appointwith encryption = off;sendon conversation @handlemessage type sync_msg_type(@message);end conversation @handle

2、sync_process，处理消息队列的存储过程，后附

第一步，建立触发器

1.1 判断是否有有效操作

declare @ins int,@del int,@handle varbinary(64),@cmd nvarchar(max)select @ins = (select count(0) from inserted),@del = (select count(0) from deleted),@handle = (select sql_handle from sysprocesses where spid=@@spid)if @ins + @del = 0return

1.2 获取触发了触发器的实际指令

declare @ib table(EventType nvarchar(max),para smallint,EventInfo nvarchar(max))insert into @ibexec('dbcc inputbuffer(@@spid)')select @cmd = (select EventInfo from @ib)

1.3 将操作相关内容发送给消息队列

begin trydeclare @tb int,@db int,@procid int,@cu varbinary(max),@pk varchar(50),@ids varchar(max),@msg xml-- @pk 是触发器对应的表的主键字段名，当然具有唯一主键的表可以通过语句获取，但有些没有主键的，或者唯一性字段不是在第一列的获取的话就很麻烦，干脆作为变量直接放到触发器内select @pk='art_id',@db=db_id(),@procid=@@procid,@tb=(select parent_obj from sysobjects where id=@@PROCID),@cu=COLUMNS_UPDATED()-- @ids 是主键值的字符串列表，格式为 id1,id2,id3...idn，从inserted表获取select @ids = stuff((select ','+convert(varchar(max),art_id) from inserted for xml path('')),1,1,'')-- 如果是删除操作，则根据deleted获取@ids，因为inserted没有数据if @del > 0 and @ins = 0beginselect @ids = stuff((select ','+convert(varchar(max),art_id) from deleted for xml path('')),1,1,'')end-- 生成要传递给消息队列的内容select @msg = (select @db as dbid,@tb as tbid,@procid as procid,@pk as pk,@cu as cu,@ids as ids,@cmd as cmd,@ins as ins,@del as del for xml path(''),root('r'),type)-- 将消息发送给消息队列exec sync_send @message=@msgend trybegin catchinsert into sync_error(tm,cmd,msg) values(getdate(),@cmd,error_message())end catch

我在这里记录了很多内容，作为参数来进行处理，比如，哪个数据库发送的消息，哪个表触发的，哪个触发器出发的等等，最重要的是，我们只获取了主键相关的值，不再获取完整的临时表，会大大降低大批量数据更新时的拥塞现象

第二步，从消息队列中将消息分发给相应的存储过程，也就是 sync_process 的内容

CREATE proc [dbo].[sync_process]asset nocount onbegin trydeclare @handle uniqueidentifier,@message xml,@rows intset @rows=1while @rows>0begin-- 从消息队列中获取消息信息waitfor(receive top(1) @handle=conversation_handle,@message=(case when message_type_name=N'sync_msg_type' then convert(xml,message_body) else null end) from sync_queue),timeout 10set @rows = @@ROWCOUNTif @rows>0beginend conversation @handle-- 将 xml 格式的消息信息解析出来，并赋值给变量declare @db int,@tb int,@proc int,@pk varchar(max),@cu varbinary(max),@ids varchar(max),@cmd nvarchar(max),@ins int,@del int,@tr nvarchar(max)select @db = t.c.value('dbid[1]','int'),@tb = t.c.value('tbid[1]','int'),@proc = t.c.value('procid[1]','int'),@pk = t.c.value('pk[1]','varchar(max)'),@cu = t.c.value('cu[1]','varbinary(max)'),@ids = t.c.value('ids[1]','varchar(max)'),@cmd = t.c.value('cmd[1]','nvarchar(max)'),@ins = t.c.value('ins[1]','int'),@del = t.c.value('del[1]','int')from @message.nodes('/r') t(c)-- 判断是否有相应的存储过程select @tr = 'tr__' + db_name(@db) + '__' + object_name(@tb,@db)if object_id(@tr) is not nullbegin-- 执行相应的存储过程select @tr = 'exec caigou_2017_sync.dbo.' + @tr + ' @db,@tb,@proc,@pk,@cu,@ids,@cmd,@ins,@del'exec sp_executesql @tr,N'@db int,@tb int,@proc int,@pk varchar(max),@cu varbinary(max),@ids varchar(max),@cmd nvarchar(max),@ins int,@del int',@db=@db,@tb=@tb,@proc=@proc,@pk=@pk,@cu=@cu,@ids=@ids,@cmd=@cmd,@ins=@ins,@del=@delendelsebegin-- 记录错误信息，因为存储过程不存在insert into sync_error(tm,cmd,msg) values(getdate(),@cmd,user_name()+'tr__' + isnull(db_name(@db),convert(varchar(max),@db)) + '__' + isnull(object_name(@tb,@db),convert(varchar(max),@tb)) + ' not exists')endendendend trybegin catch-- 记录错误信息，因为执行异常insert into sync_error(tm,cmd,msg) values(getdate(),isnull(convert(nvarchar(max),@message),'sync_process'),error_message())end catch

在这里需要注意的是，如果跨数据库了，则需要修改相应数据库的安全设置，否则 object_name(@tb,@db) 语句会返回 null 值，这样就不能继续下去了

第三步，使用存储过程处理数据库触发器后的相关更新操作

3.1 存储过程名定义及接收变量定义

CREATE PROCEDURE [dbo].[tr__数据库名__表名]@db int,@tb int,@proc int,@pk varchar(max),@cu varbinary(max),@ids varchar(max),@cmd nvarchar(max),@ins int,@del intASBEGINSET NOCOUNT ON;

3.2 处理insert指令和delete指令

begin tryif @del = 0 and @ins > 0begin-- 插入操作print '根据需求定义插入触发器相关指令'endif @del > 0 and @ins = 0begin-- 删除操作
print '根据需求定义删除触发器相关指令'
 end
3.3 定义update相关操作
if @del > 0 and @ins > 0begindeclare @fieldlist varchar(max),@sql nvarchar(max)  -- @sql 作为 sp_executesql 所执行的语句，必须是 nchar/nvarchar/ntext 类型字段-- 获取更新操作更新的字段列表，同时将字段列表放到临时表中，以判断是否需要生成相关的更新操作set @sql = 'select @fieldlist = @pk + (select '',''+name from master.dbo.GetColumnOrderList(@cu) a left join ' + db_name(@db) + '.dbo.syscolumns b on a._col=b.colorder where b.id=@tb for xml path(''''))'select name into #tb from master.dbo.GetColumnOrderList(@cu) a left join syscolumns b on a._col=b.colorder where b.id=@tbexec sp_executesql @sql,N'@tb int,@pk varchar(max),@cu varbinary(max),@fieldlist varchar(max) output',@tb=@tb,@pk=@pk,@cu=@cu,@fieldlist=@fieldlist output-- 创建一个更新字段临时表，field为产生更新操作的字段，value为当字段更新，所定义的操作create table #update(field varchar(max),value nvarchar(max))insert into #update values('art_hits','clicks=a.art_hits'),('art_type','tp=art_type'),('art_sphere','sphere=isnull(a.art_sphere,'''')'),('art_city','city=a.art_city'),('pub_time','refresh=pub_time'),('art_author','author=a.art_author'),('c_id','c_id=a.c_id'),('exhi_finish','exhi_finish=a.exhi_finish')-- 定义更新操作指令的基本语句set @sql = 'update query set 'declare @field varchar(max),@value nvarchar(max),@id int-- 为避免消息队列并发时，游标名冲突，所以定义游标时追加 local 指令declare fl_581577110 cursor local for select field,value from #updateopen fl_581577110fetch next from fl_581577110 into @field,@valuewhile @@fetch_status=0beginif (select count(0) from #tb where name=@field)>0begin-- 根据更新字段临时表#tb和更新方式临时表#update来生成相关的更新操作set @sql = @sql + (case when right(@sql,5)=' set ' then '' else ',' end) + @valueendfetch next from fl_581577110 into @field,@valueendclose fl_581577110deallocate fl_581577110-- 如果没有需要更新的内容，清空@sql变量if right(@sql,5)=' set 'beginset @sql = nullendelsebeginset @sql = @sql + ' from ' + db_name(@db) + '.dbo.' + object_name(@tb,@db) + ' a with (nolock) where a.id=@id and a.id=pk and db=@db and tb=@tb and art_online=1 and art_delete=0'end-- 将@ids的主键列表切分成表数据，SplitStr为自定义字符串切割函数，基本上百度也是一大堆declare cur_581577110 cursor local for select value from dbo.SplitStr(@ids,',')open cur_581577110fetch next from cur_581577110 into @idwhile @@fetch_status=0begin-- 字段更新操作if @sql is not nullbegin-- 如果有需要更新的操作，执行更新操作，将必要的参数传递给更新指令exec sp_executesql @sql,N'@db int,@tb int,@id int',@db=@db,@tb=@tb,@id=@idendfetch next from cur_581577110 into @idendclose cur_581577110deallocate cur_581577110end
3.4 异常记录
end trybegin catchinsert into sync_error(tm,cmd,msg) values(getdate(),'sql:'+isnull(@cmd,'')+';sql:'+isnull(@sql,''),error_message())end catchEND

当然，实际应用中，触发器需要执行的操作其实更加多变和复杂，那么就需要大家自己耐心点去完善自己的存储过程了