1       數據表結構的設計與性能優化

1.1     、數據表的存儲原理

SQL Server每次讀取1個存儲塊，每個存儲塊大小為8KB，每讀取1個存儲塊計算為1個邏輯讀。

問題：如果數據內容非常大，像我們系統中的Feeling欄位非常大，就會導致每個存儲塊存放的數據行數會非常少，這樣當我們讀取數據時，要讀取許多的存儲塊。

存儲塊1（8KB）

存儲塊2（8KB）

1.2     表設計的優化

1.2.1、         欄位類型優先順序

Bit>int，int>date,time>char,varchar>text，原因：整型，time運算快，節省空間。

所以我們在表設計時，如果是bool類型的數據值就不應該用int類型欄位。

1.2.2、         聚集索引欄位類型的選擇

主鍵的索引結構中，即存儲了主鍵值，又存儲了行數據，這種結構稱為“聚集索引”

在插入數據時，數據節點會分裂，這個問題比較嚴重，節點下存儲了“行數據”，分裂的時候，還要移動行數據。如果主鍵是無規律的，則會加速它的數據節點分裂，且效率極低。

高性能索引策略：

主鍵，儘量用整型，而且是遞增的整型。如果是無規律的數據，將會產生頁的分裂，影響速度。索引長度直接影響索引文件大小，影響增刪改的速度，並間接影響查詢速度（占用記憶體多）。因為主鍵索引在物理存放時是有序的，如果主鍵的值是無序的，那麼主鍵每次插入時，索引文件都重新進行排序，會產生額外的數據消耗，另外主鍵的葉子上存放的數據，還會導致葉子數據行的移動和分裂，又會產生一些消耗，所以主鍵儘量用整型，且自增的類型。

1.2.3、         縱向拆分

縱向拆分方法：把不需要用於查詢的大欄位，放到另外一個新建的附屬表中，如feelingStructured表和Auto表。這樣就將Evaluation表的數據內容減少到最少，存儲塊中可以多存儲許多數據行，減少程式讀取存儲塊的個數。

1.2.4、         橫向拆分

橫向拆分方法：表分區，表分區的條件，一張數據表的行數至少要達3000W行以上的數據，就可以考慮做表分區了。但這不是絕對，如果表的數據行內容特別多，查詢特別慢時，也可以儘早做表分區。

註意問題：普通表在查詢時，會比分區表要快一些，因為基於分區表的查詢會遍歷所有的分區表，而普通表只查詢了普通表一個表。

解決辦法，在查詢條件中加入分區條件，這樣查詢就會落入指定的分區中，不用遍歷所有的分區，但問題是，是不是所有的查詢都能加入分區條件呢。只要進行了表分區，那麼SQL的前提條件就是所有SQL都要加上分區條件，除非個別的彙總，統計類的SQL。

1.2.5、         資料庫分庫

資料庫分庫：當一個台資料庫表伺服器訪問壓力過大，數據量過大時，就需要考慮進行資料庫分庫，資料庫分庫條件和表分區的邏輯是比較像的。根據業務條件，如地區，時間，進行拆分。

1.2.6、         讀索引為什麼比讀表快

這裡面引發出一個觸類旁通的問題，為什麼索引查詢會比直接查數據要快？因為索引做為一個獨立的數據存儲區，也是跟數據表存儲塊一樣，以8KB為一個存儲塊，一個IO讀取一次存儲塊，而索引中只有簡單的幾個索引列，而不是整個數據行的數據，所以它一個IO讀取的數據會非常多，這樣它的IO就會非常少，加快了查詢速度。

1.2.7、         數據壓縮的利與弊

數據壓縮和索引壓縮會使存儲空間和邏輯讀減少，但是會使表更新的開銷加大，查詢耗費的CPU也更多，所以壓縮表一般適合在更新比較少，且CPU消耗不大，IO消耗很大系統中適用。像企業管理軟體就比較適用於數據壓縮和BI系統。如果當前系統的IO並不高，但CPU非常繁忙，則不應該採用表和索引壓縮，傳統資料庫的壓縮率並不是太高，真正壓縮率比較高的應該是BI的數據。

2       索引優化

2.1     索引列的設計與優化

索引列數據的重覆度稱為可選擇性，如性別列的取值範圍為”男，女”，這個索引列可選擇性就比較低，你在裡面能找出太多相同的數據列出來，如選擇列數據內容為唯一的，則可選擇性非常高。我們選擇索引列時，要儘量選擇高選擇性的列。

