我們建索引的時(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)梳理下這里的查詢步驟：
- MySQL 的 server 層首先調(diào)用存儲(chǔ)引擎定位到第一個(gè)值為 1 的 address遗座。
- 由于 address 是二級(jí)索引，二級(jí)索引的葉子結(jié)點(diǎn)中保存著主鍵值俊扳，所以還需要根據(jù)主鍵值去主鍵索引上找到完整的數(shù)據(jù)行，其實(shí)就是回表（什么是 MySQL 的“回表”猛遍？）馋记。
- 存儲(chǔ)引擎將讀取到的數(shù)據(jù)行返回給 server 層。
- 由于 address 是普通索引懊烤，不是唯一性索引梯醒，所以 address 為 1 的記錄可能不止一條，所以還需在第一次查詢的基礎(chǔ)上腌紧，沿著葉子結(jié)點(diǎn)內(nèi)部的單向鏈表繼續(xù)向后掃描茸习，掃描到新的數(shù)據(jù)后，重復(fù) 2壁肋、3 步号胚。
- 當(dāng)掃到 address 不為 1 的記錄時(shí)，停止掃描浸遗。
上面是我們的分析猫胁，我們來(lái)看下執(zhí)行計(jì)劃：
執(zhí)行計(jì)劃中的 type 為 ref，就說(shuō)明了我們的分析是沒問(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ì)垮抗！
為什么呢氏捞？
- 對(duì)于普通索引而言，雖然找到第一條記錄之后冒版，還需要繼續(xù)找后面的液茎，但是因?yàn)闈M足條件的記錄是連續(xù)的，索引只需要順著記錄之間的單向鏈表繼續(xù)向后讀就行了辞嗡，速度快捆等。
- 由于 InnoDB 引擎讀數(shù)據(jù)的時(shí)候，不是一條一條的讀续室，而是一頁(yè)一頁(yè)的讀（默認(rèn)每頁(yè) 16KB楚里，在什么是 MySQL 的“回表”？一文中猎贴，我有大致介紹 16KB 的問(wèn)題）班缎，所以，即使繼續(xù)向后讀她渴，也是內(nèi)存操作达址，速度很快。
- 也不排除個(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)速度，但是增刪改的效率并沒有因此提升剃诅，當(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ù)還沒寫到磁盤上），但是我們?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到踏，效率低）杠袱，檢查沒有沖突，然后插入窝稿。
所以楣富，很明顯，在插入的時(shí)候伴榔，非唯一性索引更有優(yōu)勢(shì)纹蝴。
4. 小結(jié)
那么對(duì)于一個(gè)需要全局唯一的字段，到底是用普通索引還是唯一性索引呢潮梯？這個(gè)我覺得很難給大家一個(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ì)于臟值的容忍程度绢陌。
