本篇內(nèi)容主要講解“MySQL索引機(jī)制有哪些”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“MySQL索引機(jī)制有哪些”吧!

成都創(chuàng)新互聯(lián)服務(wù)項(xiàng)目包括額敏網(wǎng)站建設(shè)、額敏網(wǎng)站制作、額敏網(wǎng)頁(yè)制作以及額敏網(wǎng)絡(luò)營(yíng)銷策劃等。多年來(lái)，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢(shì)、行業(yè)經(jīng)驗(yàn)、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機(jī)構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，額敏網(wǎng)站推廣取得了明顯的社會(huì)效益與經(jīng)濟(jì)效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到額敏省份的部分城市，未來(lái)相信會(huì)繼續(xù)擴(kuò)大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！

一、索引是什么

MySQL官方對(duì)索引的定義為：索引(Index)是幫助MySQL 高效 獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)，而MYSQL使用的數(shù)據(jù)結(jié)構(gòu)是：B+樹(shù)

在這里推薦大家看一本書，《深入理解計(jì)算機(jī)系統(tǒng)的書》

1.1 局部性原理

程序和數(shù)據(jù)的訪問(wèn)都有聚集成群的傾向，在一個(gè)時(shí)間段內(nèi)，僅使用其中一小部分，在最近的將來(lái)將用到的信息很可能與現(xiàn)在正在使用的信息在空間地址上是臨近的(稱空間局部性)，或者最近訪問(wèn)過(guò)的程序代碼和數(shù)據(jù)，很快又被訪問(wèn)的可能性很大(稱時(shí)間局部性)。

1.2 磁盤預(yù)讀

預(yù)讀的長(zhǎng)度一般為頁(yè)(page)的整數(shù)倍頁(yè)是存儲(chǔ)器的邏輯塊，操作系統(tǒng)往往將主存和磁盤存儲(chǔ)區(qū)分割成連續(xù)的大小相等的塊，每個(gè)存儲(chǔ)塊稱為一頁(yè)(在許多操作系統(tǒng)中，頁(yè)大小通常為4K)，主存和磁盤以頁(yè)為單位交換數(shù)據(jù)

1.3 簡(jiǎn)介

在使用數(shù)據(jù)庫(kù)中，通常數(shù)據(jù)庫(kù)查詢是數(shù)據(jù)庫(kù)的最主要功能之一。但每種查找算法都只能應(yīng)用于特定的數(shù)據(jù)結(jié)構(gòu)之上。

• 例如二分查找要求被檢索數(shù)據(jù)有序

• 而二叉樹(shù)查找只能應(yīng)用于二叉查找樹(shù)上，但是數(shù)據(jù)本身的組織結(jié)構(gòu)不可能完全滿足各種數(shù)據(jù)結(jié)構(gòu)(例如，理論上不可能同時(shí)將兩列都按順序進(jìn)行組織)，所以，在數(shù)據(jù)之外，數(shù)據(jù)庫(kù)系統(tǒng)還維護(hù)著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用(指向)數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實(shí)現(xiàn)高級(jí)查找算法。這種數(shù)據(jù)結(jié)構(gòu)，就是索引。索引一般以文件形式存儲(chǔ)在磁盤上，索引檢索需要磁盤I/O操作。所以評(píng)價(jià)一個(gè)數(shù)據(jù)結(jié)構(gòu)作為索引的優(yōu)劣最重要的指標(biāo)就是在查找過(guò)程中磁盤I/O操作次數(shù)的漸進(jìn)復(fù)雜度。

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)

2. 索引是幫助 MYSQL 高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)

3. 索引存儲(chǔ)在文件系統(tǒng)中

4. 索引的 文件存儲(chǔ)形式與存儲(chǔ)引擎有關(guān)

5. 索引文件的結(jié)構(gòu)：hash、二叉樹(shù)、B樹(shù)、B+樹(shù)

二、索引的分類

2.1 hash

這里有一個(gè)mysql數(shù)據(jù)文件，有Id和name兩個(gè)列，如果我們用hash格式存儲(chǔ)的話(hash表)，我們只要計(jì)算出某一個(gè)列的hash值，把它按照按照數(shù)組的長(zhǎng)度取一個(gè)模，就可以取到從0-7n個(gè)下標(biāo)的位置，這樣的話效率其實(shí)是比較高的，但是用hash表存儲(chǔ)，它具備一定的缺點(diǎn)  ：

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)

