小編給大家分享一下MySQL聚簇索引和非聚簇索引怎么用，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！
索引分為聚簇索引和非聚簇索引。
以一本英文課本為例，要找第8課，直接翻書(shū)，若先翻到第5課，則往后翻，再翻到第10課，則又往前翻。這本書(shū)本身就是一個(gè)索引，即“聚簇索引”。
如果要找"fire”這個(gè)單詞，會(huì)翻到書(shū)后面的附錄，這個(gè)附錄是按字母排序的，找到F字母那一塊，再找到"fire”，對(duì)應(yīng)的會(huì)是它在第幾課。這個(gè)附錄，為“非聚簇索引”。
由此可見(jiàn)，聚簇索引，索引的順序就是數(shù)據(jù)存放的順序，所以，很容易理解，一張數(shù)據(jù)表只能有一個(gè)聚簇索引。
聚簇索引要比非聚簇索引查詢(xún)效率高很多，特別是范圍查詢(xún)的時(shí)候。所以，至于聚簇索引到底應(yīng)該為主鍵，還是其他字段，這個(gè)可以再討論。
1、MYSQL的索引
mysql中，不同的存儲(chǔ)引擎對(duì)索引的實(shí)現(xiàn)方式不同，大致說(shuō)下MyISAM和InnoDB兩種存儲(chǔ)引擎。
MyISAM的B+Tree的葉子節(jié)點(diǎn)上的data，并不是數(shù)據(jù)本身，而是數(shù)據(jù)存放的地址。主索引和輔助索引沒(méi)啥區(qū)別，只是主索引中的key一定得是唯一的。這里的索引都是非聚簇索引。
MyISAM還采用壓縮機(jī)制存儲(chǔ)索引，比如，第一個(gè)索引為“her”，第二個(gè)索引為“here”，那么第二個(gè)索引會(huì)被存儲(chǔ)為“3,e”，這樣的缺點(diǎn)是同一個(gè)節(jié)點(diǎn)中的索引只能采用順序查找。
InnoDB的數(shù)據(jù)文件本身就是索引文件，B+Tree的葉子節(jié)點(diǎn)上的data就是數(shù)據(jù)本身，key為主鍵，這是聚簇索引。非聚簇索引，葉子節(jié)點(diǎn)上的data是主鍵(所以聚簇索引的key，不能過(guò)長(zhǎng))。為什么存放的主鍵，而不是記錄所在地址呢，理由相當(dāng)簡(jiǎn)單，因?yàn)橛涗浰诘刂凡⒉荒鼙ＷC一定不會(huì)變，但主鍵可以保證。
至于為什么主鍵通常建議使用自增id呢？
2、聚簇索引
聚簇索引的數(shù)據(jù)的物理存放順序與索引順序是一致的，即：只要索引是相鄰的，那么對(duì)應(yīng)的數(shù)據(jù)一定也是相鄰地存放在磁盤(pán)上的。如果主鍵不是自增id，那么可以想象，它會(huì)干些什么，不斷地調(diào)整數(shù)據(jù)的物理地址、分頁(yè)，當(dāng)然也有其他一些措施來(lái)減少這些操作，但卻無(wú)法徹底避免。但，如果是自增的，那就簡(jiǎn)單了，它只需要一頁(yè)一頁(yè)地寫(xiě)，索引結(jié)構(gòu)相對(duì)緊湊，磁盤(pán)碎片少，效率也高。
聚簇索引不但在檢索上可以大大滴提高效率，在數(shù)據(jù)讀取上也一樣。比如：需要查詢(xún)f~t的所有單詞。
一個(gè)使用MyISAM的主索引，一個(gè)使用InnoDB的聚簇索引。兩種索引的B+Tree檢索時(shí)間一樣，但讀取時(shí)卻有了差異。
因?yàn)镸yISAM的主索引并非聚簇索引，那么他的數(shù)據(jù)的物理地址必然是凌亂的，拿到這些物理地址，按照合適的算法進(jìn)行I/O讀取，于是開(kāi)始不停的尋道不停的旋轉(zhuǎn)。聚簇索引則只需一次I/O。
不過(guò)，如果涉及到大數(shù)據(jù)量的排序、全表掃描、count之類(lèi)的操作的話，還是MyISAM占優(yōu)勢(shì)些，因?yàn)樗饕伎臻g小，這些操作是需要在內(nèi)存中完成的。
鑒于聚簇索引的范圍查詢(xún)效率，很多人認(rèn)為使用主鍵作為聚簇索引太多浪費(fèi)，畢竟幾乎不會(huì)使用主鍵進(jìn)行范圍查詢(xún)。但若再考慮到聚簇索引的存儲(chǔ)，就不好定論了。
下表給出了何時(shí)使用聚簇索引與非聚簇索引:
動(dòng)作 | 使用聚簇索引 | 使用非聚簇索引 |
列經(jīng)常被分組排序 | 應(yīng) | 應(yīng) |
返回某范圍內(nèi)的數(shù)據(jù) | 應(yīng) | 不應(yīng) |
一個(gè)或極少不同值 | 不應(yīng) | 不應(yīng) |
小數(shù)目的不同值 | 應(yīng) | 不應(yīng) |
大數(shù)目的不同值 | 不應(yīng) | 應(yīng) |
頻繁更新的列 | 不應(yīng) | 應(yīng) |
外鍵列 | 應(yīng) | 應(yīng) |
主鍵列 | 應(yīng) | 應(yīng) |
頻繁修改索引列 | 不應(yīng) | 應(yīng) |
根據(jù)調(diào)優(yōu)實(shí)踐，要注意聚簇索引的選擇。首先我們要找到我們最多用到的SQL查詢(xún)，像本例就是那句類(lèi)似的組合條件查詢(xún)的情況，這種情況最好使用組合聚簇索引，而且最多用到的字段要放在組合聚簇索引的前面，否則的話就索引就不會(huì)有好的效果。
Index seek 為什么比 Index scan好？
索引掃描也就是遍歷B樹(shù)，而seek是B樹(shù)查找直接定位。
Index scan多半是出現(xiàn)在索引列在表達(dá)式中。數(shù)據(jù)庫(kù)引擎無(wú)法直接確定你要的列的值，所以只能掃描整個(gè)整個(gè)索引進(jìn)行計(jì)算。index seek就要好很多.數(shù)據(jù)庫(kù)引擎只需要掃描幾個(gè)分支節(jié)點(diǎn)就可以定位到你要的記錄?；剡^(guò)來(lái),如果聚集索引的葉子節(jié)點(diǎn)就是記錄，那么Clustered Index Scan就基本等同于full table scan。
一些優(yōu)化原則
1、缺省情況下建立的索引是非聚簇索引，但有時(shí)它并不是最佳的。在非群集索引下，數(shù)據(jù)在物理上隨機(jī)存放在數(shù)據(jù)頁(yè)上。合理的索引設(shè)計(jì)要建立在對(duì)各種查詢(xún)的分析和預(yù)測(cè)上。一般來(lái)說(shuō)：
a.有大量重復(fù)值、且經(jīng)常有范圍查詢(xún)（ > ,< ，> =,< =）和order by、group by發(fā)生的列，可考
慮建立群集索引；
b.經(jīng)常同時(shí)存取多列，且每列都含有重復(fù)值可考慮建立組合索引；
c.組合索引要盡量使關(guān)鍵查詢(xún)形成索引覆蓋，其前導(dǎo)列一定是使用最頻繁的列。索引雖有助于提高性能但不是索引越多越好，恰好相反過(guò)多的索引會(huì)導(dǎo)致系統(tǒng)低效。用戶(hù)在表中每加進(jìn)一個(gè)索引，維護(hù)索引集合就要做相應(yīng)的更新工作。
2、ORDER BY和GROPU BY使用ORDER BY和GROUP BY短語(yǔ)，任何一種索引都有助于SELECT的性能提高。
3、多表操作在被實(shí)際執(zhí)行前，查詢(xún)優(yōu)化器會(huì)根據(jù)連接條件，列出幾組可能的連接方案并從中找出系統(tǒng)開(kāi)銷(xiāo)最小的最佳方案。連接條件要充份考慮帶有索引的表、行數(shù)多的表；內(nèi)外表的選擇可由公式：外層表中的匹配行數(shù)*內(nèi)層表中每一次查找的次數(shù)確定，乘積最小為最佳方案。
4、任何對(duì)列的操作都將導(dǎo)致表掃描，它包括數(shù)據(jù)庫(kù)函數(shù)、計(jì)算表達(dá)式等等，查詢(xún)時(shí)要盡可能將操作移至等號(hào)右邊。
5、IN、OR子句常會(huì)使用工作表，使索引失效。如果不產(chǎn)生大量重復(fù)值，可以考慮把子句拆開(kāi)。拆開(kāi)的子句中應(yīng)該包含索引。
建立聚簇索引的思想
1、大多數(shù)表都應(yīng)該有聚簇索引或使用分區(qū)來(lái)降低對(duì)表尾頁(yè)的競(jìng)爭(zhēng)，在一個(gè)高事務(wù)的環(huán)境中，對(duì)最后一頁(yè)的封鎖嚴(yán)重影響系統(tǒng)的吞吐量。
2、在聚簇索引下，數(shù)據(jù)在物理上按順序排在數(shù)據(jù)頁(yè)上，重復(fù)值也排在一起，因而在那些包含范圍檢查(between、<、<=、>、>=)或使用group by或orderby的查詢(xún)時(shí)，一旦找到具有范圍中第一個(gè)鍵值的行，具有后續(xù)索引值的行保證物理上毗連在一起而不必進(jìn)一步搜索，避免了大范圍掃描，可以大大提高查詢(xún)速度。
3、在一個(gè)頻繁發(fā)生插入操作的表上建立聚簇索引時(shí)，不要建在具有單調(diào)上升值的列(如IDENTITY)上，否則會(huì)經(jīng)常引起封鎖沖突。
4、在聚簇索引中不要包含經(jīng)常修改的列，因?yàn)榇a值修改后，數(shù)據(jù)行必須移動(dòng)到新的位置。
5、選擇聚簇索引應(yīng)基于where子句和連接操作的類(lèi)型。
聚簇索引的侯選列
1、主鍵列,該列在where子句中使用并且插入是隨機(jī)的。
2、按范圍存取的列，如pri_order > 100 and pri_order < 200。
3、在group by或order by中使用的列。
4、不經(jīng)常修改的列。
5、在連接操作中使用的列。
非聚簇索引
非聚簇索引，葉級(jí)頁(yè)指向表中的記錄，記錄的物理順序與邏輯順序沒(méi)有必然的聯(lián)系。非聚簇索引則更像書(shū)的標(biāo)準(zhǔn)索引表，索引表中的順序通常與實(shí)際的頁(yè)碼順序是不一致的。
每個(gè)表只能有一個(gè)聚簇索引，因?yàn)橐粋€(gè)表中的記錄只能以一種物理順序存放。但是，一個(gè)表可以有不止一個(gè)非聚簇索引。實(shí)際上，對(duì)每個(gè)表你最多可以建立249個(gè)非聚簇索引。非聚簇索引需要大量的硬盤(pán)空間和內(nèi)存。另外，雖然非聚簇索引可以提高從表中取數(shù)據(jù)的速度，它也會(huì)降低向表中插入和更新數(shù)據(jù)的速度。每當(dāng)你改變了一個(gè)建立了非聚簇索引的表中的數(shù)據(jù)時(shí)，必須同時(shí)更新索引。因此你對(duì)一個(gè)表建立非聚簇索引時(shí)要慎重考慮。如果你預(yù)計(jì)一個(gè)表需要頻繁地更新數(shù)據(jù)，那么不要對(duì)它建立太多非聚簇索引。另外，如果硬盤(pán)和內(nèi)存空間有限，也應(yīng)該限制使用非聚簇索引的數(shù)量
非聚簇索引的使用
SQLServer缺省情況下建立的索引是非聚簇索引，由于非聚簇索引不重新組織表中的數(shù)據(jù)，而是對(duì)每一行存儲(chǔ)索引列值并用一個(gè)指針指向數(shù)據(jù)所在的頁(yè)面。換句話說(shuō)非聚簇索引具有在索引結(jié)構(gòu)和數(shù)據(jù)本身之間的一個(gè)額外級(jí)。一個(gè)表如果沒(méi)有聚簇索引時(shí),可有250個(gè)非聚簇索引。每個(gè)非聚簇索引提供訪問(wèn)數(shù)據(jù)的不同排序順序。在建立非聚簇索引時(shí)，要權(quán)衡索引對(duì)查詢(xún)速度的加快與降低修改速度之間的利弊。另外，還要考慮這些問(wèn)題：
1、索引需要使用多少空間。
2、合適的列是否穩(wěn)定。
3、索引鍵是如何選擇的，掃描效果是否更佳。
4、是否有許多重復(fù)值。
對(duì)更新頻繁的表來(lái)說(shuō)，表上的非聚簇索引比聚簇索引和根本沒(méi)有索引需要更多的額外開(kāi)銷(xiāo)。對(duì)移到新頁(yè)的每一行而言，指向該數(shù)據(jù)的每個(gè)非聚簇索引的頁(yè)級(jí)行也必須更新，有時(shí)可能還需要索引頁(yè)的分理。從一個(gè)頁(yè)面刪除數(shù)據(jù)的進(jìn)程也會(huì)有類(lèi)似的開(kāi)銷(xiāo)，另外，刪除進(jìn)程還必須把數(shù)據(jù)移到頁(yè)面上部，以保證數(shù)據(jù)的連續(xù)性。所以，建立非聚簇索引要非常慎重。非聚簇索引常被用在以下情況:
1、某列常用于集合函數(shù)(如Sum,....)。
2、某列常用于join,order by,group by。
3、查尋出的數(shù)據(jù)不超過(guò)表中數(shù)據(jù)量的20%。
以上是“MySQL聚簇索引和非聚簇索引怎么用”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)-成都網(wǎng)站建設(shè)公司行業(yè)資訊頻道！
重慶分公司
重慶分公司
