索引是一個(gè)單獨(dú)的、物理的數(shù)據(jù)庫(kù)結(jié)構(gòu)，它是某個(gè)表中一列或若干列值的集合和相應(yīng)的指向表中物理標(biāo)識(shí)這些值的數(shù)據(jù)頁(yè)的邏輯指針清單。索引提供指向存儲(chǔ)在表的指定列中的數(shù)據(jù)值的指針，然后根據(jù)您指定的排序順序?qū)@些指針排序。數(shù)據(jù)庫(kù)使用索引的方式與您使用書(shū)籍中的索引的方式很相似：它搜索索引以找到特定值，然后順指針找到包含該值的行。

要了解索引訪問(wèn)方法，首先要知道索引的結(jié)構(gòu)。

1.表和索引的結(jié)構(gòu)

頁(yè)

頁(yè)是sql server存儲(chǔ)數(shù)據(jù)的基本單位，大小為8kb，可以存儲(chǔ)表數(shù)據(jù)、索引數(shù)據(jù)、執(zhí)行計(jì)劃數(shù)據(jù)、分配位圖、可用空間信息。頁(yè)是sql server可以讀寫(xiě)的最小I/O單位。即便是讀取一行數(shù)據(jù)，它也要把整個(gè)頁(yè)加載到緩存并從緩存中讀取數(shù)據(jù)。

區(qū)

區(qū)是由8個(gè)連續(xù)頁(yè)組成的分配單元。

堆

堆是指不含聚集索引的表，它的數(shù)據(jù)不按任何順序進(jìn)行存儲(chǔ)。

聯(lián)系一個(gè)堆中的數(shù)據(jù)的唯一結(jié)構(gòu)是被稱為索引分配映射（IAM）的一個(gè)位圖頁(yè)，當(dāng)掃描對(duì)象時(shí)，SQl server使用IAM頁(yè)來(lái)遍歷該對(duì)象的數(shù)據(jù)。

聚集索引：

它的葉級(jí)表中維護(hù)所有數(shù)據(jù)，按照索引鍵列的順序存儲(chǔ)在索引的葉級(jí)。在索引頁(yè)級(jí)別的上層，索引還維護(hù)著其他級(jí)別，每個(gè)級(jí)別都概況了它下面的級(jí)別，非葉級(jí)索引上的每一行指向它下一級(jí)別的整個(gè)頁(yè)。

堆上的非聚集索引：

與聚集索引的唯一區(qū)別是非聚集索引的葉級(jí)頁(yè)只包含索引鍵列和指向特定數(shù)據(jù)行的行定位符，稱為RID。當(dāng)通過(guò)索引查找到特定的數(shù)據(jù)行后，Sqlserver必須在seek操作之后執(zhí)行RID lookup操作，該操作用于讀取包含數(shù)據(jù)行的頁(yè)。

聚集表上的非聚集索引：

指向特定數(shù)據(jù)行的行定位符是聚集鍵的值，不是RID。

2.索引訪問(wèn)方法

表掃描/無(wú)序聚集索引掃描

當(dāng)表中沒(méi)有索引時(shí)，連續(xù)的掃描表中的所有數(shù)據(jù)頁(yè)。SQl server將根據(jù)該表的IAM頁(yè)指示磁盤(pán)取數(shù)臂按物理順序掃描屬于該表的區(qū)。

當(dāng)表包含聚集索引時(shí)，所采取的方法將是無(wú)序聚集索引掃描。

示例sql：

select orderid,custid,empid,shipperid,orderdate,filler from dbo.Orders 

索引：

CREATE CLUSTERED INDEX idx_cl_od ON dbo.Orders(orderdate); 

表Orders結(jié)構(gòu):

orderid,custid,empid,shipperid,orderdate,filler 

覆蓋非聚集索引掃描

Sql server 只訪問(wèn)索引數(shù)據(jù)就可以找到滿足查詢所需的全部數(shù)據(jù)，不需要訪問(wèn)完整的數(shù)據(jù)行。

示例sql：

select orderid from dbo.Orders 

索引：

ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [PK_Orders] PRIMARY KEY NONCLUSTERED   
(  
[orderid] ASC 
) 

有序聚集索引掃描

按照鏈接列表對(duì)聚集索引葉級(jí)執(zhí)行的完整掃描 操作。

示例sql：

select orderid,custid,empid,shipperid,orderdate,filler from dbo.Orders order by orderdate 

索引：

CREATE CLUSTERED INDEX idx_cl_od ON dbo.Orders(orderdate); 

不同于無(wú)序索引掃描，有序掃描的性能取決于索引的碎片級(jí)別。

有序覆蓋非聚集索引掃描

