设计高效的存储过程

最新推荐文章于 2024-09-04 22:36:34 发布

tearsmo

最新推荐文章于 2024-09-04 22:36:34 发布

阅读量518

点赞数

分类专栏： sql server 2005 学习记录文章标签：存储 insert extension sql table join

sql server 2005 学习记录专栏收录该内容

57 篇文章 0 订阅

订阅专栏

设计背景

由于历史原因，线上库环境数据量及其庞大，很多千万级以上甚至过亿的表。目标是让N张互相关联的表按照一张源表为基表，数据搬移归档这里我们举例N为50 每张表数据5000W

最差性能sql进化客串

2表KeyName 字段意义名称等相同从bug01 表中取出前500条不在bug02 表中的数据

最差性能：

 
         SELECT 
          TOP 
         500 a.KeyName  
         FROM 
         bug01 a  
         LEFT 
          JOIN 
         bug02 b  
         on 
         a.KeyName = b.KeyName   
        
 
         WHERE 
          (a.KeyName  
         not 
         in 
         ( 
         select 
          distinct 
         b.KeyName  
         From 
         bug02))   
        
 
         ORDER 
          BY 
         a.KeyName  
         asc 
        

进化体在篇尾揭晓

详细设计

问题点：性能安全容错

流程篇为何如此设计在下文中会解释

step.1 源表数据过滤

这部分没什么好说的根据大家自己的业务场景设定不同的过滤规则

step.2 源表数据副本

程序的入口点肯定是源表了，扩展表中的内容都是以源表为Key来展开。那么这个展开的过程如何来做。

首先确定一些概念，这50表中的层级关系如何。可能直接和源表key键关联的表只有10张。

例如我统计市内所有图书馆详细信息，那么我们以图书馆为源表。图书馆关联书架、地址、会员信息。那么这3中信息我们分为一级别表。

书架关联图书类别，地址关联街道信息，会员关联用户借阅信息，那么后面3者我们继续分为二级表，......按照场景继续扩展。

方案1：使用游标循环源表根据源表key值处理和key相关的数据假设我们没批次处理500跳源表数据

　　　也就是根据图书馆ID，遍历所有节点。假设我们不分二级三级表，都是一级表我们的insert操作次数是500*50。select操作同数据量

　　　这个给谁肯定都不大乐意，而且如果再遍历2级表3级更难想象。

方案2：对源表key数据进行集合，存进变量，然后用in表达式。貌似可行。直接减少到1/500的操作次数。但是这里有个最恐怖的问题。

　　　变量都有长度，例如varchar 最大长度不能超过65535。

方案3：将源表Key做成一个查询过滤池(相对于一级表底层的sql where条件语句下面会详细介绍一下) 相对于第二种方案，我们这种似乎又将操作数提高了。

　　　不考虑层级的情况下，insert操作50。select操作50*2可以接受.

方案3扩展：对于一张大表来说操作50次也不是什么可以乐观的数字，并且这个50还有可能变成500，5000，50000。

　　　　　　更有一个问题就是，当你操作这500条的时候，可能会有数据干扰，你1秒前取得的这500条可不一定是1秒后的内容。

　　　　　　所以采取临时表策略。

 
         CREATE 
         TABLE 
         #p  
        
         (        
        
         OrderID  
         varchar 
         (50),   
        
         primary 
         key 
         (OrderID)        
        
         );  
        
         SET 
         @temp_text =  
         'INSERT INTO #p ' 
         +@KeyText  
        
         --PRINT @temp_text  
        
         EXEC 
         (@temp_text)     
        
         SET 
         @KeyText =  
         'SELECT OrderID FROM #p' 
        
         --如果一级表关联的操作次数比较多那么可以访源表操作 以临时表取代物理表  
        
         SET 
         @SubKeyText =  
         'select 一级表_A_被关联键 From 一级表_A with(nolock) where 一级表_A_关联源表键 in (' 
         + @KeyText+ 
         ')' 
        
         CREATE 
         TABLE 
         #q  
        
         (        
        
         OrderID  
         varchar 
         (50),   
        
         primary 
         key 
         (OrderID)        
        
         );  
        
         SET 
         @temp_text =  
         'INSERT INTO #q ' 
         +@SubKeyText  
        
         EXEC 
         (@temp_text)     
        
         SET 
         @SubKeyText = 
         'SELECT OrderID FROM #q' 
        
         --如果一级表关联的操作次数不多可以直接生成数据过滤池  
        
         SET 
         @SubKeyTextforA = 
         'select 一级表_B_被二级关联键 From 一级表_B with(nolock) where 一级表_B_关联源表键 in (' 
         + @KeyText+ 
         ')' 
        
         SET 
         @SubKeyTextforB = 
         'select 一级表_C_被二级关联键 From 一级表_C with(nolock) where 一级表_C_关联源表键 in (' 
         + @KeyText+ 
         ')' 
        
         --如果存在更多层操作在此处可以继续关联资源过滤池 Demo只做到三层  
        
         SET 
          @THKeyTextforA = 
         'select 二级表_A_被三级关联键 From 二级表_A with(nolock) where 二级表_A_关联一级表键 in (' 
         + @SubKeyTextforA+ 
         ')'

