目錄索引的作用認(rèn)識(shí)磁盤(pán)MySql 與磁盤(pán)交互基本單位Page共識(shí)索引的理解主鍵有序問(wèn)題理解單個(gè)Page理解多個(gè)Page頁(yè)目錄單頁(yè)情況多頁(yè)情況總結(jié)索引的作用

索引是與效率掛鉤的，所以沒(méi)有索引，可能會(huì)存在問(wèn)題

索引：提高數(shù)據(jù)庫(kù)的性能，索引是物美價(jià)廉的東西了。不用加內(nèi)存，不用改程序，不用調(diào)sql，只要執(zhí)行正確的 create index ，查詢(xún)速度就可能提高成百上千倍。但是天下沒(méi)有免費(fèi)的午餐，查詢(xún)速度的提高是以插入、更新、刪除的速度為代價(jià)的，這些寫(xiě)操作，增加了大量的IO。所以它的價(jià)值，在于提高一個(gè)海量數(shù)據(jù)的檢索速度。

MySQL的服務(wù)器，本質(zhì)是在內(nèi)存中的，所有的數(shù)據(jù)庫(kù)的CURD操作，全部都是在內(nèi)存中進(jìn)行的！所以索引也是如此

提高算法效率的因素：

1.組織數(shù)據(jù)的方式

2.算法本身。

常見(jiàn)的索引分為以下幾種

主鍵索引(primary key)唯一索引(unique)普通索引(index)全文索引(fulltext)–解決中子文索引問(wèn)題

創(chuàng)建一個(gè)海量表，在查詢(xún)的時(shí)候，沒(méi)有索引時(shí)看一下會(huì)有什么問(wèn)題

查詢(xún)員工編號(hào)為998877的員工

select * from EMP where empno=998877;

可以看到耗時(shí)17.71秒，這還是在本機(jī)一個(gè)人來(lái)操作，在實(shí)際項(xiàng)目中，如果放在公網(wǎng)中，假如同時(shí)有1000個(gè)人并發(fā)查詢(xún)，那很可能就死機(jī)，這是非常可怕的事情。

解決方法，創(chuàng)建索引

alter table EMP add index(empno);

測(cè)試看查詢(xún)時(shí)間

時(shí)間變得非常快！這就是索引帶來(lái)的好處！

想認(rèn)識(shí)索引之前，我們非常有必要先了解一下磁盤(pán)。

認(rèn)識(shí)磁盤(pán)mysql與存儲(chǔ)

MySQL 給用戶(hù)提供存儲(chǔ)服務(wù)，而存儲(chǔ)的都是數(shù)據(jù)，數(shù)據(jù)在磁盤(pán)這個(gè)外設(shè)當(dāng)中。磁盤(pán)是計(jì)算機(jī)中的一個(gè)機(jī)械設(shè)備，相比于計(jì)算機(jī)其他電子元件，磁盤(pán)效率是比較低的，在加上IO本身的特征，可以知道，如何提交效率，是 MySQL 的一個(gè)重要話題。

磁盤(pán)中一個(gè)盤(pán)片

數(shù)據(jù)庫(kù)文件，本質(zhì)其實(shí)就是保存在磁盤(pán)的盤(pán)片當(dāng)中。也就是上面的一個(gè)個(gè)小格子中，就是我們經(jīng)常所說(shuō)的扇區(qū)。當(dāng)然，數(shù)據(jù)庫(kù)文件很大，也很多，一定需要占據(jù)多個(gè)扇區(qū)

我們?cè)谑褂肔inux，所看到的大部分目錄或者文件，其實(shí)就是保存在硬盤(pán)當(dāng)中的。(當(dāng)然，有一些內(nèi)存文件系統(tǒng)，如： proc ， sys 之類(lèi)，我們不考慮)

所以，最基本的，找到一個(gè)文件的全部，本質(zhì)，就是在磁盤(pán)找到所有保存文件的扇區(qū)。而我們能夠定位任何一個(gè)扇區(qū)，那么便能找到所有扇區(qū)，因?yàn)椴檎曳绞绞且粯拥?/p>定位扇區(qū)

