SQLSERVER調優–索引片段

文章目錄

• Clustered Indexes

首先介紹些索引片段相關概念、及檢查和整理的方法：

索引片段兩種類型

一 外部片段

產生的原因是因為索引不按照的邏輯順序排列

比如 現在的索引頁分配順序是

第一頁 第二頁 第三頁

資料：2 4 6 8 10 12 14 16 18 20 22 24

當我們插入新的資料比如5，系統可能就會這樣分配，產生一個新的索引頁

第一頁 第二頁 第三頁 第四頁

資料： 2 4 5 10 12 14 16 18 20 22 24 6 8

這時如果我們要查詢4－10的資料，就需要一個額外的頁來返回6，8兩個資料

二 內部索引片段

產生的原因是因為索引頁沒有充分利用到所分配的空間，內部索引片段會導致增加索引空間

三 我們可以使用DBCC SHOWCONTIG來檢查索引片段

用法：

DBCC SHOWCONTIG --詳細用法參看sqlserver聯機文檔

樣本：

declare @table_id int
set @table_id=object_id('TA066')
dbcc showcontig(@table_id)

四 磁碟重組(重建索引)

邏輯掃描片段和擴充盤區掃描片段都非常大，需要對索引片段進行處理

一般有兩種方法解決，一是利用DBCC INDEXDEFRAG整理索引片段，二是利用DBCC DBREINDEX重建索引。二者各有優缺點。微軟文檔解釋：

DBCC INDEXDEFRAG 命令是聯機操作，所以索引只有在該命令正在運行時才可用。而且可以在不丟失已完成工作的情況下中斷該操作。這種方法的缺點是在重新組織資料方面沒有叢集索引的除去/重新建立操作有效。

重新建立叢集索引將對資料進行重新組織，其結果是使資料頁填滿。填滿程度可以使用 FILLFACTOR 選項進行配置。這種方法的缺點是索引在除去/重新建立周期內為離線狀態，並且操作屬原子級。如果中斷索引建立，則不會重新建立該索引。

