如何深入理解MySQL索引

這篇文章將為大家詳細講解有關(guān)如何深入理解MySQL索引，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關(guān)知識有一定的了解。

成都創(chuàng)新互聯(lián)專注于會寧企業(yè)網(wǎng)站建設(shè),成都響應式網(wǎng)站建設(shè)公司,商城開發(fā)。會寧網(wǎng)站建設(shè)公司,為會寧等地區(qū)提供建站服務。全流程按需設(shè)計網(wǎng)站，專業(yè)設(shè)計，全程項目跟蹤，成都創(chuàng)新互聯(lián)專業(yè)和態(tài)度為您提供的服務

前言

當提到MySQL數(shù)據(jù)庫的時候，我們的腦海里會想起幾個關(guān)鍵字：索引、事務、數(shù)據(jù)庫鎖等等，索引是MySQL的靈魂，是平時進行查詢時的利器，也是面試中的重中之重。

可能你了解索引的底層是b+樹，會加快查詢，也會在表中建立索引，但這是遠遠不夠的，這里列舉幾個索引常見的面試題：

1、索引為什么要用b+樹這種數(shù)據(jù)結(jié)構(gòu)？

2、聚集索引和非聚集索引的區(qū)別？

3、索引什么時候會失效，最左匹配原則是什么？

當遇到這些問題的時候，可能會發(fā)現(xiàn)自己對索引還是一知半解，今天我們一起學習MySQL的索引。

一、一條查詢語句是如何執(zhí)行的

首先來看在MySQL數(shù)據(jù)庫中，一條查詢語句是如何執(zhí)行的，索引出現(xiàn)在哪個環(huán)節(jié)，起到了什么作用。

1.1 應用程序發(fā)現(xiàn)SQL到服務端

當執(zhí)行SQL語句時，應用程序會連接到相應的數(shù)據(jù)庫服務器，然后服務器對SQL進行處理。

1.2 查詢緩存

接著數(shù)據(jù)庫服務器會先去查詢是否有該SQL語句的緩存，key是查詢的語句，value是查詢的結(jié)果。如果你的查詢能夠直接命中，就會直接從緩存中拿出value來返回客戶端。

注：查詢不會被解析、不會生成執(zhí)行計劃、不會被執(zhí)行。

1.3 查詢優(yōu)化處理，生成執(zhí)行計劃

如果沒有命中緩存，則開始第三步。

• 解析SQL：生成解析樹，驗證關(guān)鍵字如select,where,left join 等）是否正確。

• 預處理：進一步檢查解析樹是否合法，如檢查數(shù)據(jù)表和列是否存在，驗證用戶權(quán)限等。

• 優(yōu)化SQL：決定使用哪個索引，或者在多個表相關(guān)聯(lián)的時候決定表的連接順序。緊接著，將SQL語句轉(zhuǎn)成執(zhí)行計劃。

1.4 將查詢結(jié)果返回客戶端

最后，數(shù)據(jù)庫服務器將查詢結(jié)果返回給客戶端。(如果查詢可以緩存，MySQL也會將結(jié)果放到查詢緩存中)

這就是一條查詢語句的執(zhí)行流程，可以看到索引出現(xiàn)在優(yōu)化SQL的流程步驟中，接下來了解索引到底是什么？

二、索引概述

先簡單地了解一下索引的基本概念。

2.1 索引是什么

索引是幫助數(shù)據(jù)庫高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。

2.2 索引的分類

1）從存儲結(jié)構(gòu)上來劃分

• Btree索引（B+tree，B-tree)

• 哈希索引

• full-index全文索引

• RTree

2）從應用層次上來劃分

• 普通索引：即一個索引只包含單個列，一個表可以有多個單列索引。

• 唯一索引：索引列的值必須唯一，但允許有空值。

• 復合索引：一個索引包含多個列。

3）從表記錄的排列順序和索引的排列順序是否一致來劃分

• 聚集索引：表記錄的排列順序和索引的排列順序一致。

• 非聚集索引：表記錄的排列順序和索引的排列順序不一致。

2.3 聚集索引和非聚集索引

1）簡單概括

