我們建索引的時(shí)候，有全文索引、主鍵索引、唯一性索引、普通索引等，前面兩個(gè)好理解好區(qū)分，大家都知道啥時(shí)候用，后面兩個(gè)該如何區(qū)分呢?唯一性索引和普通索引該如何選擇呢?今天我們就來(lái)聊聊這個(gè)話題。

1. 準(zhǔn)備工作

假設(shè)我有如下表：

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `age` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `address` (`address`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

這表中有 10 萬(wàn)條模擬數(shù)據(jù)，10 萬(wàn)條模擬數(shù)據(jù)大家自行創(chuàng)建我就不啰嗦了。

看表結(jié)構(gòu)，有一個(gè) username 索引，這個(gè)索引是一個(gè)唯一性索引;還有一個(gè) address 索引，這是一個(gè)普通索引。

2. 查詢

2.1 普通索引查詢

我們先來(lái)看看普通索引的查詢。

我們來(lái)做一個(gè)簡(jiǎn)單的查詢：

select * from user where address='1';

根據(jù)我們前面的講解(索引下推，yyds!)，我們來(lái)梳理下這里的查詢步驟：

1. MySQL 的 server 層首先調(diào)用存儲(chǔ)引擎定位到第一個(gè)值為 1 的 address。
2. 由于 address 是二級(jí)索引，二級(jí)索引的葉子結(jié)點(diǎn)中保存著主鍵值，所以還需要根據(jù)主鍵值去主鍵索引上找到完整的數(shù)據(jù)行，其實(shí)就是回表(什么是 MySQL 的“回表”?)。
3. 存儲(chǔ)引擎將讀取到的數(shù)據(jù)行返回給 server 層。
4. 由于 address 是普通索引，不是唯一性索引，所以 address 為 1 的記錄可能不止一條，所以還需在第一次查詢的基礎(chǔ)上，沿著葉子結(jié)點(diǎn)內(nèi)部的單向鏈表繼續(xù)向后掃描，掃描到新的數(shù)據(jù)后，重復(fù) 2、3 步。
5. 當(dāng)掃到 address 不為 1 的記錄時(shí)，停止掃描。

上面是我們的分析，我們來(lái)看下執(zhí)行計(jì)劃：

執(zhí)行計(jì)劃中的 type 為 ref，就說(shuō)明了我們的分析是沒(méi)問(wèn)題的。

2.2 唯一性索引查詢

我們?cè)賮?lái)看看唯一性索引查詢。

先來(lái)看看一個(gè) SQL：

select * from user where username='1';

對(duì)于唯一性索引來(lái)說(shuō)，username 這一列的值是唯一的，所以在查詢的過(guò)程中，找到第一條username='1' 的記錄后，就不需要再找了，對(duì)比普通索引的查詢步驟，相當(dāng)于少了第 4、5 步。

我們來(lái)看看查詢計(jì)劃：

和前面普通索引的查詢計(jì)劃相比，這里的查詢計(jì)劃 type 為 const，也側(cè)面印證了我們的說(shuō)法。

2.3 PK

那么從上面的描述中我們可以看出來(lái)，似乎唯一性索引在查詢的時(shí)候表現(xiàn)更優(yōu)秀?真是情況到底如何，我們?cè)賮?lái)分析下。

首先，理論上來(lái)說(shuō)，唯一性索引在查詢的時(shí)候確實(shí)更優(yōu)秀一些，原因很簡(jiǎn)單：唯一性索引找到滿足條件的記錄后就不需要再找了;而普通索引找到滿足條件的記錄后，還需要繼續(xù)向后查找，直到遇到不滿足條件的記錄(address 不為 1 的記錄)才停止搜索，這么看來(lái)，確實(shí)唯一性索引更勝一籌!那么這種差異很明顯嗎?老實(shí)說(shuō)，這個(gè)優(yōu)勢(shì)可以忽略不計(jì)!

為什么呢?

