一、了解普通索引和唯一索引

普通索引

MySQL中基本索引類(lèi)型咐扭，沒(méi)有什么限制芭挽，允許在定義索引的列中插入重復(fù)值和空值滑废，純粹為了查詢(xún)數(shù)據(jù)更快一點(diǎn)。

唯一索引

索引列中的值必須是唯一的袜爪，但是允許為空值蠕趁。

主鍵索引是一種特殊的唯一索引，不允許有空值辛馆。

擴(kuò)展一下其它兩中索引俺陋，知識(shí)點(diǎn)放在一起記憶會(huì)更好

全文索引

只能在char，varchar昙篙，text類(lèi)型字段上使用全文索引腊状，介紹了要求，說(shuō)說(shuō)什么是全文索引苔可，就是在一堆文字中缴挖，通過(guò)其中的某個(gè)關(guān)鍵字等，就能找到該字段所屬的記錄行焚辅，比如有“你是個(gè)靚仔醇疼，靚女。法焰。【蟊校”通過(guò)靚仔埃仪，可能就可以找到該條記錄。

空間索引

空間索引是對(duì)空間數(shù)據(jù)類(lèi)型的字段建立的索引陕赃，MySQL中的空間數(shù)據(jù)類(lèi)型有四種卵蛉，GEOMETRY、POINT么库、LINESTRING傻丝、POLYGON。在創(chuàng)建空間索引時(shí)诉儒，使用SPATIAL關(guān)鍵字葡缰。要求，引擎為Myisam忱反，創(chuàng)建空間索引的列泛释，必須將其聲明為not null。

索引添加方式

1温算、 主鍵索引：alter table table_name add primary key (column)

2怜校、 唯一索引：alter table table_name add unique (column)

3、普通索引：alter table table_name add index index_name (column)

4注竿、全文索引：alter table table_name add fulltext (column)

5茄茁、多列索引：alter table table_name add index index_name (column1,column2,column3)

二魂贬、應(yīng)用場(chǎng)景

現(xiàn)在你應(yīng)該知道普通索引和唯一索引的區(qū)別，接下來(lái)看看在一些場(chǎng)景下如何選擇兩個(gè)索引裙顽。

丁老師文章中提到一個(gè)業(yè)務(wù)場(chǎng)景是市民系統(tǒng)付燥，通過(guò)身份證號(hào)來(lái)查姓名。

這里咔咔也借用這個(gè)場(chǎng)景來(lái)給大家通過(guò)咔咔的思路描述一下這個(gè)流程锦庸。

執(zhí)行語(yǔ)句為select name from user where card = '6104301996xxxxxxxx';

這個(gè)場(chǎng)景第一反應(yīng)肯定是給card創(chuàng)建一個(gè)索引机蔗，但創(chuàng)建什么索引呢？主鍵索引肯定不建議使用甘萧。

思考：為什么不能用身份證號(hào)來(lái)作為主鍵索引萝嘁？

三、為什么不能用太大的值作為主鍵

Innodb存儲(chǔ)引擎的主鍵索引結(jié)構(gòu)如下圖

普通索引數(shù)據(jù)結(jié)構(gòu)如下圖

主鍵索引的葉子節(jié)點(diǎn)存儲(chǔ)的是對(duì)應(yīng)主鍵的整行數(shù)據(jù)扬卷。

普通索引的葉子節(jié)點(diǎn)存儲(chǔ)的是對(duì)應(yīng)的主鍵值牙言。

如果說(shuō)B+Tree讀取數(shù)據(jù)的深度是三層，每個(gè)磁盤(pán)的大小為16kb怪得。

那在B+Tree中非葉子節(jié)點(diǎn)可以存儲(chǔ)多少數(shù)據(jù)呢咱枉！一般來(lái)說(shuō)我們每個(gè)表都會(huì)存在一個(gè)主鍵。

根據(jù)三層來(lái)計(jì)算徒恋，第一層跟第二層存儲(chǔ)的是key值蚕断，也就是主鍵值。

都知道int類(lèi)型所占的內(nèi)存時(shí)4Byte（字節(jié)）入挣，指針的存儲(chǔ)就給個(gè)6Byte亿乳，一共就是10Tybe，那么第一層節(jié)點(diǎn)就可以存儲(chǔ)16 * 1000 /10 = 1600径筏。

同理第二層每個(gè)節(jié)點(diǎn)也是可以存儲(chǔ)1600個(gè)key葛假。

第三層是葉子節(jié)點(diǎn)，每個(gè)磁盤(pán)存儲(chǔ)大小同樣安裝BTree的計(jì)算一樣滋恬，每條數(shù)據(jù)占1kb聊训。

