SQL應用與開發：（九）提高效率的索引，sql索引

SQL應用與開發：（九）提高效率的索引，sql索引

在資料庫中，索引是一個特殊的對象，是一種可以加快資料檢索的資料庫結構，它可以從大量的資料中迅速找到需要的內容，使得資料查詢時不必檢索整個資料庫。索引是一種基於表中資料的對象，與視圖不同，索引需要佔用實體儲存體。使用資料庫的索引，使我們能夠較快的查詢資料。

1.簡介

索引是表示資料的一種方式，它提供的資料順序不用於資料在磁碟上的實體儲存體順序。索引基於表的一列或多列組合建立，在表內重新排列記錄的物理位置。當使用索引時，資料是以分類排序的方式提供給使用者的，排列順序可以用建立索引語句控制。通常，通過在正確的列上建立索引，可以使資料庫效能顯著提高，特別是在表之間的聯結列上建立索引更是如此。

索引和圖書目錄的作用類似。如果把資料表中的資料看作是書的內容，則索引就是書的目錄。書的目錄指向了書的內容(通過頁碼)。同樣，索引是表的關鍵值，它提供了指向表中行的指標。目錄中的頁碼是達到書內容的直接路徑，而索引也是到達表資料的直接路徑，從而更高效地訪問資料。

在資料庫圖表中，可以為選擇的表建立、編輯或刪除索引/鍵屬性頁面中的每個索引類型。當儲存附加在此索引上的表或包含此表的資料庫圖表中，索引同時也被儲存。

1.1優點

索引是一個獨立的、物理的資料庫結構，資料庫使用者可以利用索引快速存取資料庫表中的特定資訊。

通常情況下，只有當經常查詢索引列中的資料時，才需要在表上建立索引。索引將佔用磁碟空間，並且降低添加、刪除和更新行的速度。不過在大多數情況下，索引所帶來的資料檢索資料的優勢大大超過它的不足之處。然而，如果應用程式頻繁的更新資料，或者磁碟空間有限，那麼最好不限制索引的資料。

總體來說，索引是為了加速檢索速度而建立的一種儲存結構。索引針對一個表建立，它由存放有資料頁面以外的索引頁面組成。每個索引頁面中的行都包含邏輯指標，通過該指標可以直接檢索到資料，這機會加速物理資料的檢索。

對錶中的列是否建立索引以及建立何種索引，對資料的檢索速度有很大影響。建立了索引的列幾乎可以立即響應查詢，而未建立索引的列查詢時就需要等待很長時間。因為對於未建立索引的列，需要逐行進行搜尋，這種索引耗費的時間直接同表中的資料量成正比。

利用索引進行查詢具有以下優點：

提高查詢速度：如果一個表中沒有索引，在進行查詢時，資料庫就會強制按照表的順序逐行進行搜尋，若要找到滿足條件的行，則需要訪問表的每一行，顯而易見要花費很長的時間。但是如果要查詢的表中的位置資訊確定表中的行，這樣便縮短了查詢的時間；

提高串連、ORDER BY和GROUP BY的執行速度：串連、ORDER BY和GROUP BY都需要對資料進行檢索，如果在表中建立索引，則串連、ORDER BY和GROUP BY執行的速度將大大提高；

查詢最佳化分析器依靠索引起作用：在執行查詢的過程中，資料庫會自動地對查詢進行最佳化。由於資料庫的最佳化依靠索引進行，所以在建立索引後，資料庫會依據所建立的索引，為使索引的速度最快而採取哪些索引；

強制實施行的唯一性：建立唯一索引可以保證表中的資料不重複。

總之，索引可以改善資料查詢效能，但是這是需要付出代價。帶索引的資料表在資料庫中會佔據更多的儲存空間。另外，為了維護索引，對資料進行插入、更新、刪除操作的命令所花費的時間會更長。在設計和建立索引時，應確保對效能的提高程度大一在儲存空間和處理資源方面的代價。

1.2注意事項

使用者可根據環境的需要，在基表上建立一個或者多個索引，以提供多種存取方法，加快資料檢索速度。通常，建立與刪除索引由資料庫管理員或表的擁有者負責完成。系統在存取資料時會自動選擇合適的索引作為存取路徑，使用者不必也不能選擇索引。

