通常會(huì)對(duì)多列索引缺乏理解，常見(jiàn)的錯(cuò)誤是將很多列設(shè)置獨(dú)立索引，或者是索引列使用錯(cuò)誤的次序。我們?cè)谙乱黄懻撍饕写涡虻膯?wèn)題，首先看一下多列獨(dú)立索引的情況，以下面的表結(jié)構(gòu)為例：

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

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

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

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

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

早期的MySQL版本只能使用一個(gè)索引，因此當(dāng)沒(méi)有索引輔助時(shí)，MySQL通常進(jìn)行全表掃描。例如在film_actor表有一個(gè)film_id和actor_id索引，但是在WHERE條件中同時(shí)使用這兩個(gè)索引并不是一個(gè)好的選擇：

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

在早期的MySQL版本中，除非你像下面的語(yǔ)句一樣將兩個(gè)查詢聯(lián)合起來(lái)，否則這個(gè)查詢會(huì)導(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之后的版本中，查詢會(huì)同時(shí)使用兩個(gè)索引并且合并最終的結(jié)果。需要三個(gè)變體的算法實(shí)現(xiàn)這個(gè)過(guò)程：

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

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

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

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

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

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

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

最新評(píng)論