聯(lián)合索引又叫復(fù)合索引。對(duì)于復(fù)合索引:Mysql從左到右的使用索引中的字段，一個(gè)查詢可以只使用索引中的一部份，但只能是最左側(cè)部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3種組合進(jìn)行查找，但不支持 b,c進(jìn)行查找 .當(dāng)最左側(cè)字段是常量引用時(shí)，索引就十分有效。

兩個(gè)或更多個(gè)列上的索引被稱作復(fù)合索引。
利用索引中的附加列，您可以縮小搜索的范圍，但使用一個(gè)具有兩列的索引 不同于使用兩個(gè)單獨(dú)的索引。復(fù)合索引的結(jié)構(gòu)與電話簿類似，人名由姓和名構(gòu)成，電話簿首先按姓氏對(duì)進(jìn)行排序，然后按名字對(duì)有相同姓氏的人進(jìn)行排序。如果您知 道姓，電話簿將非常有用；如果您知道姓和名，電話簿則更為有用，但如果您只知道名不姓，電話簿將沒(méi)有用處。
所以說(shuō)創(chuàng)建復(fù)合索引時(shí)，應(yīng)該仔細(xì)考慮列的順序。對(duì)索引中的所有列執(zhí)行搜索或僅對(duì)前幾列執(zhí)行搜索時(shí)，復(fù)合索引非常有用；僅對(duì)后面的任意列執(zhí)行搜索時(shí)，復(fù)合索引則沒(méi)有用處。
如：建立 姓名、年齡、性別的復(fù)合索引。

create table test(
a int,
b int,
c int,
KEY a(a,b,c)
);

復(fù)合索引的建立原則：

 如果您很可能僅對(duì)一個(gè)列多次執(zhí)行搜索，則該列應(yīng)該是復(fù)合索引中的第一列。如果您很可能對(duì)一個(gè)兩列索引中的兩個(gè)列執(zhí)行單獨(dú)的搜索，則應(yīng)該創(chuàng)建另一個(gè)僅包含第二列的索引。
如上圖所示，如果查詢中需要對(duì)年齡和性別做查詢，則應(yīng)當(dāng)再新建一個(gè)包含年齡和性別的復(fù)合索引。
包含多個(gè)列的主鍵始終會(huì)自動(dòng)以復(fù)合索引的形式創(chuàng)建索引，其列的順序是它們?cè)诒矶x中出現(xiàn)的順序，而不是在主鍵定義中指定的順序。在考慮將來(lái)通過(guò)主鍵執(zhí)行的搜索，確定哪一列應(yīng)該排在最前面。
請(qǐng)注意，創(chuàng)建復(fù)合索引應(yīng)當(dāng)包含少數(shù)幾個(gè)列，并且這些列經(jīng)常在select查詢里使用。在復(fù)合索引里包含太多的列不僅不會(huì)給帶來(lái)太多好處。而且由于使用相當(dāng)多的內(nèi)存來(lái)存儲(chǔ)復(fù)合索引的列的值，其后果是內(nèi)存溢出和性能降低。

         
 復(fù)合索引對(duì)排序的優(yōu)化：

 復(fù)合索引只對(duì)和索引中排序相同或相反的order by 語(yǔ)句優(yōu)化。
 在創(chuàng)建復(fù)合索引時(shí)，每一列都定義了升序或者是降序。如定義一個(gè)復(fù)合索引：

CREATE INDEX idx_example  
ON table1 (col1 ASC, col2 DESC, col3 ASC) 

 
 其中 有三列分別是：col1 升序，col2 降序， col3 升序?，F(xiàn)在如果我們執(zhí)行兩個(gè)查詢
 1：

Select col1, col2, col3 from table1 order by col1 ASC, col2 DESC, col3 ASC

  和索引順序相同
 2：

Select col1, col2, col3 from table1 order by col1 DESC, col2 ASC, col3 DESC 

 和索引順序相反
 查詢1，2 都可以別復(fù)合索引優(yōu)化。
 如果查詢?yōu)椋?br />  

Select col1, col2, col3 from table1 order by col1 ASC, col2 ASC, col3 ASC

  排序結(jié)果和索引完全不同時(shí)，此時(shí)的 查詢不會(huì)被復(fù)合索引優(yōu)化。