案例分析：現在有一個商家黑盒處罰功能，假如商家發佈違規的商品和促銷信息，則會被系統管理員設置為5天或10天的墨盒狀態，墨盒狀態期間無法發佈商品和促銷信息，現在設計有商家黑盒表，有3欄位，BusinessID（商家ID）， 生效開始時間（StartTime）、生效結束時間（EndTime），這生效開始時間和生效結束時間2個列誰的選擇性高呢？

示例業務數據：

現在找出今天還屬於黑盒的商家名單列表

Where startTime<=’2016-1-14’ and EndTime>=’2016-1-14’

大家可以看到，符合startTime<=’2016-1-14’的有3條記錄，而符合EndTime>=’2016-1-14’的只有1條記錄，所以EndTime的可選擇性比StartTime高，這就會決定，我們到底是採用哪一列做索引列，如果2個列都做索引列，哪個索引列會排在前面（多列索引的設計見下麵）。

2.2     表掃描查詢如何修改為索引列掃描

如：我們項目中以前用來判斷用戶是否填寫Feeling時，用where len(feeling)>0，len(best)>0，

Len(wrost)>0，

在where條件中用函數會導致表掃描，所以應該設計成標識欄位，如IsEditFeeling(bit)，IsWroteBest(bit)，IsWroteWrost(bit)，SQL就可以優化成where IsEditFeeling=1，IsWrostBest=1，IsWroteWrost=1。這樣就是索引掃描。

2.3     單列索引的設計與優化

單列索引設計比較簡單，一般就是根據業務條件來定義就行，如根據車系ID或車型ID。但要註意索引列的可選擇性。

2.4     多列索引的設計與優化

1. 2.          

2.2.          

2.3.          

2.4.          

2.4.1、         多列索引的存儲規則

假設某個表有一個聯合索引(a,b,c)，我們來看下在這個索引中是如何存儲這些欄位數據的。

2.4.2、         多列索引左首碼規則

多列索引必須用到第1個，否則不生效。

2.4.3、         多列索引列的選擇優化

根據以上的多列索引列的存儲規則和左首碼規則，在建多列索引時，應該將可選擇項高的列放在最左邊，後面依次類推，如上面的黑盒案例分析中，應該將endTime列放在最左邊，然後才是starttime列。

在選擇性相同的情況下，應該把等值（如：=）的放在左邊，不等值（如：>，<）放在後面。

Select * from t where object_id>=20 andobject_id<2000 and object_type=’TABLE’;

這個查詢對應的索引，應該建成

Create index idx_id_type on t(object_type,object_id);

2.5     書簽查找優化

2.5.          

2.5.1、         數據表物理存儲和索引的物理索引

數據存儲塊1（8KB）

數據存儲塊2（8KB）

索引存儲塊1（8KB）

因為索引裡面只存儲了固定了幾列，如果查詢時需要讀取索引列之外的數據，就需要到數據存儲塊，根據聚集索引去重新查找我們想要的數據，這就叫書簽查找。

2.5.2、         書簽查找的缺點

找出書簽查找中查找的列

2.5.3、         書簽查找優化

2.6     索引與排序的優化

索引在物理存儲上是有序的，所以如果我們的SQL的排序是基於索引列進行的，那麼不需要再重新進行排序，反之，系統會在temp庫中建立表變數或臨時表，然後在這個表變數或臨時表中重新進行排序。如果想為多表聯查時，基於多個表做排序欄位建立索引，如Order by A.Id,B.Created，則可以基於2個表建立一個物化視圖，然後在這個物化視圖上為這2列建立索引。

Create index idx_t on t(col1 desc,col2 asc)

Select * from t order by col1 desc,col2 asc

0：sorts(memory)

0：sorts(disk)

SQL Server 執行時間:

   CPU 時間= 2182 毫秒，占用時間= 231 毫秒。

SQL Server 分析和編譯時間:

   CPU 時間= 0 毫秒，占用時間= 0 毫秒。

SQL Server 執行時間:

   CPU 時間= 0 毫秒，占用時間= 2 毫秒。

SQL Server 分析和編譯時間:

   CPU 時間= 0 毫秒，占用時間= 0 毫秒。

2.7     關於索引的壞處

在有幾個索引和情況下和只有主鍵索引的情況下，插入數據的速度相差10倍，而且數據表裡數據記錄越大，插入速度越明顯。

在無索引的情況下，表的記錄越大，插入的速度只會受到很小的影響，基本不會越慢。

在無索引的情況下，分區表的插入要比普通表更慢，因為插入的數據需要做判斷，有這方面的開銷。

解決辦法：讀寫分離，在主庫（或叫寫庫）上只有主鍵索引，而沒有別的索引。只讀庫上有許多用於查詢的索引，然後寫庫的數據定時同步到只讀庫上，這樣的話，插入時不會因為索引的原因導致插入變慢，也不會因為沒有索引導致查詢變慢。

