1 什么是索引

索引可加快檢索的速度囚聚，提升查詢(xún)性能跨晴，當(dāng)前關(guān)系型數(shù)據(jù)庫(kù)普遍采用的B+樹(shù)索引，此索引是一種按字段排序的樹(shù)形目錄結(jié)構(gòu)根吁，主要用于提升查詢(xún)性能和唯一約束支持员淫。它的內(nèi)容包括根節(jié)點(diǎn)、分支節(jié)點(diǎn)和葉子節(jié)點(diǎn)击敌。根節(jié)點(diǎn)和分支節(jié)點(diǎn)主要保存了索引樹(shù)的順序以及各層級(jí)間引用關(guān)系介返；葉子節(jié)點(diǎn)保存索引字段內(nèi)容（針對(duì)聚集索引存儲(chǔ)的是實(shí)際數(shù)據(jù)，針對(duì)非聚集索引沃斤，存儲(chǔ)的是聚集索引葉子節(jié)點(diǎn)的地址）圣蝎。

索引的創(chuàng)建就是為了提升查詢(xún)性能，索引就是把查詢(xún)語(yǔ)句所需要的少量數(shù)據(jù)記錄到索引分頁(yè)中衡瓶，這樣訪問(wèn)數(shù)據(jù)時(shí)徘公，只要訪問(wèn)少量數(shù)據(jù)的索引分頁(yè)就可以了。

• 并不是索引越多越好哮针，建少了关面，用where子句找數(shù)據(jù)效率比較低坦袍，建多了，執(zhí)行增等太、刪捂齐、改操作時(shí)，會(huì)額外的更新索引缩抡，導(dǎo)致性能較慢奠宜，同時(shí)，索引占用了磁盤(pán)空間缝其，過(guò)多的索引也會(huì)導(dǎo)致空間浪費(fèi)挎塌。
• 并不是有索引一定比沒(méi)有索引要好。對(duì)于數(shù)據(jù)量較少的表内边，不需要?jiǎng)?chuàng)建索引榴都，因?yàn)橛兴饕龝r(shí)，系統(tǒng)首先會(huì)去尋找索引頁(yè)漠其，然后再去找實(shí)際的數(shù)據(jù)項(xiàng)嘴高。

2 索引分類(lèi)

索引分為兩大類(lèi)：聚集索引和非聚集索引，其中和屎，非聚集索引又可細(xì)分為普通索引拴驮、唯一索引、組合索引柴信。

2.1 聚集索引

聚集索引規(guī)定了表中數(shù)據(jù)的物理存放順序套啤，數(shù)據(jù)行的物理順序與列值（一般是主鍵的那一列）的邏輯順序相同，一個(gè)表中只能擁有一個(gè)聚集索引随常，可以類(lèi)比于字典中的按拼音目錄潜沦。它的優(yōu)勢(shì)在于：查詢(xún)小數(shù)據(jù)量的表；查詢(xún)指定范圍內(nèi)的數(shù)據(jù)绪氛。

在MySQL數(shù)據(jù)庫(kù)的InnoDB存儲(chǔ)引擎中唆鸡，主鍵索引就是聚集索引，所有數(shù)據(jù)都會(huì)按照主鍵索引進(jìn)行組織枣察；而在MyISAM存儲(chǔ)引擎中争占，就沒(méi)有聚集索引了，因?yàn)镸yISAM存儲(chǔ)引擎中的數(shù)據(jù)不是按索引順序進(jìn)行存儲(chǔ)的序目。

（1）聚集索引特點(diǎn)：

• 聚集索引的葉節(jié)點(diǎn)就是實(shí)際數(shù)據(jù)頁(yè)臂痕；
• 在數(shù)據(jù)頁(yè)中數(shù)據(jù)按照索引順序存放；
• 行的物理位置和其在索引中的位置是相同的宛琅；
• 每個(gè)表只能有一個(gè)聚集索引刻蟹。

（2）聚集索引不適用的場(chǎng)景