2. 利用hash存儲(chǔ)的話需要將所有的數(shù)據(jù)文件添加到內(nèi)存中，比較耗費(fèi)內(nèi)存空間

3. 如果所有的查詢都是等值查詢，那么hash確實(shí)很快，但是在企業(yè)或者實(shí)際工作環(huán)境中范圍查找的數(shù)據(jù)更多，而不是等值查詢，因?yàn)閔ash就不太適合了，因此在mysql里面并沒(méi)有選擇hash存儲(chǔ)的格式2.2  二叉樹(shù)

索引格式：

對(duì)于樹(shù)有他是有一個(gè)更新跌過(guò)的順序在里面，不要一上來(lái)就看結(jié)構(gòu)，先是了解什么樹(shù)，樹(shù)都是由一個(gè)樹(shù)根，然后有n多個(gè)分支組成，這些分支就是一些樹(shù)形結(jié)構(gòu)，多你有多個(gè)樹(shù)分支(多元素)的時(shí)候，這個(gè)時(shí)候查找效率就會(huì)比較低，因此就有了二叉樹(shù)的東西，二叉樹(shù)為什么會(huì)好用一點(diǎn)，因?yàn)槎鏄?shù)它是都有兩個(gè)分支，但是兩個(gè)分支的話，會(huì)導(dǎo)致一個(gè)效果，就是每次我們?cè)诓檎覕?shù)據(jù)的時(shí)候，類似于二分查找的，但是二叉樹(shù)也有自己不太好的地方，大家可以看我們上圖中的二叉樹(shù)的索引格式，在左邊的節(jié)點(diǎn)會(huì)比較短一點(diǎn)(只需要讀三次)，而右邊的節(jié)點(diǎn)會(huì)長(zhǎng)很多(需要讀五次)，會(huì)導(dǎo)致樹(shù)的深度比較深，每一次樹(shù)的節(jié)點(diǎn)讀取，都會(huì)有一次IO，深度越高，IO越高，會(huì)影響我們數(shù)據(jù)讀取的效率，因此也有了(平衡二叉樹(shù))和(紅黑樹(shù))

平衡二叉樹(shù)： 維護(hù)一個(gè)平衡，就是左子樹(shù)和右子樹(shù)高度之差，不能大于1，但是對(duì)于我們上面的格式就不太適合，因?yàn)樗呀?jīng)超過(guò)1了，但是AVL樹(shù)也會(huì)有一個(gè)問(wèn)題就是調(diào)整的次數(shù)太頻繁了，它里面涉及到了一個(gè)操作就是旋轉(zhuǎn)，一種左旋，一個(gè)右旋，為了保持平衡需要N多次的旋轉(zhuǎn)，這樣的旋轉(zhuǎn)其實(shí)是很浪費(fèi)時(shí)間的，每次新增或者刪除的時(shí)候，都要經(jīng)歷N多次旋轉(zhuǎn)，效率太低了

推薦大家一個(gè)網(wǎng)站，可以直接看到AVL樹(shù)操作過(guò)程，有不了解的同學(xué)可以去看一看，很形象：AVL Trees (Balanced binary search  trees)

紅黑樹(shù)： 本身也是一個(gè)平衡樹(shù)，但是它從中間做了一個(gè)權(quán)衡，就是損失一部分平衡的性能，但是又保持了相對(duì)的平衡，它做了這樣一個(gè)操作，就是最長(zhǎng)子樹(shù)的高度，只要不超過(guò)最短子樹(shù)的兩倍，就可以了，同時(shí)在紅黑樹(shù)中它引入了紅和黑兩個(gè)節(jié)點(diǎn)信息，有了這些信息它可以幫助我們做一個(gè)平衡，在AVL樹(shù)有旋轉(zhuǎn)保持平衡，而紅黑樹(shù)有了旋轉(zhuǎn)和變色兩種來(lái)保持平衡，紅黑樹(shù)是AVL樹(shù)的進(jìn)階，它損失了一部分平衡的性能，但是維護(hù)了我們插入和刪除數(shù)據(jù)的高效，雖然它損失了一部分性能，但是它依然是一個(gè)平衡樹(shù)，既然是平衡樹(shù)，他最長(zhǎng)子樹(shù)，不超過(guò)最短子樹(shù)的兩倍，那意味著如果最短子樹(shù)是  4 ，那么最長(zhǎng)子樹(shù)就是8，這樣在們查找數(shù)據(jù)的時(shí)候，又不是一個(gè)二分查找了，效率又會(huì)變低

