作者：不吃西紅柿

簡(jiǎn)介：CSDN博客專家、藍(lán)橋簽約作者、大數(shù)據(jù)領(lǐng)域優(yōu)質(zhì)創(chuàng)作者。

以我的資歷和文憑，將來(lái)這個(gè)城市的大街，都?xì)w我掃。

---

??【系列課程介紹】

『面試知識(shí)集錦』系列課程包括以下20個(gè)系列，超過(guò)100篇文章。每篇文章的前半部分為「知識(shí)體系」幫助你夯實(shí)基礎(chǔ)，后半部分為「面試真題」幫助你拿下面試。

如果覺(jué)得還不錯(cuò)，求點(diǎn)贊、求收藏、關(guān)注專欄。

專欄歷史文章：https://blog.csdn.net/weixin_39032019/category_11163855.html

『面試知識(shí)集錦100篇』HR的小心思，你真的懂嗎？
『面試知識(shí)集錦100篇』shell基礎(chǔ)知識(shí)大全，我奶奶的速查手冊(cè)！
『面試知識(shí)集錦100篇』mysql基礎(chǔ)知識(shí)、面試真題? ?

? ?...

目錄

1、創(chuàng)建索引的幾種方式

1.1 直接創(chuàng)建

1.2 修改表結(jié)構(gòu)(添加索引)

1.3 創(chuàng)建表的時(shí)候直接指定

2、mysql索引知識(shí)

2.1 B+Tree索引

2.2 主鍵索引和普通索引的區(qū)別

2.3 唯一索引vs普通索引

3、mysql索引優(yōu)化

3.1 查看索引使用情況

3.2 mysql索引使用策略

3.3 mysql索引使用原則

4、索引選擇異常處理辦法

---

1、創(chuàng)建索引的幾種方式

1.1 直接創(chuàng)建

CREATE INDEX indexName ON table_name (column_name)

如果是CHAR，VARCHAR類型，length可以小于字段實(shí)際長(zhǎng)度；如果是BLOB和TEXT類型，必須指定 length。

1.2 修改表結(jié)構(gòu)(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

1.3 創(chuàng)建表的時(shí)候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );

2、mysql索引知識(shí)

2.1 B+Tree索引

在InnoDB中，表都是根據(jù)主鍵順序以索引的形式存放的，這種存儲(chǔ)方式的表稱為索引組織表（IOT），InnoDB使用B+樹(shù)索引模型，數(shù)據(jù)都是存儲(chǔ)在B+樹(shù)中的。

假設(shè)，有一個(gè)表的主鍵列為ID，字段為k，并且在k上有索引。表中R1~R5的（ID,k）值分別為（100,1）、（200,2）、（300,3）、（500,5）、（600,6），每一個(gè)索引在InnoDB里面對(duì)應(yīng)一棵B+樹(shù)，兩棵樹(shù)的簡(jiǎn)意示意圖如下：

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??

2.2 主鍵索引和普通索引的區(qū)別

• 主鍵索引的葉子節(jié)點(diǎn)存的是整行數(shù)據(jù)。主鍵索引也被稱為聚簇索引(clustered index)

• 非主鍵索引的葉子節(jié)點(diǎn)內(nèi)容是主鍵的值。非主鍵索引也被稱為二級(jí)索引(secondary index)

如果語(yǔ)句是select * from T where ID=500，即主鍵查詢方式，則只需要搜索ID這棵B+樹(shù)；

如果語(yǔ)句是select * from T where k=5，即普通索引查詢方式，則需要先搜索k索引樹(shù)，得到ID的值為500，再到ID索引樹(shù)搜索一次，這個(gè)過(guò)程稱為回表！

也就是說(shuō)，基于非主鍵索引的查詢需要多掃描一棵索引樹(shù)，因此，我們?cè)趹?yīng)用中應(yīng)該盡量使用主鍵查詢。

2.3 唯一索引vs普通索引