2.8     索引建立時的開銷及註意事項

建索引過程會產生全表鎖和建索引過程中會產生全表排序.

後果：普通的對錶建索引將會導致針對該表的更新操作無法進行，需要等待索引建完，更新操作將會被建索引的動作阻塞。

解決辦法：

CREATE NONCLUSTERED INDEX [IX_Auto_Serial] ON [dbo].[Auto]

(

    [Serial] ASC,

    [RowStatus] ASC

)

 WITH (  ONLINE = ON) 

在創建索引時，加上online參數，這種建索引的方式不會阻止針對該表的更新操作，與建普通索引相反的是，online建索引的動作是反過來被更新操作阻塞。

3       多表聯查優化

3.1     、欄位冗餘，不要聯查

業務案例分析：在口碑表裡冗餘新建一個LastAppendingDrivenKilometers欄位，這樣就不用關聯追加表進行查詢了。再冗餘新建一個AutoBoughtCity欄位，就不用關聯Auto表進行查詢了。

3.2     、多表聯查的實現原理及表關聯欄位的設計原則

三大表連接的概要說明：1、Nested Loops Join。2、Hash Join。3、Merge Sort Join

Nested Loops Join驅動結果集的條數決定被驅動表的訪問次數

Hash Join兩表各自只會訪問1次或0次。

Merge Sort Join與Hash Join的相同

表驅動順序與性能（Nested LoopsJoin性能與驅動順序有關）

Hash Join性能與驅動順序有關（和NL相似）

Merge Sort Join性能與表驅動順序無關

Nested Loops Join優化要點

1：驅動表的限制條件要有索引。2、被驅動表限制條件要建立索引。3：確保小結果集先驅動，大的被驅動。

Hash Join憂化要點：1：請確保用在全掃描的OLAP場景。2：明確該SQL是否限制Hash Join。3、兩表無任何索引傾向HashJoin。第1斧：兩表限制條件有索引（看返回量）。第2斧：要小結果集先驅動，大的被驅動。第3斧：儘量保證PGA能容納Hash運算。

Merge Sort Join優化第1式（兩表限制條件有索引）

Merge Sort Join 優化第2式（連接條件索引消除排序，即消除2邊分別排序，再合併的這種情況）

因為被驅動表的查詢是依賴與ON 條件中寫的欄位列，如： A join B on A.CID=B.C ID，

那麼應該B表的CID設置為索引列，最好設置為聚集索引列，這樣關聯SQL在運行時，從B表中查找數據時，可以命中索引，否則在B表中查找數據時，會引發表掃描和Hash匹配。

代碼示例：

優化前的效果

(1 row(s) affected)

表 'Worktable'。掃描計數 0，邏輯讀取 0 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

表 'EvaluationPurposeTerms'。掃描計數 1，邏輯讀取 1383 次，物理讀取 3 次，預讀 2269 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

表 'Evaluation'。掃描計數 1，邏輯讀取 110 次，物理讀取 2 次，預讀 80 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

(1 row(s) affected)

 SQL Server 執行時間:

   CPU 時間 = 140 毫秒，占用時間 = 155 毫秒。

優化後的SQL:

(1 row(s) affected)

表 'EvaluationPurposeTerms'。掃描計數 0，邏輯讀取 1638 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

表 'Evaluation'。掃描計數 1，邏輯讀取 20 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

 SQL Server 執行時間:

   CPU 時間 = 0 毫秒，占用時間 = 4 毫秒。

3.3     、關聯查詢的優化（使用表掃描）

QualityProblem是一個數據表，QualityProblemDictionary是一個字典表，字典表的數據基本上是固定不變的，大概80行的樣子。而在與QualityProblem表關聯時，QualityProblem表中符合條件數據的，每一行都會讀取一次QualityProblemDictionary字典表。SQL和運行效果如下，現在打算將QualityProblemDictionary字典表一次全讀取到記憶體中，這樣就不用每條符合條件的QualityProblem表中數據都去訪問QualityProblemDictionary字典表了，修改方法就是在表的Hint中指出不要用索引掃描，要採用表掃描。代碼：with(index(0))

SELECT [QualityId] ,[ItemId] AS [ID] ,d.Name ,d.FullName ,d.FullPath ,d.Level ,d.ParentId FROM [QualityProblem] as p with(nolock)

join [QualityProblemDictionary] as d with(nolock) on d.Id  = p.ItemId where p.QualityId =  592805 

優化前效果：

(7 row(s) affected)

表'QualityProblemDictionary'。掃描計數7，邏輯讀取14 次，物理讀取0 次，預讀0 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預讀0 次。

