1． 數(shù)據(jù)庫(kù)的數(shù)據(jù)存儲(chǔ)

1.1文件：

我們一旦創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)，都會(huì)生成兩個(gè)文件：

DataBaseName.mdf: 主文件，這是數(shù)據(jù)庫(kù)中的數(shù)據(jù)最終存放的地方。

DataBaseName.ldf:日志文件，由數(shù)據(jù)操作產(chǎn)生的一系列日志記錄。

1.2分區(qū)：

在一個(gè)給定的文件中，為表和索引分配空間的基本存儲(chǔ)單位。 1個(gè)區(qū)占64KB,由8個(gè)連續(xù)的頁(yè)組成。 如果一個(gè)分區(qū)已滿(mǎn)，但需存一條新的記錄，那么該記錄將占用整個(gè)新分區(qū)的空間。

1.3 頁(yè)：

分區(qū)中的一個(gè)分配單位。這是實(shí)際數(shù)據(jù)行最終存放的地方。 頁(yè)用于存儲(chǔ)數(shù)據(jù)行。

Sql Server有多種類(lèi)型的頁(yè)：

Data, Index,BLOB,GAM(Global Allocation Map),SGAM,PFS(Page Free Space),IAM(Index Allocation Map),BCM(Bulk Changed Map)等。

 2． 索引

 2.1.1索引

索引是與表或視圖關(guān)聯(lián)的磁盤(pán)上結(jié)構(gòu)，可以加快從表或視圖中檢索行的速度。索引包含由表或視圖中的一列或多列生成的鍵。這些鍵存儲(chǔ)在一個(gè)結(jié)構(gòu)（B 樹(shù)）中，使 SQL Server 可以快速有效地查找與鍵值關(guān)聯(lián)的行。

 通俗點(diǎn)說(shuō)，索引與表或視圖相關(guān)，旨在加快檢索速度。索引本身占據(jù)存儲(chǔ)空間，通過(guò)索引，數(shù)據(jù)便會(huì)以B樹(shù)形式存儲(chǔ)。因此也加快了查詢(xún)速度。

 2.1.2聚集索引

聚集索引根據(jù)數(shù)據(jù)行的鍵值在表或視圖中排序和存儲(chǔ)這些數(shù)據(jù)行。索引定義中包含聚集索引列。每個(gè)表只能有一個(gè)聚集索引，因?yàn)閿?shù)據(jù)行本身只能按一個(gè)順序排序。只有當(dāng)表包含聚集索引時(shí)，表中的數(shù)據(jù)行才按排序順序存儲(chǔ)。如果表具有聚集索引，則該表稱(chēng)為聚集表。如果表沒(méi)有聚集索引，則其數(shù)據(jù)行存儲(chǔ)在一個(gè)稱(chēng)為堆的無(wú)序結(jié)構(gòu)中。

 通俗點(diǎn)說(shuō)，聚集索引的頁(yè)存儲(chǔ)的是實(shí)際數(shù)據(jù)。每個(gè)表只能建立唯一的聚集索引，但也可以沒(méi)有。

如果建立聚集索引，那么表中數(shù)據(jù)以B樹(shù)形式存儲(chǔ)數(shù)據(jù)。

 對(duì)于聚集索引的理解，打個(gè)比方，即英文字典的單詞編排。 英文字典單詞以A,B,C,D….X,Y,Z的形式順序編排，如果我們查找 Good 單詞，我們首先定位到G，然后定位o – o-d. 最終查找到Good，便是good實(shí)際存在的地方。

 建聚集索引需要至少相當(dāng)該表120%的附加空間，以存放該表的副本和索引中間頁(yè)。

 2.1.3非聚集索引

非聚集索引具有獨(dú)立于數(shù)據(jù)行的結(jié)構(gòu)。非聚集索引包含非聚集索引鍵值，并且每個(gè)鍵值項(xiàng)都有指向包含該鍵值的數(shù)據(jù)行的指針。

