跳槽必備之你設(shè)計(jì)索引的原則是什么?怎么避免索引失效?

之前我們已經(jīng)詳細(xì)介紹了關(guān)于索引的原理和索引的查詢的原則，所謂工欲善其事必先利其器，各位在學(xué)習(xí)階段一定要要循序漸進(jìn)的來學(xué)習(xí)這塊知識，千萬不要眼高手低，一定要不急不躁，爭取一個蘿卜一個坑，學(xué)完后能一次性拿下這些知識點(diǎn)，然后再加以運(yùn)用。

前面的文章我們討論過，索引的設(shè)計(jì)要根據(jù) WHERE 條件和 ORDER BY 還有 GROUP BY 后面的字段進(jìn)行設(shè)計(jì)，至于原因具體在我前面的文章MySQL索引的原理有詳細(xì)介紹。這里我們再簡單概述下。

MySQL針對主鍵索引會維護(hù)一個B+樹的結(jié)構(gòu)，這個我們稱之為聚簇索引，針對非主鍵（一般都是建立的聯(lián)合索引）會對索引字段依次排序，然后從第一個字段值開始比較，第一個字段值相同就針對下一個字段值進(jìn)行比較，依次往后推。

如果聯(lián)合索引中的字段值都是一樣的，那么就根據(jù)主鍵來排序。另外聚簇索引（主鍵索引）的B+樹中保存的是一行記錄的所有信息，非聚簇索引（非主鍵索引）僅僅保存索引字段值和主鍵字段值。

好了，對于索引原理的回顧我們就介紹到這里，本篇文章，我們繼續(xù)介紹的是MySQL設(shè)置的基本原則，這個也很好理解，就是在設(shè)計(jì)和建立索引的時(shí)候需要遵循哪些原則，按照“標(biāo)準(zhǔn)”去建立索引。今天我們就將關(guān)于索引的設(shè)計(jì)的所有的原則一次性講清楚。

再多說幾句，關(guān)于這個知識點(diǎn)，在面試的時(shí)候，我經(jīng)常會問候選人，以此來判斷他對索引是不是真的有理解，而不是簡單的背八股文！

主鍵索引

對于主鍵索引其實(shí)是最簡單的，但是這里有一些注意的地方還是再啰嗦下。

大家在設(shè)計(jì)主鍵的時(shí)候一定要是自增的，非常不建議使用UUID作為主鍵。

為什么？因?yàn)?code>UUID是無序的，MySQL在維護(hù)聚簇索引的時(shí)候都是按照主鍵的順序排序的，也就是說每個數(shù)據(jù)頁中的數(shù)據(jù)一定是按照主鍵從小到排序的，而且，數(shù)據(jù)與數(shù)據(jù)之前是通過單向鏈表連接的，上一個數(shù)據(jù)頁中的最大的主鍵的值一定是小于下一個數(shù)據(jù)頁中的最小的主鍵的值，數(shù)據(jù)頁和數(shù)據(jù)頁之間是通過雙向鏈表來維護(hù)的。

我們還是老規(guī)矩，畫個圖幫助大家理解

如果主鍵是自增的，MySQL只需要根據(jù)主鍵目錄能很快的定位到新增的記錄應(yīng)該插入到哪里，如果主鍵不是自增的那么每次都需要從頭開始比較，然后找到合適的位置，再將記錄插入進(jìn)去，這樣真的嚴(yán)重影響效率，所以主鍵的設(shè)計(jì)一定要是自增的。

另外唯一索引和主鍵索引類似，但是唯一索引不一定是自增的，所以維護(hù)唯一索引的成本肯定是大于主鍵索引的。

但是唯一索引的值是唯一的（唯一索引可以有一個值為 NULL），可以更快的通過索引字段來確定一條記錄，但是可能需要進(jìn)行回表查詢（至于什么是回表就不再贅述了，前面文章已經(jīng)詳細(xì)的講解過了）。

為頻繁查詢的字段建立索引

我們在建立索引的時(shí)候，要為那些經(jīng)常作為查詢條件的字段建立索引，這樣能夠提高整個表的查詢速度。

但是查詢條件一般不是一個字段，所以一般是建立的聯(lián)合索引比較多。

另外查詢條件中一般會有l(wèi)ike這樣的模糊查詢，如果是模糊查詢請最好遵守最左前綴查詢原則。

避免為"大字段"建立索引

這個可以換句話說：就是盡量使用數(shù)據(jù)量小的字段作為索引。

