高性能的索引策略
創建正確的索引和恰當的使用它，對查詢的性能起到關鍵的作用。我們已經介紹了各種不同的索引的能力和弱點。下面我們將介紹索引的威力所在。
有很多的創建和選擇使用索引的有效方式，因為有很多特殊情況的優化和特殊行為。
孤立列
如果你不將索引列孤立出來，MySQL通常無法用到索引。"孤立"一列意思是它不能是表達式的
一部分或者在函數中。
比如：
Sql代碼
SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;
SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;
你可以把它改造成：
Sql代碼
SELECT actor_id FROM sakila.actor WHERE actor_id = 4;
SELECT ... WHERE date_col >= DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
你可以對第二個語句進行改造，將CURRENT_DATE作為一個具體的日期值，這樣
可以命中query cache：
Sql代碼
SELECT ... WHERE date_col >= DATE_SUB(2011-05-29, INTERVAL 10 DAY);
前綴索引和索引的選擇性
有時候你需要對一個很長字符串的列見索引，這會導致你的索引非常的大，並且蠻。一種
策略是建立hash索引，我們已經在前面介紹過。
你還可以采用另外的策略，比如對這列的前幾個字符建索引，而不是全部。索引的選擇性是指索引中不同的值的個數和所有行數的比率。一個高選擇性的索引是好的，因為能夠過濾掉更多的行。
一個前綴索引如果選擇性足夠好那麼可以獲得很高的性能。如果你使用BLOB或者TEXT，或者
非常長的VARCHAR列，那麼你必須定義前綴索引，因為MySQL不允許在全部長度上建索引。
一個選擇索引的技巧是選擇足夠長的前綴來獲得比較好的選擇性，但是又足夠短來節省空間。比如對一個城市表前7個字符創建索引：
Sql代碼
ALTER TABLE sakila.city_demo ADD KEY (city(7));
聚集索引：
聚集索引並不是單獨的索引的類型，它只是一種數據存儲方式。詳細的信息依賴於實現。InnoDB的聚集索引實際上是將B-Tree索引和行存儲到相同的結構中。一個具有聚集索引的表，它的行實際上存儲在索引的葉子節點中的。聚集的意思實際上是相鄰的key的值存儲在相鄰的空間中。一個表只能有一個聚集索引，因為你不能將一行數據同時存儲在兩個位置。
（然而，覆蓋索引可以讓你模擬多個聚集索引）
由於存儲引擎負責實現索引，並且不是所有的存儲引擎都支持聚集索引。現在只有solidDB和InnoDB支持。我們只討論InnoDB，但是有些原則適用於所有的聚集索引的存儲引擎。
有些數據庫可以讓你選擇聚集的索引，但是MySQL至少現在還不允許。InnoDB通過主鍵來聚集。如果你沒有定義主鍵，InnoDB將試著使用非空的具有唯一性的索引列。如果沒有這樣的索引，InnoDB將定義一個隱藏的主鍵，然後使用它來聚集。InnoDB只有在同一個也的記錄才會聚集，因此相鄰的key可能數據存儲距離很遠。
聚集索引可以幫助提高性能，但是仍然會導致一些嚴重的性能問題。你需要對聚集特別小心，特別是你從InnoDB和其他存儲引擎切換的時候。
聚集索引優點：
1.將相關的數據保存相近的位置。比如你實現一個郵件系統，你可以按照user_id來聚集，這樣你可以所有通過訪問很少的磁盤頁就可以得到單個用戶的所有的message。如果你沒有聚集索引，那麼每一個message都需要一次磁盤I/O。
2.數據訪問訪問快。聚集索引在B-Tree上同時持有索引和數據，所以從聚集索引中獲得數據要比沒有聚集索引要快。
3.使用覆蓋索引的查詢，可以使用在存儲在葉子節點主鍵的值，不需要根據key再去找對應的行。
聚集索引缺點：
1.聚集索引使得I/O負載最大程度的改善。但如果數據可以放在內存中，那麼訪問的順序就沒有那麼大的關系，所以聚集索引沒有提供更多的好處。
2.插入的速度依賴於插入的次序。按照主鍵的順序插入到InnoDB表中，是最快的方式。如果你沒有按照主鍵的順序加載數據，那麼最好在加載完之後使用optimize table來重新組織表。
3.更新聚集索引列非常耗時，因為這迫使InnoDB跟新行到新的位置。
4.按照聚集索引創建的表，當有新的記錄插入時，如果不是按照主鍵順序，那麼可能導致頁的分裂。當一行的key需要在一個滿了的頁插入數據時，會導致頁的分裂。頁面的分裂導致表用更多的空間。
5.二級索引將會變得非常大，因為葉子節點包含了key所引用的行。
6.二次索引需要兩次索引查找而不是一次。
覆蓋索引
索引是用來高效的查找行的，但是MySQL可以使用索引來檢索一列的數據，所以不再需要去讀行。索引的葉子節點存有想找的數據，所以不需要在去讀取行再去找要檢索的數據了。這種索引中包含了query要檢索的數據被稱為覆蓋索引。
覆蓋索引是一個非常強大的能夠很大程度提高性能的工具。只讀取索引而不再讀取行中的數據的優點：
1.索引的大小要比表中所有行的大小小的多，所以Mysql只需要訪問很少的數據就能得到需要的數據。這對Cache非常有好處，因為索引要比數據小的多，放在內存中更好。這個對MyISAM更是如此，因為他的索引是壓縮的，這讓它更小。
2.索引是按照索引的值排序的，所以這比從磁盤中訪問每一行需要更少I/O。對於一些存儲引擎，比如MyISAM，你可以使用OPTIMIZE來得到完全排序的索引，這讓簡單的去區間的query，完全使用順序的索引訪問。
3.很多的存儲引擎cache索引藥比數據更好.(Falcon是一個例外）。一些存儲引擎比如
MyISAM，僅僅在MySQL內存中緩存索引，因為操作系統為MyISAM緩存數據，訪問它需要系統調用，所以可能引起很大的性能開銷，尤其在緩存，系統調用是一個非常昂貴的數據訪問部分。
4.覆蓋索引對於InnoDB表非常有幫助，由於InnoDB的聚集索引。InnoDB的二級索引的葉子幾點保存主鍵的值，所以二級索引的如果覆蓋了查詢的數據，就可以避免主鍵的二次查詢。
覆蓋索引不是任何索引類型都可以的。索引必須能夠存儲索引列的值才可以，所以Hash,空間，全文索引都不能存儲這些值，只有B-Tree索引才可以。並且不同的存儲引擎支持的不同（比如內存和Falcon引擎現在還不可以）。
當一個查詢被一個索引覆蓋，可以使用Explain的Extra列看到使用"Using index"。
使用索引掃描來排序
MySQL有兩種方式來生成有序的結果集：1.filesort 2.按照索引的順序掃描。
可以使用Explain來查看查詢計劃中的type列是否有”index“來判斷。
掃描索引是很快的，因為它只需要從index的一個entry到另一個。但是如果MySQL不能使用index去覆蓋查詢，那麼需要根據索引去查找每一行，這基本上是一個隨機的I/O操作，所以按照索引的順序讀取數據通常要比順序掃描表要慢的多。
MySQL可以同時使用相同的索引來排序和查找。使用索引來將結果集合排序，只有索引的順序和ORDER BY的順序一致，並且所有排序的列都是相同的方向排序（降序或者升序）的時候才可以。如果多個表進行Join的話，那麼只有第一個表的列在order by中出現，並且需要order by滿足最左匹配。其他的情況MySQL都是使用filesort.
壓縮（前綴壓縮）索引
MyISAM使用前綴壓縮來減小index的大小，這樣可以讓更多的索引放到內存中，在某些情況能很大的優化性能。MyISAM壓縮字符類型的值，你也可以告訴它壓縮整數的值。
MyISAM將每一個索引塊進行壓縮，它完全的存儲索引塊的第一個值，然後通過記錄和前面值的最長公共前綴的大小，加上不同的後綴值的方式來存儲其他的值。比如第一個是perform，第二個是performance，那麼第二個值將被存儲為7,ance。MySQL同事對相鄰行的指針進行前綴壓縮的方式存儲。
壓縮的塊使用較少的空間，但是讓某些操作變慢。因為每一個前綴壓縮的值都依賴於前面的值，所以MyISAM不能使用二分查找來找到索引塊中的值，必須通過從開始順序掃描的方式。
順序的向前掃描很高效，但是相反的掃描方向，比如order by desc，不能很好的工作。
任何在塊中央的值都需要順序掃描，平均需要掃描半個塊。我們做性能測試發現，壓縮的索引要慢好幾倍，因為掃描需要隨機查找，逆向的掃描會更糟。這是cpu和I/O操作的一個權衡，壓縮的索引可能會在磁盤空間上是原來的1/10左右。
你可以通過在創建表的時候使用PACK_KEYS選項來控制一個table的索引被壓縮。
多余和重復的索引
MySQL允許對一個列建多次索引，MySQL需要獨立的維護這些重復的索引，query的優化也要考慮他們每一個。這會導致嚴重的性能問題。
你可能不經意間創建重復的索引，比如：
Sql代碼
CREATE TABLE test (
ID INT NOT NULL PRIMARY KEY,
UNIQUE(ID),
INDEX(ID)
);
MySQL會對UNIQUE和PRIMARY KEY約束的列自動建索引，所以會導致重復對一個列ID創建三個索引。
多余的索引和重復索引有點不同。如果你對（A，B）建立了聯合索引，另外對A建立了索引，那麼索引A是重復的，因為他是第一個索引的前綴。
索引和鎖
索引在InnoDB中起到了重要的作用，因為它讓查詢鎖住更少的行，這是一個重要的考慮，因為MySQL 5.0的InnoDB知道事務結束才解鎖行。
如果查詢不觸及不需要的行，那麼就會鎖住更少的行，這樣會得到更好的性能：
1.雖然InnoDB的所非常高效、使用比較少的內存，但是仍然可能導致一些行過早的鎖住。
2.鎖住更多的行增加了鎖的競爭，減少並發性。
盡可能的去擴展索引，而不是添加一個新的索引，因為通常維護多列的索引要比幾個單列的索引要搞笑，如果你不知道你query的分布，那麼盡量在有區分度的列建索引。
支持多種過濾條件
在有區分度的列建索引通常會更高效，