• 數(shù)據(jù)列頻繁更改，這將導(dǎo)致整行移動(dòng)嘿辟。

2.2 非聚集索引

（1）非聚集索引是獨(dú)立于數(shù)據(jù)表之外的結(jié)構(gòu)舆瘪，即數(shù)據(jù)存放在一個(gè)地方片效，索引存放在另一個(gè)地方，索引葉子節(jié)點(diǎn)通過(guò)其自帶的指針指向數(shù)據(jù)的存儲(chǔ)位置（聚集索引葉子節(jié)點(diǎn)）英古。
（2）表中的非聚集索引可以創(chuàng)建多個(gè)淀衣，可以為表常用的每個(gè)列都創(chuàng)建一個(gè)非聚集索引（索引個(gè)數(shù)適當(dāng)就好，不要貪多）召调。
（3）形象的描述非聚集索引膨桥，可以用這個(gè)例子：一本介紹園藝的書(shū)可能包含一個(gè)植物通俗名稱(chēng)索引和一個(gè)植物學(xué)名索引，這兩個(gè)索引就是非聚集索引唠叛。每當(dāng)我們想看一個(gè)植物的詳細(xì)信息時(shí)只嚣，先通過(guò)名稱(chēng)定為到頁(yè)數(shù)，然后查看具體詳情艺沼。

2.3 聚集索引和非聚集索引比較

（1）概念層次

• 聚集索引：物理存儲(chǔ)按照索引排序册舞，就像字典正文，我們按照正文的字母順序就能找到我們的記錄障般。
• 非聚集索引：物理存儲(chǔ)不按照索引排序调鲸，就像偏旁查字法目錄，每個(gè)偏旁目錄下的字的頁(yè)碼不是順序的挽荡。

（2）速度方面

• 聚集索引：插入數(shù)據(jù)時(shí)藐石，速度較慢（時(shí)間主要花費(fèi)在“物理存儲(chǔ)的排序”上，也就是首先要找到位置然后再插入數(shù)據(jù)）定拟。但查詢(xún)數(shù)據(jù)的速度比非聚集索引要快于微。

（3）個(gè)數(shù)方面

• 聚集索引：每個(gè)表只能有一個(gè)聚集索引，默認(rèn)主鍵是聚集索引青自，但經(jīng)常業(yè)務(wù)上是不合適的角雷，一定要將聚集索引建立在下面兩種場(chǎng)景：第一，最頻繁使用的性穿、用以縮小查詢(xún)范圍的字段上；第二雷滚，最頻繁使用的需曾、需要排序的字段上。
• 非聚集索引：每個(gè)表可以創(chuàng)建多個(gè)祈远。

（4）實(shí)際舉例說(shuō)明
我們的漢語(yǔ)字典的正文本身就是一個(gè)聚集索引呆万。比如，我們要查“安”字车份，就會(huì)很自然地翻開(kāi)字典的前幾頁(yè)谋减，因?yàn)椤鞍病钡钠匆羰恰癮n”，而按照拼音排序漢字的字典是以英文字母“a”開(kāi)頭并以“z”結(jié)尾的扫沼，那么“安”字就自然地排在字典的前部出爹。如果您翻完了所有以“a”開(kāi)頭的部分仍然找不到這個(gè)字庄吼，那么就說(shuō)明您的字典中沒(méi)有這個(gè)字；同樣的严就，如果查“張”字总寻，那您也會(huì)將您的字典翻到最后部分，因?yàn)椤皬垺钡钠匆羰恰皕hang”梢为。也就是說(shuō)渐行，字典的正文部分本身就是一個(gè)目錄，您不需要再去查其他目錄來(lái)找到您需要找的內(nèi)容铸董。我們把這種正文內(nèi)容本身就是一種按照一定規(guī)則排列的目錄稱(chēng)為“聚集索引”祟印。

如果您認(rèn)識(shí)某個(gè)字，您可以快速地從拼音表中查到這個(gè)字粟害。但您也可能會(huì)遇到您不認(rèn)識(shí)的字蕴忆，不知道它的發(fā)音，這時(shí)候我磁，您就不能按照剛才的方法找到您要查的字孽文，而需要去根據(jù)“偏旁部首”查到您要找的字，然后根據(jù)這個(gè)字后的頁(yè)碼直接翻到某頁(yè)來(lái)找到您要找的字夺艰。但您結(jié)合“部首目錄”和“檢字表”而查到的字的排序并不是真正的正文的排序方法芋哭，比如您查“張”字，我們可以看到在查部首之后的檢字表中“張”的頁(yè)碼是672頁(yè)郁副，檢字表中“張”的上面是“馳”字减牺，但頁(yè)碼卻是63頁(yè)，“張”的下面是“弩”字存谎，頁(yè)面是390頁(yè)拔疚。很顯然，這些字并不是真正的分別位于“張”字的上下方既荚，現(xiàn)在您看到的連續(xù)的“馳稚失、張、弩”三字實(shí)際上就是他們?cè)诜蔷奂饕械呐判蚯∑福亲值湔闹械淖衷诜蔷奂饕械挠成渚涓鳌Ｎ覀兛梢酝ㄟ^(guò)這種方式來(lái)找到您所需要的字，但它需要兩個(gè)過(guò)程晴叨，先找到目錄中的結(jié)果凿宾，然后再翻到您所需要的頁(yè)碼。我們把這種目錄純粹是目錄兼蕊，正文純粹是正文的排序方式稱(chēng)為“非聚集索引”初厚。

（5）使用場(chǎng)景對(duì)比

2.4 聚集索引和非聚集索引常見(jiàn)問(wèn)題

（1）聚集索引的約束是唯一性，是否要求字段也是唯一的呢
分析：如果認(rèn)為是的朋友孙技，可能是受系統(tǒng)默認(rèn)設(shè)置的影響产禾，一般我們指定一個(gè)表的主鍵排作，如果這個(gè)表之前沒(méi)有聚集索引，同時(shí)建立主鍵時(shí)候沒(méi)有強(qiáng)制指定使用非聚集索引下愈，SQL會(huì)默認(rèn)在此字段上創(chuàng)建一個(gè)聚集索引纽绍，而主鍵都是唯一的，所以理所當(dāng)然的認(rèn)為創(chuàng)建聚集索引的字段也需要唯一势似。
結(jié)論：聚集索引可以創(chuàng)建在任何一列你想創(chuàng)建的字段上拌夏，這是從理論上講，實(shí)際情況并不能隨便指定履因，否則在性能上會(huì)是惡夢(mèng)障簿。

（2）在數(shù)據(jù)庫(kù)中通過(guò)什么描述聚集索引與非聚集索引的？
可以這樣區(qū)分兩者的區(qū)別：聚集索引的葉節(jié)點(diǎn)就是最終的數(shù)據(jù)節(jié)點(diǎn)栅迄，而非聚集索引的葉節(jié)仍然是索引節(jié)點(diǎn)站故，但它有一個(gè)指向最終數(shù)據(jù)的指針。

4 索引使用注意事項(xiàng)

4.1 索引不會(huì)包含有NULL值的列

只要列中包含有NULL值都將不會(huì)被包含在索引中毅舆，組合索引中只要有一列含有NULL值西篓，那么這一列對(duì)于此組合索引就是無(wú)效的。所以我們?cè)跀?shù)據(jù)庫(kù)設(shè)計(jì)時(shí)不要讓字段的默認(rèn)值為NULL憋活。

4.2 使用短索引

對(duì)串列進(jìn)行索引岂津，如果可能的話，應(yīng)該指定一個(gè)前綴長(zhǎng)度悦即。例如吮成，有一個(gè)CHAR(255)的列，如果在前10個(gè)或20個(gè)字符內(nèi)辜梳，多數(shù)值是惟一的粱甫，那么就不要對(duì)整個(gè)列進(jìn)行索引。短索引不僅可以提高查詢(xún)速度而且可以節(jié)省磁盤(pán)空間和I/O操作作瞄。

