MSSQL编程：一句T-SQL语句激发的思索

支持AIX、FreeBSD、HP-UX、Linux、MacOS、NovellNetware、OpenBSD、OS/2Wrap、Solaris、Windows等多种操作系统语句
**********************************************************

Author:黄山光亮顶

mail:leimin@jxfw.com

version:1.0.0

date:2004-1-30

(如需转载,请说明出处!,假如有成绩请发MAIL给我:-))

***********************************************************有一网友问:关于MSSQLSERVER索引优化成绩:
有表Stress_test(idint,keychar(2))
id上有一般索引;
key上有簇索引;
id有无限量的反复;
key有没有限量的反复;

如今我必要按逻辑与查询表中key=AzANDkey=BwANDkey=Cv的id

讨教妙手最无效的查询语句

测试情况：
Hardware:P42.6+512M+80G
Software:windowsserver2003(EnterpriseEdition)+Sqlserver2000+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

selecta.[id]from
(selectdistinct[id]fromstress_testwhere[key]=Az)a,
(selectdistinct[id]fromstress_testwhere[key]=Bw)b,
(selectdistinct[id]fromstress_testwhere[key]=Cv)c
wherea.id=b.idanda.id=c.id

--语句2

select[id]
fromstress_test
where[key]=Azor[key]=Bwor[key]=Cv
groupbyidhaving(count(distinct[key])=3)

--语句5

SELECTdistincta.[id]FROMstress_testASa,stress_testASb,stress_testASc
WHEREa.[key]=AzANDb.[key]=BwANDc.[key]=Cv
ANDa.[id]=b.[id]ANDa.[id]=c.[id]

但作为T-SQL的所谓“妙手”大概会以为这类写法很“土”，也显得没有程度，以是会选择一些子查询和外毗连的写法，按常理子查询的效力是对照高的：

--语句3

selectdistinct[id]fromstress_testAwhere
notexists(
select1from
(selectAzaskunionallselectBwunionallselectCv)B
leftjoinstress_testConC.id=A.idandB.[k]=C.[key]
whereC.idisnull)

--语句4

selectdistincta.idfromstress_testa
wherenotexists
(select*fromkeytbc
wherenotexists
(select*fromstress_testb
where
b.id=a.id
and
c.kf1=b.[key]
)
)


我们先剖析这几条语句（针对5千8百万条数据举行剖析）：

请人人要出格把稳Estimatedrowcount的值。

语句1:从实行计划中我们能够看出，MSSQLSERVER选择的索引优化十分有纪律，先经由过程CLUSTEREDINDEX选择出切合[KEY]=Az前提的ID,然落后行HASHMATCH,在找出ID相称的；顺次类推终极检索到切合一切前提的纪录。两头的Estimatedrowcount的值都不年夜。

语句2:从实行计划中我们能够看出，是先经由过程CLUSTEREDINDEX选择出切合[key]=Azor[key]=Bwor[key]=Cv切合一切前提的ID，然后分组举行2次HASHMATCH一切的ID。我们能够看出Estimatedrowcount的值是愈来愈少，从最后的369,262到最初排序的只要402。

语句3:从实行计划中我们能够看长短常庞大的，是先经由过程3组经由过程CONSTANTSCAN和NON-CLUSTEREDINDEX检索出切合A.ID=C.IDAND[key]=**的纪录3组，然后分组举行外键婚配，再将3组的数据兼并，排序，然后再和一个NON-CLUSTEREDINDEX检索出的纪录集举行外键婚配，我们能够看出MSSQLSERVER会对一切的纪录（5万万条）纪录举行分组，Estimatedrowcount的值是：58,720,000,以是这句T-SQL的瓶颈是对5万万笔记录举行分组。

语句4:从实行计划中我们能够看和语句3有类似的地方，都要对一切的纪录（5万万条）纪录举行分组，以是这是检索的瓶颈，并且利用的索引都是NON-CLUSTEREDINDEX。

语句5：从实行计划中我们能够看出，先经由过程CLUSTEREDINDEX检索出切合[Key]=Az的纪录集，然落后行HASHMATCH和SORTS,由于数目少以是长短常会的，在和经由过程NON-CLUSTEREDINDEX检索[KEY]=Bw的纪录举行INNERJOIN,在和经由过程CLUSTEREDINDEX检索[KEY]=Cv的纪录举行兼并,最初是对4百万条数据举行分组检索，假如是6列，我们能够看出Estimatedrowcount的值是递增，愈来愈年夜，最初的分组检索的Estimatedrowcount的值是3.46E+15,这已构成伟大的瓶颈。

我们能够先测试一下小的数据量（50000条）；

人人能够上面测试剧本的：

Select@maxgroup=500
Select@maxLoop=100

----------------------------------------------------------------------
|------------------语句1----语句2----语句3----语句4----语句5----|
|5万（3列）5ms19ms37ms59ms0ms
|5万（6列）1ms26ms36ms36ms1ms