1. 對(duì)于普通索引而言，雖然找到第一條記錄之后，還需要繼續(xù)找后面的，但是因?yàn)闈M足條件的記錄是連續(xù)的，索引只需要順著記錄之間的單向鏈表繼續(xù)向后讀就行了，速度快。
2. 由于 InnoDB 引擎讀數(shù)據(jù)的時(shí)候，不是一條一條的讀，而是一頁(yè)一頁(yè)的讀(默認(rèn)每頁(yè) 16KB，在什么是 MySQL 的“回表”?一文中，我有大致介紹 16KB 的問(wèn)題)，所以，即使繼續(xù)向后讀，也是內(nèi)存操作，速度很快。
3. 也不排除個(gè)別情況，例如滿足條件的記錄剛好是在當(dāng)前頁(yè)的最后一條，此時(shí)就需要加載新的一頁(yè)數(shù)據(jù)，但是這種概率比較小，可以忽略之。

綜上所述，唯一性索引和普通索引對(duì)搜索效率的影響可以忽略不計(jì)。

3 插入/修改

3.1 準(zhǔn)備知識(shí)

3.1.1 buffer pool

有一個(gè) buffer pool 需要大家了解。

小伙伴們知道，InnoDB 引擎存儲(chǔ)數(shù)據(jù)的時(shí)候，是以頁(yè)為單位的，每個(gè)數(shù)據(jù)頁(yè)的大小默認(rèn)是 16KB，我們可以通過(guò)如下命令來(lái)查看頁(yè)的大小：

16384/1024=16

剛好是 16KB。

計(jì)算機(jī)在存儲(chǔ)數(shù)據(jù)的時(shí)候，最小存儲(chǔ)單元是扇區(qū)，一個(gè)扇區(qū)的大小是 512 字節(jié)，而文件系統(tǒng)(例如 XFS/EXT4)最小單元是塊，一個(gè)塊的大小是 4KB，也就是四個(gè)塊組成一個(gè) InnoDB 中的頁(yè)。我們?cè)?MySQL 中針對(duì)數(shù)據(jù)庫(kù)的增刪改查操作，都是操作數(shù)據(jù)頁(yè)，說(shuō)白了，就是操作磁盤。

但是大家想想，如果每一次操作都操作磁盤，那么就會(huì)產(chǎn)生海量的磁盤 IO 操作，如果是傳統(tǒng)的機(jī)械硬盤，還會(huì)涉及到很多隨機(jī) IO 操作，效率低的令人發(fā)指。這嚴(yán)重影響了 MySQL 的性能。

為了解決這一問(wèn)題，MySQL 引入了 buffer pool，也就是我們常說(shuō)的緩沖池。

buffer pool 的主要作用就是緩存索引和表數(shù)據(jù)，以避免每一次操作都要進(jìn)行磁盤 IO，通過(guò) buffer pool 可以提高數(shù)據(jù)的訪問(wèn)速度。

通過(guò)如下命令可以查看 buffer pool 的默認(rèn)大小：

134217728/1024/1024=128

默認(rèn)大小是 128MB，因?yàn)樗筛邕@里的 MySQL 是安裝在 Docker 中，所以這個(gè)分配的小一些。一般來(lái)說(shuō)，如果一個(gè)服務(wù)器只是運(yùn)行了一個(gè) MySQL 服務(wù)，我們可以設(shè)置 buffer pool 的大小為服務(wù)器內(nèi)存大小的 75%～80%。

3.1.2 change buffer

還有一個(gè) change buffer 需要大家了解。

前面我們說(shuō)的 buffer pool 雖然提高了訪問(wèn)速度，但是增刪改的效率并沒(méi)有因此提升，當(dāng)涉及到增刪改的時(shí)候，還是需要磁盤 IO，那么效率一樣低的令人發(fā)指。

為了解決這個(gè)問(wèn)題，MySQL 中引入了 change buffer。change buffer 以前并不叫這個(gè)名字，以前叫 insert buffer，即只針對(duì) insert 操作有效，現(xiàn)在改名叫 change buffer 了，不僅僅針對(duì) insert 有效，對(duì) delete 和 update 操作也是有效的，change buffer 主要是對(duì)非唯一的索引有效，如果字段是唯一性索引，那么更新的時(shí)候要去檢查唯一性，依然無(wú)法避免磁盤 IO。