表'QualityProblem'。掃描計數1，邏輯讀取3 次，物理讀取0 次，預讀0 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預讀0 次。

 SQL Server 執行時間:

   CPU 時間= 15 毫秒，占用時間= 23 毫秒。

as d with(index(0),nolock) on d.Id  = p.ItemId

優化後效果：

(7 row(s) affected)

表'Worktable'。掃描計數0，邏輯讀取0 次，物理讀取0 次，預讀0 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預讀0 次。

表'QualityProblemDictionary'。掃描計數1，邏輯讀取19 次，物理讀取0 次，預讀0 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預讀0 次。

表'QualityProblem'。掃描計數1，邏輯讀取3 次，物理讀取0 次，預讀0 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預讀0 次。

 SQL Server 執行時間:

   CPU 時間= 0 毫秒，占用時間= 2 毫秒。

4       查詢計劃的緩存與CPU高優化

1      

2      

3      

4      

4.1     、概述

我們SQL執行分為2部分，1是SQL查詢計劃編譯部分，2是SQL運行部分。SQL分析器里顯示的CPU時間也是包括編譯時間和運行時間。

SQL Server 分析和編譯時間:

   CPU 時間= 0 毫秒，占用時間= 0 毫秒。

SQL Server 執行時間:

   CPU 時間= 2668 毫秒，占用時間= 786 毫秒。

因為生成SQL的查詢計劃是非常耗時和耗CPU的，SQL Server在生成查詢計劃之後，會將已經生成的查詢計劃保存到計劃緩存中，下次再執行該SQL時不用再重新生成一個全新的計劃。

4.2     、如何使查詢計劃被緩存

1. 3.          
2. 4.          

4.2.          

4.2.1、         sp_executesql存儲過程

普通的即席SQL的查詢計劃是沒有被緩存的，通過下麵的示例可以看出來。

通過把SQL改寫為用sp_executesql存儲過程，即可實現查詢計劃被緩存。ado.net底層就是把我們寫的SQL全部生成sp_executesql sql這種格式的sql，但ado.net無法自動區分我們sql中哪些是參數，哪些不是參數，所以光靠ado.net幫我們自動實現sp_executesql還無法實現查詢計劃的緩存，還需要我們手動把查詢條件參數化寫完整。最終實現一個標準的語法

Sp_executesql @sqltext,@param1,,,,,,@paramN

1. 1.          
2. 2.          
3. 3.          
4. 4.          

4.2.          

4.2.1、          

4.2.2、         存儲過程和函數

存儲過程和函數的查詢計劃也會被緩存。

4.3     、如何解決查詢計劃不被緩存

只要採用sp_executesql，存儲過程，函數這3種方式寫的sql查詢計劃就會被緩存，但也有原因會導致他們不會被緩存，原因如下：

1. 1.          
2. 2.          
3. 3.          
4. 4.          

4.2.          

4.3.          

4.3.1、         查詢計劃不被緩存案例示警

上次線上資料庫CPU總是高，從DMV中查到編譯數過高，原因是開發人員為線上跟蹤代碼運行結果和查錯方便，在公共數據訪問層中，自動為每一條SQL中都加了一條註釋，該註釋內容為當前系統的Url，因為一個公共方法會被不同模塊調用，當前模塊的Url自然也是不停的在變，導致SQL的查詢計劃無法重用。

4.4     、緩存的查詢計劃什麼時候被重新生成

1、當表數據增長率達到當前數據量的30%時，查詢計劃會被重編譯。

2、當新建和修改索引時。

3、當新建和刪除欄位時。

4.5     、In條件參數化   

Declare @User1 int;Declare @User2 int;Declare @User3 int;Declare @User4 int;declare @User5 int;

Where userId in(@User1,@User2,@User3,@User4,@User5)

5       併發和阻塞問題

1、減少事務長度。

2、SQL語句加Nolock。

3、讀寫分離

6       日誌庫優化

當有insert和update,delete操作時，會在日誌庫中記錄日誌，隨著時間的積累，日誌庫記錄越來越多，每插入一條日誌都非常耗時，直接影響我們系統的數據操作。

解決辦法：設定每天晚上自動備份，在資料庫備份之後，資料庫會自動收縮日誌庫，刪除一些日誌數據，這樣日誌庫的數據量就下來了。因為資料庫覺得你已經備份過了，萬一齣什麼問題，可以用備份文件來恢復，也不需要依靠日誌庫來進行還原了，沒必要再保存那麼多日誌了。

7       SQLServer伺服器系統參數配置與性能優化

7.1     、CPU最大並行度

詳細描述文檔：http://jimshu.blog.51cto.com/3171847/1266978

7.2     、並行性開銷閥值

7.3     、優化即席工作負載