索引是一把雙刃劍。雖然它會加速SELECT語句，但是它也可能減緩資料所做的更改，這是因為必須在運行時刻動態更新SQL中的索引。無論什麼時候表中的資料發生改變，表中的所有索引都必鬚髮生改變，如果這樣會導致重組索引，則當索引重組發生時，可能會發生延遲。

建立索引應考慮的主要因素有：

i.一個表如果建立大量索引會影響INSERT、UPDATE和DELECT語句的效能，因為在表中的資料更改時，所有索引都需要進行適當的調整。另一方面，對於不需要修改資料的查詢(SELECT語句)，大量索引有助於提高效能。因為查詢分析器有更多的索引可供選擇，以便確定以最快的速度訪問資料的最佳方法；

ii.通常我們是通過主鍵來對錶進行查詢的，因此首先應該考慮在主鍵列上建立索引。另外，串連中頻繁使用的列(包括 外鍵)應作為建立索引的考慮選項；

iii.對小型表進行索引可能不會產生最佳化效果，因為查詢分析器在遍曆索引以搜尋資料時，花費的時間可能比簡單的表掃描更長的時間。

綜上所述，由於建立索引使用需要一定的開銷，而且當使用INSERT或者UPDATE對資料進行插入和更新操作時，維護索引也需要花費時間和儲存空間。因此，沒有必要對錶中所有列建立索引。

2.類型

依據索引的順序和資料庫的實體儲存體順序是否相同，可以將索引分為兩類：叢集索引(Clustered Index)和非叢集索引(Non-clustered Index)。叢集索引和非叢集索引都使用B-Tree結構建立，而且都包括索引頁和資料頁，其中索引頁用來存放索引和指向下一層的的指標，資料頁用來存放記錄。

根據索引鍵的組成，可以把索引分為唯一索引和複合式索引。唯一索引(UNIQUE Index)可以確保索引列不包含重複的值，複合式索引是使用表中多個列對資料進行索引的索引。另外，因SQL環境的不同還有其他的索引類型。例如，Oracle中位元影像索引和函數索引等。

索引一旦建立，將由資料庫自動管理和維護。例如，在向表中插入、更新或者刪除一條記錄時，資料庫會自動在索引中作出相應的修改。在編寫SQL查詢語句時，具有索引的表不具有索引的表沒有任何區別。索引只是提供一種快速存取指定記錄的方法。資料表具有索引，只會影響到表的查詢速度，而不會影響到其他任何方面。

2.1B-Tree索引

B-Tree索引的頂端節點稱為根節點(Root Node)，底層節點稱為分葉節點(Leaf Node)，在根節點和分葉節點之間的節點稱為中間節點(Intermediate Node)。每級索引中的頁連結在雙向連結清單中。B-Tree資料結構從根節點開始，以左右平衡的方式排列資料，中間可以根據需要分成許多層，B-Tree索引可以很方便地為更新提供可用空間。

為B-Tree的總體結構圖：

由於各分葉節點按照所含的索引碼值有一個線性順序，所以就可以利用各個節點的指標Pn將分葉節點按照索引碼值順序連結在一起。這種順序能夠高效地對檔案進行順序處理，而B-Tree索引的其他結構能夠高效地對檔案進行隨機處理。

2.2叢集索引

叢集索引將資料行的鍵值在表內排序並儲存對應的資料記錄，使得表的物理順序與索引順序一致。一個表只能包含一個叢集索引。如果不是叢集索引，表中各行的物理順序和鍵值的邏輯順序就不會匹配。

叢集索引的B-Tree是由下而上構建的，一個資料頁(索引頁的分葉節點)包含一筆記錄，再由多個資料頁產生一個中間節點的索引頁。然後由多個中間節點的索引頁合成更上層的索引頁，組合後產生最頂層的根節點的索引頁。

為叢集索引的資料結構：

在建立叢集索引時，會對錶進行複製，並且對錶中的資料進行排序，然後刪除原始表。因此，資料庫上必須有足夠的空閑空間，以容納資料副本。

