1、索引的工作原理 我給大家推薦一個別人的總結。 http://blog.csdn.net/NightManHAHA/article/details/5648579 2、索引的設計原則 對於一張表來說，索引的有無和建立什麼樣的索引，要取決與where字句和Join表達式中。 一般來說建立索引的原則包 ...
1、索引的工作原理
我給大家推薦一個別人的總結。
http://blog.csdn.net/NightManHAHA/article/details/5648579
2、索引的設計原則
對於一張表來說，索引的有無和建立什麼樣的索引，要取決與where字句和Join表達式中。
一般來說建立索引的原則包括以下內容：
主鍵列：系統一般會自動建立聚集索引。
非主鍵列：有大量重覆值並且經常進行條件查詢、排序、分組的列，或者經常頻繁訪問的列，考慮建立聚集索引。
如果在一個經常做插入操作的表中建立索引，應使用fillfactor(填充因數)來減少頁分裂，同時提高併發度降低死鎖的發生。如果在表為只讀表，填充因數可設為100。
另外我們在選擇索引鍵的時候，儘量採用小數據類型（最好是整數）的列作為索引鍵，這樣每個索引頁能儘可能多的容納索引鍵和指針，用整數的好處是因為整數的訪問速度最快。
3、使用索引的註意事項
| 動作描述 | 使用聚集索引 | 使用非聚集索引 |
| 外鍵列 | 應 | 應 |
| 主鍵列 | 應 | 應 |
| 列經常被分組排序(order by) | 應 | 應 |
| 返回某範圍內的數據(BETWEEN、>、>=、< 和 <=) | 應 | |
| 小數目的不同值 | 應 | |
| 大數目的不同值 | 應 | |
| 頻繁更新的列 | 應 | |
| 頻繁修改索引列 | 應 | |
| 一個或極少不同值 |
4、索引的分類
按存儲結構區分：
“聚集索引（又稱聚類索引，簇集索引）”，“分聚集索引（非聚類索引，非簇集索引）”
聚集索引
每個表只能有一個聚集索引，預設情況下主鍵預設就是聚集索引。聚集索引確定表中數據的物理順序。就好比字典中按拼音查找一樣。
定義聚集索引時使用的列越少越好。
聚集索引不適用於:頻繁更改的列（這將導致整行移動（因為 SQL Server 必須按物理順序保留行中的數據值。因為在大數據量事務處理系統中數據是易失的）
聚集索引的約束是唯一性，並不是指欄位也要是唯一的。
創建聚集索引語法：create CLUSTERED Index 索引名稱 on 表名(需要創建索引列)
非聚集索引
一個表如果沒有聚集索引時,理論上可以建立249個非聚集索引。每個非聚集索引提供訪問數據的不同排序順序。
如果創建索引時不加索引關鍵字，預設創建的就是非聚集索引。
數據存儲在一個地方，索引存儲在另一個地方，索引帶有指針指向數據的存儲位置。就好比字典中按偏旁查找一樣。
創建非聚集索引語法：create NONCLUSTERED index 索引名 on 表名（創建索引列）
按數據唯一性區分：
“唯一索引”，“非唯一索引”
唯一鍵索引
建立唯一鍵約束（預設非聚集索引，實際上唯一鍵約束是用唯一索引來約束的）
創建唯一鍵約束,同時創建同名的唯一非聚集索引, 同時創建同名統計信息; 唯一鍵約束靠唯一索引來約束。
唯一鍵約束的索引不能像正常的索引使用太多的索引參數，因為唯一鍵約束與其索引同在。而單獨創建的唯一索引可以設置更多的參數。
創建唯一鍵約束語法： alter TABLE 表名 add constraint 索引名稱 unique(需要創建的列)（刪除唯一鍵索引的語句跟刪除主鍵聚集索引一樣）
唯一索引
唯一索索引跟唯一鍵約束的作用是一樣的，都是來檢測數據的唯一性。
不管是建立唯一索引還是唯一約束，被創建的列都不允許有重覆數據,重覆的NULL值也不可以。
唯一索引創建語法：CREATE unique index ix_RowID on TABLE(RowID)刪除語句：drop index 索引名
唯一鍵索引與唯一索引對比
功能一樣，唯一鍵索引比唯一索引多驗證 unique key。
唯一鍵索引沒有唯一索引靈活。
按鍵列個數區分：
“單列索引”，“多列索引”。
千萬數據量時。多列索引會比多個單列索引速度快很多。
索引視圖
索引視圖是具體化的視圖,它的結果集是經過計算的,並且存儲在資料庫中。
索引視圖更適合在OLAP（讀取較多，更新較少）的資料庫中使用，不適合在OLTP（記錄即時的增、刪、改、查）的資料庫中使用 。
一個標準視圖轉換為一個索引視圖必須遵守以下規則：
1．視圖必須使用With Schemabinding選項來創建。如果創建視圖時沒有with Schemabinding，試圖創建視圖時就會報錯，因為該視圖未綁定到架構。註意： schemabinding建立索引的時候必須先創建唯一聚集索引。
2．在這個視圖中不能使用其他視圖、導出表、行集函數或自查詢，也就是說只能使用表。
3．視圖只能鏈接同一個資料庫中的表並且鏈接表時只能使用INNER JOIN。 INNER JOIN前後不能使同一個表，不能使用LEFT（RIGHT） JOIN 或者 LEFT （RIGHT） OUTER JOIN 。
4．視圖不能包含UNION子句、TOP子句、ORDER BY子句、Having子句、Rollup子句、Cube子句、compute子句、Compute By子句或Distinct關鍵字。
5．視圖不允許使用某些集合函數，如：Count（*）可以使用count_big（*）代替、avg()、max()、min()、stdev()、stdevp()、var()或varp()等。
6．視圖不能使用Select * 這樣的語句，也就是說視圖的所有欄位都必須顯示指定。
7．視圖不能包含Text、ntext、image類型的列。
8．如果視圖包含一個Group By子句，那麼他必須在Select列中包含count_big(*)。
點陣圖索引（慎用）
點陣圖索引適用於低基數的列，比如說“性別”列，數據倉庫中的維表的主鍵，等等。理論上來說，他們都適合應用點陣圖索引。但是這並不是使用點陣圖索引唯一的條件。濫用點陣圖索引會導致嚴重的錯誤，而且這些錯誤往往是很隱蔽的，不易被髮現的錯誤。
點陣圖索引的原理：
採用點陣圖索引，一個鍵指向多行，有時候是數以百計甚至更多。如果更新了一個點陣圖索引鍵，那麼這個鍵指向的數以百計的記錄會與你實際更新的那一行一同被鎖定。
5、索引的查找
資料庫中有一個名為sysindexes的系統表，專門管理索引。查看一張表的索引屬性，可以在查詢分析器中使用以下命令：select * from sysindexes where id=object_id(‘A)；而要查看表的索引所占空間的大小，可以使用系統存儲過程命令：sp_spaceused A，其中參數A為被索引的表名。
6、索引語法
[ UNIQUE ]唯一索引 [CLUSTERED]聚集索引 [NONCLUSTERED ] 非聚集索引
CREATE [UNIQUE] [CLUSTERED| NONCLUSTERED ]
INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[with[PAD_INDEX][[,]FILLFACTOR=fillfactor]
[[,]IGNORE_DUP_KEY] //用於控制當往包含於一個唯一聚集索引中的列中插入重覆數據時SQL Server所作的反應。
[[,]DROP_EXISTING] //用於指定應刪除並重新創建已命名的先前存在的聚集索引或者非聚集索引。
[[,]STATISTICS_NORECOMPUTE] //用於指定過期的索引統計不會自動重新計算。
[[,]SORT_IN_TEMPDB] //用於指定創建索引時的中間排序結果將存儲在 tempdb 資料庫中。
]
[ ON filegroup ] //用於指定存放索引的文件組。
CREATE INDEX命令創建索引各參數說明如下：
UNIQUE：用於指定為表或視圖創建唯一索引，即不允許存在索引值相同的兩行。
CLUSTERED：用於指定創建的索引為聚集索引。
NONCLUSTERED：用於指定創建的索引為非聚集索引。
index_name：用於指定所創建的索引的名稱。
table：用於指定創建索引的表的名稱。
view：用於指定創建索引的視圖的名稱。
ASC|DESC：用於指定具體某個索引列的升序或降序排序方向。
Column：用於指定被索引的列。
PAD_INDEX：用於指定索引中間級中每個頁（節點）上保持開放的空間。
FILLFACTOR = fillfactor：用於指定在創建索引時，每個索引頁的數據占索引頁大小的百分比，fillfactor的值為1到100。
7、示例
--表bigdata創建一個名為idx_mobiel的非聚集索引，索引欄位為mobiel
create index idx_mobiel
on bigdata(mobiel)
--表bigdata創建一個名為idx_id的唯一聚集索引，索引欄位為id
--要求成批插入數據時忽略重覆值，不重新計算統計信息，填充因數為40
create unique clustered index idx_id
on bigdata(id)
with pad_index,
fillfactor=40,
ignore_dup_key,
statistics_norecompute
8、清理索引
先分析表的索引：當你發現，掃描密度行，最佳計數和實際計數的比例已經嚴重失調。邏輯掃描碎片占了非常大的百分比，每頁平均可用位元組數非常大時，就說明你的索引需要重新整理一下了。
當索引碎片太多的時候，就會很嚴重地影響到查詢的速度。檢查索引碎片 DBCC SHOWCONTIG(表)。
這時候我們可以採取兩種方法來解決：
一種時整理索引碎片（DBCC INDEXDEFRAG），另一種是重建索引（DBCC DBREINDEX）。
DBCC INDEXDEFRAG 只有在該命令正在運行時才可用。而且可以在不丟失已完成工作的情況下中斷該操作。這種方法的缺點是在重新組織數據方面沒有聚集索引的除去/重新創建操作有效。
重新創建聚集索引將對數據進行重新組織，其結果是使數據頁填滿。填滿程度可以使用 FILLFACTOR 選項進行配置。這種方法的缺點是索引在除去/重新創建周期內為離線狀態，並且操作屬原子級。如果中斷索引創建，則不會重新創建該索引。
也就是說，要想獲得好的效果，還是得用重建索引。
DBCC DBREINDEX(表，索引名，填充因數）
第一個參數，可以是表名，也可以是表ID。
第二個參數，如果是''，表示影響該表的所有索引。
第三個參數，填充因數，即索引頁的數據填充程度。如果是100（這裡是%），表示每一個索引頁都全部填滿，此時select效率最高，但以後要插入索引時，就得移動後面的所有頁，效率很低。如果是0，表示使用先前的填充因數值。
DBCC DBREINDEX(A，''，100)
9、管理索引
select * from sysindexes where id=object_id('A')// 查看A表的索引屬性
exec sp_spaceused A //查看表的索引所占空間的大小A為被索引的表名
--查看索引定義
Exec sp_helpindex A
--將索引名由'id' 改為'idx'
Exec sp_rename A.id','idx'
--刪除A表中的idx索引
drop index A.idx
--檢查A表中索引id的碎片信息
dbcc showcontig(A,id)
--整理test資料庫中A表的索引id上的碎片
dbcc indexdefrag(Test,A,id)
--更新A表中的全部索引的統計信息
update statistics A