無(wú)論是二叉樹(shù)還是紅黑樹(shù)，都會(huì)因?yàn)闃?shù)的深度過(guò)深而造成IO次數(shù)變多，影響數(shù)據(jù)的讀取的效率，最重要的就是減少IO

IO是我們IT行業(yè)中的一個(gè)瓶頸，一個(gè)是磁盤IO一個(gè)是網(wǎng)絡(luò)IO，我們作為軟件開(kāi)發(fā)，是沒(méi)有辦法去調(diào)整硬件方面的瓶頸，只能從從程序里面減少我們的IO量，我們有兩個(gè)方向，一個(gè)是減少IO的次數(shù)，一個(gè)是減少IO的量，從這兩個(gè)方面去解決，比如說(shuō)原來(lái)我們讀取數(shù)據(jù)要讀10次，現(xiàn)在只要讀取一次，這樣的IO量就少了10倍，原來(lái)我們需要讀1MB的數(shù)據(jù)，現(xiàn)在只要讀1KB的數(shù)據(jù)，這也就是為什么我們?cè)趯憁ysql查詢語(yǔ)句的時(shí)候不推薦使用select  * from  ，因?yàn)檫@樣的查詢會(huì)查詢到N多個(gè)字段，本來(lái)我只要兩個(gè)字段，但是給了我30個(gè)字段，這樣會(huì)導(dǎo)致IO量增加了，因此我們就會(huì)去考慮，關(guān)于索引的次數(shù)能不能減少，因此下面就引出了我們的——B樹(shù)

2.3 B樹(shù)

B樹(shù)的特點(diǎn)：

• 所有的鍵值分布在整顆樹(shù)中

• 搜索有可能在非葉子結(jié)點(diǎn)結(jié)束，在關(guān)鍵字全集內(nèi)做一次查找，性能逼近二分查找

• 每個(gè)節(jié)點(diǎn)最多擁有m個(gè)子樹(shù)

• 根節(jié)點(diǎn)至少有2個(gè)子樹(shù)

• 分支節(jié)點(diǎn)至少擁有m/2顆子樹(shù)(除根節(jié)點(diǎn)和葉子節(jié)點(diǎn)外都是分支節(jié)點(diǎn))

• 所有葉子節(jié)點(diǎn)都在同一層，每個(gè)節(jié)點(diǎn)最多可以有m-1個(gè)key，并且以升序排列

B樹(shù)結(jié)構(gòu)說(shuō)明：

示例圖說(shuō)明：每個(gè)節(jié)點(diǎn)占用一個(gè)磁盤塊，一個(gè)節(jié)點(diǎn)上有兩個(gè)升序排序的關(guān)鍵字和三個(gè)指向子樹(shù)根節(jié)點(diǎn)的指針，指針存儲(chǔ)的是子節(jié)點(diǎn)所在磁盤塊的地址，兩個(gè)關(guān)鍵詞劃分成的三個(gè)范圍域?qū)?yīng)三個(gè)指針指向的子樹(shù)的數(shù)據(jù)的范圍域。以根節(jié)點(diǎn)為列，關(guān)鍵字為16和34，p1指針指向的子樹(shù)的數(shù)據(jù)范圍小于16，P2指針指向的子樹(shù)的數(shù)據(jù)范圍為16-34，P3指針指向的子樹(shù)的數(shù)據(jù)范圍大于34  查找關(guān)鍵字(28)過(guò)程：

• 根據(jù)節(jié)點(diǎn)找到磁盤塊1，讀取內(nèi)存【磁盤I/O操作第1次】

• 比較關(guān)鍵字28在區(qū)間(16,34)找到磁盤塊1的指針P2

• 根據(jù)P2指針找到磁盤塊3，讀入內(nèi)存【磁盤I/O操作第2次】

• 比較關(guān)鍵字28在區(qū)間(25,31)，找到磁盤塊3的指針P2

• 根據(jù)P2指針找到磁盤塊8，讀取內(nèi)存，【磁盤I/O操作第3次】

