sqlserver 索引的一些總結

1.1.1 摘要
如果說要對資料庫進行最佳化，我們主要可以通過以下五種方法，對資料庫系統進行最佳化。

1. 電腦硬體調優
2. 應用程式調優
3. 資料庫索引最佳化
4. SQL語句最佳化
5. 交易處理調優

在本篇博文中，我們將想大家講述資料庫中索引類型和使用場合，本文以SQL Server為例，對於其他技術平台的朋友也是有參考價值的，只要替換相對應的代碼就行了！

索引使資料庫引擎執行速度更快，有針對性的資料檢索，而不是簡單地整表掃描（Full table scan）。

為了使用有效索引，我們必須對索引的構成有所瞭解，而且我們知道在資料表中添加索引必然需要建立和維護索引表，所以我們要全域地衡量添加索引是否能提高資料庫系統的查詢效能。

在物理層面上，資料庫有資料檔案組成，而這些資料檔案可以組成檔案組，然後儲存在磁碟上。每個檔案包含許多區，每個區的大小為64K由八個物理上連續的頁組成（一個頁8K），我們知道頁是SQL Server資料庫中的資料存放區的基本單位。為資料庫中的資料檔案（.mdf 或 .ndf）分配的磁碟空間可以從邏輯上劃分成頁（從0到n連續編號）。

頁中儲存的類型有：資料，索引和溢出。

檔案和檔案組
在SQL Server中，通過檔案組這個邏輯對象對存放資料的檔案進行管理。

1.1.2 本文

在物理層面上，資料庫有資料檔案組成，而這些資料檔案可以組成檔案組，然後儲存在磁碟上。每個檔案包含許多區，每個區的大小為64K由八個物理上連續的頁組成（一個頁8K），我們知道頁是SQL Server資料庫中的資料存放區的基本單位。為資料庫中的資料檔案（.mdf 或 .ndf）分配的磁碟空間可以從邏輯上劃分成頁（從0到n連續編號）。

頁中儲存的類型有：資料，索引和溢出。

檔案和檔案組
在SQL Server中，通過檔案組這個邏輯對象對存放資料的檔案進行管理。

圖1資料庫檔案組織

在頂層是我們的資料庫，由於資料庫是由一個或多個檔案組組成，而檔案組是由一個或多個檔案組成的邏輯組，所以我們可以把檔案組分散到不同的磁碟中，使使用者資料儘可能跨越多個裝置，多個I/O 運轉，避免 I/O 競爭，從而均衡I/O負載，克服訪問瓶頸。

區和頁
2所示，檔案是由區組成的，而區由八個物理上連續的頁組成，由於區的大小為64K，所以每當增加一個區檔案就增加64K。

圖2檔案組成

頁中儲存的資料類型有：表資料、索引資料、溢出資料、分配映射、頁空閑空間、索引分配等，具體如所示：

頁類型	內容
Data	當 text in row 設定為 ON 時，包含除 text、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和 xml 資料之外的所有資料的資料行。
Index	索引條目。
Text/Image	大型物件資料類型：text 、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和 xml 資料。資料行超過 8 KB 時為可變長度資料類型列：varchar 、nvarchar、varbinary 和 sql_variant
Global Allocation Map、Shared Global Allocation Map	有關區是否分配的資訊。
Page Free Space	有關頁分配和頁的可用空間的資訊。
Index Allocation Map	有關每個配置單位中表或索引所使用的區的資訊。
Bulk Changed Map	有關每個配置單位中自最後一條 BACKUP LOG 語句之後的大容量操作所修改的區的資訊。
Differential Changed Map	有關每個配置單位中自最後一條 BACKUP DATABASE 語句之後更改的區的資訊。

表1頁中儲存的資料類型

在資料頁上，資料行緊接著頁頭（標題）按順序放置；頁頭包含標識值，如頁碼或對象資料的對象ID；資料行持有實際的資料；最後，頁的末尾是行位移表，對於頁中的每一行，每個行位移表都包含一個條目，每個條目記錄對應行的第一個位元組與頁頭的距離，行位移表中的條目的順序與頁中行的順序相反。

