SQLServer覆蓋索引

標籤：style   blog   http   ar   color   os   使用   sp   on   

　　為了更好地理解覆蓋索引，在正式介紹覆蓋索引之前，首先稍微來談一談有關索引的一些基礎知識。

資料頁和索引頁

　　在SQLServer中，資料存放區的基本單位是頁，一頁的大小為8KB，分別由頁首，資料行和行位移量組成，如結構：

　　

　　頁首固定佔用96個位元組，用來儲存相關的頁面系統資訊，例如所屬的資料庫表對象Id等。資料行是真實資料的儲存地區，每一行的大小是不固定的。行位移量是一個數組，數組的每個位置佔2個位元組，用來儲存資料行距離開頭的位置位移量，主要是用來做快速定位，例如想要尋找第N行，只要訪問行位移量數組的第N項，就能快速找到資料行所在的位置。索引頁和資料頁的結構類似，所不同的是索引頁的資料行儲存的是和索引相關的資訊。

叢集索引和非叢集索引

　　叢集索引定義了表中資料存放區的真實物理位置，它是按照指定列的順序來儲存資料的，類比於新華字典中的漢字是按照拼音順序排列的，所以每張表只能建立一個叢集索引。叢集索引是一棵B+樹結構，包含索引頁和資料頁，最底下的一排葉子節點是資料頁，往上則為索引頁，來看一張圖應該更清晰一些：

　 

　　非叢集索引是獨立於資料真實儲存順序邏輯而存在的，類比於新華字典中按偏旁部首尋找漢字的方式。與叢集索引對比，非叢集索引也是B+樹的資料結構，但卻只包含索引頁，而且在一張表中可以建立多個非叢集索引，有關索引的深入分析可以查看這篇文章。同樣來看一張非叢集索引的圖：

　　

什麼是覆蓋索引

　　覆蓋索引是在SQLServer2005中引入的概念，只能建立在非叢集索引的基礎上，通常情況下，非叢集索引的索引頁是不包含真實資料的，只儲存著指向資料頁中資料行的指標，而覆蓋索引則是通過將資料存放區在索引頁上，從而在尋找對應資料的時候，只要找到索引頁就可以訪問到資料，無需再去查詢資料頁，所以說這個索引是資料“覆蓋”的。

--覆蓋索引的建立是在非叢集索引建立的基礎上增加INCLUDE語句CREATE NONCLUSTERED INDEX {index_name}ON {table_name}(column_name...) --非叢集索引可以聲明指定多個列作為索引項目INCLUDE(column_name...)         --覆蓋索引可以指定多個列儲存在索引頁上

建立覆蓋索引的文法覆蓋索引分析

　　這一小節將通過建立覆蓋索引以及使用DBCC命令查看索引的方式進行介紹。

IF DB_ID(‘Test‘) IS NULLBEGIN    CREATE DATABASE Test;ENDGOUSE Test;GOIF OBJECT_ID(‘t1‘,‘U‘) IS NULLBEGIN    CREATE TABLE t1    (        t1_id    INT         NOT NULL IDENTITY(1,1),        t1_name  VARCHAR(20) NOT NULL,        t1_name1 VARCHAR(20) NOT NULL,        t1_name2 VARCHAR(20) NOT NULL,        t1_name3 VARCHAR(20) NOT NULL,        t1_name4 VARCHAR(20) NOT NULL,        t1_name5 VARCHAR(20) NOT NULL    );END/**插入測試資料*/INSERT INTO t1 (   t1_name,    t1_name1,    t1_name2,    t1_name3,    t1_name4,    t1_name5    )    SELECT ‘name‘,           ‘name‘,           ‘name‘,           ‘name‘,           ‘name‘,           ‘name‘      FROM sysobjects o1CROSS JOIN sysobjects o2;     /**建立覆蓋索引*/IF NOT EXISTS(SELECT 1                FROM sysindexes               WHERE name=‘idx_t1_id‘)BEGIN     CREATE NONCLUSTERED INDEX idx_t1_id    ON t1(t1_id)    INCLUDE(t1_name);END

建立表&測試資料&覆蓋索引

　　執行CROSS JOIN插入的測試資料有4000條左右，現在可以使用DBCC命令來查看錶的資料頁和索引頁的情況。

/**查看頁的基本資料*前提條件：表中必須插入了資料*所需參數：（資料庫名，表名，-1表示顯示全部IAM頁,資料頁, 索引頁）*/DBCC IND (Test,t1,-1); 

查看錶的資料頁和索引頁命令

　　執行完這條命令後，應該可以看到顯示的頁資訊，其中PageType=1的行表示資料頁，PageType=2的行表示索引頁，任意選擇一條PageType=2的行，找到PageFID和PagePID，就可以使用DBCC命令來查看索引頁的具體資訊。

/**查看索引頁的基本資料*所需參數：(資料庫名，PageFID，PagePID，3表示輸出每行每列的資訊)*/DBCC PAGE(Test,1,7732,3);

查看索引頁資訊的命令

 　　執行完這條命令後，應該可以看到t1_name這一列的資訊是包含在這個索引頁中的。現在可以通過執行不同的查詢SQL來查看覆蓋索引所帶來的效能提升，在執行SQL的同時開啟顯示實際的執行計畫，從而可以清楚得看到對比結果。

SELECT t1_name  FROM t1 WHERE t1_id = 500;SELECT t1_name1  FROM t1 WHERE t1_id =500;

對比查詢SQL

　　

　　

　　查詢1開銷為33%，而查詢2的開銷為67%，對比可以看到查詢t1_name的開銷比查詢t1_name1的開銷小很多，因為查詢t1_name只需要執行索引，就可以在索引頁上找到資料，而查詢t1_name1還要去尋找資料頁。

覆蓋索引的思考

　　建立索引能帶來查詢的最佳化，但卻帶來了更改資料的負擔，覆蓋索引也不意外。由上面的分析我們知道，覆蓋索引是非叢集索引的進一步細化，在更新資料的時候，如果涉及到覆蓋索引INCLUDE的列，除了更改資料頁之外還要更改索引頁，比單純使用非叢集索引增添了額外的工作。所以，在設計覆蓋索引的時候，要綜合考慮應該覆蓋的列，確保INCLUDE的列能帶來最佳的效能最佳化。

SQLServer覆蓋索引