建立叢集索引時，應該注意一下事項：

· 大多數的表 都應該有叢集索引或使用分區來降低對錶尾頁的爭用，在一個高度事務性的環境中，對最後一頁的封鎖將會嚴重影響系統的輸送量。

· 在叢集索引下，資料在物理上按順序排在資料頁上，重複值也排在一起，因而在那些包含範圍檢查(BETWEEN、<、<=、>、>=)或使用GROUP BY和ORDER BY的查詢時，一旦找到具有範圍中第一個鍵值的行，具有後續索引值的行就可以保證物理上毗連在一起，而不必進一步搜尋。這可以避免大範圍掃描，提高了查詢速度。

· 在一個頻繁發生插入操作的表上建立叢集索引時，不要將索引在具有單調上升值得列(如IDENTITY)上，否則會經常引起封鎖衝突。

·在聚聚索引中不要包含經常修改的列，因為碼值修改後，資料行必須移動到新的位置。

· 選擇叢集索引應基於WHERE子句和串連操作的類型。

叢集索引的候選索引鍵列是主鍵列，該列在WHERE子句中使用並且可以隨機插入；按範圍存取的列；在GROUP BY或ORDER BY中使用的列；不經常修改的列；在串連操作中使用的列。

2.3非叢集索引

非叢集索引與圖書的索引類似。資料存放區的一個位置，索引儲存在另一個位置，索引帶有指向資料的儲存位置。索引中的項目按索引鍵值的順序儲存，而表中的資訊按另一種順序儲存(這裡可以由叢集索引定)。如果在表中未建立叢集索引，則無法保證這些行具有任何特定的順序。

非叢集索引與叢集索引一樣具有B-Tree結構，但是它與叢集索引有兩個重大差別：

· 資料行不按非叢集索引鍵的順序排序和儲存。

· 非叢集索引的頁層不包含資料頁，相反，分葉節點包含索引行。每個索引行包含非聚集鍵值以及一個或多個行定位器，這些行定位器指向有該鍵值的資料行(如果索引不唯一，則可能是多行)。

非叢集索引具有完全獨立於資料行的結構。非叢集索引的最低行包含非叢集索引的鍵值，並且每個鍵值項都有指標指向包含該鍵值的資料行。資料行不按基於非聚集鍵的次序儲存。

為非叢集索引的資料結構：

在使用非叢集索引時應注意以下問題：

· 索引需要使用多少空間；

· 建立索引的列是否穩定；

· 索引鍵是如何選擇的，掃描效果是否更佳；

· 是否有許多重複值。

我們常在以下情況下使用非叢集索引：常用於集合函數的列和經常使用JOIN，ORDER BY和GROUP BY子句的列。

2.4唯一索引和複合式索引

主鍵用來標識唯一行，它定義兩個表的關係，用於產生索引的一個或一組列。根據索引鍵的組成，可以把索引分為三種類型：唯一索引、複合式索引和覆蓋索引。

2.4.1唯一索引

唯一索引不允許兩行具有相同的索引值。在多列唯一索引的情況下，該索引可以確保索引列中每個值組合都是唯一的。如果現有資料中存在重複的鍵值，則大多數資料庫都不允許將新建立的唯一索引與表一起儲存。當新資料將使表中的鍵值重複時，資料庫也拒絕接受此資料。在資料庫圖表中為表定義一個主鍵將自動建立主鍵索引，主鍵索引是唯一索引的特殊類型。主鍵索引要求主鍵中的每個值是唯一的。當在查詢中使用主鍵索引時，它還允許快速存取資料。

在一個資料庫表中，如果單列中有不止一行包含NULL值，則無法再該列上建立唯一索引。在列的組合中，如果其中有多個列包含NULL值，則這些NULL值被視為重複值。因此，在這樣的多個列上也不能建立唯一索引。

2.4.2複合式索引和覆蓋索引

顧名思義，複合式索引是指在一個表中使用不止一個列對資料進行索引的索引，它是通過串連兩個或多個列值而建立的。

