MSSQL 優(yōu)化之 ———— 探索 MSSQL 執(zhí)行計(jì)劃

?

作者： no_mIss

?

最近總想整理下對(duì) MSSQL 的一些理解與感悟，卻一直沒有心思和時(shí)間寫 ，晚上無事便寫了一篇探索 MSSQL 執(zhí)行計(jì)劃,本文講執(zhí)行計(jì)劃但不僅限于講執(zhí)行計(jì)劃。 ?

網(wǎng)上的 SQL 優(yōu)化的文章實(shí)在是很多，說實(shí)在的，我也曾經(jīng)到處找這樣的文章，什么不要使用 IN 了，什么 OR 了，什么 AND 了，很多很多，還有很多人拿出僅幾 S 甚至幾 MS 的時(shí)間差的例子來證明著什么 ( 有點(diǎn)可笑 ) ，讓許多人不知道其是對(duì)還是錯(cuò)。而 SQL 優(yōu)化又是每個(gè)要與數(shù)據(jù)庫打交道的程序員的必修課，所以寫了此文，與朋友們共勉。 ?

談到優(yōu)化就必然要涉及索引，就像要講鎖必然要說事務(wù)一樣，所以你需要了解一下索引，僅僅是索引，就能講半天了，所以索引我就不說了 ( 打很多字是很累的，況且我也知之甚少 ) ，可以去參考相關(guān)的文章，這個(gè)網(wǎng)上資料比較多了。 ?

今天來探索下 MSSQL 的執(zhí)行計(jì)劃，來讓大家知道如何查看 MSSQL 的優(yōu)化機(jī)制，以此來優(yōu)化 SQL 查詢。

?

--DROP TABLE T_UserInfo----------------------------------------------------

-- 建測(cè)試表

CREATE ? TABLE ?T_UserInfo

(

???? Userid varchar ( 20 ), ?? UserName varchar ( 20 ),

???? RegTime? datetime , ? Tel varchar ( 20 ),

)

-- 插入測(cè)試數(shù)據(jù)

DECLARE ?@I? INT

DECLARE? @ENDID ?INT

SELECT ?@I? = ?1

SELECT ?@ENDID = 100 ?? -- 在此處更改要插入的數(shù)據(jù)，重新插入之前要?jiǎng)h掉所有數(shù)據(jù)

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

?

-- 相關(guān) 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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

-- 顯示有關(guān)由 Transact-SQL? 語句生成的磁盤活動(dòng)量的信息

SET ? STATISTICS ?IO? ON

-- 關(guān)閉有關(guān)由 Transact-SQL? 語句生成的磁盤活動(dòng)量的信息

SET ? STATISTICS ?IO? OFF

-- 顯示 [ 返回有關(guān)語句執(zhí)行情況的詳細(xì)信息，并估計(jì)語句對(duì)資源的需求 ]

SET ?SHOWPLAN_ALL ?? ON

-- 關(guān)閉 [ 返回有關(guān)語句執(zhí)行情況的詳細(xì)信息，并估計(jì)語句對(duì)資源的需求 ]

SET ?SHOWPLAN_ALL ?? OFF

---------------------------------------------------------------------------

請(qǐng)記住： SET ? STATISTICS ?IO? 和 ? SET ?SHOWPLAN_ALL? 是互斥的。

?

OK ，現(xiàn)在開始：

首先，我們插入 100 條數(shù)據(jù)

然后我寫了一個(gè)查詢語句：

SELECT ? * ? FROM ?T_UserInfo? WHERE ?USERID = 'ABCDE6EF'

選中以上語句，按 Ctrl + L ，如下圖

?

?

這就是 MSSQL 的執(zhí)行計(jì)劃：表掃描：掃描表中的行

?

然后我們來看該語句對(duì) IO 的讀寫：

執(zhí)行 : SET ? STATISTICS ?IO? ON

此時(shí)再執(zhí)行該 SQL : SELECT ? * ? FROM ?T_UserInfo? WHERE ?USERID = 'ABCDE6EF'

切換到消失欄顯示如下：

