创新谈-朱健彦

创新性应用： 1 多表连接处理统计的新优化方法 在做复杂查询取数据时，往往涉及到多表连接，通常两个表连接对性能影响不大，当表连接数在三个或三个以上，且表中含有大量数据时，连接就会使用很大的内存来生成中间数据，造成系统瓶颈。 现有的一般处理方法是使用临时表。将两表连接到一个临时表中，然后这个临时表和另一个表再生成临时表，依此类推，最后得到结果，本质的做法就是把多表连接转化为两表连接，提高效率。 典型的多表连接是为了生成查询和统计报表，其中涉及到使用group by语句分组进行max,sum,avg,count等运算。新的优化方法是: 我们把这些操作放在临时表方法前面的sql语句中执行，将能很大；程度上减少做下一步连接的数据量。 举例说明：小灵通库存管理系统有三个表，小灵通信息表,部门表和部门赠送情况表 PHS( PHS_NO, PHS_NAME, PHS_TYPE, PHS_PRICE) DEPARTMENT(DEPT_NO,DEPT_NAME,DEPT_GRADE) DEPT_PHS_PRESENT(DEPT_NO,PHS_NO, PRESENT _MONTH, PRESENT _YEAR,TOTAL) 假设我们要统计过去2年前端部门UT斯达康小灵通的赠送情况 ①直接的SQL语句是这样写的 SELECT a.present_year, SUM(a.total) FROM DEPT_PHS_PRESENT a JOIN PHS b ON a.PHS_NO=b.PHS_NO JOIN DEPARMENT c ON a.DEPT_NO=c.DEPT_NO WHERE a. PRESENT _YEAR>=’ 2004’ AND b.PHS_TYPE=’UT斯达康’ AND c.DEPT_TYPE=’ 前端’ GROUP BY  a.PRESENT_YEAR ②使用临时表 第一步 SELECT a.PRESENT_YEAR,SUM(a.TOTAL) AS TOTAL,a.DEPT_NO INTO #T FROM DEPT_PHS_PRESENT a JOIN PHS b ON a.PHS_NO=b.PHS_NO WHER a..PRESENT_YEAR >=’ 2004’ AND b.PHS_TYPE=’UT斯达康’ GROUP BY .PRESENT_YEAR 第二步 SELECT a.PRESENT_YEAR,SUM(a.TOTAL) FROM #T a JOIN DEPARTMENT b ON a.DEPT_NO=b.DEPT_NO AND b. DEPT_TYPE=’ 前端’ GROUP BY PRESENT_YEAR ③新思路将汇总操作提前 第一步 SELECT a.PRESENT_YEAR,SUM(a.TOTAL) AS TOTAL,a.DEPT_NO INTO #T FROM DEPT_PHS_PRESENT a JOIN PHS b ON a.PHS_NO=b.PHS_NO WHER a..PRESENT_YEAR >=’ 2004’ AND b.PHS_TYPE=’UT斯达康’

GROUP BY .PRESENT_YEAR, DEPT_NO –提前汇总 第二步 SELECT a.PRESENT_YEAR,SUM(a.TOTAL) FROM #T a JOIN DEPARTMENT b ON a.DEPT_NO=b.DEPT_NO AND b. DEPT_TYPE=’ 前端’ GROUP BY PRESENT_YEAR 按照新的方法，第一步已将PHS按年份和部门作了分组，所以第二步连接中的数据量已大大减少。通过减少中间数据，节省了系统内存，提高了系统速度。与临时表方法比较，第一步处理的数据量大致相同，在第二步中由于数据量大幅减少从而在速度上有很大优势。在大型复杂应用数据库设计中，使用新方法可以有效改善系统性能。   2．通过查分析器和大量数据测试，比较优化查询语句 测试环境: 假设有表Stress_test(id  int, key char(2))  id 上有普通索引;  key 上有簇索引;  id 有有限量的重复;   key 有无限量的重复; 现在需要按逻辑与查询表中key='Az' AND key='Bw' AND key='Cv' 的id 首先我们建立一个测试的数据，为使数据尽量的分布和随即，我们通过RAND()来随机产生2个随机数再组合成一个字符串，首先插入的数据是1,000,000条记录，然后在循环插入到58,000,000条记录。 一般的写法语句：    --语句1     select a.[id] from     (select distinct [id] from stress_test where [key] = 'Az') a,     (select distinct [id] from stress_test where [key] = 'Bw') b ,     (select distinct [id] from stress_test where [key] = 'Cv') c     where a.id = b.id and a.id = c.id    --语句2      select [id]      from stress_test       where [key]='Az' or [key]='Bw' or [key]='Cv'      group by id having(count(distinct [key])=3)     --语句5     SELECT distinct a.[id] FROM stress_test AS a,stress_test AS b,stress_test AS c     WHERE a.[key]='Az' AND b.[key]='Bw' AND c.[key]='Cv'       AND a.[id]=b.[id] AND a.[id]=c.[id]   查询和外连接的写法，按常理子查询的效率是比较高的：    --语句3     select distinct [id] from stress_test A where     not exists (     select 1 from      (select 'Az' as k union all select 'Bw' union all select 'Cv') B     left join stress_test C on  C.id=A.id and B.[k]=C.[key]     where C.id is null)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

             

 

