1.適合創(chuàng)建索引的情況

1、字段的數(shù)值有唯一性的限制

2、頻繁作為 WHERE 查詢(xún)條件的字段

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

比如 student_info 數(shù)據(jù)表（含 100 萬(wàn)條數(shù)據(jù)），假設(shè)我們想要查詢(xún) student_id=123110 的用戶(hù)信息。

①查看student_info表中的索引

可以看出，我們沒(méi)有對(duì)student_id字段創(chuàng)建索引。

②進(jìn)行如下查詢(xún)，耗時(shí)220ms

③添加索引

alter table student_info add index idx_sid(student_id);

④再查詢(xún)。耗時(shí)0ms。性能提升很大！

3、經(jīng)常 GROUP BY 和 ORDER BY 的列

????????索引其實(shí)就是讓數(shù)據(jù)按照某種順序進(jìn)行存儲(chǔ)或檢索。當(dāng)我們使用 GROUP BY 對(duì)數(shù)據(jù)進(jìn)行分組查詢(xún)，或者使用 ORDER BY 對(duì)數(shù)據(jù)進(jìn)行排序的時(shí)候，如果 對(duì)分組或者排序的字段建立索引，本身索引的數(shù)據(jù)就已經(jīng)排好序了，進(jìn)行分組查詢(xún)和排序操作性能不好嗎？另外，如果待排序的列有多個(gè)，那么可以在這些列上建立 組合索引 。

使用GROUP BY和ORDER BY，先看看不加索引的情況：

沒(méi)有添加索引，大概執(zhí)行時(shí)間900ms

加上索引

SELECT student_id,COUNT(*) AS num FROM student_info
GROUP BY student_id
LIMIT 100;

執(zhí)行時(shí)間只要40ms，大概相差20多倍！

---

再考慮復(fù)雜一點(diǎn)的情況，group by和order by一起使用

????????這是只給student_id和create_time兩列單獨(dú)添加單列索引的情況?？梢钥吹讲樵?xún)速度很慢，要5s多。

ALTER TABLE student_info ADD INDEX idx_sid_cre_time(student_id,create_time DESC);

添加聯(lián)合索引

使用聯(lián)合索引只需要耗時(shí)0.25ms！

注意：

????????如果我們僅僅使用GROUP BY 或者 ORDER BY，且后面只有一個(gè)字段，則單獨(dú)建立索引；如果后面跟多個(gè)字段，則建立聯(lián)合索引。如果既有GROUP BY 又有 ORDER BY，那就建立聯(lián)合索引，且GROUP BY的字段寫(xiě)在(聯(lián)合索引的)前面，ORDER BY的字段寫(xiě)在(聯(lián)合索引的)后面。8.0后的版本也可以考慮使用降序索引。

---

4、UPDATE、DELETE 的 WHERE 條件列

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

 UPDATE student_info SET student_id = 10002WHERE NAME = '462eed7ac6e791292a79';

---

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

????????有時(shí)候我們需要對(duì)某個(gè)字段進(jìn)行去重，使用?DISTINCT，那么對(duì)這個(gè)字段創(chuàng)建索引，也會(huì)提升查詢(xún)效率。創(chuàng)建索引以后，在B+樹(shù)種相同的字段就會(huì)在一起，這樣我們DISTINCT去重的時(shí)候，速度就會(huì)快很多。

比如，我們想要查詢(xún)課程表中不同的 student_id 都有哪些，如果我們沒(méi)有對(duì) student_id 創(chuàng)建索引，執(zhí)行 SQL 語(yǔ)句：

SELECT DISTINCT(student_id) FROM `student_info`; 

運(yùn)行結(jié)果（600637 條記錄，運(yùn)行時(shí)間 0.683s ）

如果我們對(duì) student_id 創(chuàng)建索引，再執(zhí)行 SQL 語(yǔ)句:

SELECT DISTINCT(student_id) FROM `student_info`;

運(yùn)行結(jié)果（600637 條記錄，運(yùn)行時(shí)間0.010s?）
你能看到 SQL 查詢(xún)效率有了提升，同時(shí)顯示出來(lái)的 student_id 還是按照?遞增的順序?進(jìn)行展示的。這是因?yàn)樗饕龝?huì)對(duì)數(shù)據(jù)按照某種順序進(jìn)行排序，所以在去重的時(shí)候也會(huì)快很多。

---

6、多表 JOIN 連接操作時(shí)，創(chuàng)建索引注意事項(xiàng)

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

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

????????注意：對(duì)于用連接的字段創(chuàng)建索引，這些字段在多張表中的 類(lèi)型必須一致 。比如 course_id 在student_info 表和 course 表中都為 int(11) 類(lèi)型，而不能一個(gè)為 int 另一個(gè)為 varchar 類(lèi)型。否則在查詢(xún)時(shí)，雖然也會(huì)幫我們進(jìn)行隱式的類(lèi)型轉(zhuǎn)換，轉(zhuǎn)換時(shí)會(huì)使用函數(shù)，但會(huì)導(dǎo)致索引失效。

可以看到，沒(méi)有使用索引后，多表連接查詢(xún)效率高了兩百多倍！