引用:初探Sql Server 执行计划及Sql查询优化
初探Sql Server 執(zhí)行計劃及Sql查詢優(yōu)化?收藏
MSSQL優(yōu)化之————探索MSSQL執(zhí)行計劃
?
作者：no_mIss
?
最近總想整理下對MSSQL的一些理解與感悟，卻一直沒有心思和時間寫，晚上無事便寫了一篇探索MSSQL執(zhí)行計劃,本文講執(zhí)行計劃但不僅限于講執(zhí)行計劃。
?
網(wǎng)上的SQL優(yōu)化的文章實在是很多，說實在的，我也曾經(jīng)到處找這樣的文章，什么不要使用IN了，什么OR了，什么AND了，很多很多，還有很多人拿出僅幾S甚至幾MS的時間差的例子來證明著什么(有點可笑)，讓許多人不知道其是對還是錯。而SQL優(yōu)化又是每個要與數(shù)據(jù)庫打交道的程序員的必修課，所以寫了此文，與朋友們共勉。
?
談到優(yōu)化就必然要涉及索引，就像要講鎖必然要說事務(wù)一樣，所以你需要了解一下索引，僅僅是索引，就能講半天了，所以索引我就不說了(打很多字是很累的，況且我也知之甚少)，可以去參考相關(guān)的文章，這個網(wǎng)上資料比較多了。
?
今天來探索下MSSQL的執(zhí)行計劃，來讓大家知道如何查看MSSQL的優(yōu)化機(jī)制，以此來優(yōu)化SQL查詢。
?
--DROP TABLE T_UserInfo----------------------------------------------------
--建測試表
CREATE?TABLE?T_UserInfo
(
????Userid varchar(20),??UserName varchar(20),
????RegTime?datetime,?Tel varchar(20),
)
--插入測試數(shù)據(jù)
DECLARE?@I?INT
DECLARE?@ENDID?INT
SELECT?@I?=?1
SELECT?@ENDID = 100??--在此處更改要插入的數(shù)據(jù)，重新插入之前要刪掉所有數(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?語句生成的磁盤活動量的信息
SET?STATISTICS?IO?ON
--關(guān)閉有關(guān)由Transact-SQL?語句生成的磁盤活動量的信息
SET?STATISTICS?IO?OFF
--顯示[返回有關(guān)語句執(zhí)行情況的詳細(xì)信息，并估計語句對資源的需求]
SET?SHOWPLAN_ALL??ON
--關(guān)閉[返回有關(guān)語句執(zhí)行情況的詳細(xì)信息，并估計語句對資源的需求]
SET?SHOWPLAN_ALL??OFF
---------------------------------------------------------------------------
請記住：SET?STATISTICS?IO?和?SET?SHOWPLAN_ALL?是互斥的。
?
OK，現(xiàn)在開始：
首先，我們插入100條數(shù)據(jù)
然后我寫了一個查詢語句：
SELECT?*?FROM?T_UserInfo?WHERE?USERID='ABCDE6EF'
選中以上語句，按Ctrl+L，如下圖
?
?
這就是MSSQL的執(zhí)行計劃：表掃描：掃描表中的行
?
然后我們來看該語句對IO的讀寫：
執(zhí)行:SET?STATISTICS?IO?ON
此時再執(zhí)行該SQL:SELECT?*?FROM?T_UserInfo?WHERE?USERID='ABCDE6EF'
切換到消失欄顯示如下：
表'T_UserInfo'。掃描計數(shù)1，邏輯讀1?次，物理讀0?次，預(yù)讀0?次。
解釋下其意思：
四個值分別為：
????執(zhí)行的掃描次數(shù);
????從數(shù)據(jù)緩存讀取的頁數(shù);
????從磁盤讀取的頁數(shù);
????為進(jìn)行查詢而放入緩存的頁數(shù)
重要：如果對于一個SQL查詢有多種寫法，那么這四個值中的邏輯讀(logical reads)決定了哪個是最優(yōu)化的。
?
接下來我們?yōu)槠浣ㄒ粋€聚集索引
執(zhí)行CREATE?CLUSTERED?INDEX?INDEX_Userid??ON?T_UserInfo?(Userid)
然后再執(zhí)行SELECT?*?FROM?T_UserInfo?WHERE?USERID='ABCDE6EF'
切換到消息欄如下顯示：
表'T_UserInfo'。掃描計數(shù)1，邏輯讀2?次，物理讀0?次，預(yù)讀0?次。
此時邏輯讀由原來的1變成2，
說明我們又加了一個索引頁，現(xiàn)在我們查詢時，邏輯讀就是要讀兩頁(1索引頁+1數(shù)據(jù)頁)，此時的效率還不如不建索引。
?
此時再選中查詢語句，然后再Ctrl+L，如下圖:
?
聚集索引查找：掃描聚集索引中特定范圍的行
說明，此時用了索引。
?
OK,到這里你應(yīng)該已經(jīng)知道初步知道MSSQL查詢計劃和如何查看對IO的讀取消耗了吧！
?
?
接下來我們繼續(xù)：
?
現(xiàn)在我再把測試數(shù)據(jù)改變成1000條
再執(zhí)行SET?STATISTICS?IO?ON,再執(zhí)行
SELECT?*?FROM?T_UserInfo?WHERE?USERID='ABCDE6EF'
在不加聚集索引的情況下：
表'T_UserInfo'。掃描計數(shù)1，邏輯讀7?次，物理讀0?次，預(yù)讀0?次。
在加聚集索引的情況下：CREATE?CLUSTERED?INDEX?INDEX_Userid??ON?T_UserInfo?(Userid)
表'T_UserInfo'。掃描計數(shù)1，邏輯讀2?次，物理讀0?次，預(yù)讀0?次。
(其實也就是說此時是讀了一個索引頁，一個數(shù)據(jù)頁)
如此，在數(shù)據(jù)量稍大時，索引的查詢優(yōu)勢就顯示出來了。
?
?
?
先小總結(jié)下：
當(dāng)你構(gòu)建SQL語句時，按Ctrl+L就可以看到語句是如何執(zhí)行，是用索引掃描還是表掃描？
通過SET?STATISTICS?IO?ON?來查看邏輯讀，完成同一功能的不同SQL語句，邏輯讀
越小查詢速度越快(當(dāng)然不要找那個只有幾百條記錄的例子來反我)。
?
?我們再繼續(xù)深入：
OK，現(xiàn)在我們再來看一次，我們換個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看下此時的圖行執(zhí)行計劃：
?
我再加上索引：
先關(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看下此時的圖行執(zhí)行計劃：
Ctrl+L看下此時的圖行執(zhí)行計劃：
?
?
在有索引的情況下，我們再寫一個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看下此時的圖行執(zhí)行計劃：
?
?
我們再分別看一下三種情況下對IO的操作
分別如下：
第一種情況：表'T_UserInfo'。掃描計數(shù)1，邏輯讀7?次，物理讀0?次，預(yù)讀0?次。
第二種情況：表'T_UserInfo'。掃描計數(shù)1，邏輯讀3?次，物理讀0?次，預(yù)讀0?次。
第三種情況：表'T_UserInfo'。掃描計數(shù)1，邏輯讀8?次，物理讀0?次，預(yù)讀0?次。
這說明:
第一次是表掃描，掃了7頁，也就是全表掃描
第二次是索引掃描，掃了1頁索引，2頁數(shù)據(jù)頁
第三次是索引掃描+表掃描，掃了1頁索引，7頁數(shù)據(jù)頁
[圖形界面也有對CPU和IO的消耗，也可以看出來哪個最優(yōu)!]?
?
通過比較，嘿嘿，很容易的看出：第二種第三種寫法在都有索引的情況下，like有效的使用索引，而left則不能，這樣一個最簡單的優(yōu)化的例子就出來了，哈哈。
?
?
如果以上你都明白了，那么你可能已經(jīng)對SQL的優(yōu)化有初步新的想法了，網(wǎng)上一堆堆的SQL優(yōu)化的文章真的是那樣嗎？你自己試試就知道了，而不必盲目去記那些東西，自己試試，看看MSSQL到底是怎么來執(zhí)行就明白了。在我舉的例子中，用的是聚集索引掃描，字段是字母加數(shù)字，大家可以試試看純數(shù)字的、字母的、漢字的等等，了解下MMSQL會如何改變SQL語句來利用索引。然后再試試非聚集索引是什么情況？用不用索引和什么有關(guān)？子查詢MSSQL是如何執(zhí)行？IN用不用索引，LIKE用不用索引？函數(shù)用不用索引？OR、AND、UNION？子查詢呢？在這里我不一一去試給大家看了，只要知道了如何去看MSSQL的執(zhí)行計劃（圖形和文本)，很多事情就很明朗了。
?
大總結(jié)：
實現(xiàn)同一查詢功能的SQL寫法可能會有多種，如果判斷哪種最優(yōu)化，如果僅僅是從時間上來測，會受很多外界因素的影響，而我們明白了MSSQL如何去執(zhí)行，通過IO邏輯讀、通過查看圖示的查詢計劃、通過其優(yōu)化后而執(zhí)行的SQL語句，才是優(yōu)化SQL的真正途徑。
?
另外提醒下：數(shù)據(jù)量的多少有時會影響MSSQL對同一種查詢寫法語句的執(zhí)行計劃，這一點在非聚集索引上特別明顯，還有就是在多CPU與單CPU下，在多用戶并發(fā)情況下，同一寫法的查詢語句執(zhí)行計劃會有所不同，這個就需要大家有機(jī)會去試驗了(我也沒有這方面的太多經(jīng)驗與大家分享)。
?
先寫這些吧，由于我對MSSQL認(rèn)識還很淺薄，如有不對的地方，還請指正。
posted on 2018-05-24 00:08 NET未來之路 閱讀(...) 評論(...) 編輯 收藏轉(zhuǎn)載于:https://www.cnblogs.com/lonelyxmas/p/9080502.html
創(chuàng)作挑戰(zhàn)賽新人創(chuàng)作獎勵來咯，堅持創(chuàng)作打卡瓜分現(xiàn)金大獎總結(jié)
以上是生活随笔為你收集整理的引用:初探Sql Server 执行计划及Sql查询优化的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： gpu模式是什么意思？
- 下一篇： 交通事故备案注意什么