從非聚集索引中的索引行指向數(shù)據(jù)行的指針?lè)Q為行定位器。行定位器的結(jié)構(gòu)取決于數(shù)據(jù)頁(yè)是存儲(chǔ)在堆中還是聚集表中。對(duì)于堆，行定位器是指向行的指針。對(duì)于聚集表，行定位器是聚集索引鍵。

 通俗點(diǎn)說(shuō)，非聚集索引的頁(yè)存儲(chǔ)的是不是實(shí)際數(shù)據(jù)，而是實(shí)際數(shù)據(jù)的地址。一個(gè)表可以存在多個(gè)非聚集索引。在Sql Server2005中，每個(gè)表最多可以建立249個(gè)，而在Sql server2008中，則最多可以建立999個(gè)非聚集索引。

 對(duì)于非聚集索引的理解，即新華字典的“偏旁部首”查字法。遇到您不認(rèn)識(shí)的字，不知道它的發(fā)音，這時(shí)候，您就不能按照剛才的方法找到您要查的字，而需要去根據(jù)“偏旁部首”查到您要找的字，然后根據(jù)這個(gè)字后的頁(yè)碼直接翻到某頁(yè)來(lái)找到您要找的字。但您結(jié)合“部首目錄”和“檢字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“張”字，我們可以看到在查部首之后的檢字表中“張”的頁(yè)碼是672頁(yè)，檢字表中“張”的上面是“馳”字，但頁(yè)碼卻是63頁(yè)，“張”的下面是“弩”字，頁(yè)面是390頁(yè)。很顯然，這些字并不是真正的分別位于“張”字的上下方，現(xiàn)在您看到的連續(xù)的“馳、張、弩”三字實(shí)際上就是他們?cè)诜蔷奂饕械呐判颍亲值湔闹械淖衷诜蔷奂饕械挠成?。我們可以通過(guò)這種方式來(lái)找到您所需要的字，但它需要兩個(gè)過(guò)程，先找到目錄中的結(jié)果，然后再翻到您所需要的頁(yè)碼。我們把這種目錄純粹是目錄，正文純粹是正文的排序方式稱(chēng)為“非聚集索引”。

 2.1.4 覆蓋索引：

覆蓋索引是指那些索引項(xiàng)中包含查尋所需要的全部信息的非聚集索引，這種索引之所以比較快也正是因?yàn)樗饕?yè)中包含了查尋所必須的數(shù)據(jù),不需去訪(fǎng)問(wèn)數(shù)據(jù)頁(yè)。 如果非聚簇索引中包含結(jié)果數(shù)據(jù),那么它的查詢(xún)速度將快于聚集索引。

但是由于覆蓋索引的索引項(xiàng)比較多,要占用比較大的空間。而且update 操作會(huì)引起索引值改變。所以如果潛在的覆蓋查詢(xún)并不常用或不太關(guān)鍵，則覆蓋索引的增加反而會(huì)降低性能。

 2.1.5 主鍵和索引

主鍵：表通常具有包含唯一標(biāo)識(shí)表中每一行的值的一列或一組列。這樣的一列或多列稱(chēng)為表的主鍵 (PK)，用于強(qiáng)制表的實(shí)體完整性。在創(chuàng)建或修改表時(shí)，您可以通過(guò)定義 PRIMARY KEY 約束來(lái)創(chuàng)建主鍵。 它是一種唯一索引。

下面是一個(gè)簡(jiǎn)單的比較表

	主鍵	聚集索引
用途	強(qiáng)制表的實(shí)體完整性	對(duì)數(shù)據(jù)行的排序，方便查詢(xún)用
一個(gè)表多少個(gè)	一個(gè)表最多一個(gè)主鍵	一個(gè)表最多一個(gè)聚集索引
是否允許多個(gè)字段來(lái)定義	一個(gè)主鍵可以多個(gè)字段來(lái)定義	一個(gè)索引可以多個(gè)字段來(lái)定義
是否允許 null 數(shù)據(jù)行出現(xiàn)	如果要?jiǎng)?chuàng)建的數(shù)據(jù)列中數(shù)據(jù)存在null，無(wú)法建立主鍵。 創(chuàng)建表時(shí)指定的 PRIMARY KEY 約束列隱式轉(zhuǎn)換為 NOT NULL。	沒(méi)有限制建立聚集索引的列一定必須 not null . 也就是可以列的數(shù)據(jù)是 null 參看最后一項(xiàng)比較
是否要求數(shù)據(jù)必須唯一	要求數(shù)據(jù)必須唯一	數(shù)據(jù)即可以唯一，也可以不唯一。看你定義這個(gè)索引的 UNIQUE 設(shè)置。 （這一點(diǎn)需要看后面的一個(gè)比較，雖然你的數(shù)據(jù)列可能不唯一，但是系統(tǒng)會(huì)替你產(chǎn)生一個(gè)你看不到的唯一列）
創(chuàng)建的邏輯	數(shù)據(jù)庫(kù)在創(chuàng)建主鍵同時(shí)，會(huì)自動(dòng)建立一個(gè)唯一索引。 如果這個(gè)表之前沒(méi)有聚集索引，同時(shí)建立主鍵時(shí)候沒(méi)有強(qiáng)制指定使用非聚集索引，則建立主鍵時(shí)候，同時(shí)建立一個(gè)唯一的聚集索引	如果未使用 UNIQUE 屬性創(chuàng)建聚集索引，數(shù)據(jù)庫(kù)引擎 將向表自動(dòng)添加一個(gè)四字節(jié) uniqueifier 列。 必要時(shí)，數(shù)據(jù)庫(kù)引擎 將向行自動(dòng)添加一個(gè) uniqueifier 值，使每個(gè)鍵唯一。此列和列值供內(nèi)部使用，用戶(hù)不能查看或訪(fǎng)問(wèn)。

  2.2 索引的存儲(chǔ)結(jié)構(gòu)

 2.1.1 整表掃描和索引掃描

