創建高性能的索引

選擇合適的索引列順序

當使用前綴索引的時候，在某些條件值的基數比正常值高的時候，問題就來了。例如，在某些應用程序中，對于沒有登錄的用戶，都將其用戶名記錄為"guest"，在記錄用戶行為的會話(session)表和其他記錄用戶活動的表中"guest"就成為了一個特殊用戶ID.一旦查詢涉及這個用戶，那么和對于正常用戶的查詢就大不同了，因為通常由很多會話都是沒有登錄的。系統賬號也會導致類似的問題。一個應用通常都有一個特殊的管理員賬號，和普通賬號不同，它并不是一個具體的用戶，系統中所有的其他用戶都是這個用戶的好友，所以系統往往通過它向網站的所有用戶發送狀態通知和其他消息。這個賬號的巨大的好友列表很容易導致網站初夏你服務器性能問題。這實際上是一個非常典型的問題。任何的異常用戶，不僅僅是那些用于管理應用的設計糟糕的賬號會有同樣的問題；那些擁有大量好友、圖片、狀態、收藏的用戶，也會有前面提到的系統賬號同樣的問題。下面s是一個真實案例，在一個用戶分享購買商品和購買經驗的論壇上，這個特殊表上的查詢運行得非常慢:

mysql> SELECT  COUNT(DISTINCT threadId) AS COUNT_VALUE FROM Message-> WHERE (groupId = 10137) AND (userId = 1288826) AND (anonymous = 0)-> ORDER BY priority DESC, modifiedDate DESC-> ;

這個查詢看似沒有建立合適的索引，所以客戶咨詢是否可以優化。EXPLAIN的結果如下:

id:1
select_type:SIMPLE
table:Message
type:ref
key:idx_groupId_userId
key_len:18
ref:const,const
rows:1251162
Extra:Using where

MySQL為這個查詢選擇了索引(groupId, userId)，如果不考慮列的基數，這看起來是一個非常合理的選擇。但如果考慮一下userID和groupID條件匹配的行數，可能就會有不同的想法了:

mysql> SELECT COUNT(*) , SUM(groupId=10137), SUM(userId=1288826),SUM(anonymous = 0)-> FROM Message\G
*************************** 1. row ***************************
COUNT(*):4142217
SUM(groupId=10137):4092654
SUM(userId=1288826):1288496
SUM(anonymous=0):4141934

從上面的結果來看符合組(groupId)條件幾乎滿足表中的所有行，符合用戶(userId)條件的有130彎條記錄——也就是說索引基本上沒什么用。因為這些數據是從其他應用中遷移過來的，遷移的時候把所有的消息都賦予了管理員組的用戶。這個案例的解決辦法是修改應用程序代碼。去分這類特殊用戶和組，禁止針對這類用戶和組執行這個查詢。從這個小案例可以看到經驗法則和推論在多數情況下是有用的，但要注意不要假設平均情況下的性能也能代表特殊情況下的性能，特殊情況可能會摧毀整個應用的性能。最后，盡管關于選擇性和基數的經驗法則值得去研究和分析，但一定要記住別忘了WHERE子句中的排序、分組和范圍條件等其他因素，這些因素可能對查詢的性能造成非常大的影響。

聚簇索引

