[TOC] # 獨立的列 如果查詢中的列不是獨立的,則mysql就不會使用索引.`"獨立的列"`是指索引列不能是表達式的一部分,也不能是函數的參數 例如,下面這個查詢無法使用actor_id列的索引 ~~~ select actor_id from sakila.actor where actor_id + 1 = 5; ~~~ 我們應該養成簡化where條件的習慣 # 前綴索引和索引選擇性 有時候需要索引很長的字符列,這會讓索引變得大且慢.一個策略是前面提到的哈希索引,但有時這還不夠,還可以做些什么? 通常可以索引開始的部分字符,這樣可以大大節約索引空間,從而提高索引效率,但是這樣降低索引的選擇性,不重復的索引值(也稱為基數)和數據表的記錄總數(`#T`)的比值,范圍從 `1/#T`到1之間.索引的選擇性越高則查詢效率越高,因為選擇性高的索引可以讓mysql在查找時過濾到更多的行.唯一索引的選擇性是1,這是最好的索引選擇性,性能也是最好 一般情況下某個列前綴的選擇性也是足夠高的,足以滿足查詢性能.對于blog,text或者很長的varchar類型的列,必須使用前綴索引,因為mysql不允許索引這些列的完整長度 # 多列索引 很多人對多列索引的理解還不夠.一個常見的錯誤就是,為每個列創建獨立的索引,或者按照錯誤的順序創建多列索引 這種索引策略,一般是由于人們聽到一些專家建議:把where條件里面的列都加上索引,這樣模糊的建議導致的 實際上這個建議是非常錯誤的.這樣一來最好的情況也只能是一星索引,其性能比起真正最優的索引可能差幾個數量級.有時候如果無法設計一個三星索引,那么還不如忽略掉where子句,集中精力優化索引列的順序,或者創建一個全覆蓋索引 在多個列上建立單獨索引大部分情況下并不能提高mysql的查詢性能.mysql5.0和更新版本引入一種叫"索引合并"的策略,一定程度上可以使用表上的多個單列索引來定位指定的行.更早版本的mysql只能使用其中某一個單列索引,然而這種情況下沒有哪一個獨立的單列索引是非常有效 例如,表film_actor在字段film_id和actor_id上各有一個單列索引.但對于下面這個查詢where條件,這兩個單列索引都不是好的選擇 ~~~ select film_id,actor_id from sakila.film_actor where actor_id =1 or film_id =1; ~~~ 在老的mysql版本中,mysql對這個表的查詢會使用全表掃描.除非改寫成兩個表的查詢union的方式 ~~~ select film_id,actor_id from sakila.film_actor where actor_id =1 union all select film_id,actor_id from sakila.film_actor where film_id =1 and actor_id <> 1; ~~~ 但是在mysql5.0和更新版本中,查詢能夠同時使用這兩個單列索引進行掃描,并將結果進行合并.這種算法有3個變種:OR條件的聯合,add條件的相交組合前兩種情況聯合及提交 下面的查詢就是使用2個索引掃描的聯合,通過explain中的extra列就可以看到這一點  mysql會使用這類技術優化復雜查詢,所以在某些語句的extra列還可以看到嵌套操作 索引合并策略有時候是一種優化的結果,但實際上更多說明了表上的索引建的很糟糕 * 當初學服務器對多個索引做相交操作時(通常有多個AND條件),通常意味著需要一個包含所有相關列的多列索引,而不是多個獨立的單列索引 * 當服務器需要對多個索引做聯合操作時(通常有多個OR條件),通常需要耗費大量CPU和內存資源在算法的緩存,排序和合并操作上.特別是當其中有些索引的選擇性不高,需要合并掃描返回大量數據的時候 * 更重要的是,優化器不會把這些計算到"查詢成本"(cost)中,優化器只關心隨機頁面讀取.這會使得查詢的成本被"低估",導致該執行計劃還不如走全表掃描.這樣做不但會消耗更多的CPU和內存資源,還可能會影響查詢的并發性,但如果是單獨運行這樣的查詢往往會忽略并發性的影響,通常來說,還不如像在mysql4.1或更早的時代一樣,將查詢改寫成UNION方式更好 如果在EXPLAIN中看到有索引合并,應該好好檢查一下查詢和表的結構,看是不是最優的,也可以通過參數optimizer_switch來關閉索引合并功能.也可以使用IGNORE INDEX提示讓優化器忽略掉某些索引