[[409635]]

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

前言

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

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

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

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

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

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

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

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

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

對數(shù)據(jù)按照某個(gè)條件進(jìn)行查詢后再進(jìn)行 UPDATE 或 DELETE 的操作，如果對 WHERE 字段創(chuàng)建了索引，就能大幅提升效率。原理是因?yàn)槲覀冃枰雀鶕?jù) WHERE 條件列檢索出來這條記錄，然后再對它進(jìn)行更新或刪除。如果進(jìn)行更新的時(shí)候，更新的字段是非索引字段，提升的效率會(huì)更明顯，這是因?yàn)榉撬饕侄胃虏恍枰獙λ饕M(jìn)行維護(hù)。

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

5.DISTINCT 字段需要?jiǎng)?chuàng)建索引

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

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

連接表的數(shù)量盡量不要超過 3 張，因?yàn)槊吭黾右粡埍砭拖喈?dāng)于增加了一次嵌套的循環(huán)，數(shù)量級(jí)增長會(huì)非常快，嚴(yán)重影響查詢的效率。

對 WHERE 條件創(chuàng)建索引，因?yàn)?WHERE 才是對數(shù)據(jù)條件的過濾。如果在數(shù)據(jù)量非常大的情況下，沒有 WHERE 條件過濾是非?？膳碌摹?/p>

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

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

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

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

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

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

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

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

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

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

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

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

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

5. 參與列計(jì)算的列不適合建索引。

這個(gè)是為什么呢?這個(gè)是因?yàn)椋绻饕猩嫌泻瘮?shù)運(yùn)算的話，會(huì)導(dǎo)致不走索引，也就是索引失效了 。

舉個(gè)例子，比如你在在T表上有一個(gè)索引Y，但是你的查詢語句是這樣子SELECT * FROM T WHERE FUN(Y) = XXX。這個(gè)時(shí)候索引也不會(huì)被用到，因?yàn)槟阋樵兊牧兄兴械男卸夹枰挥?jì)算一遍。

總結(jié)

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