MySQL中索引提高查詢(xún)效率的原因是什么

MySQL中索引提高查詢(xún)效率的原因是什么，很多新手對(duì)此不是很清楚，為了幫助大家解決這個(gè)難題，下面小編將為大家詳細(xì)講解，有這方面需求的人可以來(lái)學(xué)習(xí)下，希望你能有所收獲。

磁盤(pán)IO和預(yù)讀：

先說(shuō)一下磁盤(pán)IO，磁盤(pán)讀取數(shù)據(jù)靠的是機(jī)械運(yùn)動(dòng)，每一次讀取數(shù)據(jù)需要尋道、尋點(diǎn)、拷貝到內(nèi)存三步操作。

尋道時(shí)間是磁臂移動(dòng)到指定磁道所需要的時(shí)間，一般在5ms以下;

• 尋點(diǎn)是從磁道中找到數(shù)據(jù)存在的那個(gè)點(diǎn)，平均時(shí)間是半圈時(shí)間，如果是一個(gè)7200轉(zhuǎn)/min的磁盤(pán)，尋點(diǎn)時(shí)間平均是600000/7200/2=4.17ms;

拷貝到內(nèi)存的時(shí)間很快，和前面兩個(gè)時(shí)間比起來(lái)可以忽略不計(jì)，所以一次IO的時(shí)間平均是在9ms左右。聽(tīng)起來(lái)很快，但數(shù)據(jù)庫(kù)百萬(wàn)級(jí)別的數(shù)據(jù)過(guò)一遍就達(dá)到了9000s，顯然就是災(zāi)難級(jí)別的了。

考慮到磁盤(pán)IO是非常高昂的操作，計(jì)算機(jī)操作系統(tǒng)做了預(yù)讀的優(yōu)化，當(dāng)一次IO時(shí)，不光把當(dāng)前磁盤(pán)地址的數(shù)據(jù)，而是把相鄰的數(shù)據(jù)也都讀取到內(nèi)存緩沖區(qū)內(nèi)，因?yàn)楫?dāng)計(jì)算機(jī)訪(fǎng)問(wèn)一個(gè)地址的數(shù)據(jù)的時(shí)候，與其相鄰的數(shù)據(jù)也會(huì)很快被訪(fǎng)問(wèn)到。

每一次IO讀取的數(shù)據(jù)我們稱(chēng)之為一頁(yè)(page)，具體一頁(yè)有多大數(shù)據(jù)跟操作系統(tǒng)有關(guān)，一般為4k或8k，也就是我們讀取一頁(yè)內(nèi)的數(shù)據(jù)時(shí)候，實(shí)際上才發(fā)生了一次IO。

(突然想到個(gè)我剛畢業(yè)被問(wèn)過(guò)的問(wèn)題，在64位的操作系統(tǒng)中，Java中的int類(lèi)型占幾個(gè)字節(jié)?最大是多少?為什么?)

那我們想要優(yōu)化數(shù)據(jù)庫(kù)查詢(xún)，就要盡量減少磁盤(pán)的IO操作，所以就出現(xiàn)了索引。

索引是什么?

• MySQL官方對(duì)索引的定義為：索引(Index)是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。

• MySQL中常用的索引在物理上分兩類(lèi)，B-樹(shù)索引和哈希索引。

本次主要講BTree索引。

BTree索引

BTree又叫多路平衡查找樹(shù)，一顆m叉的BTree特性如下：

樹(shù)中每個(gè)節(jié)點(diǎn)最多包含m個(gè)孩子。

除根節(jié)點(diǎn)與葉子節(jié)點(diǎn)外，每個(gè)節(jié)點(diǎn)至少有[ceil(m/2)]個(gè)孩子(ceil()為向上取整)。

若根節(jié)點(diǎn)不是葉子節(jié)點(diǎn)，則至少有兩個(gè)孩子。

所有的葉子節(jié)點(diǎn)都在同一層。

每個(gè)非葉子節(jié)點(diǎn)由n個(gè)key與n+1個(gè)指針組成，其中[ceil(m/2)-1] <= n <= m-1 。

