走向DBA[MSSQL篇] 针对大表设计高效的存储过程【原理篇】附最差性能sql语句进化过程客串

设计背景

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

最差性能sql进化客串

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

最差性能：

<code>SELECT</code> <code>TOP</code> <code>500 a.KeyName </code><code>FROM</code> <code>bug01 a </code><code>LEFT</code> <code>JOIN</code> <code>bug02 b </code><code>on</code> <code>a.KeyName = b.KeyName</code>

<code>WHERE</code> <code>(a.KeyName </code><code>not</code> <code>in</code> <code>(</code><code>select</code> <code>distinct</code> <code>b.KeyName </code><code>From</code> <code>bug02))</code>

<code>ORDER</code> <code>BY</code> <code>a.KeyName </code><code>asc</code>

进化体在篇尾揭晓

详细设计

问题点：性能安全容错

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

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秒后的内容。

　　　　　　所以采取临时表策略。

<code> </code><code>CREATE</code> <code>TABLE</code> <code>#p</code>

<code> </code><code>OrderID </code><code>varchar</code><code>(50),</code>

<code> </code><code>primary</code> <code>key</code> <code>(OrderID) </code>

<code>SET</code> <code>@temp_text = </code><code>'INSERT INTO #p '</code><code>+@KeyText</code>

<code>--PRINT @temp_text</code>

<code>SET</code> <code>@KeyText = </code><code>'SELECT OrderID FROM #p'</code>

<code>--如果一级表关联的操作次数比较多那么可以访源表操作以临时表取代物理表</code>

<code>SET</code> <code>@SubKeyText = </code><code>'select 一级表_A_被关联键 From 一级表_A with(nolock) where 一级表_A_关联源表键 in ('</code> <code>+ @KeyText+</code><code>')'</code>

<code>CREATE</code> <code>TABLE</code> <code>#q</code>

<code>SET</code> <code>@temp_text = </code><code>'INSERT INTO #q '</code><code>+@SubKeyText</code>

<code>SET</code> <code>@SubKeyText =</code><code>'SELECT OrderID FROM #q'</code>

<code>--如果一级表关联的操作次数不多可以直接生成数据过滤池</code>

<code>SET</code> <code>@SubKeyTextforA =</code><code>'select 一级表_B_被二级关联键 From 一级表_B with(nolock) where 一级表_B_关联源表键 in ('</code> <code>+ @KeyText+</code><code>')'</code>

<code>SET</code> <code>@SubKeyTextforB =</code><code>'select 一级表_C_被二级关联键 From 一级表_C with(nolock) where 一级表_C_关联源表键 in ('</code> <code>+ @KeyText+</code><code>')'</code>

<code>--如果存在更多层操作在此处可以继续关联资源过滤池 Demo只做到三层</code>

<code>SET</code> <code>@THKeyTextforA =</code><code>'select 二级表_A_被三级关联键 From 二级表_A with(nolock) where 二级表_A_关联一级表键 in ('</code> <code>+ @SubKeyTextforA+</code><code>')'</code>

<code>--SET @THKeyTextforB ='select 二级表_B_被三级关联键 From 二级表_B with(nolock) where 二级表_B_关联一级表键 in (' + @SubKeyTextforBank+')'</code>

--step.3 分表归档操作

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

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

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

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

<code>--将错误的批次订单号入异常表</code>

<code> </code><code>Insert</code> <code>into</code> <code>异常表(@ExTable) </code><code>SELECT</code> <code>OrderID </code><code>FROM</code> <code>#p</code>

<code>--@ExTable用来存放异常数据如果当期批次出错则将本次批次订单信息入库@ExTable下一批次则过滤这些数据再执行</code>

<code> </code><code>SET</code> <code>@KeyText = </code><code>'SELECT TOP '</code><code>+</code><code>CAST</code><code>(@SynSize </code><code>AS</code> <code>VARCHAR</code><code>(10))+</code><code>' '</code><code>+@Base_Key+</code><code>' FROM +'</code><code>+@BaseTable+</code><code>'+ WHERE '</code><code>+@Base_Key+</code><code>' not in (select '</code><code>+@Base_Key+</code><code>' From '</code><code>+@ExTable+</code><code>') '</code>

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

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

用什么还是临时表

<code>--一级直接关联源表主键或为二级被关联的主表</code>

<code> </code><code>INSERT</code> <code>INTO</code> <code>#k </code><code>VALUES</code> <code>(</code><code>'一级表_A'</code><code>,@Base_Key,@KeyText,</code><code>''</code><code>) </code><code>--一级表_A</code>

<code> </code><code>INSERT</code> <code>INTO</code> <code>#k </code><code>VALUES</code> <code>(</code><code>'一级表_B'</code><code>,@Base_Key,@KeyText,</code><code>''</code><code>) </code><code>--一级表_B</code>

<code> </code><code>INSERT</code> <code>INTO</code> <code>#k </code><code>VALUES</code> <code>(</code><code>'一级表_C'</code><code>,@Base_Key,@KeyText,</code><code>''</code><code>) </code><code>--一级表_C</code>

<code>--二级规则间接关联</code>

<code> </code><code>--@SubKeyText相关</code>

<code> </code><code>INSERT</code> <code>INTO</code> <code>#k </code><code>VALUES</code> <code>(</code><code>'二级表_A'</code><code>,</code><code>'二级表_A_关联一级键'</code><code>,@SubKeyText,</code><code>''</code><code>) </code><code>--二级表_A</code>