與有序聚集索引掃描類(lèi)似，但是覆蓋非聚集索引掃描時(shí)，因?yàn)樗婕案俚捻?yè)，它的成本肯定比聚集索引索引掃描要低。

示例sql：

select orderid, orderdate from dbo.Orders order by orderid 

非聚集索引索引查找+有序局部掃描+lookups

通常用于小范圍查詢，且用到的非聚集索引沒(méi)有覆蓋該查詢。

示例sql：

select orderid,custid,empid,shipperid,orderdate,filler   
from dbo.Orders where orderid between 101 and 200 

無(wú)序非聚集索引掃描 + lookups

通常符合以下情況時(shí)，優(yōu)化器會(huì)選擇此種訪問(wèn)方法：

• 該查詢的選擇性足夠高
• 最適合某查詢的索引并不覆蓋該查詢
• 索引沒(méi)有按順序維護(hù)被查找鍵

示例sql：

select orderid,custid,empid,shipperid,orderdate,filler   
from dbo.Orders where custid = ‘’ 

聚集索引查找+有序局部掃描

對(duì)于按聚集索引的***個(gè)鍵列進(jìn)行篩選的范圍查詢，優(yōu)化器通常使用這種方法。

示例sql：

select orderid,custid,empid,shipperid,orderdate,filler   
from dbo.Orders where orderdate = ‘20060212’ 

這種方法的好處是不涉及l(fā)ookups.

覆蓋非聚集索引查找+有序局部掃描

訪問(wèn)方法與上一個(gè)類(lèi)似，唯一的區(qū)別是非聚集索引。相對(duì)于上一個(gè)訪問(wèn)方法，這個(gè)方法的好處在于非聚集索引的的葉級(jí)頁(yè)比聚集索引的葉級(jí)頁(yè)能夠容納更多的行。

示例sql:

select shipperid,orderdate, custid from dbo.Orders  
Where shipperid='C' and orderdate >='20060101' and orderdate <'20070101' 
CREATE NONCLUSTERED INDEX idx_nc_sid_od_cid  
ON dbo.Orders(shipperid, orderdate, custid); 

3.索引優(yōu)化等級(jí)

需要優(yōu)化的sql：select orderid,custid,empid,shipperid,orderdate,filler from dbo.Orders where orderid > 999001

1.這個(gè)表沒(méi)有任何索引：該計(jì)劃將使用表掃描

2.接下來(lái)優(yōu)化，創(chuàng)建一個(gè)非聚集覆蓋索引，且不把篩選列（orderid）作為***個(gè)篩選列：

CREATE INDEX idx_nc_od_i_oid_cid_eid_sid  
ON performance.dbo.Orders(orderdate)   
include(orderid,custid,empid,shipperid); 

優(yōu)化器將采用覆蓋非聚集索引掃描

3.下一步優(yōu)化：創(chuàng)建一個(gè)不覆蓋該查詢的非聚集索引

CREATE NONCLUSTERED INDEX idx_nc_od_i_oid  
ON dbo.Orders(orderdate)  
INCLUDE(orderid); 

優(yōu)化器將采用非聚集索引掃描+lookup，這個(gè)查詢依賴于選擇性。選擇性越高，性能越高。

4.繼續(xù)優(yōu)化：在orderid上創(chuàng)建非聚集非覆蓋索引，

CREATE UNIQUE NONCLUSTERED INDEX idx_unc_oid  
ON dbo.Orders(orderid); 

優(yōu)化器將采用非聚集索引查找+lookup

5.繼續(xù)優(yōu)化：在orderid上創(chuàng)建聚集索引

CREATE UNIQUE CLUSTERED INDEX idx_cl_oid ON dbo.Orders(orderid); 

這個(gè)計(jì)劃主要不涉及l(fā)ookup，

6.繼續(xù)優(yōu)化：

***優(yōu)化應(yīng)該是把orderid作為鍵列，并把其他列定義為包含性非鍵列的非聚集覆蓋索引。

CREATE UNIQUE NONCLUSTERED INDEX idx_unc_oid_i_od_cid_eid_sid  
ON dbo.Orders(orderid)  
INCLUDE(orderdate, custid, empid, shipperid); 

這個(gè)計(jì)劃的邏輯與上一個(gè)類(lèi)似，只是非聚集覆蓋索引有序局部掃描讀取的頁(yè)更少。

【編輯推薦】

1. SQL Server 索引結(jié)構(gòu)及其使用
2. ORACLE的索引和約束詳解
3. Oracle數(shù)據(jù)庫(kù)效率技巧：避免錯(cuò)誤的索引
4. 由淺入深講解MySQL數(shù)據(jù)庫(kù)索引的選擇性
5. 建立合理的索引提高SQL Server的性能