-语句4      select distinct a.id from stress_test a      where not exists      ( select * from keytb c       where not exists       ( select * from stress_test b        where              b.id = a.id              and              c.kf1 = b.[key]        )      )   我们先分析这几条语句（针对5千8百万条数据进行分析）： 请大家要特别留心Estimated row count的值。   语句1:从执行规划中我们可以看出，MSSQLSERVER选择的索引优化非常有规律，先通过CLUSTERED INDEX筛选出符合[KEY]='Az'条件的ID,然后进行HASH MATCH,在找出ID相等的；依次类推最终检索到符合所有条件的记录。中间的Estimated row count的值都不大。   语句2:从执行规划中我们可以看出，是先通过CLUSTERED INDEX筛选出符合 [key]='Az' or [key]='Bw' or [key]='Cv' 符合所有条件的ID，然后分组进行2次HASH MATCH 所有的ID。我们可以看出Estimated row count的值是越来越少，从最初的369,262到最后排序的只有402。   语句3:从执行规划中我们可以看是非常复杂的，是先通过3组 通过CONSTANT SCAN和NON-CLUSTERED INDEX检索出符合 A.ID=C.ID AND [key]='**' 的记录3组，然后分组进行外键匹配，再将3组的数据合并，排序，然后再和一个NON-CLUSTERED INDEX检索出的记录集进行外键匹配，我们可以看出MSSQLSERVER会对所有的记录（5千万条）记录进行分组，Estimated row count的值是：58,720,000,所以这句T-SQL的瓶颈是对5千万条记录进行分组。   语句4:从执行规划中我们可以看和语句3有相似之处，都要对所有的记录（5千万条）记录进行分组，所以这是检索的瓶颈，而且使用的索引都是NON-CLUSTERED INDEX。   语句5：从执行规划中我们可以看出，先通过CLUSTERED INDEX检索出符合[Key]='Az'的记录集，然后进行HASH MATCH和SORTS,因为数量少所以是非常会的，在和通过NON-CLUSTERED INDEX检索[KEY]='Bw'的记录进行INNER JOIN,在和通过CLUSTERED INDEX检索[KEY]='Cv'的记录进行合并,最后是对4百万条数据进行分组检索，如果是6列，我们可以看出Estimated row count的值是递增，越来越大，最后的分组检索的Estimated row count的值是3.46E+15,这已经形成巨大的瓶颈。   我们可以先测试一下小的数据量（50000条）； 大家可以下面测试脚本的：    Select @maxgroup=500    Select @maxLoop=100..