聚簇索引并不是一種單獨的索引類型，而是一種數據存儲方式。具體的細節依賴于其實現方式但InnoDB得聚簇索引實際上在同一個結構中保存了B-Tree索引和數據行。當表有聚簇索引時，它的數據行實際上存放在索引的葉子頁(leaf page)中。術語"聚簇"表示數據行和相鄰的鍵值緊湊地存儲在一起。因為無法同時把數據行存放在兩個不同的地方，所以一個表只能有一個聚簇索引(不過，覆蓋索引可以模擬多個聚簇索引的情況)。因為是存儲引擎負責實現索引，因此不是所有的存儲引擎都支持聚簇索引。主要關注InnoDB.如圖展示了聚簇索引中的記錄是如何存放的。注意到，葉子頁包含了行的全部數據，但是節點頁只包含了索引列。該圖中，索引包含的是整數值。
一些數據庫服務器允許選擇哪個索引作為聚簇索引，但是目前市場上，還沒有任何一個MySQL內建的存儲引擎支持這一點。InnoDB將通過主鍵聚集數據，這也就是說上圖中的"被索引的列"就是主鍵列。如果沒有定義主鍵，InnoDB會選擇一個唯一的非空索引代替。如果沒有這樣的素銀，InnoDB會隱式定義一個主鍵來作為聚簇索引，InnoDB只聚集在同一個頁面中的記錄。包含相鄰鍵值的頁面可能會相距甚遠。
聚簇索引可能對性能有幫助，但也可能導致嚴重的性能問題。所以需要仔細地考慮聚簇素銀，尤其是將表的存儲引擎從InnoDB改成其他引擎的時候(反過來也一樣)。聚集的數據有一些重要的優點:

• 1.可以把相關數據保存在一起。例如實現電子郵箱時，可以根據用戶ID來聚集數據，這樣只需要從磁盤讀取少數的數據也就能獲取某個用戶的全部郵件。如果沒有使用聚簇索引，則每封郵件都可能導致一次磁盤IO
• 2.數據訪問更快。聚簇索引將索引和數據保存在同一個B-Tree中，因此聚簇索引中獲取數據通常比在非聚簇索引中查找要快。
• 3.使用覆蓋索引掃描的查詢可以直接使用葉節點中的主鍵值
  如果在設計表和查詢時能充分利用上面的優點，那就能極大地提升性能。

同時，聚簇索引也有一些缺點:

• 1.聚簇數據最大限度地提高了IO密集型應用的性能，但入股哦數據全部都存放在內存中，則訪問的順序就沒那么重要了，聚簇索引也就沒什么優勢了
• 2.插入速度嚴重依賴于插入順序。按照主鍵的順序插入是加載數據到InnoDB表中速度最快的方式。但如果不是按照主鍵順序加載數據，那么在加載完成后最好使用OPTIMIZE TABLE命令重新組織一下表
• 3.更新聚簇索引列的代價很高，因為會強制InnoDB將每個被更新的行移動到新的位置
• 4.基于聚簇索引的表在插入新行，或者主鍵被更新導致需要移動行的時候，可能面臨"頁分裂(page split)"的問題。當行的主鍵值要求必須將這一行插入到某個已滿的頁中時，存儲引擎會將該頁分裂成兩個頁面來容納該行，這就是一次頁分裂操作。也分裂會導致表占用更多的磁盤空間
• 5.聚簇索引可能導致全表掃描變慢，尤其是行比較稀疏，或者由于頁分裂導致數據存儲不連續的時候
• 6.二級索引(非聚簇索引)可能比想象的要更大，因為在二級索引的葉子節點包含了引用行的主鍵列
• 7.二級索引訪問需要兩次索引查找，而不是一次

最后一點可能讓人有些疑惑，為什么二級索引需要兩次索引查找?答案在于二級索引中保存的"行指針"的實質。要記住，二級索引葉子節點保存的不是指向行的物理位置的指針，而是行的主鍵值。這意味著通過二級索引查找行，存儲引擎需要找到二級索引的葉子節點獲得對應的主鍵值，然后根據這個值去聚簇索引中查找到對應的行。這里做了重復的工作:兩次B-Tree查找而不是一次(順便提一下，并不是所有的非聚簇索引都能做到一次索引查詢就找到行。當行更新的時候可能無法存儲在原來的位置，這會導致表中出現行的碎片花或者移動行并在原位置保存"向前指針"。這兩種情況都會導致查找行時需要更多的工作)，對于InnoDB，自適應哈希索引能夠減少這樣的重復工作