(轉)資料庫中索引的政策和設計

​

    下面讨論下MySQL5.0中的索引的設計和使用。任何東西設計的好，那麼使用起來就順手。不過很多時候給出設計什麼規則這些都是相對的。做任何的事情，最重要的是能否根據當時情況就合理的調整你的設計。如果你隻會看着書本或者權威來死套什麼設計理念來進行實際的開發和設計的話，那麼我請你還是少讀書為妙。 

索引是資料庫中用來提高性能的常用工具。（注意如果要優化資料庫的性能，這是一個點）。 

  所有MySQL列類型都可以被索引，對相關列使用索引是提高SELECT操作性能的最佳途徑。根據存儲引擎可以定義每個表的最大索引數和最大索引長度，每種存儲引擎(MyISAM、InnoDB、BDB、MEMORY等)對每個表至少支援16個索引，總索引長度至少為256位元組。大多數存儲引擎有更高的限制。 

   MyISAM和InnoDB存儲引擎的表預設建立的都是BTREE索引。MySQL目前還不支援函數索引，但是支援字首索引，即對索引字段的前N個字元建立索引。字首索引的長度跟存儲引擎相關，對于MyISAM存儲引擎的表，索引的字首長度可以達到1000位元組長，而對于Inn0DB存儲引擎的表，索引的字首長度最長是767位元組。請注意字首的限制應以位元組為機關進行測量，而CREATE TABLE語句中的字首長度解釋為字元數。在為使用多位元組字元集的列指定字首長度時一定要加以考慮。 

MySQL中還支援全文本索引（FULLTEXT），該索引可以用于全文搜尋。但是在Mysql5.0中隻有MyISAM存儲引擎支援全文本索引，并且僅僅局限于CHAR、VARCHAR和TEXT列。索引總是對整個列進行的，不支援局部索引。也可以為空間類型建立索引，但是隻要MyISAM存儲引擎支援空間類型索引，而且索引的字段必須是非空。 

建立索引的文法如下： 

引用

   CREATE [UNIQUE | FULLTEXT | SPATIAL] IDEX index_name 

   [USING index_type] 

   ON tb1_name (index_col_name,....) 

index_col_name: 

   col_name [(length)][ASC | DESC] 

索引的建立可以在建立表的時候就建立，也可以随時增加新的索引。 

下面給出一個例子： 

  create index personname on person (person(10)) 

查詢,可以發現索引personname被使用 

explain select * from person where person='hi' \G 

索引的删除文法為： 

DROP INDEX index_name ON tb1_name 

如果想删除person表中的personname索引如下操作 

   drop index personname on person 

上面簡單的示範了索引的建立和删除。 

接下來讨論下索引的設計原則： 

   索引的設計可以遵循一些已有的原則，建立索引的時候請盡量考慮符合這些原則，便于提升索引的使用效率，更高效地使用索引。 

1. 搜尋的索引列，不一定是所要選擇的列。最合适索引的列是出現在WHERE子句中的列，或連接配接子句中指定的列，而不是出現在SELECT關鍵字後的選擇清單中的列。 

2. 使用唯一索引。考慮某列中值的分布。索引的列的基數越大，索引的效果越好。例如資料庫中有好幾年的資料，這些資料中有日期這個字段，而且查詢中常常要區分日期。那麼針對日期設索引就很容易區分。 

3. 使用短索引。如果對字元串列進行索引，應該指定字首長度，隻要有可能就應該這樣做。例如：如果有一個CHAR(200)的列，如果在前10個或20個字元内，多數值是唯一的。那麼就不要對整個列進行索引。對前10個或者20個字元進行索引能夠節省大量索引空間，也可能會使查詢更快。較小的索引涉及的磁盤IO較少，較短的值比較起來更快。更為重要的是，對于較短的鍵值，索引高速緩存中的塊能容納更多的鍵值，是以,MySQL也可以在記憶體中容納更多的值。這樣就增加了找到行而不用讀取索引中較多塊的可能性。 

4.利用最左字首。在建立一個n列的索引時，實際是建立了MySQL可利用的n個索引。多列索引起幾個索引的作用，因為可利用索引中最左的列集來比對行。這樣的列集稱最左字首。 