柱面(磁道): 多盤(pán)磁盤(pán)，每盤(pán)都是雙面，大小完全相等。那么同半徑的磁道，整體上便構(gòu)成了一個(gè)柱面

每個(gè)盤(pán)面都有一個(gè)磁頭，那么磁頭和盤(pán)面的對(duì)應(yīng)關(guān)系便是1對(duì)1的

所以，我們只需要知道，磁頭（Heads）、柱面(Cylinder)(等價(jià)于磁道)、扇區(qū)(Sector)對(duì)應(yīng)的編號(hào)。即可在磁盤(pán)上定位所要訪問(wèn)的扇區(qū)。這種磁盤(pán)數(shù)據(jù)定位方式叫做 CHS 。不過(guò)實(shí)際系統(tǒng)軟件使用的并不是 CHS （但是硬件是），而是 LBA ，一種線性地址，可以想象成虛擬地址與物理地址。系統(tǒng)將 LBA 地址最后會(huì)轉(zhuǎn)化成為 CHS ，交給磁盤(pán)去進(jìn)行數(shù)據(jù)讀取。不過(guò)，我們現(xiàn)在不關(guān)心轉(zhuǎn)化細(xì)節(jié)。

結(jié)論

我們現(xiàn)在已經(jīng)能夠在硬件層面定位，任何一個(gè)基本數(shù)據(jù)塊了(扇區(qū))。但是在系統(tǒng)軟件上，就不是直接按照扇區(qū)(512字節(jié)，部分4096字節(jié)),進(jìn)行IO交互了，這是因?yàn)槿绻僮飨到y(tǒng)直接使用硬件提供的數(shù)據(jù)大小進(jìn)行交互，那么系統(tǒng)的IO代碼，就和硬件強(qiáng)相關(guān)，換言之，如果硬件發(fā)生變化，系統(tǒng)必須跟著變化；另外目前來(lái)看，單次IO 512字節(jié)，還是太小了。IO單位小，意味著讀取同樣的數(shù)據(jù)內(nèi)容，需要進(jìn)行多次磁盤(pán)訪問(wèn)，會(huì)帶來(lái)效率的降低；

文件系統(tǒng)讀取基本單位，就不是扇區(qū)，而是數(shù)據(jù)塊。既系統(tǒng)讀取磁盤(pán)，是以塊為單位的，基本單位是 4KB

磁盤(pán)隨機(jī)訪問(wèn)(Random Access)與連續(xù)訪問(wèn)(Sequential Access)

隨機(jī)訪問(wèn)：本次IO所給出的扇區(qū)地址和上次IO給出扇區(qū)地址不連續(xù)，這樣的話磁頭在兩次IO操作之間需要作比較大的移動(dòng)動(dòng)作才能重新開(kāi)始讀/寫(xiě)數(shù)據(jù)。連續(xù)訪問(wèn)：如果當(dāng)次IO給出的扇區(qū)地址與上次IO結(jié)束的扇區(qū)地址是連續(xù)的，那磁頭就能很快的開(kāi)始這次IO操作，這樣的多個(gè)IO操作稱(chēng)為連續(xù)訪問(wèn)。因此盡管相鄰的兩次IO操作在同一時(shí)刻發(fā)出，但如果它們的請(qǐng)求的扇區(qū)地址相差很大的話也只能稱(chēng)為隨機(jī)訪問(wèn)，而非連續(xù)訪問(wèn)。磁盤(pán)是通過(guò)機(jī)械運(yùn)動(dòng)進(jìn)行尋址的，隨機(jī)訪問(wèn)不需要過(guò)多的定位，故效率比較高

MySql 與磁盤(pán)交互基本單位Page