查詢優(yōu)化器在在where查詢中的作用：

 如果一個(gè)多列索引存在于 列 Col1 和 Col2 上，則以下語(yǔ)句：Select   * from table where   col1=val1 AND col2=val2 查詢優(yōu)化器會(huì)試圖通過(guò)決定哪個(gè)索引將找到更少的行。之后用得到的索引去取值。
 1． 如果存在一個(gè)多列索引，任何最左面的索引前綴能被優(yōu)化器使用。所以聯(lián)合索引的順序不同，影響索引的選擇，盡量將值少的放在前面。
如：一個(gè)多列索引為 (col1 ，col2， col3)
    那么在索引在列 (col1) 、(col1 col2) 、(col1 col2 col3) 的搜索會(huì)有作用。

SELECT * FROM tb WHERE col1 = val1 
SELECT * FROM tb WHERE col1 = val1 and col2 = val2 
SELECT * FROM tb WHERE col1 = val1 and col2 = val2 AND col3 = val3 

 2． 如果列不構(gòu)成索引的最左面前綴，則建立的索引將不起作用。
如：

SELECT * FROM tb WHERE col3 = val3 
SELECT * FROM tb WHERE col2 = val2 
SELECT * FROM tb WHERE col2 = val2 and col3=val3 

 
 3． 如果一個(gè) Like 語(yǔ)句的查詢條件不以通配符起始則使用索引。
如：%車 或 %車%   不使用索引。
    車%              使用索引。
索引的缺點(diǎn)：
1.       占用磁盤(pán)空間。
2.       增加了插入和刪除的操作時(shí)間。一個(gè)表?yè)碛械乃饕蕉啵迦牒蛣h除的速度越慢。如 要求快速錄入的系統(tǒng)不宜建過(guò)多索引。

下面是一些常見(jiàn)的索引限制問(wèn)題

1、使用不等于操作符(<>, !=)
下面這種情況，即使在列dept_id有一個(gè)索引，查詢語(yǔ)句仍然執(zhí)行一次全表掃描
select * from dept where staff_num <> 1000;
但是開(kāi)發(fā)中的確需要這樣的查詢，難道沒(méi)有解決問(wèn)題的辦法了嗎？
有！
通過(guò)把用 or 語(yǔ)法替代不等號(hào)進(jìn)行查詢，就可以使用索引，以避免全表掃描：上面的語(yǔ)句改成下面這樣的，就可以使用索引了。

select * from dept shere staff_num < 1000 or dept_id > 1000; 

2、使用 is null 或 is not null
使用 is null 或is nuo null也會(huì)限制索引的使用，因?yàn)閿?shù)據(jù)庫(kù)并沒(méi)有定義null值。如果被索引的列中有很多null，就不會(huì)使用這個(gè)索引（除非索引是一個(gè)位圖索引，關(guān)于位圖索引，會(huì)在以后的blog文章里做詳細(xì)解釋）。在sql語(yǔ)句中使用null會(huì)造成很多麻煩。
解決這個(gè)問(wèn)題的辦法就是：建表時(shí)把需要索引的列定義為非空(not null)

3、使用函數(shù)
如果沒(méi)有使用基于函數(shù)的索引，那么where子句中對(duì)存在索引的列使用函數(shù)時(shí)，會(huì)使優(yōu)化器忽略掉這些索引。下面的查詢就不會(huì)使用索引：

select * from staff where trunc(birthdate) = '01-MAY-82'; 

 
但是把函數(shù)應(yīng)用在條件上，索引是可以生效的，把上面的語(yǔ)句改成下面的語(yǔ)句，就可以通過(guò)索引進(jìn)行查找。

select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999); 

4、比較不匹配的數(shù)據(jù)類型
比較不匹配的數(shù)據(jù)類型也是難于發(fā)現(xiàn)的性能問(wèn)題之一。
下面的例子中，dept_id是一個(gè)varchar2型的字段，在這個(gè)字段上有索引，但是下面的語(yǔ)句會(huì)執(zhí)行全表掃描。

