索引簡(jiǎn)單介紹

索引的本質(zhì)：

• MySQL索引或者說(shuō)其他關(guān)系型數(shù)據(jù)庫(kù)的索引的本質(zhì)就只有一句話(huà)，以空間換時(shí)間。

索引的作用：

• 索引關(guān)系型數(shù)據(jù)庫(kù)為了加速對(duì)表中行數(shù)據(jù)檢索的（磁盤(pán)存儲(chǔ)的）數(shù)據(jù)結(jié)構(gòu)

索引的分類(lèi)

數(shù)據(jù)結(jié)構(gòu)上面的分類(lèi)：

• HASH 索引
  • 等值匹配效率高
  • 不支持范圍查找
• 樹(shù)形索引
  • 二叉樹(shù)，遞歸二分查找法，左小右大
  • 平衡二叉樹(shù)，二叉樹(shù)到平衡二叉樹(shù)，主要原因是左旋右旋
  • 缺點(diǎn)1，IO次數(shù)過(guò)多
  • 缺點(diǎn)2，IO利用率不高，IO飽和度
• 多路平衡查找樹(shù)（B-Tree）
  • 特點(diǎn)，大大的減少了樹(shù)的高度
• B+樹(shù)
  • 特點(diǎn)，采用左閉合的比較方式
  • 根節(jié)點(diǎn)支節(jié)點(diǎn)沒(méi)有數(shù)據(jù)區(qū)，只有葉子結(jié)點(diǎn)才包含數(shù)據(jù)區(qū)（說(shuō)白了就是即便在根節(jié)點(diǎn)和子節(jié)點(diǎn)已經(jīng)定位到，因?yàn)闆](méi)有數(shù)據(jù)區(qū)的原因也不會(huì)停留，會(huì)一直找到葉子結(jié)點(diǎn)為止。）

當(dāng)我們搜索13這條數(shù)據(jù)時(shí)，在根節(jié)點(diǎn)和子節(jié)點(diǎn) 都能定位，但是一直會(huì)找到葉子結(jié)點(diǎn)。

二叉樹(shù)平衡二叉樹(shù)，B樹(shù)對(duì)比:

如圖顯示如果是自增主鍵情況下：

二叉樹(shù)顯然不適合做關(guān)系型數(shù)據(jù)庫(kù)索引（和全表掃描沒(méi)什么區(qū)別）。

平衡二叉樹(shù)呢，雖然解決了這種情況，但是同樣會(huì)導(dǎo)致這棵樹(shù)，又瘦又高，這同樣會(huì)造成上文所提到查詢(xún)IO次數(shù)過(guò)多以及IO利用率不高。

B樹(shù)呢，顯然已經(jīng)解決了這兩個(gè)問(wèn)題，所以下文來(lái)解釋?zhuān)瑸槭裁丛谶@種情況下MySQL還用了B+樹(shù)，又做了那些增強(qiáng)。

B樹(shù)和B+樹(shù)比較:

B+樹(shù)在B樹(shù)上面的優(yōu)化:

IO效率更高（B樹(shù)每個(gè)節(jié)點(diǎn)都會(huì)保留數(shù)據(jù)區(qū)，而B(niǎo)+樹(shù)則不會(huì)，假設(shè)我們查詢(xún)一條數(shù)據(jù)要遍歷三層，那么顯然B+樹(shù)查詢(xún)中IO消耗更?。?/p>

范圍查找效率更高（如圖，B+樹(shù)已經(jīng)形成了一個(gè)天然鏈表形式，只需要根據(jù)最結(jié)尾的鏈?zhǔn)浇Y(jié)構(gòu)查找）

基于索引的數(shù)據(jù)掃描效率更高。

索引類(lèi)型的分類(lèi)

索引類(lèi)型可分為兩類(lèi):

• 主鍵索引
• 輔佐索引(二級(jí)索引)
  • 唯一性索引
  • 復(fù)合索引
  • 普通索引
  • 覆蓋索引

主鍵索引相對(duì)來(lái)說(shuō)性能是最好的，但是對(duì)于SQL優(yōu)化，其實(shí)大多時(shí)候我們都在輔佐索引上面做一些改進(jìn)和補(bǔ)充。

B+樹(shù)在儲(chǔ)存引擎層面落地