MySql 作為一款應(yīng)用軟件，可以想象成一種特殊的文件系統(tǒng)。它有著更高的IO場(chǎng)景，所以，為了提高基本的IO效率， MySql 進(jìn)行IO的基本單位是16KB:MySql是應(yīng)用層服務(wù)，是不可能直接訪問(wèn)硬件的，這個(gè)16KB是站在MySql角度向OS提出來(lái)的，OS內(nèi)部存在文件緩沖區(qū)的,MySql進(jìn)入到某一個(gè)目錄，對(duì)某張表做CURD，對(duì)某張表內(nèi)部做增刪查改，在MySql就得到了文件的fd,一個(gè)文件被打開(kāi)有自己的結(jié)構(gòu)體，緩沖區(qū)；MySql以16KB為單位與文件緩沖區(qū)進(jìn)行IO。

show global status like 'innodb_page_size';-- 16382/1024=16KB

也就是說(shuō)，磁盤(pán)這個(gè)硬件設(shè)備的基本單位是 512 字節(jié)，而 MySQL InnoDB引擎 使用 16KB 進(jìn)行IO交互。即， MySQL 和磁盤(pán)進(jìn)行數(shù)據(jù)交互的基本單位是 16KB 。這個(gè)基本數(shù)據(jù)單元，在 MySQL 這里叫做page（注意和系統(tǒng)的page區(qū)分）

共識(shí)

MySQL 中的數(shù)據(jù)文件，是以page為單位保存在磁盤(pán)當(dāng)中的。

MySQL 的 CURD 操作，都需要通過(guò)計(jì)算，找到對(duì)應(yīng)的插入位置，或者找到對(duì)應(yīng)要修改或者查詢(xún)的數(shù)據(jù)

只要涉及計(jì)算，就需要CPU參與，而為了便于CPU參與，一定要能夠先將數(shù)據(jù)移動(dòng)到內(nèi)存當(dāng)中

所以在特定時(shí)間內(nèi)，數(shù)據(jù)一定是磁盤(pán)中有，內(nèi)存中也有。后續(xù)操作完內(nèi)存數(shù)據(jù)之后，以特定的刷新策略，刷新到磁盤(pán)。而這時(shí)，就涉及到磁盤(pán)和內(nèi)存的數(shù)據(jù)交互，也就是IO。此時(shí)IO的基本單位就是Page。

為了更好的進(jìn)行上面的操作， MySQL 服務(wù)器在內(nèi)存中運(yùn)行的時(shí)候，在服務(wù)器內(nèi)部，就申請(qǐng)了被稱(chēng)為 Buffer Pool 的的大內(nèi)存空間，來(lái)進(jìn)行各種緩存。其實(shí)就是很大的內(nèi)存空間，來(lái)和磁盤(pán)數(shù)據(jù)進(jìn)行IO交互

為了更高的效率，一定要盡可能的減少系統(tǒng)和磁盤(pán)IO的次數(shù)

索引的理解

創(chuàng)建一張表：

create table if not exists user (id int primary key, age int not null,name varchar(16) not null);

現(xiàn)在往表里插入5條數(shù)據(jù)，結(jié)果如下：插入的數(shù)據(jù)是無(wú)序的，但是查看結(jié)果卻是有序的！

mysql> insert into user (id, age, name) values(3, 18, '楊過(guò)');Query OK, 1 row affected (0.00 sec)mysql> insert into user (id, age, name) values(4, 16, '小龍女');Query OK, 1 row affected (0.04 sec)mysql> insert into user (id, age, name) values(2, 26, '黃蓉');Query OK, 1 row affected (0.03 sec)mysql> insert into user (id, age, name) values(5, 36, '郭靖');Query OK, 1 row affected (0.02 sec)mysql> insert into user (id, age, name) values(1, 56, '歐陽(yáng)鋒');Query OK, 1 row affected (0.03 sec)

主鍵有序問(wèn)題

我們向一個(gè)具有主鍵的表中，亂序插入數(shù)據(jù)，發(fā)現(xiàn)數(shù)據(jù)會(huì)自動(dòng)排序，這是誰(shuí)做的，為什么要這樣做？