5. 不要過度索引。不要以為索引“越多越好”，什麼東西都用索引是錯誤的。每個額外的索引都要占用額外的磁盤空間，并降低寫操作的性能。在修改表的内容時，索引必須進行更新，有時可能需要重構，是以，索引越多，所花的時間越長。如果有一個索引很少利用或者從不使用，那麼會不必要地減緩表的修改速度。此為MySQL在生成一個執行計劃時，要考慮各個索引，這也要花費時間。建立多餘的索引給查詢優化帶來了更多的工作。索引太多，也可能會使MySQL選擇不到所要使用的最好索引。值保持所需的索引有利于查詢優化。 

6. 對于InnoDB存儲引擎的表，記錄預設會按照一個的順序儲存，如果有明确定義的主鍵，則按照主鍵順序儲存。如果沒有主鍵，但是有唯一索引，那麼就是按照唯一索引的順序儲存。如果既沒有主鍵有沒有唯一索引，那麼表中會自動生成一個内部列，按照這個列的順序儲存。按照主鍵或者内部列進行的通路是最快的，是以InnoDB表盡量自己指定主鍵，當表中同時有幾個列都是唯一的，都可以作為主鍵的時候，要選擇最常作為通路條件的列作為主鍵，提高查詢的效率。另外還需要注意，InnoDB表的普通索引都會儲存主鍵的鍵值，是以主鍵要盡可能選擇較短的資料類型，可以有效減少索引的磁盤占用，提高索引的緩存效果。 

BTREE索引與HASH索引 

   MEMORY存儲引擎的表可以選擇使用BTREE或者HASH索引，兩種不同類型的索引各有其不同的适用範圍。HASH索引有些重要的特征需要在使用的時候特别注意，如下所示： 

1. 隻用于使用=或<=>操作符的等式比較。 

2. 優化器不能使用HASH索引來加速ORDER BY操作 

3. MySQL不能确定在兩個值之間大約有多少行。如果将一個MyISAM表改為HASH索引的MEMORY表，會影響一些查詢的執行效率。 

4. 隻能使用整個關鍵字來搜尋一行。 

而對于BTREE索引，當使用 > 、< 、>= 、<= 、BETWEEN 、!= 或者 <> ,或者LINKE'pattern'（其中'pattern'不以通配符開始）操作符時，都可以使用相關列上的索引。

索引的類型和使用場合

索引分單列索引群組合索引

   單列索引：即一個索引隻包含單個列，一個表可以有多個單列索引，但這不是組合索引。

   組合索引：即一個索包含多個列。

