[[409635]]

本文轉(zhuǎn)載自微信公眾號「碼上Java」，作者碼上Java。轉(zhuǎn)載本文請聯(lián)系碼上Java公眾號。

前言

上一篇文章中我們一起探討了索引的底層原理及為什么我們常用B+樹作為索引的數(shù)據(jù)結(jié)構(gòu)。本文我們一起學(xué)習(xí)一下索引創(chuàng)建的原則，看看什么時候適合創(chuàng)建索引?看看什么時候不適合創(chuàng)建索引?

什么情況下適合創(chuàng)建索引

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

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

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

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

3. 需要經(jīng)常 GROUP BY 和 ORDER BY 的列

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

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

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

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

5.DISTINCT 字段需要創(chuàng)建索引

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

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

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

對 WHERE 條件創(chuàng)建索引，因為 WHERE 才是對數(shù)據(jù)條件的過濾。如果在數(shù)據(jù)量非常大的情況下，沒有 WHERE 條件過濾是非常可怕的。

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

什么情況適合創(chuàng)建索引

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

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

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

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

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

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

3. 數(shù)據(jù)比較少的情況不適合建立索引。

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

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

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

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

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

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

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

總結(jié)

今天我們一起索引使用相關(guān)原則進(jìn)行了簡單梳理，要記?。核饕悄軌蛱嵘齋QL 查詢的效率，但是索引不是萬能的，一定要遵守基本原則。