MS SQLSERVER索引优化


有一网友问:关于MS SQLSERVER索引优化问题:
 有表Stress_test(id  int, key char(2))
        id 上有普通索引;
        key 上有簇索引;


        id 有有限量的重复;
        key 有无限量的重复;

现在我需要按逻辑与查询表中key='Az' AND key='Bw' AND key='Cv' 的id

求教高手最有效的查询语句

测试环境:
     Hardware:P4 2.6+512M+80G
     Software:windows server 2003(Enterprise Edition)+Sqlserver 2000 +sp3a

  首先我们建立一个测试的数据,为使数据尽量的分布和随即,我们通过RAND()来随机产生2个随机数再组合成一个字符串,首先插入的数据是1,000,000条记录,然后在循环插入到58,000,000条记录。
   因为是随机产生的数据,所以如果你自己测试的数据集和我测试的会不一样,但对索引的优化和运行的效率是一样的。
   下面的“--//测试脚本”是产生测试数据的脚本,你可以根据需要修改 @maxgroup, @maxLoop的值,比如测试1百万的记录可以:

     Select @maxgroup=1000
     Select @maxLoop=1000

如果要测试5千万:

     Select @maxgroup=5000
     Select @maxLoop=10000

 

所以如果你的SERVER或PC比较慢,请耐心等待.....,
 (在我的PC上运行的速度是插入1百万条的时间是1.14m,插入5千八百万条的时间是19.41m,重新建立INDEX的时间是34.36m)

 

作为一般的开发人员很容易就想到的语句:

   --语句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]

但作为T-SQL的所谓“高手”可能会认为这种写法很“土”,也显得没有水平,所以会选择一些子查询和外连接的写法,按常理子查询的效率是比较高的:

   --语句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

----------------------------------------------------------------------
 |------------------语句 1----语句 2----语句 3----语句 4----语句 5----|
 | 5万(3列)        5ms       19ms     37ms     59ms      0ms
 | 5万(6列)        1ms       26ms     36ms     36ms     1ms
 

从测试的的数据来看,语句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%  &n

本文作者:



相关阅读:
javascript for循环设法提高性能
用session做客户验证时的注意事项
jQuery的Ajax时无响应数据的解决方法
Javascript 入门基础学习
Js 获取HTML DOM节点元素的方法小结
CSS实例教程:使用CSS3实现实现文字渐变方法
用CSS控制网站总队形
Windows Vista系统的术语介绍
JavaScript Table行定位效果
在JQuery dialog里的服务器控件 事件失效问题
IIS6架设网站的问题总结
asp.net中一款极为简单实用的图表插件(jquery)
用asp实现网页邮箱访问的方法
Web服务器控件:Image控件
快速导航

Copyright © 2016 phpStudy |