一句T-SQL語句引發的思考 轉帖
2024-07-21 02:34:43
供稿：網友
關于MS SQLSERVER索引優化問題:
有表Stress_test(id int, key char(2))
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]id 上有普通索引;
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]key 上有簇索引;
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]id 有有限量的重復;
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]key 有無限量的重復;
現在我需要按邏輯與查詢表中key='Az' AND key='Bw' AND key='Cv' 的id
求教高手最有效的查詢語句
測試環境：
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]Hardware:P4 2.6+512M+80G
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]Software:windows server 2003(EnterPRise Edition)+Sqlserver 2000 +sp3a
[$nbsp][$nbsp]首先我們建立一個測試的數據，為使數據盡量的分布和隨即，我們通過RAND()來隨機產生2個隨機數再組合成一個字符串，首先插入的數據是1,000,000條記錄，然后在循環插入到58,000,000條記錄。
[$nbsp][$nbsp][$nbsp]因為是隨機產生的數據，所以假如你自己測試的數據集和我測試的會不一樣，但對索引的優化和運行的效率是一樣的。
[$nbsp][$nbsp][$nbsp]下面的“--//測試腳本”是產生測試數據的腳本，你可以根據需要修改 @maxgroup， @maxLoop的值，比如測試1百萬的記錄可以:
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]Select @maxgroup=1000
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]Select @maxLoop=1000
假如要測試5千萬：
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]Select @maxgroup=5000
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]Select @maxLoop=10000
所以假如你的SERVER或PC比較慢，請耐心等待.....，
（在我的PC上運行的速度是插入1百萬條的時間是1.14m，插入5千八百萬條的時間是19.41m，重新建立INDEX的時間是34.36m)
作為一般的開發人員很輕易就想到的語句：
[$nbsp][$nbsp][$nbsp]--語句1
[$nbsp][$nbsp][$nbsp][$nbsp]select a.[id] from
[$nbsp][$nbsp][$nbsp][$nbsp](select distinct [id] from stress_test where [key] = 'Az') a,
[$nbsp][$nbsp][$nbsp][$nbsp](select distinct [id] from stress_test where [key] = 'Bw') b ,
[$nbsp][$nbsp][$nbsp][$nbsp](select distinct [id] from stress_test where [key] = 'Cv') c
[$nbsp][$nbsp][$nbsp][$nbsp]where a.id = b.id and a.id = c.id
[$nbsp][$nbsp][$nbsp]--語句2
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]select [id]
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]from stress_test
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]where [key]='Az' or [key]='Bw' or [key]='Cv'
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]group by id having(count(distinct [key])=3)
[$nbsp][$nbsp][$nbsp]--語句5
[$nbsp][$nbsp][$nbsp][$nbsp]SELECT distinct a.[id] FROM stress_test AS a,stress_test AS b,stress_test AS c
[$nbsp][$nbsp][$nbsp][$nbsp]WHERE a.[key]='Az' AND b.[key]='Bw' AND c.[key]='Cv'
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]AND a.[id]=b.[id] AND a.[id]=c.[id]
但作為T-SQL的所謂“高手”可能會認為這種寫法很“土”，也顯得沒有水平，所以會選擇一些子查詢和外連接的寫法，按常理子查詢的效率是比較高的：
[$nbsp][$nbsp][$nbsp]--語句3
[$nbsp][$nbsp][$nbsp][$nbsp]select distinct [id] from stress_test A where
[$nbsp][$nbsp][$nbsp][$nbsp]not exists (
[$nbsp][$nbsp][$nbsp][$nbsp]select 1 from
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp](select 'Az' as k union all select 'Bw' union all select 'Cv') B
[$nbsp][$nbsp][$nbsp][$nbsp]left join stress_test C on C.id=A.id and B.[k]=C.[key]
[$nbsp][$nbsp][$nbsp][$nbsp]where C.id is null)
[$nbsp][$nbsp][$nbsp]--語句4
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]select distinct a.id from stress_test a
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]where not exists
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]( select * from keytb c
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]where not exists
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]( select * from stress_test b
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]where
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]b.id = a.id
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]and
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]c.kf1 = b.[key]
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp])
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp])
我們先分析這幾條語句（針對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條）；
大家可以下面測試腳本的：
[$nbsp][$nbsp][$nbsp]Select @maxgroup=500
[$nbsp][$nbsp][$nbsp]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的優化一定的情況下，數據量不同，檢索的效率也是不同的。我們
