Sql Server 執行計畫及Sql查詢最佳化

今天來討論下MSSQL的執行計畫，來讓大家知道如何查看MSSQL的最佳化機制，以此來最佳化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

--相關SQL語句解釋
-------------------------------------------------------------建叢集索引
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' 選中以上語句，按Ctrl+L，如   這就是MSSQL的執行計畫：表掃描：掃描表中的行  
然後我們來看該語句對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資料頁)，此時的效率還不如不建索引。  
此時再選中查詢語句，然後再Ctrl+L，如:
叢集索引尋找：掃描叢集索引中特定範圍的行 說明，此時用了索引。  
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))) Ctrl+L看下此時的圖行執行計畫：  

我再加上索引：
先關閉：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)Ctrl+L看下此時的圖行執行計畫： Ctrl+L看下此時的圖行執行計畫：    
在有索引的情況下，我們再寫一個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%')) Ctrl+L看下此時的圖行執行計畫：  
我們再分別看一下三種情況下對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的最佳化有初步新的想法了，網上一堆堆的SQL最佳化的文章真的是那樣嗎？你自己試試就知道了，而不必盲目去記那些東西，自己試試，看看MSSQL到底是怎麼來執行就明白了。

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

總結： 實現同一查詢功能的SQL寫法可能會有多種，如果判斷哪種最佳化，如果僅僅是從時間上來測，會受很多外界因素的影響，而我們明白了MSSQL如何去執行，通過IO邏輯讀、通過查看圖示的查詢計劃、通過其最佳化後而執行的SQL語句，才是最佳化SQL的真正途徑。   另外提醒下：資料量的多少有時會影響MSSQL對同一種查詢寫法語句的執行計畫，這一點在非叢集索引上特別明顯，還有就是在多CPU與單CPU下，在多使用者並發情況下，同一寫法的查詢語句執行計畫會有所不同，這個就需要大家有機會去實驗了(我也沒有這方面的太多經驗與大家分享)。   先寫這些吧，由於我對MSSQL認識還很淺薄，如有不對的地方，還請指正。