SQL Server 數(shù)據(jù)庫(kù)執(zhí)行計(jì)劃和索引訪問(wèn)原理
當(dāng)前位置:點(diǎn)晴教程→知識(shí)管理交流
→『 技術(shù)文檔交流 』
執(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ù)索引原理 還是實(shí)踐說(shuō)明更容易了解。
以上我創(chuàng)建了一張表,并執(zhí)行查詢檢查執(zhí)行計(jì)劃情況。 接下來(lái)就看看統(tǒng)計(jì)信息,sp_helpstats 可參考表有哪些統(tǒng)計(jì)信息,SHOW_STATISTICS 可以查看某個(gè)統(tǒng)計(jì)信息的分布情況。
在統(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) 前
自 SQL Server 2016 (13.x) 起
保持統(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)。 現(xiàn)在我們創(chuàng)建一個(gè)非聚集索引,創(chuàng)建索引后,相關(guān)的索引統(tǒng)計(jì)信息也會(huì)自動(dòng)生成,與字段name的統(tǒng)計(jì)信息沒(méi)多大差別。
可以看到,查詢使用了該非聚集索引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ì)劃是什么樣的。
執(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ù)合索引或者包含列索引。
復(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ì)劃是怎樣的呢?
可以看到只查找了非聚集索引idx_tab_name,這是因?yàn)樵摲蔷奂饕呀?jīng)包含了聚集索引鍵列,不用再回表了。如其中的一個(gè)葉節(jié)點(diǎn)如下。
在 SQLServer 中,成本開銷主要參考CPU開銷與IO開銷,而IO開銷的計(jì)算主要是參考頁(yè)面的讀寫情況。現(xiàn)在我們重新來(lái)過(guò),驗(yàn)證IO的讀取計(jì)算。
(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è)面。
非聚集索引的葉子頁(yè)可以確認(rèn)fileid的數(shù)據(jù)行數(shù)為7行,因?yàn)槲覀儾樵兊氖亲侄蝘nsert_time,在非聚集索引不存在,需要回表查詢。回表就需要確認(rèn)聚集索引鍵列(oid,cid)。我以第一行為例,繼續(xù)查看相關(guān)頁(yè)面。
在聚集索引中,通過(guò)鍵列(oid,cid)查找(-337551382,2)所在葉子頁(yè),需要讀取聚集索引中間索引節(jié)點(diǎn)1個(gè)頁(yè)面,1個(gè)葉子頁(yè)面,也就是2個(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)文章
正在查詢... |