舉個例子來說，假設(shè)有兩個這樣的字段，一個是varchar(5),一個是varchar(200)，這種情況下優(yōu)先選擇為varchar(5)的字段建立索引，因?yàn)?code>MySQL在維護(hù)索引的時(shí)候是會將字段值一起維護(hù)的，那這樣必然會導(dǎo)致索引占用更多的空間，另外在排序的時(shí)候需要花費(fèi)更多的時(shí)間去對比。

那假如就要為varchar(100)建立索引呢？那就取部分?jǐn)?shù)據(jù)，例如 address 類型為varchar(200)，在建立索引的時(shí)候可以這么寫：

CREATE INDEX  tbl_address ON dual(address(20));

選擇區(qū)分度大的列作為索引

這又是什么意思？舉個例子相信大家一下子就明白了。

假設(shè)現(xiàn)在有一個"性別"字段，里面存放的數(shù)據(jù)的值要么是男，要么是女，那么這樣的字段很不適合作為索引。

這樣的字段的值的主要特點(diǎn)就是區(qū)分度不夠高，而區(qū)分度不高的字段不適合做索引，為什么呢？

因?yàn)槿绻党霈F(xiàn)的幾率幾乎相等，那么無論搜索哪個值都可能得到一半的數(shù)據(jù)。

在這些情況下，還不如不要索引，因?yàn)镸ySQL他還有一個查詢優(yōu)化器，查詢優(yōu)化器發(fā)現(xiàn)某個值出現(xiàn)在表的數(shù)據(jù)行中的百分比很高的時(shí)候，它一般會忽略索引，進(jìn)行全表掃描。

慣用的百分比界線是"30%"。（匹配的數(shù)據(jù)量超過一定限制的時(shí)候查詢器會放棄使用索引（這也是索引失效的場景之一哦）。

這就是原因。所以看到這里相信大家應(yīng)該知道為什么要盡量避免使用基數(shù)小的字段作為索引了吧。其實(shí)這里涉及到MySQL的一個專有名詞【Cardinality（索引基數(shù)）是mysql索引很重要的一個概念】

盡量為ORDER BY 和 GROUP BY 后面的字段建立索引

將 Order By后面的字段建立索引，這樣在查詢的時(shí)候就不需要再去做一次排序了，因?yàn)槲覀兌家呀?jīng)知道了建立索引之后在B+樹中的記錄都是排序好的。

GROUP BY 和 ORDER BY 其實(shí)是類似，所以將這兩個放在一起說了。

因?yàn)樵?code>GROUP BY 的時(shí)候也要先根據(jù) GROUP BY 后面的字段排序，然后在執(zhí)行聚合操作。

如果 GROUP BY 后面的字段沒有排序，那么這個時(shí)候MySQL是需要先進(jìn)行排序的，這樣就會產(chǎn)生臨時(shí)表，一個排好序的臨時(shí)表，然后再在臨時(shí)表中執(zhí)行聚合操作，這樣子當(dāng)然效率很低了，如果 GROUP BY 后面的字段已經(jīng)建立了索引，那么MySQL 就不需要再去排序，也就不會產(chǎn)生臨時(shí)表。

然而比較坑的是，如果 GROUP BY的列和 ORDER BY的列不一樣，即使都有索引也會產(chǎn)生臨時(shí)表，其實(shí)對于這些情況我網(wǎng)上搜了下好像還很多，這里我給大家列出來，說實(shí)話，這些雖然是標(biāo)準(zhǔn)，但是這個標(biāo)準(zhǔn)好像很難實(shí)現(xiàn)，因?yàn)閷?shí)際的場景肯定沒這么簡單和單純

1. 如果GROUP BY 的列沒有索引,產(chǎn)生臨時(shí)表.
2. 如果GROUP BY時(shí),SELECT的列不止GROUP BY列一個,并且GROUP BY的列不是主鍵 ,產(chǎn)生臨時(shí)表.
3. 如果GROUP BY的列有索引,ORDER BY的列沒索引.產(chǎn)生臨時(shí)表.
4. 如果GROUP BY的列和ORDER BY的列不一樣,即使都有索引也會產(chǎn)生臨時(shí)表.
5. 如果GROUP BY或ORDER BY的列不是來自JOIN語句第一個表.會產(chǎn)生臨時(shí)表.
6. 如果DISTINCT 和 ORDER BY的列沒有索引,產(chǎn)生臨時(shí)表.
7. GROUP BY 和 ORDER BY 的列一樣且是主鍵，但SELECT 列含有除GROUP BY列之外的列，也會產(chǎn)生臨時(shí)表

