索引的概念

索引的用途：我們對數(shù)據(jù)查詢及處理速度已成為衡量應用系統(tǒng)成敗的標準，而采用索引來加快數(shù)據(jù)處理速度通常是最普遍采用的優(yōu)化方法。

索引是什么：數(shù)據(jù)庫中的索引類似于一本書的目錄，在一本書中使用目錄可以快速找到你想要的信息，而不需要讀完全書。在數(shù)據(jù)庫中，數(shù)據(jù)庫程序使用索 引可以重啊到表中的數(shù)據(jù)，而不必掃描整個表。書中的目錄是一個字詞以及各字詞所在的頁碼列表，數(shù)據(jù)庫中的索引是表中的值以及各值存儲位置的列表。

索引的利弊：查詢執(zhí)行的大部分開銷是I/O，使用索引提高性能的一個主要目標是避免全表掃描，因為全表掃描需要從磁盤上讀取表的每一個數(shù)據(jù)頁，如果有 索引指向數(shù)據(jù)值，則查詢只需要讀少數(shù)次的磁盤就行啦。所以合理的使用索引能加速數(shù)據(jù)的查詢。但是索引并不總是提高系統(tǒng)的性能，帶索引的表需要在數(shù) 據(jù)庫中占用更多的存儲空間，同樣用來增刪數(shù)據(jù)的命令運行時間以及維護索引所需的處理時間會更長。所以我們要合理使用索引，及時更新去除次優(yōu)索引。

數(shù)據(jù)表的基本結(jié)構(gòu)

一個新表被創(chuàng)建之時，系統(tǒng)將在磁盤中分配一段以8K為單位的連續(xù)空間，當字段的值從內(nèi)存寫入磁盤時，就在這一既定空間隨機保存，當一個 8K用完的時候，數(shù)據(jù)庫指針會自動分配一個8K的空間。這里，每個8K空間被稱為一個數(shù)據(jù)頁（Page），又名頁面或數(shù)據(jù)頁面，并分配從0-7的頁號, 每個文件的第0頁記錄引導信息，叫文件頭（File header）；每8個數(shù)據(jù)頁（64Ｋ）的組合形成擴展區(qū)（Extent），稱為擴展。全部數(shù)據(jù)頁的組合形成堆（Heap）。

SQLS規(guī)定行不能跨越數(shù)據(jù)頁，所以，每行記錄的最大數(shù)據(jù)量只能為8K。這就是char和varchar這兩種字符串類型容量要限制在8K以內(nèi)的 原因，存儲超過8K的數(shù)據(jù)應使用text類型，實際上，text類型的字段值不能直接錄入和保存，它只是存儲一個指針，指向由若干8K的文本數(shù)據(jù)頁所組成 的擴展區(qū)，真正的數(shù)據(jù)正是放在這些數(shù)據(jù)頁中。?

頁面有空間頁面和數(shù)據(jù)頁面之分。??

當一個擴展區(qū)的8個數(shù)據(jù)頁中既包含了空間頁面又包括了數(shù)據(jù)或索引頁面時，稱為混合擴展（Mixed Extent）,每張表都以混合擴展開始；反之，稱為一致擴展（Uniform Extent），專門保存數(shù)據(jù)及索引信息。?

表被創(chuàng)建之時，SQLS在混合擴展中為其分配至少一個數(shù)據(jù)頁面，隨著數(shù)據(jù)量的增長，SQLS可即時在混合擴展中分配出7個頁面，當數(shù)據(jù)超過8個頁面時，則從一致擴展中分配數(shù)據(jù)頁面。??

空間頁面專門負責數(shù)據(jù)空間的分配和管理，包括：PFS頁面（Page free space）：記錄一個頁面是否已分配、位于混合擴展還是一致擴展以及頁面上還有多少可用空間等信息；GAM頁面（Global allocation map）和SGAM頁面(Secodary global allocation map)：用來記錄空閑的擴展或含有空閑頁面的混合擴展的位置。SQLS綜合利用這三種類型的頁面文件在必要時為數(shù)據(jù)表創(chuàng)建新空間；??

