數(shù)據(jù)庫引擎是高度優(yōu)化的閉環(huán)系統(tǒng)，基于執(zhí)行計劃的反饋，查詢優(yōu)化器在一定程度上自動優(yōu)化現(xiàn)有的執(zhí)行計劃。查詢優(yōu)化的核心是索引優(yōu)化，數(shù)據(jù)庫引擎通過計數(shù)器統(tǒng)計關(guān)于索引操作的數(shù)據(jù)，統(tǒng)計的信息包括：使用次數(shù)、物理存儲、底層操作的計數(shù)，以及缺失索引等，這些統(tǒng)計數(shù)據(jù)存儲在內(nèi)存中，是數(shù)據(jù)庫引擎執(zhí)行情況的真實反饋，高度概括了索引的執(zhí)行情況，有意識地利用索引的統(tǒng)計信息，有針對性地優(yōu)化現(xiàn)有的業(yè)務(wù)邏輯代碼，調(diào)整查詢的執(zhí)行計劃，能夠提高數(shù)據(jù)庫的查詢性能。

一，統(tǒng)計索引的使用次數(shù)

在用戶成功提交查詢語句時，執(zhí)行計劃中每一個單獨的索引操作（Seek，Scan，Lookup或Update）都會被統(tǒng)計到sys.dm_db_index_usage_stats 中，例如，user_updates 計數(shù)器統(tǒng)計索引執(zhí)行Insert,Update或Delete操作的次數(shù)，查找計數(shù)器（user_seeks, user_scans, user_lookups）統(tǒng)計在索引上執(zhí)行的seek，scan和lookup操作的次數(shù)，如果查找計數(shù)器遠(yuǎn)遠(yuǎn)小于user_updates 計數(shù)器，這說明基礎(chǔ)表會執(zhí)行大量的更新操作，維護(hù)索引更新的開銷比較大，數(shù)據(jù)庫引擎利用索引提升查詢性能的空間有限。 

在計數(shù)時，每一個單獨的seek、scan、lookup或update操作都被計算為對該索引的一次使用，并使該視圖中的相應(yīng)計數(shù)器加1。

索引的Seek，Scan，Lookup和Update的含義是：

  • Seek是Index Seek：通過該索引進(jìn)行查找的次數(shù)

  • Scan是Index Scan：通過該索引執(zhí)行掃描查找的次數(shù)

  • Lookup是Key Lookup：通過該索引查找到數(shù)據(jù)后，再到源數(shù)據(jù)表進(jìn)行鍵值查找的次數(shù)，Key Lookup是非聚集索引特有的，查詢性能低下，應(yīng)避免這種查找方法；

  • Update是Index Update：由于源表數(shù)據(jù)更新導(dǎo)致索引頁更新的次數(shù)

Index Seek和Index Scan的區(qū)別是：

  • Index Seek是從BTree的根節(jié)點開始，向子節(jié)點查找，直到葉子節(jié)點；

  • Index Scan是在Index的葉子節(jié)點上，從左到右，把整個BTree的葉子節(jié)點遍歷一遍，類似于Table Scan。

如果索引的Seek，Scan，Lookup的計數(shù)值較多，那么說明索引被引用的次數(shù)多；如果查找計數(shù)器數(shù)值較小，但是Update數(shù)值較多，說明維護(hù)Index的開銷高于查詢帶來的性能提升，應(yīng)該考慮修改索引的結(jié)構(gòu)，或者直接把索引刪除。

iOS培訓(xùn),Swift培訓(xùn),蘋果開發(fā)培訓(xùn),移動開發(fā)培訓(xùn) View Code

二，統(tǒng)計索引的物理存儲

使用 sys.dm_db_index_physical_stats 函數(shù)統(tǒng)計索引的物理存儲，例如，碎片的百分比，數(shù)據(jù)存儲的集中和分散程度，以及page空間的利用率等：

  • avg_fragmentation_in_percent：索引外部碎片的百分比，值越大，說明索引的邏輯順序和物理順序差異越大，查找性能越低；

  • fragment_count：分段的數(shù)量，表示索引數(shù)據(jù)的集中/分散程度；

  • avg_fragment_size_in_pages：分段的大小

  • avg_page_space_used_in_percent：索引內(nèi)部碎片的百分比，值越大，說明page空間的利用率越高；

