MySQL聚簇索引和非聚簇索引怎么用

小編給大家分享一下MySQL聚簇索引和非聚簇索引怎么用,相信大部分人都還不怎么了解,因此分享這篇文章給大家參考一下,希望大家閱讀完這篇文章后大有收獲,下面讓我們一起去了解一下吧!

公司2013年成立成都創(chuàng)新互聯(lián)公司專注于”幫助中小企業(yè)+互聯(lián)網(wǎng)”, 也是目前成都地區(qū)具有實力的互聯(lián)網(wǎng)服務商。團隊致力于為企業(yè)提供--站式網(wǎng)站建設、移動端應用( H5手機營銷、app軟件開發(fā)公司、微信開發(fā))、軟件開發(fā)、信息化解決方案等服務。

索引分為聚簇索引和非聚簇索引。

以一本英文課本為例,要找第8課,直接翻書,若先翻到第5課,則往后翻,再翻到第10課,則又往前翻。這本書本身就是一個索引,即“聚簇索引”。
如果要找"fire”這個單詞,會翻到書后面的附錄,這個附錄是按字母排序的,找到F字母那一塊,再找到"fire”,對應的會是它在第幾課。這個附錄,為“非聚簇索引”。
由此可見,聚簇索引,索引的順序就是數(shù)據(jù)存放的順序,所以,很容易理解,一張數(shù)據(jù)表只能有一個聚簇索引。
聚簇索引要比非聚簇索引查詢效率高很多,特別是范圍查詢的時候。所以,至于聚簇索引到底應該為主鍵,還是其他字段,這個可以再討論。
1、MYSQL的索引
mysql中,不同的存儲引擎對索引的實現(xiàn)方式不同,大致說下MyISAM和InnoDB兩種存儲引擎。
MyISAM的B+Tree的葉子節(jié)點上的data,并不是數(shù)據(jù)本身,而是數(shù)據(jù)存放的地址。主索引和輔助索引沒啥區(qū)別,只是主索引中的key一定得是唯一的。這里的索引都是非聚簇索引。
MyISAM還采用壓縮機制存儲索引,比如,第一個索引為“her”,第二個索引為“here”,那么第二個索引會被存儲為“3,e”,這樣的缺點是同一個節(jié)點中的索引只能采用順序查找。



InnoDB的數(shù)據(jù)文件本身就是索引文件,B+Tree的葉子節(jié)點上的data就是數(shù)據(jù)本身,key為主鍵,這是聚簇索引。非聚簇索引,葉子節(jié)點上的data是主鍵(所以聚簇索引的key,不能過長)。為什么存放的主鍵,而不是記錄所在地址呢,理由相當簡單,因為記錄所在地址并不能保證一定不會變,但主鍵可以保證。
至于為什么主鍵通常建議使用自增id呢?
2、聚簇索引
聚簇索引的數(shù)據(jù)的物理存放順序與索引順序是一致的,即:只要索引是相鄰的,那么對應的數(shù)據(jù)一定也是相鄰地存放在磁盤上的。如果主鍵不是自增id,那么可以想象,它會干些什么,不斷地調(diào)整數(shù)據(jù)的物理地址、分頁,當然也有其他一些措施來減少這些操作,但卻無法徹底避免。但,如果是自增的,那就簡單了,它只需要一頁一頁地寫,索引結(jié)構(gòu)相對緊湊,磁盤碎片少,效率也高。
聚簇索引不但在檢索上可以大大滴提高效率,在數(shù)據(jù)讀取上也一樣。比如:需要查詢f~t的所有單詞。
一個使用MyISAM的主索引,一個使用InnoDB的聚簇索引。兩種索引的B+Tree檢索時間一樣,但讀取時卻有了差異。
因為MyISAM的主索引并非聚簇索引,那么他的數(shù)據(jù)的物理地址必然是凌亂的,拿到這些物理地址,按照合適的算法進行I/O讀取,于是開始不停的尋道不停的旋轉(zhuǎn)。聚簇索引則只需一次I/O。
不過,如果涉及到大數(shù)據(jù)量的排序、全表掃描、count之類的操作的話,還是MyISAM占優(yōu)勢些,因為索引所占空間小,這些操作是需要在內(nèi)存中完成的。
鑒于聚簇索引的范圍查詢效率,很多人認為使用主鍵作為聚簇索引太多浪費,畢竟幾乎不會使用主鍵進行范圍查詢。但若再考慮到聚簇索引的存儲,就不好定論了。

