建站
咨詢
售后
建站咨詢
新聞中心
SQL Server的執(zhí)行計劃及SQL查詢優(yōu)化是本文我們主要要介紹的內(nèi)容，談到優(yōu)化就必然要涉及索引，就像要講鎖必然要說事務一樣，所以你需要了解一下索引，今天來探索下SQL Server的執(zhí)行計劃，來讓大家知道如何查看SQL Server的優(yōu)化機制，以此來優(yōu)化SQL查詢。
創(chuàng)新互聯(lián)建站專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務，包含不限于成都網(wǎng)站建設、成都網(wǎng)站制作、翁源網(wǎng)絡推廣、微信小程序開發(fā)、翁源網(wǎng)絡營銷、翁源企業(yè)策劃、翁源品牌公關、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運營等，從售前售中售后，我們都將竭誠為您服務，您的肯定，是我們最大的嘉獎；創(chuàng)新互聯(lián)建站為所有大學生創(chuàng)業(yè)者提供翁源建站搭建服務，24小時服務熱線：028-86922220，官方網(wǎng)址：www.cdcxhl.com
- --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
- --建聚集索引
- 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
- --顯示[返回有關語句執(zhí)行情況的詳細信息，并估計語句對資源的需求]
- SET SHOWPLAN_ALL ON
- --關閉[返回有關語句執(zhí)行情況的詳細信息，并估計語句對資源的需求]
- SET SHOWPLAN_ALL OFF
請記?。篠ET STATISTICS IO 和 SET SHOWPLAN_ALL 是互斥的。
OK，現(xiàn)在開始：
首先，我們插入100條數(shù)據(jù)，然后我寫了一個查詢語句：SELECT * FROM T_UserInfo WHERE USERID='ABCDE6EF'，這就是SQL Server的執(zhí)行計劃。
表掃描：掃描表中的行，然后我們來看該語句對IO的讀寫，執(zhí)行:SET STATISTICS IO ON，此時再執(zhí)行該SQL:SELECT * FROM T_UserInfo WHERE USERID='ABCDE6EF'。
切換到消失欄顯示如下：表'T_UserInfo'。掃描計數(shù)1，邏輯讀1 次，物理讀0 次，預讀0 次。
四個值分別為：執(zhí)行的掃描次數(shù);從數(shù)據(jù)緩存讀取的頁數(shù);從磁盤讀取的頁數(shù);為進行查詢而放入緩存的頁數(shù)。
注意：如果對于一個SQL查詢有多種寫法，那么這四個值中的邏輯讀(logical reads)決定了哪個是***化的。
接下來我們?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 次，預讀0 次。
此時邏輯讀由原來的1變成2，說明我們又加了一個索引頁，現(xiàn)在我們查詢時，邏輯讀就是要讀兩頁(1索引頁+1數(shù)據(jù)頁)，此時的效率還不如不建索引。
聚集索引查找：掃描聚集索引中特定范圍的行，說明，此時用了索引。OK,到這里你應該已經(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 次，預讀0 次。
在加聚集索引的情況下：CREATE CLUSTERED INDEX INDEX_Userid ON T_UserInfo (Userid)，表'T_UserInfo'。掃描計數(shù)1，邏輯讀2 次，物理讀0 次，預讀0 次。(其實也就是說此時是讀了一個索引頁，一個數(shù)據(jù)頁)如此，在數(shù)據(jù)量稍大時，索引的查詢優(yōu)勢就顯示出來了。
總結(jié)：
當你構建SQL語句時，按Ctrl+L就可以看到語句是如何執(zhí)行，是用索引掃描還是表掃描？
通過SET STATISTICS IO ON 來查看邏輯讀，完成同一功能的不同SQL語句，邏輯讀越小查詢速度越快(當然不要找那個只有幾百條記錄的例子來反我)。
我們再繼續(xù)深入：
OK，現(xiàn)在我們再來看一次，我們換個SQL語句，來看下MSSQL如何來執(zhí)行的此SQL呢？
現(xiàn)在去掉索引：DROP INDEX T_UserInfo.INDEX_Userid，現(xiàn)在打開[顯示語句執(zhí)行情況的詳細信息]：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)))
我再加上索引：
先關閉：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)
在有索引的情況下，我們再寫一個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%'))
我們再分別看一下三種情況下對IO的操作
分別如下：
***種情況：表'T_UserInfo'。掃描計數(shù)1，邏輯讀7 次，物理讀0 次，預讀0 次。
第二種情況：表'T_UserInfo'。掃描計數(shù)1，邏輯讀3 次，物理讀0 次，預讀0 次。
第三種情況：表'T_UserInfo'。掃描計數(shù)1，邏輯讀8 次，物理讀0 次，預讀0 次。
這說明:
***次是表掃描，掃了7頁，也就是全表掃描
第二次是索引掃描，掃了1頁索引，2頁數(shù)據(jù)頁
第三次是索引掃描+表掃描，掃了1頁索引，7頁數(shù)據(jù)頁。
[圖形界面也有對CPU和IO的消耗，也可以看出來哪個***!]
通過比較，很容易的看出：第二種第三種寫法在都有索引的情況下，like有效的使用索引，而left則不能，這樣一個最簡單的優(yōu)化的例子就出來了。
在上面的例子中，用的是聚集索引掃描，字段是字母加數(shù)字，大家可以試試看純數(shù)字的、字母的、漢字的等等，了解下SQL Server會如何改變SQL語句來利用索引。然后再試試非聚集索引是什么情況？用不用索引和什么有關？子查詢MSSQL是如何執(zhí)行？IN用不用索引，LIKE用不用索引？函數(shù)用不用索引？OR、AND、UNION？子查詢呢？在這里我不一一去試給大家看了，只要知道了如何去看MSSQL的執(zhí)行計劃（圖形和文本)，很多事情就很明朗了。
總結(jié)：
實現(xiàn)同一查詢功能的SQL寫法可能會有多種，如果判斷哪種***化，如果僅僅是從時間上來測，會受很多外界因素的影響，而我們明白了MSSQL如何去執(zhí)行，通過IO邏輯讀、通過查看圖示的查詢計劃、通過其優(yōu)化后而執(zhí)行的SQL語句，才是優(yōu)化SQL的真正途徑。
另外提醒下：數(shù)據(jù)量的多少有時會影響MSSQL對同一種查詢寫法語句的執(zhí)行計劃，這一點在非聚集索引上特別明顯，還有就是在多CPU與單CPU下，在多用戶并發(fā)情況下，同一寫法的查詢語句執(zhí)行計劃會有所不同，這個就需要大家有機會去試驗。
關于SQL Server執(zhí)行計劃及SQL查詢優(yōu)化的實例分析就介紹到這里了，希望本次的介紹能夠?qū)δ兴斋@！
本文題目：SQLServer的執(zhí)行計劃及SQL查詢優(yōu)化實例分析與總結(jié)
新聞來源：http://www.5511xx.com/article/dpiogip.html
