目錄

一、索引的概念及作用

二、實(shí)際看看索引的效率提升

三、認(rèn)識(shí)磁盤

1. 簡(jiǎn)單了解磁盤

2. 數(shù)據(jù)庫文件存儲(chǔ)位置

3. 定位扇區(qū)

4. 數(shù)據(jù)讀取效率問題

5. 磁盤隨機(jī)訪問與磁盤連續(xù)訪問

5.1 隨機(jī)訪問

5.2 連續(xù)訪問

四、mysql與磁盤的交互

五、建立共識(shí)?

一、索引的概念及作用

索引，其實(shí)就是用于提高數(shù)據(jù)庫的性能的。使用它不用加內(nèi)存、不用改程序、不用調(diào)sql，只需要執(zhí)行正確的“create index”，就可以讓數(shù)據(jù)庫的查詢速度提高成百上千倍。

當(dāng)然，數(shù)據(jù)庫查詢效率的提高也是有代價(jià)，那就是在插入、更新、刪除的時(shí)候會(huì)增加大量的IO，拉低效率。因此，索引的價(jià)值僅僅體現(xiàn)在提高海量數(shù)據(jù)的檢索速度上。

常見的索引一般分為如下幾種：
主鍵索引（primary key）、唯一索引（unique）、普通索引（index）和全文索引（fulltext）。

注意，mysql的服務(wù)器是在內(nèi)存中的，因此，當(dāng)mysql啟動(dòng)后，它會(huì)在內(nèi)存中為我們開辟一塊空間，當(dāng)需要修改數(shù)據(jù)時(shí)，就會(huì)將磁盤中的數(shù)據(jù)加載到這塊內(nèi)存中，在內(nèi)存中執(zhí)行CURD操作，然后在合適的時(shí)間將內(nèi)存中的數(shù)據(jù)刷新到外設(shè)中。索引也是如此。

大家知道，在實(shí)際中如果我們要提高搜索效率，其實(shí)就是對(duì)搜索的算法進(jìn)行優(yōu)化。對(duì)算法優(yōu)化一般包含兩個(gè)方面，一個(gè)是數(shù)據(jù)結(jié)構(gòu)本身，例如將線性結(jié)構(gòu)換成二叉樹結(jié)構(gòu)。第二種就是修改算法本身，即優(yōu)化在特定數(shù)據(jù)結(jié)構(gòu)下的查找方法，例如在線性表中不再線性遍歷，而是二分查找。一般而言，數(shù)據(jù)結(jié)構(gòu)改變，算法本身也需要跟著改變。

對(duì)于索引而言也是如此。在索引中就是將數(shù)據(jù)存儲(chǔ)到了特定的數(shù)據(jù)結(jié)構(gòu)中，利用這個(gè)數(shù)據(jù)結(jié)構(gòu)的優(yōu)勢(shì)提高搜索效率。因此，所謂的索引，其實(shí)就是在一個(gè)特定的數(shù)據(jù)結(jié)構(gòu)中搜素?cái)?shù)據(jù)。

二、實(shí)際看看索引的效率提升

為了方便看到索引帶來的效果，所以我們需要準(zhǔn)備一份非常大的數(shù)據(jù)進(jìn)行索引。

在這里準(zhǔn)備了這樣的一張表，然后向里面插入800w行數(shù)據(jù)。如果大家也想在自己的機(jī)器上實(shí)際看到索引的效果，可以到網(wǎng)上直接搜索存儲(chǔ)大量數(shù)據(jù)的數(shù)據(jù)表，有現(xiàn)成的代碼，大家直接復(fù)制就可以向特定表中插入大量的數(shù)據(jù)。

注意，當(dāng)插入成百上千萬行數(shù)據(jù)時(shí)，根據(jù)你的配置，插入需要的時(shí)間可能不同，配置好的話可能幾分鐘，配置差點(diǎn)可能就要10幾分鐘乃至更高。大家看到mysql一直下面的插入狀態(tài)的話，不要去結(jié)束它。

