MySQL中的索引有什么用

這篇文章主要介紹了MySQL中的索引有什么用，具有一定借鑒價(jià)值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

索引

1、索引的優(yōu)勢(shì)

（1）提高查詢效率（降低IO使用率）

（2）降低CPU使用率

比如查詢order by age desc，因?yàn)锽+索引樹(shù)本身就是排好序的，所以再查詢?nèi)绻|發(fā)索引，就不用再重新查詢了。

2、索引的弊端

（1）索引本身很大，可以存放在內(nèi)存或硬盤上，通常存儲(chǔ)在硬盤上。

（2）索引不是所有情況都使用，比如①少量數(shù)據(jù)②頻繁變化的字段③很少使用的字段

（3）索引會(huì)降低增刪改的效率

3、索引的分類

（1）單值索引

（2）唯一索引

（3）聯(lián)合索引

（4）主鍵索引

備注：唯一索引和主鍵索引唯一的區(qū)別：主鍵索引不能為null

4、創(chuàng)建索引

alter table user add INDEX `user_index_username_password` (`username`,`password`)

5、MySQL索引原理 -> B+樹(shù)

MySQL索引的底層數(shù)據(jù)結(jié)構(gòu)是B+樹(shù)

B+Tree是在B-Tree基礎(chǔ)上的一種優(yōu)化，使其更適合實(shí)現(xiàn)外存儲(chǔ)索引結(jié)構(gòu)，InnoDB存儲(chǔ)引擎就是用B+Tree實(shí)現(xiàn)其索引結(jié)構(gòu)。

B-Tree結(jié)構(gò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的深度較大，增大查詢時(shí)的磁盤I/O次數(shù)，進(jìn)而影響查詢效率。在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ǔ)鍵值信息。
所有葉子節(jié)點(diǎn)之間都有一個(gè)鏈指針。
數(shù)據(jù)記錄都存放在葉子節(jié)點(diǎn)中。
將上一節(jié)中的B-Tree優(yōu)化，由于B+Tree的非葉子節(jié)點(diǎn)只存儲(chǔ)鍵值信息，假設(shè)每個(gè)磁盤塊能存儲(chǔ)4個(gè)鍵值及指針信息，則變成B+Tree后其結(jié)構(gòu)如下圖所示：

通常在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ùn)算：一種是對(duì)于主鍵的范圍查找和分頁(yè)查找，另一種是從根節(jié)點(diǎn)開(kāi)始，進(jìn)行隨機(jī)查找。

可能上面例子中只有22條數(shù)據(jù)記錄，看不出B+Tree的優(yōu)點(diǎn)，下面做一個(gè)推算：

InnoDB存儲(chǔ)引擎中頁(yè)的大小為16KB，一般表的主鍵類型為INT（占用4個(gè)字節(jié)）或BIGINT（占用8個(gè)字節(jié)），指針類型也一般為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億 條記錄。

實(shí)際情況中每個(gè)節(jié)點(diǎn)可能不能填充滿，因此在數(shù)據(jù)庫(kù)中，B+Tree的高度一般都在2~4層。MySQL的InnoDB存儲(chǔ)引擎在設(shè)計(jì)時(shí)是將根節(jié)點(diǎn)常駐內(nèi)存的，也就是說(shuō)查找某一鍵值的行記錄時(shí)最多只需要1~3次磁盤I/O操作。

數(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)查詢數(shù)據(jù)時(shí)，InnoDB存儲(chǔ)引擎會(huì)遍歷輔助索引找到主鍵，然后再通過(guò)主鍵在聚集索引中找到完整的行記錄數(shù)據(jù)。

感謝你能夠認(rèn)真閱讀完這篇文章，希望小編分享的“MySQL中的索引有什么用”這篇文章對(duì)大家有幫助，同時(shí)也希望大家多多支持億速云，關(guān)注億速云行業(yè)資訊頻道，更多相關(guān)知識(shí)等著你來(lái)學(xué)習(xí)!