這是一個(gè)3叉(只是舉例，真實(shí)會(huì)有很多叉)的BTree結(jié)構(gòu)圖，每一個(gè)方框塊我們稱(chēng)之為一個(gè)磁盤(pán)塊或者叫做一個(gè)block塊，這是操作系統(tǒng)一次IO往內(nèi)存中讀的內(nèi)容，一個(gè)塊對(duì)應(yīng)四個(gè)扇區(qū)，紫色代表的是磁盤(pán)塊中的數(shù)據(jù)key，黃色代表的是數(shù)據(jù)data，藍(lán)色代表的是指針p，指向下一個(gè)磁盤(pán)塊的位置。

來(lái)模擬下查找key為29的data的過(guò)程：

1. 根據(jù)根結(jié)點(diǎn)指針讀取文件目錄的根磁盤(pán)塊1?！敬疟P(pán)IO操作1次】

2. 磁盤(pán)塊1存儲(chǔ)17，35和三個(gè)指針數(shù)據(jù)。我們發(fā)現(xiàn)17<29<35，因此我們找到指針p2。

3. 根據(jù)p2指針，我們定位并讀取磁盤(pán)塊3?！敬疟P(pán)IO操作2次】

4. 磁盤(pán)塊3存儲(chǔ)26，30和三個(gè)指針數(shù)據(jù)。我們發(fā)現(xiàn)26<29<30，因此我們找到指針p2。

5. 根據(jù)p2指針，我們定位并讀取磁盤(pán)塊8?！敬疟P(pán)IO操作3次】

6. 磁盤(pán)塊8中存儲(chǔ)28，29。我們找到29，獲取29所對(duì)應(yīng)的數(shù)據(jù)data。

由此可見(jiàn)，BTree索引使每次磁盤(pán)I/O取到內(nèi)存的數(shù)據(jù)都發(fā)揮了作用，從而提高了查詢(xún)效率。

但是有沒(méi)有什么可優(yōu)化的地方呢?

我們從圖上可以看到，每個(gè)節(jié)點(diǎn)中不僅包含數(shù)據(jù)的key值，還有data值。而每一個(gè)頁(yè)的存儲(chǔ)空間是有限的，如果data數(shù)據(jù)較大時(shí)將會(huì)導(dǎo)致每個(gè)節(jié)點(diǎn)(即一個(gè)頁(yè))能存儲(chǔ)的key的數(shù)量很小，當(dāng)存儲(chǔ)的數(shù)據(jù)量很大時(shí)同樣會(huì)導(dǎo)致B-Tree的深度較大，增大查詢(xún)時(shí)的磁盤(pán)I/O次數(shù)，進(jìn)而影響查詢(xún)效率。

B+Tree索引

B+Tree是在B-Tree基礎(chǔ)上的一種優(yōu)化，使其更適合實(shí)現(xiàn)外存儲(chǔ)索引結(jié)構(gòu)。在B+Tree中，所有數(shù)據(jù)記錄節(jié)點(diǎn)都是按照鍵值大小順序存放在同一層的葉子節(jié)點(diǎn)上，而非葉子節(jié)點(diǎn)上只存儲(chǔ)key值信息，這樣可以大大加大每個(gè)節(jié)點(diǎn)存儲(chǔ)的key值數(shù)量，降低B+Tree的高度。

B+Tree相對(duì)于B-Tree有幾點(diǎn)不同：

• 非葉子節(jié)點(diǎn)只存儲(chǔ)鍵值信息， 數(shù)據(jù)記錄都存放在葉子節(jié)點(diǎn)中，  將上一節(jié)中的B-Tree優(yōu)化，由于B+Tree的非葉子節(jié)點(diǎn)只存儲(chǔ)鍵值信息，所以B+Tree的高度可以被壓縮到特別的低。

具體的數(shù)據(jù)如下：

• InnoDB存儲(chǔ)引擎中頁(yè)的大小為16KB，一般表的主鍵類(lèi)型為INT(占用4個(gè)字節(jié))或BIGINT(占用8個(gè)字節(jié))，指針類(lèi)型也一般為4或8個(gè)字節(jié)，也就是說(shuō)一個(gè)頁(yè)(B+Tree中的一個(gè)節(jié)點(diǎn))中大概存儲(chǔ)16KB/(8B+8B)=1K個(gè)鍵值(因?yàn)槭枪乐担瑸榉奖阌?jì)算，這里的K取值為〖10〗^3)。