複合式索引中可以使用2~16個列的索引，以兩列或多列作為一個單位搜尋時最好，或者許多查詢只引用索引中指定的列時，應使用複合式索引。複合式索引中的所有列必須在同一個表中，並且複合式索引值所允許的最大大小為900位元組，即組成複合式索引的固定大小列的總長度不得超過900位元組。

當索引中包含了需要的所有資訊時，這個索引稱為覆蓋索引。它的鍵值包含了滿足查詢的所有資料。

2.5其他索引類型

在各個版本的資料庫系統中可以建立多種類型的索引，以適應各種資料表的特點，滿足不同情況下的需求。下面簡單舉一個例子：

位元影像索引

在位元影像索引中並不是以索引列的值得的方式來儲存索引資訊的。位元影像索引為每個唯一的索引列建立一個位元影像，在這個位元影像中使用一個單元(Bit，取值為0或1)來對應一條記錄的列值。如果該位元為1，說明與該位元對應的列值是一條包含該位元影像的索引列的記錄。位元到列值得對應關係通過位元影像索引中的映射函數來完成。

這樣，位元影像索引就能夠以一種完全不同的內部機制來完成與B-Tree索引相同的功能。如果索引列的取值範圍只包括少數幾個固定的值，位元影像索引將會十分有效，並且能夠節省儲存空間。

3.建立

建立索引要知道哪些索引帶來的好處大於開銷。建立索引的最基本的命令是：

<span style="font-size:18px;color:#000099;"><strong>CREATE INDEX Index_nameON Table_name(Column_name1,Column_name2,...)</strong></span>

如同前面多次遇到的情況一樣，CREATE INDEX語句的文法在不同資料庫系統中差別很大。但所有的資料庫系統都支援上述基本命令。

3.1複合索引

複合索引是指一個索引中包含了一個以上的列，也稱為複合式索引。在SQL Server中最多可以由16個列組合到一個索引中。

舉例來說，在資料庫“珠寶銷售系統”中，查詢所在城市為北京市的珠寶商資訊。由於珠寶商所在城市的列值多次出現了重複值，則該列的選擇性就比較低，如果在該列建立一個單列索引，那麼氣查詢效能不是很高、注意珠寶商編號列的每一個值都是唯一值。為了提高索引的選擇性，可以建立一個組合珠寶商所在城市和珠寶商編號的複合索引。其代碼如下：

<span style="font-size:18px;color:#000099;"><strong>CREATE INDEX  所在城市_IndexON 珠寶商資訊(珠寶商所在城市，珠寶商編號)</strong></span>

3.2唯一索引和主鍵索引

唯一索引可以確保列中不包含重複的值。當向具有唯一索引的表添加資料時，資料庫會檢查新添加的行在該列是否具有重複值。如果新添加的行與其他已經存在行在唯一索引列出現重複值，則資料庫會顯示錯誤，並且撤銷輸入操作。

只有當唯一性是資料本身的特性時，指定唯一索引才有意義。例如，當建立一個學籍管理系統資料庫時，我們不想對學生姓名建立唯一索引，因為可能存在姓名相同的學生。正確的做法是，為每個學生建立一個學號，並在學號欄位上建立唯一索引。

建立唯一索引的方法非常簡單，只需在CREATE INDEX中使用UNIQUE關鍵字即可，這樣就組成了唯一符合索引。

舉例來說，在“銷售管理系統”的操作人員資訊表中規定：同名的操作人員必須設定不用密碼，以保證系統的安全性。那麼應該在密碼和操作人員列上建立一個唯一符合索引：

<span style="font-size:18px;color:#000099;"><strong>CREATE UNIQUE INDEX 操作人員安全_IndexON 操作人員資訊(操作人員姓名，密碼) </strong></span>

現在向資料表操作人員資訊添加一個新的操作人員資訊，新添加的操作人員資訊是表中已重複的資料。

<span style="font-size:18px;color:#000099;"><strong></strong></span><pre name="code" class="sql">INSERT INTO 操作人員資訊(操作人員編號，操作人員姓名，密碼)VALUES(1009，'周慧芳'，'9698xie')

執行上述語句應該會收到一個錯誤資訊，提示使用者不允許使用INSERT命令。這是由於該INSERT指令添加的資料違反了“操作人員安全_Index”索引。修改INSERT指令添加的密碼值。