下表給出了何時使用聚簇索引與非聚簇索引:

動作

使用聚簇索引

使用非聚簇索引

列經(jīng)常被分組排序

       應

      應

返回某范圍內(nèi)的數(shù)據(jù)

     應

     不應

一個或極少不同值

    不應

    不應

小數(shù)目的不同值

     應

    不應

大數(shù)目的不同值

   不應

    應

頻繁更新的列

  不應

    應

外鍵列

   應

    應

主鍵列

   應

    應

頻繁修改索引列

  不應

     應



根據(jù)調(diào)優(yōu)實踐,要注意聚簇索引的選擇。首先我們要找到我們最多用到的SQL查詢,像本例就是那句類似的組合條件查詢的情況,這種情況最好使用組合聚簇索引,而且最多用到的字段要放在組合聚簇索引的前面,否則的話就索引就不會有好的效果。


Index seek 為什么比 Index scan好?
索引掃描也就是遍歷B樹,而seek是B樹查找直接定位。
Index scan多半是出現(xiàn)在索引列在表達式中。數(shù)據(jù)庫引擎無法直接確定你要的列的值,所以只能掃描整個整個索引進行計算。index seek就要好很多.數(shù)據(jù)庫引擎只需要掃描幾個分支節(jié)點就可以定位到你要的記錄。回過來,如果聚集索引的葉子節(jié)點就是記錄,那么Clustered Index Scan就基本等同于full table scan。


一些優(yōu)化原則
1、缺省情況下建立的索引是非聚簇索引,但有時它并不是最佳的。在非群集索引下,數(shù)據(jù)在物理上隨機存放在數(shù)據(jù)頁上。合理的索引設計要建立在對各種查詢的分析和預測上。一般來說: 
a.有大量重復值、且經(jīng)常有范圍查詢( > ,< ,> =,< =)和order by、group by發(fā)生的列,可考 
慮建立群集索引; 
b.經(jīng)常同時存取多列,且每列都含有重復值可考慮建立組合索引; 
c.組合索引要盡量使關(guān)鍵查詢形成索引覆蓋,其前導列一定是使用最頻繁的列。索引雖有助于提高性能但不是索引越多越好,恰好相反過多的索引會導致系統(tǒng)低效。用戶在表中每加進一個索引,維護索引集合就要做相應的更新工作。 


2、ORDER BY和GROPU BY使用ORDER BY和GROUP BY短語,任何一種索引都有助于SELECT的性能提高。 


3、多表操作在被實際執(zhí)行前,查詢優(yōu)化器會根據(jù)連接條件,列出幾組可能的連接方案并從中找出系統(tǒng)開銷最小的最佳方案。連接條件要充份考慮帶有索引的表、行數(shù)多的表;內(nèi)外表的選擇可由公式:外層表中的匹配行數(shù)*內(nèi)層表中每一次查找的次數(shù)確定,乘積最小為最佳方案。


4、任何對列的操作都將導致表掃描,它包括數(shù)據(jù)庫函數(shù)、計算表達式等等,查詢時要盡可能將操作移至等號右邊。 


5、IN、OR子句常會使用工作表,使索引失效。如果不產(chǎn)生大量重復值,可以考慮把子句拆開。拆開的子句中應該包含索引。


                    
建立聚簇索引的思想


1、大多數(shù)表都應該有聚簇索引或使用分區(qū)來降低對表尾頁的競爭,在一個高事務的環(huán)境中,對最后一頁的封鎖嚴重影響系統(tǒng)的吞吐量。
2、在聚簇索引下,數(shù)據(jù)在物理上按順序排在數(shù)據(jù)頁上,重復值也排在一起,因而在那些包含范圍檢查(between、<、<=、>、>=)或使用group by或orderby的查詢時,一旦找到具有范圍中第一個鍵值的行,具有后續(xù)索引值的行保證物理上毗連在一起而不必進一步搜索,避免了大范圍掃描,可以大大提高查詢速度。
3、在一個頻繁發(fā)生插入操作的表上建立聚簇索引時,不要建在具有單調(diào)上升值的列(如IDENTITY)上,否則會經(jīng)常引起封鎖沖突。
4、在聚簇索引中不要包含經(jīng)常修改的列,因為碼值修改后,數(shù)據(jù)行必須移動到新的位置。
5、選擇聚簇索引應基于where子句和連接操作的類型。
 
