通常會對多列索引缺乏理解，常見的錯誤是將很多列設置獨立索引，或者是索引列使用錯誤的次序。我們在下一篇討論索引列次序的問題，首先看一下多列獨立索引的情況，以下面的表結構為例：

CREATE TABLE test ( c1 INT, c2 INT, c3 INT, KEY(c1), KEY(c2), KEY(c3),);

使用這種索引策略通常是一些權威的建議（例如在WHERE條件中用到的條件列增加索引）的結果。事實上，這是大錯特錯的，要評分的話頂多給1顆星。這種方式的索引與真正優(yōu)化的索引相比，要慢上幾個數(shù)量級。有時候當你不能設計三星以上的索引時，去關注優(yōu)化行次序或者創(chuàng)建覆蓋索引都比忽略WHERE條件強。

覆蓋索引（covering index）指一個查詢語句的執(zhí)行只用從索引中就能夠取得，不必從數(shù)據(jù)表中讀取。也可以稱之為實現(xiàn)了索引覆蓋。 當一條查詢語句符合覆蓋索引條件時，MySQL只需要通過索引就可以返回查詢所需要的數(shù)據(jù)，這樣避免了查到索引后再返回表操作，減少I/O提高效率。 如，表covering_index_sample中有一個普通索引 idx_key1_key2(key1,key2)。當我們通過SQL語句：select key2 from covering_index_sample where key1 = ‘keytest’;的時候，就可以通過覆蓋索引查詢，無需再從數(shù)據(jù)表找數(shù)據(jù)行。

對很多列創(chuàng)建獨立的索引在很多情況下，并不能幫助MySQL改善性能。MySQL 5.0及更新的版本可以使用索引合并策略對這類設計進行些許的優(yōu)化 —— 這種方式允許在有多列索引的數(shù)據(jù)表中的查詢中限制在索引的使用去定位所需的數(shù)據(jù)行。

index merge 是對多個索引分別進行條件掃描，然后將它們各自的結果進行合并(intersect/union)

早期的MySQL版本只能使用一個索引，因此當沒有索引輔助時，MySQL通常進行全表掃描。例如在film_actor表有一個film_id和actor_id索引，但是在WHERE條件中同時使用這兩個索引并不是一個好的選擇：

SELECT film_id, actor_id FROM film_actor WHERE actor_id = 1 OR film_id = 1;

在早期的MySQL版本中，除非你像下面的語句一樣將兩個查詢聯(lián)合起來，否則這個查詢會導致全表掃描。

SELECT film_id, actor_id FROM film_actor WHERE actor_id = 1 UNION ALL SELECT film_id, actor_id FROM film_actor WHERE film_id = 1 AND actor_id <> 1;

在MySQL 5.0之后的版本中，查詢會同時使用兩個索引并且合并最終的結果。需要三個變體的算法實現(xiàn)這個過程：

使用OR條件獲取并集（union）數(shù)據(jù) 使用AND條件獲取交集數(shù)據(jù) 將上面兩個步驟的數(shù)據(jù)的交集再取并集。

上面有點費解，其實應該是分布使用單個條件（以便使用索引）查出全部數(shù)據(jù)，然后再組合數(shù)據(jù)。下面使用EXPLAIN查看一下。

EXPLAIN SELECT `film_id`,`actor_id` FROM `film_actor` WHERE `actor_id`=1 OR `film_id`=1

可以看到查詢方式是全表掃描，但是使用了Extra做優(yōu)化。MySQL在處理負責查詢時會使用這種技巧，因此你可能會在Extra中看到嵌套操作。這種索引合并的策略有些時候會發(fā)揮很好的作用，但更多的時候應該當作是對差勁索引使用的一個指示：

當服務器使用交集索引（通常是使用AND條件），通常意味著你需要一個索引包含所有相關的列，而不是獨立的索引列再組合。 當服務器使用并集索引（通常是使用OR條件），有時候緩存、排序和合并操作會占用很多的CPU和內(nèi)存資源，尤其是索引并不都是具備篩選的時候，這會導致掃描返回大量的數(shù)據(jù)行供合并操作。 記住優(yōu)化器并不承擔這些成本——它僅僅是優(yōu)化隨機頁讀取的數(shù)量。這會使得查詢“掉價”，導致全表掃描造成事實上更慢。CPU和內(nèi)存的高占用會影響并發(fā)查詢，但這些影響在你單獨運行查詢語句時并不會發(fā)生。因此，有時候像在MySQL 4.1版本那樣重寫那些使用UNION的查詢會得到更優(yōu)的效果。

當你使用EXPLAIN分析的時候看到了索引合并，你應該檢查查詢語句和表結構，看看是不是最優(yōu)的方式。你可以使用optimizer_switch（優(yōu)化開關）禁用索引合并來檢查。

再將film_actor的索引改為聯(lián)合索引(刪除原先的兩列獨立索引film_id和actor_id)看一下效果，可以看到此時避免了全表查詢。

ALTER TABLE film_actor ADD INDEX `sindex` (`film_id`,`actor_id`);

以上就是MySQL 獨立索引和聯(lián)合索引的選擇的詳細內(nèi)容，更多關于MySQL 獨立索引和聯(lián)合索引的資料請關注好吧啦網(wǎng)其它相關文章！