7.4     、資料庫文件佈局

將資料庫文件分別存放在不同的伺服器上，以加快並行處理速度。

8       DB伺服器監控

參考附件

數據性能調校--查出最耗資源的各種SQL.docx

9       SQL語句性能優化

9.1     、SQL語句分析方法

SQL分析技巧，SQL可以分為3段：

1、  Select部分，重點關註Select部分有沒有標題子查詢，有沒有自定義函數

2、  From後面，重點關註有沒有內聯視圖，有沒有視圖，有沒有進行視圖合併

3、  Where條件部分，看有沒有In/Not In，Exists/Not in子查詢，有沒有外連接，有沒有

在列上面有函數導致不能走索引，比如：where len(feeling)>0

內聯視圖要手工運行返回多少行，子查詢也要查看返回多少行。

9.2     、子查詢優化

子查詢的特點：主查詢返回一行，子查詢就會被執行一次。In語句外面是驅動表，in語句裡面是被驅動表。

優化目的：

1、  子查詢不用執行很多次

2、  優化器可以根據統計信息來選擇不同的連接方法和不同的連接順序

子查詢中的連接條件，過濾條件分別變成了父查詢的連接條件，過濾條件，優化器可以對這些條件進行下推，以提高執行效率。

1. 5.          
2. 6.          
3. 7.          
4. 8.          
5. 9.          

9.2.          

9.2.1、         非關聯子查詢

非關聯子查詢的執行，不依賴於外層父查詢的任何屬性值。這樣子查詢具有獨立性，可獨自求解，形成一個子查詢計劃先於外層的查詢求解，如：

相關和非相關子詢的判斷條件就是看子查詢是否出現外層父查詢中的數據列。

9.2.2、         關聯子查詢

相關子查詢，子查詢的執行依賴於外層父查詢的一些屬性值。子查詢因依賴於父查詢的參數，當父查詢的參數改變時，子查詢需要根據新參數值重新執行（查詢優化器對相關子查詢進行優化有一定意義），如：

9.2.3、         子查詢優化方法一（子查詢合併）

子查詢合併（subquery coalescing）

在某些條件下（語義等價：兩個查詢塊產生同樣的結果集），多個子查詢能夠合併成一個子查詢（合併後還是子查詢，以後可以通過其他技術消除掉子查詢）。這樣可以把多次表掃描，多次連接減少為單次表掃描和單次連接，如：

可優化為：

9.2.4、         非關聯子查詢優化--子查詢展開

子查詢展開（subquery unnesting）。

又稱子查詢反嵌套，又稱為子查詢上拉。

把一些子查詢置於外層的父查詢中，作為連接關係與外層父查詢併列，其實質是把某些子查詢重寫為等價的多表連接操作（展開後，子查詢不存在了，外部查詢變成了多表連接）。

帶來的好處是，有關的訪問路徑，連接方法和連接順序可能被有效使用，使得查詢語句的層次儘可能的減少。

WHERE  evl.[AutoSeries] =@id and evl.EvaluationID in (select EvaluationID from EvaluationPurposeTerms ept with(nolock) where ept.PurposeID=1)

修改為

INNER JOIN EvaluationPurposeTerms ept ON evl.EvaluationID = ept.EvaluationID

WHERE  evl.[AutoSeries] =@id and ept.PurposeID=1

註意：其實子查詢和聯合查詢中的嵌套迴圈查詢的底層思路是一樣的，都是驅動表查詢被驅動表，嵌套迴圈的偽代碼如下：

那我們為什麼還要修改子查詢為關聯查詢呢？因為帶來的好處是，有關的訪問路徑，連接方法和連接順序可能被有效使用，使得查詢語句的層次儘可能的減少。在子查詢中，In語句外固定是驅動表，In語句內固定是被驅動表，如果驅動表返回1W條記錄，那被驅動表則會被查詢1W次。而關聯查詢時，驅動表和被驅動表的關係是根據誰返回數據行的大小來動態決定的，如A Join B，B返回10行，A返回1W行，則SQL底層執行時會將B作為驅動表，而A作為被驅動表，這樣B執行一次查詢，A執行10次查詢，反過來如果A in(B)，則A執行一次查詢，B執行1W次查詢。

代碼示例2：

and evl.[AutoModel] in (601,1473,1474,1993,1994,1995,2743,2750,2751,2752,3681,3682,3683,3684,5405,5819,5910,5911,10665,10666,10667,13444,13481,13482,13483,13484,13485,15377,15379,17807,17808,17809,17810,17811,17812)

修改方法：SQL中關聯Product_Spec表，

INNER JOIN dbo.Product_Spec prod_Spec ON prod_Spec.SpecId=evl.AutoModel

