關(guān)于SQL 數(shù)據(jù)庫表中的聚集索引和非聚集索引等（轉(zhuǎn)）

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

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

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

         索引的結(jié)構(gòu)是由：根節(jié)點--->非葉節(jié)點--->非葉節(jié)點--->葉節(jié)點（注意索引在數(shù)據(jù)庫引擎中所用的
         內(nèi)部數(shù)據(jù)結(jié)構(gòu)一般是B+樹，參考后文）

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

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

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

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

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

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

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

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

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

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

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

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