目錄索引的作用認識磁盤MySql 與磁盤交互基本單位Page共識索引的理解主鍵有序問題理解單個Page理解多個Page頁目錄單頁情況多頁情況總結索引的作用

索引是與效率掛鉤的，所以沒有索引，可能會存在問題

索引：提高數據庫的性能，索引是物美價廉的東西了。不用加內存，不用改程序，不用調sql，只要執行正確的 create index ，查詢速度就可能提高成百上千倍。但是天下沒有免費的午餐，查詢速度的提高是以插入、更新、刪除的速度為代價的，這些寫操作，增加了大量的IO。所以它的價值，在于提高一個海量數據的檢索速度。

MySQL的服務器，本質是在內存中的，所有的數據庫的CURD操作，全部都是在內存中進行的！所以索引也是如此

提高算法效率的因素：

1.組織數據的方式

2.算法本身。

常見的索引分為以下幾種

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

創建一個海量表，在查詢的時候，沒有索引時看一下會有什么問題

查詢員工編號為998877的員工

select * from EMP where empno=998877;

可以看到耗時17.71秒，這還是在本機一個人來操作，在實際項目中，如果放在公網中，假如同時有1000個人并發查詢，那很可能就死機，這是非常可怕的事情。

解決方法，創建索引

alter table EMP add index(empno);

測試看查詢時間

時間變得非常快！這就是索引帶來的好處！

想認識索引之前，我們非常有必要先了解一下磁盤。

認識磁盤mysql與存儲

MySQL 給用戶提供存儲服務，而存儲的都是數據，數據在磁盤這個外設當中。磁盤是計算機中的一個機械設備，相比于計算機其他電子元件，磁盤效率是比較低的，在加上IO本身的特征，可以知道，如何提交效率，是 MySQL 的一個重要話題。

磁盤中一個盤片

數據庫文件，本質其實就是保存在磁盤的盤片當中。也就是上面的一個個小格子中，就是我們經常所說的扇區。當然，數據庫文件很大，也很多，一定需要占據多個扇區

我們在使用Linux，所看到的大部分目錄或者文件，其實就是保存在硬盤當中的。(當然，有一些內存文件系統，如： proc ， sys 之類，我們不考慮)

所以，最基本的，找到一個文件的全部，本質，就是在磁盤找到所有保存文件的扇區。而我們能夠定位任何一個扇區，那么便能找到所有扇區，因為查找方式是一樣的

定位扇區

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

每個盤面都有一個磁頭，那么磁頭和盤面的對應關系便是1對1的

所以，我們只需要知道，磁頭（Heads）、柱面(Cylinder)(等價于磁道)、扇區(Sector)對應的編號。即可在磁盤上定位所要訪問的扇區。這種磁盤數據定位方式叫做 CHS 。不過實際系統軟件使用的并不是 CHS （但是硬件是），而是 LBA ，一種線性地址，可以想象成虛擬地址與物理地址。系統將 LBA 地址最后會轉化成為 CHS ，交給磁盤去進行數據讀取。不過，我們現在不關心轉化細節。

結論

我們現在已經能夠在硬件層面定位，任何一個基本數據塊了(扇區)。但是在系統軟件上，就不是直接按照扇區(512字節，部分4096字節),進行IO交互了，這是因為如果操作系統直接使用硬件提供的數據大小進行交互，那么系統的IO代碼，就和硬件強相關，換言之，如果硬件發生變化，系統必須跟著變化；另外目前來看，單次IO 512字節，還是太小了。IO單位小，意味著讀取同樣的數據內容，需要進行多次磁盤訪問，會帶來效率的降低；

文件系統讀取基本單位，就不是扇區，而是數據塊。既系統讀取磁盤，是以塊為單位的，基本單位是 4KB

磁盤隨機訪問(Random Access)與連續訪問(Sequential Access)

