玩轉(zhuǎn)數(shù)據(jù)庫(kù)索引

本篇文章將向大家介紹數(shù)據(jù)庫(kù)中索引類型和使用場(chǎng)合，本文以SQL Server為例，對(duì)于其他技術(shù)平臺(tái)的朋友也是有參考價(jià)值的，原理差不多。查詢數(shù)據(jù)時(shí)索引使數(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)的查詢性能。

1、概述

通常我們要對(duì)數(shù)據(jù)庫(kù)進(jìn)行優(yōu)化，主要可以通過(guò)以下五種方法。

計(jì)算機(jī)硬件調(diào)優(yōu)
應(yīng)用程序調(diào)優(yōu)
數(shù)據(jù)庫(kù)索引優(yōu)化
SQL語(yǔ)句優(yōu)化
事務(wù)處理調(diào)優(yōu)

數(shù)據(jù)庫(kù)優(yōu)化

本篇文章將向大家介紹數(shù)據(jù)庫(kù)中索引類型和使用場(chǎng)合，本文以SQL Server為例，對(duì)于其他技術(shù)平臺(tái)的朋友也是有參考價(jià)值的，原理差不多。

查詢數(shù)據(jù)時(shí)索引使數(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)的查詢性能。

2、數(shù)據(jù)庫(kù)中的文件和文件組

在物理層面上，數(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)瓶頸。

3、區(qū)和頁(yè)

如下圖所示，文件是由區(qū)組成的，而區(qū)由八個(gè)物理上連續(xù)的頁(yè)組成，由于區(qū)的大小為64K，所以每當(dāng)增加一個(gè)區(qū)文件就增加64K。

區(qū)和頁(yè)

頁(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è)中行的順序相反。

數(shù)據(jù)頁(yè)

4、索引的基本結(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;

索引的葉節(jié)點(diǎn)和相應(yīng)的表數(shù)據(jù)

如上圖，索引葉節(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ù)的物理地址值。

5、索引的類型

索引的類型主要有兩種：聚集索引和非聚集索引。

聚集索引：物理存儲(chǔ)按照索引排序。

指數(shù)據(jù)庫(kù)表行中數(shù)據(jù)的物理順序與鍵值的邏輯（索引）順序相同。一個(gè)表只能有一個(gè)聚集索引，因?yàn)橐粋€(gè)表的物理順序只有一種情況，所以，對(duì)應(yīng)的聚集索引只能有一個(gè)。如果某索引不是聚集索引，則表中的行物理順序與索引順序不匹配，與非聚集索引相比，聚集索引有著更快的檢索速度。

非聚集索引：物理存儲(chǔ)不按照索引排序。

該索引中索引的邏輯順序與磁盤上行的物理存儲(chǔ)順序不同，一個(gè)表中可以擁有多個(gè)非聚集索引。除了聚集索引以外的索引都是非聚集索引，只是人們想細(xì)分一下非聚集索引，分成普通索引，唯一索引，全文索引。如果非要把非聚集索引類比成現(xiàn)實(shí)生活中的東西，那么非聚集索引就像新華字典的偏旁字典，他結(jié)構(gòu)順序與實(shí)際存放順序不一定一致。

5.1、聚集索引

聚集索引的數(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)

聚集索引

如上圖所示，從左往右的第一和第二層是索引頁(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ù)聚集索引獲取記錄，查詢速度最快。

查詢計(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 毫秒。

查詢結(jié)果

現(xiàn)在我們把表中的索引刪除，重新執(zhí)行查詢計(jì)劃，這時(shí)我們可以發(fā)現(xiàn)Logical Operation已經(jīng)變?yōu)門able Scan，由于表中有100萬(wàn)行數(shù)據(jù)，這時(shí)查詢速度就相當(dāng)緩慢。

查詢計(jì)劃