4.3 索引列排序

MySQL查詢(xún)只使用一個(gè)索引茶宵，因此如果where子句中已經(jīng)使用了索引的話，那么order by中的列是不會(huì)使用索引的宗挥。因此节预，數(shù)據(jù)庫(kù)默認(rèn)排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個(gè)列的排序属韧，如果需要最好給這些列創(chuàng)建組合索引。

4.4 like語(yǔ)句操作

一般情況下不鼓勵(lì)使用like操作蛤吓，如果非使用不可宵喂，不要將匹配符放于匹配字符串最前面，比如：like “%aaa%” 不會(huì)使用索引会傲，而like “aaa%”可以使用索引锅棕。

4.5 不要在列上進(jìn)行運(yùn)算

例如：select * from users where YEAR(adddate)<2007拙泽，將在每個(gè)行上進(jìn)行運(yùn)算，這將導(dǎo)致索引失效而進(jìn)行全表掃描裸燎，因此我們可以改成：

select * from users where adddate<’2007-01-01′

關(guān)于這一點(diǎn)可以圍觀：一個(gè)單引號(hào)引發(fā)的MYSQL性能損失顾瞻。

5 哪些場(chǎng)景會(huì)使索引失效

（1）以“%”開(kāi)頭的LIKE語(yǔ)句，模糊匹配
（2）OR語(yǔ)句前后沒(méi)有同時(shí)使用索引
（3）數(shù)據(jù)類(lèi)型出現(xiàn)隱式轉(zhuǎn)化（如varchar不加單引號(hào)的話可能會(huì)自動(dòng)轉(zhuǎn)換為int型）
（4）帶非操作符德绿，如<>/!=/not in/not exist
（5）列上作運(yùn)算
最后總結(jié)一下荷荤，MySQL只對(duì)以下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些時(shí)候的like(不以通配符%或_開(kāi)頭的情形)，而對(duì)于<>移稳，not in是不會(huì)使用索引的蕴纳。而理論上每張表里面最多可創(chuàng)建16個(gè)索引，不過(guò)除非是數(shù)據(jù)量真的很多个粱，否則過(guò)多的使用索引也不是那么好玩的古毛。

6 哪些場(chǎng)景需要?jiǎng)?chuàng)建索引

（1）不一定要?jiǎng)?chuàng)建索引，可通過(guò)優(yōu)化sql實(shí)現(xiàn)都许。
（2）避免對(duì)經(jīng)常更新的表進(jìn)行過(guò)多的索引稻薇，并且索引中的列盡可能少。而對(duì)經(jīng)常用于查詢(xún)的字段(外鍵)應(yīng)該創(chuàng)建索引胶征，但要避免添加不必要的字段
（3）數(shù)據(jù)量小的表最好不要使用索引塞椎，由于數(shù)據(jù)較少，查詢(xún)花費(fèi)的時(shí)間可能比遍歷索引的時(shí)間還要短弧烤，索引可能不會(huì)產(chǎn)生優(yōu)化效果忱屑。
（4）在條件表達(dá)式中經(jīng)常用到的、不同值較多(主鍵的列)的列上建立索引暇昂，在不同值少的列上不要建立索引莺戒。比如在學(xué)生表的“性別”字段上只有“男”與“女”兩個(gè)不同值，因此就無(wú)須建立索引急波。如果建立索引从铲，不但不會(huì)提高查詢(xún)效率，反而會(huì)嚴(yán)重降低更新速度澄暮。
（5）在頻繁進(jìn)行排序或分組（即進(jìn)行GROUPBY或ORDERBY操作）的列上建立索引名段，如果待排序的列有多個(gè)，可以在這些列上建立組合索引泣懊。在經(jīng)常使用WHERE子句中的列上面創(chuàng)建索引伸辟，加快條件的判斷速度。對(duì)于那些定義為text, image和bit數(shù)據(jù)類(lèi)型的列不應(yīng)該增加索引馍刮。