對(duì)于這個(gè)問(wèn)題的回答，我們來(lái)看一看。

首先磁盤(pán)上有對(duì)應(yīng)的文件數(shù)據(jù)，文件數(shù)據(jù)最終會(huì)被預(yù)讀到文件緩沖區(qū)，mysql啟動(dòng)的時(shí)候會(huì)申請(qǐng)buffer pool,mysql層面上，所有的page都會(huì)被放到buffer pool中，理解mysql中page的概念：一個(gè)page是16KB，mysql內(nèi)部一定需要并且會(huì)存在大量的page,也就決定了mysql必須要將多個(gè)同時(shí)存在的page管理起來(lái)。要管理所有的mysql內(nèi)的page,需要先描述，在組織，所以不要簡(jiǎn)單將page認(rèn)為是一個(gè)內(nèi)存塊，page內(nèi)部也必須寫(xiě)入對(duì)應(yīng)的管理信息！如：

struct page{struct page*next;struct page*prev;char buffer[NUM];};-- 16KB,

所謂的申請(qǐng)，在mysql申請(qǐng)的page就是new page,在用簡(jiǎn)單的方式：比如將所有的page用鏈表的形式管理起來(lái)。在buffer pool內(nèi)部，對(duì)mysql中的page進(jìn)行了一個(gè)建模。

了解一下：MySQL和磁盤(pán)進(jìn)行IO交互的時(shí)候，采用Page的方案進(jìn)行交互

為什么MySQL和磁盤(pán)進(jìn)行IO交互的時(shí)候，要采用Page的方案進(jìn)行交互?用多少，加載多少不可以嗎?如上面的5條記錄，如果MySQL要查找id=2的記錄，第一次加載id=1，第二次加載id=2，一次一條記錄，那么就需要2次IO。如果要找id=5，那么就需要5次IO。但，如果這5條(或者更多)都被保存在一個(gè)Page中(16KB，能保存很多記錄),那么第一次IO查找id=2的時(shí)候，整個(gè)Page會(huì)被加載到MySQL的Buffer Pool中，這里完成了一次IO。但是往后如果在查找id=1,3,4,5等，完全不需要進(jìn)行IO了，而是直接在內(nèi)存中進(jìn)行了。所以，就在單Page里面，大大減少了IO的次數(shù)。

怎么保證，用戶(hù)一定下次找的數(shù)據(jù)，就在這個(gè)Page里面？我們不能?chē)?yán)格保證，但是有很大概率，因?yàn)橛芯植啃栽怼Ｍ鵌O效率低下的最主要矛盾不是IO單次數(shù)據(jù)量的大小，而是IO的次數(shù)

理解單個(gè)Page

MySQL 中要管理很多數(shù)據(jù)表文件，而要管理好這些文件，就需要先描述，在組織 ,我們目前可以簡(jiǎn)單理解成一個(gè)個(gè)獨(dú)立文件是有一個(gè)或者多個(gè)Page構(gòu)成的，這在我們一開(kāi)始也有說(shuō)到

對(duì)于不同的 Page ，在 MySQL 中，都是 16KB ，使用 prev 和 next 構(gòu)成雙向鏈表因?yàn)橛兄麈I的問(wèn)題， MySQL 會(huì)默認(rèn)按照主鍵給我們的數(shù)據(jù)進(jìn)行排序，從上面的Page內(nèi)數(shù)據(jù)記錄可以看出，數(shù)據(jù)是有序且彼此關(guān)聯(lián)的。

**插入數(shù)據(jù)時(shí)排序的目的，就是優(yōu)化查詢(xún)的效率。**頁(yè)內(nèi)部存放數(shù)據(jù)的模塊，實(shí)質(zhì)上也是一個(gè)鏈表的結(jié)構(gòu)，鏈表的特點(diǎn)也就是增刪快，查詢(xún)修改慢，所以?xún)?yōu)化查詢(xún)的效率是必須的。正式因?yàn)橛行颍诓檎业臅r(shí)候，從頭到后都是有效查找，沒(méi)有任何一個(gè)查找是浪費(fèi)的，而且，如果運(yùn)氣好，是可以提前結(jié)束查找過(guò)程的。

