欧美成人精品手机在线观看_69视频国产_动漫精品第一页_日韩中文字幕网 - 日本欧美一区二区

LOGO OA教程 ERP教程 模切知識(shí)交流 PMS教程 CRM教程 開發(fā)文檔 其他文檔  
 
網(wǎng)站管理員

SQL Server 數(shù)據(jù)庫(kù)執(zhí)行計(jì)劃和索引訪問(wèn)原理

admin
2025年1月9日 22:0 本文熱度 14

執(zhí)行計(jì)劃是數(shù)據(jù)庫(kù)系統(tǒng)為了執(zhí)行SQL查詢而生成的一種指導(dǎo)性的路線圖,它描述了數(shù)據(jù)庫(kù)引擎如何獲取數(shù)據(jù)、操作數(shù)據(jù)以及返回結(jié)果。執(zhí)行計(jì)劃中的統(tǒng)計(jì)信息是指數(shù)據(jù)庫(kù)系統(tǒng)收集和存儲(chǔ)的關(guān)于表、索引、列等對(duì)象的數(shù)據(jù)分布、數(shù)據(jù)量、數(shù)據(jù)分布情況以及數(shù)據(jù)變化情況等信息。這些統(tǒng)計(jì)信息對(duì)于數(shù)據(jù)庫(kù)優(yōu)化和查詢性能的評(píng)估都至關(guān)重要。


原本打算分開兩篇說(shuō)明執(zhí)行計(jì)劃與索引訪問(wèn)原理。當(dāng)前就簡(jiǎn)單說(shuō)明一下,不會(huì)深入執(zhí)行計(jì)劃原理。建議先了解下索引的存儲(chǔ)原理SQL Server 數(shù)據(jù)庫(kù)索引原理


?
執(zhí)行計(jì)劃
?

還是實(shí)踐說(shuō)明更容易了解。

--  drop  table tabcreate table tab(oid int not null,cid int not null,name varchar(50),insert_time datetime)goinsert into tab(oid,cid,name,insert_time)select [object_id],[column_id],[name],dateadd(second,-abs(checksum(newid())),getdate())from sys.all_columnsgoselect count(*) from tab -- 10914 行select * from tab where name = 'fileid'

以上我創(chuàng)建了一張表,并執(zhí)行查詢檢查執(zhí)行計(jì)劃情況。


接下來(lái)就看看統(tǒng)計(jì)信息,sp_helpstats 可參考表有哪些統(tǒng)計(jì)信息,SHOW_STATISTICS 可以查看某個(gè)統(tǒng)計(jì)信息的分布情況。

EXEC sp_helpstats N'[dbo].[tab]', 'ALL'DBCC SHOW_STATISTICS('[dbo].[tab]','_WA_Sys_00000003_3A81B327')

列名說(shuō)明
RANGE_HI_KEY直方圖梯級(jí)的上限列值,列值也稱為鍵值。(按name的范圍分布)
RANGE_ROWS其列值位于直方圖梯級(jí)內(nèi)(不包括上限)的行的估算數(shù)目。(表示2個(gè)name值之間有多少行)
EQ_ROWS其列值等于直方圖梯級(jí)的上限的行的估算數(shù)目。(等于當(dāng)前行name值的有多少行)
DISTINCT_RANGE_ROWS非重復(fù)列值位于直方圖梯級(jí)內(nèi)(不包括上限)的行的估算數(shù)目。(2個(gè)name值之間有多少不重復(fù)的鍵值name)
AVG_RANGE_ROWS重復(fù)列值位于直方圖梯級(jí)內(nèi)(不包括上限)的平均行數(shù)(如果  DISTINCT_RANGE_ROWS > 0,則為 RANGE_ROWS / DISTINCT_RANGE_ROWS)。

在統(tǒng)計(jì)信息里面,數(shù)據(jù)將字段name的值按順序分成200份,每一份包含多個(gè)不同的值,每個(gè)值可能有多行。以上圖統(tǒng)計(jì)信息為例,字段name的值為“FileId”的數(shù)據(jù)有7行,字段name值在范圍大于“file_id”、小于“FileID”的數(shù)據(jù)有31行,去重之后有18行。

