這篇文章將為大家詳細(xì)講解有關(guān)如何深入理解MySQL索引，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個(gè)參考，希望大家閱讀完這篇文章后對(duì)相關(guān)知識(shí)有一定的了解。
成都創(chuàng)新互聯(lián)專注于會(huì)寧企業(yè)網(wǎng)站建設(shè),成都響應(yīng)式網(wǎng)站建設(shè)公司,商城開(kāi)發(fā)。會(huì)寧網(wǎng)站建設(shè)公司,為會(huì)寧等地區(qū)提供建站服務(wù)。全流程按需設(shè)計(jì)網(wǎng)站，專業(yè)設(shè)計(jì)，全程項(xiàng)目跟蹤，成都創(chuàng)新互聯(lián)專業(yè)和態(tài)度為您提供的服務(wù)
當(dāng)提到MySQL數(shù)據(jù)庫(kù)的時(shí)候，我們的腦海里會(huì)想起幾個(gè)關(guān)鍵字：索引、事務(wù)、數(shù)據(jù)庫(kù)鎖等等，索引是MySQL的靈魂，是平時(shí)進(jìn)行查詢時(shí)的利器，也是面試中的重中之重。
可能你了解索引的底層是b+樹(shù)，會(huì)加快查詢，也會(huì)在表中建立索引，但這是遠(yuǎn)遠(yuǎn)不夠的，這里列舉幾個(gè)索引常見(jiàn)的面試題：
1、索引為什么要用b+樹(shù)這種數(shù)據(jù)結(jié)構(gòu)？
2、聚集索引和非聚集索引的區(qū)別？
3、索引什么時(shí)候會(huì)失效，最左匹配原則是什么？
當(dāng)遇到這些問(wèn)題的時(shí)候，可能會(huì)發(fā)現(xiàn)自己對(duì)索引還是一知半解，今天我們一起學(xué)習(xí)MySQL的索引。
首先來(lái)看在MySQL數(shù)據(jù)庫(kù)中，一條查詢語(yǔ)句是如何執(zhí)行的，索引出現(xiàn)在哪個(gè)環(huán)節(jié)，起到了什么作用。
當(dāng)執(zhí)行SQL語(yǔ)句時(shí)，應(yīng)用程序會(huì)連接到相應(yīng)的數(shù)據(jù)庫(kù)服務(wù)器，然后服務(wù)器對(duì)SQL進(jìn)行處理。
接著數(shù)據(jù)庫(kù)服務(wù)器會(huì)先去查詢是否有該SQL語(yǔ)句的緩存，key是查詢的語(yǔ)句，value是查詢的結(jié)果。如果你的查詢能夠直接命中，就會(huì)直接從緩存中拿出value來(lái)返回客戶端。
注：查詢不會(huì)被解析、不會(huì)生成執(zhí)行計(jì)劃、不會(huì)被執(zhí)行。
如果沒(méi)有命中緩存，則開(kāi)始第三步。
解析SQL：生成解析樹(shù)，驗(yàn)證關(guān)鍵字如select,where,left join 等）是否正確。
預(yù)處理：進(jìn)一步檢查解析樹(shù)是否合法，如檢查數(shù)據(jù)表和列是否存在，驗(yàn)證用戶權(quán)限等。
優(yōu)化SQL：決定使用哪個(gè)索引，或者在多個(gè)表相關(guān)聯(lián)的時(shí)候決定表的連接順序。緊接著，將SQL語(yǔ)句轉(zhuǎn)成執(zhí)行計(jì)劃。
最后，數(shù)據(jù)庫(kù)服務(wù)器將查詢結(jié)果返回給客戶端。(如果查詢可以緩存，MySQL也會(huì)將結(jié)果放到查詢緩存中)
這就是一條查詢語(yǔ)句的執(zhí)行流程，可以看到索引出現(xiàn)在優(yōu)化SQL的流程步驟中，接下來(lái)了解索引到底是什么？
先簡(jiǎn)單地了解一下索引的基本概念。
索引是幫助數(shù)據(jù)庫(kù)高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。
Btree索引（B+tree，B-tree)
哈希索引
full-index全文索引
RTree
普通索引：即一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單列索引。
唯一索引：索引列的值必須唯一，但允許有空值。
復(fù)合索引：一個(gè)索引包含多個(gè)列。
聚集索引：表記錄的排列順序和索引的排列順序一致。
非聚集索引：表記錄的排列順序和索引的排列順序不一致。
聚集索引：就是以主鍵創(chuàng)建的索引。
非聚集索引：就是以非主鍵創(chuàng)建的索引（也叫做二級(jí)索引）。
聚集索引
聚集索引表記錄的排列順序和索引的排列順序一致，所以查詢效率快，因?yàn)橹灰业降谝粋€(gè)索引值記錄，其余的連續(xù)性的記錄在物理表中也會(huì)連續(xù)存放，一起就可以查詢到。
缺點(diǎn)：新增比較慢，因?yàn)闉榱吮ＷC表中記錄的物理順序和索引順序一致，在記錄插入的時(shí)候，會(huì)對(duì)數(shù)據(jù)頁(yè)重新排序。
非聚集索引
索引的邏輯順序與磁盤(pán)上行的物理存儲(chǔ)順序不同，非聚集索引在葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵和索引列，當(dāng)我們使用非聚集索引查詢數(shù)據(jù)時(shí)，需要拿到葉子上的主鍵再去表中查到想要查找的數(shù)據(jù)。這個(gè)過(guò)程就是我們所說(shuō)的回表。
聚集索引在葉子節(jié)點(diǎn)存儲(chǔ)的是表中的數(shù)據(jù)。
非聚集索引在葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵和索引列。
比如漢語(yǔ)字典，想要查「阿」字，只需要翻到字典前幾頁(yè)，a開(kāi)頭的位置，接著「啊」「愛(ài)」都會(huì)出來(lái)。也就是說(shuō)，字典的正文部分本身就是一個(gè)目錄，不需要再去查其他目錄來(lái)找到需要找的內(nèi)容。我們把這種正文內(nèi)容本身就是一種按照一定規(guī)則排列的目錄稱為==聚集索引==。
如果遇到不認(rèn)識(shí)的字，只能根據(jù)“偏旁部首”進(jìn)行查找，然后根據(jù)這個(gè)字后的頁(yè)碼直接翻到某頁(yè)來(lái)找到要找的字。但結(jié)合部首目錄和檢字表而查到的字的排序并不是真正的正文的排序方法。
比如要查“玉”字，我們可以看到在查部首之后的檢字表中“玉”的頁(yè)碼是587頁(yè)，然后是玨，是251頁(yè)。很顯然，在字典中這兩個(gè)字并沒(méi)有挨著，現(xiàn)在看到的連續(xù)的“玉、玨、瑩”三字實(shí)際上就是他們?cè)诜蔷奂饕械呐判?，是字典正文中的字在非聚集索引中的映射。我們可以通過(guò)這種方式來(lái)找到所需要的字，但它需要兩個(gè)過(guò)程，先找到目錄中的結(jié)果，然后再翻到結(jié)果所對(duì)應(yīng)的頁(yè)碼。我們把這種目錄純粹是目錄，正文純粹是正文的排序方式稱為==非聚集索引==。
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
ALTER TABLE `table_name` ADD UNIQUE (`column`)
ALTER TABLE `table_name` ADD INDEX index_name (`column` )
ALTER TABLE `table_name` ADD FULLTEXT (`column`)
ALTER TABLE `table_name` ADD INDEX index_name (`column1`,`column2`,`column3`)
了解了索引的基本概念后，可能最好奇的就是索引的底層是怎么實(shí)現(xiàn)的呢？為什么索引可以如此高效地進(jìn)行數(shù)據(jù)的查找？如何設(shè)計(jì)數(shù)據(jù)結(jié)構(gòu)可以滿足我們的要求？ 下文通過(guò)一般程序員的思維來(lái)想一下如果是我們來(lái)設(shè)計(jì)索引，要如何設(shè)計(jì)來(lái)達(dá)到索引的效果。
可能直接想到的就是用哈希表來(lái)實(shí)現(xiàn)快速查找，就像我們平時(shí)用的hashmap一樣，value = get(key) O(1)時(shí)間復(fù)雜度一步到位，確實(shí)，哈希索引是一種方式。
哈希索引就是采用一定的哈希算法，只需一次哈希算法即可立刻定位到相應(yīng)的位置，速度非常快。本質(zhì)上就是把鍵值換算成新的哈希值，根據(jù)這個(gè)哈希值來(lái)定位。
哈希索引沒(méi)辦法利用索引完成排序。
不能進(jìn)行多字段查詢。
在有大量重復(fù)鍵值的情況下，哈希索引的效率也是極低的（出現(xiàn)哈希碰撞問(wèn)題）。
不支持范圍查詢。
在MySQL常用的InnoDB引擎中，還是使用B+樹(shù)索引比較多。InnoDB是自適應(yīng)哈希索引的（hash索引的創(chuàng)建由==InnoDB存儲(chǔ)引擎自動(dòng)優(yōu)化創(chuàng)建==，我們干預(yù)不了）。
假設(shè)要查詢某個(gè)區(qū)間的數(shù)據(jù)，我們只需要拿到區(qū)間的起始值，然后在樹(shù)中進(jìn)行查找。
如數(shù)據(jù)為：
當(dāng)查找到起點(diǎn)節(jié)點(diǎn)10后，再順著鏈表進(jìn)行遍歷，直到鏈表中的節(jié)點(diǎn)數(shù)據(jù)大于區(qū)間的終止值為止。所有遍歷到的數(shù)據(jù)，就是符合區(qū)間值的所有數(shù)據(jù)。
利用二叉查找樹(shù)，區(qū)間查詢的功能已經(jīng)實(shí)現(xiàn)了。但是，為了節(jié)省內(nèi)存，我們只能把樹(shù)存儲(chǔ)在硬盤(pán)中。
那么，每個(gè)節(jié)點(diǎn)的讀取或者訪問(wèn)，都對(duì)應(yīng)一次硬盤(pán)IO操作。每次查詢數(shù)據(jù)時(shí)磁盤(pán)IO操作的次數(shù)，也叫做==IO漸進(jìn)復(fù)雜度==，也就是==樹(shù)的高度==。
所以，我們要減少磁盤(pán)IO操作的次數(shù)，也就是要==降低樹(shù)的高度==。
結(jié)構(gòu)優(yōu)化過(guò)程如下圖所示：
這里將二叉樹(shù)變?yōu)榱薓叉樹(shù)，降低了樹(shù)的高度，那么這個(gè)M應(yīng)該選擇多少才合適呢？
問(wèn)題：對(duì)于相同個(gè)數(shù)的數(shù)據(jù)構(gòu)建m叉樹(shù)索引，m叉樹(shù)中的m越大，那樹(shù)的高度就越小，那m叉樹(shù)中的m是不是越大越好呢？到底多大才合適呢？
不管是內(nèi)存中的數(shù)據(jù)還是磁盤(pán)中的數(shù)據(jù)，操作系統(tǒng)都是按頁(yè)（一頁(yè)的大小通常是4kb，這個(gè)值可以通過(guò)getconfig(PAGE_SIZE)命令查看）來(lái)讀取的，一次只會(huì)讀取一頁(yè)的數(shù)據(jù)。
如果要讀取的數(shù)據(jù)量超過(guò)了一頁(yè)的大小，就會(huì)觸發(fā)多次IO操作。所以在選擇m大小的時(shí)候，要盡量讓每個(gè)節(jié)點(diǎn)的大小等于一個(gè)頁(yè)的大小。
一般實(shí)際應(yīng)用中，出度d（樹(shù)的分叉數(shù)）是非常大的數(shù)字，通常超過(guò)100；==樹(shù)的高度（h）非常小，通常不超過(guò)3==。
順著解決問(wèn)題的思路知道了我們想要的數(shù)據(jù)結(jié)構(gòu)是什么。目前索引常用的數(shù)據(jù)結(jié)構(gòu)是B+樹(shù)，先介紹一下什么是B樹(shù)（也就是B-樹(shù)）。
關(guān)鍵字分布在整棵樹(shù)的所有節(jié)點(diǎn)。
任何一個(gè)關(guān)鍵字出現(xiàn)且只出現(xiàn)在一個(gè)節(jié)點(diǎn)中。
搜索有可能在非葉子節(jié)點(diǎn)結(jié)束。
其搜索性能等價(jià)于在關(guān)鍵字全集內(nèi)做一次二分查找。
如下圖所示：
了解了B樹(shù)，再來(lái)看一下B+樹(shù)，也是MySQL索引大部分情況所使用的數(shù)據(jù)結(jié)構(gòu)。
非葉子節(jié)點(diǎn)的子樹(shù)指針與關(guān)鍵字個(gè)數(shù)相同。
非葉子節(jié)點(diǎn)的子樹(shù)指針P[i]，指向關(guān)鍵字屬于 [k[i],K[i+1])的子樹(shù)（注意：區(qū)間是前閉后開(kāi))。
為所有葉子節(jié)點(diǎn)增加一個(gè)鏈指針。
所有關(guān)鍵字都在葉子節(jié)點(diǎn)出現(xiàn)。
這些基本特點(diǎn)是為了滿足以下的特性。
所有的關(guān)鍵字都出現(xiàn)在葉子節(jié)點(diǎn)的鏈表中，且鏈表中的關(guān)鍵字是有序的。
搜索只在葉子節(jié)點(diǎn)命中。
非葉子節(jié)點(diǎn)相當(dāng)于是葉子節(jié)點(diǎn)的索引層，葉子節(jié)點(diǎn)是存儲(chǔ)關(guān)鍵字?jǐn)?shù)據(jù)的數(shù)據(jù)層。
B+樹(shù)的磁盤(pán)讀寫(xiě)代價(jià)更低。B+樹(shù)的內(nèi)部沒(méi)有指向關(guān)鍵字具體信息的指針，所以其內(nèi)部節(jié)點(diǎn)相對(duì)B樹(shù)更小，如果把所有關(guān)鍵字存放在同一塊盤(pán)中，那么盤(pán)中所能容納的關(guān)鍵字?jǐn)?shù)量也越多，一次性讀入內(nèi)存的需要查找的關(guān)鍵字也就越多，相應(yīng)的，IO讀寫(xiě)次數(shù)就降低了。
樹(shù)的查詢效率更加穩(wěn)定。B+樹(shù)所有數(shù)據(jù)都存在于葉子節(jié)點(diǎn)，所有關(guān)鍵字查詢的路徑長(zhǎng)度相同，每次數(shù)據(jù)的查詢效率相當(dāng)。而B(niǎo)樹(shù)可能在非葉子節(jié)點(diǎn)就停止查找了，所以查詢效率不夠穩(wěn)定。
B+樹(shù)只需要去遍歷葉子節(jié)點(diǎn)就可以實(shí)現(xiàn)整棵樹(shù)的遍歷。
因?yàn)镸ongoDB不是傳統(tǒng)的關(guān)系型數(shù)據(jù)庫(kù)，而是以Json格式作為存儲(chǔ)的NOSQL非關(guān)系型數(shù)據(jù)庫(kù)，目的就是高性能、高可用、易擴(kuò)展。擺脫了關(guān)系模型，所以范圍查詢和遍歷查詢的需求就沒(méi)那么強(qiáng)烈了。
主鍵索引
MyISAM的索引文件（.MYI）和數(shù)據(jù)文件（.MYD）文件是分離的，索引文件僅保存記錄所在頁(yè)的指針（物理位置），通過(guò)這些指針來(lái)讀取頁(yè)，進(jìn)而讀取被索引的行。
樹(shù)中的葉子節(jié)點(diǎn)保存的是對(duì)應(yīng)行的物理位置。通過(guò)該值，==存儲(chǔ)引擎能順利地進(jìn)行回表查詢，得到一行完整記錄==。
同時(shí)，每個(gè)葉子也保存了指向下一個(gè)葉子的指針，從而方便葉子節(jié)點(diǎn)的范圍遍歷。
輔助索引
在MyISAM中，主鍵索引和輔助索引在結(jié)構(gòu)上沒(méi)有任何區(qū)別，==只是主鍵索引要求key是唯一的，而輔助索引的key可以重復(fù)==。
Innodb的主鍵索引和輔助索引之前提到過(guò)，再回顧一次。
主鍵索引
InnoDB主鍵索引中既存儲(chǔ)了主健值，又存儲(chǔ)了行數(shù)據(jù)。
輔助索引
對(duì)于輔助索引，InnoDB采用的方式是在葉子節(jié)點(diǎn)中保存主鍵值，通過(guò)這個(gè)主鍵值來(lái)回表查詢到一條完整記錄，因此按輔助索引檢索其實(shí)進(jìn)行了二次查詢，效率是沒(méi)有主鍵索引高的。
在上一節(jié)中了解了索引的多種數(shù)據(jù)結(jié)構(gòu)，以及B樹(shù)和B+樹(shù)的對(duì)比等，大家應(yīng)該對(duì)索引的底層實(shí)現(xiàn)有了初步的了解。這一節(jié)從應(yīng)用層的角度出發(fā)，看一下如何建索引更能滿足我們的需求，以及MySQL索引什么時(shí)候會(huì)失效的問(wèn)題。
先來(lái)思考一個(gè)小問(wèn)題。
問(wèn)題：當(dāng)查詢條件為2個(gè)及2個(gè)以上時(shí)，是創(chuàng)建多個(gè)單列索引還是創(chuàng)建一個(gè)聯(lián)合索引好呢？它們之間的區(qū)別是什么？哪個(gè)效率高呢？
先來(lái)建立一些單列索引進(jìn)行測(cè)試：
這里建立了一張表，里面建立了三個(gè)單列索引userId,mobile,billMonth。
然后進(jìn)行多列查詢。
explain select * from `t_mobilesms_11` where userid = '1' and mobile = '13504679876' and billMonth = '1998-03'
我們發(fā)現(xiàn)查詢時(shí)只用到了userid這一個(gè)單列索引，這是為什么呢？因?yàn)檫@取決于MySQL優(yōu)化器的優(yōu)化策略。
當(dāng)多條件聯(lián)合查詢時(shí)，優(yōu)化器會(huì)評(píng)估哪個(gè)條件的索引效率高，它會(huì)選擇最佳的索引去使用。也就是說(shuō)，此處三個(gè)索引列都可能被用到，只不過(guò)優(yōu)化器判斷只需要使用userid這一個(gè)索引就能完成本次查詢，故最終explain展示的key為userid。
多個(gè)單列索引在多條件查詢時(shí)優(yōu)化器會(huì)選擇最優(yōu)索引策略，可能只用一個(gè)索引，也可能將多個(gè)索引都用上。
但是多個(gè)單列索引底層會(huì)建立多個(gè)B+索引樹(shù)，比較占用空間，也會(huì)浪費(fèi)搜索效率 所以多條件聯(lián)合查詢時(shí)最好建聯(lián)合索引。
那聯(lián)合索引就可以三個(gè)條件都用到了嗎？會(huì)出現(xiàn)索引失效的問(wèn)題嗎？
該部分參考并引用文章：
一張圖搞懂MySQL的索引失效
創(chuàng)建user表，然后建立 name, age, pos, phone 四個(gè)字段的聯(lián)合索引 全值匹配（索引最佳）。
索引生效，這是最佳的查詢。
那么時(shí)候會(huì)失效呢？
最左匹配原則：最左優(yōu)先，以最左邊的為起點(diǎn)任何連續(xù)的索引都能匹配上，如不連續(xù)，則匹配不上。
如：建立索引為(a,b)的聯(lián)合索引，那么只查 where b = 2 則不生效。換句話說(shuō)：如果建立的索引是(a,b,c)，也只有(a),(a,b),(a,b,c)三種查詢可以生效。
這里跳過(guò)了最左的name字段進(jìn)行查詢，發(fā)現(xiàn)索引失效了。
遇到范圍查詢（>、<、between、like）就會(huì)停止匹配。
比如：a= 1 and b = 2 and c>3 and d =4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，因?yàn)閏字段是一個(gè)范圍查詢，它之后的字段會(huì)停止匹配。
如計(jì)算、函數(shù)、（手動(dòng)或自動(dòng)）類型轉(zhuǎn)換等操作，會(huì)導(dǎo)致索引失效而進(jìn)行全表掃描。
explain select * from user where left(name,3) = 'zhangsan' and age =20
這里對(duì)name字段進(jìn)行了left函數(shù)操作，導(dǎo)致索引失效。
explain select * from user where age != 20;
explain select * from user where age <> 20;
索引失效
explain select * from user where name like ‘%zhangsan’;
索引生效
explain select * from user where name like ‘zhangsan%’;
explain select * from user where name = 2000;
explain select * from user where name = ‘2000’ or age = 20 or pos =‘cxy’;
正常（索引參與了排序），沒(méi)有違反最左匹配原則。
explain select * from user where name = 'zhangsan' and age = 20 order by age,pos;
違反最左前綴法則，導(dǎo)致額外的文件排序（會(huì)降低性能）。
explain select name,age from user where name = 'zhangsan' order by pos;
正常（索引參與了排序）。
explain select name,age from user where name = 'zhangsan' group by age;
違反最左前綴法則，導(dǎo)致產(chǎn)生臨時(shí)表（會(huì)降低性能）。
explain select name,age from user where name = 'zhangsan' group by pos,age;
了解一條查詢語(yǔ)句是如何執(zhí)行的，發(fā)現(xiàn)建立索引是一種可以高效查找的數(shù)據(jù)結(jié)構(gòu)。
了解了索引的各種分類情況，聚集索引和非聚集索引的區(qū)別，如何創(chuàng)建各種索引。
通過(guò)需求一步步分析出為什么MySQL要選b+tree作為索引的數(shù)據(jù)結(jié)構(gòu)，對(duì)比了btree和b+tree的區(qū)別、 MyISAM和innodb中索引的區(qū)別。
了解了索引會(huì)失效的多種情況，比較重要的最左匹配原則，相應(yīng)地我們可以在建索引的時(shí)候做一些優(yōu)化。
關(guān)于如何深入理解MySQL索引就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到。
重慶分公司
重慶分公司
