sqlserver 使用 service broker 代替触发器

轉發自: https://blog.csdn.net/superwfei/article/details/78327376

我们在 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，用触发器触发调用，将触发内容发送到消息队列

[sql]  view plain  copy

1. CREATE proc [dbo].[sync_send]  
2.     @message xml  
3. as  
4. set nocount on  
5. declare @handle uniqueidentifier  
6. begin dialog conversation @handle  
7.     from service sync_svc  
8.     to service N'sync_svc'  
9.     on contract sync_msg_appoint  
10.     with encryption = off;  
11. send  
12.     on conversation @handle  
13.     message type sync_msg_type(@message);  
14. end conversation @handle  

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

第一步，建立触发器

1.1 判断是否有有效操作

[sql]  view plain  copy

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

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

[sql]  view plain  copy

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

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

[sql]  view plain  copy

1.     begin try  
2.         declare @tb int,@db int,@procid int,@cu varbinary(max),@pk varchar(50),@ids varchar(max),@msg xml  
3. <span style="white-space:pre;">     </span>-- @pk 是触发器对应的表的主键字段名，当然具有唯一主键的表可以通过语句获取，但有些没有主键的，或者唯一性字段不是在第一列的获取的话就很麻烦，干脆作为变量直接放到触发器内  
4.         select @pk='art_id',@db=db_id(),@procid=@@procid,@tb=(select parent_obj from sysobjects where id=@@PROCID),@cu=COLUMNS_UPDATED()  
5. <span style="white-space:pre;">     </span>-- @ids 是主键值的字符串列表，格式为 id1,id2,id3...idn，从inserted表获取  
6.         select @ids = stuff((select ','+convert(varchar(max),art_id) from inserted for xml path('')),1,1,'')<span style="white-space:pre;">     </span>-- 如果是删除操作，则根据deleted获取@ids，因为inserted没有数据  
7.         if @del > 0 and @ins = 0  
8.             begin  
9.                 select @ids = stuff((select ','+convert(varchar(max),art_id) from deleted for xml path('')),1,1,'')  
10.             end  
11. <span style="white-space:pre;">     </span>-- 生成要传递给消息队列的内容  
12.         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)  
13. <span style="white-space:pre;">     </span>-- 将消息发送给消息队列  
14.         exec sync_send @message=@msg  
15.     end try  
16.     begin catch  
17.         insert into sync_error(tm,cmd,msg) values(getdate(),@cmd,error_message())  
18.     end catch  

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

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

[sql]  view plain  copy

1. CREATE proc [dbo].[sync_process]  
2. as  
3. set nocount on  
4. begin try  
5.     declare @handle uniqueidentifier,@message xml,@rows int  
6.     set @rows=1  
7.     while @rows>0  
8.         begin  
9. <span style="white-space:pre;">         </span>-- 从消息队列中获取消息信息  
10.             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  
11.             set @rows = @@ROWCOUNT  
12.             if @rows>0  
13.                 begin  
14.                     end conversation @handle  
15. <span style="white-space:pre;">                 </span>-- 将 xml 格式的消息信息解析出来，并赋值给变量  
16.                     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)  
17.                     select   
18.                         @db = t.c.value('dbid[1]','int'),  
19.                         @tb = t.c.value('tbid[1]','int'),  
20.                         @proc = t.c.value('procid[1]','int'),  
21.                         @pk = t.c.value('pk[1]','varchar(max)'),  
22.                         @cu = t.c.value('cu[1]','varbinary(max)'),  
23.                         @ids = t.c.value('ids[1]','varchar(max)'),  
24.                         @cmd = t.c.value('cmd[1]','nvarchar(max)'),  
25.                         @ins = t.c.value('ins[1]','int'),  
26.                         @del = t.c.value('del[1]','int')  
27.                     from @message.nodes('/r') t(c)  
28. <span style="white-space:pre;">                 </span>-- 判断是否有相应的存储过程  
29.                     select @tr = 'tr__' + db_name(@db) + '__' + object_name(@tb,@db)  
30.                     if object_id(@tr) is not null  
31.                         begin  
32. <span style="white-space:pre;">                         </span>-- 执行相应的存储过程  
33.                             select @tr = 'exec caigou_2017_sync.dbo.' + @tr + ' @db,@tb,@proc,@pk,@cu,@ids,@cmd,@ins,@del'  
34.                             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  
35.                         end  
36.                     else  
37.                         begin  
38. <span style="white-space:pre;">                         </span>-- 记录错误信息，因为存储过程不存在  
39.                             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')  
40.                         end  
41.                 end  
42.         end  
43. end try  
44. begin catch  
45. <span style="white-space:pre;"> </span>-- 记录错误信息，因为执行异常  
46.     insert into sync_error(tm,cmd,msg) values(getdate(),isnull(convert(nvarchar(max),@message),'sync_process'),error_message())  
47. end catch  

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

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

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