WHERE prod_Spec.SyearId=452 AND SpecState=(銷售狀態)

9.2.5、         關聯子查詢優化--子查詢展開

非關聯子查詢的每次查詢都依賴了驅動表的行數據內容，所以想修改為子查詢展開方式不太好做，但我們可以從業務上分析，將相關的數據修改為不相關的數據集合，然後再跟不相關的數據集合進行關聯，但跟不相關數據集合關聯時，要帶上條件。

代碼示例：

and ((evl.[DrivenKiloms] >5000 and evl.[DrivenKiloms] <=20000) or evl.EvaluationID in (select EvaluationID from EvaluationAppending with(nolock) where DrivenKilometers>5000 and DrivenKilometers<20000 and RowStatus=0 and EvaluationAppendingID in (select MAX(EvaluationAppendingID) as EvaluationAppendingID from EvaluationAppending where RowStatus=0 group by EvaluationID)))) as

上面的邏輯含義是口碑的驅動公裡數>5000 And <=20000，或者最後一條追加口碑的驅動公裡數>5000 And <=20000，我們可以一次性把所有口碑的最後一條追加口碑並且是符合驅動公裡數>5000 And <20000的條件，保存到臨時表或衍生數據表和CTE中，然後用這個臨時表或CTE與口碑表進行關聯查詢，修改後的代碼如下：

;WITH AppendEvalIDCTE AS( 

    SELECT  EvaluationID

        FROM    ( SELECT    ROW_NUMBER() OVER ( PARTITION BY EvaluationID ORDER BY EvaluationAppendingID DESC ) AS RowNum ,

                            EvaluationID ,DrivenKilometers

                  FROM      EvaluationAppending with(nolock)

                  WHERE     RowStatus = 0

                ) T

        WHERE   T.RowNum = 1 AND DrivenKilometers > 5000 AND DrivenKilometers <= 20000

)

關聯代碼：

INNER JOIN AppendEvalIDCTE evlAppend ON evl.EvaluationID = evlAppend.EvaluationID

Where  evl.[DrivenKiloms] > 5000 AND evl.[DrivenKiloms] <= 20000

9.2.6、         關聯子查詢優化--謂詞推入

         謂詞推入：當SQL語句中包含有不能合併的視圖，並且視圖有謂詞過濾（也就是where過濾條件），CBO會將where過濾條件推入視圖中，這個就叫做謂詞推入。謂詞推入主要目的就是讓SQL Server儘可能早的過濾掉無用的數據，從而提升查詢性能。

當SQL語句中，OR條件上面有一個為子查詢，並且子查詢上的表與源表不同，這個時候就可以用union代替OR或者你發現執行計劃中的filter有or並且or後面跟上子查詢(Exists)的時候就要註意，比如：

修改為：

代碼示例：

;WITH AppendEvalIDCTE AS( 

   SELECT  EvaluationID

        FROM    ( SELECT    ROW_NUMBER() OVER ( PARTITION BY EvaluationID ORDER BY EvaluationAppendingID DESC ) AS RowNum ,

                            EvaluationID ,DrivenKilometers

                  FROM      EvaluationAppending with(nolock)

                  WHERE     RowStatus = 0

                ) T

        WHERE   T.RowNum = 1 AND DrivenKilometers > 5000 AND DrivenKilometers <= 20000

)

, AllEvalIDCTE AS

( 

   SELECT HelpfulCount,LastAppend,Grade,AppendCount,EvaluationID FROM

   (

       SELECT evl.HelpfulCount,evl.LastAppend, evl.Grade ,evl.AppendCount,evl.EvaluationID FROM Evaluation evl

       INNER JOIN [Auto] at WITH ( NOLOCK ) ON at.AutoID = evl.AutoID

        WHERE  evl.[AutoSeries] =@id

              AND  evl.[DrivenKiloms] > 5000 AND evl.[DrivenKiloms] <= 20000

       UNION

        SELECT evl.HelpfulCount,evl.LastAppend, evl.Grade ,evl.AppendCount,evl.EvaluationID FROM Evaluation evl

        INNER JOIN [Auto] at WITH ( NOLOCK ) ON at.AutoID = evl.AutoID

       INNER JOIN AppendEvalIDCTE evlAppend ON evl.EvaluationID = evlAppend.EvaluationID

        WHERE  evl.[AutoSeries] =@id  

              --不能再有了AND  evl.[DrivenKiloms] > 5000 AND evl.[DrivenKiloms] <= 20000

   ) T

)

9.2.7、         子查詢優化—-Update語句

update table_1 set score = score + 5 where uid in (select uid from table_2 where sid = 10);