select * from dept where dept_id = 900198; 

 
這是因?yàn)閛racle會(huì)自動(dòng)把where子句轉(zhuǎn)換成to_number(dept_id)=900198，就是3所說(shuō)的情況，這樣就限制了索引的使用。
把SQL語(yǔ)句改為如下形式就可以使用索引

select * from dept where dept_id = '900198'; 

恩，這里還有要注意的：

 比方說(shuō)有一個(gè)文章表，我們要實(shí)現(xiàn)某個(gè)類別下按時(shí)間倒序列表顯示功能：

 SELECT * FROM articles WHERE category_id = ... ORDER BY created DESC LIMIT ...

 這樣的查詢很常見(jiàn)，基本上不管什么應(yīng)用里都能找出一大把類似的SQL來(lái)，學(xué)院派的讀者看到上面的SQL，可能會(huì)說(shuō)SELECT *不好，應(yīng)該僅僅查詢需要的字段，那我們就索性徹底點(diǎn)，把SQL改成如下的形式：

SELECT id FROM articles WHERE category_id = ... ORDER BY created DESC LIMIT ...

 我們假設(shè)這里的id是主鍵，至于文章的具體內(nèi)容，可以都保存到memcached之類的鍵值類型的緩存里，如此一來(lái)，學(xué)院派的讀者們應(yīng)該挑不出什么毛病來(lái)了，下面我們就按這條SQL來(lái)考慮如何建立索引：

 不考慮數(shù)據(jù)分布之類的特殊情況，任何一個(gè)合格的WEB開(kāi)發(fā)人員都知道類似這樣的SQL，應(yīng)該建立一個(gè)”category_id, created“復(fù)合索引，但這是最佳答案不？不見(jiàn)得，現(xiàn)在是回頭看看標(biāo)題的時(shí)候了：MySQL里建立索引應(yīng)該考慮數(shù)據(jù)庫(kù)引擎的類型！

 如果我們的數(shù)據(jù)庫(kù)引擎是InnoDB，那么建立”category_id, created“復(fù)合索引是最佳答案。讓我們看看InnoDB的索引結(jié)構(gòu)，在InnoDB里，索引結(jié)構(gòu)有一個(gè)特殊的地方：非主鍵索引在其BTree的葉節(jié)點(diǎn)上會(huì)額外保存對(duì)應(yīng)主鍵的值，這樣做一個(gè)最直接的好處就是Covering Index，不用再到數(shù)據(jù)文件里去取id的值，可以直接在索引里得到它。

 如果我們的數(shù)據(jù)庫(kù)引擎是MyISAM，那么建立"category_id, created"復(fù)合索引就不是最佳答案。因?yàn)镸yISAM的索引結(jié)構(gòu)里，非主鍵索引并沒(méi)有額外保存對(duì)應(yīng)主鍵的值，此時(shí)如果想利用上Covering Index，應(yīng)該建立"category_id, created, id"復(fù)合索引。

 嘮完了，應(yīng)該明白我的意思了吧。希望以后大家在考慮索引的時(shí)候能思考的更全面一點(diǎn)，實(shí)際應(yīng)用中還有很多類似的問(wèn)題，比如說(shuō)多數(shù)人在建立索引的時(shí)候不從Cardinality（SHOW INDEX FROM ...能看到此參數(shù)）的角度看是否合適的問(wèn)題，Cardinality表示唯一值的個(gè)數(shù)，一般來(lái)說(shuō)，如果唯一值個(gè)數(shù)在總行數(shù)中所占比例小于20%的話，則可以認(rèn)為Cardinality太小，此時(shí)索引除了拖慢insert/update/delete的速度之外，不會(huì)對(duì)select產(chǎn)生太大作用；還有一個(gè)細(xì)節(jié)是建立索引的時(shí)候未考慮字符集的影響，比如說(shuō)username字段，如果僅僅允許英文，下劃線之類的符號(hào)，那么就不要用gbk，utf-8之類的字符集，而應(yīng)該使用latin1或者ascii這種簡(jiǎn)單的字符集，索引文件會(huì)小很多，速度自然就會(huì)快很多。這些細(xì)節(jié)問(wèn)題需要讀者自己多注意，我就不多說(shuō)了。

最新評(píng)論