因此,當(dāng)系統(tǒng)在估計(jì)查詢計(jì)劃的時(shí)候,會(huì)根據(jù)條件中不同的比較符號(hào),估計(jì)出不同的行數(shù)。如果統(tǒng)計(jì)信息不準(zhǔn)確,那么生成的執(zhí)行計(jì)劃可能就不是最優(yōu)的,會(huì)導(dǎo)致使用更大的代價(jià)。系統(tǒng)會(huì)觸發(fā)統(tǒng)計(jì)信息的更新,但對(duì)于一些大表、變化量大的表來(lái)說(shuō),觸發(fā)更新的閾值也隨之較大,這就要求我們需要定期地更新統(tǒng)計(jì)信息。

在 SQL Server 2016 (13.x) 前

表類型表基數(shù) (n)重新編譯閾值(# 次修改)
臨時(shí)n< 66
臨時(shí)6 <= n<= 500500
永久性n<= 500500
臨時(shí)或永久n> 500500 + (0.20 * n)


自 SQL Server 2016 (13.x) 起

類型表基數(shù) (n)重新編譯閾值(# 次修改)
臨時(shí)n< 66
臨時(shí)6 <= n<= 500500
永久性n<= 500500
臨時(shí)或永久n> 500MIN ( 500 + (0.20 * n), SQRT(1,000 * n) )

保持統(tǒng)計(jì)數(shù)據(jù)最新非常重要,以確保實(shí)際行和估計(jì)行盡可能緊密地對(duì)齊。對(duì)于每次插入、更新和刪除更改數(shù)據(jù),分布都會(huì)發(fā)生變化,并且可能會(huì)扭曲估計(jì)。這些偏差可能會(huì)導(dǎo)致查詢計(jì)劃不夠理想并導(dǎo)致性能下降。設(shè)置每周更新統(tǒng)計(jì)作業(yè)可以幫助他們保持最新狀態(tài)。


?
索引訪問(wèn)原理
?

現(xiàn)在我們創(chuàng)建一個(gè)非聚集索引,創(chuàng)建索引后,相關(guān)的索引統(tǒng)計(jì)信息也會(huì)自動(dòng)生成,與字段name的統(tǒng)計(jì)信息沒(méi)多大差別。

create index idx_tab_name on tab(name)select insert_time from tab where name = 'fileid'

可以看到,查詢使用了該非聚集索引idx_tab_name的索引查找,但是為什么還有嵌套循環(huán)、進(jìn)行 RID Lookup 呢?因?yàn)椴樵兪谦@取所有的字段,但是索引只有字段name、以及執(zhí)行堆表的 RID,通過(guò)RID進(jìn)行了一次回表查詢,將其他字段值全部取出。要了解索引原理,參考文章 XXXXX。

在執(zhí)行計(jì)劃的圖中,你可以點(diǎn)擊相應(yīng)的箭頭,返回的數(shù)據(jù)量越大,箭頭也會(huì)越粗。從上圖可以分析,通過(guò)字段name查找出7行數(shù)據(jù),每行數(shù)據(jù)都回表查詢一次,累計(jì)回表7次。要了解IO讀取情況,參考文章 XXXXX。

現(xiàn)在創(chuàng)建一個(gè)聚集索引,看看執(zhí)行計(jì)劃是什么樣的。

create clustered index idx_tab_oid_cid on tab(oid,cid)select insert_time from tab where name = 'fileid'

執(zhí)行計(jì)劃與“RID Lookup”差別不大。創(chuàng)建聚集索引后,堆表轉(zhuǎn)為聚集索引表。那么非聚集索引中葉節(jié)點(diǎn)存儲(chǔ)的不在是RID,而是聚集索引的鍵列(oid,cid)。在執(zhí)行計(jì)劃中,回表查找則顯示為“Key Lookup”。同樣可以看到,“Key Lookup”的開銷占比85%,在數(shù)據(jù)量較大的時(shí)候,影響會(huì)更加明顯。

那么,應(yīng)該如何優(yōu)化這類查詢呢?可以創(chuàng)建以下一種索引,復(fù)合索引或者包含列索引。

create nonclustered index idx_tab_oname_insert_time1 on tab(name,insert_time)create nonclustered index idx_tab_oname_insert_time2 on tab(name)include(insert_time)

復(fù)合索引相信大家比較好理解,在索引B+Tree結(jié)構(gòu)中,中間的索引節(jié)點(diǎn)會(huì)存在2個(gè)字段的值。而在包含列的索引中,字段insert_time只存在于葉子節(jié)點(diǎn)。也就是在這2個(gè)索引中,insert_time的值都包含在內(nèi)。當(dāng)查詢insert_time時(shí),不需要再回表查詢了。這種優(yōu)勢(shì)可以用在分頁(yè)查詢中。

如果我執(zhí)行以下這個(gè)SQL,執(zhí)行計(jì)劃是怎樣的呢?

select oid,cid,name from tab where name = 'built_substitute'

可以看到只查找了非聚集索引idx_tab_name,這是因?yàn)樵摲蔷奂饕呀?jīng)包含了聚集索引鍵列,不用再回表了。如其中的一個(gè)葉節(jié)點(diǎn)如下。