圖3資料頁

索引的基本結構
“索引（Index）提供查詢的速度”這是對索引的最基本的解釋，接下來我們將通過介紹索引的組成，讓大家對索引有更深入的理解。

索引是資料庫中的一個獨特的結構，由於它儲存資料庫資訊，那麼我們就需要給它分配磁碟空間和維護索引表。建立索引並不會改變表中的資料，它只是建立了一個新的資料結構指向資料表；打個比方，平時我們使用字典查字時，首先我們要知道查詢單詞起始字母，然後翻到目錄頁，接著尋找單詞具體在哪一頁，這時我們目錄就是索引表，而目錄項就是索引了。

當然，索引比字典目錄更為複雜，因為資料庫必須處理插入，刪除和更新等操作，這些操作將導致索引發生變化。

分葉節點

假設我們磁碟上的資料是物理有序的，那麼資料庫在進行插入，刪除和更新操作時，必然會導致資料發生變化，如果我們要儲存資料的連續和有序，那麼我們就需要移動資料的物理位置，這將增大磁碟的I/O，使得整個資料庫運行非常緩慢；使用索引的主要目的是使資料邏輯有序，使資料獨立於物理有序儲存。

為了實現資料邏輯有序，索引使用雙向鏈表的資料結構來保持資料邏輯順序，如果要在兩個節點中插入一個新的節點只需修改節點的前驅和後繼，而且無需修改新節點的物理位置。

雙向鏈表（Doubly linked list）也叫雙鏈表，是鏈表的一種，它的每個資料結點中都有兩個指標，分別指向直接後繼和直接前驅。所以，從雙向鏈表中的任意一個結點開始，都可以很方便地訪問它的前驅結點和後繼結點。

理論上說，從雙向鏈表中刪除一個元素操作的時間複雜度是O(1)，如果希望刪除一個具體有給定關鍵字的元素，那麼最壞的情況下的時間複雜度為O(n)。

在刪除的過程中，我們只需要將要刪除的節點的前節點和後節點相連，然後將要刪除的節點的前節點和後節點置為null即可。

複製代碼 代碼如下://虛擬碼
node.prev.next=node.next;
node.next.prev=node.prev;
node.prev=node.next=null;

圖4索引的分葉節點和相應的表資料

如4所示，索引分葉節點包含索引值和相應的RID（ROWID），而且分葉節點通過雙向鏈表有序地串連起來；同時我們主要到資料表不同於索引分葉節點，表中的資料無序儲存，它們不全是儲存在同一表塊中，而且塊之間不存在串連。

總的來說，索引儲存著具體資料的物理地址值。

索引的類型

我們知道索引的類型有兩種：叢集索引和非叢集索引。
叢集索引：實體儲存體按照索引排序。
非叢集索引：實體儲存體不按照索引排序。

叢集索引

叢集索引的資料頁是物理有序地儲存，資料頁是叢集索引的分葉節點，資料頁之間通過雙向鏈表的形式串連起來，而且實際的資料都儲存在資料頁中。當我們給表添加索引後，表中的資料將根據索引進行排序。
假設我們有一個表T_Pet，它包含四個欄位分別是：animal，name，sex和age，而且使用animal作為索引列，具體SQL代碼如下： 複製代碼 代碼如下:-----------------------------------------------------------
---- Create T_Pet table in tempdb.
-----------------------------------------------------------
USE tempdb
CREATE TABLE T_Pet
(
animal VARCHAR(20),
[name] VARCHAR(20),
sex CHAR(1),
age INT
)
CREATE UNIQUE CLUSTERED INDEX T_PetonAnimal1_ClterIdx ON T_Pet (animal)