从测试的的数据来看，语句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列）9ms22ms723ms753ms4ms
|1百万（6列）15ms38ms764ms773ms11ms
|5万万（3列）575ms262ms110117ms110601ms12533ms
|5万万（6列）1070ms576ms107988ms109704ms10m以上


测试总结：（我们能够对照存眷：语句2和语句5）
1.在1百万笔记录的情形下，语句5是最快的，但在5万万笔记录下是最慢的。这申明INDEX的优化必定的情形下，数据量分歧，检索的效力也是分歧的。我们平常在写T-SQL时一样平常存眷的时INDEX的利用，只需我们写的T-SQL是使用CLUSTEREDINDEX，我们就以为是最优化了，实在这是一个误区，我们还要存眷Estimatedrowcount的值，大批的I/O操纵是我们应当存眷的，以是我们应当依据数据量的分歧选择响应的T-SQL语句，不要以为在小数据量下是最高的在年夜数据量的形态下大概是最慢的:-(。


2.在实行计划中最快的，并非运转最快的，我们能够看在1百万（6列）在这行中，语句2和语句5的比例是0.81％：1.14％，但实践的运转效力是，38ms:11ms。以是，我们在选择T-SQL是要思索当地I/O的速率，以是在优化语句时不但要看实行计划还要盘算一下详细的效力。

在测试的语句上到场：

SETSTATISTICSTIMEON/OFF
SETSTATISTICSIOON/OFF
是一个很好的调试办法。


3.综合评价，语句2的效力是最高的，实行效力没有随数据质变化而有很年夜的不同。


4.实行计划越复杂的语句（语句1），综合效力越高，反之则越低（语句3，语句4）。


5.在平常写T-SQL语句时，必定要依据分歧的数据量举行测试，固然都是用CLUSTEREDINDEX，但检索的效力却天差地别。

--//测试剧本
USENorthwind
GO
ifexists(select*fromsysobjectswherename=Nstress_testandtype=U)
Droptablestress_test
GO
--//界说测试的表stress_test,寄存一切的测试数据
Createtablestress_test([id]int,[key]char(2))

GO
--//拔出测试的数据
Setnocounton
--//变量界说
Declare@idint--//Stress_testID值
Declare@keychar(2)--//Stress_test[key]值
Declare@maxgroupint--//组最年夜的轮回数
Declare@maxLoopint--//ID最年夜的轮回数
Declare@tempGroupint--//一时变量
Declare@tempLoopint--//一时变量
Declare@tempint1int--//一时变量
Declare@tempint2int--//一时变量
Declare@rowcountint--//纪录事件提交的行数

--//初始化变量
Select@id=1
Select@maxgroup=1000
Select@maxLoop=1000
Select@tempGroup=1
Select@tempLoop=1
Select@key=
Select@rowcount=0

while@tempLoop<=@maxLoop
begin
while@tempGroup<=@maxGroup
begin
select@tempint1=65+convert(int,rand()*50)
select@tempint2=65+convert(int,rand()*100)
if(@tempint1>=122or@tempint2>=122)
begin
select@tempint1=@tempint1-100
select@tempint2=@tempint2-100

if(@tempint1<=65or@tempint2<=65)
begin
select@tempint1=@tempint1+57
select@tempint2=@tempint2+57
end
end
select@key=char(@tempint1)+char(@tempint2)
if@rowcount=0
begintranins
insertintostress_test([id],[key])values(@id,@key)
select@rowcount=@rowcount+1

if@rowcount>3000--//判别当行数到达3000条时，入手下手提交事件
begin
committranins
select@rowcount=0
end

select@tempGroup=@tempgroup+1
end
if@rowcount>0
begin
committranins
select@rowcount=0
end

select@tempGroup=1
select@id=@id+1
select@tempLoop=@tempLoop+1
end
GO
--//删除KEY值为NULL的纪录
deletestress_testwhere[key]isnull
GO
--//创建簇索引PK_STRESS
CreateClusteredindexpk_stressonstress_test([Key])
--//创建非簇索引NI_STRESS_ID
CreateNonClusteredindexNI_stress_idonstress_test([id])
GO
--//界说测试的表keytb
ifexists(select*fromsysobjectswherename=Nkeytbandtype=U)
Droptablekeytb
GO
createtablekeytb-----//寄存你必要婚配的值的表
(
kf1varchar(20)
)

--//寄存你必要婚配的值，暂定为三个
insertintokeytb(kf1)values(Az);
insertintokeytb(kf1)values(Bw);
insertintokeytb(kf1)values(Cv);

--insertintokeytb(kf1)values(Du);
--insertintokeytb(kf1)values(Ex);
--insertintokeytb(kf1)values(Fy);
GO


上面我们就入手下手测试几种T-SQL的INDEX优化成绩：

--先对1百万条/1亿笔记录举行测试（拔取3列）的T-SQL:

PRINT第一种语句：
SETSTATISTICSTIMEON
SETSTATISTICSIOON
selecta.[id]from
(selectdistinct[id]fromstress_testwhere[key]=Az)a,
(selectdistinct[id]fromstress_testwhere[key]=Bw)b,
(selectdistinct[id]fromstress_testwhere[key]=Cv)c
wherea.id=b.idanda.id=c.id
GO
PRINT第二种语句：
select[id]
fromstress_test
where[key]=Azor[key]=Bwor[key]=Cv
groupbyidhaving(count(distinct[key])=3)
GO
PRINT第三种语句：
selectdistinct[id]fromstress_testAwhere
notexists(
select1from
(selectAzaskunionallselectBwunionallselectCv)B
leftjoinstress_testConC.id=A.idandB.[k]=C.[key]
whereC.idisnull)
GO
PRINT第四种语句：
selectdistincta.idfromstress_testa
wherenotexists
(select*fromkeytbc
wherenotexists
(select*fromstress_testb
where
b.id=a.id
and
c.kf1=b.[key]
)
)
GO
PRINT第五种语句：
SELECTdistincta.[id]FROMstress_testASa,stress_testASb,stress_testASc
WHEREa.[key]=AcANDb.[key]=BbANDc.[key]=Ca
ANDa.[id]=b.[id]ANDa.[id]=c.[id]

GO
SETSTATISTICSTIMEOFF
SETSTATISTICSIOOFF

--先对1百万条/1亿笔记录举行测试（拔取6列）的T-SQL:
PRINT第一种语句：
SETSTATISTICSTIMEON
SETSTATISTICSIOON
selecta.[id]from
(selectdistinct[id]fromstress_testwhere[key]=Az)a,
(selectdistinct[id]fromstress_testwhere[key]=Bw)b,
(selectdistinct[id]fromstress_testwhere[key]=Cv)c,
(selectdistinct[id]fromstress_testwhere[key]=Du)d,
(selectdistinct[id]fromstress_testwhere[key]=Ex)e,
(selectdistinct[id]fromstress_testwhere[key]=Fy)f
wherea.[id]=b.[id]anda.[id]=c.[id]anda.[id]=d.[id]anda.[id]=e.[id]anda.[id]=f.[id]
GO
PRINT第二种语句：
select[id]
fromstress_test
where[key]=Azor[key]=Bwor[key]=Cvor[Key]=Duor[Key]=Exor[Key]=Fy
groupbyidhaving(count(distinct[key])=6)
GO
PRINT第三种语句：
selectdistinct[id]fromstress_testAwhere
notexists(
select1from
(selectAzaskunionallselectBwunionallselectCvunionallselectDuunionallselectExunionallselectFy)B
leftjoinstress_testConC.id=A.idandB.[k]=C.[key]
whereC.idisnull)
GO
PRINT第四种语句：
selectdistincta.idfromstress_testa
wherenotexists
(select*fromkeytbc
wherenotexists
(select*fromstress_testb
where
b.id=a.id
and
c.kf1=b.[key]
)
)
GO
PRINT第五种语句：
SELECTdistincta.[id]FROMstress_testASa,stress_testASb,stress_testASc,stress_testASd,stress_testASe,stress_testASf
WHEREa.[key]=AzANDb.[key]=BwANDc.[key]=CvANDd.[key]=DuANDe.[key]=ExANDf.[key]=Fy
anda.[id]=b.[id]anda.[id]=c.[id]anda.[id]=d.[id]anda.[id]=e.[id]anda.[id]=f.[id]

GO
SETSTATISTICSTIMEOFF
SETSTATISTICSIOOFF

请参考:

http://expert.csdn.net/Expert/topic/2630/2630484.xml?temp=.9921686


出于效率方面的考虑，InnoDB数据表的数据行级锁定实际发生在它们的索引上，而不是数据表自身上。显然，数据行级锁定机制只有在有关的数据表有一个合适的索引可供锁定的时候才能发挥效力。

多走走一此相关论坛，多看一些实例开发，多交流0经验，没什么的，我也是刚学没多久!加油

语句级快照和事务级快照终于为SQLServer的并发性能带来了突破。个人感觉语句级快照大家应该应用。事务级快照，如果是高并发系统还要慎用。如果一个用户总是被提示修改不成功要求重试时，会杀人的！

XML字段类型更好的解决了XML数据的操作。XQuery确实不错，但是个人对其没好感。（CSDN的开发者应该是相当的熟了！）

需要注意的一点，也是我使用过程中发现的一个问题。在建立function->schema->table后，如果在现有的分区表上建立没有显式声明的聚集索引时，分区表会自动变为非分区表。这一点很让我纳闷。

但换公司用MSSQL2K感觉自己好像根本就不了解MSSQL。什么DTS触发器以前根本没用过。

发几份SQL课件，以飨阅者

对于微软系列的东西除了一遍遍尝试还真没有太好的办法