sqlserver 索引的一些總結(jié)
更新時(shí)間：2012年08月02日 14:39:13 作者：
如果說(shuō)要對(duì)數(shù)據(jù)庫(kù)進(jìn)行優(yōu)化，我們主要可以通過(guò)以下五種方法，對(duì)數(shù)據(jù)庫(kù)系統(tǒng)進(jìn)行優(yōu)化
1.1.1 摘要
如果說(shuō)要對(duì)數(shù)據(jù)庫(kù)進(jìn)行優(yōu)化，我們主要可以通過(guò)以下五種方法，對(duì)數(shù)據(jù)庫(kù)系統(tǒng)進(jìn)行優(yōu)化。
1. 計(jì)算機(jī)硬件調(diào)優(yōu)
2. 應(yīng)用程序調(diào)優(yōu)
3. 數(shù)據(jù)庫(kù)索引優(yōu)化
4. SQL語(yǔ)句優(yōu)化
5. 事務(wù)處理調(diào)優(yōu)
在本篇博文中，我們將想大家講述數(shù)據(jù)庫(kù)中索引類型和使用場(chǎng)合，本文以SQL Server為例，對(duì)于其他技術(shù)平臺(tái)的朋友也是有參考價(jià)值的，只要替換相對(duì)應(yīng)的代碼就行了！
索引使數(shù)據(jù)庫(kù)引擎執(zhí)行速度更快，有針對(duì)性的數(shù)據(jù)檢索，而不是簡(jiǎn)單地整表掃描（Full table scan）。
為了使用有效的索引，我們必須對(duì)索引的構(gòu)成有所了解，而且我們知道在數(shù)據(jù)表中添加索引必然需要?jiǎng)?chuàng)建和維護(hù)索引表，所以我們要全局地衡量添加索引是否能提高數(shù)據(jù)庫(kù)系統(tǒng)的查詢性能。
在物理層面上，數(shù)據(jù)庫(kù)有數(shù)據(jù)文件組成，而這些數(shù)據(jù)文件可以組成文件組，然后存儲(chǔ)在磁盤上。每個(gè)文件包含許多區(qū)，每個(gè)區(qū)的大小為64K由八個(gè)物理上連續(xù)的頁(yè)組成（一個(gè)頁(yè)8K），我們知道頁(yè)是SQL Server數(shù)據(jù)庫(kù)中的數(shù)據(jù)存儲(chǔ)的基本單位。為數(shù)據(jù)庫(kù)中的數(shù)據(jù)文件（.mdf 或 .ndf）分配的磁盤空間可以從邏輯上劃分成頁(yè)（從0到n連續(xù)編號(hào)）。
頁(yè)中存儲(chǔ)的類型有：數(shù)據(jù)，索引和溢出。
文件和文件組
在SQL Server中，通過(guò)文件組這個(gè)邏輯對(duì)象對(duì)存放數(shù)據(jù)的文件進(jìn)行管理。
1.1.2 正文
在物理層面上，數(shù)據(jù)庫(kù)有數(shù)據(jù)文件組成，而這些數(shù)據(jù)文件可以組成文件組，然后存儲(chǔ)在磁盤上。每個(gè)文件包含許多區(qū)，每個(gè)區(qū)的大小為64K由八個(gè)物理上連續(xù)的頁(yè)組成（一個(gè)頁(yè)8K），我們知道頁(yè)是SQL Server數(shù)據(jù)庫(kù)中的數(shù)據(jù)存儲(chǔ)的基本單位。為數(shù)據(jù)庫(kù)中的數(shù)據(jù)文件（.mdf 或 .ndf）分配的磁盤空間可以從邏輯上劃分成頁(yè)（從0到n連續(xù)編號(hào)）。
頁(yè)中存儲(chǔ)的類型有：數(shù)據(jù)，索引和溢出。
文件和文件組
在SQL Server中，通過(guò)文件組這個(gè)邏輯對(duì)象對(duì)存放數(shù)據(jù)的文件進(jìn)行管理。
在頂層是我們的數(shù)據(jù)庫(kù)，由于數(shù)據(jù)庫(kù)是由一個(gè)或多個(gè)文件組組成，而文件組是由一個(gè)或多個(gè)文件組成的邏輯組，所以我們可以把文件組分散到不同的磁盤中，使用戶數(shù)據(jù)盡可能跨越多個(gè)設(shè)備，多個(gè)I/O 運(yùn)轉(zhuǎn)，避免 I/O 競(jìng)爭(zhēng)，從而均衡I/O負(fù)載，克服訪問(wèn)瓶頸。
區(qū)和頁(yè)
如圖2所示，文件是由區(qū)組成的，而區(qū)由八個(gè)物理上連續(xù)的頁(yè)組成，由于區(qū)的大小為64K，所以每當(dāng)增加一個(gè)區(qū)文件就增加64K。
頁(yè)中保存的數(shù)據(jù)類型有：表數(shù)據(jù)、索引數(shù)據(jù)、溢出數(shù)據(jù)、分配映射、頁(yè)空閑空間、索引分配等，具體如下圖所示：
表1頁(yè)中保存的數(shù)據(jù)類型
在數(shù)據(jù)頁(yè)上，數(shù)據(jù)行緊接著頁(yè)頭（標(biāo)頭）按順序放置；頁(yè)頭包含標(biāo)識(shí)值，如頁(yè)碼或?qū)ο髷?shù)據(jù)的對(duì)象ID；數(shù)據(jù)行持有實(shí)際的數(shù)據(jù)；最后，頁(yè)的末尾是行偏移表，對(duì)于頁(yè)中的每一行，每個(gè)行偏移表都包含一個(gè)條目，每個(gè)條目記錄對(duì)應(yīng)行的第一個(gè)字節(jié)與頁(yè)頭的距離，行偏移表中的條目的順序與頁(yè)中行的順序相反。
索引的基本結(jié)構(gòu)
“索引（Index）提供查詢的速度”這是對(duì)索引的最基本的解釋，接下來(lái)我們將通過(guò)介紹索引的組成，讓大家對(duì)索引有更深入的理解。
索引是數(shù)據(jù)庫(kù)中的一個(gè)獨(dú)特的結(jié)構(gòu)，由于它保存數(shù)據(jù)庫(kù)信息，那么我們就需要給它分配磁盤空間和維護(hù)索引表。創(chuàng)建索引并不會(huì)改變表中的數(shù)據(jù)，它只是創(chuàng)建了一個(gè)新的數(shù)據(jù)結(jié)構(gòu)指向數(shù)據(jù)表；打個(gè)比方，平時(shí)我們使用字典查字時(shí)，首先我們要知道查詢單詞起始字母，然后翻到目錄頁(yè)，接著查找單詞具體在哪一頁(yè)，這時(shí)我們目錄就是索引表，而目錄項(xiàng)就是索引了。
當(dāng)然，索引比字典目錄更為復(fù)雜，因?yàn)閿?shù)據(jù)庫(kù)必須處理插入，刪除和更新等操作，這些操作將導(dǎo)致索引發(fā)生變化。
葉節(jié)點(diǎn)
假設(shè)我們磁盤上的數(shù)據(jù)是物理有序的，那么數(shù)據(jù)庫(kù)在進(jìn)行插入，刪除和更新操作時(shí)，必然會(huì)導(dǎo)致數(shù)據(jù)發(fā)生變化，如果我們要保存數(shù)據(jù)的連續(xù)和有序，那么我們就需要移動(dòng)數(shù)據(jù)的物理位置，這將增大磁盤的I/O，使得整個(gè)數(shù)據(jù)庫(kù)運(yùn)行非常緩慢；使用索引的主要目的是使數(shù)據(jù)邏輯有序，使數(shù)據(jù)獨(dú)立于物理有序存儲(chǔ)。
為了實(shí)現(xiàn)數(shù)據(jù)邏輯有序，索引使用雙向鏈表的數(shù)據(jù)結(jié)構(gòu)來(lái)保持?jǐn)?shù)據(jù)邏輯順序，如果要在兩個(gè)節(jié)點(diǎn)中插入一個(gè)新的節(jié)點(diǎn)只需修改節(jié)點(diǎn)的前驅(qū)和后繼，而且無(wú)需修改新節(jié)點(diǎn)的物理位置。
雙向鏈表（Doubly linked list）也叫雙鏈表，是鏈表的一種，它的每個(gè)數(shù)據(jù)結(jié)點(diǎn)中都有兩個(gè)指針，分別指向直接后繼和直接前驅(qū)。所以，從雙向鏈表中的任意一個(gè)結(jié)點(diǎn)開始，都可以很方便地訪問(wèn)它的前驅(qū)結(jié)點(diǎn)和后繼結(jié)點(diǎn)。
理論上說(shuō)，從雙向鏈表中刪除一個(gè)元素操作的時(shí)間復(fù)雜度是O(1)，如果希望刪除一個(gè)具體有給定關(guān)鍵字的元素，那么最壞的情況下的時(shí)間復(fù)雜度為O(n)。
在刪除的過(guò)程中，我們只需要將要?jiǎng)h除的節(jié)點(diǎn)的前節(jié)點(diǎn)和后節(jié)點(diǎn)相連，然后將要?jiǎng)h除的節(jié)點(diǎn)的前節(jié)點(diǎn)和后節(jié)點(diǎn)置為null即可。
//偽代碼
node.prev.next=node.next;
node.next.prev=node.prev;
node.prev=node.next=null;
圖4索引的葉節(jié)點(diǎn)和相應(yīng)的表數(shù)據(jù)
如上圖4所示，索引葉節(jié)點(diǎn)包含索引值和相應(yīng)的RID（ROWID），而且葉節(jié)點(diǎn)通過(guò)雙向鏈表有序地連接起來(lái)；同時(shí)我們主要到數(shù)據(jù)表不同于索引葉節(jié)點(diǎn)，表中的數(shù)據(jù)無(wú)序存儲(chǔ)，它們不全是存儲(chǔ)在同一表塊中，而且塊之間不存在連接。
總的來(lái)說(shuō)，索引保存著具體數(shù)據(jù)的物理地址值。
索引的類型
我們知道索引的類型有兩種：聚集索引和非聚集索引。
聚集索引：物理存儲(chǔ)按照索引排序。
非聚集索引：物理存儲(chǔ)不按照索引排序。
聚集索引
聚集索引的數(shù)據(jù)頁(yè)是物理有序地存儲(chǔ)，數(shù)據(jù)頁(yè)是聚集索引的葉節(jié)點(diǎn)，數(shù)據(jù)頁(yè)之間通過(guò)雙向鏈表的形式連接起來(lái)，而且實(shí)際的數(shù)據(jù)都存儲(chǔ)在數(shù)據(jù)頁(yè)中。當(dāng)我們給表添加索引后，表中的數(shù)據(jù)將根據(jù)索引進(jìn)行排序。
假設(shè)我們有一個(gè)表T_Pet，它包含四個(gè)字段分別是：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所示，從左往右的第一和第二層是索引頁(yè)，第三層是數(shù)據(jù)頁(yè)（葉節(jié)點(diǎn)），數(shù)據(jù)頁(yè)之間通過(guò)雙向鏈表連接起來(lái)，而且數(shù)據(jù)頁(yè)中的數(shù)據(jù)根據(jù)索引排序；假設(shè)，我們要查找名字（name）為Xnnbqba的動(dòng)物Ifcey，這里我們以animal作為表的索引，所以數(shù)據(jù)庫(kù)首先根據(jù)索引查找，當(dāng)找到索引值animal = ‘Ifcey時(shí)，接著查找該索引的數(shù)據(jù)頁(yè)（葉節(jié)點(diǎn)）獲取具體數(shù)據(jù)。具體的查詢語(yǔ)句如下：
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
當(dāng)我們執(zhí)行完SQL查詢計(jì)劃時(shí)，把鼠標(biāo)指針?lè)诺健熬奂饕檎摇鄙?，這時(shí)會(huì)出現(xiàn)如下圖信息，我們可以查看到一個(gè)重要的信息Logical Operation——Clustered Index Seek，SQL查詢是直接根據(jù)聚集索引獲取記錄，查詢速度最快。
從下圖查詢結(jié)果，我們發(fā)現(xiàn)查詢步驟只有2步，首先通過(guò)Clustered Index Seek快速地找到索引Ifcey，接著查詢索引的葉節(jié)點(diǎn)（數(shù)據(jù)頁(yè)）獲取數(shù)據(jù)。
查詢執(zhí)行時(shí)間：CPU 時(shí)間= 0 毫秒，占用時(shí)間= 1 毫秒。
現(xiàn)在我們把表中的索引刪除，重新執(zhí)行查詢計(jì)劃，這時(shí)我們可以發(fā)現(xiàn)Logical Operation已經(jīng)變?yōu)門able Scan，由于表中有100萬(wàn)行數(shù)據(jù)，這時(shí)查詢速度就相當(dāng)緩慢。
從下圖查詢結(jié)果，我們發(fā)現(xiàn)查詢步驟變成3步了，首先通過(guò)Table Scan查找animal = ‘Ifcey'，在執(zhí)行查詢的時(shí)候，SQL Server會(huì)自動(dòng)分析SQL語(yǔ)句，而且它估計(jì)我們這次查詢比較耗時(shí)，所以數(shù)據(jù)庫(kù)進(jìn)行并發(fā)操作加快查詢的速度。
查詢執(zhí)行時(shí)間：CPU 時(shí)間= 329 毫秒，占用時(shí)間= 182 毫秒。
通過(guò)上面的有聚集索引和沒(méi)有的對(duì)比，我們發(fā)現(xiàn)了查詢性能的差異，如果使用索引數(shù)據(jù)庫(kù)首先查找索引，而不是漫無(wú)目的的全表遍歷。
非聚集索引
在沒(méi)有聚集索引的情況下，表中的數(shù)據(jù)頁(yè)是通過(guò)堆(Heap)形式進(jìn)行存儲(chǔ)，堆是不含聚集索引的表；SQL Server中的堆存儲(chǔ)是把新的數(shù)據(jù)行存儲(chǔ)到最后一個(gè)頁(yè)中。
非聚集索引是物理存儲(chǔ)不按照索引排序，非聚集索引的葉節(jié)點(diǎn)（Index leaf pages）包含著指向具體數(shù)據(jù)行的指針或聚集索引，數(shù)據(jù)頁(yè)之間沒(méi)有連接是相對(duì)獨(dú)立的頁(yè)。
假設(shè)我們有一個(gè)表T_Pet，它包含四個(gè)字段分別是：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
如下圖所示，我們發(fā)現(xiàn)查詢計(jì)劃的最右邊有兩個(gè)步驟：RID和索引查找。由于這兩種查找方式相對(duì)于聚集索引查找要慢（Clustered Index Seek）。
首先SQL Server查找索引值，然后根據(jù)RID查找數(shù)據(jù)行，直到找到符合查詢條件的結(jié)果。
查詢執(zhí)行時(shí)間：CPU 時(shí)間= 0 毫秒，占用時(shí)間= 1 毫秒
堆表非聚集索引
由于堆是不含聚集索引的表，所以非聚集索引的葉節(jié)點(diǎn)將包含指向具體數(shù)據(jù)行的指針。
以前面的T_Pet表為例，假設(shè)T_Pet使用animal列作為非聚集索引，那么它的堆表非聚集索引結(jié)構(gòu)如下圖所示：
通過(guò)上圖，我們發(fā)現(xiàn)非聚集索引通過(guò)雙向鏈表連接，而且葉節(jié)點(diǎn)包含指向具體數(shù)據(jù)行的指針。
如果我們要查找animal = ‘Dog'的信息，首先我們遍歷第一層索引，然后數(shù)據(jù)庫(kù)判斷Dog屬于Cat范圍的索引，接著遍歷第二層索引，然后找到Dog索引獲取其中的保存的指針信息，根據(jù)指針信息獲取相應(yīng)數(shù)據(jù)頁(yè)中的數(shù)據(jù)，接下來(lái)我們將通過(guò)具體的例子說(shuō)明。
現(xiàn)在我們創(chuàng)建表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現(xiàn)在我們查找employee_id = 29976的員工信息。
SELECT *
FROM employees
WHERE employee_id = 29976
查詢計(jì)劃如下圖所示：
首先，查找索引值employee_id = ‘29976'的索引，然后根據(jù)RID查找符合條件的數(shù)據(jù)行；所以說(shuō)，堆表索引的查詢效率不如聚集表，接下來(lái)我們將介紹聚集表的非聚集索引。
聚集表非聚集索引
當(dāng)表上存在聚集索引時(shí)，任何非聚集索引的葉節(jié)點(diǎn)不再是包含指針值，而是包含聚集索引的索引值。
以前面的T_Pet表為例，假設(shè)T_Pet使用animal列作為非聚集索引，那么它的索引表非聚集索引結(jié)構(gòu)如下圖所示：
通過(guò)上圖，我們發(fā)現(xiàn)非聚集索引通過(guò)雙向鏈表連接，而且葉節(jié)點(diǎn)包含索引表的索引值。
如果我們要查找animal = ‘Dog'的信息，首先我們遍歷第一層索引，然后數(shù)據(jù)庫(kù)判斷Dog屬于Cat范圍的索引，接著遍歷第二層索引，然后找到Dog索引獲取其中的保存的索引值，然后根據(jù)索引值獲取相應(yīng)數(shù)據(jù)頁(yè)中的數(shù)據(jù)。
接下來(lái)我們修改之前的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
索引的有效性
SQL Server每執(zhí)行一個(gè)查詢，首先要檢查該查詢是否存在執(zhí)行計(jì)劃，如果沒(méi)有，則要生成一個(gè)執(zhí)行計(jì)劃，那么什么是執(zhí)行計(jì)劃呢？簡(jiǎn)單來(lái)說(shuō)，它能幫助SQL Server制定一個(gè)最優(yōu)的查詢計(jì)劃。（關(guān)于查詢計(jì)劃請(qǐng)參考這里）
下面我們將通過(guò)具體的例子說(shuō)明SQL Server中索引的使用，首先我們定義一個(gè)表testIndex，它包含三個(gè)字段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的數(shù)據(jù)行，而且表中bitValue = 0的數(shù)據(jù)有2000行。
SELECT *
FROM testIndex
WHERE bitValue = 0
圖17查詢計(jì)劃
現(xiàn)在我們查詢bitValue = 1的數(shù)據(jù)行。
SELECT *FROM testIndexWHERE bitValue = 1
圖18查詢計(jì)劃
現(xiàn)在我們注意到對(duì)同一個(gè)表不同數(shù)據(jù)查詢，居然執(zhí)行截然不同的查詢計(jì)劃，這究竟是什么原因?qū)е碌哪兀?
我們可以通過(guò)使用DBCC SHOW_STATISTICS查看到表中索引的詳細(xì)使用情況，具體SQL代碼如下：
UPDATE STATISTICS dbo.testIndex
DBCC SHOW_STATISTICS('dbo.testIndex', 'XtestIndex_bitValue')
WITH HISTOGRAM
圖19直方圖
通過(guò)上面的直方圖，我們知道SQL Server估計(jì)bitValue = 0數(shù)據(jù)行行有約19989行，而bitValue = 1估計(jì)約21；SQL Server優(yōu)化器根據(jù)數(shù)據(jù)量估算值，采取不同的執(zhí)行計(jì)劃，從而到達(dá)最優(yōu)的查詢性能，由于bitValue = 0數(shù)據(jù)量大，SQL Server只能提供掃描聚集索引獲取相應(yīng)數(shù)據(jù)行，而bitValue = 1實(shí)際數(shù)據(jù)行只有10行，SQL Server首先通過(guò)鍵查找bitValue = 1的數(shù)據(jù)行，然后嵌套循環(huán)聯(lián)接到聚集索引獲得余下數(shù)據(jù)行。
總結(jié) 完整實(shí)例代碼：
-- =============================================
-- 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
如果說(shuō)要對(duì)數(shù)據(jù)庫(kù)進(jìn)行優(yōu)化，我們主要可以通過(guò)以下五種方法，對(duì)數(shù)據(jù)庫(kù)系統(tǒng)進(jìn)行優(yōu)化。
1. 計(jì)算機(jī)硬件調(diào)優(yōu)
2. 應(yīng)用程序調(diào)優(yōu)
3. 數(shù)據(jù)庫(kù)索引優(yōu)化
4. SQL語(yǔ)句優(yōu)化
5. 事務(wù)處理調(diào)優(yōu)
在本篇博文中，我們將想大家講述數(shù)據(jù)庫(kù)中索引類型和使用場(chǎng)合，本文以SQL Server為例，對(duì)于其他技術(shù)平臺(tái)的朋友也是有參考價(jià)值的，只要替換相對(duì)應(yīng)的代碼就行了！
索引使數(shù)據(jù)庫(kù)引擎執(zhí)行速度更快，有針對(duì)性的數(shù)據(jù)檢索，而不是簡(jiǎn)單地整表掃描（Full table scan）。
為了使用有效的索引，我們必須對(duì)索引的構(gòu)成有所了解，而且我們知道在數(shù)據(jù)表中添加索引必然需要?jiǎng)?chuàng)建和維護(hù)索引表，所以我們要全局地衡量添加索引是否能提高數(shù)據(jù)庫(kù)系統(tǒng)的查詢性能。
在物理層面上，數(shù)據(jù)庫(kù)有數(shù)據(jù)文件組成，而這些數(shù)據(jù)文件可以組成文件組，然后存儲(chǔ)在磁盤上。每個(gè)文件包含許多區(qū)，每個(gè)區(qū)的大小為64K由八個(gè)物理上連續(xù)的頁(yè)組成（一個(gè)頁(yè)8K），我們知道頁(yè)是SQL Server數(shù)據(jù)庫(kù)中的數(shù)據(jù)存儲(chǔ)的基本單位。為數(shù)據(jù)庫(kù)中的數(shù)據(jù)文件（.mdf 或 .ndf）分配的磁盤空間可以從邏輯上劃分成頁(yè)（從0到n連續(xù)編號(hào)）。
頁(yè)中存儲(chǔ)的類型有：數(shù)據(jù)，索引和溢出。
文件和文件組
在SQL Server中，通過(guò)文件組這個(gè)邏輯對(duì)象對(duì)存放數(shù)據(jù)的文件進(jìn)行管理。
1.1.2 正文
在物理層面上，數(shù)據(jù)庫(kù)有數(shù)據(jù)文件組成，而這些數(shù)據(jù)文件可以組成文件組，然后存儲(chǔ)在磁盤上。每個(gè)文件包含許多區(qū)，每個(gè)區(qū)的大小為64K由八個(gè)物理上連續(xù)的頁(yè)組成（一個(gè)頁(yè)8K），我們知道頁(yè)是SQL Server數(shù)據(jù)庫(kù)中的數(shù)據(jù)存儲(chǔ)的基本單位。為數(shù)據(jù)庫(kù)中的數(shù)據(jù)文件（.mdf 或 .ndf）分配的磁盤空間可以從邏輯上劃分成頁(yè)（從0到n連續(xù)編號(hào)）。
頁(yè)中存儲(chǔ)的類型有：數(shù)據(jù)，索引和溢出。
文件和文件組
在SQL Server中，通過(guò)文件組這個(gè)邏輯對(duì)象對(duì)存放數(shù)據(jù)的文件進(jìn)行管理。
在頂層是我們的數(shù)據(jù)庫(kù)，由于數(shù)據(jù)庫(kù)是由一個(gè)或多個(gè)文件組組成，而文件組是由一個(gè)或多個(gè)文件組成的邏輯組，所以我們可以把文件組分散到不同的磁盤中，使用戶數(shù)據(jù)盡可能跨越多個(gè)設(shè)備，多個(gè)I/O 運(yùn)轉(zhuǎn)，避免 I/O 競(jìng)爭(zhēng)，從而均衡I/O負(fù)載，克服訪問(wèn)瓶頸。
區(qū)和頁(yè)
如圖2所示，文件是由區(qū)組成的，而區(qū)由八個(gè)物理上連續(xù)的頁(yè)組成，由于區(qū)的大小為64K，所以每當(dāng)增加一個(gè)區(qū)文件就增加64K。
頁(yè)中保存的數(shù)據(jù)類型有：表數(shù)據(jù)、索引數(shù)據(jù)、溢出數(shù)據(jù)、分配映射、頁(yè)空閑空間、索引分配等，具體如下圖所示：
|
頁(yè)類型 |
內(nèi)容 |
|
Data |
當(dāng) text in row 設(shè)置為 ON 時(shí)，包含除 text、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和 xml 數(shù)據(jù)之外的所有數(shù)據(jù)的數(shù)據(jù)行。 |
|
Index |
索引條目。 |
|
Text/Image |
大型對(duì)象數(shù)據(jù)類型：text 、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和 xml 數(shù)據(jù)。數(shù)據(jù)行超過(guò) 8 KB 時(shí)為可變長(zhǎng)度數(shù)據(jù)類型列：varchar 、nvarchar、varbinary 和 sql_variant |
|
Global Allocation Map、Shared Global Allocation Map |
有關(guān)區(qū)是否分配的信息。 |
|
Page Free Space |
有關(guān)頁(yè)分配和頁(yè)的可用空間的信息。 |
|
Index Allocation Map |
有關(guān)每個(gè)分配單元中表或索引所使用的區(qū)的信息。 |
|
Bulk Changed Map |
有關(guān)每個(gè)分配單元中自最后一條 BACKUP LOG 語(yǔ)句之后的大容量操作所修改的區(qū)的信息。 |
|
Differential Changed Map |
有關(guān)每個(gè)分配單元中自最后一條 BACKUP DATABASE 語(yǔ)句之后更改的區(qū)的信息。 |
在數(shù)據(jù)頁(yè)上，數(shù)據(jù)行緊接著頁(yè)頭（標(biāo)頭）按順序放置；頁(yè)頭包含標(biāo)識(shí)值，如頁(yè)碼或?qū)ο髷?shù)據(jù)的對(duì)象ID；數(shù)據(jù)行持有實(shí)際的數(shù)據(jù)；最后，頁(yè)的末尾是行偏移表，對(duì)于頁(yè)中的每一行，每個(gè)行偏移表都包含一個(gè)條目，每個(gè)條目記錄對(duì)應(yīng)行的第一個(gè)字節(jié)與頁(yè)頭的距離，行偏移表中的條目的順序與頁(yè)中行的順序相反。
索引的基本結(jié)構(gòu)
“索引（Index）提供查詢的速度”這是對(duì)索引的最基本的解釋，接下來(lái)我們將通過(guò)介紹索引的組成，讓大家對(duì)索引有更深入的理解。
索引是數(shù)據(jù)庫(kù)中的一個(gè)獨(dú)特的結(jié)構(gòu)，由于它保存數(shù)據(jù)庫(kù)信息，那么我們就需要給它分配磁盤空間和維護(hù)索引表。創(chuàng)建索引并不會(huì)改變表中的數(shù)據(jù)，它只是創(chuàng)建了一個(gè)新的數(shù)據(jù)結(jié)構(gòu)指向數(shù)據(jù)表；打個(gè)比方，平時(shí)我們使用字典查字時(shí)，首先我們要知道查詢單詞起始字母，然后翻到目錄頁(yè)，接著查找單詞具體在哪一頁(yè)，這時(shí)我們目錄就是索引表，而目錄項(xiàng)就是索引了。
當(dāng)然，索引比字典目錄更為復(fù)雜，因?yàn)閿?shù)據(jù)庫(kù)必須處理插入，刪除和更新等操作，這些操作將導(dǎo)致索引發(fā)生變化。
葉節(jié)點(diǎn)
假設(shè)我們磁盤上的數(shù)據(jù)是物理有序的，那么數(shù)據(jù)庫(kù)在進(jìn)行插入，刪除和更新操作時(shí)，必然會(huì)導(dǎo)致數(shù)據(jù)發(fā)生變化，如果我們要保存數(shù)據(jù)的連續(xù)和有序，那么我們就需要移動(dòng)數(shù)據(jù)的物理位置，這將增大磁盤的I/O，使得整個(gè)數(shù)據(jù)庫(kù)運(yùn)行非常緩慢；使用索引的主要目的是使數(shù)據(jù)邏輯有序，使數(shù)據(jù)獨(dú)立于物理有序存儲(chǔ)。
為了實(shí)現(xiàn)數(shù)據(jù)邏輯有序，索引使用雙向鏈表的數(shù)據(jù)結(jié)構(gòu)來(lái)保持?jǐn)?shù)據(jù)邏輯順序，如果要在兩個(gè)節(jié)點(diǎn)中插入一個(gè)新的節(jié)點(diǎn)只需修改節(jié)點(diǎn)的前驅(qū)和后繼，而且無(wú)需修改新節(jié)點(diǎn)的物理位置。
雙向鏈表（Doubly linked list）也叫雙鏈表，是鏈表的一種，它的每個(gè)數(shù)據(jù)結(jié)點(diǎn)中都有兩個(gè)指針，分別指向直接后繼和直接前驅(qū)。所以，從雙向鏈表中的任意一個(gè)結(jié)點(diǎn)開始，都可以很方便地訪問(wèn)它的前驅(qū)結(jié)點(diǎn)和后繼結(jié)點(diǎn)。
理論上說(shuō)，從雙向鏈表中刪除一個(gè)元素操作的時(shí)間復(fù)雜度是O(1)，如果希望刪除一個(gè)具體有給定關(guān)鍵字的元素，那么最壞的情況下的時(shí)間復(fù)雜度為O(n)。
在刪除的過(guò)程中，我們只需要將要?jiǎng)h除的節(jié)點(diǎn)的前節(jié)點(diǎn)和后節(jié)點(diǎn)相連，然后將要?jiǎng)h除的節(jié)點(diǎn)的前節(jié)點(diǎn)和后節(jié)點(diǎn)置為null即可。
復(fù)制代碼 代碼如下:
//偽代碼
node.prev.next=node.next;
node.next.prev=node.prev;
node.prev=node.next=null;
圖4索引的葉節(jié)點(diǎn)和相應(yīng)的表數(shù)據(jù)
如上圖4所示，索引葉節(jié)點(diǎn)包含索引值和相應(yīng)的RID（ROWID），而且葉節(jié)點(diǎn)通過(guò)雙向鏈表有序地連接起來(lái)；同時(shí)我們主要到數(shù)據(jù)表不同于索引葉節(jié)點(diǎn)，表中的數(shù)據(jù)無(wú)序存儲(chǔ)，它們不全是存儲(chǔ)在同一表塊中，而且塊之間不存在連接。
總的來(lái)說(shuō)，索引保存著具體數(shù)據(jù)的物理地址值。
索引的類型
我們知道索引的類型有兩種：聚集索引和非聚集索引。
聚集索引：物理存儲(chǔ)按照索引排序。
非聚集索引：物理存儲(chǔ)不按照索引排序。
聚集索引
聚集索引的數(shù)據(jù)頁(yè)是物理有序地存儲(chǔ)，數(shù)據(jù)頁(yè)是聚集索引的葉節(jié)點(diǎn)，數(shù)據(jù)頁(yè)之間通過(guò)雙向鏈表的形式連接起來(lái)，而且實(shí)際的數(shù)據(jù)都存儲(chǔ)在數(shù)據(jù)頁(yè)中。當(dāng)我們給表添加索引后，表中的數(shù)據(jù)將根據(jù)索引進(jìn)行排序。
假設(shè)我們有一個(gè)表T_Pet，它包含四個(gè)字段分別是：animal，name，sex和age，而且使用animal作為索引列，具體SQL代碼如下：
復(fù)制代碼 代碼如下:
-----------------------------------------------------------
---- 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.
-----------------------------------------------------------
復(fù)制代碼 代碼如下:
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所示，從左往右的第一和第二層是索引頁(yè)，第三層是數(shù)據(jù)頁(yè)（葉節(jié)點(diǎn)），數(shù)據(jù)頁(yè)之間通過(guò)雙向鏈表連接起來(lái)，而且數(shù)據(jù)頁(yè)中的數(shù)據(jù)根據(jù)索引排序；假設(shè)，我們要查找名字（name）為Xnnbqba的動(dòng)物Ifcey，這里我們以animal作為表的索引，所以數(shù)據(jù)庫(kù)首先根據(jù)索引查找，當(dāng)找到索引值animal = ‘Ifcey時(shí)，接著查找該索引的數(shù)據(jù)頁(yè)（葉節(jié)點(diǎn)）獲取具體數(shù)據(jù)。具體的查詢語(yǔ)句如下：
復(fù)制代碼 代碼如下:
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
當(dāng)我們執(zhí)行完SQL查詢計(jì)劃時(shí)，把鼠標(biāo)指針?lè)诺健熬奂饕檎摇鄙?，這時(shí)會(huì)出現(xiàn)如下圖信息，我們可以查看到一個(gè)重要的信息Logical Operation——Clustered Index Seek，SQL查詢是直接根據(jù)聚集索引獲取記錄，查詢速度最快。
從下圖查詢結(jié)果，我們發(fā)現(xiàn)查詢步驟只有2步，首先通過(guò)Clustered Index Seek快速地找到索引Ifcey，接著查詢索引的葉節(jié)點(diǎn)（數(shù)據(jù)頁(yè)）獲取數(shù)據(jù)。
查詢執(zhí)行時(shí)間：CPU 時(shí)間= 0 毫秒，占用時(shí)間= 1 毫秒。
現(xiàn)在我們把表中的索引刪除，重新執(zhí)行查詢計(jì)劃，這時(shí)我們可以發(fā)現(xiàn)Logical Operation已經(jīng)變?yōu)門able Scan，由于表中有100萬(wàn)行數(shù)據(jù)，這時(shí)查詢速度就相當(dāng)緩慢。
從下圖查詢結(jié)果，我們發(fā)現(xiàn)查詢步驟變成3步了，首先通過(guò)Table Scan查找animal = ‘Ifcey'，在執(zhí)行查詢的時(shí)候，SQL Server會(huì)自動(dòng)分析SQL語(yǔ)句，而且它估計(jì)我們這次查詢比較耗時(shí)，所以數(shù)據(jù)庫(kù)進(jìn)行并發(fā)操作加快查詢的速度。
查詢執(zhí)行時(shí)間：CPU 時(shí)間= 329 毫秒，占用時(shí)間= 182 毫秒。
通過(guò)上面的有聚集索引和沒(méi)有的對(duì)比，我們發(fā)現(xiàn)了查詢性能的差異，如果使用索引數(shù)據(jù)庫(kù)首先查找索引，而不是漫無(wú)目的的全表遍歷。
非聚集索引
在沒(méi)有聚集索引的情況下，表中的數(shù)據(jù)頁(yè)是通過(guò)堆(Heap)形式進(jìn)行存儲(chǔ)，堆是不含聚集索引的表；SQL Server中的堆存儲(chǔ)是把新的數(shù)據(jù)行存儲(chǔ)到最后一個(gè)頁(yè)中。
非聚集索引是物理存儲(chǔ)不按照索引排序，非聚集索引的葉節(jié)點(diǎn)（Index leaf pages）包含著指向具體數(shù)據(jù)行的指針或聚集索引，數(shù)據(jù)頁(yè)之間沒(méi)有連接是相對(duì)獨(dú)立的頁(yè)。
假設(shè)我們有一個(gè)表T_Pet，它包含四個(gè)字段分別是：animal，name，sex和age，而且使用animal作為非索引列，具體SQL代碼如下：
復(fù)制代碼 代碼如下:
-----------------------------------------------------------
---- 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代碼如下：
復(fù)制代碼 代碼如下:
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
如下圖所示，我們發(fā)現(xiàn)查詢計(jì)劃的最右邊有兩個(gè)步驟：RID和索引查找。由于這兩種查找方式相對(duì)于聚集索引查找要慢（Clustered Index Seek）。
首先SQL Server查找索引值，然后根據(jù)RID查找數(shù)據(jù)行，直到找到符合查詢條件的結(jié)果。
查詢執(zhí)行時(shí)間：CPU 時(shí)間= 0 毫秒，占用時(shí)間= 1 毫秒
堆表非聚集索引
由于堆是不含聚集索引的表，所以非聚集索引的葉節(jié)點(diǎn)將包含指向具體數(shù)據(jù)行的指針。
以前面的T_Pet表為例，假設(shè)T_Pet使用animal列作為非聚集索引，那么它的堆表非聚集索引結(jié)構(gòu)如下圖所示：
通過(guò)上圖，我們發(fā)現(xiàn)非聚集索引通過(guò)雙向鏈表連接，而且葉節(jié)點(diǎn)包含指向具體數(shù)據(jù)行的指針。
如果我們要查找animal = ‘Dog'的信息，首先我們遍歷第一層索引，然后數(shù)據(jù)庫(kù)判斷Dog屬于Cat范圍的索引，接著遍歷第二層索引，然后找到Dog索引獲取其中的保存的指針信息，根據(jù)指針信息獲取相應(yīng)數(shù)據(jù)頁(yè)中的數(shù)據(jù)，接下來(lái)我們將通過(guò)具體的例子說(shuō)明。
現(xiàn)在我們創(chuàng)建表employees，然后給該表添加堆表非聚集索引，具體SQL代碼如下：
復(fù)制代碼 代碼如下:
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現(xiàn)在我們查找employee_id = 29976的員工信息。
復(fù)制代碼 代碼如下:
SELECT *
FROM employees
WHERE employee_id = 29976
查詢計(jì)劃如下圖所示：
首先，查找索引值employee_id = ‘29976'的索引，然后根據(jù)RID查找符合條件的數(shù)據(jù)行；所以說(shuō)，堆表索引的查詢效率不如聚集表，接下來(lái)我們將介紹聚集表的非聚集索引。
聚集表非聚集索引
當(dāng)表上存在聚集索引時(shí)，任何非聚集索引的葉節(jié)點(diǎn)不再是包含指針值，而是包含聚集索引的索引值。
以前面的T_Pet表為例，假設(shè)T_Pet使用animal列作為非聚集索引，那么它的索引表非聚集索引結(jié)構(gòu)如下圖所示：
通過(guò)上圖，我們發(fā)現(xiàn)非聚集索引通過(guò)雙向鏈表連接，而且葉節(jié)點(diǎn)包含索引表的索引值。
如果我們要查找animal = ‘Dog'的信息，首先我們遍歷第一層索引，然后數(shù)據(jù)庫(kù)判斷Dog屬于Cat范圍的索引，接著遍歷第二層索引，然后找到Dog索引獲取其中的保存的索引值，然后根據(jù)索引值獲取相應(yīng)數(shù)據(jù)頁(yè)中的數(shù)據(jù)。
接下來(lái)我們修改之前的employees表，首先我們刪除之前的堆表非聚集索引，然后增加索引表的非聚集索引，具體SQL代碼如下：
復(fù)制代碼 代碼如下:
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
索引的有效性
SQL Server每執(zhí)行一個(gè)查詢，首先要檢查該查詢是否存在執(zhí)行計(jì)劃，如果沒(méi)有，則要生成一個(gè)執(zhí)行計(jì)劃，那么什么是執(zhí)行計(jì)劃呢？簡(jiǎn)單來(lái)說(shuō)，它能幫助SQL Server制定一個(gè)最優(yōu)的查詢計(jì)劃。（關(guān)于查詢計(jì)劃請(qǐng)參考這里）
下面我們將通過(guò)具體的例子說(shuō)明SQL Server中索引的使用，首先我們定義一個(gè)表testIndex，它包含三個(gè)字段testIndex，bitValue和filler，具體的SQL代碼如下：
復(fù)制代碼 代碼如下:
-----------------------------------------------------------
---- 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的數(shù)據(jù)行，而且表中bitValue = 0的數(shù)據(jù)有2000行。
復(fù)制代碼 代碼如下:
SELECT *
FROM testIndex
WHERE bitValue = 0
圖17查詢計(jì)劃 現(xiàn)在我們查詢bitValue = 1的數(shù)據(jù)行。
SELECT *FROM testIndexWHERE bitValue = 1
圖18查詢計(jì)劃
現(xiàn)在我們注意到對(duì)同一個(gè)表不同數(shù)據(jù)查詢，居然執(zhí)行截然不同的查詢計(jì)劃，這究竟是什么原因?qū)е碌哪兀?
我們可以通過(guò)使用DBCC SHOW_STATISTICS查看到表中索引的詳細(xì)使用情況，具體SQL代碼如下：
復(fù)制代碼 代碼如下:
UPDATE STATISTICS dbo.testIndex
DBCC SHOW_STATISTICS('dbo.testIndex', 'XtestIndex_bitValue')
WITH HISTOGRAM
圖19直方圖
通過(guò)上面的直方圖，我們知道SQL Server估計(jì)bitValue = 0數(shù)據(jù)行行有約19989行，而bitValue = 1估計(jì)約21；SQL Server優(yōu)化器根據(jù)數(shù)據(jù)量估算值，采取不同的執(zhí)行計(jì)劃，從而到達(dá)最優(yōu)的查詢性能，由于bitValue = 0數(shù)據(jù)量大，SQL Server只能提供掃描聚集索引獲取相應(yīng)數(shù)據(jù)行，而bitValue = 1實(shí)際數(shù)據(jù)行只有10行，SQL Server首先通過(guò)鍵查找bitValue = 1的數(shù)據(jù)行，然后嵌套循環(huán)聯(lián)接到聚集索引獲得余下數(shù)據(jù)行。
總結(jié) 完整實(shí)例代碼：
復(fù)制代碼 代碼如下:
-- =============================================
-- 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
相關(guān)文章
SQLServer更改sa用戶名的方法，需要的朋友可以參考下。2010-11-11
這篇文章主要給大家介紹了關(guān)于SQL?Server啟用sa賬戶的簡(jiǎn)單圖文教程,因?yàn)榘惭b數(shù)據(jù)的時(shí)候沒(méi)有啟用sa賬戶,這里記錄一下如何啟用,需要的朋友可以參考下2023-09-09
查詢SQL Server Index上次Rebuild時(shí)間的方法
這篇文章主要介紹了查詢SQL Server Index上次Rebuild時(shí)間的方法,本文直接給出實(shí)現(xiàn)腳本代碼,需要的朋友可以參考下2015-07-07
這篇文章主要為大家詳細(xì)介紹了SQL服務(wù)器無(wú)法啟動(dòng)的解決方法，以及SQLserverAgent無(wú)法啟動(dòng)的解決方法2017-03-03
自動(dòng)清理 MSSQL Server Table Collation問(wèn)題的解決方法
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation2013-02-02
mssql查找備注(text,ntext)類型字段為空的方法
在sql語(yǔ)句中，如果查找某個(gè)文本字段值為空的，可以用select * from 表 where 字段='' ，但是如果這個(gè)字段數(shù)據(jù)類型是text或者ntext，那上面的sql語(yǔ)句就要出錯(cuò)了。2008-08-08
SQL學(xué)習(xí)筆記七函數(shù) 數(shù)字，日期，類型轉(zhuǎn)換，空值處理，case
SQL學(xué)習(xí)筆記七函數(shù) 數(shù)字，日期，類型轉(zhuǎn)換，空值處理，case 使用說(shuō)明2011-08-08
SQL將一個(gè)表中的數(shù)據(jù)插入到另一個(gè)表中的方法
這篇文章介紹了SQL將一個(gè)表中的數(shù)據(jù)插入到另一個(gè)表中的方法，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2022-02-02
SQL(MSSQLSERVER)服務(wù)啟動(dòng)錯(cuò)誤代碼3414的解決方法
這篇文章主要介紹了SQL(MSSQLSERVER)服務(wù)啟動(dòng)錯(cuò)誤代碼3414的解決方法,需要的朋友可以參考下2016-03-03
SQLSERVERS 數(shù)據(jù)整理方法，需要的朋友可以參考下。2009-11-11