初探Sql Server 執行計劃及Sql查詢優化

SQL Server的執行計劃及SQL查詢優化是本文我們主要要介紹的内容，談到優化就必然要涉及索引，就像要講鎖必然要說事務一樣，是以你需要了解一下索引，今天來探索下SQL Server的執行計劃，來讓大家知道如何檢視SQL Server的優化機制，以此來優化SQL查詢。

 
 --DROP TABLE T_UserInfo----------------------------------------------------         
      
 --建測試表         
      
 CREATE TABLE T_UserInfo         
      
 (         
      
 Userid varchar(20), UserName varchar(20),         
      
 RegTime datetime, Tel varchar(20),         
      
 )        
     
    

--插入測試資料

 
 DECLARE @I INT         
      
 DECLARE @ENDID INT         
      
 SELECT @I = 1        
      
 SELECT @ENDID = 100 --在此處更改要插入的資料，重新插入之前要删掉所有資料         
      
 WHILE @I <= @ENDID         
      
 BEGIN         
      
 INSERT INTO T_UserInfo         
      
 SELECT 'ABCDE'+CAST(@I AS VARCHAR(20))+'EF','李'+CAST(@I AS VARCHAR(20)),         
      
 GETDATE(),'876543'+CAST(@I AS VARCHAR(20))         
      
 SELECT @I = @I + 1         
      
 END        
     
    

 
 --建聚集索引         
      
 CREATE CLUSTERED INDEX INDEX_Userid ON T_UserInfo (Userid)         
      
 --建非聚集索引         
      
 CREATE NONCLUSTERED INDEX INDEX_Userid ON T_UserInfo (Userid)         
      
 --删除索引         
      
 DROP INDEX T_UserInfo.INDEX_Userid         
      
 --顯示有關由Transact-SQL 語句生成的磁盤活動量的資訊         
      
 SET STATISTICS IO ON         
      
 --關閉有關由Transact-SQL 語句生成的磁盤活動量的資訊         
      
 SET STATISTICS IO OFF         
      
 --顯示[傳回有關語句執行情況的詳細資訊，并估計語句對資源的需求]         
      
 SET SHOWPLAN_ALL ON         
      
 --關閉[傳回有關語句執行情況的詳細資訊，并估計語句對資源的需求]         
      
 SET SHOWPLAN_ALL OFF        
     
    

請記住：SET STATISTICS IO 和 SET SHOWPLAN_ALL 是互斥的。

OK，現在開始：

首先，我們插入100條資料，然後我寫了一個查詢語句：SELECT * FROM T_UserInfo WHERE USERID='ABCDE6EF'，這就是SQL Server的執行計劃。

表掃描：掃描表中的行，然後我們來看該語句對IO的讀寫，執行:SET STATISTICS IO ON，此時再執行該SQL:SELECT * FROM T_UserInfo WHERE USERID='ABCDE6EF'。

切換到消失欄顯示如下：表'T_UserInfo'。掃描計數1，邏輯讀1 次，實體讀0 次，預讀0 次。

四個值分别為：執行的掃描次數;從資料緩存讀取的頁數;從磁盤讀取的頁數;為進行查詢而放入緩存的頁數。

注意：如果對于一個SQL查詢有多種寫法，那麼這四個值中的邏輯讀(logical reads)決定了哪個是最優化的。

接下來我們為其建一個聚集索引，執行CREATE CLUSTERED INDEX INDEX_Userid ON T_UserInfo (Userid)，然後再執行SELECT * FROM T_UserInfo WHERE USERID='ABCDE6EF'

切換到消息欄如下顯示：

表'T_UserInfo'。掃描計數1，邏輯讀2 次，實體讀0 次，預讀0 次。

此時邏輯讀由原來的1變成2，說明我們又加了一個索引頁，現在我們查詢時，邏輯讀就是要讀兩頁(1索引頁+1資料頁)，此時的效率還不如不建索引。

聚集索引查找：掃描聚集索引中特定範圍的行，說明，此時用了索引。OK,到這裡你應該已經知道初步知道MSSQL查詢計劃和如何檢視對IO的讀取消耗了吧！

接下來我們繼續：

現在我再把測試資料改變成1000條，再執行SET STATISTICS IO ON,再執行，SELECT * FROM T_UserInfo WHERE USERID='ABCDE6EF'

在不加聚集索引的情況下：表'T_UserInfo'。掃描計數1，邏輯讀7 次，實體讀0 次，預讀0 次。

在加聚集索引的情況下：CREATE CLUSTERED INDEX INDEX_Userid ON T_UserInfo (Userid)，表'T_UserInfo'。掃描計數1，邏輯讀2 次，實體讀0 次，預讀0 次。(其實也就是說此時是讀了一個索引頁，一個資料頁)如此，在資料量稍大時，索引的查詢優勢就顯示出來了。

