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