select oid,cid,name,%%lockres%% as KeyHashValue,sys.fn_physlocformatter(%%physloc%%) as file_page_slotfrom tab with(index(idx_tab_name))
DBCC PAGE(DBName,1,8880,3)


?
訪問(wèn) IO 統(tǒng)計(jì)
?

在 SQLServer 中,成本開銷主要參考CPU開銷與IO開銷,而IO開銷的計(jì)算主要是參考頁(yè)面的讀寫情況。現(xiàn)在我們重新來(lái)過(guò),驗(yàn)證IO的讀取計(jì)算。

?create index idx_tab_name on tab(name)create clustered index idx_tab_oid_cid on tab(oid,cid)
SET STATISTICS IO ONselect insert_time from tab where name = 'fileid'

(7 行受影響)

表“tab”。掃描計(jì)數(shù) 1,邏輯讀取次數(shù) 16,物理讀取次數(shù) 0,頁(yè)面服務(wù)器讀取次數(shù) 0,預(yù)讀讀取次數(shù) 0,頁(yè)面服務(wù)器預(yù)讀讀取次數(shù) 0,LOb 邏輯讀取次數(shù) 0,LOB 邏輯讀取次數(shù) 0,LOB 頁(yè)面服務(wù)器讀取次數(shù) 0,LOB 預(yù)讀讀取次數(shù) 0,LOB 頁(yè)面服務(wù)器預(yù)讀讀取次數(shù) 0。

不管掃描聚集索引還是非聚集索引,掃描次數(shù)只有一次,不要考慮同一張表非聚集索引的嵌套循環(huán)。邏輯讀取次數(shù)為16,說(shuō)明讀取了16個(gè)頁(yè)面,頁(yè)面已經(jīng)緩存中。這16個(gè)頁(yè)面我們也可以猜到引擎是如何讀取的。即先通過(guò)非聚集索引讀取其子葉頁(yè)面,再回表通過(guò)聚集索引讀取其子葉。

非聚集索引idx_tab_name需要訪問(wèn)3個(gè)頁(yè)面,1個(gè)IAM頁(yè)、1個(gè)索引頁(yè)、1個(gè)葉子頁(yè)面。 


DBCC IND(DBName,tab,3) --查看索引頁(yè)有哪些DBCC PAGE(DBName,1,208,3)  --IAM頁(yè)DBCC PAGE(DBName,1,8920,3)  --索引(idx_tab_name)中間頁(yè)DBCC PAGE(DBName,1,8890,3)  --索引(idx_tab_name)葉子頁(yè)

非聚集索引的葉子頁(yè)可以確認(rèn)fileid的數(shù)據(jù)行數(shù)為7行,因?yàn)槲覀儾樵兊氖亲侄蝘nsert_time,在非聚集索引不存在,需要回表查詢。回表就需要確認(rèn)聚集索引鍵列(oid,cid)。我以第一行為例,繼續(xù)查看相關(guān)頁(yè)面。

