大家都知道，數(shù)據(jù)庫(kù)中使用索引，進(jìn)行檢索數(shù)據(jù)的話，那么就會(huì)大幅度的提升你的查詢效率，原本可能需要三秒甚至四秒左右的查詢SQL，增加索引之后，會(huì)可以能讓查詢速率至少提升百分之30，那么加索引怎么才能如何讓自己的查詢命中索引呢？又應(yīng)該怎么去給自己的表結(jié)構(gòu)建立索引呢？這才是阿粉想要講的事情。

索引失效

我們?cè)谌粘ｉ_(kāi)發(fā)的時(shí)候，很多時(shí)候都會(huì)在創(chuàng)建表完成之后，給這個(gè)對(duì)應(yīng)的表建立上一個(gè)索引，而這個(gè)索引的定義呢，一般也是根據(jù)自己的業(yè)務(wù)需求來(lái)的，但是有些雖然根據(jù)自己的業(yè)務(wù)需求弄好了之后，發(fā)現(xiàn)有些查詢明明自己感覺(jué)都運(yùn)用了索引，但是最終卻發(fā)現(xiàn)自己的索引失效了。

那么引發(fā)索引失效，都有哪些騷操作呢？

實(shí)際上就是七個(gè)字，模 型 數(shù) 空 運(yùn) 最 快

模：模糊查詢的意思。like的模糊查詢以%開(kāi)頭，索引失效。比如：

SELECT * FROM user? WHERE name LIKE '%極客技術(shù)';

型：代表數(shù)據(jù)類型。類型錯(cuò)誤，如字段類型為varchar，where條件用number，索引也會(huì)失效。比如：

SELECT * FROM user WHERE height= 10;

height為varchar類型導(dǎo)致索引失效。

數(shù)：是函數(shù)的意思。對(duì)索引的字段使用內(nèi)部函數(shù)，索引也會(huì)失效。這種情況下應(yīng)該建立基于函數(shù)的索引。比如：

SELECT * FROM user WHERE DATE(create_time) = '2020-09-03';

create_time字段設(shè)置索引，那就無(wú)法使用函數(shù)，否則索引失效。

空：是Null的意思。索引不存儲(chǔ)空值，如果不限制索引列是not null，數(shù)據(jù)庫(kù)會(huì)認(rèn)為索引列有可能存在空值，所以不會(huì)按照索引進(jìn)行計(jì)算。比如：

SELECT * FROM user WHERE address IS NULL不走索引。

SELECT * FROM user WHERE address IS NOT NULL;走索引。

建議大家這設(shè)計(jì)字段的時(shí)候，如果沒(méi)有必要的要求必須為NULL，那么最好給個(gè)默認(rèn)值空字符串，這可以解決很多后續(xù)的麻煩（切記）。

運(yùn)：是運(yùn)算的意思。對(duì)索引列進(jìn)行（+，-，*，/，!, ！=, <>）等運(yùn)算，會(huì)導(dǎo)致索引失效。比如：

SELECT * FROM user WHERE age - 1 = 20;

最：是最左原則。在復(fù)合索引中索引列的順序至關(guān)重要。如果不是按照索引的最左列開(kāi)始查找，則無(wú)法使用索引。

快：全表掃描更快的意思。如果數(shù)據(jù)庫(kù)預(yù)計(jì)使用全表掃描要比使用索引快，則不使用索引。

如何建立索引呢？

這個(gè)時(shí)候，如果面試官問(wèn)你的時(shí)候，說(shuō)，如何建立索引，就是建立索引的規(guī)范的時(shí)候，你應(yīng)該怎么回答呢？

其實(shí)這就是問(wèn)你，你在設(shè)計(jì)表的時(shí)候，怎么去設(shè)計(jì)表里面的索引比較合適呢？阿粉列出幾個(gè)：

1.經(jīng)常與其他表進(jìn)行連接的表，在連接字段上應(yīng)該建立索引

也就是在關(guān)聯(lián)條件上面，建立索引，比如a.id = b.aid

a表的id，是主鍵，而這時(shí)候，我們就需要把b表的對(duì)應(yīng)a表的id建立一個(gè)索引，這樣在使用關(guān)聯(lián)查詢的時(shí)候，能夠命中索引。

2.經(jīng)常出現(xiàn)在Where子句中的字段，特別是大表的字段，應(yīng)該建立索引

3.索引應(yīng)該建在小字段上，對(duì)于大的文本字段甚至超長(zhǎng)字段，不要建索引

4.頻繁進(jìn)行數(shù)據(jù)操作的表，不要建立太多的索引

5.在經(jīng)常需要排序的列上創(chuàng)建索引

6.為經(jīng)常出現(xiàn)在關(guān)鍵字order by、group by、distinct后面的字段，建立索引。

哪些字段不適合去建立索引

這些字段是索引應(yīng)該建立的在什么字段上，那么什么樣的表字段，不適合去建索引呢？

1.對(duì)于那些在查詢中很少使用或者參考的列不應(yīng)該創(chuàng)建索引

2.不要在有大量相同取值的字段上，建立索引

3.當(dāng)修改性能遠(yuǎn)遠(yuǎn)大于檢索性能時(shí)，不應(yīng)該創(chuàng)建索引。這是因?yàn)?，修改性能和檢索性能是互相矛盾的。當(dāng)增加索引時(shí)，會(huì)提高檢索性能，但是會(huì)降低修改性能。當(dāng)減少索引時(shí)，會(huì)提高修改性能，降低檢索性能。因此，當(dāng)修改性能遠(yuǎn)遠(yuǎn)大于檢索性能時(shí)，不應(yīng)該創(chuàng)建索引。

所以在我們創(chuàng)建表的時(shí)候，適當(dāng)?shù)乃饕軌蚣涌煳覀兊牟樵兯俣龋贿m當(dāng)?shù)乃饕?，反而?duì)我們的表，有害而無(wú)益。你學(xué)會(huì)了么？