• 聚集索引：就是以主鍵創(chuàng)建的索引。

• 非聚集索引：就是以非主鍵創(chuàng)建的索引（也叫做二級索引）。

2）詳細概括

• 聚集索引

聚集索引表記錄的排列順序和索引的排列順序一致，所以查詢效率快，因為只要找到第一個索引值記錄，其余的連續(xù)性的記錄在物理表中也會連續(xù)存放，一起就可以查詢到。

缺點：新增比較慢，因為為了保證表中記錄的物理順序和索引順序一致，在記錄插入的時候，會對數(shù)據(jù)頁重新排序。

• 非聚集索引

索引的邏輯順序與磁盤上行的物理存儲順序不同，非聚集索引在葉子節(jié)點存儲的是主鍵和索引列，當我們使用非聚集索引查詢數(shù)據(jù)時，需要拿到葉子上的主鍵再去表中查到想要查找的數(shù)據(jù)。這個過程就是我們所說的回表。

3）聚集索引和非聚集索引的區(qū)別

• 聚集索引在葉子節(jié)點存儲的是表中的數(shù)據(jù)。

• 非聚集索引在葉子節(jié)點存儲的是主鍵和索引列。

舉個例子

比如漢語字典，想要查「阿」字，只需要翻到字典前幾頁，a開頭的位置，接著「啊」「愛」都會出來。也就是說，字典的正文部分本身就是一個目錄，不需要再去查其他目錄來找到需要找的內(nèi)容。我們把這種正文內(nèi)容本身就是一種按照一定規(guī)則排列的目錄稱為==聚集索引==。

如果遇到不認識的字，只能根據(jù)“偏旁部首”進行查找，然后根據(jù)這個字后的頁碼直接翻到某頁來找到要找的字。但結(jié)合部首目錄和檢字表而查到的字的排序并不是真正的正文的排序方法。

比如要查“玉”字，我們可以看到在查部首之后的檢字表中“玉”的頁碼是587頁，然后是玨，是251頁。很顯然，在字典中這兩個字并沒有挨著，現(xiàn)在看到的連續(xù)的“玉、玨、瑩”三字實際上就是他們在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我們可以通過這種方式來找到所需要的字，但它需要兩個過程，先找到目錄中的結(jié)果，然后再翻到結(jié)果所對應的頁碼。我們把這種目錄純粹是目錄，正文純粹是正文的排序方式稱為==非聚集索引==。

2.4 MySQL如何添加索引

1）添加PRIMARY KEY（主鍵索引）

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2）添加UNIQUE（唯一索引）

ALTER TABLE `table_name` ADD UNIQUE (`column`)

3）添加INDEX（普通索引）

ALTER TABLE `table_name` ADD INDEX index_name (`column` )

4）添加FULLTEXT（全文索引）

ALTER TABLE `table_name` ADD FULLTEXT (`column`)

5）添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name (`column1`,`column2`,`column3`)

三、索引底層數(shù)據(jù)結(jié)構(gòu)

了解了索引的基本概念后，可能最好奇的就是索引的底層是怎么實現(xiàn)的呢？為什么索引可以如此高效地進行數(shù)據(jù)的查找？如何設(shè)計數(shù)據(jù)結(jié)構(gòu)可以滿足我們的要求？ 下文通過一般程序員的思維來想一下如果是我們來設(shè)計索引，要如何設(shè)計來達到索引的效果。

3.1 哈希索引

可能直接想到的就是用哈希表來實現(xiàn)快速查找，就像我們平時用的hashmap一樣，value = get(key) O(1)時間復雜度一步到位，確實，哈希索引是一種方式。

1）定義

哈希索引就是采用一定的哈希算法，只需一次哈希算法即可立刻定位到相應的位置，速度非?？?。本質(zhì)上就是把鍵值換算成新的哈希值，根據(jù)這個哈希值來定位。

2）局限性

• 哈希索引沒辦法利用索引完成排序。

• 不能進行多字段查詢。

• 在有大量重復鍵值的情況下，哈希索引的效率也是極低的（出現(xiàn)哈希碰撞問題）。

• 不支持范圍查詢。