還有一個(gè)點(diǎn)，在你復(fù)制的代碼中檢查一下數(shù)據(jù)表的創(chuàng)建里面有沒有帶上主鍵、唯一鍵等屬性，如果有，將它去除掉，以方便看到一份“沒有索引”的表的搜索效率。

當(dāng)數(shù)據(jù)插入完成后，千萬不要直接輸入“select * from 數(shù)據(jù)表”查看數(shù)據(jù)表內(nèi)容，因?yàn)槔锩娴臄?shù)據(jù)量非常大，如果直接用該命令而不帶篩選選項(xiàng)，mysql就會(huì)不斷顯示數(shù)據(jù)。

有了這份存儲(chǔ)有大量數(shù)據(jù)的表后，我們搜索一份數(shù)據(jù)：

可以看到，在這份存儲(chǔ)有800w行數(shù)據(jù)的表內(nèi)查詢一個(gè)數(shù)據(jù)，花費(fèi)了5.65s。很明顯，這是不能接受的。要知道，一個(gè)公司的數(shù)據(jù)庫中有個(gè)幾百上千萬行數(shù)據(jù)是非常正常的，如果一個(gè)用戶查詢一條數(shù)據(jù)，數(shù)據(jù)庫要用5s才能返回?cái)?shù)據(jù)，很明顯是用戶所不能接受。并且這僅僅是一個(gè)用戶查，如果是成百上千乃至更多用戶同時(shí)查數(shù)據(jù)，那么數(shù)據(jù)庫的響應(yīng)速度會(huì)更慢，甚至直接掛掉。

由此，我們就必須要想辦法提高數(shù)據(jù)庫中數(shù)據(jù)的查詢效率。

輸入“alter table 數(shù)據(jù)庫名 add index(列名)”，指定一列為其添加索引。

索引添加完后，我們?cè)俚綌?shù)據(jù)庫中查詢同一份數(shù)據(jù)：

可以看到，當(dāng)我們?yōu)楸硖砑铀饕笤偃ケ碇兴阉鲾?shù)據(jù)，它的搜索時(shí)間就變?yōu)榱?s。幾乎就是在一瞬間就搜索完成了。通過這個(gè)例子，就可以明顯的看到索引帶來的查詢效率提升。

三、認(rèn)識(shí)磁盤

1. 簡(jiǎn)單了解磁盤

我們知道，一般來講，數(shù)據(jù)都是存儲(chǔ)在磁盤中的。因此，mysql作為一個(gè)給用戶提供數(shù)據(jù)存儲(chǔ)服務(wù)的服務(wù)端，它也是將用戶數(shù)據(jù)存儲(chǔ)到磁盤這個(gè)外設(shè)中的。但我們知道，磁盤作為計(jì)算機(jī)中的一個(gè)機(jī)械設(shè)備，相比與計(jì)算機(jī)中的其他電子元件，磁盤的存儲(chǔ)效率是比較低的。再加上數(shù)據(jù)IO本身還有一定的效率消耗，如何提升mysql的效率就是一個(gè)重要問題了。

由于本章的重點(diǎn)并不是研究硬件，并且在我以前關(guān)于linux的文章中也介紹過磁盤的結(jié)構(gòu)了，這里就簡(jiǎn)要介紹一下。

一塊普通的磁盤，它的物理結(jié)構(gòu)如下所示：

在這里面的圓盤，就是盤片，上面是有起伏的，每個(gè)起伏就代表著二進(jìn)制，我們的數(shù)據(jù)就是存儲(chǔ)在這塊盤片中的。在盤片的上下有一個(gè)磁頭，它會(huì)左右來回?cái)[動(dòng)，磁頭的作用就是讀取盤片中保存的二進(jìn)制數(shù)據(jù)。在盤片的中間有一個(gè)主軸，這個(gè)主軸中有一個(gè)馬達(dá)，用于讓盤片高速旋轉(zhuǎn)。

注意，磁盤上的盤片是一摞，而不是一片。在這些盤片的上下兩面都可以存儲(chǔ)數(shù)據(jù)，這就意味著每一面都有一個(gè)磁頭在來回?cái)[動(dòng)讀取數(shù)據(jù)。

在盤片中，磁盤表面被分為許多個(gè)同心圓，每個(gè)同心圓都稱為一個(gè)磁道，每個(gè)磁道都有一個(gè)編號(hào)，最外面的就是0號(hào)磁道。

每個(gè)磁道又被劃分為若干段（段又叫扇區(qū)）。每個(gè)扇區(qū)的存儲(chǔ)容量都是相同的，一般為512字節(jié)，每個(gè)扇區(qū)都有一個(gè)編號(hào)。但是隨著磁盤的發(fā)展，現(xiàn)在的扇區(qū)正在逐漸擴(kuò)大，已經(jīng)出現(xiàn)了更高效的4096字節(jié)扇區(qū)，被稱為“4K扇區(qū)”。當(dāng)然，因?yàn)槲覀儾皇菍ｉT研究磁盤的，不必太過關(guān)心，有個(gè)大致了解即可。