也就是說(shuō)一個(gè)深度為3的B+Tree索引可以維護(hù)10^3 * 10^3 * 10^3 = 10億  條記錄。(這種計(jì)算方式存在誤差，而且沒(méi)有計(jì)算葉子節(jié)點(diǎn)，如果計(jì)算葉子節(jié)點(diǎn)其實(shí)是深度為4了)

我們只需要進(jìn)行三次的IO操作就可以從10億條數(shù)據(jù)中找到我們想要的數(shù)據(jù)，比起最開(kāi)始的百萬(wàn)數(shù)據(jù)9000秒不知道好了多少個(gè)華萊士了。

而且在B+Tree上通常有兩個(gè)頭指針，一個(gè)指向根節(jié)點(diǎn)，另一個(gè)指向關(guān)鍵字最小的葉子節(jié)點(diǎn)，而且所有葉子節(jié)點(diǎn)(即數(shù)據(jù)節(jié)點(diǎn))之間是一種鏈?zhǔn)江h(huán)結(jié)構(gòu)。所以我們除了可以對(duì)B+Tree進(jìn)行主鍵的范圍查找和分頁(yè)查找，還可以從根節(jié)點(diǎn)開(kāi)始，進(jìn)行隨機(jī)查找。

數(shù)據(jù)庫(kù)中的B+Tree索引可以分為聚集索引(clustered index)和輔助索引(secondary index)。

上面的B+Tree示例圖在數(shù)據(jù)庫(kù)中的實(shí)現(xiàn)即為聚集索引，聚集索引的B+Tree中的葉子節(jié)點(diǎn)存放的是整張表的行記錄數(shù)據(jù)，輔助索引與聚集索引的區(qū)別在于輔助索引的葉子節(jié)點(diǎn)并不包含行記錄的全部數(shù)據(jù)，而是存儲(chǔ)相應(yīng)行數(shù)據(jù)的聚集索引鍵，即主鍵。

當(dāng)通過(guò)輔助索引來(lái)查詢(xún)數(shù)據(jù)時(shí)，InnoDB存儲(chǔ)引擎會(huì)遍歷輔助索引找到主鍵，然后再通過(guò)主鍵在聚集索引中找到完整的行記錄數(shù)據(jù)。

不過(guò)，雖然索引可以加快查詢(xún)速度，提高 MySQL 的處理性能，但是過(guò)多地使用索引也會(huì)造成以下弊端：

• 創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間，這種時(shí)間隨著數(shù)據(jù)量的增加而增加。

• 除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個(gè)索引還要占一定的物理空間。如果要建立聚簇索引，那么需要的空間就會(huì)更大。

• 當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)地維護(hù)，這樣就降低了數(shù)據(jù)的維護(hù)速度。

注意：索引可以在一些情況下加速查詢(xún)，但是在某些情況下，會(huì)降低效率。

索引只是提高效率的一個(gè)因素，因此在建立索引的時(shí)候應(yīng)該遵循以下原則：

• 在經(jīng)常需要搜索的列上建立索引，可以加快搜索的速度。

• 在作為主鍵的列上創(chuàng)建索引，強(qiáng)制該列的唯一性，并組織表中數(shù)據(jù)的排列結(jié)構(gòu)。

• 在經(jīng)常使用表連接的列上創(chuàng)建索引，這些列主要是一些外鍵，可以加快表連接的速度。

• 在經(jīng)常需要根據(jù)范圍進(jìn)行搜索的列上創(chuàng)建索引，因?yàn)樗饕呀?jīng)排序，所以其指定的范圍是連續(xù)的。

• 在經(jīng)常需要排序的列上創(chuàng)建索引，因?yàn)樗饕呀?jīng)排序，所以查詢(xún)時(shí)可以利用索引的排序，加快排序查詢(xún)。

• 在經(jīng)常使用 WHERE 子句的列上創(chuàng)建索引，加快條件的判斷速度。

看完上述內(nèi)容是否對(duì)您有幫助呢？如果還想對(duì)相關(guān)知識(shí)有進(jìn)一步的了解或閱讀更多相關(guān)文章，請(qǐng)關(guān)注億速云行業(yè)資訊頻道，感謝您對(duì)億速云的支持。