• 我們創(chuàng)建兩個(gè)表分別為test_innodb（采用InnoDB作為儲(chǔ)存引擎）test_myisam（采用MyISAM作為儲(chǔ)存引擎）下圖是兩張表磁盤(pán)落地的相關(guān)文件，這兩個(gè)儲(chǔ)存引擎在B+樹(shù)磁盤(pán)落地式截然不同的。

B+樹(shù)在MyISAM落地：

• *.frm文件是表格骨架文件比如這個(gè)表中的id字段name字段是什么類(lèi)型的存儲(chǔ)在這里
• *.MYD（D=data）則儲(chǔ)存數(shù)據(jù)
• *.MYI （I=index）則儲(chǔ)存索引

• 比如現(xiàn)在執(zhí)行如下sql語(yǔ)句 ，那么在MyISAM中他就是先在test_myisam.MYI中查找到103然后拿到0x194281這個(gè)地址然后再去test_myisam.MYD中找到這個(gè)數(shù)據(jù)返回。

SELECT id,name from test_myisam where id =103

• 如果test_myisam表中，id為主鍵索引，name也是一個(gè)索引，那么在test_myisam.MYI中則會(huì)有兩個(gè)平級(jí)的B+樹(shù)，這也導(dǎo)致MyISAM引擎中主鍵索引和二級(jí)索引是沒(méi)有主次之分的，是平級(jí)關(guān)系。因?yàn)檫@種機(jī)制在MyISAM引擎中，有可能使用多個(gè)索引，在InnoDB中則不會(huì)出現(xiàn)這種情況。

B+樹(shù)在InnoDB落地：

• InnoDB不像MyISAM來(lái)獨(dú)立一個(gè)MYD 文件來(lái)存儲(chǔ)數(shù)據(jù)，它的數(shù)據(jù)直接存儲(chǔ)在葉子結(jié)點(diǎn)關(guān)鍵字對(duì)應(yīng)的數(shù)據(jù)區(qū)在這保存這一個(gè)id列所有行的詳細(xì)記錄。
• InnoDB 主鍵索引和輔助索引關(guān)系

我們現(xiàn)在執(zhí)行如下SQL語(yǔ)句,他會(huì)先去找輔助索引，然后找到輔助索引下101的主鍵，再去回表（二次掃描）根據(jù)主鍵索引查詢(xún)103這條數(shù)據(jù)將其返回。

SELECT id,name from test_myisam where name ='zhangsan'

這里就有一個(gè)問(wèn)題了，為什么不像MyISAM在輔助索引下直接記錄磁盤(pán)地址，而是要多此一舉再去回表掃描主鍵索引，這個(gè)問(wèn)題在下面相關(guān)面試題中回答，記一下這個(gè)問(wèn)題是這里來(lái)的。

相關(guān)面試題

• 為什么MySQL選擇B+樹(shù)作為索引結(jié)構(gòu)

這個(gè)就不說(shuō)了，上文應(yīng)該講清楚了。

• B+樹(shù)在MyISAM和InnoDB落地區(qū)別。

這個(gè)可以總結(jié)一下，MyISAM落地?cái)?shù)據(jù)儲(chǔ)存會(huì)有三個(gè)類(lèi)型文件 ，.frm文件是表骨架文件，.MYD（D=data）則儲(chǔ)存數(shù)據(jù) ，.MYI （I=index）則儲(chǔ)存索引，MyISAM引擎中主鍵索引和二級(jí)索引平級(jí)關(guān)系，在MyISAM引擎中，有可能使用多個(gè)索引，InnoDB則相反，主鍵索引和二級(jí)索有嚴(yán)格的主次之分在InnoDB一條語(yǔ)句只能用一個(gè)索引要么不用。

• 如何判斷一條sql語(yǔ)句是否使用了索引。

可以通過(guò)執(zhí)行計(jì)劃來(lái)判斷 可以在sql語(yǔ)句前explain/ desc

set global optimizer_trace='enabled=on' 打開(kāi)執(zhí)行計(jì)劃開(kāi)關(guān)他將會(huì)把每一條查詢(xún)sql執(zhí)行計(jì)劃記錄在information_schema 庫(kù)中OPTIMIZER_TRACE表中

• 為什么主鍵索引最好選擇自增列？

自增列，數(shù)據(jù)插入時(shí)整個(gè)索引樹(shù)是只有右邊在增加的，相對(duì)來(lái)說(shuō)索引樹(shù)的變動(dòng)更小。

• 為什么經(jīng)常變動(dòng)的列不建議使用索引？

和上一個(gè)問(wèn)題原因一樣，當(dāng)一個(gè)索引經(jīng)常發(fā)生變化，那么就意味這，這個(gè)縮印樹(shù)也要經(jīng)常發(fā)生變化。4

• 為什么說(shuō)重復(fù)度高的列，不建議建立索引？

這個(gè)原因是因?yàn)殡x散性，比如說(shuō)，一張一百萬(wàn)數(shù)據(jù)的表，其中一個(gè)字段代表性別，0代表男1代表女，把這字段加了索引，那么在索引樹(shù)上，將會(huì)有大量的重復(fù)數(shù)據(jù)。而我們常見(jiàn)的索引建立一般都是驅(qū)動(dòng)型的。其目的是，盡可能的刪減數(shù)據(jù)的查詢(xún)范圍，這個(gè)顯然是不匹配的。

• 什么是聯(lián)合索引

聯(lián)合索引是一個(gè)包含了多個(gè)功效的索引，他只是一個(gè)索引而不是多個(gè)，

其次，單列索引是一種特殊的聯(lián)合索引

聯(lián)合索引的創(chuàng)立要遵循最左前置原則（最常用列>離散度>占用空間?。?/p>

• 什么是覆蓋索引

通過(guò)索引項(xiàng)信息可直接返回所需要查詢(xún)的索引列，該索引被稱(chēng)之為覆蓋索引，說(shuō)白了就是不需要做回表操作，可以從二級(jí)索引中直接取到所需數(shù)據(jù)。

• 什么是ICP機(jī)制

索引下推，簡(jiǎn)單點(diǎn)來(lái)說(shuō)就是，在sql執(zhí)行過(guò)程中，面對(duì)where多條件過(guò)濾時(shí)，通過(guò)一個(gè)索引，完成數(shù)據(jù)搜索和過(guò)濾條件其，特點(diǎn)能減少io操作。

• 在InnoDB表中不可能沒(méi)有主鍵對(duì)還是不對(duì)原因是什么？

首先這句話(huà)是對(duì)的，但是情況有三種:

• 就是在你手動(dòng)顯式指定這一個(gè)字段為主鍵時(shí)候，會(huì)以這一個(gè)字段為聚集索引。
• 在沒(méi)有顯式指定主鍵時(shí)候有兩種情況：
• 他會(huì)尋找第一個(gè)UK（unique key）作為主鍵索引組織索引編排。
• 如果既沒(méi)有指定主鍵也沒(méi)有UK的情況下，此時(shí)會(huì)以rowId（在InnoDB表中每一個(gè)記錄都會(huì)有一個(gè)隱藏（6byte）的rowId）為聚集索引。

• 什么是回表操作

在InnoDB 中基于輔助索引查詢(xún)的內(nèi)容，從輔助索引中無(wú)法直接獲取，需要基于主鍵索引的二次掃描的操作叫做回表操作。

• 為什么在InnoDB 中輔助索引葉子結(jié)點(diǎn)數(shù)據(jù)區(qū)記錄的是主鍵索引的值而不是像MyISAM中去記錄磁盤(pán)地址。

這個(gè)原因其實(shí)很簡(jiǎn)單，因?yàn)橹麈I索引的數(shù)據(jù)結(jié)構(gòu)是會(huì)經(jīng)常發(fā)生變化的，如果在輔助索引數(shù)據(jù)區(qū)記錄磁盤(pán)地址，那么假設(shè)我們有10個(gè)輔助索引，當(dāng)我們主鍵索引結(jié)構(gòu)發(fā)生變化后，還要一個(gè)個(gè)去通知輔助索引，且主鍵索引結(jié)構(gòu)是經(jīng)常發(fā)生變化的，增刪都有可能影響他的
數(shù)據(jù)結(jié)構(gòu)。

 到此這篇關(guān)于MySQL優(yōu)化及索引解析的文章就介紹到這了,更多相關(guān)MySQL優(yōu)化索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！