-----------------------------------------------------------
---- Insert data into data table.
----------------------------------------------------------- 複製代碼 代碼如下:DECLARE @i int
SET @i=0
WHILE(@i<1000000)
BEGIN
INSERT INTO T_Pet (
animal,
[name],
sex,
age
)
SELECT [dbo].random_string(11) animal,
[dbo].random_string(11) [name],
'F' sex,
cast(floor(rand()*5) as int) age
SET @i=@i+1
END
INSERT INTO T_Pet VALUES('Aardark', 'Hello', 'F', 1)
INSERT INTO T_Pet VALUES('Cat', 'Kitty', 'F', 2)
INSERT INTO T_Pet VALUES('Horse', 'Ma', 'F', 1)
INSERT INTO T_Pet VALUES('Turtles', 'SiSi', 'F', 4)
INSERT INTO T_Pet VALUES('Dog', 'Tomma', 'F', 2)
INSERT INTO T_Pet VALUES('Donkey', 'YoYo', 'F', 3)

圖5叢集索引

如5所示，從左往右的第一和第二層是索引頁，第三層是資料頁（分葉節點），資料頁之間通過雙向鏈表串連起來，而且資料頁中的資料根據索引排序；假設，我們要尋找名字（name）為Xnnbqba的動物Ifcey，這裡我們以animal作為表的索引，所以資料庫首先根據索引尋找，當找到索引值animal = ‘Ifcey時，接著尋找該索引的資料頁（分葉節點）擷取具體資料。具體的查詢語句如下： 複製代碼 代碼如下:SET STATISTICS PROFILE ON
SET STATISTICS TIME ON

SELECT animal, [name], sex, age
FROM T_Pet
WHERE animal = 'Ifcey'

SET STATISTICS PROFILE OFF
SET STATISTICS TIME OFF

當我們執行完SQL查詢計劃時，把滑鼠指標放到“叢集索引尋找”上，這時會出現如資訊，我們可以查看到一個重要的資訊Logical Operation——Clustered Index Seek，SQL查詢是直接根據叢集索引擷取記錄，查詢速度最快。

圖6查詢計劃

從查詢結果，我們發現查詢步驟只有2步，首先通過Clustered Index Seek快速地找到索引Ifcey，接著查詢索引的分葉節點（資料頁）擷取資料。

查詢執行時間：CPU 時間= 0 毫秒，佔用時間= 1 毫秒。

圖7查詢結果

現在我們把表中的索引刪除，重新執行查詢計劃，這時我們可以發現Logical Operation已經變為Table Scan，由於表中有100萬行資料，這時查詢速度就相當緩慢。

圖8查詢計劃

從查詢結果，我們發現查詢步驟變成3步了，首先通過Table Scan尋找animal = ‘Ifcey'，在執行查詢的時候，SQL Server會自動分析SQL語句，而且它估計我們這次查詢比較耗時，所以資料庫進行並行作業加快查詢的速度。

查詢執行時間：CPU 時間= 329 毫秒，佔用時間= 182 毫秒。

圖9查詢結果

通過上面的有叢集索引和沒有的對比，我們發現了查詢效能的差異，如果使用索引資料庫首先尋找索引，而不是漫無目的的全表遍曆。

非叢集索引

在沒有叢集索引的情況下，表中的資料頁是通過堆(Heap)形式進行儲存，堆是不含叢集索引的表；SQL Server中的堆儲存是把新的資料行儲存到最後一個頁中。

非叢集索引是實體儲存體不按照索引排序，非叢集索引的分葉節點（Index leaf pages）包含著指向具體資料行的指標或叢集索引，資料頁之間沒有串連是相對獨立的頁。

假設我們有一個表T_Pet，它包含四個欄位分別是：animal，name，sex和age，而且使用animal作為非索引列，具體SQL代碼如下： 複製代碼 代碼如下:-----------------------------------------------------------
---- Create T_Pet table in tempdb with NONCLUSTERED INDEX.
-----------------------------------------------------------
USE tempdb
CREATE TABLE T_Pet
(
animal VARCHAR(20),
[name] VARCHAR(20),
sex CHAR(1),
age INT
)
CREATE UNIQUE NONCLUSTERED INDEX T_PetonAnimal1_NonClterIdx ON T_Pet (animal)

