程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL中的聯合索引學習教程,mysql聯合索引教程

MySQL中的聯合索引學習教程,mysql聯合索引教程

編輯:MySQL綜合教程

MySQL中的聯合索引學習教程,mysql聯合索引教程


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


兩個或更多個列上的索引被稱作復合索引。
利用索引中的附加列,您可以縮小搜索的范圍,但使用一個具有兩列的索引 不同於使用兩個單獨的索引。復合索引的結構與電話簿類似,人名由姓和名構成,電話簿首先按姓氏對進行排序,然後按名字對有相同姓氏的人進行排序。如果您知 道姓,電話簿將非常有用;如果您知道姓和名,電話簿則更為有用,但如果您只知道名不姓,電話簿將沒有用處。
所以說創建復合索引時,應該仔細考慮列的順序。對索引中的所有列執行搜索或僅對前幾列執行搜索時,復合索引非常有用;僅對後面的任意列執行搜索時,復合索引則沒有用處。
如:建立 姓名、年齡、性別的復合索引。

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

復合索引的建立原則:

 如果您很可能僅對一個列多次執行搜索,則該列應該是復合索引中的第一列。如果您很可能對一個兩列索引中的兩個列執行單獨的搜索,則應該創建另一個僅包含第二列的索引。
如上圖所示,如果查詢中需要對年齡和性別做查詢,則應當再新建一個包含年齡和性別的復合索引。
包含多個列的主鍵始終會自動以復合索引的形式創建索引,其列的順序是它們在表定義中出現的順序,而不是在主鍵定義中指定的順序。在考慮將來通過主鍵執行的搜索,確定哪一列應該排在最前面。
請注意,創建復合索引應當包含少數幾個列,並且這些列經常在select查詢裡使用。在復合索引裡包含太多的列不僅不會給帶來太多好處。而且由於使用相當多的內存來存儲復合索引的列的值,其後果是內存溢出和性能降低。

         
 復合索引對排序的優化:

 復合索引只對和索引中排序相同或相反的order by 語句優化。
 在創建復合索引時,每一列都定義了升序或者是降序。如定義一個復合索引:


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

 
 其中 有三列分別是:col1 升序,col2 降序, col3 升序。現在如果我們執行兩個查詢
 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 都可以別復合索引優化。
 如果查詢為:
 

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) 的搜索會有作用。

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. 如果列不構成索引的最左面前綴,則建立的索引將不起作用。
如:

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

 
 3. 如果一個 Like 語句的查詢條件不以通配符起始則使用索引。
如:%車 或 %車%   不使用索引。
    車%              使用索引。
索引的缺點:
1.       占用磁盤空間。
2.       增加了插入和刪除的操作時間。一個表擁有的索引越多,插入和刪除的速度越慢。如 要求快速錄入的系統不宜建過多索引。

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

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

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子句中對存在索引的列使用函數時,會使優化器忽略掉這些索引。下面的查詢就不會使用索引:

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

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

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

 

4、比較不匹配的數據類型
比較不匹配的數據類型也是難於發現的性能問題之一。
下面的例子中,dept_id是一個varchar2型的字段,在這個字段上有索引,但是下面的語句會執行全表掃描。

select * from dept where dept_id = 900198; 

 
這是因為oracle會自動把where子句轉換成to_number(dept_id)=900198,就是3所說的情況,這樣就限制了索引的使用。
把SQL語句改為如下形式就可以使用索引

select * from dept where dept_id = '900198'; 

 

恩,這裡還有要注意的:


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

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

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

 

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這種簡單的字符集,索引文件會小很多,速度自然就會快很多。這些細節問題需要讀者自己多注意,我就不多說了。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved