所謂工欲善其事必先利其器，各位在學習階段一定要要循序漸進的來學習這塊知識，千萬不要眼高手低，一定要不急不躁，争取一個蘿蔔一個坑，學完後能一次性拿下這些知識點，然後再加以運用。

索引的設計要根據 WHERE 條件和 Order By 還有 GROUP BY 後面的字段進行設計。

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後面的字段建立索引，這樣在查詢的時候就不需要再去做一次排序了，因為我們都已經知道了建立索引之後在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:本文基本全是理論，我想畫圖來表達，結果發現根本無法下手希望大家再堅持下，就快完事了。)

1. 使用 OR 關鍵字會導緻索引失效，不過如果要想使用OR 又不想讓索引失效，那就得需要為or條件中的每個列都建立索引。這很顯然是和上面的不要建立太多的索引相違背。
2. 聯合索引如果不遵循最左前綴原則，那麼索引也将失效
3. 使用模糊查詢的時候以%開頭也會導緻索引失效（這裡就不再重複原因了，因為前面的文章都是說過了，這裡就是為了幫助大家再會回憶下）
4. 索引列如果使用了隐式轉換也會導緻索引失效

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

SELECT * FROM student WHERE age=15

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

SELECT * FROM student WHERE age='15'

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

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

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

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!