在B+Tree中三層可以存儲(chǔ)的數(shù)據(jù)就是1600 * 1600 * 16 = 40960000

結(jié)論：若主鍵過(guò)大會(huì)直接影響索引存儲(chǔ)的數(shù)據(jù)量，所以非常不建議使用過(guò)大的數(shù)據(jù)作為主鍵索引恢氯。

四带斑、從查詢(xún)的角度分析

假設(shè)現(xiàn)在要查card = 5 這條記錄，查詢(xún)過(guò)程為勋拟，先通過(guò)B+樹(shù)從樹(shù)根開(kāi)始遏暴，按層搜索到葉子節(jié)點(diǎn)，然后通過(guò)二分法來(lái)定位card = 5 的這條記錄指黎。

普通索引

對(duì)于普通索引來(lái)說(shuō)當(dāng)找到card = 5這條記錄后朋凉，還會(huì)繼續(xù)查找，直到碰到第一個(gè)不滿(mǎn)足card = 5的記錄為止醋安。

唯一索引

對(duì)于唯一索引就非常簡(jiǎn)單的了杂彭，唯一索引的特性就是數(shù)據(jù)唯一性墓毒，所以查到card = 5這條記錄后就不在查找下一條記錄了。

普通索引多查詢(xún)的一次對(duì)性能影響大嗎亲怠？

這個(gè)影響幾乎可以忽略所计，在之前的幾期文章中咔咔給大家普及了一個(gè)名詞“局部性原理”。

數(shù)據(jù)和程序都有聚集成群的傾向团秽，在訪(fǎng)問(wèn)了一條數(shù)據(jù)之后主胧，在之后有極大的可能再次訪(fǎng)問(wèn)這條數(shù)據(jù)和這條數(shù)據(jù)的相鄰數(shù)據(jù)。

所以說(shuō)MySQL的Innodb存儲(chǔ)引擎习勤，在讀取數(shù)據(jù)時(shí)也會(huì)采取這種局部性原理踪栋，每次讀取的數(shù)據(jù)是16kb，也就是一頁(yè)图毕。

在Innodb存儲(chǔ)引擎下每頁(yè)的大小默認(rèn)為16kb夷都，這個(gè)參數(shù)也可以進(jìn)行調(diào)整，參數(shù)為innodb_page_size予颤。

但有一種情況雖說(shuō)幾率非常低囤官，但還是需要知道的。

當(dāng)索引為普通索引時(shí)蛤虐，查到的數(shù)據(jù)正好是一頁(yè)的最后一個(gè)數(shù)據(jù)党饮，此時(shí)就需要讀取下一頁(yè)的數(shù)據(jù)，這個(gè)操作是有點(diǎn)復(fù)雜驳庭，但對(duì)于現(xiàn)在的CPU來(lái)說(shuō)可以忽略不計(jì)刑顺。

五、了解change buffer

首先嚷掠，需要先了解一個(gè)新的知識(shí)點(diǎn)change buffer。

當(dāng)需要更新card = 5這條記錄時(shí)荞驴，這條數(shù)據(jù)所在的數(shù)據(jù)頁(yè)在內(nèi)存中就直接更新不皆，如若不在的話(huà)就需要將更新的操作緩存在change buffer中。當(dāng)下次查詢(xún)需要訪(fǎng)問(wèn)這個(gè)數(shù)據(jù)頁(yè)時(shí)熊楼，將這個(gè)數(shù)據(jù)頁(yè)讀入內(nèi)存霹娄，然后執(zhí)行change buffer中與這個(gè)頁(yè)有關(guān)的操作。

接著鲫骗，了解另一個(gè)新的知識(shí)點(diǎn)merge犬耻。

當(dāng)把change buffer中的數(shù)據(jù)應(yīng)用到數(shù)據(jù)頁(yè)，得到最新結(jié)果的過(guò)程成為merge执泰，另外數(shù)據(jù)庫(kù)正常關(guān)閉的過(guò)程中枕磁，也會(huì)執(zhí)行merge操作。

結(jié)論：更新操作將記錄先記錄到change buffer中术吝，可以減少磁盤(pán)I/O计济，語(yǔ)句執(zhí)行速度會(huì)提升茸苇。

注意

1、 數(shù)據(jù)從change buffer讀入內(nèi)存是需要占用buffer pool的沦寂，使用change buffer可以避免占用內(nèi)存学密。

2、change buffer 也是可以持久化數(shù)據(jù)的传藏，change buffer 在內(nèi)存中有拷貝腻暮，也會(huì)被寫(xiě)入到磁盤(pán)。

六毯侦、change buffer在什么條件下使用

思考：為什么唯一索引使用不到change buffer