理解多個(gè)Page

上面頁(yè)模式中，只有一個(gè)功能，就是在查詢(xún)某條數(shù)據(jù)的時(shí)候直接將一整頁(yè)的數(shù)據(jù)加載到內(nèi)存中，以減少硬盤(pán)IO次數(shù)，從而提高性能。但是，我們也可以看到，現(xiàn)在的頁(yè)模式內(nèi)部，實(shí)際上是采用了鏈表的結(jié)構(gòu)，前一條數(shù)據(jù)指向后一條數(shù)據(jù)，本質(zhì)上還是通過(guò)數(shù)據(jù)的逐條比較來(lái)取出特定的數(shù)據(jù)；如果有1千萬(wàn)條數(shù)據(jù)，一定需要多個(gè)Page來(lái)保存1千萬(wàn)條數(shù)據(jù)，多個(gè)Page彼此使用雙鏈表鏈接起來(lái)，而且每個(gè)Page內(nèi)部的數(shù)據(jù)也是基于鏈表的。那么，查找特定一條記錄，也一定是線性查找。效率也太低了。

所以要提高效率，我們要有兩個(gè)角度進(jìn)行考量：第一個(gè)角度是在單page的時(shí)候如何提高一個(gè)Page內(nèi)部的鏈?zhǔn)奖闅v的效率；另一個(gè)是多Page的時(shí)候怎么解決Page間進(jìn)行查找的效率。所以我們有了頁(yè)目錄的出現(xiàn)。

頁(yè)目錄

比如我們?cè)诳础盾浖こ虒?dǎo)論》這本書(shū)的時(shí)候，如果我們要看軟件生命周期這個(gè)章節(jié)，找到該章節(jié)有兩種做法從頭逐頁(yè)的向后翻，直到找到目標(biāo)內(nèi)容；通過(guò)書(shū)提供的目錄，發(fā)現(xiàn)軟件生命周期章節(jié)在11頁(yè)(假設(shè))，那么我們便直接翻到11頁(yè)。同時(shí)，查找目錄的方案，可以順序找，不過(guò)因?yàn)槟夸浀捻?yè)數(shù)肯定少，所以可以快速提高定位本質(zhì)上，書(shū)中的目錄，是多花了紙張的，但是卻提高了效率；所以，目錄，是一種“空間換時(shí)間的做法”

單頁(yè)情況

對(duì)于上面的單頁(yè)P(yáng)age，我們也可以引入目錄

在一個(gè)Page內(nèi)部，我們引入了目錄。比如，我們要查找id=4記錄，之前必須線性遍歷4次，才能拿到結(jié)果。現(xiàn)在直接通過(guò)目錄2[3]，直接進(jìn)行定位新的起始位置，提高了效率。

所以對(duì)于一開(kāi)始的問(wèn)題，我們可以正式回答了：主鍵插入無(wú)序的，但是結(jié)果是有序的，很簡(jiǎn)單，只有把數(shù)據(jù)變成有序的，能夠方便我們引入頁(yè)內(nèi)目錄！

多頁(yè)情況

MySQL 中每一頁(yè)的大小只有 16KB ，單個(gè)Page大小固定，所以隨著數(shù)據(jù)量不斷增大， 16KB 不可能存下所有的數(shù)據(jù)，那么必定會(huì)有多個(gè)頁(yè)來(lái)存儲(chǔ)數(shù)據(jù)。

