--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，如下圖:
現在我再把測試數據改變成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看下此時的圖行執行計劃：
