程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> 更多數據庫知識 >> SqlServer 執行計劃及Sql查詢優化初探

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

編輯:更多數據庫知識

網上的SQL優化的文章實在是很多,說實在的,我也曾經到處找這樣的文章,什麼不要使用IN了,什麼OR了,什麼AND了,很多很多,還有很多人拿出僅幾S甚至幾MS的時間差的例子來證明著什麼(有點可笑),讓許多人不知道其是對還是錯。而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用不用索引?函數用不用索引?ORANDUNION?子查詢呢?在這裡我不一一去試給大家看了,只要知道了如何去看MSSQL的執行計劃(圖形和文本),很多事情就很明朗了。

 

大總結:

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

 

另外提醒下:數據量的多少有時會影響MSSQL對同一種查詢寫法語句的執行計劃,這一點在非聚集索引上特別明顯,還有就是在多CPU與單CPU下,在多用戶並發情況下,同一寫法的查詢語句執行計劃會有所不同,這個就需要大家有機會去試驗了(我也沒有這方面的太多經驗與大家分享)

 

先寫這些吧,由於我對MSSQL認識還很淺薄,如有不對的地方,還請指正。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved