深入講解MySQL Innodb索引的原理
引言
回想四年前,我在學(xué)習(xí)mysql的索引這塊的時候,老師在講索引的時候,是像下面這么說的
索引就像一本書的目錄。而當(dāng)用戶通過索引查找數(shù)據(jù)時,就好比用戶通過目錄查詢某章節(jié)的某個知識點(diǎn)。這樣就幫助用戶有效地提高了查找速度。所以,使用索引可以有效地提高數(shù)據(jù)庫系統(tǒng)的整體性能。
嗯,這么說其實也對。但是呢,大家看完這種說法,其實可能還是覺得太抽象了!因此呢,我還想再深入的細(xì)說一下,所以就有了此文!
需要說明的是,我說的內(nèi)容只在Mysql的Innodb引擎中是成立的。在Sql Server、oracle、Mysql的Mysiam引擎中的正確性,不一定成立!
InnoDB是 MySQL最常用的存儲引擎,了解InnoDB存儲引擎的索引對于日常工作有很大的益處,索引的存在便是為了加速數(shù)據(jù)庫行記錄的檢索。
什么是索引?
索引(index)翻譯為一個目錄,用于快速定位我們想要找的數(shù)據(jù)的位置。例如:我們把一個數(shù)據(jù)庫比作一本書,而索引(index)就是書中的目錄,此刻要找到書的某個感興趣的內(nèi)容,我們一般是不會整本書翻完再去確認(rèn)該內(nèi)容在哪里,而是通過書的目錄,定位到該內(nèi)容章節(jié)所在頁數(shù),最后直接翻到該頁面。
我們來看看在數(shù)據(jù)庫中的索引:
全表掃描 VS 索引掃描
以字典為例,全表掃描就是如果我們查找某個字時,那么通讀一遍新華字典,然后找到我們想要找到的字,而跟全表掃描相對應(yīng)的就是索引查找,索引查找就是在表的索引部分找到我們想要找的數(shù)據(jù)具體位置,然后會到表里面將我們想要找的數(shù)據(jù)全部查出。
OK,廢話不多說,開始啰嗦!
正文
索引的科普
先引進(jìn)聚簇索引和非聚簇索引的概念!
我們平時在使用的Mysql中,使用下述語句
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON tbl_name (index_col_name,...) index_col_name: col_name [(length)] [ASC | DESC]
創(chuàng)建的索引,如復(fù)合索引、前綴索引、唯一索引,都是屬于非聚簇索引,在有的書籍中,又將其稱為輔助索引(secondary index)。在后文中,我們稱其為非聚簇索引,其數(shù)據(jù)結(jié)構(gòu)為B+樹。
那么,這個聚簇索引,在Mysql中是沒有語句來另外生成的。在Innodb中,Mysql中的數(shù)據(jù)是按照主鍵的順序來存放的。那么聚簇索引就是按照每張表的主鍵來構(gòu)造一顆B+樹,葉子節(jié)點(diǎn)存放的就是整張表的行數(shù)據(jù)。由于表里的數(shù)據(jù)只能按照一顆B+樹排序,因此一張表只能有一個聚簇索引。
在Innodb中,聚簇索引默認(rèn)就是主鍵索引。
這個時候,機(jī)智的讀者,應(yīng)該要問我
如果我的表沒建主鍵呢?
回答是,如果沒有主鍵,則按照下列規(guī)則來建聚簇索引
沒有主鍵時,會用一個唯一且不為空的索引列做為主鍵,成為此表的聚簇索引如果沒有這樣的索引,InnoDB會隱式定義一個主鍵來作為聚簇索引。
ps:大家還記得,自增主鍵和uuid作為主鍵的區(qū)別么?由于主鍵使用了聚簇索引,如果主鍵是自增id,,那么對應(yīng)的數(shù)據(jù)一定也是相鄰地存放在磁盤上的,寫入性能比較高。如果是uuid的形式,頻繁的插入會使innodb頻繁地移動磁盤塊,寫入性能就比較低了。
索引原理介紹
先來一張帶主鍵的表,如下所示,pId是主鍵
pId | name | birthday |
---|---|---|
5 | zhangsan | 2016-10-02 |
8 | lisi | 2015-10-04 |
11 | wangwu | 2016-09-02 |
13 | zhaoliu | 2015-10-07 |
畫出該表的結(jié)構(gòu)圖如下
如上圖所示,分為上下兩個部分,上半部分是由主鍵形成的B+樹,下半部分就是磁盤上真實的數(shù)據(jù)!那么,當(dāng)我們, 執(zhí)行下面的語句
select * from table where pId='11'
那么,執(zhí)行過程如下
如上圖所示,從根開始,經(jīng)過3次查找,就可以找到真實數(shù)據(jù)。如果不使用索引,那就要在磁盤上,進(jìn)行逐行掃描,直到找到數(shù)據(jù)位置。顯然,使用索引速度會快。但是在寫入數(shù)據(jù)的時候,需要維護(hù)這顆B+樹的結(jié)構(gòu),因此寫入性能會下降!
OK,接下來引入非聚簇索引!我們執(zhí)行下面的語句
create index index_name on table(name);
此時結(jié)構(gòu)圖如下所示
大家注意看,會根據(jù)你的索引字段生成一顆新的B+樹。因此, 我們每加一個索引,就會增加表的體積, 占用磁盤存儲空間。然而,注意看葉子節(jié)點(diǎn),非聚簇索引的葉子節(jié)點(diǎn)并不是真實數(shù)據(jù),它的葉子節(jié)點(diǎn)依然是索引節(jié)點(diǎn),存放的是該索引字段的值以及對應(yīng)的主鍵索引(聚簇索引)。
如果我們執(zhí)行下列語句
select * from table where name='lisi'
此時結(jié)構(gòu)圖如下所示
通過上圖紅線可以看出,先從非聚簇索引樹開始查找,然后找到聚簇索引后。根據(jù)聚簇索引,在聚簇索引的B+樹上,找到完整的數(shù)據(jù)!
那
什么情況不去聚簇索引樹上查詢呢?
還記得我們的非聚簇索引樹上存著該索引字段的值么。如果,此時我們執(zhí)行下面的語句
select name from table where name='lisi'
此時結(jié)構(gòu)圖如下
如上圖紅線所示,如果在非聚簇索引樹上找到了想要的值,就不會去聚簇索引樹上查詢。還記得,博主在《select的正確姿勢》提到的索引問題么:
當(dāng)執(zhí)行select col from table where col = ?,col上有索引的時候,效率比執(zhí)行select * from table where col = ? 速度快好幾倍!
看完上面的圖,你應(yīng)該對這句話有更深層的理解了。
那么這個時候,我們執(zhí)行了下述語句,又會發(fā)生什么呢?
create index index_birthday on table(birthday);
此時結(jié)構(gòu)圖如下
看到了么,多加一個索引,就會多生成一顆非聚簇索引樹。因此,很多文章才說,索引不能亂加。因為,有幾個索引,就有幾顆非聚簇索引樹!你在做插入操作的時候,需要同時維護(hù)這幾顆樹的變化!因此,如果索引太多,插入性能就會下降!
總結(jié)
講到這里,大家應(yīng)該清楚的明白索引的原理了!可能細(xì)節(jié)方面還不夠嚴(yán)謹(jǐn),但是我覺得一個研發(fā),理解到這里可以了,夠用了,畢竟我們也不是專業(yè)的DBA。
希望大家有所收獲!
相關(guān)文章
SQL Server 完整備份遇到的一個不常見的錯誤及解決方法
這篇文章給大家介紹了SQL Server 完整備份遇到的一個不常見的錯誤及解決方法,非常不錯,具有一定的參考借鑒價值,需要的朋友參考下吧2019-05-05MySQL利用profile分析慢sql詳解(group left join效率高于子查詢)
最近因為一個用了子查詢的sql語句查詢很慢,嚴(yán)重影響了性能,所以需要進(jìn)行優(yōu)化,下面這篇文章主要跟大家介紹了關(guān)于MySQL利用profile分析慢sql的相關(guān)資料,文中介紹的非常詳細(xì),需要的朋友們可以參考借鑒,下面來一起看看吧。2017-03-03MySQL?Binlog日志的記錄模式寫入機(jī)制文件操作詳解
這篇文章主要介紹了MySQL?Binlog日志的記錄模式寫入機(jī)制文件操作詳解,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪2023-07-07Mysql數(shù)據(jù)庫設(shè)計三范式實例解析
這篇文章主要介紹了Mysql數(shù)據(jù)庫設(shè)計三范式實例解析,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2020-04-04MySQL 8.0.20 Window10免安裝版配置及Navicat管理教程圖文詳解
這篇文章主要介紹了MySQL 8.0.20 Window10免安裝版配置及Navicat管理,本文通過圖文并茂的形式給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下2020-06-06