初探Sql Server 執行計劃及Sql查詢優化? 收藏
MSSQL
優化之
————
探索
MSSQL
執行計劃
作者: no_mIss
?
最近總想整理下對
MSSQL
的一些理解與感悟,卻一直沒有心思和時間寫
,晚上無事便寫了一篇探索
MSSQL
執行計劃,本文講執行計劃但不僅限于講執行計劃。
網上的
SQL
優化的文章實在是很多,說實在的,我也曾經到處找這樣的文章,什么不要使用
IN
了,什么
OR
了,什么
AND
了,很多很多,還有很多人拿出僅幾
S
甚至幾
MS
的時間差的例子來證明著什么
(
有點可笑
)
,讓許多人不知道其是對還是錯。而
SQL
優化又是每個要與數據庫打交道的程序員的必修課,所以寫了此文,與朋友們共勉。
談到優化就必然要涉及索引,就像要講鎖必然要說事務一樣,所以你需要了解一下索引,僅僅是索引,就能講半天了,所以索引我就不說了
(
打很多字是很累的,況且我也知之甚少
)
,可以去參考相關的文章,這個網上資料比較多了。
今天來探索下
MSSQL
的執行計劃,來讓大家知道如何查看
MSSQL
的優化機制,以此來優化
SQL
查詢。
--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
,如下圖
:
?
聚集索引查找:掃描聚集索引中特定范圍的行
說明,此時用了索引。
OK
,
到這里你應該已經知道初步知道
MSSQL
查詢計劃和如何查看對
IO
的讀取消耗了吧!
接下來我們繼續:
現在我再把測試數據改變成
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
看下此時的圖行執行計劃:
?
我們再分別看一下三種情況下對
IO
的操作
分別如下:
第一種情況:表
'T_UserInfo'
。掃描計數
1
,邏輯讀
7?
次,物理讀
0?
次,預讀
0?
次。
第二種情況:表
'T_UserInfo'
。掃描計數
1
,邏輯讀
3?
次,物理讀
0?
次,預讀
0?
次。
第三種情況:表
'T_UserInfo'
。掃描計數
1
,邏輯讀
8?
次,物理讀
0?
次,預讀
0?
次。
這說明
:
第一次是表掃描,掃了
7
頁,也就是全表掃描
第二次是索引掃描,掃了
1
頁索引,
2
頁數據頁
第三次是索引掃描
+
表掃描,掃了
1
頁索引,
7
頁數據頁
通過比較,嘿嘿,很容易的看出:第二種第三種寫法在都有索引的情況下,
like
有效的使用索引,而
left
則不能,這樣一個最簡單的優化的例子就出來了,哈哈。
在我舉的例子中,用的是聚集索引掃描,字段是字母加數字,大家可以試試看純數字的、字母的、漢字的等等,了解下
MMSQL
會如何改變
SQL
語句來利用索引。然后再試試非聚集索引是什么情況?用不用索引和什么有關?子查詢
MSSQL
是如何執行?
IN
用不用索引,
LIKE
用不用索引?函數用不用索引?
OR
、
AND
、
UNION
?子查詢呢?在這里我不一一去試給大家看了,只要知道了如何去看
MSSQL
的執行計劃(圖形和文本
)
,很多事情就很明朗了。
大總結:
實現同一查詢功能的
SQL
寫法可能會有多種,如果判斷哪種最優化,如果僅僅是從時間上來測,會受很多外界因素的影響,而我們明白了
MSSQL
如何去執行,通過
IO
邏輯讀、通過查看圖示的查詢計劃、通過其優化后而執行的
SQL
語句,才是優化
SQL
的真正途徑。
另外提醒下:數據量的多少有時會影響
MSSQL
對同一種查詢寫法語句的執行計劃,這一點在非聚集索引上特別明顯,還有就是在多
CPU
與單
CPU
下,在多用戶并發情況下,同一寫法的查詢語句執行計劃會有所不同,這個就需要大家有機會去試驗了
(
我也沒有這方面的太多經驗與大家分享
)
。
先寫這些吧,由于我對 MSSQL 認識還很淺薄,如有不對的地方,還請指正。
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
