索引的設計與使用
5.1 Mysql索引概述
所有MySQL欄位類型可以被索引。對相關欄位使用索引是提高SELECT操作效能的最佳途徑。根據儲存引擎定義每個資料表的最大索引數和最大索引長度。所有儲存引擎支援每個表至少16個索引，總索引長度至少為256位元組。大多數儲存引擎有更高的限制。
在MySQL 5.1中，對於MyISAM和InnoDB表，前綴可以達到1000位元組長。請注意前綴的限制應以位元組為單位進行測量，而CREATE TABLE語句中的前綴長度解釋為字元數。當使用多位元組字元集的列指定前綴長度時一定要加以考慮。
也可以建立FULLTEXT索引。此索引可以用於全文搜尋。只有 MyISAM儲存引擎支援FULLTEXT索引，且只為CHAR、VARCHAR和TEXT欄位。索引總是對整個列進行，不支援局部(前綴)索引。也可以為空間列類型建立索引。只有MyISAM儲存引擎支援空間類型。空間索引使用R-樹。預設MEMORY(HEAP)儲存引擎使用hash索引，但也支援B-樹索引。
5.2 設計索引的原則
1. 搜尋的索引列，不一定是所要選擇的列。換句話說，最適合索引的欄位是出現在WHERE 子句中的資料列，或連接子句中指定的資料列，而不是出現在SELECT 關鍵字後的選擇清單中的資料列。
2. 使用惟一索引。考慮某列中位數的分佈。對於惟一值的列，索引的效果最好，而具有多個重複值的列，其索引效果最差。例如，存放年齡的列具有不同值，很容易區分各行。
而用來記錄性別的列，只含有“ M”和“F”，則對此列進行索引沒有多大用處（不管搜尋哪個值，都會得出大約一半的行）
3. 使用短索引。如果對串列進行索引，應該指定一個前綴長度，只要有可能就應該這樣做。
例如，如果有一個 CHAR(200) 欄，如果在前10 個或20 個字元內，多數值是惟一的，那麼就不要對整個欄位進行索引。對前10 個或20 個字元進行索引能夠節省大量索引空間，也可能會使查詢更快。較小的索引涉及的磁碟I/O 較少，較短的值比較起來更快。
更為重要的是，對於較短的鍵值，索引高速緩存中的區塊能容納更多的鍵值，因此，MySQL也可以在記憶體中容納更多的值。這增加 了找到行而不用讀取索引中較多區塊的可能性。
（當然，應該利用一些常識。 如僅用列值的第一個字符進行索引是不可能有多大好處的，因為這個索引中不會有許多不 同的值。 ）
4. 利用最左前綴。在建立一個n 欄位的索引時，實際上是建立了 MySQL可利用的n 個索引。
多列索引可起幾個索引的作用，因為可利用索引中最左邊的列集來匹配行。這樣的列集稱為最左前綴。 （這與索引一個列的前綴不同，索引一個列的前綴是利用該的前n個字元作為索引值。 ）
5. 不要過度索引。不要以為索引“越多越好”，什麼東西都用索引是錯的。每個額外的索引都要佔用額外的磁碟空間，並降低寫入操作的效能，這一點我們前面已經介紹過 過。在修改表的內容時，索引必須更新，有時可能需要重構，因此，索引越多，所花的時
間越長。如果有一個索引很少利用或從不使用，那麼會不必要地減緩表 的修改速度。
此外，MySQL在產生一個執行計畫時，要考慮各個索引，這也要費時間。創建多餘的索引為查詢優化帶來了更多的工作。索引太多，也可能會使 MySQL選擇不到所要使用的最佳索引。只保持所需的索引有利於查詢最佳化。如果想要為已索引的資料表增加索引，應該考慮所要增加的索引是否是現有多列索引的最左 索引。如果是，就不要費力增加這個索引了，因為已經有了。
6. 考慮在列上進行的比較類型。索引可用於「 =」、「 >」和BETWEEN 運算。在模式具有一個直接量前綴時，索引也用於LIKE 運算。如果只將某個欄位用於其他類型的運算時（如STRCMP( )） ，則對其進行索引沒有價值。
5.3 btree索引與hash索引
對於BTREE和HASH索引，當使用=、、IN、IS NULL或IS NOT NULL運算元時，關鍵元素與常數值的比較關係對應一個範圍條件。 Hash索引還有一些其它特徵：它們只用於使用=或操作符的等式比較(但很快)。 優化器不能使用hash索引來加速ORDER BY操作。
(該類別索引不能用來依序搜尋下一個條目）。 MySQL不能確定在兩個值之間大約有多少行(這被範圍優化器用來確定使用哪個索引)。 如果你將一個MyISAM表改為hash-索引的MEMORY表，會影響一些查詢。只能使用整個關鍵字來搜尋一行。 (用B-樹索引，任何關鍵字的最左面的前綴可用來找到行)。
對於BTREE索引，當使用>、=、，或LIKE 'pattern'(其中'pattern'不以通配符開始)運算子時， 關鍵元素與常數值的比較關係對應一個範圍條件。
「常數值」係指：查詢字串中的常數、同一聯接中的const或system表中的列、無關聯子查詢的結果、完全從前面類型的子表達式組成的表達式
下面是一些WHERE子句中有範圍條件的查詢的例子：
下列範圍查詢適用於btree索引和hash索引
SELECT * FROM t1WHEREkey_col = 1ORkey_col IN (15,18,20);
下列範圍查詢適用於btree索引
SELECT * FROM t1WHERE key_col > 1AND key_col SELECT * FROM t1WHERE key_col LIKE 'ab%'OR key_col BETWEEN 'bar' AND
'foo';
5.4 Mysql如何使用索引
索引有一特定值的行。不使用索引，MySQL必須從第1筆記錄開始然後讀取完整個表直到找出相關的行。表越大，花費的時間越多。如果表中查詢的欄位有索引，MySQL能快速到達一個位置去搜尋到資料檔案的中間，沒有必要看所有資料。如
果一個表格有1000行，比順序讀取至少快100倍。注意如果你需要存取大部分行，順序讀取要快得多，因為此時我們避免磁碟搜尋。
大多數MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B樹中儲存。只是空間列類型的索引使用R-樹，並且MEMORY表也支援hash索引。
關於什麼情況下資料庫會使用索引以及什麼情況下資料庫不會使用索引的詳細解釋請看優化篇的相關章節，這裡就不再累述。
想要取得更多相關文章請關注php中文網（www.php.cn）！