也就是說，要想獲得好的效果，還是得用重建索引，所以決定重建索引。
DBCC DBREINDEX(表，索引名，填滿因數）
第一個參數，可以是表名，也可以是表ID。
第二個參數，如果是''，表示影響該表的所有索引。
第三個參數，填滿因數，即索引頁的資料填充程度。如果是100，表示每一個索引頁都全部填滿，此時select效率最高，但以後要插入索引時，就得移動竺嫻乃幸常屎艿汀Ｈ綣?，表示使用先前的填滿因數值。

DBCC DBREINDEX(A，''，90)

五 本次案例分析：

近期調整一個SQL，耗時6秒，發現索引片段非常多，重建索引

ddeclare @table_id int
set @table_id=object_id('TC005')
dbcc showcontig(@table_id)

DBCC SHOWCONTIG scanning 'TC005' table...
Table: 'TC005' (1453352342); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 63766
- Extents Scanned..............................: 12031
- Extent Switches..............................: 63371
- Avg. Pages per Extent........................: 5.3
- Scan Density [Best Count:Actual Count].......: 12.58% [7971:63372] --該值接近100%為好
- Logical Scan Fragmentation ..................: 50.35% --該值接近0為好
- Extent Scan Fragmentation ...................: 92.39% --該值接近0為好
- Avg. Bytes Free per Page.....................: 6048.2
- Avg. Page Density (full).....................: 25.28%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

dbcc dbreindex('newjoyo2.dbo.TC005','',90)

再次檢查：

declare @table_id int
set @table_id=object_id('TC005')
dbcc showcontig(@table_id)

DBCC SHOWCONTIG scanning 'TC005' table...
Table: 'TC005' (1453352342); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 17678
- Extents Scanned..............................: 2232
- Extent Switches..............................: 2231
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.01% [2210:2232] --已經接近100%
- Logical Scan Fragmentation ..................: 0.04%
- Extent Scan Fragmentation ...................: 0.40%
- Avg. Bytes Free per Page.....................: 707.6
- Avg. Page Density (full).....................: 91.26%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

再次TRACE跟蹤了一下，發現CPU時間基本上在100毫秒以內：

樣本資料如下：

TraceStart CPU READS WRITES DURATION CLIENT_PROCESSID SPID 2007-10-10 07:40:30.267
SQL:BatchCompleted Internet Information Services joyo2 62 21474 0 63 3784 286 2007-10-10 07:41:07.173
SQL:BatchCompleted Internet Information Services joyo2 62 21474 0 60 7496 233 2007-10-10 07:41:10.910
SQL:BatchCompleted Internet Information Services joyo2 46 478 0 46 4992 258 2007-10-10 07:41:19.847
SQL:BatchCompleted Internet Information Services joyo2 47 959 0 46 7496 233 2007-10-10 07:41:21.800
SQL:BatchCompleted Internet Information Services joyo2 0 128 0 0 3784 286 2007-10-10 07:41:42.503
SQL:BatchCompleted Internet Information Services joyo2 0 176 0 0 7496 233 2007-10-10 07:42:03.313
SQL:BatchCompleted Internet Information Services joyo2 0 525 0 0 3784 286 2007-10-10 07:42:20.597
SQL:BatchCompleted Internet Information Services joyo2 47 3264 0 60 4992 222 2007-10-10 07:42:21.723
SQL:BatchCompleted Internet Information Services joyo2 79 7871 0 76 3784 286 2007-10-10 07:42:47.783

效果仍然不理想，最後檢查SQL，發現2個千萬級大表關聯沒有用到CLUSTERED INDEX。該SQL已經無法再繼續最佳化了，整個SQL大部分時間消耗在了BOOKMARK LOOKUP上。

結論：

對於BOOKMARK LOOKUP這個計劃步驟產生的原因是由於沒有使用CLUSTERED索引，而無法避免。

SQLSERVER索引有2大類，聚簇索引和非聚簇索引2種：聚簇索引和表是一體的(類似ORACLE的唯索引)，而非聚簇索引節點儲存了索引本身並且儲存了聚簇索引的相關資訊，尋找時先找到對應的聚簇索引的值再去尋找。

一個表只能有一個聚簇索引（資料的儲存按照該所引得順序實體儲存體，非常寶貴），所以建議設計時候要全面考慮以後的查詢等（尤其是業務系統的大表），不建議用IDNETITY欄位作聚簇索引。這個也是微軟強烈建議的。

索引尋找的效率應該是：

索引覆蓋(當然這個有時候我們無法完全做到) > CLUSTERED INDEX SEEK > INDEX SEEK

尋找了一下SQLSERVER文檔解釋：

Clustered Indexes

Clustered indexes have one row in sysindexes with indid = 1. The pages in the data chain and the rows in them are ordered on the value of the clustered index key. All inserts are made at the point the key value in the inserted row fits in the ordering sequence.

Microsoft SQL Server 2000 indexes are organized as B-trees. Each page in an index holds a page header followed by index rows. Each index row contains a key value and a pointer to either a lower-level page or a data row. Each page in an index is called an index node. The top node of the B-tree is called the root node. The bottom layer of nodes in the index are called the leaf nodes. The pages in each level of the index are linked together in a doubly-linked list. In a clustered index, the data pages make up the leaf nodes. Any index levels between the root and the leaves are collectively known as intermediate levels.

For a clustered index, sysindexes.root points to the top of the clustered index. SQL Server navigates down the index to find the row corresponding to a clustered index key. To find a range of keys, SQL Server navigates through the index to find the starting key value in the range, and then scans through the data pages using the previous or next pointers. To find the first page in the chain of data pages, SQL Server follows the leftmost pointers from the root node of the index.

This illustration shows the structure of a clustered index.

最後再總結一下：

聚簇索引和表是一體的，表述據順序是有序的（物理有序），所以聚簇索引對於尋找非常有效，尤其是查詢連續返回結果集比較大的情況，直接順序返回大量值（如果用非聚簇索引可以想象有多麼多的IO）。一個表如果沒有聚簇索引，表內資料是無序的，稱之為堆儲存（就是一大堆資料，無序）。如果建立非聚簇索引(和表完全獨立的結構)，從索引行指向資料的稱為行定位器，行定位器的結構取決於資料頁的儲存方式是堆集還是聚集。對於堆集，行定位器是指向行的指標；對於聚集，行定位器是叢集索引索引值。所以在聚集表中的非叢集索引會比在堆集表中的要慢。頻繁刪除修改會造成聚集表很多片段text column會影響聚集表效能SQLSERVE視圖的一個索引預設識叢集索引，所以要謹慎考慮這個索引，沒有必要我們可以不去建立。整理該文檔時參考了以下地址和SQLSERVER聯機文檔：http://topic.csdn.net/t/20051201/11/4430562.html