<strong><span style="color:#000099;">INSERT INTO 操作人員資訊(操作人員編號，操作人員姓名，密碼)VALUES(1009，'周慧芳'，'9698')</span></strong>

主鍵索引是唯一索引的特殊類型。主鍵索引是在為表定義主鍵時自動建立的。在表中建立主鍵只需要列定義前使用關鍵字PRIMARY KEY即可。主鍵索引要求主鍵中的每一個值是唯一的。當在查詢中使用主鍵索引時，它還不允許快速存取資料。

當在表上定義主鍵或者唯一性限制式時，如果表中已經有了使用CREATE INDEX語句建立的標準索引時，那麼主鍵約束或者唯一性限制式建立的索引會覆蓋以前建立的標準索引。也就是說，主鍵索引或唯一性限制式建立的索引的優先順序高於使用CREATE INDEX語句建立的索引。

3.3聚集和非叢集索引

當表被使用叢集索引時，資料存放區在同一資料區塊中，這樣就只需要讀取極少的資料庫塊，從而導致更快的執行效能。當使用叢集索引時，表內資料的物理排列就修改了。使用叢集索引通常比非叢集索引更快。

用於建立叢集索引的列通常是主鍵。在SQL Server中，預設情況下建立的是索引為非叢集索引。除非使用關鍵字CLUSTERED指定建立叢集索引，當然也可以使用關鍵字NONCLUSTERED指定建立非叢集索引。

舉例來說，在資料庫“銷售管理系統”中，在供應商資訊表的供應商名稱列上建立唯一叢集索引。

<span style="font-size:18px;color:#000099;"><strong>USE 銷售管理系統GOCREATE UNIQUE CLUSTERED INDEX 供應商名稱_IndexON 供應商資訊(供應商名稱)GO</strong></span>

當為資料表建立叢集索引時，資料庫會對索引列進行排序。關鍵字ASC或DESC可以確保索引列的升序或降序排序方向。預設設定為ASC升序排列。

4.查看和刪除

在SQL Server中，索引的查看和刪除可以通過企業管理器和SQL語言兩種方式。

對於索引的查看，大家可以在SQL Server中實踐一把，很基礎的操作，這裡便不多做介紹。

當一個索引不再需要時，可以將其從資料庫中刪除，以回收它當前使用的儲存空間。這些回收的空間可以由資料庫中的任何對象使用。

索引的刪除方式和索引的建立時的使用方式有關。如果索引是使用CREATE INDEX語句顯式建立的，則可以使用DROP INDEX語句將其刪除。其文法格式為：

<span style="font-size:18px;color:#000099;"><strong>DROP INDEX 'table.index | view.index'[,...n]</strong></span>

其中，table和view表示索引所在的表或視圖；index表示要刪除的索引名稱，索引名稱必須是符合標識符的規則；[,...]表示前面的項可重複n次。

舉例來說，下面的SQL語句用於刪除資料庫“經銷商”的“商品資訊”中的索引“商品_INDEX”。

<strong><span style="font-size:18px;color:#000099;">USE 經銷商GODROP INDEX 商品資訊.商品_INDEX</span></strong>

如果想刪除一個索引，但是不能夠確定這個索引是否存在，同樣可以添加簡單的判斷語句先進行判斷。

舉例來說，刪除資料庫“經銷商”的“顧客資訊表”中名為“顧客資訊表_index”索引。

<span style="font-size:18px;color:#000099;"><strong>USE 經銷商IF EXISTS(SELECT name FROM sysindexes)          WHERE name='顧客資訊表_index'     DROP INDEX 顧客資訊表.顧客資訊表_indexGO</strong></span>

5.學習小結

對於索引這一章節的學習總結，感覺自己又學會了一個工具，而這個工具的作用正是我們想要的——提高效率。尤其是在做項目的時候，資料很大而不得做好索引，便於尋找和利用，很開心學習和總結這一章節的內容，收穫還蠻不錯的，主要還是先前所說，對於SQL的知識，實踐一把，很是值得，願與大家分享一下吧！