• 在磁盤塊8中的關(guān)鍵字列表找到關(guān)鍵字28

缺點(diǎn)：

• 每個(gè)節(jié)點(diǎn)都有key，同時(shí)也包含data，而每個(gè)頁(yè)存儲(chǔ)空間是有限的，如果data比較大的話會(huì)導(dǎo)致每個(gè)節(jié)點(diǎn)存儲(chǔ)的key數(shù)量變小

• 當(dāng)存儲(chǔ)的數(shù)據(jù)量很大的時(shí)候會(huì)導(dǎo)致深度較大，增大查詢時(shí)磁盤IO次數(shù)，進(jìn)而影響查詢性能

2.4 B+樹(shù)

B+Tree 是在BTree 的基礎(chǔ)之上做的一種優(yōu)化，變化如下：

• B+Tree 每個(gè)節(jié)點(diǎn)可以包含更多的節(jié)點(diǎn)，這個(gè)做的  原因有兩個(gè)，第一個(gè)原因是為了降低樹(shù)的高度，第二個(gè)原因是將數(shù)據(jù)范圍變?yōu)槎鄠€(gè)區(qū)間，區(qū)間越多，數(shù)據(jù)檢索的越快

• 非葉子節(jié)點(diǎn)存儲(chǔ)key(1,2,3磁盤都是存儲(chǔ)的key)，葉子節(jié)點(diǎn)存儲(chǔ)key和數(shù)據(jù)

• 葉子節(jié)點(diǎn)兩兩指針相互連接(符合磁盤的預(yù)讀特性)順序查詢性能更高如果當(dāng)前磁盤塊下沒(méi)有其他節(jié)點(diǎn)，就是 葉子節(jié)點(diǎn)，反之就是 非葉子節(jié)點(diǎn)

結(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ī)查找。

三、mysql的存儲(chǔ)引擎

3.1 mysql innoDB (葉子節(jié)點(diǎn)直接放置數(shù)據(jù))

3.1 mysql innoDB (葉子節(jié)點(diǎn)直接放置數(shù)據(jù))

存放的是對(duì)應(yīng)的行記錄

1、InnoDB是通過(guò)B+Tree結(jié)構(gòu)對(duì)主鍵創(chuàng)建索引，然后葉子節(jié)點(diǎn)中存儲(chǔ)記錄，如果沒(méi)有主鍵，那么會(huì)選擇唯一鍵，如果沒(méi)有唯一鍵，那么會(huì)生成一個(gè)6位的row_id來(lái)作為主鍵

2、如果創(chuàng)建索引的鍵是其他字段，那么在葉子節(jié)點(diǎn)中存儲(chǔ)的是該記錄的主鍵，然后在通過(guò)主鍵索引找到對(duì)應(yīng)的記錄

在name上建立索引

在name列上存放的是ID，然后通過(guò)ID去找到對(duì)應(yīng)的key和數(shù)據(jù)

3.1 mysql MyISAM

下面0X0022其實(shí)就是地址，顯示根據(jù)我們的ID，找到我們的地址，然后通過(guò)地址去找到對(duì)應(yīng)的表對(duì)應(yīng)的數(shù)據(jù)

四、索引的分類

mysql索引的五種類型：主鍵索引、唯一索引、普通索引和全文索引、組合索引。通過(guò)給字段添加索引可以提高數(shù)據(jù)的讀取速度，提高項(xiàng)目的并發(fā)能力和抗壓能力

• 主鍵索引：> 主鍵是一種唯一性索引，但它必須指定為PRIMARY KEY，每個(gè)表只能有一個(gè)主鍵

• 唯一索引 > 索引列的所有值都只能出現(xiàn)一次，即必須唯一，值可以為空

• 普通索引 > 基本的索引類型，值可以為空，沒(méi)有唯一性的限制

• 全文索引 > 全文索引的索引類型為FULLTEXT，全文索引可以在 varchar、char、text類型的列上創(chuàng)建

• 組合索引 > 多列值組成的一個(gè)索引，專門用于組合搜索

五、mysql的存儲(chǔ)引擎

到此，相信大家對(duì)“MySQL索引機(jī)制有哪些”有了更深的了解，不妨來(lái)實(shí)際操作一番吧！這里是創(chuàng)新互聯(lián)網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！