從查詢上來(lái)說(shuō)

• 對(duì)于普通索引來(lái)說(shuō)，查找到滿足條件的第一個(gè)記錄后，需要查找下一個(gè)記錄，直到碰到第一個(gè)不滿足條件的記錄。

• 對(duì)于唯一索引來(lái)說(shuō)，由于索引定義了唯一性，查找到第一個(gè)滿足條件的記錄后，就會(huì)停止繼續(xù)檢索。

從更新上來(lái)說(shuō)

A??如果目標(biāo)頁(yè)在內(nèi)存中：

• 對(duì)于唯一索引來(lái)說(shuō)，找到3和5之間的位置，判斷有沒(méi)有沖突，插入這個(gè)值，語(yǔ)句執(zhí)行結(jié)束；

• 對(duì)于普通索引來(lái)說(shuō)，找到3和5之間的位置，插入這個(gè)值，語(yǔ)句執(zhí)行結(jié)束。

B??如果目標(biāo)頁(yè)在不在內(nèi)存中：

• 對(duì)于唯一索引來(lái)說(shuō)，需要將數(shù)據(jù)頁(yè)讀入內(nèi)存，判斷到?jīng)]有沖突，插入這個(gè)值，語(yǔ)句執(zhí)行結(jié)束；

• 對(duì)于普通索引來(lái)說(shuō)，則是將更新記錄在change buffer，語(yǔ)句執(zhí)行就結(jié)束了。

從這里可以看到，查詢上普通索引只是比唯一索引多了一個(gè)一次指針尋找和一次計(jì)算，由于數(shù)據(jù)是按頁(yè)讀取的，數(shù)據(jù)幾乎都在內(nèi)存中，所以性能相差不大。

? 但從更新上來(lái)看，如果數(shù)據(jù)不在內(nèi)存中，唯 一索引需要將數(shù)據(jù)從磁盤(pán)上讀取到內(nèi)存中，這樣會(huì)引發(fā)隨機(jī)讀，導(dǎo)致IO消耗增多，而普通索引可以利用change buffer，IO上邊要節(jié)省很多。性能相差會(huì)很多，所以如果可以在業(yè)務(wù)端保證數(shù)據(jù)的唯一性，那就可以使用普通索引。

3、mysql索引優(yōu)化

3.1 查看索引使用情況

使用方法：在select語(yǔ)句前加上explain

示例：EXPLAIN SELECT surname,first_name form a,b WHERE a.id=b.id

EXPLAIN列的解釋：

• table：顯示這一行的數(shù)據(jù)是關(guān)于哪張表的。
• type：這是重要的列，顯示連接使用了何種類型。從最好到最差的連接類型為const、eq_reg、ref、range、index和ALL。
• possible_keys：顯示可能應(yīng)用在這張表中的索引。如果為空，沒(méi)有可能的索引。可以為相關(guān)的域從WHERE語(yǔ)句中選擇一個(gè)合適的語(yǔ)句。
• key： 實(shí)際使用的索引。如果為NULL，則沒(méi)有使用索引。很少的情況下，MySQL會(huì)選擇優(yōu)化不足的索引。這種情況下，可以在SELECT語(yǔ)句中使用USE INDEX（indexname）來(lái)強(qiáng)制使用一個(gè)索引或者用IGNORE INDEX（indexname）來(lái)強(qiáng)制MySQL忽略索引。
• key_len：使用的索引的長(zhǎng)度。在不損失精確性的情況下，長(zhǎng)度越短越好。
• ref：顯示索引的哪一列被使用了，如果可能的話，是一個(gè)常數(shù)。
• rows：MySQL認(rèn)為必須檢查的用來(lái)返回請(qǐng)求數(shù)據(jù)的行數(shù)。
• Extra：關(guān)于MySQL如何解析查詢的額外信息。

Extra列返回的描述的意義：