數(shù)據(jù)頁或索引頁則專門保存數(shù)據(jù)及索引信息，SQLS使用4種類型的數(shù)據(jù)頁面來管理表或索引：它們是IAM頁、數(shù)據(jù)頁、文本/圖像頁和索引頁。?

在WINDOWS中，我們對文件執(zhí)行的每一步操作，在磁盤上的物理位置只有系統(tǒng)（system）才知道；SQL SERVER沿襲了這種工作方式，在插入數(shù)據(jù)的過程中，不但每個字段值在數(shù)據(jù)頁面中的保存位置是隨機的，而且每個數(shù)據(jù)頁面在“堆”中的排列位置也只有系統(tǒng) （system）才知道。??

這是為什么呢？眾所周知，OS之所以能管理DISK，是因為在系統(tǒng)啟動時首先加載了文件分配表：FAT（File Allocation Table），正是由它管理文件系統(tǒng)并記錄對文件的一切操作，系統(tǒng)才得以正常運行；同理，作為管理系統(tǒng)級的SQL SERVER,也有這樣一張類似FAT的表存在，它就是索引分布映像頁：IAM（Index Allocation Map）。??

IAM的存在，使SQLS對數(shù)據(jù)表的物理管理有了可能。??

IAM頁從混合擴展中分配，記錄了8個初始頁面的位置和該擴展區(qū)的位置，每個IAM頁面能管理512,000個數(shù)據(jù)頁面，如果數(shù)據(jù)量太 大，SQLS也可以增加更多的IAM頁，可以位于文件的任何位置。第一個IAM頁被稱為FirstIAM,其中記錄了以后的IAM頁的位置。??

頁分裂

一半的數(shù)據(jù)將保留在老頁面，而另一半將放入新頁面，并且新頁面可能被分配到任何可用的頁。所以，頻繁頁分裂，后果很嚴重，將使物理表產(chǎn)生大量數(shù)據(jù)碎片，導致直接造成I/O效率的急劇下降，最后，停止SQLS的運行并重建索引將是我們的唯一選擇!

填充因子

索引的一個特性，定義該索引每頁上的可用空間量。FILLFACTOR（填充因子）適應以后表數(shù)據(jù)的擴展并減小了頁拆分的可能性。 填充因子是從0到100的百分比數(shù)值，設為100時表示將數(shù)據(jù)頁填滿。只有當不會對數(shù)據(jù)進行更改時(例如 只讀表中)才用此設置。值越小則數(shù)據(jù)頁上的空閑空間越大，這樣可以減少在索引增長過程中進行頁分裂的需要，但這一操作需要占用更多的硬盤空間。 填充因子指定不當，會降低數(shù)據(jù)庫的讀取性能，其降低量與填充因子設置值成反比。

索引的分類

SQL SERVER中有多種索引類型。

按存儲結(jié)構(gòu)區(qū)分：“聚集索引（又稱聚類索引，簇集索引）”，“分聚集索引（非聚類索引，非簇集索引）”

按數(shù)據(jù)唯一性區(qū)分：“唯一索引”，“非唯一索引”

按鍵列個數(shù)區(qū)分：“單列索引”，“多列索引”。

聚集索引

聚集索引是一種對磁盤上實際數(shù)據(jù)重新組織以按指定的一列或多列值排序。像我們用到的漢語字典，就是一個聚集索引，比如要查“張”，我們自然而然就翻到字典的后面百十頁。然后根據(jù)字母順序跟查找出來。這里用到微軟的平衡二叉樹算法，即 首先 把書翻到大概二分之一的位置，如果要找的頁碼比該頁的頁碼小，就把書 向前翻到四分之一處，否則，就把書向后翻到四分之三的地方，依 此類推，把書頁續(xù)分成更小的部分，直至正確的頁碼。

由于聚集索引是給數(shù)據(jù)排序，不可能有多種排法，所以一個表只能建立一個聚集索引。科學統(tǒng)計建立這樣的索引需要至少相當與該表120%的附加空間，用來存放該表的副本和索引中間頁，但是他的性能幾乎總是比其它索引要快。

由于在聚集索引下，數(shù)據(jù)在物理上是按序排列在數(shù)據(jù)頁上的，重復值也排在一起，因而包含范圍檢查（bentween,<,><=,>=）或使用group by 或order by的查詢時，一旦找到第一個鍵值的行，后面都將是連在一起，不必在進一步的搜索，避免啦大范圍的掃描，可以大大提高查詢速度。

