[[409635]]

本文轉載自微信公眾號「碼上Java」，作者碼上Java。轉載本文請聯系碼上Java公眾號。

前言

上一篇文章中我們一起探討了索引的底層原理及為什么我們常用B+樹作為索引的數據結構。本文我們一起學習一下索引創建的原則，看看什么時候適合創建索引?看看什么時候不適合創建索引?

什么情況下適合創建索引

1. 字段的數值有唯一性的限制，比如用戶名

索引本身可以起到約束的作用，比如唯一索引、主鍵索引都是可以起到唯一性約束的，因此在我們的數據表中，如果某個字段是唯一性的，就可以直接創建唯一性索引，或者主鍵索引。

2. 頻繁作為 WHERE 查詢條件的字段，尤其在數據表大的情況下

在數據量大的情況下，某個字段在 SQL 查詢的 WHERE 條件中經常被使用到，那么就需要給這個字段創建索引了。創建普通索引就可以大幅提升數據查詢的效率。

3. 需要經常 GROUP BY 和 ORDER BY 的列

索引就是讓數據按照某種順序進行存儲或檢索，因此當我們使用 GROUP BY 對數據進行分組查詢，或者使用 ORDER BY 對數據進行排序的時候，就需要對分組或者排序的字段進行索引。

4.UPDATE、DELETE 的 WHERE 條件列，一般也需要創建索引

對數據按照某個條件進行查詢后再進行 UPDATE 或 DELETE 的操作，如果對 WHERE 字段創建了索引，就能大幅提升效率。原理是因為我們需要先根據 WHERE 條件列檢索出來這條記錄，然后再對它進行更新或刪除。如果進行更新的時候，更新的字段是非索引字段，提升的效率會更明顯，這是因為非索引字段更新不需要對索引進行維護。

不過在實際工作中，我們也需要注意平衡，如果索引太多了，在更新數據的時候，如果涉及到索引更新，就會造成負擔。

5.DISTINCT 字段需要創建索引

有時候我們需要對某個字段進行去重，使用 DISTINCT，那么對這個字段創建索引，也會提升查詢效率。

6. 做多表 JOIN 連接操作時，創建索引需要注意以下的原則

連接表的數量盡量不要超過 3 張，因為每增加一張表就相當于增加了一次嵌套的循環，數量級增長會非?？?，嚴重影響查詢的效率。

對 WHERE 條件創建索引，因為 WHERE 才是對數據條件的過濾。如果在數據量非常大的情況下，沒有 WHERE 條件過濾是非?？膳碌?。

對用于連接的字段創建索引，并且該字段在多張表中的類型必須一致。

什么情況適合創建索引

1. 頻繁更新的字段不適合建立索引。

這個是為什么呢?你要知道，索引為什么能夠提高查詢效率呢，那是因為索引在插入數據的時候會對數據進行排序。所以說，如果涉及頻繁更新的字段的話，這個時候就不適合建立索引了。

這里以B+ 樹索引為例(B+ 樹索引是數據庫中最為常見的一種索引數據結構，幾乎所有的關系型數據庫都支持它)，B+ 樹在插入時就要對數據進行排序，其實排序的開銷成本并沒有多大，那是因為排序是 CPU 操作(當前一個時鐘周期 CPU 能處理上億指令)。如果面對數據順序或者逆序插入的時候還好，這個時候B+ 樹索引維護成本比較低，因為葉子節點都是從左往右進行插入操作，比如自增 ID 的插入、時間的插入(若在自增 ID 上創建索引，時間列上創建索引，則 B+ 樹插入通常是比較快的)。

但是如果插入的數據是無序的，B+ 樹為了維護排序，需要對頁進行分裂、旋轉等開銷較大的操作，另外，即便對于固態硬盤，隨機寫的性能也不如順序寫，所以磁盤性能也會收到較大影響。比如用戶昵稱，每個用戶注冊時，昵稱大多都是隨意取的，如果在昵稱上創建索引，插入是無序的，索引維護需要的開銷會比較大。

2. WHERE條件中用不到的字段不適合建立索引。

這個是為什么呢?索引的價值是快速定位，如果起不到定位的字段通常是不需要創建索引的。

3. 數據比較少的情況不適合建立索引。

這個是為什么呢?這個很簡單，那是因為在數據量不大的情況下，沒必要用索引，全表掃描的速度已經很快了，即使你用了索引，索引就發揮不出作用了，沒卵用。

4. 唯一性太差的字段不適合建立索引。

這個是為什么呢?這個也很簡單，你可以想象一種非常極端的情況，假設數據表中的數據都一樣(不可能)，你使用索引也一樣沒卵用。

我們可以以性別這個字段為例，性別無外乎兩種可能，男的或者女的(這里別有杠精哈)，因為你訪問索引需要付出額外的IO開銷，你從索引中拿到的只是地址(記住只是地址哦)，要想真正訪問到數據還是要對表進行一次IO。假如你要從表的100萬行數據中取幾個數據，那么利用索引迅速定位，訪問索引的這IO開銷也是非常值了。但如果你是從100萬行數據中取50萬行數據，就比如性別字段吧，那你相對需要訪問50萬次索引，再訪問50萬次表，加起來的開銷可想而知。并不會比直接對表進行一次完整掃描小。所以即使你用了索引，沒卵用。

5. 參與列計算的列不適合建索引。

這個是為什么呢?這個是因為，如果索引列上有函數運算的話，會導致不走索引，也就是索引失效了 。

舉個例子，比如你在在T表上有一個索引Y，但是你的查詢語句是這樣子SELECT * FROM T WHERE FUN(Y) = XXX。這個時候索引也不會被用到，因為你要查詢的列中所有的行都需要被計算一遍。

總結

今天我們一起索引使用相關原則進行了簡單梳理，要記住：索引是能夠提升SQL 查詢的效率，但是索引不是萬能的，一定要遵守基本原則。