一、先闡述下單列索引和組合索引的概念
單列索引：即一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單列索引，但這不是組合索引。
組合索引：即一個(gè)索包含多個(gè)列。
如果我們的查詢where條件只有一個(gè)，我們完全可以用單列索引，這樣的查詢速度較快，索引也比較瘦身。如果我們的業(yè)務(wù)場(chǎng)景是需要經(jīng)常查詢多個(gè)組合列，不要試圖分別基于單個(gè)列建立多個(gè)單列索引（因?yàn)殡m然有多個(gè)單列索引，但是MySQL只能用到其中的那個(gè)它認(rèn)為似乎最有效率的單列索引）。這是因?yàn)楫?dāng)SQL語(yǔ)句所查詢的列，全部都出現(xiàn)在復(fù)合索引中時(shí)，此時(shí)由于只需要查詢索引塊即可獲得所有數(shù)據(jù)，當(dāng)然比使用多個(gè)單列索引要快得多。下面以實(shí)際例子說(shuō)明：
舉例：
以下是代碼片段：
CREATE TABLE people (
peopleid SMALLINT NOT NULL AUTO_INCREMENT,
firstname CHAR(50) NOT NULL,
lastname CHAR(50) NOT NULL,
age SMALLINT NOT NULL,
townid SMALLINT NOT NULL,
PRIMARY KEY (peopleid)
);
下面是我們插入到這個(gè)people表的數(shù)據(jù)：
這個(gè)數(shù)據(jù)片段中有四個(gè)名字為“Mikes”的人(其中兩個(gè)姓Sullivans，兩個(gè)姓McConnells)，有兩個(gè)年齡為17歲的人，還有一個(gè)名字與眾不同的Joe Smith。
這個(gè)表的主要用途是根據(jù)指定的用戶姓、名以及年齡返回相應(yīng)的peopleid。例如，我們可能需要查找姓名為Mike Sullivan、年齡17歲用戶的peopleid(SQL命令為SELECT peopleid FROM people WHERE firstname="Mike" AND lastname="Sullivan" AND age=17;)。由于我們不想讓MySQL每次執(zhí)行查詢就去掃描整個(gè)表，這里需要考慮運(yùn)用索引。
首先，我們可以考慮在單個(gè)列上創(chuàng)建索引，比如firstname、lastname或者age列。如果我們創(chuàng)建firstname列的索引(ALTER TABLE people ADD INDEX firstname (firstname);)，MySQL將通過(guò)這個(gè)索引迅速把搜索范圍限制到那些firstname="Mike"的記錄，然后再在這個(gè)“中間結(jié)果集”上進(jìn)行其他條件的搜索：它首先排除那些lastname不等于“Sullivan”的記錄，然后排除那些age不等于17的記錄。當(dāng)記錄滿足所有搜索條件之后，MySQL就返回最終的搜索結(jié)果。
由于建立了firstname列的索引，與執(zhí)行表的完全掃描相比，MySQL的效率提高了很多，但我們要求MySQL掃描的記錄數(shù)量仍舊遠(yuǎn)遠(yuǎn)超過(guò)了實(shí)際所需要的。雖然我們可以刪除firstname列上的索引，再創(chuàng)建lastname或者age列的索引，但總地看來(lái)，不論在哪個(gè)列上創(chuàng)建索引搜索效率仍舊相似。
為了提高搜索效率，我們需要考慮運(yùn)用多列索引。如果為firstname、lastname和age這三個(gè)列創(chuàng)建一個(gè)多列索引，MySQL只需一次檢索就能夠找出正確的結(jié)果!下面是創(chuàng)建這個(gè)多列索引的SQL命令：
以下是代碼片段：
ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age);
由于索引文件以B+樹(shù)格式保存，MySQL能夠立即轉(zhuǎn)到合適的firstname，然后再轉(zhuǎn)到合適的lastname，最后轉(zhuǎn)到合適的age。在沒(méi)有掃描數(shù)據(jù)文件任何一個(gè)記錄的情況下，MySQL就正確地找出了搜索的目標(biāo)記錄!
那么，如果在firstname、lastname、age這三個(gè)列上分別創(chuàng)建單列索引，效果是否和創(chuàng)建一個(gè)firstname、lastname、age的多列索引一樣呢？答案是否定的，兩者完全不同。當(dāng)我們執(zhí)行查詢的時(shí)候，MySQL只能使用一個(gè)索引。如果你有三個(gè)單列的索引，MySQL會(huì)試圖選擇一個(gè)限制最嚴(yán)格的索引。但是，即使是限制最嚴(yán)格的單列索引，它的限制能力也肯定遠(yuǎn)遠(yuǎn)低于firstname、lastname、age這三個(gè)列上的多列索引。
二、謹(jǐn)防最左前綴索引失效問(wèn)題
繼續(xù)考慮前面的例子，現(xiàn)在我們有一個(gè)firstname、lastname、age列上的多列索引，我們稱這個(gè)索引為fname_lname_age。它相當(dāng)于我們創(chuàng)建了(firstname，lastname，age)、(firstname，lastname)以及(firstname)這些列組合上的索引。為什么沒(méi)有 (lastname,age)等這樣的組合索引呢？這是因?yàn)?mysql 組合索引"最左前綴"（Leftmost Prefixing）的結(jié)果。簡(jiǎn)單的理解就是只從最左面的開(kāi)始組合。并不是只要包含這三列的查詢都會(huì)用到該組合索引。
以下是代碼片段：
# The following queries can use the Leftmost Prefixing index:
SELECT peopleid FROM people WHERE firstname="Mike" AND lastname="Sullivan" AND age="17";
SELECT peopleid FROM people WHERE firstname="Mike" AND lastname="Sullivan";
SELECT peopleid FROM people WHERE firstname="Mike";
# The following queries cannot use the Leftmost Prefixing index at all:
SELECT peopleid FROM people WHERE lastname="Sullivan";
SELECT peopleid FROM people WHERE age="17";
SELECT peopleid FROM people WHERE lastname="Sullivan" AND age="17";
這里我特地實(shí)踐了下：
[圖片上傳失敗...(image-fd474e-1583674549539)]
執(zhí)行結(jié)果如下：
[圖片上傳失敗...(image-58fad5-1583674549539)]
可以看出這里最左前綴索引失效了。這里沒(méi)有用到索引直接全表掃描了。
我們?cè)倏聪氯绻@樣呢？
[圖片上傳失敗...(image-e57d2c-1583674549539)]
執(zhí)行結(jié)果如下：
[圖片上傳失敗...(image-81c3ae-1583674549540)]
可見(jiàn)還是用到了索引，這里只是最左前綴索引失效，但是不代表整個(gè)索引失效，只是效率沒(méi)有那么高了。最左前綴索引的效率是比較高的。本來(lái)我誤以為只要第一個(gè)查詢字段不是組合索引的最左前綴索引字段整個(gè)索引會(huì)失效，其實(shí)不然。這里強(qiáng)調(diào)下只是最有效率的最左前綴索引失效不是整個(gè)索引失效。
三、使用explain分析索引
在不確定應(yīng)該在哪些數(shù)據(jù)列上創(chuàng)建索引的時(shí)候，我們可以從EXPLAIN SELECT命令那里往往可以獲得一些幫助。這其實(shí)只是簡(jiǎn)單地給一條普通的SELECT命令加一個(gè)EXPLAIN關(guān)鍵字作為前綴而已。有了這個(gè)關(guān)鍵字，MySQL將不是去執(zhí)行那條SELECT命令，而是去對(duì)它進(jìn)行分析。MySQL將以表格的形式把查詢的執(zhí)行過(guò)程和用到的索引(如果有的話)等信息列出來(lái)。這里我基本闡述下每個(gè)信息字段含義，不展開(kāi)闡述，我們只要注意幾個(gè)關(guān)鍵點(diǎn)（關(guān)鍵點(diǎn)以下用紅色加粗顯示）能大概看懂即可呵呵~~
1、id:SQL執(zhí)行的順序的標(biāo)識(shí)。
[圖片上傳失敗...(image-6acea1-1583674549539)]
sql從里向外執(zhí)行，通過(guò)以上觀察發(fā)現(xiàn)sql是按照id從大到小執(zhí)行的。
2、select_type: select類型
1)、SIMPLE(不使用UNION或子查詢等)
[圖片上傳失敗...(image-532c25-1583674549539)]
- 、PRIMARY：最外層的select
[圖片上傳失敗...(image-b19c45-1583674549538)]
3）、DERIVED：派生表的SELECT(FROM子句的子查詢)
[圖片上傳失敗...(image-8a6b8d-1583674549539)]
[圖片上傳失敗...(image-c3eff2-1583674549539)]
4）、UNION：UNION中的第二個(gè)或后面的SELECT語(yǔ)句
[圖片上傳失敗...(image-2138e8-1583674549539)]
5）、UNION RESULT：UNION的結(jié)果。
[圖片上傳失敗...(image-912683-1583674549539)]
[圖片上傳失敗...(image-b3c4ba-1583674549539)]
6）、DEPENDENT UNION：UNION中的第二個(gè)或后面的SELECT語(yǔ)句，取決于外面的查詢
[圖片上傳失敗...(image-dc22ed-1583674549539)]
7）、SUBQUERY：子查詢中的第一個(gè)SELECT
[圖片上傳失敗...(image-6de717-1583674549538)]
8）、DEPENDENT SUBQUERY：子查詢中的第一個(gè)SELECT，取決于外面的查詢
[圖片上傳失敗...(image-561a4e-1583674549538)]
3、table：表的名字。
有時(shí)不是真實(shí)的表名字,看到的是derivedx(x是個(gè)數(shù)字,我的理解是第幾步執(zhí)行的結(jié)果)
4、type：連接操作的類型。
這列很重要,顯示了連接使用了哪種類別,有無(wú)使用索引。在各種類型的關(guān)聯(lián)關(guān)系當(dāng)中，效率最高的是system，然后依次是const、eq_ref、ref、range、index和 All。一般來(lái)說(shuō)，得保證查詢至少達(dá)到range級(jí)別，最好能達(dá)到ref，否則就可能會(huì)出現(xiàn)性能問(wèn)題。
1）、system
表只有一行：system表。這是const連接類型的特殊情況
2）、const
表中的一個(gè)記錄的最大值能夠匹配這個(gè)查詢（索引可以是主鍵或惟一索引）。因?yàn)橹挥幸恍校@個(gè)值實(shí)際就是常數(shù)，因?yàn)镸YSQL先讀這個(gè)值然后把它當(dāng)做常數(shù)來(lái)對(duì)待
3）、eq_ref
在連接中，MYSQL在查詢時(shí)，從前面的表中，對(duì)每一個(gè)記錄的聯(lián)合都從表中讀取一個(gè)記錄，它在查詢使用了索引為主鍵或惟一鍵的全部時(shí)使用
4）、ref
這個(gè)連接類型只有在查詢使用了不是惟一或主鍵的鍵或者是這些類型的部分（比如，利用最左邊前綴）時(shí)發(fā)生。對(duì)于之前的表的每一個(gè)行聯(lián)合，全部記錄都將從表中讀出。這個(gè)類型嚴(yán)重依賴于根據(jù)索引匹配的記錄多少（越少越好）
5）、range
這個(gè)連接類型使用索引返回一個(gè)范圍中的行，比如使用>或<查找東西時(shí)發(fā)生的情況
6）、index
這個(gè)連接類型對(duì)前面的表中的每一個(gè)記錄聯(lián)合進(jìn)行完全掃描（比ALL更好，因?yàn)樗饕话阈∮诒頂?shù)據(jù)）
7）、ALL
這個(gè)連接類型對(duì)于前面的每一個(gè)記錄聯(lián)合進(jìn)行完全掃描，這一般比較糟糕，應(yīng)該盡量避免。因?yàn)樗獟呙枵麄€(gè)表。你可以加入更多的索引來(lái)解決這個(gè)問(wèn)題。
5、possible_key：MySQL在搜索數(shù)據(jù)記錄時(shí)可以選用的各個(gè)索引名。
這里的索引名是創(chuàng)建索引時(shí)指定的索引昵稱;如果索引沒(méi)有昵稱，則默認(rèn)顯示的是索引中第一個(gè)列的名字(在上一節(jié)舉的例子中是“firstname”)。默認(rèn)索引名字的含義往往不是很明顯。
6、key：它顯示了MySQL實(shí)際使用的索引。
key數(shù)據(jù)列是MySQL實(shí)際選用的索引，如果它為空(或NULL)，則MySQL不使用索引。
7、key_len：索引中被使用部分的長(zhǎng)度，以字節(jié)計(jì)。
key_len的值可以告訴你在聯(lián)合索引中mysql會(huì)真正使用了哪些索引。 在上例中，key_len是102，其中firstname占50字節(jié)，lastname占50字節(jié)，age占2字節(jié)（smallint存儲(chǔ)大小為2字節(jié)）。如果MySQL只使用索引中的firstname部分，則key_len將是50。 在不損失精確性的情況下 ，key_len數(shù)據(jù)列里的值越小越好(意思是更快)。
8、ref：顯示使用哪個(gè)列或常數(shù)與key一起從表中選擇行。
ref數(shù)據(jù)列給出了關(guān)聯(lián)關(guān)系中另一個(gè)數(shù)據(jù)表里的數(shù)據(jù)列的名字。
9、rows：MySQL所認(rèn)為的它在找到正確的結(jié)果之前必須掃描的記錄數(shù)。
顯然，這里最理想的數(shù)字就是1。
10、extra：附加信息
Using index和Using where會(huì)遇到的比較多，可以重點(diǎn)記下，其他的我沒(méi)怎么遇到過(guò)了解即可，遇到具體問(wèn)題可以查閱哈
1）、Distinct
一旦MYSQL找到了與行相聯(lián)合匹配的行，就不再搜索了
2）、Not exists
MYSQL優(yōu)化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標(biāo)準(zhǔn)的行，就不再搜索了
3）、Range checked for each
沒(méi)有找到理想的索引，因此對(duì)于從前面表中來(lái)的每一個(gè)行組合，MYSQL檢查使用哪個(gè)索引，并用它來(lái)從表中返回行。這是使用索引的最慢的連接之一
4）、Using filesort
看到這個(gè)的時(shí)候，查詢就需要優(yōu)化了。MYSQL需要進(jìn)行額外的步驟來(lái)發(fā)現(xiàn)如何對(duì)返回的行排序。它根據(jù)連接類型以及存儲(chǔ)排序鍵值和匹配條件的全部行的行指針來(lái)排序全部行
5）Using index
列數(shù)據(jù)是從僅僅使用了索引中的信息而沒(méi)有讀取實(shí)際的行動(dòng)的表返回的，這發(fā)生在對(duì)表的全部的請(qǐng)求列都是同一個(gè)索引的部分的時(shí)候
6）Using temporary
看到這個(gè)的時(shí)候，查詢需要優(yōu)化了。這里，MYSQL需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來(lái)存儲(chǔ)結(jié)果，這通常發(fā)生在對(duì)不同的列集進(jìn)行ORDER BY上，而不是GROUP BY上
7）Using where
使用了WHERE從句來(lái)限制哪些行將與下一張表匹配或者是返回給用戶。如果不想返回表中的全部行，并且連接類型ALL或index，這就會(huì)發(fā)生，或者是查詢有問(wèn)題
-- 轉(zhuǎn)載自：
https://blog.csdn.net/xtdhqdhq/article/details/17582779