从测试的的数据来看，语句5的效率是最高的，几乎没有花费时间，而语句2的效率只能说是一般。如果测试到这里就结束了，我们可以毫不犹豫的选择语句 5 :-(,继续进行下面的测试..... 我们测试百万条以上的记录：  1.先对1百万条记录进行测试（选取3列）  2.先对1百万条记录进行测试（选取6列）  3.对5千万条数据测试（选取3列）  4.对5千万条数据测试（选取6列）   统计表1：  ----------------------------------------------------------------------  |------------------语句 1----语句 2----语句 3----语句 4----语句 5----|  | 1百万（3列）    0.77%     0.41%    49.30%     48.99%     0.52%  | 1百万（6列）     1.61%     0.81%    48.99%     47.44%     1.14%  | 5千万（3列）     0.14%     0.18%    48.88%     48.86%     1.93%  | 5千万（6列）     0.00%     0.00%     0.00%      0.00%   100.00% 统计表2：  ----------------------------------------------------------------------  |------------------语句 1----语句 2----语句 3----语句 4----语句 5----|  | 1百万（3列）     9ms       22ms     723ms     753ms      4ms  | 1百万（6列）      15ms      38ms     764ms     773ms     11ms  | 5千万（3列）     575ms     262ms  110117ms  110601ms  12533ms  | 5千万（6列）    1070ms     576ms  107988ms  109704ms     10m 以上   测试总结,创建经验：（我们可以比较关注：语句 2和语句 5） 1.在1百万条记录的情况下，语句 5是最快的，但在5千万条记录下是最慢的。这说明INDEX的优化一定的情况下，数据量不同，检索的效率也是不同的。我们平时在写T-SQL时一般关注的时INDEX的使用，只要我们写的T-SQL是利用CLUSTERED INDEX，我们就认为是最优化了，其实这是一个误区，我们还要关注Estimated row count的值，大量的I/O操作是我们应该关注的，所以我们应该根据数据量的不同选择相应的T-SQL语句，不要认为在小数据量下是最高的在大数据量的状态下也许是最慢的:-(。   2.在执行规划中最快的，并不是运行最快的，我们可以看在1百万（6列）在这行中，语句 2和语句 5的比例是0.81％：1.14％，但实际的运行效率是，38ms:11ms。所以，我们在选择T-SQL是要考虑本地I/O的速度，所以在优化语句时不仅要看执行规划还要计算一下具体的效率。 在测试的语句上加入：     SET STATISTICS TIME  ON/OFF     SET STATISTICS IO  ON/OFF 是一个很好的调试方法。   3.综合评价 语句 2的效率是最高的，执行效率没有随数据量变化而有很大的差别。 4.执行规划越简单的语句（语句1），综合效率越高，反之则越低（语句3，语句4）。 5.在平时写T-SQL语句时，一定要根据不同的数据量进行测试，虽然都是用CLUSTERED INDEX，但检索的效率却大相径庭。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

行业借鉴经验： 1．数据库压力测试 1） 数据库连接测试 数据库连接测试确定数据库服务器是否能够支持期望的连接数，通过模拟期望的用户并发连接和交易量，测量服务器的负荷、性能、内存使用情况和应用软件的稳定性。该测试的结果将确定数据库服务器能够持续处理的负荷的上限。 2）  破坏性测试 按照设计连接的客户端连接数量进行测试，把应用服务器处理请求的设计频度增加1－10倍，分别测试出现错误的状态和和出现错误的比率，考察是否出现不可恢复错误。 3)           强度稳定性测试 选择一种负荷比设计负荷重的情况（应用服务器处理请求的频度为应用服务器处理请求的设计频度的1.5倍），进行24小时稳定性测试。   2. 如何解决数据库阻塞 1)  引起阻塞的可能原因： ①提交执行时间长的查询。 ②查询不适当地使用游标,但使用游标可能比使用面向集合的查询慢。 ③取消没有提交或回滚的查询。 ④分布式客户端/服务器死锁。 2)   数据库避免阻塞方法 ①对每个查询使用查询超时。 ②对每个查询使用锁定超时。    ③使用绑定连接。 ④检查客户端应用程序。 ⑤阻塞问题常要求检查应用程序提交的 SQL 语句本身，以及检查与连接管理、所有结果行的处理等有关的应用程序行为本身。 3)   应用程序中避免阻塞的方法 ①不要使用或设计使用户得以填写编辑框的应用程序，这可能导致应用程序提交运行时间过长的查询，从而导致阻塞问题。 ②不要使用或设计使用户得以在事务内输入内容的应用程序。    ③允许取消查询。    ④使用查询或锁定超时，防止失控查询和避免分布式死锁。    ⑤使事务尽可能简短。    ⑥显式控制连接管理。     3.  SQL Server安全设置 1)  防火墙，端口和安全补丁的设置