圖10非叢集索引

接著我們要查詢表中animal = ‘Cat'的寵物資訊，具體的SQL代碼如下： 複製代碼 代碼如下:SET STATISTICS PROFILE ON
SET STATISTICS TIME ON

SELECT animal, [name], sex, age
FROM T_Pet
WHERE animal = 'Cat'

SET STATISTICS PROFILE OFF
SET STATISTICS TIME OFF

如所示，我們發現查詢計劃的最右邊有兩個步驟：RID和索引尋找。由於這兩種尋找方式相對於叢集索引尋找要慢（Clustered Index Seek）。

圖11查詢計劃

首先SQL Server尋找索引值，然後根據RID尋找資料行，直到找到符合查詢條件的結果。

查詢執行時間：CPU 時間= 0 毫秒，佔用時間= 1 毫秒

圖12查詢結果

堆表非叢集索引

由於堆是不含叢集索引的表，所以非叢集索引的分葉節點將包含指向具體資料行的指標。

以前面的T_Pet表為例，假設T_Pet使用animal列作為非叢集索引，那麼它的堆表非叢集索引結構如所示：

圖13堆表非叢集索引

通過，我們發現非叢集索引通過雙向鏈表串連，而且分葉節點包含指向具體資料行的指標。

如果我們要尋找animal = ‘Dog'的資訊，首先我們遍曆第一層索引，然後資料庫判斷Dog屬於Cat範圍的索引，接著遍曆第二層索引，然後找到Dog索引擷取其中的儲存的指標資訊，根據指標資訊擷取相應資料頁中的資料，接下來我們將通過具體的例子說明。

現在我們建立表employees，然後給該表添加堆表非叢集索引，具體SQL代碼如下： 複製代碼 代碼如下:USE tempdb
---- Creates a sample table.
CREATE TABLE employees (
employee_id NUMERIC NOT NULL,
first_name VARCHAR(1000) NOT NULL,
last_name VARCHAR(900) NOT NULL,
date_of_birth DATETIME ,
phone_number VARCHAR(1000) NOT NULL,
junk CHAR(1000) ,
CONSTRAINT employees_pk PRIMARY KEY NONCLUSTERED (employee_id)
);

GO現在我們尋找employee_id = 29976的員工資訊。 複製代碼 代碼如下:SELECT *
FROM employees
WHERE employee_id = 29976

查詢計劃如所示：

圖14查詢計劃

首先，尋找索引值employee_id = ‘29976'的索引，然後根據RID尋找合格資料行；所以說，堆表索引的查詢效率不如聚集表，接下來我們將介紹聚集表的非叢集索引。

聚集表非叢集索引

當表上存在叢集索引時，任何非叢集索引的分葉節點不再是包含指標值，而是包含叢集索引的索引值。

以前面的T_Pet表為例，假設T_Pet使用animal列作為非叢集索引，那麼它的索引表非叢集索引結構如所示：

圖15索引表非叢集索引

通過，我們發現非叢集索引通過雙向鏈表串連，而且分葉節點包含索引表的索引值。

如果我們要尋找animal = ‘Dog'的資訊，首先我們遍曆第一層索引，然後資料庫判斷Dog屬於Cat範圍的索引，接著遍曆第二層索引，然後找到Dog索引擷取其中的儲存的索引值，然後根據索引值擷取相應資料頁中的資料。

接下來我們修改之前的employees表，首先我們刪除之前的堆表非叢集索引，然後增加索引表的非叢集索引，具體SQL代碼如下： 複製代碼 代碼如下:ALTER TABLE employees
DROP CONSTRAINT employees_pk

ALTER TABLE employees
ADD CONSTRAINT employees_pk PRIMARY KEY CLUSTERED (employee_id)
GO