在MySQL常用的InnoDB引擎中，還是使用B+樹索引比較多。InnoDB是自適應哈希索引的（hash索引的創(chuàng)建由==InnoDB存儲引擎自動優(yōu)化創(chuàng)建==，我們干預不了）。

3.2 如何設(shè)計索引的數(shù)據(jù)結(jié)構(gòu)呢

假設(shè)要查詢某個區(qū)間的數(shù)據(jù)，我們只需要拿到區(qū)間的起始值，然后在樹中進行查找。

如數(shù)據(jù)為：

1）查詢[7,30]區(qū)間的數(shù)據(jù)

當查找到起點節(jié)點10后，再順著鏈表進行遍歷，直到鏈表中的節(jié)點數(shù)據(jù)大于區(qū)間的終止值為止。所有遍歷到的數(shù)據(jù)，就是符合區(qū)間值的所有數(shù)據(jù)。

2）還可以怎么優(yōu)化呢？

利用二叉查找樹，區(qū)間查詢的功能已經(jīng)實現(xiàn)了。但是，為了節(jié)省內(nèi)存，我們只能把樹存儲在硬盤中。

那么，每個節(jié)點的讀取或者訪問，都對應一次硬盤IO操作。每次查詢數(shù)據(jù)時磁盤IO操作的次數(shù)，也叫做==IO漸進復雜度==，也就是==樹的高度==。

所以，我們要減少磁盤IO操作的次數(shù)，也就是要==降低樹的高度==。

結(jié)構(gòu)優(yōu)化過程如下圖所示：

這里將二叉樹變?yōu)榱薓叉樹，降低了樹的高度，那么這個M應該選擇多少才合適呢？

問題：對于相同個數(shù)的數(shù)據(jù)構(gòu)建m叉樹索引，m叉樹中的m越大，那樹的高度就越小，那m叉樹中的m是不是越大越好呢？到底多大才合適呢？

不管是內(nèi)存中的數(shù)據(jù)還是磁盤中的數(shù)據(jù)，操作系統(tǒng)都是按頁（一頁的大小通常是4kb，這個值可以通過getconfig(PAGE_SIZE)命令查看）來讀取的，一次只會讀取一頁的數(shù)據(jù)。

如果要讀取的數(shù)據(jù)量超過了一頁的大小，就會觸發(fā)多次IO操作。所以在選擇m大小的時候，要盡量讓每個節(jié)點的大小等于一個頁的大小。

一般實際應用中，出度d（樹的分叉數(shù)）是非常大的數(shù)字，通常超過100；==樹的高度（h）非常小，通常不超過3==。

3.3 B樹

順著解決問題的思路知道了我們想要的數(shù)據(jù)結(jié)構(gòu)是什么。目前索引常用的數(shù)據(jù)結(jié)構(gòu)是B+樹，先介紹一下什么是B樹（也就是B-樹）。

1）B樹的特點：

• 關(guān)鍵字分布在整棵樹的所有節(jié)點。

• 任何一個關(guān)鍵字出現(xiàn)且只出現(xiàn)在一個節(jié)點中。

• 搜索有可能在非葉子節(jié)點結(jié)束。

• 其搜索性能等價于在關(guān)鍵字全集內(nèi)做一次二分查找。

如下圖所示：

3.4 B+樹

了解了B樹，再來看一下B+樹，也是MySQL索引大部分情況所使用的數(shù)據(jù)結(jié)構(gòu)。

1）B+樹基本特點

• 非葉子節(jié)點的子樹指針與關(guān)鍵字個數(shù)相同。

• 非葉子節(jié)點的子樹指針P[i]，指向關(guān)鍵字屬于 [k[i],K[i+1])的子樹（注意：區(qū)間是前閉后開)。

• 為所有葉子節(jié)點增加一個鏈指針。

• 所有關(guān)鍵字都在葉子節(jié)點出現(xiàn)。

這些基本特點是為了滿足以下的特性。

2）B+樹的特性

• 所有的關(guān)鍵字都出現(xiàn)在葉子節(jié)點的鏈表中，且鏈表中的關(guān)鍵字是有序的。