禁用 NetBIOS 和 SMB――外围网络中的服务器应禁用所有不必要的协议，包括 NetBIOS 和服务器消息块 (SMB)。Web 服务器和域名系统 (DNS) 服务器不需要 NetBIOS 或 SMB。禁用这两个协议，以对抗用户枚举的威胁。 操作方法: 禁用SMB:使用"本地连接"属性中的"TCP/IP 属性"（TCP/IP 指传输控制协议/Internet 协议）对话框删除"Microsoft 网络的文件和打印机共享"和"Microsoft 网络客户端"，从而禁用 SMB。 禁用NetBIOS: 停用 "本地连接"属性中的"TCP/IP 属性"上的 NetBios。 2)  删除旧安装文件, 删除示例数据库 删除c:/winnt下的sqlstp.log、sqlsp.log      删除 c:/Program Files/Microsoft SQL Server/MSSQL/Install下的setup.iss文件      删除 Northwind,Pus示例数据库 3)        将登录审核级别设置为“失败”，启动安全审核。 4)        管理扩展存储过程，因为有些系统的存储过程能很容易地被人利用起来提升权限或进行破坏。 ① 去掉不需要的扩展存储过程 ② 丢弃不需要OLE自动存储过程 ③ 去掉不需要的注册表访问的存储过程 5）删除 BUILTIN/Administrators 服务器登录 6）不启用 SQL 来宾用户帐户 7）不对 Public 角色授予权限 8）严格控制远程管理   4．对Oracle索引使用的建议 B-tree索引：是基于二叉树的，适用于快速定位行,当查询取得的行数占总行数比例较小时,B-tree比全文检索更有效，当查询超过10%时就不能提高很多的性能。 反向索引： 不适合做区域扫描，只能应用于特殊场合。 降序索引：适用于有降序排列的查询语句，提供了快速的降序搜索。 位图索引：适合集中读取，不适合插入和修改，比B-tree索引节省空间，不支持行级锁定，最好用于列的唯一值除以行数为一个很小的值的情况。 函数索引：适用于查询条件中包含函数的情况，必须使用基于成本的优化器。   5．①数据文件通常读更加活跃，大量的用户随机地读 (一部分连续)，为优化读性能我们可以设置（一个或两个镜像），使用RAID 1+0 or RAID 0+1, RAID 5对于大规模写操作不是最好. ②优化日志 (最小化VLF的数量),过多的VLF 增加了与日志相关操作的负载 (transaction logging, log backups, logreader, triggers (inserted/deleted)等.),解决办法 执行 DBCC LOGINFO，行的数量就是VLF的数量，如果VLF太多，需要释放日志的空间。 ③行不能跨页面，因此记录的大小会影响磁盘空间、缓存利用率、并发性 ④锁的最低粒度是行– 垂直或水平分区能够改进缓存利用率和并发性 ⑤定义关系的重复键可以在执行联接操作时通过增加更多的选择来提高性能

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