請閱讀《索引碎片的檢測和整理》，以了解更多。

三，底層操作的計數(shù)

使用 sys.dm_db_index_operational_stats 函數(shù)統(tǒng)計底層IO、加鎖（Locking）、Latch和數(shù)據(jù)訪問模式的計數(shù)，通過這些數(shù)據(jù)，用戶能夠追蹤到查詢請求必須等待多長時間才能完成數(shù)據(jù)的讀寫、標(biāo)識索引是否存在IO熱點。

在統(tǒng)計索引的底層操作之前，先了解跟數(shù)據(jù)的物理存儲相關(guān)的術(shù)語：

  • 幽靈數(shù)據(jù)(ghost)是指：在索引的葉子節(jié)點中，數(shù)據(jù)行被標(biāo)記為刪除，但是還沒有從索引結(jié)構(gòu)中物理刪除，幽靈數(shù)據(jù)只存在于索引的葉子節(jié)點中，幽靈數(shù)據(jù)由后臺進(jìn)程定期執(zhí)行物理刪除。

  • 轉(zhuǎn)發(fā)數(shù)據(jù)（forwarding）：需要兩次IO操作才能獲取到指定的數(shù)據(jù)，轉(zhuǎn)發(fā)操作只發(fā)生于堆表（Heap）中；當(dāng)數(shù)據(jù)行被更新，導(dǎo)致行的Size增大，以致于該行無法存儲在當(dāng)前的page中，為了避免相關(guān)索引的更新，數(shù)據(jù)庫引擎會把該數(shù)據(jù)行轉(zhuǎn)存到一個新的Page中，并在新舊 Page中分別添加一個Pointer：在原Page中，Pointer指向新Page，該Pointer稱作Forwarder Pointer；在新page中，Pointer指向原Page，稱作Back Pointer。在讀取數(shù)據(jù)時，數(shù)據(jù)庫引擎首先從Forwarder Pointer中讀取數(shù)據(jù)存儲的指針，然后，根據(jù)指針到相應(yīng)的地址空間中讀取真正的數(shù)據(jù)。

  • 獲取（Fetch）數(shù)據(jù)：用于從LOB或Row_Overflow的分配單元（Allocation Unit）中取回（Retrive）數(shù)據(jù)，大字段數(shù)據(jù)存儲在特定的LOB或Row_Overflow類型的數(shù)據(jù)頁中。

  • 剝離（Push Off）數(shù)據(jù)列：用于統(tǒng)計數(shù)據(jù)庫引擎把LOB或Row-Overflow數(shù)據(jù)從原有的In-Row 數(shù)據(jù)頁剝離的次數(shù)。在執(zhí)行Insert或Update操作之后，數(shù)據(jù)行的Size增長，不能存儲在當(dāng)前的Page中，必須把大數(shù)據(jù)字段的數(shù)據(jù)從原來的數(shù)據(jù)行中分離，存儲在指定的分配單元中，這個過程就是數(shù)據(jù)列的剝離。

  • 拉回（Pull In）數(shù)據(jù)行：是Push Off的逆過程，用于統(tǒng)計數(shù)據(jù)庫引擎把數(shù)據(jù)從LOB或Row-Overflow數(shù)據(jù)頁拉入到In-Row數(shù)據(jù)頁的次數(shù)，拉入數(shù)據(jù)行一般發(fā)生在更新數(shù)據(jù)之后，數(shù)據(jù)行的Size減小，數(shù)據(jù)行在釋放存儲空間之后，能夠存儲在In-Row Page中，數(shù)據(jù)引擎把數(shù)據(jù)從LOB或Row-Overflow數(shù)據(jù)頁拉入到In-Row數(shù)據(jù)頁，這個過程是數(shù)據(jù)列的拉回。

This (pulled in-row) occurs when an update operation frees up space in a record and provides an opportunity to pull in one or more off-row values from the LOB_DATA or ROW_OVERFLOW_DATA allocation units to the IN_ROW_DATA allocation unit.

以下腳本用于統(tǒng)計索引底層的存儲動作和鎖/Latch的爭用：

