MySQL 獨(dú)立索引和聯(lián)合索引的選擇

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

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

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

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

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

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

早期的MySQL版本只能使用一個索引，因此當(dāng)沒有索引輔助時，MySQL通常進(jìn)行全表掃描。例如在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)合起來，否則這個查詢會導(dǎo)致全表掃描。

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之后的版本中，查詢會同時使用兩個索引并且合并最終的結(jié)果。需要三個變體的算法實(shí)現(xiàn)這個過程：

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

上面有點(diǎn)費(fèi)解，其實(shí)應(yīng)該是分布使用單個條件（以便使用索引）查出全部數(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在處理負(fù)責(zé)查詢時會使用這種技巧，因此你可能會在Extra中看到嵌套操作。這種索引合并的策略有些時候會發(fā)揮很好的作用，但更多的時候應(yīng)該當(dāng)作是對差勁索引使用的一個指示：

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

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

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

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

以上就是MySQL 獨(dú)立索引和聯(lián)合索引的選擇的詳細(xì)內(nèi)容，更多關(guān)于MySQL 獨(dú)立索引和聯(lián)合索引的資料請關(guān)注腳本之家其它相關(guān)文章！