Distinct: 一旦MySQL找到了與行相聯(lián)合匹配的行，就不再搜索了。Not exists: MySQL優(yōu)化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標(biāo)準(zhǔn)的行，就不再搜索了。Range checked for each Record（index map:#）: 沒(méi)有找到理想的索引，因此對(duì)于從前面表中來(lái)的每一個(gè)行組合，MySQL檢查使用哪個(gè)索引，并用它來(lái)從表中返回行。這是使用索引的最慢的連接之一。Using filesort: 看到這個(gè)的時(shí)候，查詢就需要優(yōu)化了。MySQL需要進(jìn)行額外的步驟來(lái)發(fā)現(xiàn)如何對(duì)返回的行排序。它根據(jù)連接類型以及存儲(chǔ)排序鍵值和匹配條件的全部行的行指針來(lái)排序全部行。Using index: 列數(shù)據(jù)是從僅僅使用了索引中的信息而沒(méi)有讀取實(shí)際的行動(dòng)的表返回的，這發(fā)生在對(duì)表的全部的請(qǐng)求列都是同一個(gè)索引的部分的時(shí)候。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上。Where used: 使用了WHERE從句來(lái)限制哪些行將與下一張表匹配或者是返回給用戶。如果不想返回表中的全部行，并且連接類型ALL或index，這就會(huì)發(fā)生，或者是查詢有問(wèn)題不同連接類型的解釋（按照效率高低的順序排序）。system: 表只有一行：system表。這是const連接類型的特殊情況。const: 表中的一個(gè)記錄的最大值能夠匹配這個(gè)查詢（索引可以是主鍵或惟一索引）。因?yàn)橹挥幸恍?#xff0c;這個(gè)值實(shí)際就是常數(shù)，因?yàn)镸ySQL先讀這個(gè)值然后把它當(dāng)做常數(shù)來(lái)對(duì)待。eq_ref: 在連接中，MySQL在查詢時(shí)，從前面的表中，對(duì)每一個(gè)記錄的聯(lián)合都從表中讀取一個(gè)記錄，它在查詢使用了索引為主鍵或惟一鍵的全部時(shí)使用。ref: 這個(gè)連接類型只有在查詢使用了不是惟一或主鍵的鍵或者是這些類型的部分（比如，利用最左邊前綴）時(shí)發(fā)生。對(duì)于之前的表的每一個(gè)行聯(lián)合，全部記錄都將從表中讀出。這個(gè)類型嚴(yán)重依賴于根據(jù)索引匹配的記錄多少—越少越好。range: 這個(gè)連接類型使用索引返回一個(gè)范圍中的行，比如使用>或<查找東西時(shí)發(fā)生的情況。index: 這個(gè)連接類型對(duì)前面的表中的每一個(gè)記錄聯(lián)合進(jìn)行完全掃描（比ALL更好，因?yàn)樗饕话阈∮诒頂?shù)據(jù)）。ALL: 這個(gè)連接類型對(duì)于前面的每一個(gè)記錄聯(lián)合進(jìn)行完全掃描，這一般比較糟糕，應(yīng)該盡量避免。

3.2 mysql索引使用策略

最好全值匹配--索引怎么建我怎么用。

最佳左前綴法則--如果是多列復(fù)合索引，要遵守最左前綴法則。指的是查詢要從索引的最左前列開(kāi)始并且不跳過(guò)索引中的列。

不在索引列上做任何操作（計(jì)算，函數(shù)，（自動(dòng)或者手動(dòng)）類型裝換），會(huì)導(dǎo)致索引失效而導(dǎo)致全表掃描。

存儲(chǔ)引擎不能使用索引中范圍條件右邊的列。--范圍之后索引失效（< ,>,between and）。

盡量使用覆蓋索引--索引和查詢列一致，減少select *。--按需取數(shù)據(jù)用多少取多少。

在MYSQL使用不等于（<,>,!=）的時(shí)候無(wú)法使用索引，會(huì)導(dǎo)致索引失效。