注意，雖然磁盤上每個(gè)扇區(qū)的實(shí)際大小有差距，但是上面的存儲(chǔ)容量都是一樣的，因?yàn)槊總€(gè)扇區(qū)的存儲(chǔ)數(shù)據(jù)量不是看扇區(qū)大小，而是看數(shù)據(jù)存儲(chǔ)密度。雖然上文中說磁盤上每個(gè)扇區(qū)的大小是一樣的，但這并不絕對(duì)，隨著磁盤的發(fā)展，已經(jīng)慢慢出現(xiàn)了扇區(qū)容量不一樣的磁盤了。在這里暫時(shí)不做考慮。

2. 數(shù)據(jù)庫文件存儲(chǔ)位置

在數(shù)據(jù)庫中的數(shù)據(jù)庫文件，無論是數(shù)據(jù)庫，還是表，還是表中的數(shù)據(jù)，其本質(zhì)都是被保存在磁盤的盤片中的一個(gè)個(gè)扇區(qū)內(nèi)的。當(dāng)然，由于一個(gè)扇區(qū)的存儲(chǔ)容量很小，所以當(dāng)數(shù)據(jù)庫文件很大是，就需要占據(jù)多個(gè)扇區(qū)。

就算是我們當(dāng)前使用的linux中所看到的大部分目錄或文件，其實(shí)也是保存在硬盤中的。（注意，有一些內(nèi)存文件系統(tǒng)，如porc、sys等，我們不做考慮）。

由此，找到一個(gè)文件，本質(zhì)就是在磁盤上找到保存該文件的扇區(qū)。這也就要求磁盤要有能夠定位任意一個(gè)扇區(qū)的能力。

3. 定位扇區(qū)

上圖是一張有三個(gè)磁盤六個(gè)盤面的磁盤。在這個(gè)磁盤中，每一面都有一個(gè)對(duì)應(yīng)的磁頭用于讀取數(shù)據(jù)。當(dāng)我們需要查找某份數(shù)據(jù)時(shí)，就是先確定它在哪一面，找到數(shù)據(jù)所處的盤面后，再找到它所在磁道， 然后找到數(shù)據(jù)在該磁道上的哪一個(gè)扇區(qū)。當(dāng)找到數(shù)據(jù)所在扇區(qū)后，就可以通過磁頭讀取數(shù)據(jù)。

由此，我們只需要知道磁頭、柱面（等價(jià)于磁道）、扇區(qū)對(duì)應(yīng)的編號(hào)，就可以在磁盤上定位所要訪問的扇區(qū)，這種磁盤數(shù)據(jù)定位方式叫做CHS。不過實(shí)際系統(tǒng)軟件中使用的不是CHS，而是LBA，是一種線性地址，大家可以LBA和CHS分別想象為虛擬地址和物理地址。系統(tǒng)會(huì)將LBA地址轉(zhuǎn)化為CHS，交給磁盤去進(jìn)行數(shù)據(jù)讀取。

4. 數(shù)據(jù)讀取效率問題

通過上面的內(nèi)容大家應(yīng)該就知道磁盤是可以定義任意一個(gè)扇區(qū)的。但是，在系統(tǒng)層面上來看，難道系統(tǒng)軟件就是直接按一個(gè)扇區(qū)（512字節(jié)或4096字節(jié)）進(jìn)行IO交互的嗎？其實(shí)并不是。