總結：

當你建構SQL語句時，按Ctrl+L就可以看到語句是如何執行，是用索引掃描還是表掃描？

通過SET STATISTICS IO ON 來檢視邏輯讀，完成同一功能的不同SQL語句，邏輯讀越小查詢速度越快(當然不要找那個隻有幾百條記錄的例子來反我)。

我們再繼續深入：

OK，現在我們再來看一次，我們換個SQL語句，來看下MSSQL如何來執行的此SQL呢？

現在去掉索引：DROP INDEX T_UserInfo.INDEX_Userid，現在打開[顯示語句執行情況的詳細資訊]：SET SHOWPLAN_ALL ON，然後再執行：SELECT * FROM T_UserInfo WHERE USERID LIKE 'ABCDE8%'

看結果欄：結果中有些具體參數，比如IO的消耗，CPU的消耗。

在這裡我們隻看StmtText:SELECT * FROM T_UserInfo WHERE USERID LIKE 'ABCDE8%'|--Table Scan(OBJECT:([student].[dbo].[T_UserInfo]), WHERE:(like([T_UserInfo].[Userid], 'ABCDE8%', NULL)))

我再加上索引：

先關閉：SET SHOWPLAN_ALL OFF

再執行：CREATE CLUSTERED INDEX INDEX_Userid ON T_UserInfo (Userid)

再開啟：SET SHOWPLAN_ALL ON

再執行：SELECT * FROM T_UserInfo WHERE USERID LIKE 'ABCDE8%'

檢視StmtText:SELECT * FROM T_UserInfo WHERE USERID LIKE 'ABCDE8%'|--Clustered Index Seek(OBJECT:([student].[dbo].[T_UserInfo].[INDEX_Userid]), SEEK:([T_UserInfo].[Userid] >= 'ABCDE8' AND [T_UserInfo].[Userid] < 'ABCDE9'), WHERE:(like([T_UserInfo].[Userid], 'ABCDE8%', NULL)) ORDERED FORWARD)

在有索引的情況下，我們再寫一個SQL：

 
 SET SHOWPLAN_ALL ON         
      
 SELECT * FROM T_UserInfo WHERE LEFT(USERID,4)='ABCDE8%'        
     
    

檢視StmtText:SELECT * FROM T_UserInfo WHERE LEFT(USERID,4)='ABCDE8%'|--Clustered Index Scan(OBJECT:([student].[dbo].[T_UserInfo].[INDEX_Userid]), WHERE:(substring([T_UserInfo].[Userid], 1, 4)='ABCDE8%'))

我們再分别看一下三種情況下對IO的操作

分别如下：

第一種情況：表'T_UserInfo'。掃描計數1，邏輯讀7 次，實體讀0 次，預讀0 次。

第二種情況：表'T_UserInfo'。掃描計數1，邏輯讀3 次，實體讀0 次，預讀0 次。

第三種情況：表'T_UserInfo'。掃描計數1，邏輯讀8 次，實體讀0 次，預讀0 次。

這說明:

第一次是表掃描，掃了7頁，也就是全表掃描

第二次是索引掃描，掃了1頁索引，2頁資料頁

第三次是索引掃描+表掃描，掃了1頁索引，7頁資料頁。

[圖形界面也有對CPU和IO的消耗，也可以看出來哪個最優!]

通過比較，很容易的看出：第二種第三種寫法在都有索引的情況下，like有效的使用索引，而left則不能，這樣一個最簡單的優化的例子就出來了。

在上面的例子中，用的是聚集索引掃描，字段是字母加數字，大家可以試試看純數字的、字母的、漢字的等等，了解下SQL Server會如何改變SQL語句來利用索引。然後再試試非聚集索引是什麼情況？用不用索引和什麼有關？子查詢MSSQL是如何執行？IN用不用索引，LIKE用不用索引？函數用不用索引？OR、AND、UNION？子查詢呢？在這裡我不一一去試給大家看了，隻要知道了如何去看MSSQL的執行計劃（圖形和文本)，很多事情就很明朗了。

總結：

實作同一查詢功能的SQL寫法可能會有多種，如果判斷哪種最優化，如果僅僅是從時間上來測，會受很多外界因素的影響，而我們明白了MSSQL如何去執行，通過IO邏輯讀、通過檢視圖示的查詢計劃、通過其優化後而執行的SQL語句，才是優化SQL的真正途徑。

另外提醒下：資料量的多少有時會影響MSSQL對同一種查詢寫法語句的執行計劃，這一點在非聚集索引上特别明顯，還有就是在多CPU與單CPU下，在多使用者并發情況下，同一寫法的查詢語句執行計劃會有所不同，這個就需要大家有機會去試驗。

關于SQL Server執行計劃及SQL查詢優化的執行個體分析就介紹到這裡了，希望本次的介紹能夠對您有所收獲！