其實update也可以用到left join、inner join來進行關聯，可能執行效率更高，把上面的sql替換成join的方式如下：

update table_1 t1 inner join table_2 t2 on t1.uid = t2.uid set score = score + 5 where t2.sid = 10;

9.2.8、         子查詢優化—-欄位冗餘

在Evaluation表新建一個LastAppendingDrivenKilometers欄位

用於冗餘存放EvaluationAppending表中當前口碑的最後一條追加數據的DrivenKilometers欄位數據，代碼就簡潔多了。

9.2.9、         子查詢優化—-物化視圖（索引視圖）

物化視圖概念：對於涉及對大量的行進行複雜處理的非索引視圖，為引用視圖的每個查詢動態生成結果集的開銷會很大。這類視圖包括聚集大量數據或聯接許多行的視圖。若經常在查詢中引用這類視圖，可通過在視圖上創建唯一聚集索引來提高性能。在視圖上創建唯一聚集索引時將執行該視圖，並且結果集將存儲在資料庫中，就像帶有聚集索引的表一樣。

對於涉及對大量的行進行複雜處理的視圖，由於結果集已經保存為一張帶有聚集索引的表，因此無需重新計算，索引視圖有明顯的速度優勢。

案例分析：

  SELECT ps.BrandId ,SUM(tfw.SCount) SCount FROM   [Replication].dbo.dxp_SaleData_Cleaned_Count tfw WITH(NOLOCK)INNER JOIN dbo.Product_Series ps WITH ( NOLOCK ) ON ps.SeriesId = tfw.SeriesId

                     WHERE  ( tfw.Syear = @spFromYear AND tfw.Smonth >= @spFromMonth )

                            OR ( tfw.Syear = @spToYear AND tfw.Smonth < @spToMonth)

                            OR ( tfw.Syear > @spFromYear AND tfw.Syear < @spToYear)

                     GROUP BY ps.BrandId) s ON s.BrandId = sbf.BrandId

WHERE   1 = 1 AND sbf.CarType = 1 AND sbf.CountryId IN (1,2,3,4,5,6)

(8 行受影響)

表'dxp_SaleData_Cleaned_Count'。掃描計數1，邏輯讀取378 次，物理讀取0 次，預讀0 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預讀0 次。

表'Product_Series'。掃描計數1，邏輯讀取10 次，物理讀取0 次，預讀0 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預讀0 次。

 SQL Server 執行時間:

   CPU 時間= 15 毫秒，占用時間= 15 毫秒。

CREATE VIEW v_dxp_SaleData_Cleaned_Count WITH SCHEMABINDING AS

SELECT id,SeriesId,SCount,(Syear*100+Smonth))AS YearMonth

FROM dbo.dxp_SaleData_Cleaned_Count

CREATE  UNIQUE CLUSTERED INDEX v_dxp_SaleData_Cleaned_Count_Idx_ID ON v_dxp_SaleData_Cleaned_Count(id ASC);

CREATE  NONCLUSTERED INDEX v_dxp_SaleData_Cleaned_Count_Idx_YearMonth ON v_dxp_SaleData_Cleaned_Count(YearMonth ASC)INCLUDE(SeriesId,SCount);

;WITH CTE AS

(

SELECT ps.BrandId , SUM(tfw.SCount) SCount FROM   [Replication].dbo.v_dxp_SaleData_Cleaned_Count tfw WITH(NOLOCK)

INNER JOIN koubei.dbo.Product_Series ps WITH ( NOLOCK ) ON ps.SeriesId = tfw.SeriesId

WHERE tfw.YearMonth>=201407 AND tfw.yearmonth<201511 GROUP BY ps.BrandId

)

SELECT  sbf.BrandId Id,sbf.BrandName Name,sbf.PPH ,sbf.DefectiveSampleCount ,

        sbf.TroublefreeSamplesCount ,CTE.SCount

FROM   koubei.dbo.Stat_Brand_Fault sbf WITH ( NOLOCK ) INNER JOIN CTE  ON CTE.BrandId = sbf.BrandId

WHERE   1 = 1 AND sbf.CarType = 1 AND sbf.CountryId >=1 AND sbf.CountryId<=6 -- (1,2,3,4,5,6)

表'v_dxp_SaleData_Cleaned_Count'。掃描計數1，邏輯讀取34 次，物理讀取0 次，預讀0 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預讀0 次。

表'Product_Series'。掃描計數1，邏輯讀取10 次，物理讀取0 次，預讀0 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預讀0 次。

 SQL Server 執行時間:

   CPU 時間= 0 毫秒，占用時間= 5 毫秒。

9.3     、海量數據分頁優化

9.3.          

9.3.1、         延遲索引