[sql]  view plain  copy

1. CREATE PROCEDURE [dbo].[tr__数据库名__表名]  
2.     @db int,@tb int,@proc int,@pk varchar(max),@cu varbinary(max),@ids varchar(max),@cmd nvarchar(max),@ins int,@del int  
3. AS  
4. BEGIN  
5.     SET NOCOUNT ON;  

3.2 处理insert指令和delete指令

[sql]  view plain  copy

1. begin try  
2.     if @del = 0 and @ins > 0  
3.         begin  
4.             -- 插入操作  
5.             print '根据需求定义插入触发器相关指令'  
6.         end  
7.     if @del > 0 and @ins = 0  
8.         begin  
9.             -- 删除操作  

[sql]  view plain  copy

1. print '根据需求定义删除触发器相关指令'  

end

3.3 定义update相关操作

[sql]  view plain  copy

1.         if @del > 0 and @ins > 0  
2.             begin  
3.                 declare @fieldlist varchar(max),@sql nvarchar(max)  -- @sql 作为 sp_executesql 所执行的语句，必须是 nchar/nvarchar/ntext 类型字段  
4. <span style="white-space:pre;">             </span>-- 获取更新操作更新的字段列表，同时将字段列表放到临时表中，以判断是否需要生成相关的更新操作  
5.                 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(''''))'  
6.                 select name into #tb from master.dbo.GetColumnOrderList(@cu) a left join syscolumns b on a._col=b.colorder where b.id=@tb  
7.                 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  
8. <span style="white-space:pre;">             </span>-- 创建一个更新字段临时表，field为产生更新操作的字段，value为当字段更新，所定义的操作  
9.                 create table #update(field varchar(max),value nvarchar(max))  
10.                 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')  
11. <span style="white-space:pre;">             </span>-- 定义更新操作指令的基本语句  
12.                 set @sql = 'update query set '  
13.                 declare @field varchar(max),@value nvarchar(max),@id int  
14. <span style="white-space:pre;">             </span>-- 为避免消息队列并发时，游标名冲突，所以定义游标时追加 local 指令  
15.                 declare fl_581577110 cursor local for select field,value from #update  
16.                 open fl_581577110  
17.                 fetch next from fl_581577110 into @field,@value  
18.                 while @@fetch_status=0  
19.                     begin  
20.                         if (select count(0) from #tb where name=@field)>0  
21.                             begin  
22. <span style="white-space:pre;">                             </span>-- 根据更新字段临时表#tb和更新方式临时表#update来生成相关的更新操作  
23.                                 set @sql = @sql + (case when right(@sql,5)=' set ' then '' else ',' end) + @value  
24.                             end  
25.                         fetch next from fl_581577110 into @field,@value  
26.                     end  
27.                 close fl_581577110  
28.                 deallocate fl_581577110  
29. <span style="white-space:pre;">             </span>-- 如果没有需要更新的内容，清空@sql变量  
30.                 if right(@sql,5)=' set '  
31.                     begin  
32.                         set @sql = null  
33.                     end  
34.                 else  
35.                     begin  
36.                         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'  
37.                     end  
38. <span style="white-space:pre;">             </span>-- 将@ids的主键列表切分成表数据，SplitStr为自定义字符串切割函数，基本上百度也是一大堆  
39.                 declare cur_581577110 cursor local for select value from dbo.SplitStr(@ids,',')  
40.                 open cur_581577110  
41.                 fetch next from cur_581577110 into @id  
42.                 while @@fetch_status=0  
43.                     begin  
44.                         -- 字段更新操作  
45.                         if @sql is not null  
46.                             begin  
47. <span style="white-space:pre;">                             </span>-- 如果有需要更新的操作，执行更新操作，将必要的参数传递给更新指令  
48.                                 exec sp_executesql @sql,N'@db int,@tb int,@id int',@db=@db,@tb=@tb,@id=@id  
49.                             end  
50.                         fetch next from cur_581577110 into @id  
51.                     end  
52.                 close cur_581577110  
53.                 deallocate cur_581577110  
54.             end  

3.4 异常记录

[sql]  view plain  copy

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

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