這里存在幾個(gè)原因。

（1）如果OS直接使用硬件提供的數(shù)據(jù)大小進(jìn)行交互，這就意味著系統(tǒng)的IO代碼和硬件強(qiáng)相關(guān)，當(dāng)硬件發(fā)生變化時(shí)，系統(tǒng)的IO也就必須跟著變化。

（2）從實(shí)際來看，以512字節(jié)進(jìn)行IO還是太小了。IO單位過小，就意味著讀取同樣的數(shù)據(jù)內(nèi)容，需要進(jìn)行更多的磁盤訪問，會(huì)帶來效率的降低。

（3）大家應(yīng)該了解過文件系統(tǒng)，文件系統(tǒng)中讀取數(shù)據(jù)的基本單位就不是扇區(qū)，而是數(shù)據(jù)塊。一個(gè)數(shù)據(jù)塊的大小是4KB。

因此，系統(tǒng)讀取磁盤時(shí)，并不是以扇區(qū)為單位，而是以數(shù)據(jù)塊為單位，基本單位是4KB。

至于為什么一個(gè)數(shù)據(jù)塊是4KB，一個(gè)方面是內(nèi)存管理的問題。磁盤數(shù)據(jù)是需要加載到內(nèi)存中的，而內(nèi)存中本身就是以4KB劃分成多個(gè)數(shù)據(jù)塊的，因此，以數(shù)據(jù)塊為基本單位與磁盤交互，更有利于磁盤與內(nèi)存的IO交互。

另一個(gè)理由就是效率問題。在磁盤讀取中，就算系統(tǒng)只需要1字節(jié)的數(shù)據(jù)，也需要從磁盤中讀取4KB，有人可能認(rèn)為這是一種浪費(fèi)，但是這是從效率上考慮過的。因?yàn)?strong>系統(tǒng)讀取數(shù)據(jù)時(shí)，它讀取的數(shù)據(jù)大概率都是存放在一起的，而一次性讀取4KB數(shù)據(jù)，就可以做到數(shù)據(jù)的預(yù)加載，當(dāng)系統(tǒng)再次讀取數(shù)據(jù)時(shí)，它讀取的數(shù)據(jù)大概率就是已經(jīng)提前加載好的數(shù)據(jù)，以此減少系統(tǒng)與磁盤的IO次數(shù)，提高數(shù)據(jù)的讀取效率。

5. 磁盤隨機(jī)訪問與磁盤連續(xù)訪問

5.1 隨機(jī)訪問

隨機(jī)訪問，是指本次IO所給出的扇區(qū)地址和上次IO給出的扇區(qū)地址不連續(xù)，這樣的話磁頭在兩次IO操作之間需要作比較大的移動(dòng)動(dòng)作才能重新開始讀寫數(shù)據(jù)。

5.2 連續(xù)訪問

如果當(dāng)次IO給出的扇區(qū)地址與上次IO結(jié)束的扇區(qū)地址是連續(xù)的，那磁頭就能夠很快的開始這次IO操作。這樣的多個(gè)IO操作稱為連續(xù)訪問。

注意，哪怕相鄰的兩次IO操作在同一時(shí)刻發(fā)出，但如果它們的請(qǐng)求的扇區(qū)地址相差很大的話，也只能稱為隨機(jī)訪問，而非連續(xù)訪問。

因?yàn)檫B續(xù)訪問的效率要比隨機(jī)訪問高，所以大家涉及IO的程序中，盡量還是要體現(xiàn)出連續(xù)訪問，而非隨機(jī)訪問。但是要注意，磁盤的效率問題并不是這么簡(jiǎn)單就能夠解決的，磁盤中也是有很多方案設(shè)計(jì)用于提高效率的。這里只是簡(jiǎn)單介紹一下。

四、mysql與磁盤的交互

mysql作為一個(gè)應(yīng)用軟件，可以想象為一種特殊的文件系統(tǒng)。它有著更高的IO需求。因此，為了提高基本的IO效率，mysql進(jìn)行IO的基本單位是16KB。

