聯(lián)合索引又叫復(fù)合索引。對于復(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)成，電話簿首先按姓氏對進(jìn)行排序，然后按名字對有相同姓氏的人進(jìn)行排序。如果您知 道姓，電話簿將非常有用；如果您知道姓和名，電話簿則更為有用，但如果您只知道名不姓，電話簿將沒有用處。
所以說創(chuàng)建復(fù)合索引時(shí)，應(yīng)該仔細(xì)考慮列的順序。對索引中的所有列執(zhí)行搜索或僅對前幾列執(zhí)行搜索時(shí)，復(fù)合索引非常有用；僅對后面的任意列執(zhí)行搜索時(shí)，復(fù)合索引則沒有用處。
如：建立 姓名、年齡、性別的復(fù)合索引。

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

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

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

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

 復(fù)合索引只對和索引中排序相同或相反的order by 語句優(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í)的 查詢不會被復(fù)合索引優(yōu)化。

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

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

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 語句的查詢條件不以通配符起始則使用索引。
如：%車 或 %車%   不使用索引。
    車%              使用索引。
索引的缺點(diǎn)：
1.       占用磁盤空間。
2.       增加了插入和刪除的操作時(shí)間。一個(gè)表擁有的索引越多，插入和刪除的速度越慢。如 要求快速錄入的系統(tǒng)不宜建過多索引。

下面是一些常見的索引限制問題

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

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

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

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

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

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

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

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

select * from dept where dept_id = 900198; 

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

select * from dept where dept_id = '900198'; 

恩，這里還有要注意的：

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

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

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

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

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

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

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

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

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

最新評論