共計 4509 個字符，預計需要花費 12 分鐘才能閱讀完成。
SqlServer 執行計劃及 Sql 查詢優化的示例分析，相信很多沒有經驗的人對此束手無策，為此本文總結了問題出現的原因和解決方法，通過這篇文章希望你能解決這個問題。
談到優化就必然要涉及索引，就像要講鎖必然要說事務一樣，所以你需要了解一下索引，僅僅是索引，就能講半天了，所以索引我就不說了(打很多字是很累的，況且我也知之甚少)，可以去參考相關的文章，這個網上資料比較多了。
今天來探索下 MSSQL 的執行計劃，來讓大家知道如何查看 MSSQL 的優化機制，以此來優化 SQL 查詢。
–DROP TABLE T_UserInfo—————————————————-
– 建測試表
CREATETABLET_UserInfo
(
Userid varchar(20),UserName varchar(20),
RegTimedatetime,Tel varchar(20),
)
– 插入測試數據
DECLARE@IINT
DECLARE@ENDIDINT
SELECT@I=1
SELECT@ENDID = 100– 在此處更改要插入的數據，重新插入之前要刪掉所有數據
WHILE@I =@ENDID
BEGIN
INSERTINTOT_UserInfo
SELECT ABCDE +CAST(@IASVARCHAR(20))+ EF , 李 +CAST(@IASVARCHAR(20)),
GETDATE(), 876543 +CAST(@IASVARCHAR(20))
SELECT@I=@I+1
END
– 相關 SQL 語句解釋
—————————————————————————
– 建聚集索引
CREATECLUSTEREDINDEXINDEX_UseridONT_UserInfo(Userid)
– 建非聚集索引
CREATENONCLUSTEREDINDEXINDEX_UseridONT_UserInfo(Userid)
– 刪除索引
DROPINDEXT_UserInfo.INDEX_Userid
—————————————————————————
—————————————————————————
– 顯示有關由 Transact-SQL 語句生成的磁盤活動量的信息
SETSTATISTICSIOON
– 關閉有關由 Transact-SQL 語句生成的磁盤活動量的信息
SETSTATISTICSIOOFF
– 顯示[返回有關語句執行情況的詳細信息，并估計語句對資源的需求]
SETSHOWPLAN_ALLON
– 關閉[返回有關語句執行情況的詳細信息，并估計語句對資源的需求]
SETSHOWPLAN_ALLOFF
—————————————————————————
請記住：SETSTATISTICSIO 和 SETSHOWPLAN_ALL 是互斥的。
OK，現在開始：
首先，我們插入 100 條數據
然后我寫了一個查詢語句：
SELECT*FROMT_UserInfoWHEREUSERID= ABCDE6EF
選中以上語句，按 Ctrl+L，如下圖
這就是 MSSQL 的執行計劃：表掃描：掃描表中的行
然后我們來看該語句對 IO 的讀寫：
執行:SETSTATISTICSIOON
此時再執行該 SQL:SELECT*FROMT_UserInfoWHEREUSERID= ABCDE6EF
切換到消失欄顯示如下：
表 T_UserInfo。掃描計數 1，邏輯讀 1 次，物理讀 0 次，預讀 0 次。
解釋下其意思：
四個值分別為：
執行的掃描次數;
從數據緩存讀取的頁數;
從磁盤讀取的頁數;
為進行查詢而放入緩存的頁數
重要：如果對于一個 SQL 查詢有多種寫法，那么這四個值中的邏輯讀 (logical reads) 決定了哪個是最優化的。
接下來我們為其建一個聚集索引
執行 CREATECLUSTEREDINDEXINDEX_UseridONT_UserInfo(Userid)
然后再執行 SELECT*FROMT_UserInfoWHEREUSERID= ABCDE6EF
切換到消息欄如下顯示：
表 T_UserInfo。掃描計數 1，邏輯讀 2 次，物理讀 0 次，預讀 0 次。
此時邏輯讀由原來的 1 變成 2，
說明我們又加了一個索引頁，現在我們查詢時，邏輯讀就是要讀兩頁(1 索引頁 + 1 數據頁)，此時的效率還不如不建索引。
此時再選中查詢語句，然后再 Ctrl+L，如下圖:
聚集索引查找：掃描聚集索引中特定范圍的行
說明，此時用了索引。
OK, 到這里你應該已經知道初步知道 MSSQL 查詢計劃和如何查看對 IO 的讀取消耗了吧！
接下來我們繼續：
現在我再把測試數據改變成 1000 條
再執行 SETSTATISTICSIOON, 再執行
SELECT*FROMT_UserInfoWHEREUSERID= ABCDE6EF
在不加聚集索引的情況下：
表 T_UserInfo。掃描計數 1，邏輯讀 7 次，物理讀 0 次，預讀 0 次。
在加聚集索引的情況下：CREATECLUSTEREDINDEXINDEX_UseridONT_UserInfo(Userid)
表 T_UserInfo。掃描計數 1，邏輯讀 2 次，物理讀 0 次，預讀 0 次。
(其實也就是說此時是讀了一個索引頁，一個數據頁)
如此，在數據量稍大時，索引的查詢優勢就顯示出來了。
先小總結下：
當你構建 SQL 語句時，按 Ctrl+ L 就可以看到語句是如何執行，是用索引掃描還是表掃描？
通過 SETSTATISTICSIOON 來查看邏輯讀，完成同一功能的不同 SQL 語句，邏輯讀
越小查詢速度越快(當然不要找那個只有幾百條記錄的例子來反我)。
我們再繼續深入：
OK，現在我們再來看一次，我們換個 SQL 語句，來看下 MSSQL 如何來執行的此 SQL 呢？
現在去掉索引：DROPINDEXT_UserInfo.INDEX_Userid
現在打開[顯示語句執行情況的詳細信息]：SETSHOWPLAN_ALLON
然后再執行：SELECT*FROMT_UserInfoWHEREUSERIDLIKE ABCDE8%
看結果欄：結果中有些具體參數，比如 IO 的消耗，CPU 的消耗。
在這里我們只看 StmtText:
SELECT*FROMT_UserInfoWHEREUSERIDLIKE ABCDE8%
|–Table Scan(OBJECT:([student].[dbo].[T_UserInfo]), WHERE:(like([T_UserInfo].[Userid], ABCDE8% , NULL)))
Ctrl+ L 看下此時的圖行執行計劃：
我再加上索引：
先關閉：SETSHOWPLAN_ALLOFF
再執行：CREATECLUSTEREDINDEXINDEX_UseridONT_UserInfo(Userid)
再開啟：SETSHOWPLAN_ALLON
再執行：SELECT*FROMT_UserInfoWHEREUSERIDLIKE ABCDE8%
查看 StmtText:
SELECT*FROMT_UserInfoWHEREUSERIDLIKE 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：
SETSHOWPLAN_ALLON
SELECT*FROMT_UserInfoWHERELEFT(USERID,4)= ABCDE8%
查看 StmtText:
SELECT*FROMT_UserInfoWHERELEFT(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 下，在多用戶并發情況下，同一寫法的查詢語句執行計劃會有所不同。
看完上述內容，你們掌握 SqlServer 執行計劃及 Sql 查詢優化的示例分析的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注丸趣 TV 行業資訊頻道，感謝各位的閱讀！