• 搜索只在葉子節(jié)點命中。

• 非葉子節(jié)點相當于是葉子節(jié)點的索引層，葉子節(jié)點是存儲關(guān)鍵字數(shù)據(jù)的數(shù)據(jù)層。

3）相對B樹，B+樹做索引的優(yōu)勢

• B+樹的磁盤讀寫代價更低。B+樹的內(nèi)部沒有指向關(guān)鍵字具體信息的指針，所以其內(nèi)部節(jié)點相對B樹更小，如果把所有關(guān)鍵字存放在同一塊盤中，那么盤中所能容納的關(guān)鍵字數(shù)量也越多，一次性讀入內(nèi)存的需要查找的關(guān)鍵字也就越多，相應的，IO讀寫次數(shù)就降低了。

• 樹的查詢效率更加穩(wěn)定。B+樹所有數(shù)據(jù)都存在于葉子節(jié)點，所有關(guān)鍵字查詢的路徑長度相同，每次數(shù)據(jù)的查詢效率相當。而B樹可能在非葉子節(jié)點就停止查找了，所以查詢效率不夠穩(wěn)定。

• B+樹只需要去遍歷葉子節(jié)點就可以實現(xiàn)整棵樹的遍歷。

3.5 MongoDB的索引為什么選擇B樹，而MySQL的索引是B+樹？

因為MongoDB不是傳統(tǒng)的關(guān)系型數(shù)據(jù)庫，而是以Json格式作為存儲的NOSQL非關(guān)系型數(shù)據(jù)庫，目的就是高性能、高可用、易擴展。擺脫了關(guān)系模型，所以范圍查詢和遍歷查詢的需求就沒那么強烈了。

3.6 MyISAM存儲引擎和InnoDB的索引有什么區(qū)別

1）MyISAM存儲引擎

• 主鍵索引

MyISAM的索引文件（.MYI）和數(shù)據(jù)文件（.MYD）文件是分離的，索引文件僅保存記錄所在頁的指針（物理位置），通過這些指針來讀取頁，進而讀取被索引的行。

樹中的葉子節(jié)點保存的是對應行的物理位置。通過該值，==存儲引擎能順利地進行回表查詢，得到一行完整記錄==。

同時，每個葉子也保存了指向下一個葉子的指針，從而方便葉子節(jié)點的范圍遍歷。

• 輔助索引

在MyISAM中，主鍵索引和輔助索引在結(jié)構(gòu)上沒有任何區(qū)別，==只是主鍵索引要求key是唯一的，而輔助索引的key可以重復==。

1）Innodb存儲引擎

Innodb的主鍵索引和輔助索引之前提到過，再回顧一次。

• 主鍵索引

InnoDB主鍵索引中既存儲了主健值，又存儲了行數(shù)據(jù)。

• 輔助索引

對于輔助索引，InnoDB采用的方式是在葉子節(jié)點中保存主鍵值，通過這個主鍵值來回表查詢到一條完整記錄，因此按輔助索引檢索其實進行了二次查詢，效率是沒有主鍵索引高的。

四、MySQL索引失效

在上一節(jié)中了解了索引的多種數(shù)據(jù)結(jié)構(gòu)，以及B樹和B+樹的對比等，大家應該對索引的底層實現(xiàn)有了初步的了解。這一節(jié)從應用層的角度出發(fā)，看一下如何建索引更能滿足我們的需求，以及MySQL索引什么時候會失效的問題。

先來思考一個小問題。

問題：當查詢條件為2個及2個以上時，是創(chuàng)建多個單列索引還是創(chuàng)建一個聯(lián)合索引好呢？它們之間的區(qū)別是什么？哪個效率高呢？

先來建立一些單列索引進行測試：

這里建立了一張表，里面建立了三個單列索引userId,mobile,billMonth。

然后進行多列查詢。

explain select * from `t_mobilesms_11` where userid = '1' and mobile = '13504679876' and billMonth = '1998-03'

我們發(fā)現(xiàn)查詢時只用到了userid這一個單列索引，這是為什么呢？因為這取決于MySQL優(yōu)化器的優(yōu)化策略。

