亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频

引用:初探Sql Server 執行計劃及Sql查詢優化

系統 1902 0
原文: 引用:初探Sql Server 執行計劃及Sql查詢優化

初探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 看下此時的圖行執行計劃:

引用:初探Sql Server 執行計劃及Sql查詢優化

?

我再加上索引:

先關閉: 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 Server 執行計劃及Sql查詢優化 ?

?

在有索引的情況下,我們再寫一個 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 看下此時的圖行執行計劃:

引用:初探Sql Server 執行計劃及Sql查詢優化 ?

?

我們再分別看一下三種情況下對 IO 的操作

分別如下:

第一種情況:表 'T_UserInfo' 。掃描計數 1 ,邏輯讀 7? 次,物理讀 0? 次,預讀 0? 次。

第二種情況:表 'T_UserInfo' 。掃描計數 1 ,邏輯讀 3? 次,物理讀 0? 次,預讀 0? 次。

第三種情況:表 'T_UserInfo' 。掃描計數 1 ,邏輯讀 8? 次,物理讀 0? 次,預讀 0? 次。

這說明 :

第一次是表掃描,掃了 7 頁,也就是全表掃描

第二次是索引掃描,掃了 1 頁索引, 2 頁數據頁

第三次是索引掃描 + 表掃描,掃了 1 頁索引, 7 頁數據頁

[圖形界面也有對CPU和IO的消耗,也可以看出來哪個最優!]?

?

通過比較,嘿嘿,很容易的看出:第二種第三種寫法在都有索引的情況下, like 有效的使用索引,而 left 則不能,這樣一個最簡單的優化的例子就出來了,哈哈。

?

? 如果以上你都明白了,那么你可能已經對 SQL 的優化有初步新的想法了,網上一堆堆的 SQL 優化的文章真的是那樣嗎?你自己試試就知道了,而不必盲目去記那些東西,自己試試,看看 MSSQL 到底是怎么來執行就明白了。

在我舉的例子中,用的是聚集索引掃描,字段是字母加數字,大家可以試試看純數字的、字母的、漢字的等等,了解下 MMSQL 會如何改變 SQL 語句來利用索引。然后再試試非聚集索引是什么情況?用不用索引和什么有關?子查詢 MSSQL 是如何執行? IN 用不用索引, LIKE 用不用索引?函數用不用索引? OR AND 、 UNION ?子查詢呢?在這里我不一一去試給大家看了,只要知道了如何去看 MSSQL 的執行計劃(圖形和文本 ) ,很多事情就很明朗了。

?

大總結:

實現同一查詢功能的 SQL 寫法可能會有多種,如果判斷哪種最優化,如果僅僅是從時間上來測,會受很多外界因素的影響,而我們明白了 MSSQL 如何去執行,通過 IO 邏輯讀、通過查看圖示的查詢計劃、通過其優化后而執行的 SQL 語句,才是優化 SQL 的真正途徑。

?

另外提醒下:數據量的多少有時會影響 MSSQL 對同一種查詢寫法語句的執行計劃,這一點在非聚集索引上特別明顯,還有就是在多 CPU 與單 CPU 下,在多用戶并發情況下,同一寫法的查詢語句執行計劃會有所不同,這個就需要大家有機會去試驗了 ( 我也沒有這方面的太多經驗與大家分享 ) 。

?

先寫這些吧,由于我對 MSSQL 認識還很淺薄,如有不對的地方,還請指正。

引用:初探Sql Server 執行計劃及Sql查詢優化


更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索:z360901061

微信掃一掃加我為好友

QQ號聯系: 360901061

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

【本文對您有幫助就好】

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧,站長會非常 感謝您的哦?。。?/p>

發表我的評論
最新評論 總共0條評論
主站蜘蛛池模板: 久久久噜噜噜 | 欧美色视频超清在线观看 | 中文字幕亚洲综合久久男男 | 午夜在线播放 | 婷婷的久久五月综合先锋影音 | 欧美成人性做爰网站免费 | 亚洲资源站资源网在线 | 亚洲欧美不卡中文字幕 | 97国产在线播放 | 亚洲国产美女精品久久 | 大香伊人久久 | 91av综合 | 欧美视频亚洲视频 | 不卡欧美| 久久伊人免费 | 免费精品美女久久久久久久久久 | 天天做天天爱夜夜爽女人爽宅 | 99在线国产视频 | 亚洲日比视频 | 97夜夜澡人人爽人人免费 | 在线精品国产第一页 | 亚洲小说春色综合另类网蜜桃 | 免费人成黄页网站在线观看 | 九天玄帝诀在线观看 | 热久久久久久久 | 美国免费三片在线观看 | 欧美性猛交xxxx免费看手交 | 精品久久久久久久久久香蕉 | 美女毛片免费 | 欧美日韩不卡码一区二区三区 | 婷婷综合激情网 | 99久久综合久中文字幕 | 97免费在线观看视频 | 日韩欧美在线观看视频 | 四虎自拍 | 久久精品国产一区 | 精品亚洲一区二区 | 亚洲欧美视频二区 | 国产精品福利一区二区 | 九九网| 日狠狠 |