一句T-SQL語句引發的思考 轉帖

　　關於MS SQLSERVER索引優化問題:
　　有表Stress_test(id int key char())
　　[$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:P +M+G
　　[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]Software:windows server (Enterprise Edition)+Sqlserver +spa
　　
　　[$nbsp][$nbsp]首先我們建立一個測試的數據為使數據盡量的分布和隨即我們通過RAND()來隨機產生個隨機數再組合成一個字符串首先插入的數據是條記錄然後在循環插入到條記錄
　　[$nbsp][$nbsp][$nbsp]因為是隨機產生的數據所以如果你自己測試的數據集和我測試的會不一樣但對索引的優化和運行的效率是一樣的
　　[$nbsp][$nbsp][$nbsp]下面的//測試腳本是產生測試數據的腳本你可以根據需要修改 @maxgroup @maxLoop的值比如測試百萬的記錄可以:
　　
　　[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]Select @maxgroup=
　　[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]Select @maxLoop=
　　
　　如果要測試千萬
　　
　　[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]Select @maxgroup=
　　[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]Select @maxLoop=
　　
　　所以如果你的SERVER或PC比較慢請耐心等待
　　（在我的PC上運行的速度是插入百萬條的時間是m插入千八百萬條的時間是m重新建立INDEX的時間是m)
　　
　　
　　
　　作為一般的開發人員很容易就想到的語句
　　
　　[$nbsp][$nbsp][$nbsp]語句
　　
　　[$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 aid = bid and aid = cid
　　
　　[$nbsp][$nbsp][$nbsp]語句
　　
　　[$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])=)
　　
　　[$nbsp][$nbsp][$nbsp]語句
　　
　　[$nbsp][$nbsp][$nbsp][$nbsp]SELECT distinct a[id] FROM stress_test AS astress_test AS bstress_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]
　　
　　但作為TSQL的所謂高手可能會認為這種寫法很土也顯得沒有水平所以會選擇一些子查詢和外連接的寫法按常理子查詢的效率是比較高的
　　
　　[$nbsp][$nbsp][$nbsp]語句
　　
　　[$nbsp][$nbsp][$nbsp][$nbsp]select distinct [id] from stress_test A where
　　[$nbsp][$nbsp][$nbsp][$nbsp]not exists (
　　[$nbsp][$nbsp][$nbsp][$nbsp]select 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 Cid=Aid and B[k]=C[key]
　　[$nbsp][$nbsp][$nbsp][$nbsp]where Cid is null)
　　
　　[$nbsp][$nbsp][$nbsp]語句
　　
　　[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]select distinct aid 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]bid = aid
　　[$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]ckf = b[key]
　　[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp])
　　[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp])
　　
　　我們先分析這幾條語句（針對千百萬條數據進行分析）
　　
　　請大家要特別留心Estimated row count的值
　　
　　語句:從執行規劃中我們可以看出MSSQLSERVER選擇的索引優化非常有規律先通過CLUSTERED INDEX篩選出符合[KEY]=Az條件的ID然後進行HASH MATCH在找出ID相等的依次類推最終檢索到符合所有條件的記錄中間的Estimated row count的值都不大
　　
　　語句:從執行規劃中我們可以看出是先通過CLUSTERED INDEX篩選出符合 [key]=Az or [key]=Bw or [key]=Cv 符合所有條件的ID然後分組進行次HASH MATCH 所有的ID我們可以看出Estimated row count的值是越來越少從最初的到最後排序的只有
　　
　　語句:從執行規劃中我們可以看是非常復雜的是先通過組 通過CONSTANT SCAN和NONCLUSTERED INDEX檢索出符合 AID=CID AND [key]=** 的記錄組然後分組進行外鍵匹配再將組的數據合並排序然後再和一個NONCLUSTERED INDEX檢索出的記錄集進行外鍵匹配我們可以看出MSSQLSERVER會對所有的記錄（千萬條）記錄進行分組Estimated row count的值是所以這句TSQL的瓶頸是對千萬條記錄進行分組
　　
　　語句:從執行規劃中我們可以看和語句有相似之處都要對所有的記錄（千萬條）記錄進行分組所以這是檢索的瓶頸而且使用的索引都是NONCLUSTERED INDEX
　　
　　語句從執行規劃中我們可以看出先通過CLUSTERED INDEX檢索出符合[Key]=Az的記錄集然後進行HASH MATCH和SORTS因為數量少所以是非常會的在和通過NONCLUSTERED INDEX檢索[KEY]=Bw的記錄進行INNER JOIN在和通過CLUSTERED INDEX檢索[KEY]=Cv的記錄進行合並最後是對百萬條數據進行分組檢索如果是列我們可以看出Estimated row count的值是遞增越來越大最後的分組檢索的Estimated row count的值是E+這已經形成巨大的瓶頸
　　
　　我們可以先測試一下小的數據量（條）
　　
　　大家可以下面測試腳本的
　　
　　[$nbsp][$nbsp][$nbsp]Select @maxgroup=
　　[$nbsp][$nbsp][$nbsp]Select @maxLoop=
　　
　　
　　|語句 語句 語句 語句 語句 |
　　| 萬（列） ms ms ms ms ms
　　| 萬（列） ms ms ms ms ms
　　
　　
　　從測試的的數據來看語句的效率是最高的幾乎沒有花費時間而語句的效率只能說是一般如果測試到這裡就結束了我們可以毫不猶豫的選擇語句 :(繼續進行下面的測試
　　
　　我們測試百萬條以上的記錄
　　先對百萬條記錄進行測試（選取列）
　　先對百萬條記錄進行測試（選取列）
　　對千萬條數據測試（選取列）
　　對千萬條數據測試（選取列）
　　
　　統計表
　　
　　|語句 語句 語句 語句 語句 |
　　| 百萬（列） % % % % %
　　| 百萬（列） % % % % %
　　| 千萬（列） % % % % %
　　| 千萬（列） % % % % %
　　統計表
　　
　　|語句 語句 語句 語句 語句 |
　　| 百萬（列） ms ms ms ms ms
　　| 百萬（列） ms ms ms ms ms
　　| 千萬（列） ms ms ms ms ms
　　| 千萬（列） ms ms ms ms m以上
　　
　　測試總結（我們可以比較關注語句 和語句 ）
　　在百萬條記錄的情況下語句 是最快的但在千萬條記錄下是最慢的這說明INDEX的優化一定的情況下數據量不同檢索的效率也是不同的我們
From:http://tw.wingwit.com/Article/program/Oracle/201311/18861.html