应用难点技巧： 1. 在存储过程中使用系统存储过程ＳＰ＿Ｅｘｅｃｕｔｅｓｑｌ的注意事项 在编写自己的存储过程的时候，往往在很多的情况下，会使用到系统的存储过程ＳＰ＿Ｅｘｅｃｕｔｅ。但是需要的注意的是，如果你在这个存储过程的参数（一般是一段ＳＱＬ语句）当中进行了临时Ｔａｂｌｅ的操作，那末对于调用者来说，这个临时Ｔａｂｌｅ是不可见的，也就是说你无法通过临时Ｔａｂｌｅ来在调用者和被调用者之间传递值。解决的方法是使用全局临时Ｔａｂｌｅ，也就是“＃＃”开头的Ｔａｂｌｅ。   2. Update语句使用技巧: 累积求和问题 有一表：       col1       value        running_tot      1         10          0          （10）      2         15          0          （25）      3         50          0          （75）           ……   若要将Value列累积求和的结果放到running_tot 列，可以这样： DECLARE @run_total int SELECT @run_total=0 UPDATE running_tot   SELECT @run_total=running_tot=run_total+value FROM running_tot   这是一种高效的方法，使用了UPDATE语句中不太常用的特性，这种方法只使用一次逻辑读来计算所有的值。（任何逻辑读的次数都不可以少于1次）。可以把这个过程看作从右往左发生。   3. 临时表问题 在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免log，提高速度；如果数据量不大，为了缓和系统表的资源，建议先create table，然后insert。如果临时表的数据量较大，需要建立索引，那么应该将创建临时表和建立索引的过程放在单独   4． 如果用到其他库的Table或View，务必在当前库中建立View来实现跨库操作，最好不要直接使用“databse.dbo.table_name”，因为sp_depends不能显示出该SP所使用的跨库table或view，不方便校验。   5．求分割的子串 create function getstrofindex (@str varchar(8000),@index int =0) returns varchar(8000) as begin   declare @str_return varchar(8000)   declare @start int   declare @next int   declare @location int   select @start =1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

select @next =1   select @location = charindex(',',@str,@start)   while (@location <>0 and @index > @next )   begin     select @start = @location +1     select @location = charindex(',',@str,@start)     select @next =@next +1   end   if @location =0 select @location =len(@str)+1   select @str_return = substring(@str,@start,@location -@start)   if (@index <> @next ) select @str_return = ''   return @str_return end go select dbo.getstrofindex('aa vv,cc ee, sdfasdfasf',3)   6．在客户端调用SQLSERVER的DTS包(并且可以输入参数)      先在SERVER端建立一个DTS包： 比如一个导入EXCEL文件到SQLSERVER的DTS包：DTS_ExcelToSqlserver   在客户端建立一个批处理文件   CRUNDTS.BAT   用文本编辑器编辑：   @@echo off   if [%1] == [] goto Usage if [%2] == [] goto Usage if [%3] == [] goto Usage if [%4] == [] goto Usage cls   SET LOGFILE=c:/DTSRUN.LOG   echo %TIME% Manual DTS package start run.........     >> %LOGFILE% echo ------------------------------------------       >> %LOGFILE%   @dtsrun /S %1 /U %2 /P %3 /N %4          >> %LOGFILE% if errorlevel 1 goto Error echo %TIME% Manual DTS package end.........           >> %LOGFILE% echo ------------------------------------------       >> %LOGFILE%

PAUSE goto FINISH   :Error echo ------------------------------------------- >> %LOGFILE% echo DTSRUN ERROR PLS CHECK LOG ......... echo %TIME% GlobalStore Backup abnormal end.  >> %LOGFILE% pause goto FINISH   :Usage echo. echo Usage:crundts %1 servername %2 use_name %3 password %4 dts_name :FINISH   保存该文件： 然后你可以在任何程序中调用，也可以手工执行，输入参数。   测试运行： 在DOS提示符下运行：CRUNDTS SERVERNAME sa password DTS_ExcelToSqlserver   日志信息：   10:24:29.38 Manual DTS package start run.........     ------------------------------------------  DTSRun:  Loading...   Error:  -2147217900 (80040E14); Provider Error:  14262 (37B6)    Error string:     Error source:     Help file:     Help context:  0 10:24:29.66 Manual DTS package end.........            ------------------------------------------  10:27:29.41 Manual DTS package start run.........     ------------------------------------------       DTSRun:  Loading... DTSRun:  Executing... DTSRun OnStart:  DTSStep_DTSDataPumpTask_1 DTSRun OnProgress:  DTSStep_DTSDataPumpTask_1; 23 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 23...

7. Check约束使用 Check约束中可用来提高性能的操作符只有如下这些：    BETWEEN, AND, OR, <, <=, >, >=, =. 其它的都只能用来实现完整性保护    不要用With NOCHECK ALTER TABLE with CHECK - 所有的已有数据都被检查和验证 ALTER TABLE with NOCHECK–加速创建过程但是对于以后使用约束没有任何性能提高

 

 

 

文章请同时提交信箱：bestdba@ciw.com.cn  mulibox@yahoo.com.cn