海量數據分頁優化的2個重要思想就是：延遲索引和延遲關聯。

舉例：

select id,name from lx_com join userInfo between 5000000 and 5000010;

這個SQL中，它的工作原理是，他先取500W條，並且因為name欄位不是主鍵，需要不斷的回行到磁碟上去取，然後最後返回的是500W到5000010行數據，那麼前500W行數據的name，又要扔掉，不返給客戶端，這樣就形成極大的資源浪費。

修改為

With T as (select id from lx_com between 5000000 and 5000010)

select id,name from UserInfo join T on UserInfo.id=T.id

具體項目中的應用：

;WITH AllCTE AS( SELECT evl.EvaluationID,ROW_NUMBER() OVER(ORDER BY evl.Grade desc,evl.AppendCount desc,evl.LastAppend desc)

AS RowNum FROM Evaluation evl WITH(NOLOCK)

WHERE  evl.[AutoSeries]=@id),evlCTE AS

(SELECT TOP 100 PERCENT EvaluationID,RowNum FROM AllCTE WHERE RowNum BETWEEN @pagstart AND @pagend ORDER BY RowNum ASC)

9.3.2、         延遲關聯

 SELECT evlCTE.RowNum,evl.AppendCount,evl.EvaluationID,evl.AutoID,at.Brand AS AutoBrand,evl.AutoModel,evl.AutoSeries

                     ,evl.AutoOwner,at.[Level] AS AutoLevel,evl.CommentCount,evl.HelpfulCount

                ,evl.Created,evl.FeelingSummary ,at.BoughtDate AS AutoBoughtDate,at.BoughtProvince AS AutoBoughtProvince

,at.BoughtCity AS AutoBoughtCity, up.MemberId,up.NickName,up.IsAuthenticated,up.Gender,up.HeadImage,up.UserGrade,ps.SpecName

,ps.MinPrice,pss.SeriesName,pss.IsElectric

FROM evlCTE INNER JOIN Evaluation evl WITH(NOLOCK) ON evlCTE.EvaluationID = evl.EvaluationID

INNER JOIN [Auto] at with(nolock) ON at.AutoID = evl.AutoID  INNER JOIN UserProxy up with(nolock) ON up.UserID = evl.AutoOwner

INNER JOIN Product_Spec ps with(nolock) ON ps.SpecId=evl.AutoModel

INNER JOIN Product_Series pss with(nolock) ON pss.SeriesId=evl.AutoSeries ORDER BY evlCTE.RowNum ASC;

9.3.3、         求總行數的性能優化

1、  連接消除

在求總行數是，凡是僅僅是因為顯示數據列需要關聯的表，就可以不用再關聯了。

2、  排序消除

排序是需要很大消耗的，在求總行數時，不需要再用到排序。

3、  與分頁數據查詢放在同一個查詢中執行，這樣可以減少網路連接與傳輸

第一筆數據就是分頁數據，比如：第10條到第20條數據，另外一筆數據是當前查詢的總記錄數，那麼我們可以用一個”;”分號進行SQL來實現一次查詢取出2筆數據，然後在C#的DataSet對象中分別從Table[0]和Table[1]中取出這2筆不同的數據。

簡化後的代碼如下：

SELECT COUNT(*)AS TotalCount

 FROM Evaluation evl WITH(NOLOCK) WHERE  evl.RowStatus=0 AND evl.[AutoSeries]=@id and evl.[EditedFeeling]=1 and evl.[Grade]>-10

9.4     、With As與性能優化

對於SELECT查詢語句來說,通常情況下，為了使T-SQL代碼更加簡潔和可讀，在一個查詢中引用另外的結果集都是通過視圖而不是子查詢來進行分解的.但是，視圖是作為系統對象存在資料庫中，那對於結果集僅僅需要在存儲過程或是用戶自定義函數中使用一次的時候，使用視圖就顯得有些奢侈了.

    公用表表達式（Common Table Expression)是SQL SERVER 2005版本之後引入的一個特性.CTE可以看作是一個臨時的結果集，可以在接下來的一個SELECT,INSERT,UPDATE,DELETE,MERGE語句中被多次引用。使用公用表達式可以讓語句更加清晰簡練.

     除此之外，根據微軟對CTE好處的描述，可以歸結為四點:

• 可以定義遞歸公用表表達式(CTE)
• 當不需要將結果集作為視圖被多個地方引用時，CTE可以使其更加簡潔
• GROUP BY語句可以直接作用於子查詢所得的標量列
• 可以在一個語句中多次引用公用表表達式(CTE)
• 註意：如果with as短語被調用了2次以上，CBO會自動將with as 短語的數據放入一個臨時表。

在MSDN中的原型：

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

代碼示例：