[[409635]]

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

前言

上一篇文章中我們一起探討了索引的底層原理及為什么我們常用B+樹(shù)作為索引的數(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 對(duì)數(shù)據(jù)進(jìn)行分組查詢，或者使用 ORDER BY 對(duì)數(shù)據(jù)進(jìn)行排序的時(shí)候，就需要對(duì)分組或者排序的字段進(jìn)行索引。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

總結(jié)

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