從下圖查詢結(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 毫秒。

查詢結(jié)果

通過(guò)上面的有聚集索引和沒(méi)有的對(duì)比，我們發(fā)現(xiàn)了查詢性能的差異，如果使用索引數(shù)據(jù)庫(kù)首先查找索引，而不是漫無(wú)目的的全表遍歷。

5.2、非聚集索引

在沒(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)

非聚集索引

接著我們要查詢表中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）。

查詢計(jì)劃

首先SQL Server查找索引值，然后根據(jù)RID查找數(shù)據(jù)行，直到找到符合查詢條件的結(jié)果。

查詢執(zhí)行時(shí)間：CPU 時(shí)間= 0 毫秒，占用時(shí)間= 1 毫秒

查詢結(jié)果

5.3、堆表非聚集索引

由于堆是不含聚集索引的表，所以非聚集索引的葉節(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ì)劃如下圖所示：

查詢計(jì)劃

首先，查找索引值employee_id = ‘29976’的索引，然后根據(jù)RID查找符合條件的數(shù)據(jù)行；所以說(shuō)，堆表索引的查詢效率不如聚集表，接下來(lái)我們將介紹聚集表的非聚集索引。

5.4、聚集表非聚集索引

當(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

查詢計(jì)劃

6、索引的有效性

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ò)具體的例子說(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

查詢計(jì)劃

現(xiàn)在我們查詢bitValue = 1的數(shù)據(jù)行。

SELECT *
FROM   testIndex
WHERE  bitValue = 1

查詢計(jì)劃

現(xiàn)在我們注意到對(duì)同一個(gè)表不同數(shù)據(jù)查詢，居然執(zhí)行截然不同的查詢計(jì)劃，這究竟是什么原因?qū)е碌哪兀?/p>

我們可以通過(guò)使用DBCC SHOW_STATISTICS查看到表中索引的詳細(xì)使用情況，具體SQL代碼如下：

UPDATE STATISTICS dbo.testIndex
DBCC SHOW_STATISTICS('dbo.testIndex', 'XtestIndex_bitValue')
WITH HISTOGRAM

直方圖

通過(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ù)行。

7、索引的優(yōu)缺點(diǎn)

優(yōu)點(diǎn)
第一，通過(guò)創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫(kù)表中每一行數(shù)據(jù)的唯一性。
第二，可以大大加快數(shù)據(jù)的檢索速度，這也是創(chuàng)建索引的最主要的原因。
第三，可以加速表和表之間的連接，特別是在實(shí)現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義。
第四，在使用分組和排序子句進(jìn)行數(shù)據(jù)檢索時(shí)，同樣可以顯著減少查詢中分組和排序的時(shí)間。
第五，通過(guò)使用索引，可以在查詢的過(guò)程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能。

缺點(diǎn)
第一，創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間，這種時(shí)間隨著數(shù)據(jù)量的增加而增加。
第二，索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個(gè)索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會(huì)更大。
第三，當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)的維護(hù)，這樣就降低了數(shù)據(jù)的維護(hù)速度。

8、索引創(chuàng)建原則

1.定義主鍵的數(shù)據(jù)列一定要建立索引。

2.定義有外鍵的數(shù)據(jù)列一定要建立索引。

3.對(duì)于經(jīng)常查詢的數(shù)據(jù)列最好建立索引。

4.對(duì)于需要在指定范圍內(nèi)的快速或頻繁查詢的數(shù)據(jù)列;

5.經(jīng)常用在WHERE子句中的數(shù)據(jù)列。

6.經(jīng)常出現(xiàn)在關(guān)鍵字order by、group by、distinct后面的字段。如果建立的是復(fù)合索引，索引的字段順序要和這些關(guān)鍵字后面的字段順序一致，否則索引不會(huì)被使用。

7.對(duì)于那些查詢中很少涉及的列，重復(fù)值比較多的列不要建立索引。

8.對(duì)于定義為text、image和bit的數(shù)據(jù)類型的列不要建立索引。

9.對(duì)于經(jīng)常存取的列不要建立索引

10.限制表上的索引數(shù)目。對(duì)一個(gè)存在大量更新操作的表，所建索引的數(shù)目一般不要超過(guò)3個(gè)，最多不要超過(guò)5個(gè)。索引雖說(shuō)提高了訪問(wèn)速度，但太多索引會(huì)影響數(shù)據(jù)的更新操作。

11.對(duì)復(fù)合索引，按照字段在查詢條件中出現(xiàn)的頻度建立索引。在復(fù)合索引中，記錄首先按照第一個(gè)字段排序。對(duì)于在第一個(gè)字段上取值相同的記錄，系統(tǒng)再按照第二個(gè)字段的取值排序，以此類推。因此只有復(fù)合索引的第一個(gè)字段出現(xiàn)在查詢條件中，該索引才可能被使用,因此將應(yīng)用頻度高的字段，放置在復(fù)合索引的前面，會(huì)使系統(tǒng)最大可能地使用此索引，發(fā)揮索引的作用。