聚簇索引的侯選列


1、主鍵列,該列在where子句中使用并且插入是隨機的。
2、按范圍存取的列,如pri_order > 100 and pri_order < 200。
3、在group by或order by中使用的列。
4、不經(jīng)常修改的列。
5、在連接操作中使用的列。
 
 
 
非聚簇索引


非聚簇索引,葉級頁指向表中的記錄,記錄的物理順序與邏輯順序沒有必然的聯(lián)系。非聚簇索引則更像書的標準索引表,索引表中的順序通常與實際的頁碼順序是不一致的。
 
 
每個表只能有一個聚簇索引,因為一個表中的記錄只能以一種物理順序存放。但是,一個表可以有不止一個非聚簇索引。實際上,對每個表你最多可以建立249個非聚簇索引。非聚簇索引需要大量的硬盤空間和內(nèi)存。另外,雖然非聚簇索引可以提高從表中取數(shù)據(jù)的速度,它也會降低向表中插入和更新數(shù)據(jù)的速度。每當你改變了一個建立了非聚簇索引的表中的數(shù)據(jù)時,必須同時更新索引。因此你對一個表建立非聚簇索引時要慎重考慮。如果你預計一個表需要頻繁地更新數(shù)據(jù),那么不要對它建立太多非聚簇索引。另外,如果硬盤和內(nèi)存空間有限,也應該限制使用非聚簇索引的數(shù)量
 
 
非聚簇索引的使用


SQLServer缺省情況下建立的索引是非聚簇索引,由于非聚簇索引不重新組織表中的數(shù)據(jù),而是對每一行存儲索引列值并用一個指針指向數(shù)據(jù)所在的頁面。換句話說非聚簇索引具有在索引結(jié)構(gòu)和數(shù)據(jù)本身之間的一個額外級。一個表如果沒有聚簇索引時,可有250個非聚簇索引。每個非聚簇索引提供訪問數(shù)據(jù)的不同排序順序。在建立非聚簇索引時,要權(quán)衡索引對查詢速度的加快與降低修改速度之間的利弊。另外,還要考慮這些問題:
1、索引需要使用多少空間。
2、合適的列是否穩(wěn)定。
3、索引鍵是如何選擇的,掃描效果是否更佳。
4、是否有許多重復值。
對更新頻繁的表來說,表上的非聚簇索引比聚簇索引和根本沒有索引需要更多的額外開銷。對移到新頁的每一行而言,指向該數(shù)據(jù)的每個非聚簇索引的頁級行也必須更新,有時可能還需要索引頁的分理。從一個頁面刪除數(shù)據(jù)的進程也會有類似的開銷,另外,刪除進程還必須把數(shù)據(jù)移到頁面上部,以保證數(shù)據(jù)的連續(xù)性。所以,建立非聚簇索引要非常慎重。非聚簇索引常被用在以下情況:
1、某列常用于集合函數(shù)(如Sum,....)。
2、某列常用于join,order by,group by。
3、查尋出的數(shù)據(jù)不超過表中數(shù)據(jù)量的20%。

以上是“MySQL聚簇索引和非聚簇索引怎么用”這篇文章的所有內(nèi)容,感謝各位的閱讀!相信大家都有了一定的了解,希望分享的內(nèi)容對大家有所幫助,如果還想學習更多知識,歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道!

分享標題:MySQL聚簇索引和非聚簇索引怎么用
轉(zhuǎn)載源于:http://muchs.cn/article8/gecgip.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián),為您提供網(wǎng)頁設計公司、定制開發(fā)、App開發(fā)、全網(wǎng)營銷推廣、Google、面包屑導航

廣告

聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時需注明來源: 創(chuàng)新互聯(lián)

網(wǎng)站建設網(wǎng)站維護公司