人妻夜夜爽天天爽三区丁香花-人妻夜夜爽天天爽三-人妻夜夜爽天天爽欧美色院-人妻夜夜爽天天爽免费视频-人妻夜夜爽天天爽-人妻夜夜爽天天

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

原本打算分開兩篇說明執(zhí)行計劃與索引訪問原理。當前就簡單說明一下，不會深入執(zhí)行計劃原理。建議先了解下索引的存儲原理SQL Server 數(shù)據(jù)庫索引原理

還是實踐說明更容易了解。

--  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í)行計劃情況。

接下來就看看統(tǒng)計信息，sp_helpstats 可參考表有哪些統(tǒng)計信息，SHOW_STATISTICS 可以查看某個統(tǒng)計信息的分布情況。

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

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

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

在 SQL Server 2016 (13.x) 前

自 SQL Server 2016 (13.x) 起

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

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

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

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

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

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

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

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

那么，應(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é)點會存在2個字段的值。而在包含列的索引中，字段insert_time只存在于葉子節(jié)點。也就是在這2個索引中，insert_time的值都包含在內(nèi)。當查詢insert_time時，不需要再回表查詢了。這種優(yōu)勢可以用在分頁查詢中。

如果我執(zhí)行以下這個SQL，執(zhí)行計劃是怎樣的呢？

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

可以看到只查找了非聚集索引idx_tab_name，這是因為該非聚集索引已經(jīng)包含了聚集索引鍵列，不用再回表了。如其中的一個葉節(jié)點如下。

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)

在 SQLServer 中，成本開銷主要參考CPU開銷與IO開銷，而IO開銷的計算主要是參考頁面的讀寫情況?，F(xiàn)在我們重新來過，驗證IO的讀取計算。

?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”。掃描計數(shù) 1，邏輯讀取次數(shù) 16，物理讀取次數(shù) 0，頁面服務(wù)器讀取次數(shù) 0，預(yù)讀讀取次數(shù) 0，頁面服務(wù)器預(yù)讀讀取次數(shù) 0，LOb 邏輯讀取次數(shù) 0，LOB 邏輯讀取次數(shù) 0，LOB 頁面服務(wù)器讀取次數(shù) 0，LOB 預(yù)讀讀取次數(shù) 0，LOB 頁面服務(wù)器預(yù)讀讀取次數(shù) 0。

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

非聚集索引idx_tab_name需要訪問3個頁面，1個IAM頁、1個索引頁、1個葉子頁面。 

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

非聚集索引的葉子頁可以確認fileid的數(shù)據(jù)行數(shù)為7行，因為我們查詢的是字段insert_time，在非聚集索引不存在，需要回表查詢?；乇砭托枰_認聚集索引鍵列(oid,cid)。我以第一行為例，繼續(xù)查看相關(guān)頁面。

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)中間頁DBCC PAGE(DBName,1,602,3)  --索引(idx_tab_oid_cid)葉子頁

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

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

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

• idx_tab_oid_cid: 7*（1個索引頁 + 1個葉子頁面）

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

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

閱讀原文：原文鏈接