建站
咨詢
售后
建站咨詢
新聞中心
前言
在使用MySQL的過程中，隨著表數(shù)據(jù)的逐漸增多，為了更快的查詢我們需要的數(shù)據(jù)，我們會(huì)在表中建立不同類型的索引。
今天我們來聊一聊，普通索引和唯一索引的使用場景，以及為什么說推薦大家優(yōu)先使用普通索引，盡量避免使用唯一索引。
對(duì)于一個(gè)普通的二級(jí)索引，目的就是為了加速查詢，所以我們可能會(huì)為表中的某個(gè)字段或者某些字段，建立一個(gè)普通的二級(jí)索引。
而對(duì)于唯一索引來說，由于其唯一鍵約束的特性，有時(shí)我們會(huì)更多的賦予其業(yè)務(wù)含義。比如有一張存儲(chǔ)身份證號(hào)的表，為了保證身份證號(hào)的唯一性，我們會(huì)在身份證號(hào)字段上建立唯一索引。
那為什么說，不推薦大家使用唯一索引呢？
接下來，我們從查詢和更新兩方面分析一下唯一索引和普通索引的性能差距。
查詢性能
我們知道每個(gè)索引其實(shí)都是一棵二叉樹，所以我簡單畫了一個(gè)索引圖，不太好看，大家多多擔(dān)待。
給大家稍微解釋一下這張圖，不同顏色代表不同的數(shù)據(jù)頁，這里假設(shè)一個(gè)數(shù)據(jù)頁里面存放兩條數(shù)據(jù)。
我們知道MySQL磁盤與內(nèi)存交互是通過一個(gè)叫做數(shù)據(jù)頁的單位，每個(gè)數(shù)據(jù)頁默認(rèn)的大小是16K。
在一棵樹上，只有葉子節(jié)點(diǎn)才會(huì)真正的存放數(shù)據(jù)，非葉子節(jié)點(diǎn)存放的是每個(gè)下級(jí)數(shù)據(jù)頁中最小的索引字段以及指向下級(jí)數(shù)據(jù)頁的指針。
對(duì)于主鍵索引，葉子節(jié)點(diǎn)存放的是一行真正的數(shù)據(jù)，而對(duì)于二級(jí)索引來說，在葉子節(jié)點(diǎn)存儲(chǔ)的是索引字段以及對(duì)應(yīng)的主鍵id。
好了，下面我們分析一下，普通二級(jí)索引和唯一索引是如何查數(shù)據(jù)的？
以一個(gè)簡單的查詢sql為例：select id from t where m=103;
1，MySQL從根節(jié)點(diǎn)出發(fā)，通過二分法判斷m=103大于100小于104，所以會(huì)找到根節(jié)點(diǎn)中100對(duì)應(yīng)的數(shù)據(jù)頁100-102；
2，在100-102的數(shù)據(jù)頁上，由于103大于102，所以會(huì)找到102對(duì)應(yīng)的102-103的數(shù)據(jù)頁；
3，在這個(gè)數(shù)據(jù)頁上，找到了m=103的記錄，并獲取到了要查詢的id字段。
對(duì)于普通的二級(jí)索引來說，找到第一條m=103的記錄之后，會(huì)繼續(xù)向后查找，在104-105這個(gè)數(shù)據(jù)頁中判斷是否還有符合m=103條件的記錄，如果沒有則結(jié)束查詢。
而對(duì)于唯一索引來說，由于其唯一性約束，所以在查找到第一條記錄之后，就結(jié)束了查找。
可以看到，二者的差別就在于是否繼續(xù)查到下一條。
那這兩者有多大的性能差距呢？答案是幾乎沒有。
我們知道，MySQL的數(shù)據(jù)是以頁為單位存放的，以一個(gè)int類型的二級(jí)索引為例，一個(gè)int占4個(gè)字節(jié)，加上MySQL的頭信息6個(gè)字節(jié)，相當(dāng)于10個(gè)字節(jié)。
那么一個(gè)16k的頁上能存放多少記錄呢？
16*1024/10 = 1638。也就是說，一個(gè)數(shù)據(jù)頁就可能放下1600多條記錄。那么我們?cè)诓樵償?shù)據(jù)時(shí)，會(huì)把整個(gè)數(shù)據(jù)頁都加載進(jìn)內(nèi)存，此時(shí)對(duì)于普通二級(jí)索引判斷下一個(gè)記錄的操作所需的消耗是非常非常小的。
可以說，從查詢方面來看，普通二級(jí)索引和唯一索引的性能基本是相當(dāng)?shù)摹?/p>
更新性能
唯一索引和普通二級(jí)索引的性能差距主要體現(xiàn)在更新操作上。
對(duì)于MySQL來說，更新一條語句的邏輯是首先讀到要更新的記錄，如果這個(gè)記錄沒有在內(nèi)存里，就先加載到內(nèi)存。然后執(zhí)行更新的語句，之后再把變更的數(shù)據(jù)刷新到磁盤中。
但是，對(duì)于MySQL來說，把數(shù)據(jù)從磁盤讀到內(nèi)存涉及到隨機(jī)IO，是成本非常高的一種操作。
如果每次更新數(shù)據(jù)都要這么來一次的話，高性能這個(gè)指標(biāo)恐怕很難保證。
所以，設(shè)計(jì)MySQL的大神們引入了一個(gè)叫做change buffer的東西。
change buffer是一種可以持久化的緩存數(shù)據(jù)，當(dāng)我們要更新數(shù)據(jù)時(shí)，如果要更新的數(shù)據(jù)不存在于內(nèi)存，此時(shí)并不需要把數(shù)據(jù)從磁盤加載到內(nèi)存，而是將更新操作記錄在change buffer中，更新操作就算完成了。
當(dāng)下次要讀取這些數(shù)據(jù)時(shí)，會(huì)把讀到的數(shù)據(jù)和change buffer進(jìn)行合并，或者叫merge。
通過change buffer，更新操作就不需要去讀磁盤了，全程都是內(nèi)存操作，性能自然可以得到極大的提升。
但是！但是問題又來了！
change buffer只對(duì)普通二級(jí)索引有效，對(duì)于唯一索引是沒有效果的。
為什么呢？
因?yàn)樵诟乱粭l記錄時(shí)，我們需要檢查索引的唯一性約束。
如何檢查呢？自然首先要把數(shù)據(jù)從磁盤加載到內(nèi)存里面才能進(jìn)行判斷。
可是如果都已經(jīng)把數(shù)據(jù)加載到內(nèi)存里，再去使用change buffer不就顯得多此一舉了。
所以，唯一索引不能，也沒必要去使用change buffer來提升性能了。
由于對(duì)唯一索引的更新涉及到讀磁盤這個(gè)隨機(jī)IO操作，性能自然也是比不上普通二級(jí)索引了，這就是推薦大家優(yōu)先使用普通二級(jí)索引的原因了。
經(jīng)過對(duì)比，大家也可以看到，這兩種索引在查詢上性能基本是一致的，其性能差距主要體現(xiàn)在更新操作上。
其實(shí)即便是大家有一些特殊的業(yè)務(wù)需要，比如存放唯一的身份證號(hào)等，還是建議大家通過業(yè)務(wù)層去約束。
總的來說，普通的二級(jí)索引比唯一索引帶來的收益要更大。
當(dāng)前標(biāo)題：MySQL：為什么說應(yīng)該優(yōu)先選擇普通索引，盡量避免使用唯一索引
當(dāng)前地址：http://m.5511xx.com/article/djigdjj.html
