如在我們的用戶表中，每個(gè)用戶都有一個(gè)身份證號(hào)，用戶在注冊或者是身份信息認(rèn)證時(shí)，業(yè)務(wù)代碼已校驗(yàn)了這個(gè)身份證號(hào)的唯一性。

當(dāng)經(jīng)常會(huì)有根據(jù)用戶身份證號(hào)來查用戶信息時(shí)，一般會(huì)在 身份證號(hào)(id_card)上建立 索引。

給id_card 字段創(chuàng)建索引可以考慮唯一索引或者是普通索引。

唯一索引的添加

主鍵索引添加

普通索引添加

1 從查詢角度來分析

如執(zhí)行查詢語句如下：

如果是普通索引，查詢過程如下所示

從 id_card 索引樹上查詢到 id_card為740這一行然后回表獲取這一行數(shù)據(jù)，然后再向下查詢一次，不滿足條件，終結(jié)查詢。

如果是唯一索引，查詢過程如下圖所示

對(duì)于唯一索引來講，索引定義了唯一性，查找到第一個(gè)滿足條件的記錄后，就會(huì)停止繼續(xù)檢索。

在這個(gè)查詢過程中，這兩種選擇的區(qū)別只是“查找和判斷下一條記錄”的操作，只是一次指針尋找和一次計(jì)算，性能消耗差異對(duì)于現(xiàn)在的 CPU 來說可以忽略不計(jì)。

2 從修改數(shù)據(jù)角度來分析

InnoDB引擎是按數(shù)據(jù)頁為單位來讀寫數(shù)據(jù)的，InnoDB引擎是以頁為單位，從磁盤讀出來，然后以頁為單位，將其整體讀入內(nèi)存，在 InnoDB 中，每個(gè)數(shù)據(jù)頁的大小默認(rèn)是 16KB。

所以在更新數(shù)據(jù)時(shí)，如果數(shù)據(jù)頁在內(nèi)存中就直接更新，否則會(huì)將這些更新操作緩存在 change buffer 中，先不作從磁盤中讀入數(shù)據(jù)頁，減少讀磁盤，語句的執(zhí)行速度會(huì)得到 明顯的提升。

然后在下次查詢需要訪問這個(gè)數(shù)據(jù)頁的時(shí)候，將數(shù)據(jù)頁讀入內(nèi)存，然后執(zhí)行 change buffer 中與這個(gè)頁有關(guān)的操作。

將 change buffer 中的操作應(yīng)用到原數(shù)據(jù)頁，得到最新結(jié)果的過程稱為 merge，同時(shí)系統(tǒng)有后臺(tái)線程會(huì)定期 merge，當(dāng)然在數(shù)據(jù)庫正常關(guān)閉 (shutdown)的過程中，也會(huì)執(zhí)行 merge 操作。

2.1 唯一索引的更新

唯一索引的更新就不能使用 change buffer。

因?yàn)?唯一索引來，所有的更新操作都要先判斷這個(gè)操作是否違反唯一性約束，如要插入 id_card 為 740的用戶數(shù)據(jù)，而數(shù)據(jù)庫中已有740這個(gè)唯一身份證，就不能進(jìn)行操作，而這個(gè)過程必須要將數(shù)據(jù)頁讀入內(nèi)存才能判斷。

2.2 插入新值時(shí)

如插入id_card 為 740的用戶數(shù)據(jù)，需要插入的目標(biāo)頁在內(nèi)存中，

對(duì)于唯一索引來說，找到 739 和 741之間的位置，判斷到?jīng)]有沖突，插入這個(gè)值，語句執(zhí)行

結(jié)束;

對(duì)于普通索引來說，找到 739 和 741之間的位置，插入這個(gè)值，語句執(zhí)行結(jié)束。

兩者的差別就是一次的沖突判斷，性能方面可以忽略。

當(dāng)需要插入的目標(biāo)頁不在內(nèi)存中

對(duì)于唯一索引來說，需要將數(shù)據(jù)頁讀入內(nèi)存，判斷到?jīng)]有沖突，插入這個(gè)值，語句執(zhí)行結(jié)

束;

對(duì)于普通索引來說，則是將更新記錄在 change buffer，語句執(zhí)行就結(jié)束了。

從磁盤讀數(shù)據(jù)內(nèi)存涉及隨機(jī) IO 的訪問，是數(shù)據(jù)庫里面成本最高的操作之一，change buffer 因?yàn)闇p少了隨機(jī)磁盤訪問，所以對(duì)更新性能的提升是會(huì)很明顯的。

3 索引選擇

普通索引和唯一索引應(yīng)該怎么選擇。其實(shí)，這兩類索引在查詢能力上是沒差別的，主要考慮的是對(duì)更新性能的影響。

在業(yè)務(wù)開發(fā)中，如果所有的更新后面，都馬上伴隨著對(duì)這個(gè)記錄的查詢，那么應(yīng)該關(guān)閉 change buffer，反之則應(yīng)該開啟。

完畢

不局限于思維，不局限語言限制，才是編程的最高境界。