隨機訪問：本次IO所給出的扇區地址和上次IO給出扇區地址不連續，這樣的話磁頭在兩次IO操作之間需要作比較大的移動動作才能重新開始讀/寫數據。連續訪問：如果當次IO給出的扇區地址與上次IO結束的扇區地址是連續的，那磁頭就能很快的開始這次IO操作，這樣的多個IO操作稱為連續訪問。因此盡管相鄰的兩次IO操作在同一時刻發出，但如果它們的請求的扇區地址相差很大的話也只能稱為隨機訪問，而非連續訪問。磁盤是通過機械運動進行尋址的，隨機訪問不需要過多的定位，故效率比較高

MySql 與磁盤交互基本單位Page

MySql 作為一款應用軟件，可以想象成一種特殊的文件系統。它有著更高的IO場景，所以，為了提高基本的IO效率， MySql 進行IO的基本單位是16KB:MySql是應用層服務，是不可能直接訪問硬件的，這個16KB是站在MySql角度向OS提出來的，OS內部存在文件緩沖區的,MySql進入到某一個目錄，對某張表做CURD，對某張表內部做增刪查改，在MySql就得到了文件的fd,一個文件被打開有自己的結構體，緩沖區；MySql以16KB為單位與文件緩沖區進行IO。

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

也就是說，磁盤這個硬件設備的基本單位是 512 字節，而 MySQL InnoDB引擎 使用 16KB 進行IO交互。即， MySQL 和磁盤進行數據交互的基本單位是 16KB 。這個基本數據單元，在 MySQL 這里叫做page（注意和系統的page區分）

共識

MySQL 中的數據文件，是以page為單位保存在磁盤當中的。

MySQL 的 CURD 操作，都需要通過計算，找到對應的插入位置，或者找到對應要修改或者查詢的數據

只要涉及計算，就需要CPU參與，而為了便于CPU參與，一定要能夠先將數據移動到內存當中

所以在特定時間內，數據一定是磁盤中有，內存中也有。后續操作完內存數據之后，以特定的刷新策略，刷新到磁盤。而這時，就涉及到磁盤和內存的數據交互，也就是IO。此時IO的基本單位就是Page。

為了更好的進行上面的操作， MySQL 服務器在內存中運行的時候，在服務器內部，就申請了被稱為 Buffer Pool 的的大內存空間，來進行各種緩存。其實就是很大的內存空間，來和磁盤數據進行IO交互

為了更高的效率，一定要盡可能的減少系統和磁盤IO的次數

索引的理解

創建一張表：

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

現在往表里插入5條數據，結果如下：插入的數據是無序的，但是查看結果卻是有序的！

mysql> insert into user (id, age, name) values(3, 18, '楊過');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, '歐陽鋒');Query OK, 1 row affected (0.03 sec)

主鍵有序問題

我們向一個具有主鍵的表中，亂序插入數據，發現數據會自動排序，這是誰做的，為什么要這樣做？

對于這個問題的回答，我們來看一看。

首先磁盤上有對應的文件數據，文件數據最終會被預讀到文件緩沖區，mysql啟動的時候會申請buffer pool,mysql層面上，所有的page都會被放到buffer pool中，理解mysql中page的概念：一個page是16KB，mysql內部一定需要并且會存在大量的page,也就決定了mysql必須要將多個同時存在的page管理起來。要管理所有的mysql內的page,需要先描述，在組織，所以不要簡單將page認為是一個內存塊，page內部也必須寫入對應的管理信息！如：

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

所謂的申請，在mysql申請的page就是new page,在用簡單的方式：比如將所有的page用鏈表的形式管理起來。在buffer pool內部，對mysql中的page進行了一個建模。

了解一下：MySQL和磁盤進行IO交互的時候，采用Page的方案進行交互

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

怎么保證，用戶一定下次找的數據，就在這個Page里面？我們不能嚴格保證，但是有很大概率，因為有局部性原理。往往IO效率低下的最主要矛盾不是IO單次數據量的大小，而是IO的次數

理解單個Page

MySQL 中要管理很多數據表文件，而要管理好這些文件，就需要先描述，在組織 ,我們目前可以簡單理解成一個個獨立文件是有一個或者多個Page構成的，這在我們一開始也有說到