select insert_time from tab where oid=-337551382 and cid=2DBCC IND(DBName,tab,1)DBCC PAGE(DBName,1,608,3)  --索引(idx_tab_oid_cid)中間頁(yè)DBCC PAGE(DBName,1,602,3)  --索引(idx_tab_oid_cid)葉子頁(yè)

select insert_time,%%lockres%% as KeyHashValue,sys.fn_physlocformatter(%%physloc%%) as file_page_slotfrom tab where oid=-337551382 and cid=2

在聚集索引中,通過(guò)鍵列(oid,cid)查找(-337551382,2)所在葉子頁(yè),需要讀取聚集索引中間索引節(jié)點(diǎn)1個(gè)頁(yè)面,1個(gè)葉子頁(yè)面,也就是2個(gè)頁(yè)面。

  • idx_tab_name: 1個(gè)IAM頁(yè) + 1個(gè)索引頁(yè) + 1個(gè)葉子頁(yè)面(7行數(shù)據(jù))

  • idx_tab_oid_cid: 7*(1個(gè)索引頁(yè) + 1個(gè)葉子頁(yè)面)

總頁(yè)面數(shù)為1 + 1 + 1*7*(1 + 1) = 16 ,即我們最開始 看到的一樣。

為了SQL有效地使用索引,我們應(yīng)盡量獲取必要的字段,不要使用星號(hào)。當(dāng)我們有較多表關(guān)聯(lián)的時(shí)候,條件和關(guān)聯(lián)字段應(yīng)建立相關(guān)索引,盡量減少回表二次查詢。回表查詢開銷是比較大的,尤其字段較多的時(shí)候。數(shù)據(jù)是按行存儲(chǔ)的,當(dāng)我們?nèi)∧匙侄蔚臅r(shí)候,整行數(shù)據(jù)也會(huì)讀取到內(nèi)存中,而行數(shù)據(jù)是存儲(chǔ)在頁(yè)面中的,這也將導(dǎo)致更多的IO讀取。


閱讀原文:原文鏈接


該文章在 2025/1/10 11:05:16 編輯過(guò)
關(guān)鍵字查詢
相關(guān)文章
正在查詢...
點(diǎn)晴ERP是一款針對(duì)中小制造業(yè)的專業(yè)生產(chǎn)管理軟件系統(tǒng),系統(tǒng)成熟度和易用性得到了國(guó)內(nèi)大量中小企業(yè)的青睞。
點(diǎn)晴PMS碼頭管理系統(tǒng)主要針對(duì)港口碼頭集裝箱與散貨日常運(yùn)作、調(diào)度、堆場(chǎng)、車隊(duì)、財(cái)務(wù)費(fèi)用、相關(guān)報(bào)表等業(yè)務(wù)管理,結(jié)合碼頭的業(yè)務(wù)特點(diǎn),圍繞調(diào)度、堆場(chǎng)作業(yè)而開發(fā)的。集技術(shù)的先進(jìn)性、管理的有效性于一體,是物流碼頭及其他港口類企業(yè)的高效ERP管理信息系統(tǒng)。
點(diǎn)晴WMS倉(cāng)儲(chǔ)管理系統(tǒng)提供了貨物產(chǎn)品管理,銷售管理,采購(gòu)管理,倉(cāng)儲(chǔ)管理,倉(cāng)庫(kù)管理,保質(zhì)期管理,貨位管理,庫(kù)位管理,生產(chǎn)管理,WMS管理系統(tǒng),標(biāo)簽打印,條形碼,二維碼管理,批號(hào)管理軟件。
點(diǎn)晴免費(fèi)OA是一款軟件和通用服務(wù)都免費(fèi),不限功能、不限時(shí)間、不限用戶的免費(fèi)OA協(xié)同辦公管理系統(tǒng)。
Copyright 2010-2025 ClickSun All Rights Reserved