整表掃描和索引掃描是Sql Server數(shù)據(jù)庫(kù)檢索到數(shù)據(jù)的唯一的兩種方式。除此之外，沒(méi)有第三種方式供Sql Server檢索到數(shù)據(jù)。

 整表掃描

最直接的檢索方式， Sql Server進(jìn)行表掃描時(shí)，會(huì)從表頭開(kāi)始掃描，直到整個(gè)表結(jié)束。 當(dāng)找到符合條件的記錄，便把該記錄存在結(jié)果集中。對(duì)于小數(shù)據(jù)量的表，這是一種很快捷的方式。如果沒(méi)有為表創(chuàng)建索引，那么Sql server便按這種方式檢索數(shù)據(jù)。

 索引掃描

如果為表創(chuàng)建了索引，在進(jìn)行檢索前，Sql Server優(yōu)化器會(huì)根據(jù)查詢(xún)條件，從可用的索引中選擇最優(yōu)化的索引。檢索時(shí)，便會(huì)遍歷B樹(shù)，當(dāng)找到符合條件的記錄，便把該記錄存在結(jié)果集中。因此，檢索大數(shù)據(jù)量的表，使用索引相對(duì)于整表掃描會(huì)顯著地提高性能。

 2.1.2 B-Tree

 

 2.2.3 聚集索引

 

   葉子節(jié)點(diǎn)存放的是實(shí)際的數(shù)據(jù)。索引的入口點(diǎn)存放在master->sys.indexes中。

 2.2.4 非聚集索引

 2．4．1 堆上的非聚集索引（Non-clustered index on heap）

 

與聚集索引很類(lèi)似。

不同處在：

葉子節(jié)點(diǎn)存放的不是實(shí)際數(shù)據(jù)，而是指向?qū)嶋H數(shù)據(jù)的指針。檢索速度非常接近于聚集索引，比起聚集索引，實(shí)際上只是多一步由根據(jù)指針檢索到實(shí)際數(shù)據(jù)的過(guò)程。

 2．4．2 聚集表上的非聚集索引

 3． 管理索引

3．1 創(chuàng)建

CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED]

INDEX <index name> ON <table or view name>(<column name> [ASC|DESC] [,...n])

INCLUDE (<column name> [, ...n])

