|
**********************************************************
Author:黄山光明顶
mail:leimin@jxfw.com
version:1.0.0
date:2004-1-30
(如需转载,请注明出处!,如果有问题请发MAIL给我:-))
***********************************************************有一网友问:关于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% 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 [1] [2] [3] 下一页
[办公软件]在sybase中插入图片、PDF、文本文件 [办公软件]安装Sybase ASE
[办公软件]linux指令大全(完整篇) [办公软件]Linux新手入门常用命令大全
[办公软件]在RedHat Linux 9里安装gaim0.80 [办公软件]浅谈Linux 下Java 1.5 汉字方块问题解决方法
[办公软件]Linux程序员必读：中文化与GB18030标准 [办公软件]linux指令大全
[办公软件]制作Linux启动盘的四种方法 [办公软件]Linux文件系统的反删除方法
| 
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