表 'T_UserInfo' 。掃描計(jì)數(shù) 1 ，邏輯讀 1? 次，物理讀 0? 次，預(yù)讀 0? 次。

解釋下其意思：

四個(gè)值分別為：

???? 執(zhí)行的掃描次數(shù) ;

???? 從數(shù)據(jù)緩存讀取的頁數(shù) ;

?? ?? 從磁盤讀取的頁數(shù) ;

???? 為進(jìn)行查詢而放入緩存的頁數(shù)

重要：如果對(duì)于一個(gè) SQL 查詢有多種寫法，那么這四個(gè)值中的邏輯讀 ( logical reads ) 決定了哪個(gè)是最優(yōu)化的。

?

接下來我們?yōu)槠浣ㄒ粋€(gè)聚集索引

執(zhí)行 CREATE ? CLUSTERED ? INDEX ?INDEX_Userid ?? ON ?T_UserInfo? ( Userid )

然后再執(zhí)行 SELECT ? * ? FROM ?T_UserInfo? WHERE ?USERID = 'ABCDE6EF'

切換到消息欄如下顯示：

表 'T_UserInfo' 。掃描計(jì)數(shù) 1 ，邏輯讀 2? 次，物理讀 0? 次，預(yù)讀 0? 次。

此時(shí)邏輯讀由原來的 1 變成 2 ，

說明我們又加了一個(gè)索引頁，現(xiàn)在我們查詢時(shí)，邏輯讀就是要讀兩頁 (1 索引頁 +1 數(shù)據(jù)頁 ) ，此時(shí)的效率還不如不建索引。

?

此時(shí)再選中查詢語句，然后再 Ctrl + L ，如下圖 :

?

聚集索引查找：掃描聚集索引中特定范圍的行

說明，此時(shí)用了索引。

?

OK , 到這里你應(yīng)該已經(jīng)知道初步知道 MSSQL 查詢計(jì)劃和如何查看對(duì) IO 的讀取消耗了吧！

?

?

接下來我們繼續(xù)：

?

現(xiàn)在我再把測(cè)試數(shù)據(jù)改變成 1000 條

再執(zhí)行 SET ? STATISTICS ?IO? ON , 再執(zhí)行

SELECT ? * ? FROM ?T_UserInfo? WHERE ?USERID = 'ABCDE6EF'

在不加聚集索引的情況下：

表 'T_UserInfo' 。掃描計(jì)數(shù) 1 ，邏輯讀 7? 次，物理讀 0? 次，預(yù)讀 0? 次。

在加聚集索引的情況下： CREATE ? CLUSTERED ? INDEX ?INDEX_Userid ?? ON ?T_UserInfo? ( Userid )

表 'T_UserInfo' 。掃描計(jì)數(shù) 1 ，邏輯讀 2? 次，物理讀 0? 次，預(yù)讀 0? 次。

( 其實(shí)也就是說此時(shí)是讀了一個(gè)索引頁，一個(gè)數(shù)據(jù)頁 )

如此，在數(shù)據(jù)量稍大時(shí)，索引的查詢優(yōu)勢(shì)就顯示出來了。

?

?

?

先小總結(jié)下 ：

當(dāng)你構(gòu)建 SQL 語句時(shí)，按 Ctrl + L 就可以看到語句是如何執(zhí)行，是用索引掃描還是表掃描？

通過 SET ? STATISTICS ?IO? ON ? 來查看邏輯讀，完成同一功能的不同 SQL 語句，邏輯讀

越小查詢速度越快 ( 當(dāng)然不要找那個(gè)只有幾百條記錄的例子來反我 ) 。

? ?

我們?cè)倮^續(xù)深入：

OK ，現(xiàn)在我們?cè)賮砜匆淮危覀儞Q個(gè) SQL 語句，來看下 MSSQL 如何來執(zhí)行的此 SQL 呢？

現(xiàn)在去掉索引： DROP ? INDEX ?T_UserInfo . INDEX_Userid

現(xiàn)在打開 [ 顯示語句執(zhí)行情況的詳細(xì)信息 ] ： SET ?SHOWPLAN_ALL ?? ON

