這篇文章主要介紹了MySQL中的聯合索引學習教程,其中談到了聯合索引對排序的優化等知識點,需要的朋友可以參考下

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

兩個或更多個列上的索引被稱作復合索引。

利用索引中的附加列，您可以縮小搜索的范圍，但使用一個具有兩列的索引 不同于使用兩個單獨的索引。復合索引的結構與電話簿類似，人名由姓和名構成，電話簿首先按姓氏對進行排序，然后按名字對有相同姓氏的人進行排序。如果您知 道姓，電話簿將非常有用;如果您知道姓和名，電話簿則更為有用，但如果您只知道名不姓，電話簿將沒有用處。

所以說創建復合索引時，應該仔細考慮列的順序。對索引中的所有列執行搜索或僅對前幾列執行搜索時，復合索引非常有用;僅對后面的任意列執行搜索時，復合索引則沒有用處。

如：建立 姓名、年齡、性別的復合索引。

1. create table test( 
2. a int, 
3. b int, 
4. c int, 
5. KEY a(a,b,c) 
6. ); 

復合索引的建立原則：

如果您很可能僅對一個列多次執行搜索，則該列應該是復合索引中的第一列。如果您很可能對一個兩列索引中的兩個列執行單獨的搜索，則應該創建另一個僅包含第二列的索引。

如上圖所示，如果查詢中需要對年齡和性別做查詢，則應當再新建一個包含年齡和性別的復合索引。

包含多個列的主鍵始終會自動以復合索引的形式創建索引，其列的順序是它們在表定義中出現的順序，而不是在主鍵定義中指定的順序。在考慮將來通過主鍵執行的搜索，確定哪一列應該排在最前面。

請注意，創建復合索引應當包含少數幾個列，并且這些列經常在select查詢里使用。在復合索引里包含太多的列不僅不會給帶來太多好處。而且由于使用相當多的內存來存儲復合索引的列的值，其后果是內存溢出和性能降低。

復合索引對排序的優化：

復合索引只對和索引中排序相同或相反的order by 語句優化。

在創建復合索引時，每一列都定義了升序或者是降序。如定義一個復合索引：

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

其中 有三列分別是：col1 升序，col2 降序， col3 升序。現在如果我們執行兩個查詢

1：

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

和索引順序相同

2：

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

和索引順序相反

查詢1，2 都可以別復合索引優化。

如果查詢為：

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

排序結果和索引完全不同時，此時的查詢不會被復合索引優化。

查詢優化器在在where查詢中的作用：

如果一個多列索引存在于 列 Col1 和 Col2 上，則以下語句：Select * from table where col1=val1 AND col2=val2 查詢優化器會試圖通過決定哪個索引將找到更少的行。之后用得到的索引去取值。

1. 如果存在一個多列索引，任何最左面的索引前綴能被優化器使用。所以聯合索引的順序不同，影響索引的選擇，盡量將值少的放在前面。

如：一個多列索引為 (col1 ，col2， col3)

那么在索引在列 (col1) 、(col1 col2) 、(col1 col2 col3) 的搜索會有作用。

1. SELECT * FROM tb WHERE col1 = val1  
2. SELECT * FROM tb WHERE col1 = val1 and col2 = val2  
3. SELECT * FROM tb WHERE col1 = val1 and col2 = val2 AND col3 = val3  

2. 如果列不構成索引的最左面前綴，則建立的索引將不起作用。

如：

1. SELECT * FROM tb WHERE col3 = val3  
2. SELECT * FROM tb WHERE col2 = val2  
3. SELECT * FROM tb WHERE col2 = val2 and col3=val3  

3. 如果一個 Like 語句的查詢條件不以通配符起始則使用索引。

如：%車 或 %車% 不使用索引。

車% 使用索引。

索引的缺點：

1. 占用磁盤空間。

2. 增加了插入和刪除的操作時間。一個表擁有的索引越多，插入和刪除的速度越慢。如 要求快速錄入的系統不宜建過多索引。

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

1、使用不等于操作符(<>, !=)

下面這種情況，即使在列dept_id有一個索引，查詢語句仍然執行一次全表掃描

select * from dept where staff_num <> 1000;

但是開發中的確需要這樣的查詢，難道沒有解決問題的辦法了嗎?

有!

通過把用 or 語法替代不等號進行查詢，就可以使用索引，以避免全表掃描：上面的語句改成下面這樣的，就可以使用索引了。

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

2、使用 is null 或 is not null

使用 is null 或is nuo null也會限制索引的使用，因為數據庫并沒有定義null值。如果被索引的列中有很多null，就不會使用這個索引(除非索引是一個位圖索引，關于位圖索引，會在以后的blog文章里做詳細解釋)。在sql語句中使用null會造成很多麻煩。

解決這個問題的辦法就是：建表時把需要索引的列定義為非空(not null)

3、使用函數

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

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

但是把函數應用在條件上，索引是可以生效的，把上面的語句改成下面的語句，就可以通過索引進行查找。

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

4、比較不匹配的數據類型

比較不匹配的數據類型也是難于發現的性能問題之一。

下面的例子中，dept_id是一個varchar2型的字段，在這個字段上有索引，但是下面的語句會執行全表掃描。

1. select * from dept where dept_id = 900198;  

這是因為oracle會自動把where子句轉換成to_number(dept_id)=900198，就是3所說的情況，這樣就限制了索引的使用。

把SQL語句改為如下形式就可以使用索引

1. select * from dept where dept_id = '900198';  

恩，這里還有要注意的：

比方說有一個文章表，我們要實現某個類別下按時間倒序列表顯示功能：

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

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

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

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

不考慮數據分布之類的特殊情況，任何一個合格的WEB開發人員都知道類似這樣的SQL，應該建立一個”category_id, created“復合索引，但這是最佳答案不?不見得，現在是回頭看看標題的時候了：MySQL里建立索引應該考慮數據庫引擎的類型!

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

如果我們的數據庫引擎是MyISAM，那么建立"category_id, created"復合索引就不是最佳答案。因為MyISAM的索引結構里，非主鍵索引并沒有額外保存對應主鍵的值，此時如果想利用上Covering Index，應該建立"category_id, created, id"復合索引。

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