當多條件聯(lián)合查詢時，優(yōu)化器會評估哪個條件的索引效率高，它會選擇最佳的索引去使用。也就是說，此處三個索引列都可能被用到，只不過優(yōu)化器判斷只需要使用userid這一個索引就能完成本次查詢，故最終explain展示的key為userid。

4.1 總結(jié)

多個單列索引在多條件查詢時優(yōu)化器會選擇最優(yōu)索引策略，可能只用一個索引，也可能將多個索引都用上。

但是多個單列索引底層會建立多個B+索引樹，比較占用空間，也會浪費搜索效率 所以多條件聯(lián)合查詢時最好建聯(lián)合索引。

那聯(lián)合索引就可以三個條件都用到了嗎？會出現(xiàn)索引失效的問題嗎？

4.2 聯(lián)合索引失效問題

該部分參考并引用文章：

一張圖搞懂MySQL的索引失效

創(chuàng)建user表，然后建立 name, age, pos, phone 四個字段的聯(lián)合索引 全值匹配（索引最佳）。

索引生效，這是最佳的查詢。

那么時候會失效呢？

1）違反最左匹配原則

最左匹配原則：最左優(yōu)先，以最左邊的為起點任何連續(xù)的索引都能匹配上，如不連續(xù)，則匹配不上。

如：建立索引為(a,b)的聯(lián)合索引，那么只查 where b = 2 則不生效。換句話說：如果建立的索引是(a,b,c)，也只有(a),(a,b),(a,b,c)三種查詢可以生效。

這里跳過了最左的name字段進行查詢，發(fā)現(xiàn)索引失效了。

遇到范圍查詢（>、<、between、like）就會停止匹配。

比如：a= 1 and b = 2 and c>3 and d =4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，因為c字段是一個范圍查詢，它之后的字段會停止匹配。

2）在索引列上做任何操作

如計算、函數(shù)、（手動或自動）類型轉(zhuǎn)換等操作，會導致索引失效而進行全表掃描。

explain select * from user where left(name,3) = 'zhangsan' and age =20

這里對name字段進行了left函數(shù)操作，導致索引失效。

3）使用不等于（!= 、<>）

explain select * from user where age != 20;

explain select * from user where age <> 20;

4）like中以通配符開頭('%abc')

索引失效

explain select * from user where name like ‘%zhangsan’;

索引生效

explain select * from user where name like ‘zhangsan%’;

5）字符串不加單引號索引失效

explain select * from user where name = 2000;

6）or連接索引失效

explain select * from user where name = ‘2000’ or age = 20 or pos =‘cxy’;

7）order by

正常（索引參與了排序），沒有違反最左匹配原則。

explain select * from user where name = 'zhangsan' and age = 20 order by age,pos;

違反最左前綴法則，導致額外的文件排序（會降低性能）。

explain select name,age from user where name = 'zhangsan' order by pos;

8）group by

正常（索引參與了排序）。

explain select name,age from user where name = 'zhangsan' group by age;

違反最左前綴法則，導致產(chǎn)生臨時表（會降低性能）。

explain select name,age from user where name = 'zhangsan' group by pos,age;

• 了解一條查詢語句是如何執(zhí)行的，發(fā)現(xiàn)建立索引是一種可以高效查找的數(shù)據(jù)結(jié)構(gòu)。

• 了解了索引的各種分類情況，聚集索引和非聚集索引的區(qū)別，如何創(chuàng)建各種索引。

• 通過需求一步步分析出為什么MySQL要選b+tree作為索引的數(shù)據(jù)結(jié)構(gòu)，對比了btree和b+tree的區(qū)別、 MyISAM和innodb中索引的區(qū)別。

• 了解了索引會失效的多種情況，比較重要的最左匹配原則，相應地我們可以在建索引的時候做一些優(yōu)化。

關(guān)于如何深入理解MySQL索引就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。

文章名稱：如何深入理解MySQL索引
本文來源：http://bm7419.com/article2/igddic.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供響應式網(wǎng)站、微信小程序、標簽優(yōu)化、小程序開發(fā)、網(wǎng)站內(nèi)鏈、域名注冊

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)