**********************************************************
author:黃山光明頂
mail:[email protected]
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 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，但檢索的效率卻大相徑庭。
--//測試腳本
use northwind
go
if exists(select * from sysobjects where name=n'stress_test' and type='u')
drop table stress_test
go
--//定義測試的表stress_test,存放所有的測試數據
create table stress_test([id] int,[key] char(2))
go
--//插入測試的數據
set nocount on
--//變量定義
declare @id int --//stress_test id 值
declare @key char(2) --//stress_test [key] 值
declare @maxgroup int --//組最大的循環數
declare @maxloop int --//id最大的循環數
declare @tempgroup int --//臨時變量
declare @temploop int --//臨時變量
declare @tempint1 int --//臨時變量
declare @tempint2 int --//臨時變量
declare @rowcount int --//記錄事務提交的行數
--//初始化變量
select @id=1
select @maxgroup=1000
select @maxloop=1000
select @tempgroup=1
select @temploop=1
select @key=''
select @rowcount=0
while @temploop<[email protected]
begin
while @tempgroup<[email protected]
begin
select @tempint1=65+convert(int,rand()*50)
select @tempint2=65+convert(int,rand()*100)
if (@tempint1>=122 or @tempint2>=122)
begin
select @[email protected]
select @[email protected]
if (@tempint1<=65 or @tempint2<=65)
begin
select @[email protected]+57
select @[email protected]+57
end
end
select @key=char(@tempint1)+char(@tempint2)
if @rowcount=0
begin tran ins
insert into stress_test([id],[key])values(@id,@key)
select @[email protected]+1
if @rowcount>3000 --//判斷當行數達到3000條時，開始提交事務
begin
commit tran ins
select @rowcount=0
end
select @[email protected]+1
end
if @rowcount>0
begin
commit tran ins
select @rowcount=0
end
select @tempgroup=1
select @[email protected]+1
select @[email protected]+1
end
go
--//刪除key值為null的記錄
delete stress_test where [key]is null
go
--//建立簇索引pk_stress
create clustered index pk_stress on stress_test([key])
--//建立非簇索引ni_stress_id
create nonclustered index ni_stress_id on stress_test([id])
go
--//定義測試的表keytb
if exists(select * from sysobjects where name=n'keytb' and type='u')
drop table keytb
go
create table keytb -----//存放你需要匹配的值的表
(
kf1 varchar(20)
)
--//存放你需要匹配的值，暫定為三個
insert into keytb(kf1) values('az');
insert into keytb(kf1) values('bw');
insert into keytb(kf1) values('cv');
--insert into keytb(kf1) values('du');
--insert into keytb(kf1) values('ex');
--insert into keytb(kf1) values('fy');
go
下面我們就開始測試幾種t-sql的index優化問題：
--先對1百萬條/1億條記錄進行測試（選取3列）的t-sql:
print '第一種語句：'
set statistics time on
set statistics io on
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
go
print '第二種語句：'
select [id]
from stress_test
where [key]='az' or [key]='bw' or [key]='cv'
group by id having(count(distinct [key])=3)
go
print '第三種語句：'
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)
go
print '第四種語句：'
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]
)
)
go
print '第五種語句：'
select distinct a.[id] from stress_test as a,stress_test as b,stress_test as c
where a.[key]='ac' and b.[key]='bb' and c.[key]='ca'
and a.[id]=b.[id] and a.[id]=c.[id]
go
set statistics time off
set statistics io off
--先對1百萬條/1億條記錄進行測試（選取6列）的t-sql:
print '第一種語句：'
set statistics time on
set statistics io on
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,
(select distinct [id] from stress_test where [key] = 'du') d,
(select distinct [id] from stress_test where [key] = 'ex') e,
(select distinct [id] from stress_test where [key] = 'fy') f
where a.[id] = b.[id] and a.[id] = c.[id] and a.[id] = d.[id] and a.[id] = e.[id] and a.[id] = f.[id]
go
print '第二種語句：'
select [id]
from stress_test
where [key]='az' or [key]='bw' or [key]='cv' or [key]='du'or [key]='ex'or [key]='fy'
group by id having(count(distinct [key])=6)
go
print '第三種語句：'
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'union all select 'du'union all select 'ex'union all select 'fy') b
left join stress_test c on c.id=a.id and b.[k]=c.[key]
where c.id is null)
go
print '第四種語句：'
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]
)
)
go
print '第五種語句：'
select distinct a.[id] from stress_test as a,stress_test as b,stress_test as c,stress_test as d,stress_test as e,stress_test as f
where a.[key]='az' and b.[key]='bw' and c.[key]='cv' and d.[key]='du' and e.[key]='ex' and f.[key]='fy'
and a.[id] = b.[id] and a.[id] = c.[id] and a.[id] = d.[id] and a.[id] = e.[id] and a.[id] = f.[id]
go
set statistics time off
set statistics io off
請參考:
http://expert.csdn.net/expert/topic/2630/2630484.xml?temp=.9921686