這也就意味著，雖然磁盤的基本單位是512字節(jié)，但是MYSQL中的InnoDB引擎使用16KB進(jìn)行IO交互。即mysql和磁盤進(jìn)行數(shù)據(jù)交互的基本單位是16KB。這個(gè)基本數(shù)據(jù)單元，在mysql中叫做“page”（注意，這個(gè)page和系統(tǒng)的page不同，要將兩者區(qū)分開來）。

但是大家知道，在OS中，應(yīng)用軟件是無法直接與外設(shè)交互的，必須通過OS。因此，當(dāng)mysql要與磁盤IO時(shí)，并不是雙方直接交互，而是OS從磁盤中讀取4個(gè)數(shù)據(jù)塊，即16KB的數(shù)據(jù)放到文件系統(tǒng)的文件緩沖區(qū)內(nèi)，而在文件系統(tǒng)中的一個(gè)文件緩沖區(qū)，其實(shí)就可以看成是文件系統(tǒng)打開的一個(gè)文件，這個(gè)文件會(huì)返回一個(gè)文件描述符給mysql，讓mysql用這個(gè)文件描述符讀取數(shù)據(jù)。當(dāng)然，這并不準(zhǔn)確。在mysql中也是存在一個(gè)自己的“buffer pool”的，大家可以將其看做一個(gè)存儲(chǔ)數(shù)據(jù)的空間。mysql所有的CURD操作都是在這個(gè)buffer pool中完成的。

至于mysql中寫入的數(shù)據(jù)，就是交給OS，然后OS在滿足一定條件后將數(shù)據(jù)刷新到磁盤上即可。通過這種方式，就提高了mysql的IO效率。

大家也可以在自己的linux中的mysql下輸入“show?global status like 'innodb_page_size';”命令查看使用InnoDB引擎的mysql下的page大?。?/p>

可以看到，大小為16384，單位是字節(jié)，換算過來就是16KB。

五、建立共識(shí)?

?通過上面的內(nèi)容，我們就需要建立以下幾個(gè)共識(shí)：

（1）mysql中的數(shù)據(jù)文件，是以page為單位保存在磁盤當(dāng)中的。

（2）mysql的CURD操作，都需要通過計(jì)算找到對(duì)應(yīng)的插入位置，或者找到對(duì)應(yīng)要修或查詢的數(shù)據(jù)。

（3）在計(jì)算機(jī)中只要涉及到計(jì)算，就需要CPU參與，而為了方便CPU參與，就需要將數(shù)據(jù)移動(dòng)到內(nèi)存中。

（4）因?yàn)橐獙?shù)據(jù)移動(dòng)到內(nèi)存中，這就意味著在特定時(shí)間段內(nèi)，數(shù)據(jù)一定是磁盤和內(nèi)存中都有的。后續(xù)操作完內(nèi)存數(shù)據(jù)之后，就需要以特定的刷新策略將數(shù)據(jù)刷新到磁盤中。此時(shí)，就涉及到磁盤和內(nèi)存的數(shù)據(jù)交互，也就是IO了。而mysql中IO的基本單位就是page。

（5）為了能夠更好的進(jìn)行上面的操作，mysql服務(wù)器在內(nèi)存中運(yùn)行的時(shí)候，在服務(wù)器內(nèi)部就申請(qǐng)了被稱為“buffer pool”的大內(nèi)存空間，這個(gè)空間默認(rèn)為128MB，用于和磁盤數(shù)據(jù)進(jìn)行IO交互。

（6）為了提高效率，一定要盡可能的減少系統(tǒng)和磁盤IO的次數(shù)。

上文中說mysql中有一個(gè)buffer pool內(nèi)存空間，我們可以輸入“vim /etc/my.cnf”打開數(shù)據(jù)庫配置文件查看：

可以看到，配置文件中有這么一條注釋，將這條指令放開，我們就可以修改buffer pool的大小。不放開的話大小默認(rèn)為128MB。文章來源地址http://www.zghlxwxcb.cn/news/detail-576476.html

到了這里，關(guān)于初識(shí)mysql數(shù)據(jù)庫之索引概念與磁盤效率問題的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！