唯一索引肯定是用不到哭靖，對(duì)于這個(gè)答案如果你感覺(jué)有點(diǎn)不適，就需要在回到之前幾期文章再好好看看叫惊。

唯一索引插入一行數(shù)據(jù)時(shí)都會(huì)執(zhí)行一次查詢(xún)操作判斷表中是否已經(jīng)存在這條記錄款青，判斷是否違反唯一約束，既然必須得把數(shù)據(jù)頁(yè)的數(shù)據(jù)讀入內(nèi)存霍狰，那還用change buffer個(gè)什么勁奥詹荨！

因此蔗坯，只有普通索引可以使用康震。

在上文中知道了將change buffer數(shù)據(jù)讀入內(nèi)存時(shí)是需要占用buffer pool的內(nèi)存，因此在MySQL中也給了一個(gè)參數(shù)來(lái)設(shè)置change buffer的大小宾濒。跟其它的數(shù)據(jù)單位可能有點(diǎn)出入腿短，若設(shè)置為30，就表示change buffer只占用buffer pool內(nèi)存的30%绘梦。

思考：在什么場(chǎng)景下不能使用change buffer橘忱？

change buffer的作用是將更新的動(dòng)作緩存下來(lái)，所以對(duì)一個(gè)數(shù)據(jù)頁(yè)做merge時(shí)卸奉，change buffer記錄的變更越多钝诚，收益就越大。

但也并不是所有場(chǎng)景都適用榄棵，咔咔目前所開(kāi)發(fā)的是一款賬款軟件凝颇，大部分更新后都是立馬查看，這種情況是不是就違背了上面說(shuō)的對(duì)一個(gè)數(shù)據(jù)頁(yè)做merge時(shí)疹鳄，change buffer記錄的越多拧略，收益越大。

因此瘪弓，只有寫(xiě)多讀少的場(chǎng)景垫蛆，change buffer才能發(fā)揮非常大的作用。

思考：為什么更新完立馬查詢(xún)change buffer就沒(méi)多大用處了呢？

一條記錄發(fā)起更新操作后月褥，先記錄到change buffer 中弛随，接著，當(dāng)查詢(xún)的數(shù)據(jù)在這個(gè)數(shù)據(jù)頁(yè)時(shí)會(huì)立即觸發(fā)merge宁赤，這樣隨機(jī)訪(fǎng)問(wèn)的IO的次數(shù)不會(huì)減少舀透，反而增加了change buffer的維護(hù)代價(jià)。所以說(shuō)這種業(yè)務(wù)模式使用change biffer會(huì)起到反作用决左。

思考：如何關(guān)閉change buffer

只需要將參數(shù)innodb_change_buffer_max_size = 0 即可愕够。

七、從更新語(yǔ)句性能的影響的角度分析

第一種情況這條數(shù)據(jù)要更新的數(shù)據(jù)頁(yè)在內(nèi)存中佛猛。

唯一索引：在內(nèi)存中查找是否有這條記錄惑芭，不存在時(shí)則插入這個(gè)值。

普通索引：直接更新需要更新的值即可继找。

結(jié)論:當(dāng)要更新的數(shù)據(jù)頁(yè)在內(nèi)存中時(shí)遂跟，唯一索引就比普通索引多一次判斷。

第二種情況這條數(shù)據(jù)要更新的數(shù)據(jù)頁(yè)不在內(nèi)存中婴渡。

唯一索引：需要將這條數(shù)據(jù)所在的數(shù)據(jù)頁(yè)讀入內(nèi)存中幻锁，查找是否存在這條記錄，然后更新數(shù)據(jù)边臼。

普通索引：將這條要更新的數(shù)據(jù)記錄在change buffer即可哄尔。

結(jié)論：change buffer 當(dāng)更新的數(shù)據(jù)不在數(shù)據(jù)頁(yè)中時(shí)，如果你的索引是普通索引則可以很顯著的提升性能柠并。

注意：當(dāng)你把一個(gè)索引從普通索引改為唯一索引時(shí)一定要注意change buffer的影響岭接，會(huì)直接影響內(nèi)存命中率。

八臼予、總結(jié)

回到文章主題如何選擇普通索引和唯一索引鸣戴，在查詢(xún)方面兩者是沒(méi)有什么差別的，主要是在更新操作上的影響粘拾。

如果你的業(yè)務(wù)跟咔咔的場(chǎng)景一樣窄锅，更新后立馬要對(duì)這個(gè)記錄查詢(xún)，那么就可以選擇直接關(guān)閉change buffer半哟。

若不是這種場(chǎng)景酬滤，則盡量選擇普通索引签餐，使用change buffer可以非常明顯的提升更新性能寓涨。