在單表數(shù)據(jù)不斷被插入的情況下， MySQL 會(huì)在容量不足的時(shí)候，自動(dòng)開(kāi)辟新的Page來(lái)保存新的數(shù)據(jù)，然后通過(guò)指針的方式，將所有的Page組織起來(lái) 對(duì)于上面的圖，是理想結(jié)構(gòu)，要保證整體有序，那么新插入的數(shù)據(jù)，不一定會(huì)在新Page上面，這里僅僅做演示。這樣，我們就可以通過(guò)多個(gè)Page遍歷，Page內(nèi)部通過(guò)目錄來(lái)快速定位數(shù)據(jù)。可是，這樣也有效率問(wèn)題，在Page之間，也是需要 MySQL 遍歷的，遍歷意味著依舊需要進(jìn)行大量的IO，將下一個(gè)Page加載到內(nèi)存，進(jìn)行線性檢測(cè)。這樣就顯得我們之前的Page內(nèi)部的目錄，作用沒(méi)那么大了。

所以，我們給Page也帶上目錄。

使用一個(gè)目錄項(xiàng)來(lái)指向某一頁(yè)，而這個(gè)目錄項(xiàng)存放的就是將要指向的頁(yè)中存放的最小數(shù)據(jù)的鍵值。和頁(yè)內(nèi)目錄不同的地方在于，這種目錄管理的級(jí)別是頁(yè)，而頁(yè)內(nèi)目錄管理的級(jí)別是行。其中，每個(gè)目錄項(xiàng)的構(gòu)成是：鍵值+指針。

存在一個(gè)目錄頁(yè)來(lái)管理頁(yè)目錄，目錄頁(yè)中的數(shù)據(jù)存放的就是指向的那一頁(yè)中最小的數(shù)據(jù)。有數(shù)據(jù)，就可通過(guò)比較，找到該訪問(wèn)那個(gè)Page，進(jìn)而通過(guò)指針，找到下一個(gè)Page。其實(shí)目錄頁(yè)的本質(zhì)也是頁(yè)，普通頁(yè)中存的數(shù)據(jù)是用戶(hù)數(shù)據(jù)，而目錄頁(yè)中存的數(shù)據(jù)是普通頁(yè)的地址。但是我們每次檢索數(shù)據(jù)的時(shí)候，應(yīng)該從哪里開(kāi)始，雖然頂層的目錄頁(yè)少了，但是還需要遍歷，不用擔(dān)心，可以在加目錄頁(yè)，哈哈，你沒(méi)想錯(cuò)！

這就是傳說(shuō)中的B+樹(shù)！把整個(gè)的B+樹(shù)稱(chēng)作mysql innode db下的索引結(jié)構(gòu)，一般我們建表的時(shí)候，就是在該結(jié)構(gòu)下進(jìn)行CURD，即使沒(méi)有主鍵也是這樣子的，會(huì)有默認(rèn)主鍵的至此，我們已經(jīng)給我們的表user構(gòu)建完了主鍵索引。隨便找一個(gè)id=？我們發(fā)現(xiàn)，現(xiàn)在查找的Page數(shù)一定減少了，也就意味著IO次數(shù)減少了，那么效率也就提高了。

1.葉子節(jié)點(diǎn)保存有數(shù)據(jù)，路上節(jié)點(diǎn)沒(méi)有，非葉子節(jié)點(diǎn)，不要數(shù)據(jù)，只要目錄項(xiàng)

非葉子節(jié)點(diǎn)不存數(shù)據(jù)，可以存儲(chǔ)更多的目錄項(xiàng)，目錄頁(yè)可以管理更多的葉子page,這棵樹(shù)一定是一個(gè)矮胖型的樹(shù)。這也意味著途徑的路上節(jié)點(diǎn)減少。這也意味著找到目標(biāo)數(shù)據(jù)只需要更少的page,也就是IO次數(shù)更少，在IO層面，提高了效率！

每一個(gè)節(jié)點(diǎn)，都有目錄項(xiàng)，可以大大提高搜索效率。

2.葉子節(jié)點(diǎn)全部用鏈表級(jí)聯(lián)起來(lái)

這是b+樹(shù)的特點(diǎn)；我們比較希望進(jìn)行范圍查找

總結(jié)

到此這篇關(guān)于MySql索引的作用以及對(duì)索引的理解的文章就介紹到這了,更多相關(guān)MySql索引作用及理解內(nèi)容請(qǐng)搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！