然后再執(zhí)行： SELECT ? * ? FROM ?T_UserInfo? WHERE ?USERID? LIKE ? 'ABCDE8%'

看結(jié)果欄：結(jié)果中有些具體參數(shù)，比如 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 看下此時(shí)的圖行執(zhí)行計(jì)劃：

?

我再加上索引：

先關(guān)閉： SET ?SHOWPLAN_ALL? OFF

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

再開啟： SET ?SHOWPLAN_ALL? ON

再執(zhí)行： 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 看下此時(shí)的圖行執(zhí)行計(jì)劃：

Ctrl + L 看下此時(shí)的圖行執(zhí)行計(jì)劃：

?

?

在有索引的情況下，我們?cè)賹懸粋€(gè) 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 看下此時(shí)的圖行執(zhí)行計(jì)劃：

?

?

我們?cè)俜謩e看一下三種情況下對(duì) IO 的操作

分別如下：

第一種情況：表 'T_UserInfo' 。掃描計(jì)數(shù) 1 ，邏輯讀 7? 次，物理讀 0? 次，預(yù)讀 0? 次。

第二種情況：表 'T_UserInfo' 。掃描計(jì)數(shù) 1 ，邏輯讀 3? 次，物理讀 0? 次，預(yù)讀 0? 次。

第三種情況：表 'T_UserInfo' 。掃描計(jì)數(shù) 1 ，邏輯讀 8? 次，物理讀 0? 次，預(yù)讀 0? 次。

這說明 :

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

第二次是索引掃描，掃了 1 頁索引， 2 頁數(shù)據(jù)頁

第三次是索引掃描 + 表掃描，掃了 1 頁索引， 7 頁數(shù)據(jù)頁

[圖形界面也有對(duì)CPU和IO的消耗，也可以看出來哪個(gè)最優(yōu)!]?

?

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

?

? 如果以上你都明白了，那么你可能已經(jīng)對(duì) SQL 的優(yōu)化有初步新的想法了，網(wǎng)上一堆堆的 SQL 優(yōu)化的文章真的是那樣嗎？你自己試試就知道了，而不必盲目去記那些東西，自己試試，看看 MSSQL 到底是怎么來執(zhí)行就明白了。

在我舉的例子中，用的是聚集索引掃描，字段是字母加數(shù)字，大家可以試試看純數(shù)字的、字母的、漢字的等等，了解下 MMSQL 會(huì)如何改變 SQL 語句來利用索引。然后再試試非聚集索引是什么情況？用不用索引和什么有關(guān)？子查詢 MSSQL 是如何執(zhí)行？ IN 用不用索引， LIKE 用不用索引？函數(shù)用不用索引？ OR 、 AND 、 UNION ？子查詢呢？在這里我不一一去試給大家看了，只要知道了如何去看 MSSQL 的執(zhí)行計(jì)劃（圖形和文本 ) ，很多事情就很明朗了。

?

大總結(jié)：

實(shí)現(xiàn)同一查詢功能的 SQL 寫法可能會(huì)有多種，如果判斷哪種最優(yōu)化，如果僅僅是從時(shí)間上來測(cè)，會(huì)受很多外界因素的影響，而我們明白了 MSSQL 如何去執(zhí)行，通過 IO 邏輯讀、通過查看圖示的查詢計(jì)劃、通過其優(yōu)化后而執(zhí)行的 SQL 語句，才是優(yōu)化 SQL 的真正途徑。

?

另外提醒下：數(shù)據(jù)量的多少有時(shí)會(huì)影響 MSSQL 對(duì)同一種查詢寫法語句的執(zhí)行計(jì)劃，這一點(diǎn)在非聚集索引上特別明顯，還有就是在多 CPU 與單 CPU 下，在多用戶并發(fā)情況下，同一寫法的查詢語句執(zhí)行計(jì)劃會(huì)有所不同，這個(gè)就需要大家有機(jī)會(huì)去試驗(yàn)了 ( 我也沒有這方面的太多經(jīng)驗(yàn)與大家分享 ) 。

?

先寫這些吧，由于我對(duì) MSSQL 認(rèn)識(shí)還很淺薄，如有不對(duì)的地方，還請(qǐng)指正。