關于SQL 數據庫表中的聚集索引和非聚集索引等（轉）

         按照存儲方式分為：聚集與非聚集索引（需要重視和區別的概念，后面詳解）
         按照維護與管理索引角度分為：唯一索引、復合索引和系統自動創建的索引（相對簡單，如下解釋：）
         1).唯一索引：惟一索引可以確保索引列不包含重復的值.
         可以用多個列，但是索引可以確保索引列中每個值組合都是唯一的，
         即下面的姓不能有重復，同時名也不能有重復：
                                             姓      名
                                             李      二
                                             張      三
                                             王      五
          語法： create unique index idxempid on emp(姓,名)

          2).復合索引：如果在兩上以上的列上創建一個索引，則稱為復合索引。
          那么，不可能有兩行的姓和名是重復的，即上面的表沒有兩行其姓和名的組合是一樣的。
          語法： create index indxfullname on emp(姓,名)

          3).系統自建的索引：在使用T_sql語句創建表的時候使用PRIMARY KEY或UNIQUE約束時，會在表上
          自動創建一個惟一索引，自動創建的索引是無法刪除的。
           語法：
                   create table ABC
                   ( empID int PRIMARY KEY,
                      firstname varchar(50) UNIQUE,
                      lastname  varchar(50) UNIQUE,
                     )    /*這樣的結果就出來了三個索引，但只有一個聚集索引empID*/

         索引的結構是由：根節點--->非葉節點--->非葉節點--->葉節點（注意索引在數據庫引擎中所用的
         內部數據結構一般是B+樹，參考后文）

          聚集索引和非聚集索引——   
         用一個現實中的例子說明以助理解。我們的漢語字典的正文本身就是一個聚集索引。比如，我們要查“安”字，就會很自然地翻開字典的前幾頁，因為“安”的拼音是“an”，而按照拼音排序漢字的字典是以英文字母“a”開頭并以“z”結尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”開頭的部分仍然找不到這個字，那么就說明您的字典中沒有這個字；同樣的，如果查“張”字，那您也會將您的字典翻到最后部分，因為“張”的拼音是“zhang”。也就是說，字典的正文部分本身就是一個目錄，您不需要再去查其他目錄來找到您需要找的內容。
　　我們把這種正文內容本身就是一種按照一定規則排列的目錄稱為“聚集索引”。

　　如果您認識某個字，您可以快速地從自動中查到這個字。但您也可能會遇到您不認識的字，不知道它的發音，這時候，您就不能按照剛才的方法找到您要查的字，而需要去根據“偏旁部首”查到您要找的字，然后根據這個字后的頁碼直接翻到某頁來找到您要找的字。但您結合“部首目錄”和“檢字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“張”字，我們可以看到在查部首之后的檢字表中“張”的頁碼是672頁，檢字表中“張”的上面是“馳”字，但頁碼卻是63頁，“張”的下面是“弩”字，頁面是390頁。很顯然，這些字并不是真正的分別位于“張”字的上下方，現在您看到的連續的“馳、張、弩”三字實際上就是他們在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我們可以通過這種方式來找到您所需要的字，但它需要兩個過程，先找到目錄中的結果，然后再翻到您所需要的頁碼。
        我們把這種目錄純粹是目錄，正文純粹是正文的排序方式稱為“非聚集索引”。

         2.性質及使用方法
         1)聚集索引：表中存儲的數據按照索引的順序存儲，檢索效率比普通索引高，索引占用硬盤
          存儲空間小（1%左右），但對數據新增/修改/刪除的速度影響比較大（降低）。
          特點：
                  (1) 無索引，數據無序
                  (2) 有索引，數據與索引同序 
                  (3) 數據會根據索引鍵的順序重新排列數據
                  (4) 一個表只能有一個索引
                  (5) 葉節點的指針指向的數據也在同一位置存儲
         語法：create CLUSTERED INDEX idxempID ON emp(empID)

         2)非聚集索引：不影響表中的數據存儲順序，檢索效率比聚集索引低，索引占用硬盤存儲
         空間大（30%~40%），對數據新增/修改/刪除的影響很少。
         特點：
               (1) 一個表可以最多可以創建249個非聚集索引
               (2) 先建聚集索引才能創建非聚集索引
               (3) 非聚集索引數據與索引不同序
               (4) 數據與非聚集索引在不同位置
               (5) 非聚集索引在葉節點上存儲，在葉節點上有一個“指針”直接指向要查詢的數據區域
               (6) 數據不會根據非聚集索引鍵的順序重新排列數據
         語法：create NONCLUSTERED INDEX idximpID ON emp(empID)
         
         創建索引的方法：
         1)企業管理器中
               (1)右擊某個表，所有任務---管理索引，打開管理索引，單擊“新建”就可以創建索引
               (2)在設計表中進行設計表，管理索引/鍵
               (3)在關系圖中，添加表后右擊關系圖中的某個表，就有“索引/鍵”
               (4)通過向導,數據庫---創建索引向導
               (5)通過T-SQL語句
         2)能過“索引優化向導”來優化索引的向導，通過它可以決定選擇哪些列做為索引列
         
         何時應使用聚集索引或非聚集索引
         

