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 xml
as
set nocount on
declare @handle uniqueidentifier
begin dialog conversation @handle
	from service sync_svc
	to service N'sync_svc'
	on contract sync_msg_appoint
	with encryption = off;
send
	on conversation @handle
	message 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 = 0
		return

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

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

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

	begin try
		declare @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 = 0
			begin
				select @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=@msg
	end try
	begin catch
		insert into sync_error(tm,cmd,msg) values(getdate(),@cmd,error_message())
	end catch

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

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

CREATE proc [dbo].[sync_process]
as
set nocount on
begin try
	declare @handle uniqueidentifier,@message xml,@rows int
	set @rows=1
	while @rows>0
		begin
			-- 从消息队列中获取消息信息
			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 10
			set @rows = @@ROWCOUNT
			if @rows>0
				begin
					end 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 null
						begin
							-- 执行相应的存储过程
							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=@del
						end
					else
						begin
							-- 记录错误信息，因为存储过程不存在
							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')
						end
				end
		end
end try
begin 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 int
AS
BEGIN
	SET NOCOUNT ON;

3.2 处理insert指令和delete指令

	begin try
		if @del = 0 and @ins > 0
			begin
				-- 插入操作
				print '根据需求定义插入触发器相关指令'
			end
		if @del > 0 and @ins = 0
			begin
				-- 删除操作
				print '根据需求定义删除触发器相关指令'
 end



3.3 定义update相关操作


		if @del > 0 and @ins > 0
			begin
				declare @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=@tb
				exec 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 #update
				open fl_581577110
				fetch next from fl_581577110 into @field,@value
				while @@fetch_status=0
					begin
						if (select count(0) from #tb where name=@field)>0
							begin
								-- 根据更新字段临时表#tb和更新方式临时表#update来生成相关的更新操作
								set @sql = @sql + (case when right(@sql,5)=' set ' then '' else ',' end) + @value
							end
						fetch next from fl_581577110 into @field,@value
					end
				close fl_581577110
				deallocate fl_581577110
				-- 如果没有需要更新的内容，清空@sql变量
				if right(@sql,5)=' set '
					begin
						set @sql = null
					end
				else
					begin
						set @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_581577110
				fetch next from cur_581577110 into @id
				while @@fetch_status=0
					begin
						-- 字段更新操作
						if @sql is not null
							begin
								-- 如果有需要更新的操作，执行更新操作，将必要的参数传递给更新指令
								exec sp_executesql @sql,N'@db int,@tb int,@id int',@db=@db,@tb=@tb,@id=@id
							end
						fetch next from cur_581577110 into @id
					end
				close cur_581577110
				deallocate cur_581577110
			end
3.4 异常记录


	end try
	begin catch
		insert into sync_error(tm,cmd,msg) values(getdate(),'sql:'+isnull(@cmd,'')+';sql:'+isnull(@sql,''),error_message())
	end catch
END


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