SqlServer執行計劃及Sql查詢優化的示例分析

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查詢優化的示例分析的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！