動作描述	使用聚集索引	使用非聚集索引
列經常被分組排序	應	應
返回某范圍內的數據	應	不應
一個或極少不同值	不應	不應
小數目的不同值	應	不應
大數目的不同值	不應	應
頻繁更新的列	不應	應
外鍵列	應	應
主鍵列	應	應
頻繁修改索引列	不應	應

         3.數據庫引擎中索引的內部結構
         有必要先說明一下數據庫引擎，
         這部分是較深的內容，需要有一定的數據庫理論知識和數據結構與算法知識，數據結構和算法告訴我們，對索引關鍵字進行快速查找時要使用樹形數據結構，在數據庫引擎中，索引通常用B+樹來表示，google發現這方面的文章較少，后面找到相關詳細資料會補充。

         4.主鍵、索引、聚集索引和非聚集索引
         1)主鍵   (PK)   
                唯一標識表中的所有行的一個列或一組列。主鍵不允許空值。不能存在具有相同的主鍵值的兩個
         行，因此主鍵值總是唯一標識單個行。表中可以有不止一個鍵唯一標識行，每個鍵都稱作候選鍵。只有
         一個候選鍵可以選作表的主鍵，所有其它候選鍵稱作備用鍵。盡管表不要求具有主鍵，但定義主鍵是很
         好的做法。   在規范化的表中，每行中的所有數據值都完全依賴于主鍵。例如，在以   EmployeeID   作為
         主鍵的規范化的   employee   表中，所有列都應包含與某個特定職員相關的數據。該表不具有   
         DepartmentName 列，因為部門的名稱依賴于部門   ID，而不是職員   ID。   

         2)索引   
               關系數據庫中基于鍵值提供對表的行中數據的快速訪問的數據庫對象。索引還可以在表的行上強制唯
         一性。SQL   Server   支持聚集索引和非聚集索引。對表的主鍵自動進行索引。在全文搜索中，全文索引
         存儲關于重要詞和這些詞在給定列中的位置的信息。   
               如果某列有多行包含   NULL   值，則不能在該列上創建唯一索引。同樣，如果列的組合中有多行包
         含   NULL   值，則不能在多個列上創建唯一索引。在創建索引時，這些被視為重復的值。   

        3)聚集索引   
               在創建聚集索引時，將會對表進行復制，對表中的數據進行排序，然后刪除原始的表。因此，數據庫
         上必須有足夠的空閑空間，以容納數據復本。默認情況下，表中的數據在創建索引時排序。但是，如果
         因聚集索引已經存在，且正在使用同一名稱和列重新創建，而數據已經排序，則會重建索引，而不是從
         頭創建該索引，以自動跳過排序操作。重建操作會檢查行是否在生成索引時進行了排序。如果有任何行
         排序不正確，即會取消操作，不創建索引。   

         4)非聚集索引   
               非聚集索引與課本中的索引類似。數據存儲在一個地方，索引存儲在另一個地方，索引帶有指針指向
         數據的存儲位置。索引中的項目按索引鍵值的順序存儲，而表中的信息按另一種順序存儲（這可以由聚
         集索引規定）。如果在表中未創建聚集索引，則無法保證這些行具有任何特定的順序。 

         打開設計表界面里面有個鑰匙就是主鍵的意思，當你聲明一列為主鍵的時候數據庫實際上就是生成一個
唯一的索引，查詢優化器實際上是根據列上有沒有唯一索引來保證列的唯一性而不是根據列是否被聲明為主鍵。   
         聚集索引一個表只有一個，實際上它的葉子節點就是數據頁，比非聚集索引速度快，占用的空間小，大概只有表的1％左右。如果在聲明的時候沒有選擇UNIQUE選項，則在插入數據的時候會自動生成一個唯一標示符。   
         非聚集索引一個表可以有多個，一個3層的非聚簇索引要查詢6次才可以找到真實數據，因為其葉子節點并不是真實數據，而是標識（如果表上有聚集索引則為聚集索引，如沒有，則為實際數據的頁號），非聚集索引通常占用空間比較大，表的30－40％。  

posted on 2009-05-14 19:19 蔣耘 閱讀(1759) 評論(1)  編輯  收藏 所屬分類: 數據庫