--step.3 分表归档操作

这个环节的问题是安全事务如何控制事务的大小如何衡量如何容错以及如何将程序做得可扩展可维护

大家根据业务场景区分自己的批次范围拿虫子这篇demo来说 50张千万级大表如果是批次5000条以上事务要放在内层处理如果是5000条以下可以放在最外层

事务的大小直接影响性能的波动

容错的方案大家也可以自己设计虫子的程序员采用第三类表异常表来重置失败了就插入下一个批次直接就过滤

 
         --将错误的批次订单号入异常表  
        
 
              
         Insert 
         into 
         异常表(@ExTable)  
         SELECT 
         OrderID  
         FROM 
         #p  
        
 
         --@ExTable用来存放异常数据 如果当期批次出错 则将本次批次订单信息入库@ExTable下一批次则过滤这些数据再执行  
        
 
              
         SET 
         @KeyText =  
         'SELECT TOP ' 
         + 
         CAST 
         (@SynSize  
         AS 
         VARCHAR 
         (10))+ 
         ' ' 
         +@Base_Key+'  
         FROM 
         + 
        

'+@BaseTable+' + WHERE '+@Base_Key+' not in ( select '+@Base_Key+' From '+@ExTable+' ) '

如何让程序变的漂亮可维护

我们在存储过程中同样可以使用面试对象的思想只不过存储过程没有类这样的概念给我们那么我们不妨自己设计

用什么还是临时表

 
         --一级 直接关联源表主键 或为二级被关联的主表  
        
 
              
         INSERT 
         INTO 
         #k  
         VALUES 
         ( 
         '一级表_A' 
         ,@Base_Key,@KeyText, 
         '' 
         )                    
         --一级表_A  
        
 
              
         INSERT 
         INTO 
         #k  
         VALUES 
         ( 
         '一级表_B' 
         ,@Base_Key,@KeyText, 
         '' 
         )                    
         --一级表_B  
        
 
              
         INSERT 
         INTO 
         #k  
         VALUES 
         ( 
         '一级表_C' 
         ,@Base_Key,@KeyText, 
         '' 
         )                    
         --一级表_C  
        
 
         --二级 规则间接关联  
        
 
              
         --@SubKeyText相关  
        
 
              
         INSERT 
         INTO 
         #k  
         VALUES 
         ( 
         '二级表_A' 
         , 
         '二级表_A_关联一级键' 
         ,@SubKeyText, 
         '' 
         )                 
         --二级表_A  
        
 
              
         INSERT 
         INTO 
         #k  
         VALUES 
         ( 
         '二级表_B' 
         , 
         '二级表_B_关联一级键' 
         ,@SubKeyText, 
         '' 
         )                 
         --二级表_B   
        
 
              
         INSERT 
         INTO 
         #k  
         VALUES 
         ( 
         '二级表_C' 
         , 
         '二级表_C_关联一级键' 
         ,@SubKeyText, 
         '' 
         )                 
         --二级表_C  
        
 
         --特殊处理   
        
 
              
         --自定义操作  
        
 
              
         INSERT 
         INTO 
         #k  
         VALUES 
         ( 
         '特殊表' 
         , 
         '特殊表关联键' 
         , 
         '自定义数据过滤方式' 
         , 
         '' 
         )             
        
 
                
        
 
              
         --其他 自增列处理  
        
 
              
         --修改订单,及其取消修改订单状态历史表  
        
 
              
         INSERT 
         INTO 
         #k  
         VALUES 
         ( 
         '自增表' 
         ,@Base_Key,@KeyText, 
         '自定义字段' 
         ) 
        