對于不同的 Page ，在 MySQL 中，都是 16KB ，使用 prev 和 next 構成雙向鏈表因為有主鍵的問題， MySQL 會默認按照主鍵給我們的數據進行排序，從上面的Page內數據記錄可以看出，數據是有序且彼此關聯的。

**插入數據時排序的目的，就是優化查詢的效率。**頁內部存放數據的模塊，實質上也是一個鏈表的結構，鏈表的特點也就是增刪快，查詢修改慢，所以優化查詢的效率是必須的。正式因為有序，在查找的時候，從頭到后都是有效查找，沒有任何一個查找是浪費的，而且，如果運氣好，是可以提前結束查找過程的。

理解多個Page

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

所以要提高效率，我們要有兩個角度進行考量：第一個角度是在單page的時候如何提高一個Page內部的鏈式遍歷的效率；另一個是多Page的時候怎么解決Page間進行查找的效率。所以我們有了頁目錄的出現。

頁目錄

比如我們在看《軟件工程導論》這本書的時候，如果我們要看軟件生命周期這個章節，找到該章節有兩種做法從頭逐頁的向后翻，直到找到目標內容；通過書提供的目錄，發現軟件生命周期章節在11頁(假設)，那么我們便直接翻到11頁。同時，查找目錄的方案，可以順序找，不過因為目錄的頁數肯定少，所以可以快速提高定位本質上，書中的目錄，是多花了紙張的，但是卻提高了效率；所以，目錄，是一種“空間換時間的做法”

單頁情況

對于上面的單頁Page，我們也可以引入目錄

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

所以對于一開始的問題，我們可以正式回答了：主鍵插入無序的，但是結果是有序的，很簡單，只有把數據變成有序的，能夠方便我們引入頁內目錄！

多頁情況

MySQL 中每一頁的大小只有 16KB ，單個Page大小固定，所以隨著數據量不斷增大， 16KB 不可能存下所有的數據，那么必定會有多個頁來存儲數據。

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

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

使用一個目錄項來指向某一頁，而這個目錄項存放的就是將要指向的頁中存放的最小數據的鍵值。和頁內目錄不同的地方在于，這種目錄管理的級別是頁，而頁內目錄管理的級別是行。其中，每個目錄項的構成是：鍵值+指針。

存在一個目錄頁來管理頁目錄，目錄頁中的數據存放的就是指向的那一頁中最小的數據。有數據，就可通過比較，找到該訪問那個Page，進而通過指針，找到下一個Page。其實目錄頁的本質也是頁，普通頁中存的數據是用戶數據，而目錄頁中存的數據是普通頁的地址。但是我們每次檢索數據的時候，應該從哪里開始，雖然頂層的目錄頁少了，但是還需要遍歷，不用擔心，可以在加目錄頁，哈哈，你沒想錯！

這就是傳說中的B+樹！把整個的B+樹稱作mysql innode db下的索引結構，一般我們建表的時候，就是在該結構下進行CURD，即使沒有主鍵也是這樣子的，會有默認主鍵的至此，我們已經給我們的表user構建完了主鍵索引。隨便找一個id=？我們發現，現在查找的Page數一定減少了，也就意味著IO次數減少了，那么效率也就提高了。

1.葉子節點保存有數據，路上節點沒有，非葉子節點，不要數據，只要目錄項

非葉子節點不存數據，可以存儲更多的目錄項，目錄頁可以管理更多的葉子page,這棵樹一定是一個矮胖型的樹。這也意味著途徑的路上節點減少。這也意味著找到目標數據只需要更少的page,也就是IO次數更少，在IO層面，提高了效率！

每一個節點，都有目錄項，可以大大提高搜索效率。

2.葉子節點全部用鏈表級聯起來

這是b+樹的特點；我們比較希望進行范圍查找

總結

到此這篇關于MySql索引的作用以及對索引的理解的文章就介紹到這了,更多相關MySql索引作用及理解內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！