介紹一下索引的類型

    1.普通索引。

      這是最基本的索引，它沒有任何限制。它有以下幾種建立方式：

      （1）建立索引：CREATE INDEX indexName ON tableName(tableColumns(length));如果是CHAR,VARCHAR類型，length可以小于字段實際長度;如果是BLOB 和 TEXT 類型，必須指定length，下同。

      （2）修改表結構：ALTER tableName ADD INDEX [indexName] ON (tableColumns(length)) 

      （3）建立表的時候直接指定：CREATE TABLE tableName ( [...], INDEX [indexName] (tableColumns(length)) ;

    2.唯一索引。

       它與前面的"普通索引"類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。它有以下幾種建立方式：

       （1）建立索引：CREATE UNIQUE INDEX indexName ON tableName(tableColumns(length))

      （2）修改表結構：ALTER tableName ADD UNIQUE [indexName] ON (tableColumns(length))

      （3）建立表的時候直接指定：CREATE TABLE tableName ( [...], UNIQUE [indexName] (tableColumns(length));

     3.主鍵索引

       它是一種特殊的唯一索引，不允許有空值。一般是在建表的時候同時建立主鍵索引：CREATE TABLE testIndex(i_testID INT NOT NULL AUTO_INCREMENT,vc_Name VARCHAR(16) NOT NULL,PRIMARY KEY(i_testID)); 當然也可以用ALTER指令。

       記住：一個表隻能有一個主鍵。

      4.全文索引

        MySQL從3.23.23版開始支援全文索引和全文檢索。這裡不作讨論，呵呵~~

    删除索引的文法：DROP INDEX index_name ON tableName

單列索引群組合索引

    為了形象地對比兩者，再建一個表：

    CREATE TABLE myIndex ( i_testID INT NOT NULL AUTO_INCREMENT, vc_Name VARCHAR(50) NOT NULL, vc_City VARCHAR(50) NOT NULL, i_Age INT NOT NULL, i_SchoolID INT NOT NULL, PRIMARY KEY (i_testID) );

    在這10000條記錄裡面7上8下地分布了5條vc_Name="erquan"的記錄，隻不過city,age,school的組合各不相同。

  來看這條T-SQL：

    SELECT i_testID FROM myIndex WHERE vc_Name='erquan' AND vc_City='鄭州' AND i_Age=25;

    首先考慮建單列索引：

    在vc_Name列上建立了索引。執行T-SQL時，MYSQL很快将目标鎖定在了vc_Name=erquan的5條記錄上，取出來放到一中間結果集。在這個結果集裡，先排除掉vc_City不等于"鄭州"的記錄，再排除i_Age不等于25的記錄，最後篩選出唯一的符合條件的記錄。

    雖然在vc_Name上建立了索引，查詢時MYSQL不用掃描整張表，效率有所提高，但離我們的要求還有一定的距離。同樣的，在vc_City和i_Age分别建立的單列索引的效率相似。

    為了進一步榨取MySQL的效率，就要考慮建立組合索引。就是将vc_Name,vc_City,i_Age建到一個索引裡：

    ALTER TABLE myIndex ADD INDEX name_city_age (vc_Name(10),vc_City,i_Age);--注意了，建表時，vc_Name長度為50，這裡為什麼用10呢？因為一般情況下名字的長度不會超過10，這樣會加速索引查詢速度，還會減少索引檔案的大小，提高INSERT的更新速度。

    執行T-SQL時，MySQL無須掃描任何記錄就到找到唯一的記錄！！

    肯定有人要問了，如果分别在vc_Name,vc_City,i_Age上建立單列索引，讓該表有3個單列索引，查詢時和上述的組合索引效率一樣吧？嘿嘿，大不一樣，遠遠低于我們的組合索引~~雖然此時有了三個索引，但MySQL隻能用到其中的那個它認為似乎是最有效率的單列索引。

    建立這樣的組合索引，其實是相當于分别建立了

        vc_Name,vc_City,i_Age

        vc_Name,vc_City

        vc_Name

    這樣的三個組合索引！為什麼沒有vc_City,i_Age等這樣的組合索引呢？這是因為mysql組合索引"最左字首"的結果。簡單的了解就是隻從最左面的開始組合。并不是隻要包含這三列的查詢都會用到該組合索引，下面的幾個T-SQL會用到：

    SELECT * FROM myIndex WHREE vc_Name="erquan" AND vc_City="鄭州"

    SELECT * FROM myIndex WHREE vc_Name="erquan"

而下面幾個則不會用到：

    SELECT * FROM myIndex WHREE i_Age=20 AND vc_City="鄭州"

    SELECT * FROM myIndex WHREE vc_City="鄭州"

使用索引

    到此你應該會建立、使用索引了吧？但什麼情況下需要建立索引呢？一般來說，在WHERE和JOIN中出現的列需要建立索引，但也不完全如此，因為MySQL隻對 <，<=，=，>，>=，BETWEEN，IN，以及某些時候的LIKE(後面有說明)才會使用索引。

    SELECT t.vc_Name FROM testIndex t LEFT JOIN myIndex m ON t.vc_Name=m.vc_Name WHERE m.i_Age=20 AND m.vc_City='鄭州'  時，有對myIndex表的vc_City和i_Age建立索引的需要，由于testIndex表的vc_Name開出現在了JOIN子句中，也有對它建立索引的必要。

    剛才提到了，隻有某些時候的LIKE才需建立索引？是的。因為在以通配符 % 和 _ 開頭作查詢時，MySQL不會使用索引，如

    SELECT * FROM myIndex WHERE vc_Name like'erquan%'

會使用索引，而

    SELECT * FROM myIndex WHEREt vc_Name like'%erquan'

    就不會使用索引了。

索引的不足之處

    上面說了那麼多索引的好話，它真的有像傳說中那麼優秀麼？當然會有缺點了。

    1.雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要儲存資料，還要儲存一下索引檔案

    2.建立索引會占用磁盤空間的索引檔案。一般情況這個問題不太嚴重，但如果你在一個大表上建立了多種組合索引，索引檔案的會膨脹很快。

寫在前面：索引對查詢的速度有着至關重要的影響，了解索引也是進行資料庫性能調優的起點。考慮如下情況，假設資料庫中一個表有10^6條記 錄，DBMS的頁面大小為4K，并存儲100條記錄。如果沒有索引，查詢将對整個表進行掃描，最壞的情況下，如果所有資料頁都不在記憶體，需要讀取10^4 個頁面，如果這10^4個頁面在磁盤上随機分布，需要進行10^4次I/O，假設磁盤每次I/O時間為10ms(忽略資料傳輸時間)，則總共需要 100s(但實際上要好很多很多)。如果對之建立B-Tree索引，則隻需要進行log100(10^6)=3次頁面讀取，最壞情況下耗時30ms。這就 是索引帶來的效果，很多時候，當你的應用程式進行SQL查詢速度很慢時，應該想想是否可以建索引。進入正題： 

1、選擇索引的資料類型 

MySQL支援很多資料類型，選擇合适的資料類型存儲資料對性能有很大的影響。通常來說，可以遵循以下一些指導原則： 

(1)越小的資料類型通常更好：越小的資料類型通常在磁盤、記憶體和CPU緩存中都需要更少的空間，處理起來更快。 

(2)簡單的資料類型更好：整型資料比起字元，處理開銷更小，因為字元串的比較更複雜。在MySQL中，應該用内置的日期和時間資料類型，而不是用字元串來存儲時間；以及用整型資料類型存儲IP位址。 

(3)盡量避免NULL：應該指定列為NOT NULL，除非你想存儲NULL。在MySQL中，含有空值的列很難進行查詢優化，因為它們使得索引、索引的統計資訊以及比較運算更加複雜。你應該用0、一個特殊的值或者一個空串代替空值。 

1.1、選擇辨別符 

選擇合适的辨別符是非常重要的。選擇時不僅應該考慮存儲類型，而且應該考慮MySQL是怎樣進行運算和比較的。一旦標明資料類型，應該保證所有相關的表都使用相同的資料類型。 

(1)   整型：通常是作為辨別符的最好選擇，因為可以更快的處理，而且可以設定為AUTO_INCREMENT。 

(2)   字元串：盡量避免使用字元串作為辨別符，它們消耗更好的空間，處理起來也較慢。而且，通常來說，字元串都是随機的，是以它們在索引中的位置也是随機的，這會導緻頁面分裂、随機通路磁盤，聚簇索引分裂（對于使用聚簇索引的存儲引擎）。 

2、索引入門 

對于任何DBMS，索引都是進行優化的最主要的因素。對于少量的資料，沒有合适的索引影響不是很大，但是，當随着資料量的增加，性能會急劇下降。 

如果對多列進行索引(組合索引)，列的順序非常重要，MySQL僅能對索引最左邊的字首進行有效的查找。例如： 

假 設存在組合索引it1c1c2(c1,c2)，查詢語句select * from t1 where c1=1 and c2=2能夠使用該索引。查詢語句select * from t1 where c1=1也能夠使用該索引。但是，查詢語句select * from t1 where c2=2不能夠使用該索引，因為沒有組合索引的引導列，即，要想使用c2列進行查找，必需出現c1等于某值。 

2.1、索引的類型 

索引是在存儲引擎中實作的，而不是在伺服器層中實作的。是以，每種存儲引擎的索引都不一定完全相同，并不是所有的存儲引擎都支援所有的索引類型。 

2.1.1、B-Tree索引 

假設有如下一個表： 

CREATE TABLE People ( 

   last_name varchar(50)    not null, 

   first_name varchar(50)    not null, 

   dob        date           not null, 

   gender     enum('m', 'f') not null, 

   key(last_name, first_name, dob) 

); 

其索引包含表中每一行的last_name、first_name和dob列。其結構大緻如下： 

索引存儲的值按索引列中的順序排列。可以利用B-Tree索引進行全關鍵字、關鍵字範圍和關鍵字字首查詢，當然，如果想使用索引，你必須保證按索引的最左邊字首(leftmost prefix of the index)來進行查詢。 

(1)比對全值(Match the full value)：對索引中的所有列都指定具體的值。例如，上圖中索引可以幫助你查找出生于1960-01-01的Cuba Allen。 

(2)比對最左字首(Match a leftmost prefix)：你可以利用索引查找last name為Allen的人，僅僅使用索引中的第1列。 

(3)比對列字首(Match a column prefix)：例如，你可以利用索引查找last name以J開始的人，這僅僅使用索引中的第1列。 

(4)比對值的範圍查詢(Match a range of values)：可以利用索引查找last name在Allen和Barrymore之間的人，僅僅使用索引中第1列。 

(5)比對部分精确而其它部分進行範圍比對(Match one part exactly and match a range on another part)：可以利用索引查找last name為Allen，而first name以字母K開始的人。 

(6)僅對索引進行查詢(Index-only queries)：如果查詢的列都位于索引中，則不需要讀取元組的值。 

由于B-樹中的節點都是順序存儲的，是以可以利用索引進行查找(找某些值)，也可以對查詢結果進行ORDER BY。當然，使用B-tree索引有以下一些限制： 

(1) 查詢必須從索引的最左邊的列開始。關于這點已經提了很多遍了。例如你不能利用索引查找在某一天出生的人。 

(2) 不能跳過某一索引列。例如，你不能利用索引查找last name為Smith且出生于某一天的人。 

(3) 存儲引擎不能使用索引中範圍條件右邊的列。例如，如果你的查詢語句為WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23'，則該查詢隻會使用索引中的前兩列，因為LIKE是範圍查詢。 

2.1.2、Hash索引 

MySQL 中，隻有Memory存儲引擎顯示支援hash索引，是Memory表的預設索引類型，盡管Memory表也可以使用B-Tree索引。Memory存儲 引擎支援非唯一hash索引，這在資料庫領域是罕見的，如果多個值有相同的hash code，索引把它們的行指針用連結清單儲存到同一個hash表項中。 

假設建立如下一個表： 

CREATE TABLE testhash ( 

fname VARCHAR(50) NOT NULL, 

lname VARCHAR(50) NOT NULL, 

KEY USING HASH(fname) 

) ENGINE=MEMORY; 

包含的資料如下： 

假設索引使用hash函數f( )，如下： 

f('Arjen') = 2323 

f('Baron') = 7437 

f('Peter') = 8784 

f('Vadim') = 2458 

此時，索引的結構大概如下： 

Slots是有序的，但是記錄不是有序的。當你執行 

mysql> SELECT lname FROM testhash WHERE fname='Peter'; 

MySQL會計算’Peter’的hash值，然後通過它來查詢索引的行指針。因為f('Peter') = 8784，MySQL會在索引中查找8784，得到指向記錄3的指針。 

因為索引自己僅僅存儲很短的值，是以，索引非常緊湊。Hash值不取決于列的資料類型，一個TINYINT列的索引與一個長字元串列的索引一樣大。 

Hash索引有以下一些限制： 

(1)由于索引僅包含hash code和記錄指針，是以，MySQL不能通過使用索引避免讀取記錄。但是通路記憶體中的記錄是非常迅速的，不會對性造成太大的影響。 

(2)不能使用hash索引排序。 

(3)Hash索引不支援鍵的部分比對，因為是通過整個索引值來計算hash值的。 

(4)Hash索引隻支援等值比較，例如使用=，IN( )和<=>。對于WHERE price>100并不能加速查詢。 

2.1.3、空間(R-Tree)索引 

MyISAM支援空間索引，主要用于地理空間資料類型，例如GEOMETRY。 

2.1.4、全文(Full-text)索引 

全文索引是MyISAM的一個特殊索引類型，主要用于全文檢索。 

索引的不适用場合

表記錄太少

如果一個表隻有5條記錄，采用索引去通路記錄的話，那首先需通路索引表，再通過索引表通路資料表，一般索引表與資料表不在同一個資料塊，這種情況下ORACLE至少要往返讀取資料塊兩次。而不用索引的情況下ORACLE會将所有的資料一次讀出，處理速度顯然會比用索引快。

如表zl_sybm（使用部門）一般隻有幾條記錄，除了主關鍵字外對任何一個字段建索引都不會産生性能優化，實際上如果對這個表進行了統計分析後ORACLE也不會用你建的索引，而是自動執行全表通路。如：

select * from zl_sybm where sydw_bh=’5401’（對sydw_bh建立索引不會産生性能優化）

經常插入、删除、修改的表

對一些經常處理的業務表應在查詢允許的情況下盡量減少索引，如zl_yhbm，gc_dfss，gc_dfys，gc_fpdy等業務表。

資料重複且分布平均的表字段

假如一個表有10萬行記錄，有一個字段A隻有T和F兩種值，且每個值的分布機率大約為50%，那麼對這種表A字段建索引一般不會提高資料庫的查詢速度。

經常和主字段一塊查詢但主字段索引值比較多的表字段

如gc_dfss（電費實收）表經常按收費序号、戶辨別編号、抄表日期、電費發生年月、操作 标志來具體查詢某一筆收款的情況，如果将所有的字段都建在一個索引裡那将會增加資料的修改、插入、删除時間，從實際上分析一筆收款如果按收費序号索引就已 經将記錄減少到隻有幾條，如果再按後面的幾個字段索引查詢将對性能不産生太大的影響。