--step.4 处理细节

游标循环临时表针对每一张表操作一次

 
         DECLARE 
          CUR_ORDERHEDER  
         INSENSITIVE 
          CURSOR 
         FOR 
         SELECT 
         TableName,KeyName,temptext,colname  
         FROM 
         #k   
        
         OPEN 
         CUR_ORDERHEDER  
        
         FETCH 
         CUR_ORDERHEDER  
         INTO 
         @Cur_Table,@Cur_Key,@Cur_W,@Cur_K  
        
         WHILE @@FETCH_STATUS = 0  
        
         BEGIN                
        
         EXECUTE 
         P_Task_Sub_Synchronization  
        
         @OutParam  = @OutParam  
         OUT 
         , @OutMessage = @OutMessage  
         OUT 
         ,  
        
         @KeyText =  @Cur_W,@ 
         Table 
         = @Cur_Table,@Extension=@Extension,@IsDelSource=@IsDelSource,@KeyName=@Cur_Key,@ColName=@Cur_K  
        
         --SET @OutMessage = @OutMessage+@OutMessage  
        
         --PRINT @OutMessage  
        
         IF @OutParam <> 0    
        
         BEGIN 
        
         SET 
         @OutMessage = @OutMessage + @Cur_Table + 
         '操作失败'                       
        
         ROLLBACK 
         TRAN  
        
         --将错误的批次订单号入异常表  
        
         Insert 
         into 
         异常表(@ExTable)  
         SELECT 
         OrderID  
         FROM 
         #p  
        
         DROP 
         TABLE 
         #k   
        
         DROP 
         TABLE 
         #p   
        
         DROP 
         TABLE 
         #q  
        
         RETURN 
        
         END     
        
         FETCH 
         CUR_ORDERHEDER  
         INTO 
         @Cur_Table,@Cur_Key,@Cur_W,@Cur_K  
        
         END 
        
         ClOSE 
         CUR_ORDERHEDER  
        
         DEALLOCATE 
         CUR_ORDERHEDER

--step.5 资源释放

--step.6 流程处理

这2个部分就不详细说了

最差性能sql进化过程

step.1 not in了就别再distinc了 distinc和not in都是臭名昭著的角色 not in后+dinstinc画蛇添足而已

改后sql：

SELECT TOP 500 a.KeyName FROM bug01 a LEFT JOIN bug02 b on a.KeyName = b.KeyName
WHERE (a.KeyName not in (select b.KeyName From bug02))
ORDER BY a.KeyName asc

step.2 别名别小看别名用图来说话原sql计划

改后sql：

SELECT TOP 500 a.KeyName FROM bug01 a LEFT JOIN bug02 b on a.KeyName = b.KeyName
WHERE (a.KeyName not in (select c.KeyName From bug02 c))
ORDER BY a.KeyName asc

step.3 何必要用外联直接过滤不就得了嘿嘿

改后sql：

SELECT TOP 500 a.KeyName FROM bug01 a
WHERE (a.KeyName not in (select c.KeyName From bug02 c))
ORDER BY a.KeyName asc

step.4 根据luofer同学的建议再进化一次直接EXCEPT

SELECT TOP 500 a.KeyName FROM bug01 a except
SELECT b.KeyName from bug02 b

轉自： http://www.cnblogs.com/dubing/archive/2011/11/11/2245836.html

tearsmo

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
设计高效的存储过程

设计背景由于历史原因，线上库环境数据量及其庞大，很多千万级以上甚至过亿的表。目标是让N张互相关联的表按照一张源表为基表，数据搬移归档这里我们举例N为50 每张表数据5000W最差性能sql进化客串2表KeyName 字段意义名称等相同从bug01 表中取出前500条不在bug02 表中的数据最差性能：?SELECT TOP 5
复制链接

扫一扫

专栏目录