標簽：des style blog http java color
原文:SqlServer索引的原理與應用
索引的用途：我們對數據查詢及處理速度已成爲衡量應用系統成敗的標准，而采用索引來加快數據處理速度通常是最普遍采用的優化方法。
索引是什么：數據庫中的索引类似于一本书的目录，在一本书中使用目录可以快速找到你想要的信息，而不需要读完全书。在數據庫中，數據庫程序使用索引可以重啊到表中的数据，而不必扫描整个表。书中的目录是一个字词以及各字词所在的页码列表，數據庫中的索引是表中的值以及各值存储位置的列表。
索引的利弊：查詢執行的大部分開銷是I/O，使用索引提高性能的一個主要目標是避免全表掃描，因爲全表掃描需要從磁盤上讀取表的每一個數據頁，如果有索引指向數據值，則查詢只需要讀少數次的磁盤就行啦。所以合理的使用索引能加速數據的查詢。但是索引並不總是提高系統的性能，帶索引的表需要在數據庫中占用更多的存儲空間，同樣用來增刪數據的命令運行時間以及維護索引所需的處理時間會更長。所以我們要合理使用索引，及時更新去除次優索引。
一个新表被创建之时，系统将在磁盘中分配一段以8K为单位的连续空间，当字段的值从内存写入磁盘时，就在这一既定空间随机保存，当一个 8K用完的时候，數據庫指针会自动分配一个8K的空间。这里，每个8K空间被称为一个数据页（Page），又名页面或数据页面，并分配从0-7的页号, 每个文件的第0页记录引导信息，叫文件头（File header）；每8个数据页（64Ｋ）的组合形成扩展区（Extent），称为扩展。全部数据页的组合形成堆（Heap）。
SQLS规定行不能跨越数据页，所以，每行记录的最大数据量只能为8K。这就是char和varchar这两种字符串类型容量要限制在8K以内的 原因，存储超过8K的数据应使用text类型，实际上，text类型的字段值不能直接录入和保存，它只是存储一个指针，指向由若干8K的文本数据页所组成 的扩展区，真正的数据正是放在这些数据页中。
頁面有空間頁面和數據頁面之分。
当一个扩展区的8个数据页中既包含了空间页面又包括了数据或索引页面时，称为混合扩展（Mixed Extent）,每张表都以混合扩展开始；反之，称为一致扩展（Uniform Extent），专门保存数据及索引信息。
表被創建之時，SQLS在混合擴展中爲其分配至少一個數據頁面，隨著數據量的增長，SQLS可即時在混合擴展中分配出7個頁面，當數據超過8個頁面時，則從一致擴展中分配數據頁面。
空间页面专门负责数据空间的分配和管理，包括：PFS页面（Page free space）：记录一个页面是否已分配、位于混合扩展还是一致扩展以及页面上还有多少可用空间等信息；GAM页面（Global allocation map）和SGAM页面(Secodary global allocation map)：用来记录空闲的扩展或含有空闲页面的混合扩展的位置。SQLS综合利用这三种类型的页面文件在必要时为数据表创建新空间；
數據頁或索引頁則專門保存數據及索引信息，SQLS使用4種類型的數據頁面來管理表或索引：它們是IAM頁、數據頁、文本/圖像頁和索引頁。
在WINDOWS中，我们对文件执行的每一步操作，在磁盘上的物理位置只有系统（system）才知道；SQL SERVER沿袭了这种工作方式，在插入数据的过程中，不但每个字段值在数据页面中的保存位置是随机的，而且每个数据页面在“堆”中的排列位置也只有系统 （system）才知道。
这是为什么呢？众所周知，OS之所以能管理DISK，是因为在系统启动时首先加载了文件分配表：FAT（File Allocation Table），正是由它管理文件系统并记录对文件的一切操作，系统才得以正常运行；同理，作为管理系统级的SQL SERVER,也有这样一张类似FAT的表存在，它就是索引分布映像页：IAM（Index Allocation Map）。
IAM的存在，使SQLS對數據表的物理管理有了可能。
IAM页从混合扩展中分配，记录了8个初始页面的位置和该扩展区的位置，每个IAM页面能管理512,000个数据页面，如果数据量太 大，SQLS也可以增加更多的IAM页，可以位于文件的任何位置。第一个IAM页被称为FirstIAM,其中记录了以后的IAM页的位置。
一半的数据将保留在老页面，而另一半将放入新页面，并且新页面可能被分配到任何可用的页。所以，频繁頁分裂，后果很严重，将使物理表产生大量数据碎片，导致直接造成I/O效率的急剧下降，最后，停止SQLS的运行并重建索引将是我们的唯一选择!
索引的一個特性，定義該索引每頁上的可用空間量。FILLFACTOR（填充因子）適應以後表數據的擴展並減小了頁拆分的可能性。填充因子是从0到100的百分比数值，设为100时表示将数据页填满。只有当不会对数据进行更改时(例如 只读表中)才用此设置。值越小则数据页上的空闲空间越大，这样可以减少在索引增长过程中进行頁分裂的需要，但这一操作需要占用更多的硬盘空间。填充因子指定不当，会降低數據庫的读取性能，其降低量与填充因子设置值成反比。
SQL SERVER中有多种索引类型。
按存儲結構區分：“聚集索引（又稱聚類索引，簇集索引）”，“分聚集索引（非聚類索引，非簇集索引）”
按數據唯一性區分：“唯一索引”，“非唯一索引”
按鍵列個數區分：“單列索引”，“多列索引”。
聚集索引是一種對磁盤上實際數據重新組織以按指定的一列或多列值排序。像我們用到的漢語字典，就是一個聚集索引，比如要查“張”，我們自然而然就翻到字典的後面百十頁。然後根據字母順序跟查找出來。這裏用到微軟的平衡二叉樹算法，即首先把書翻到大概二分之一的位置，如果要找的頁碼比該頁的頁碼小，就把書向前翻到四分之一處，否則，就把書向後翻到四分之三的地方，依此類推，把書頁續分成更小的部分，直至正確的頁碼。
由于聚集索引是給數據排序，不可能有多種排法，所以一個表只能建立一個聚集索引。科學統計建立這樣的索引需要至少相當與該表120%的附加空間，用來存放該表的副本和索引中間頁，但是他的性能幾乎總是比其它索引要快。
由于在聚集索引下，数据在物理上是按序排列在数据页上的，重复值也排在一起，因而包含范围检查（bentween,<,><=,>=）或使用group by 或order by的查询时，一旦找到第一个键值的行，后面都将是连在一起，不必在进一步的搜索，避免啦大范围的扫描，可以大大提高查询速度。
sqlserver默認情況下建立的索引是非聚集索引，他不重新組織表中的數據，而是對每一行存儲索引列值並用一個指針指向數據所在的頁面。他像漢語字典中的根據‘偏旁部首’查找要找的字，即便對數據不排序，然而他擁有的目錄更像是目錄，對查取數據的效率也是具有的提升空間，而不需要全表掃描。
一個表可以擁有多個非聚集索引，每個非聚集索引根據索引列的不同提供不同的排序順序。
語法
CREATE [UNIQUE] [CLUSTERED| NONCLUSTERED ] INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ ,...n ] ) [with[PAD_INDEX][[,]FILLFACTOR=fillfactor] [[,]IGNORE_DUP_KEY] [[,]DROP_EXISTING] [[,]STATISTICS_NORECOMPUTE] [[,]SORT_IN_TEMPDB] ] [ ON filegroup ]
CREATE INDEX命令創建索引各参数说明如下：
UNIQUE：用于指定爲表或視圖創建唯一索引，即不允許存在索引值相同的兩行。
CLUSTERED：用于指定創建的索引爲聚集索引。
NONCLUSTERED：用于指定創建的索引爲非聚集索引。
index_name：用于指定所創建的索引的名稱。
table：用于指定創建索引的表的名称。
view：用于指定創建索引的视图的名称。
ASC|DESC：用于指定具體某個索引列的升序或降序排序方向。
Column：用于指定被索引的列。
PAD_INDEX：用于指定索引中間級中每個頁（節點）上保持開放的空間。
FILLFACTOR = fillfactor：用于指定在創建索引时，每个索引页的数据占索引页大小的百分比，fillfactor的值为1到100。
IGNORE_DUP_KEY：用于控制当往包含于一个唯一聚集索引中的列中插入重复数据时SQL Server所作的反应。
DROP_EXISTING：用于指定應刪除並重新創建已命名的先前存在的聚集索引或者非聚集索引。
STATISTICS_NORECOMPUTE：用于指定過期的索引統計不會自動重新計算。
SORT_IN_TEMPDB：用于指定創建索引时的中间排序结果将存储在 tempdb 數據庫中。
ON filegroup：用于指定存放索引的文件组。
例子：
--表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
Exec sp_helpindex BigData --查看索引定義 Exec sp_rename ‘BigData.idx_mobiel‘,‘idx_big_mobiel‘ --将索引名由‘idx_mobiel‘ 改为‘idx_big_mobiel‘ drop index BigData.idx_big_mobiel --刪除bigdata表中的idx_big_mobiel索引 dbcc showcontig(bigdata,idx_mobiel) --檢查bigdata表中索引idx_mobiel的碎片信息 dbcc indexdefrag(Test,bigdata,idx_mobiel) --整理test數據庫中bigdata表的索引idx_mobiel上的碎片 update statistics bigdata --更新bigdata表中的全部索引的統計信息
對于一張表來說索引的有無和建立什麽樣的索引，要取決與where字句和Join表達式中。
一般來說建立索引的原則包括以下內容：
SqlServer索引的原理與應用,码迷,mamicode.com
標簽：des style blog http java color
原文地址：http://www.cnblogs.com/lonelyxmas/p/3698703.html