SELECT * FROM employees
WHERE employee_id=29976

圖16查詢計劃

索引的有效性
SQL Server每執行一個查詢，首先要檢查該查詢是否存在執行計畫，如果沒有，則要產生一個執行計畫，那麼什麼是執行計畫呢？簡單來說，它能協助SQL Server制定一個最優的查詢計劃。（關於查詢計劃請參考這裡）

下面我們將通過具體的例子說明SQL Server中索引的使用，首先我們定義一個表testIndex，它包含三個欄位testIndex，bitValue和filler，具體的SQL代碼如下： 複製代碼 代碼如下:-----------------------------------------------------------
---- Index Usefulness sample
-----------------------------------------------------------

CREATE TABLE testIndex
(
testIndex int identity(1,1) constraint PKtestIndex primary key,
bitValue bit,
filler char(2000) not null default (replicate('A',2000))
)

CREATE INDEX XtestIndex_bitValue on testIndex(bitValue)
GO

INSERT INTO testIndex(bitValue)
VALUES (0)
GO 20000 --runs current batch 20000 times.

INSERT INTO testIndex(bitValue)
VALUES (1)
GO 10 --puts 10 rows into table with value 1

接著我們查詢表中bitValue = 0的資料行，而且表中bitValue = 0的資料有2000行。 複製代碼 代碼如下:SELECT *
FROM testIndex
WHERE bitValue = 0

圖17查詢計劃

現在我們查詢bitValue = 1的資料行。

SELECT *FROM testIndexWHERE bitValue = 1

圖18查詢計劃

現在我們注意到對同一個表不同資料查詢，居然執行截然不同的查詢計劃，這究竟是什麼原因導致的呢？

我們可以通過使用DBCC SHOW_STATISTICS查看到表中索引的詳細使用方式，具體SQL代碼如下： 複製代碼 代碼如下:UPDATE STATISTICS dbo.testIndex
DBCC SHOW_STATISTICS('dbo.testIndex', 'XtestIndex_bitValue')
WITH HISTOGRAM

圖19長條圖

通過上面的長條圖，我們知道SQL Server估計bitValue = 0資料行行有約19989行，而bitValue = 1估計約21；SQL Server最佳化器根據資料量估算值，採取不同的執行計畫，從而到達最優的查詢效能，由於bitValue = 0資料量大，SQL Server只能提供掃描叢集索引擷取相應資料行，而bitValue = 1實際資料行只有10行，SQL Server首先通過鍵尋找bitValue = 1的資料行，然後巢狀迴圈聯結到叢集索引獲得餘下資料行。

總結 完整執行個體代碼：複製代碼 代碼如下:-- =============================================
-- Author: JKhuang
-- Create date: 04/20/2012
-- Description: Create sample for Clustered and
-- Nonclustered index.
-- =============================================

-----------------------------------------------------------
---- Create T_Pet table in tempdb with NONCLUSTERED INDEX.
-----------------------------------------------------------
USE tempdb
CREATE TABLE T_Pet
(
animal VARCHAR(20),
[name] VARCHAR(20),
sex CHAR(1),
age INT
)
CREATE UNIQUE NONCLUSTERED INDEX T_PetonAnimal1_NonClterIdx ON T_Pet (animal)
CREATE UNIQUE CLUSTERED INDEX T_PetonAnimal1_ClterIdx ON T_Pet (animal)
-----------------------------------------------------------
---- Insert data into data table.
-----------------------------------------------------------
DECLARE @i int
SET @i=0
WHILE(@i<1000000)
BEGIN
INSERT INTO T_Pet (
animal,
[name],
sex,
age
)
SELECT [dbo].random_string(11) animal,
[dbo].random_string(11) [name],
'F' sex,
cast(floor(rand()*5) as int) age
SET @i=@i+1
END
INSERT INTO T_Pet VALUES('Aardark', 'Hello', 'F', 1)
INSERT INTO T_Pet VALUES('Cat', 'Kitty', 'F', 2)
INSERT INTO T_Pet VALUES('Horse', 'Ma', 'F', 1)
INSERT INTO T_Pet VALUES('Turtles', 'SiSi', 'F', 4)
INSERT INTO T_Pet VALUES('Dog', 'Tomma', 'F', 2)
INSERT INTO T_Pet VALUES('Donkey', 'YoYo', 'F', 3)

SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SELECT animal, [name], sex, age
FROM T_Pet
WHERE animal = 'Cat'
SET STATISTICS PROFILE OFF
SET STATISTICS TIME OFF

-----------------------------------------------------------
---- Create employees table in tempdb.
-----------------------------------------------------------
CREATE TABLE employees (

employee_id NUMERIC NOT NULL,
first_name VARCHAR(1000) NOT NULL,
last_name VARCHAR(900) NOT NULL,
date_of_birth DATETIME ,
phone_number VARCHAR(1000) NOT NULL,
junk CHAR(1000) ,
--PK constraint defaults to clustered
CONSTRAINT employees_pk PRIMARY KEY (employee_id)
);
GO

-----------------------------------------------------------
---- Insert data into data table.
-----------------------------------------------------------
CREATE VIEW rand_helper AS SELECT RND=RAND();
GO
---- Generates random string function.
CREATE FUNCTION random_string (@maxlen int) RETURNS VARCHAR(255)
AS BEGIN
DECLARE @rv VARCHAR(255)
DECLARE @loop int
DECLARE @len int
SET @len = (SELECT CAST(rnd * (@maxlen-3) AS INT) +3
FROM rand_helper)
SET @rv = ''
SET @loop = 0
WHILE @loop < @len BEGIN
SET @rv = @rv
+ CHAR(CAST((SELECT rnd
FROM rand_helper) * 26 AS INT )+97)
IF @loop = 0 BEGIN
SET @rv = UPPER(@rv)
END
SET @loop = @loop +1;
END
RETURN @rv
END
GO
---- Generates random date function.
CREATE FUNCTION random_date (@mindaysago int, @maxdaysago int)
RETURNS VARCHAR(255)
AS BEGIN
DECLARE @rv datetime
SET @rv = (SELECT GetDate()
- rnd * (@maxdaysago-@mindaysago)
- @mindaysago
FROM rand_helper)
RETURN @rv
END
GO
---- Generates random int function.
CREATE FUNCTION random_int (@min int, @max int) RETURNS INT
AS BEGIN
DECLARE @rv INT
SET @rv = (SELECT rnd * (@max) + @min
FROM rand_helper)
RETURN @rv
END
GO
---- Inserts data into employees table.
WITH generator (n) as
(
select 1
union all
select n + 1 from generator
where N < 30000
)
INSERT INTO employees (employee_id
, first_name, last_name
, date_of_birth, phone_number, junk)
select n employee_id
, [dbo].random_string(11) first_name
, [dbo].random_string(11) last_name
, [dbo].random_date(20*365, 60*365) dob
, 'N/A' phone
, 'junk' junk
from generator
OPTION (MAXRECURSION 30000)
-----------------------------------------------------------
---- Index Usefulness sample
-----------------------------------------------------------
CREATE TABLE testIndex
(
testIndex int identity(1,1) constraint PKtestIndex primary key,
bitValue bit,
filler char(2000) not null default (replicate('A',2000))
)
CREATE INDEX XtestIndex_bitValue on testIndex(bitValue)
GO
INSERT INTO testIndex(bitValue)
VALUES (0)
GO 20000 --runs current batch 20000 times.
INSERT INTO testIndex(bitValue)
VALUES (1)
GO 10 --puts 10 rows into table with value 1
SELECT filler
FROM testIndex
WHERE bitValue = 1
UPDATE STATISTICS dbo.testIndex
DBCC SHOW_STATISTICS('dbo.testIndex', 'XtestIndex_bitValue')
WITH HISTOGRAM