不要在條件中使用函數(shù)

如果是已經(jīng)建立好的索引的字段在使用的時(shí)候執(zhí)行了函數(shù)操作，那么這個索引就使用不到了。

這是為什么？

因?yàn)?code>MySQL為該索引維護(hù)的B+樹就是基于該字段原始數(shù)據(jù)的，如果正在使用過程中加了函數(shù)，MySQL就不會認(rèn)為這個是原來的字段，那肯定不會走索引了。

但是如果有人就犟，那我就要使用到函數(shù)怎么辦？總不能為了索引而改變業(yè)務(wù)啊？如果是使用MySQL內(nèi)部函數(shù)導(dǎo)致索引失效的，那么在建立索引的時(shí)候可以連著函數(shù)一起創(chuàng)建。

這又是什么意思？假設(shè)有一個字段叫age，并為其創(chuàng)建了索引，但是使用的時(shí)候是這樣子的

SELECT * FROM student WHERE round(age) = 2;

這個時(shí)候索引是使用不到的，那么如果真的非要讓round(age)也走索引，那么你可以這么創(chuàng)建索引

create index stu_age_round on test(round(age)); 

這個時(shí)候在通過上面的方式去查詢，索引就是生效的，相信這個大家是能想明白的。

不要建立太多的索引

因?yàn)镸ySQL維護(hù)索引是需要空間和耗費(fèi)性能的，MySQL會為每個索引字段維護(hù)一顆B+樹。

所以如果索引過多，這無疑是增加了MySQL的負(fù)擔(dān)。

頻繁增刪改的字段不要建立索引

這個就很好理解了，因?yàn)槲覀兦懊嬖缇徒榻B過，字段的變化MySQL是需要重新維護(hù)索引的。

假設(shè)某個字段頻繁修改，那就意味著需要頻繁的重建索引，這必然影響MySQL的性能啊。這里不再多說了。

說到這里大部分說的是所以設(shè)計(jì)的時(shí)候需要注意的一些原則，其實(shí)真正的原則還是需要根據(jù)實(shí)際的業(yè)務(wù)變更的，沒有所謂的“公式”，只要適合自己實(shí)際的業(yè)務(wù)場景的設(shè)計(jì)才是最好的。所以大家也不要過于追求“優(yōu)化”，因?yàn)檫@樣往往會適得其反，畢竟脫離了業(yè)務(wù)談技術(shù)就是在耍流氓。

好了下面我們再來一起重點(diǎn)看看哪些情況下索引會失效。(PS:本文基本全是理論，我想畫圖來表達(dá)，結(jié)果發(fā)現(xiàn)根本無法下手希望大家再堅(jiān)持下，就快完事了。)

索引失效的常見場景

1. 使用 OR 關(guān)鍵字會導(dǎo)致索引失效，不過如果要想使用OR 又不想讓索引失效，那就得需要為or條件中的每個列都建立索引。這很顯然是和上面的不要建立太多的索引相違背。
2. 聯(lián)合索引如果不遵循最左前綴原則，那么索引也將失效
3. 使用模糊查詢的時(shí)候以%開頭也會導(dǎo)致索引失效（這里就不再重復(fù)原因了，因?yàn)榍懊娴奈恼露际钦f過了，這里就是為了幫助大家再會回憶下）
4. 索引列如果使用了隱式轉(zhuǎn)換也會導(dǎo)致索引失效

假設(shè)字段 age 類型為 int，那我們一般是這么查詢的

SELECT * FROM student WHERE age=15

上面這種情況是能使用到索引的，但是如果你這么寫

SELECT * FROM student WHERE age='15'

那這種情況是使用不到索引的，也就是age列情的索引是失效的。

如果字段基數(shù)小也可能會導(dǎo)致索引失效，具體在本文的上面部分已經(jīng)詳細(xì)解釋了，也就是MySQL 查詢優(yōu)化器導(dǎo)致的。

其他的一些原則請大家還是要去看下索引的原理和查詢的基本原則，如果沒有前面的鋪墊，這些看起來似乎有些空洞。所以請大家在索引這一塊一定要循序漸進(jìn)的學(xué)習(xí)，這一塊基本也是我們平時(shí)在使用MySQL時(shí)候的一些核心知識點(diǎn)了。

以上就是跳槽必備之你設(shè)計(jì)索引的原則是什么?怎么避免索引失效?的詳細(xì)內(nèi)容，更多關(guān)于設(shè)計(jì)索引的原則的資料請關(guān)注腳本之家其它相關(guān)文章！