iOS培訓(xùn),Swift培訓(xùn),蘋果開發(fā)培訓(xùn),移動開發(fā)培訓(xùn) View Code

該函數(shù)統(tǒng)計的Latch征用數(shù)據(jù)主要分為PageLatch和PageIOLatch，其區(qū)別是：

  • PageLatch是指：在訪問數(shù)據(jù)有關(guān)的數(shù)據(jù)頁（Data Page或Index Page）時，如果相應(yīng)的Page已經(jīng)存在于Buffer Pool中，那么SQL Server先獲取buffer的latch，這個Latch就是 PageLatch，然后讀取Buffer中的數(shù)據(jù)。

    PageLatch是施加在Buffer上的Latch， 用來保護(hù)：Data page，Index Page, 系統(tǒng)page（PFS,GAM,SGAM,IAM等）的爭用訪問；在數(shù)據(jù)更新時，分配新的page，或拆分 索引頁（Index Page），會產(chǎn)生PageLatch 等待。

  • PageIOLatch是指：用于把數(shù)據(jù)從索引或Heap中加載到內(nèi)存。當(dāng)數(shù)據(jù)頁從物理文件中的Page中讀取到內(nèi)存時，申請對內(nèi)存Buffer施加的Latch是PageIOLatch。當(dāng)數(shù)據(jù)頁不在內(nèi)存里時，SQL Server 先在內(nèi)存中預(yù)留一個Page，然后從硬盤讀取，加載到內(nèi)存Buffer中，此時，SQL Server申請并獲取的latch類型是PAGEIOLATCH，PageIOLatch表示正在進(jìn)行IO操作。PageIOLatch_EX表示正在將disk中的數(shù)據(jù)頁加載到內(nèi)存，PageIOLatch_SH表示在加載數(shù)據(jù)頁到內(nèi)存期間，試圖讀取內(nèi)存中的數(shù)據(jù)頁，此時加載數(shù)據(jù)頁的過程沒有完成，處于Loading狀態(tài)。如果經(jīng)常出現(xiàn)PageIOLatch_SH，表明Loading數(shù)據(jù)頁的時間太長，可能出現(xiàn)IO bottleneck。

分析查詢結(jié)果，根據(jù)計數(shù)器的數(shù)值，調(diào)整數(shù)據(jù)庫，使系統(tǒng)達(dá)到最優(yōu)狀態(tài)：

  • 如果發(fā)現(xiàn)字段leaf_ghost_count的數(shù)值特別大，說明索引中存儲很多幽靈數(shù)據(jù)，可以通過重建索引（Rebuild）清理幽靈數(shù)據(jù)行：

alter index index_nameon table_name
rebuild

  • 如果PageIOLatch等待較多，說明數(shù)據(jù)庫頻繁的執(zhí)行硬盤IO操作，可能的原因是內(nèi)存不足，或者數(shù)據(jù)文件沒有分散到多個物理硬盤上

  • 如果PageLatch等待較多，說明數(shù)據(jù)庫存在IO熱點，可以通過增加數(shù)據(jù)文件ndf，把數(shù)據(jù)庫分散到不同的物理硬盤上，以減少IO熱點

四，缺失索引

查詢優(yōu)化器（Query Optimizer）在執(zhí)行查詢時，如果檢測到執(zhí)行計劃缺失索引，會把缺失索引的相關(guān)信息存儲在緩存中，通過  sys.dm_db_missing_index_details 可以檢測查詢優(yōu)化器建議創(chuàng)建的缺失索引。

該視圖返回的缺失索引的索引鍵及包含列信息，在索引列的順序上，相等列（equality）應(yīng)該排在不等列（inequality）之前，包含列（Included）應(yīng)該添加到INCLUDE子句中，但是，該視圖不會標(biāo)識出相等列（equality）的排列順序，需要根據(jù)查詢語句和選擇性來設(shè)置，索引鍵的第一列至關(guān)重要。

iOS培訓(xùn),Swift培訓(xùn),蘋果開發(fā)培訓(xùn),移動開發(fā)培訓(xùn) View Code

 

參考文檔：

An in-depth look at Ghost Records in SQL Server

Index Related Dynamic Management Views and Functions (Transact-SQL)

http://www.cnblogs.com/ljhdo/p/4491911.html