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

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

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

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

--建測(cè)試表

CREATETABLET_UserInfo

(

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

RegTimedatetime,Tel varchar(20),

)

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

DECLARE@IINT

DECLARE@ENDIDINT

SELECT@I=1

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

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

--相關(guān)SQL語(yǔ)句解釋

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

--建聚集索引

CREATECLUSTEREDINDEXINDEX_UseridONT_UserInfo(Userid)

--建非聚集索引

CREATENONCLUSTEREDINDEXINDEX_UseridONT_UserInfo(Userid)

--刪除索引

DROPINDEXT_UserInfo.INDEX_Userid

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

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

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

SETSTATISTICSIOON

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

SETSTATISTICSIOOFF

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

SETSHOWPLAN_ALLON

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

SETSHOWPLAN_ALLOFF

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

請(qǐng)記?。?/SPAN>SETSTATISTICSIO和SETSHOWPLAN_ALL是互斥的。

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

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

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

SELECT*FROMT_UserInfoWHEREUSERID='ABCDE6EF'

選中以上語(yǔ)句，按Ctrl+L，如下圖

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

然后我們來(lái)看該語(yǔ)句對(duì)IO的讀寫：

執(zhí)行:SETSTATISTICSIOON

此時(shí)再執(zhí)行該SQL:SELECT*FROMT_UserInfoWHEREUSERID='ABCDE6EF'

切換到消失欄顯示如下：

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

解釋下其意思：

四個(gè)值分別為：

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

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

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

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

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

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

執(zhí)行CREATECLUSTEREDINDEXINDEX_UseridONT_UserInfo(Userid)

然后再執(zhí)行SELECT*FROMT_UserInfoWHEREUSERID='ABCDE6EF'

切換到消息欄如下顯示：

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

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

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

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

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

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

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

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

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

再執(zhí)行SETSTATISTICSIOON,再執(zhí)行

SELECT*FROMT_UserInfoWHEREUSERID='ABCDE6EF'

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

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

在加聚集索引的情況下：CREATECLUSTEREDINDEXINDEX_UseridONT_UserInfo(Userid)

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

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

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

先小總結(jié)下：

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

通過(guò)SETSTATISTICSIOON來(lái)查看邏輯讀，完成同一功能的不同SQL語(yǔ)句，邏輯讀

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

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

OK，現(xiàn)在我們?cè)賮?lái)看一次，我們換個(gè)SQL語(yǔ)句，來(lái)看下MSSQL如何來(lái)執(zhí)行的此SQL呢？

現(xiàn)在去掉索引：DROPINDEXT_UserInfo.INDEX_Userid

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

版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。