is null或者is not null 也會(huì)導(dǎo)致無(wú)法使用索引。

like以通配符開(kāi)頭（'%abc...'）MYSQL索引失效會(huì)變成全表掃描的操作。--覆蓋索引。

隱式轉(zhuǎn)換索引失效：字符串不加單引號(hào)。

where條件少用or，用它來(lái)連接時(shí)索引會(huì)失效。

3.3 mysql索引使用原則

（1）復(fù)合索引：選擇索引列的順序

????? 1)盡量把字段長(zhǎng)度小的列放在聯(lián)合索引的最左側(cè)（因?yàn)樽侄伍L(zhǎng)度越小，一頁(yè)能存儲(chǔ)的數(shù)據(jù)量越大，IO性能也就越好）???

????? 2)區(qū)分度最高的放在聯(lián)合索引的最左側(cè)（區(qū)分度=列中不同值的數(shù)量/列的總行數(shù)）

????? 3)使用最頻繁的列放到聯(lián)合索引的左側(cè)（這樣可以比較少的建立一些索引）

（2）表關(guān)聯(lián)查詢

????? 1)類型和大小要相同，可以使用索引。

????????? VARCHAR(10)和?CHAR(10)大小相同，但?VARCHAR(10)與?CHAR(15)不相同。

????? 2)字符串列之間比較，兩列應(yīng)使用相同的字符集。例如，將utf8列與?latin1列進(jìn)行比較會(huì)不使用索引。

????? 3)將字符串列與時(shí)間或數(shù)字列進(jìn)行比較時(shí)，在沒(méi)有轉(zhuǎn)換情況下,不使用索引。

（3）常見(jiàn)的索引列建議

????? 1)? ?WHERE 字段

????? 2)? ?ORDER BY、GROUP BY、DISTINCT 中的字段不要將符合1和2中字段的列都建立一個(gè)索引，通常將1、2中的字段建立聯(lián)合索引效果更好

????? 3)? 多表join的關(guān)聯(lián)列

????? 4）通過(guò)索引掃描的行記錄數(shù)超過(guò)全表的10%~30%左右,優(yōu)化器不會(huì)走索引，而變成全表掃描

????? 5）避免使用雙%號(hào)的查詢條件。

（如果無(wú)前置%,只有后置%，是可以用到列上的索引的）

? 覆蓋索引、前綴索引、索引下推，在滿足語(yǔ)句需求的情況下，盡量少地訪問(wèn)資源是數(shù)據(jù)庫(kù)設(shè)計(jì)的重要原則之一。我們?cè)谑褂脭?shù)據(jù)庫(kù)的時(shí)候，尤其是在設(shè)計(jì)表結(jié)構(gòu)時(shí)，也要以減少資源消耗為目標(biāo)。

4、索引選擇異常處理辦法

• 采用force index 強(qiáng)行選擇一個(gè)索引。
• 修改sql語(yǔ)句、引導(dǎo)MySQL使用我們期望的索引。
• 在有些場(chǎng)景下，我們可以新建一個(gè)更適合的索引，來(lái)提供給優(yōu)化器做選擇，或刪除掉誤用的索引。

由于索引統(tǒng)計(jì)信息的不準(zhǔn)確，可以用analyze table來(lái)解決。

而對(duì)于其它優(yōu)化器誤判斷的情況，你可以在應(yīng)用端用force index 來(lái)強(qiáng)行指定索引，也可以通過(guò)修改語(yǔ)句來(lái)引導(dǎo)優(yōu)化器，還可以通過(guò)增加或者刪除索引來(lái)繞過(guò)這個(gè)問(wèn)題。

知識(shí)集錦專欄：https://blog.csdn.net/weixin_39032019/category_11163855.html

求點(diǎn)贊、求收藏、關(guān)注專欄

總結(jié)

以上是生活随笔為你收集整理的❤『知识集锦』一文搞懂mysql索引！！（建议收藏）的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。