[WITH

[PAD_INDEX = { ON | OFF }]

[[,] FILLFACTOR = <fillfactor>]

[[,] IGNORE_DUP_KEY = { ON | OFF }]

[[,] DROP_EXISTING = { ON | OFF }]

[[,] STATISTICS_NORECOMPUTE = { ON | OFF }]

[[,] SORT_IN_TEMPDB = { ON | OFF }]

[[,] ONLINE = { ON | OFF }

[[,] ALLOW_ROW_LOCKS = { ON | OFF }

[[,] ALLOW_PAGE_LOCKS = { ON | OFF }

[[,] MAXDOP =

]

[ON {<filegroup> | <partition scheme name> | DEFAULT }]

 3．2 修改

ALTER INDEX { <name of index> | ALL }

ON <table or view name>

{ REBUILD

[ [ WITH (

[ PAD_INDEX = { ON | OFF } ]

| [[,] FILLFACTOR = <fillfactor>

| [[,] SORT_IN_TEMPDB = { ON | OFF } ]

| [[,] IGNORE_DUP_KEY = { ON | OFF } ]

| [[,] STATISTICS_NORECOMPUTE = { ON | OFF } ]

| [[,] ONLINE = { ON | OFF } ]

| [[,] ALLOW_ROW_LOCKS = { ON | OFF } ]

| [[,] ALLOW_PAGE_LOCKS = { ON | OFF } ]

| [[,] MAXDOP =

) ]

| [ PARTITION = <partition number>

[ WITH ( <partition rebuild index option>

[ ,...n ] ) ] ] ]

| DISABLE

| REORGANIZE

[ PARTITION = <partition number> ]

[ WITH ( LOB_COMPACTION = { ON | OFF } ) ]

| SET ([ ALLOW_ROW_LOCKS= { ON | OFF } ]

| [[,] ALLOW_PAGE_LOCKS = { ON | OFF } ]

| [[,] IGNORE_DUP_KEY = { ON | OFF } ]

| [[,] STATISTICS_NORECOMPUTE = { ON | OFF } ]

)

} [ ; ]

 3．3 刪除

 DROP INDEX <table name>.<index name>

  4． 使用索引應(yīng)注意十么

1）聚集索引通常速度優(yōu)于非聚集索引

2) 建索引時(shí)應(yīng)考慮是否有足夠的空間。索引占據(jù)空間,平均約1.2倍數(shù)據(jù)庫(kù)本身大小。

3) 在經(jīng)常用于查詢(xún)或聚合條件的字段上建立聚集索引。這類(lèi)查詢(xún)條件包括 between, >, <,group by, max,min, count等。

4) 不要在經(jīng)常作為插入，且插入字段無(wú)序的列上建立聚集索引。 插入數(shù)據(jù)行會(huì)涉及分頁(yè)，rebuild索引會(huì)消耗大量時(shí)間。參考文末"一個(gè)不恰當(dāng)使用聚集索引的例子"。   

5) 在值高度的唯一性字段上建立索引。不能在諸如性別的字段上建立索引。

6) 只有作為索引的第一個(gè)列包含在查詢(xún)條件中，該索引才的作用。

    打個(gè)比方，我們用偏旁+部首來(lái)查漢字，那么偏旁首先必須包括在查詢(xún)條件中，只有先定位偏旁，再結(jié)合部首，才能發(fā)揮偏旁+部首來(lái)檢索的快速功效。

7) 刪除一直不用的索引。特別是對(duì)于刪除和修改比較頻繁的數(shù)據(jù)表，必須考慮如何精華索引。

 

一個(gè)不恰當(dāng)使用聚集索引的例子(摘自"Microsoft Sql Server 2008 Programming" Chapter 6, Rob Vieira):

Imagine this scenario: You are creating an accounting system. You would like to make use of the concept
of a transaction number for your primary key in your transaction files, but you would also like those
transaction numbers to be somewhat indicative of what kind of transaction it is. (It really helps troubleshooting
by your accountants.) So, you come up with something of a scheme: You’ll place a prefix on
all the transactions indicating what sub-system they come out of. They will look something like this:
ARXXXXXX Accounts Receivable Transactions
GLXXXXXX General Ledger Transactions
APXXXXXX Accounts Payable Transactions
where XXXXXX will be a sequential numeric value.
This seems like a great idea, so you implement it, leaving the default of the clustered index going on the
primary key.
At first look, everything about this setup looks fine. You’re going to have unique values, and the accountants
will love the fact that they can infer where something came from based on the transaction number.
The clustered index seems to make sense since they will often be querying for ranges of transaction IDs.
Ah, if only it were that simple. Think about your inserts for a bit. With a clustered index, you originally
had a nice mechanism to avoid much of the overhead of page splits. When a new record was inserted
that was to go after the last record in the table, then, even if there was a page split, only that record would
go to the new page, SQL Server wouldn’t try to move around any of the old data. Now you’ve messed
things up though.
New records inserted from the General Ledger will wind up going on the end of the file just fine. (GL
is last alphabetically, and the numbers will be sequential.) The AR and AP transactions have a major
problem though; they are going to be doing non-sequential inserts. When AP000025 gets inserted and

there isn’t room on the page, SQL Server is going to see AR000001 in the table, and know that it’s not
a sequential insert. Half the records from the old page will be copied to a new page before AP000025 is
inserted.
The overhead of this can be staggering. Remember that you’re dealing with a clustered index, and that
the clustered index is the data. The data is in index order. This means that, when you move the index
to a new page, you are also moving the data. Now imagine that you’re running this accounting system
in a typical OLTP environment (you don’t get much more OLTP-like than an accounting system) with a
bunch of data-entry people keying in vendor invoices or customer orders as fast as they can. You’re going
to have page splits occurring constantly, and every time you do, you’re going to see a brief hesitation for
users of that table while the system moves data around.
Fortunately, there are a couple of ways to avoid this scenario:
❑ Choose a cluster key that is going to be sequential in its inserting. You can either create an identity
column for this or you may have another column that logically is sequential to any transaction
entered regardless of system.
❑ Choose not to use a clustered index on this table. This is often the best option in a situation like
that in this example, since an insert into a non-clustered index on a heap is usually faster than
one on a cluster key.