非聚集索引

sqlserver默認情況下建立的索引是非聚集索引，他不重新組織表中的數(shù)據(jù)，而是對每一行存儲索引列值并用一個指針指向數(shù)據(jù)所在的頁面。他像漢語字典中的根據(jù)‘偏旁部首’查找要找的字，即便對數(shù)據(jù)不排序，然而他擁有的目錄更像是目錄，對查取數(shù)據(jù)的效率也是具有的提升空間，而不需要全表掃描。

一個表可以擁有多個非聚集索引，每個非聚集索引根據(jù)索引列的不同提供不同的排序順序。

創(chuàng)建索引

語法

      
        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命令創(chuàng)建索引各參數(shù)說明如下：

UNIQUE：用于指定為表或視圖創(chuàng)建唯一索引，即不允許存在索引值相同的兩行。

CLUSTERED：用于指定創(chuàng)建的索引為聚集索引。

NONCLUSTERED：用于指定創(chuàng)建的索引為非聚集索引。

index_name：用于指定所創(chuàng)建的索引的名稱。

table：用于指定創(chuàng)建索引的表的名稱。

view：用于指定創(chuàng)建索引的視圖的名稱。

ASC|DESC：用于指定具體某個索引列的升序或降序排序方向。

Column：用于指定被索引的列。

PAD_INDEX：用于指定索引中間級中每個頁（節(jié)點）上保持開放的空間。

FILLFACTOR = fillfactor：用于指定在創(chuàng)建索引時，每個索引頁的數(shù)據(jù)占索引頁大小的百分比，fillfactor的值為1到100。

IGNORE_DUP_KEY：用于控制當往包含于一個唯一聚集索引中的列中插入重復數(shù)據(jù)時SQL Server所作的反應。

DROP_EXISTING：用于指定應刪除并重新創(chuàng)建已命名的先前存在的聚集索引或者非聚集索引。

STATISTICS_NORECOMPUTE：用于指定過期的索引統(tǒng)計不會自動重新計算。

SORT_IN_TEMPDB：用于指定創(chuàng)建索引時的中間排序結(jié)果將存儲在 tempdb 數(shù)據(jù)庫中。

ON filegroup：用于指定存放索引的文件組。

例子：

      
        --
      
      
        表bigdata創(chuàng)建一個名為idx_mobiel的非聚集索引，索引字段為mobiel
      
      
        create
      
      
        index
      
      
         idx_mobiel


      
      
        on
      
      
         bigdata(mobiel) 






      
      
        --
      
      
        表bigdata創(chuàng)建一個名為idx_id的唯一聚集索引，索引字段為id
      
      
        

--
      
      
        要求成批插入數(shù)據(jù)時忽略重復值，不重新計算統(tǒng)計信息，填充因子為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數(shù)據(jù)庫中bigdata表的索引idx_mobiel上的碎片
      
      
        update
      
      
        statistics
      
       bigdata  
      
        --
      
      
        更新bigdata表中的全部索引的統(tǒng)計信息
      
    

索引的設計原則

對于一張表來說索引的有無和建立什么樣的索引，要取決與where字句和Join表達式中。

一般來說建立索引的原則包括以下內(nèi)容：

• 系統(tǒng)一般會給逐漸字段自動建立聚集索引。
• 有大量重復值且經(jīng)常有范圍查詢和排序、分組的列，或者經(jīng)常頻繁訪問的列，考慮建立聚集索引。
• 在一個經(jīng)常做插入操作的表中建立索引，應使用fillfactor(填充因子)來減少頁分裂，同時提高并發(fā)度降低死鎖的發(fā)生。如果在表為只讀表，填充因子可設為100.
• 在選擇索引鍵時，盡可能采用小數(shù)據(jù)類型的列作為鍵以使每個索引頁能容納盡可能多的索引鍵和指針，通過這種方式，可使一個查詢必需遍歷的索引頁面降低到最小，此外，盡可能的使用整數(shù)做為鍵值，因為整數(shù)的訪問速度最快。

SqlServer索引的原理與應用