主鍵索引

主鍵索引，簡稱主鍵，原文是PRIMARY KEY，由一個(gè)或多個(gè)列組成，用于唯一性標(biāo)識(shí)數(shù)據(jù)表中的某一條記錄。一個(gè)表可以沒有主鍵，但最多只能有一個(gè)主鍵，并且主鍵值不能包含NULL。

在MySQL中，InnoDB數(shù)據(jù)表的主鍵設(shè)計(jì)我們通常遵循幾個(gè)原則：

采用一個(gè)沒有業(yè)務(wù)用途的自增屬性列作為主鍵；
主鍵字段值總是不更新，只有新增或者刪除兩種操作；
不選擇會(huì)動(dòng)態(tài)更新的類型，比如當(dāng)前時(shí)間戳等。
這么做的好處有幾點(diǎn)：

新增數(shù)據(jù)時(shí)，由于主鍵值是順序增長的，innodb page發(fā)生分裂的概率降低了；可以參考以往的分享“[MySQL FAQ]系列 — 為什么InnoDB表要建議用自增列做主鍵”；
業(yè)務(wù)數(shù)據(jù)有變更時(shí)，不修改主鍵值，物理存儲(chǔ)位置發(fā)生變化的概率降低了，innodb page中產(chǎn)生碎片的概率也降低了。
MyISAM表因?yàn)槭嵌呀M織表，主鍵類型設(shè)計(jì)方面就可以這么講究了。

輔助索引，就是我們常規(guī)所指的索引，原文是SECONDARY KEY。輔助索引里還可以再分為唯一索引，非唯一索引。

唯一索引其實(shí)應(yīng)該叫做唯一性約束，它的作用是避免一列或多列值存在重復(fù)，是一種約束性索引。

在MyISAM引擎中，唯一索引除了key值允許存在NULL外，其余的和主鍵索引沒有本質(zhì)性區(qū)別。也就是說，在MyISAM引擎中，不允許存在NULL值的唯一索引，本質(zhì)上和主鍵索引是一回事。

而在InnoDB引擎中，主鍵索引和輔助索引的區(qū)別就很大了。主鍵索引會(huì)被選中作為聚集索引，而唯一索引和普通輔助索引間除了唯一性約束外，在存儲(chǔ)上沒本質(zhì)區(qū)別。

從查詢性能上來說，在MyISAM表中主鍵索引和不允許有NULL的唯一索引的查詢性能是相當(dāng)?shù)?，在InnoDB表通過唯一索引查詢則需要多一次從輔助索引到主鍵索引的轉(zhuǎn)換過程。InnoDB表基于普通索引的查找代價(jià)更高，因?yàn)槊看螜z索到結(jié)果后，還需要至少再多檢索一次才能確認(rèn)是否還有更多符合條件的結(jié)果，主鍵索引和唯一索引就不需要這么做了。

經(jīng)過測試，對100萬行數(shù)據(jù)的MyISAM做隨機(jī)檢索（整數(shù)類型），主鍵和唯一索引的效率基本一樣，普通索引的檢索效率則慢了30%以上。換成InnoDB表的話，唯一索引比主鍵索引效率約慢9%，普通索引比主鍵索引約慢了50%以上。

聚集索引
在MySQL中，InnoDB引擎表是（聚集）索引組織表（clustered index organize table），而MyISAM引擎表則是堆組織表（heap organize table）。

也有人把聚集索引稱為聚簇索引。

當(dāng)然了，聚集索引的概念不是MySQL里特有的，其他數(shù)據(jù)庫系統(tǒng)也同樣有。

簡言之，聚集索引是一種索引組織形式，索引的鍵值邏輯順序決定了表數(shù)據(jù)行的物理存儲(chǔ)順序，而非聚集索引則就是普通索引了，僅僅只是對數(shù)據(jù)列創(chuàng)建相應(yīng)的索引，不影響整個(gè)表的物理存儲(chǔ)順序。

我們先來看看兩種存儲(chǔ)形式的不同之處：
簡單說，IOT表里數(shù)據(jù)物理存儲(chǔ)順序和主鍵索引的順序一致，所以如果新增數(shù)據(jù)是離散的，會(huì)導(dǎo)致數(shù)據(jù)塊趨于離散，而不是趨于順序。而HOT表數(shù)據(jù)寫入的順序是按寫入時(shí)間順序存儲(chǔ)的。
IOT表相比HOT表的優(yōu)勢是：

范圍查詢效率更高；
數(shù)據(jù)頻繁更新（聚集索引本身不更新）時(shí)，更不容易產(chǎn)生碎片；
特別適合有一小部分熱點(diǎn)數(shù)據(jù)頻繁讀寫的場景；
通過主鍵訪問數(shù)據(jù)時(shí)快速可達(dá)；
IOT表的不足則有：

• 數(shù)據(jù)變化如果是離散為主的話，那么效率會(huì)比HOT表差；

HOT表的不足有：

• 索引回表讀開銷很大；
• 大部分?jǐn)?shù)據(jù)讀取時(shí)隨機(jī)的，無法保證被順序讀取，開銷大；
• 每張InnoDB表只能創(chuàng)建一個(gè)聚集索引，聚集索引可以由一列或多列組成。

上面說過，InnoDB是聚集索引組織表，它的聚集索引選擇規(guī)則是這樣的：

首先選擇顯式定義的主鍵索引做為聚集索引；
如果沒有，則選擇第一個(gè)不允許NULL的唯一索引；
還是沒有的話，就采用InnoDB引擎內(nèi)置的ROWID作為聚集索引；

可以看到，在這個(gè)索引結(jié)構(gòu)的葉子節(jié)點(diǎn)中，節(jié)點(diǎn)key值是主鍵的值，而節(jié)點(diǎn)的value則存儲(chǔ)其余列數(shù)據(jù)，以及額外的ROWID、rollback pointer、trx id等信息。

結(jié)合這個(gè)圖，以及上面所述，我們可以知道：在InnoDB表中，其聚集索引相當(dāng)于整張表，而整張表也是聚集索引。主鍵必然是聚集索引，而聚集索引則未必是主鍵。

MyISAM是堆組織表，它沒有聚集索引的概念。

兩者的比較

下面是一個(gè)簡單的比較表

最新評論