change buffer 就是說(shuō)，當(dāng)我們需要更改數(shù)據(jù)庫(kù)中的數(shù)據(jù)的時(shí)候，我們把更改記錄到內(nèi)存中，等到將來(lái)數(shù)據(jù)被讀取的時(shí)候，再將內(nèi)存中的數(shù)據(jù) merge 到 buffer pool，此時(shí) buffer pool 中的數(shù)據(jù)和磁盤中的數(shù)據(jù)就會(huì)有差異，有差異的數(shù)據(jù)我們稱之為臟頁(yè)，在滿足條件的時(shí)候(redo log 寫滿了、內(nèi)存寫滿了、其他空閑時(shí)候)，InnoDB 會(huì)把臟頁(yè)刷新回磁盤。這種方式可以有效降低寫操作的磁盤 IO，提升數(shù)據(jù)庫(kù)的性能。

通過(guò)如下命令我們可以查看 change buffer 的大小以及哪些操作會(huì)涉及到 change buffer：

• innodb_change_buffer_max_size：這個(gè)配置表示 change buffer 的大小占整個(gè)緩沖池的比例，默認(rèn)值是 25%，最大值是 50%。
• innodb_change_buffering：這個(gè)操作表示哪些寫操作會(huì)用到 change buffer，默認(rèn)的 all 表示所有寫操作，我們也可以自己設(shè)置為none/inserts/deletes/changes/purges 等。

不過(guò) change buffer 和 buffer pool 都涉及到內(nèi)存操作，數(shù)據(jù)不能持久化，那么，當(dāng)存在臟頁(yè)的時(shí)候，MySQL 如果突然掛了，就有可能造成數(shù)據(jù)丟失(因?yàn)閮?nèi)存中的數(shù)據(jù)還沒(méi)寫到磁盤上)，但是我們?cè)趯?shí)際使用 MySQL 的時(shí)候，其實(shí)并不會(huì)有這個(gè)問(wèn)題，那么問(wèn)題是怎么解決的?那就得靠 redo log 了，這個(gè)松哥以后再寫文章和大家介紹 redo log。

3.2 PK

看了上面 change buffer 的介紹，大家應(yīng)該已經(jīng)明白了：

• 對(duì)于非唯一性索引，插入時(shí)候直接將數(shù)據(jù)存儲(chǔ)到 change buffer 中就行了，這是一個(gè)內(nèi)存操作，很快。
• 對(duì)于唯一性索引，插入的時(shí)候，必須要將數(shù)據(jù)頁(yè)讀入到內(nèi)存中(這一步涉及到大量的隨機(jī) IO，效率低)，檢查沒(méi)有沖突，然后插入。

所以，很明顯，在插入的時(shí)候，非唯一性索引更有優(yōu)勢(shì)。

4. 小結(jié)

那么對(duì)于一個(gè)需要全局唯一的字段，到底是用普通索引還是唯一性索引呢?這個(gè)我覺(jué)得很難給大家一個(gè)放之四海而皆準(zhǔn)的建議，因?yàn)閿?shù)據(jù)庫(kù)優(yōu)化很多時(shí)候不是絕對(duì)的，要結(jié)合自己的實(shí)際業(yè)務(wù)來(lái)，所以，無(wú)論何時(shí)何地，先滿足業(yè)務(wù)需求，在此基礎(chǔ)上，再去討論數(shù)據(jù)庫(kù)優(yōu)化。

如果你能從業(yè)務(wù)上確保該字段唯一，那么可以使用普通索引，這樣可以提高插入/更新速度。

然而，根據(jù)墨菲定律，你要是不用唯一索引，該字段中將來(lái)大概率會(huì)出現(xiàn)臟值，所以你也要考慮業(yè)務(wù)上對(duì)于臟值的容忍程度。