<code> </code><code>INSERT</code> <code>INTO</code> <code>#k </code><code>VALUES</code> <code>(</code><code>'二级表_B'</code><code>,</code><code>'二级表_B_关联一级键'</code><code>,@SubKeyText,</code><code>''</code><code>) </code><code>--二级表_B</code>

<code> </code><code>INSERT</code> <code>INTO</code> <code>#k </code><code>VALUES</code> <code>(</code><code>'二级表_C'</code><code>,</code><code>'二级表_C_关联一级键'</code><code>,@SubKeyText,</code><code>''</code><code>) </code><code>--二级表_C</code>

<code> </code><code>--自定义操作</code>

<code> </code><code>INSERT</code> <code>INTO</code> <code>#k </code><code>VALUES</code> <code>(</code><code>'特殊表'</code><code>,</code><code>'特殊表关联键'</code><code>,</code><code>'自定义数据过滤方式'</code><code>,</code><code>''</code><code>) </code>

<code> </code><code>--其他自增列处理</code>

<code> </code><code>--修改订单,及其取消修改订单状态历史表</code>

<code> </code><code>INSERT</code> <code>INTO</code> <code>#k </code><code>VALUES</code> <code>(</code><code>'自增表'</code><code>,@Base_Key,@KeyText,</code><code>'自定义字段'</code><code>)</code>

--step.4 处理细节

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

<code>DECLARE</code> <code>CUR_ORDERHEDER </code><code>INSENSITIVE</code> <code>CURSOR</code> <code>FOR</code> <code>SELECT</code> <code>TableName,KeyName,temptext,colname </code><code>FROM</code> <code>#k</code>

<code> </code><code>OPEN</code> <code>CUR_ORDERHEDER</code>

<code> </code><code>FETCH</code> <code>CUR_ORDERHEDER </code><code>INTO</code> <code>@Cur_Table,@Cur_Key,@Cur_W,@Cur_K</code>

<code> </code><code>WHILE @@FETCH_STATUS = 0</code>

<code> </code><code>BEGIN</code>

<code> </code><code>EXECUTE</code> <code>P_Task_Sub_Synchronization</code>

<code> </code><code>@OutParam = @OutParam </code><code>OUT</code><code>, @OutMessage = @OutMessage </code><code>OUT</code><code>,</code>

<code> </code><code>@KeyText = @Cur_W,@</code><code>Table</code><code>= @Cur_Table,@Extension=@Extension,@IsDelSource=@IsDelSource,@KeyName=@Cur_Key,@ColName=@Cur_K</code>

<code> </code><code>--SET @OutMessage = @OutMessage+@OutMessage</code>

<code> </code><code>--PRINT @OutMessage</code>

<code> </code><code>IF @OutParam <> 0 </code>

<code> </code><code>BEGIN</code>

<code> </code><code>SET</code> <code>@OutMessage = @OutMessage + @Cur_Table +</code><code>'操作失败'</code>

<code> </code><code>ROLLBACK</code> <code>TRAN</code>

<code> </code><code>--将错误的批次订单号入异常表</code>

<code> </code><code>Insert</code> <code>into</code> <code>异常表(@ExTable) </code><code>SELECT</code> <code>OrderID </code><code>FROM</code> <code>#p</code>

<code> </code><code>DROP</code> <code>TABLE</code> <code>#k</code>

<code> </code><code>DROP</code> <code>TABLE</code> <code>#p</code>

<code> </code><code>DROP</code> <code>TABLE</code> <code>#q</code>

<code> </code><code>RETURN</code>

<code> </code><code>FETCH</code> <code>CUR_ORDERHEDER </code><code>INTO</code> <code>@Cur_Table,@Cur_Key,@Cur_W,@Cur_K</code>

<code> </code><code>ClOSE</code> <code>CUR_ORDERHEDER</code>

<code> </code><code>DEALLOCATE</code> <code>CUR_ORDERHEDER </code>

--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计划

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))

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

SELECT TOP 500 a.KeyName FROM bug01 a

WHERE (a.KeyName not in (select c.KeyName From bug02 c))

本篇就讲到此处欢迎大家讨论

本文转自熬夜的虫子 51CTO博客，原文链接:http://blog.51cto.com/dubing/712446

走向DBA[MSSQL篇] 针对大表设计高效的存储过程【原理篇】附最差性能sql语句进化过程客串

继续阅读

压缩编码M-JPEG、MPEG4、H.264

ASP编程中20个非常有用的例子

龙珠训练营task04

阿里云天池龙珠计划SQL训练营打卡

阿里云天池龙珠计划SQL训练营day1

实验楼sql进阶之成绩管理系统的数据操作(window)

转详解C#数据库存取图片三大方式

Oracle的基本操作

BMP文件结构及图像每行字节计算方法

磁盘结构及在Linux中的命名

SQL优化SQL语句优化的目的

JAVA高效编程指南

关于SQL语言

SQL语言基础：常用的数据查询语句

neo4j之cypher使用文档

sqlServer根据经纬查距离

走向DBA[MSSQL篇] 针对大表 设计高效的存储过程【原理篇】 附最差性能sql语句进化过程客串

继续阅读

走向DBA[MSSQL篇] 针对大表设计高效的存储过程【原理篇】附最差性能sql语句进化过程客串