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

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

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

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

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

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

主鍵索引

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

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

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

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

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

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

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

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

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

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

另外查詢條件中一般會有like這樣的模糊查詢，如果是模糊查詢請最好遵守最左前綴查詢原則。

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

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

舉個例子來說，假設有兩個這樣的字段，一個是varchar(5),一個是varchar(200)，這種情況下優先選擇為varchar(5)的字段建立索引，因為MySQL在維護索引的時候是會將字段值一起維護的，那這樣必然會導致索引占用更多的空間，另外在排序的時候需要花費更多的時間去對比。

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

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

選擇區分度大的列作為索引

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

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

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

因為如果值出現的幾率幾乎相等，那么無論搜索哪個值都可能得到一半的數據。

在這些情況下，還不如不要索引，因為MySQL他還有一個查詢優化器，查詢優化器發現某個值出現在表的數據行中的百分比很高的時候，它一般會忽略索引，進行全表掃描。

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

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

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

將 Order By后面的字段建立索引，這樣在查詢的時候就不需要再去做一次排序了，因為我們都已經知道了建立索引之后在B+樹中的記錄都是排序好的。

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

因為在GROUP BY 的時候也要先根據 GROUP BY 后面的字段排序，然后在執行聚合操作。

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

然而比較坑的是，如果 GROUP BY的列和 ORDER BY的列不一樣，即使都有索引也會產生臨時表，其實對于這些情況我網上搜了下好像還很多，這里我給大家列出來，說實話，這些雖然是標準，但是這個標準好像很難實現，因為實際的場景肯定沒這么簡單和單純

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

不要在條件中使用函數

如果是已經建立好的索引的字段在使用的時候執行了函數操作，那么這個索引就使用不到了。

這是為什么？

因為MySQL為該索引維護的B+樹就是基于該字段原始數據的，如果正在使用過程中加了函數，MySQL就不會認為這個是原來的字段，那肯定不會走索引了。

但是如果有人就犟，那我就要使用到函數怎么辦？總不能為了索引而改變業務啊？如果是使用MySQL內部函數導致索引失效的，那么在建立索引的時候可以連著函數一起創建。

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

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

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

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

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

不要建立太多的索引

因為MySQL維護索引是需要空間和耗費性能的，MySQL會為每個索引字段維護一顆B+樹。

所以如果索引過多，這無疑是增加了MySQL的負擔。

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

這個就很好理解了，因為我們前面早就介紹過，字段的變化MySQL是需要重新維護索引的。

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

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

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

索引失效的常見場景

使用 OR 關鍵字會導致索引失效，不過如果要想使用OR 又不想讓索引失效，那就得需要為or條件中的每個列都建立索引。這很顯然是和上面的不要建立太多的索引相違背。

聯合索引如果不遵循最左前綴原則，那么索引也將失效

使用模糊查詢的時候以%開頭也會導致索引失效（這里就不再重復原因了，因為前面的文章都是說過了，這里就是為了幫助大家再會回憶下）

索引列如果使用了隱式轉換也會導致索引失效

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

SELECT * FROM student WHERE age=15

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

SELECT * FROM student WHERE age='15'

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

如果字段基數小也可能會導致索引失效，具體在本文的上面部分已經詳細解釋了，也就是MySQL 查詢優化器導致的。

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

總結

以上是生活随笔為你收集整理的我面试几乎必问：你设计索引的原则是什么？怎么避免索引失效？的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。