索引在mysql中怎么存儲(chǔ)的

聚集索引：也稱 Clustered Index。是指關(guān)系表記錄的物理順序與索引的邏輯順序相同。由于一張表只能按照一種物理順序存放，一張表最多也只能存在一個(gè)聚集索引。與非聚集索引相比，聚集索引有著更快的檢索速度。

創(chuàng)新互聯(lián)公司長(zhǎng)期為成百上千家客戶提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊(duì)從業(yè)經(jīng)驗(yàn)10年，關(guān)注不同地域、不同群體，并針對(duì)不同對(duì)象提供差異化的產(chǎn)品和服務(wù)；打造開放共贏平臺(tái)，與合作伙伴共同營(yíng)造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為解放企業(yè)提供專業(yè)的網(wǎng)站制作、做網(wǎng)站，解放網(wǎng)站改版等技術(shù)服務(wù)。擁有10年豐富建站經(jīng)驗(yàn)和眾多成功案例,為您定制開發(fā)。

MySQL 里只有 INNODB 表支持聚集索引，INNODB 表數(shù)據(jù)本身就是聚集索引，也就是常說 IOT，索引組織表。非葉子節(jié)點(diǎn)按照主鍵順序存放，葉子節(jié)點(diǎn)存放主鍵以及對(duì)應(yīng)的行記錄。所以對(duì) INNODB 表進(jìn)行全表順序掃描會(huì)非常快。

非聚集索引：也叫 Secondary Index。指的是非葉子節(jié)點(diǎn)按照索引的鍵值順序存放，葉子節(jié)點(diǎn)存放索引鍵值以及對(duì)應(yīng)的主鍵鍵值。MySQL 里除了 INNODB 表主鍵外，其他的都是二級(jí)索引。MYISAM，memory 等引擎的表索引都是非聚集索引。簡(jiǎn)單點(diǎn)說，就是索引與行數(shù)據(jù)分開存儲(chǔ)。一張表可以有多個(gè)二級(jí)索引。

「Mysql索引原理（六）」聚簇索引

? ?本節(jié)課主要關(guān)注InnoDB，但是這里討論的原理對(duì)于任何支持聚簇索引的存儲(chǔ)引擎都是適用的。

? ?葉子節(jié)點(diǎn)包含了全部數(shù)據(jù)，其他節(jié)點(diǎn)只包含索引列。InnoDB將通過主鍵聚集數(shù)據(jù)，也就是說上圖中的“被索引的列”就是主鍵列。如果沒有定義主鍵，InnoDB會(huì)選擇一個(gè)唯一的非空索引代替。如果沒有這樣的索引InnoDB會(huì)隱式定義一個(gè)主鍵來作為聚簇索引。

? ?如果主鍵比較大的話，那輔助索引將會(huì)變的更大，因?yàn)?輔助索引的葉子存儲(chǔ)的是主鍵值；過長(zhǎng)的主鍵值，會(huì)導(dǎo)致非葉子節(jié)點(diǎn)占用占用更多的物理空間

所以建議使用int的auto_increment作為主鍵

? ?主鍵的值是順序的，所以 InnoDB 把每一條記錄都存儲(chǔ)在上一條記錄的后面。當(dāng)達(dá)到頁的最大值時(shí)，下一條記錄就會(huì)寫入新的頁中。一旦數(shù)據(jù)按照這種順序的方式加載，主鍵頁就會(huì)近似于被順序的記錄填滿。

? ?聚簇索引的數(shù)據(jù)的物理存放順序與索引順序是一致的，即：只要索引是相鄰的，那么對(duì)應(yīng)的數(shù)據(jù)一定也是相鄰地存放在磁盤上的。如果主鍵不是自增id，那么可以想 象，它會(huì)干些什么，不斷地調(diào)整數(shù)據(jù)的物理地址、分頁，當(dāng)然也有其他一些措施來減少這些操作，但卻無法徹底避免。但，如果是自增的，那就簡(jiǎn)單了，它只需要一 頁一頁地寫，索引結(jié)構(gòu)相對(duì)緊湊，磁盤碎片少，效率也高。

? ?因?yàn)镸yISAM的主索引并非聚簇索引，那么他的數(shù)據(jù)的物理地址必然是凌亂的，拿到這些物理地址，按照合適的算法進(jìn)行I/O讀取，于是開始不停的尋道不停的旋轉(zhuǎn)。聚簇索引則只需一次I/O。（強(qiáng)烈的對(duì)比）

? ?不過，如果涉及到大數(shù)據(jù)量的排序、全表掃描、count之類的操作的話，還是MyISAM占優(yōu)勢(shì)些，因?yàn)樗饕伎臻g小，這些操作是需要在內(nèi)存中完成的。

? ?MyISM使用的是非聚簇索引， 非聚簇索引的兩棵B+樹看上去沒什么不同 ，節(jié)點(diǎn)的結(jié)構(gòu)完全一致只是存儲(chǔ)的內(nèi)容不同而已，主鍵索引B+樹的節(jié)點(diǎn)存儲(chǔ)了主鍵，輔助鍵索引B+樹存儲(chǔ)了輔助鍵。表數(shù)據(jù)存儲(chǔ)在獨(dú)立的地方，這兩顆B+樹的葉子節(jié)點(diǎn)都使用一個(gè)地址指向真正的表數(shù)據(jù)，對(duì)于表數(shù)據(jù)來說，這兩個(gè)鍵沒有任何差別。由于 索引樹是獨(dú)立的，通過輔助鍵檢索無需訪問主鍵的索引樹 。

? ?所以說，聚簇索引性能最好而且具有唯一性，所以非常珍貴，必須慎重設(shè)置。 一般要根據(jù)這個(gè)表最常用的SQL查詢方式來進(jìn)行選擇，某個(gè)字段作為聚簇索引，或組合聚簇索引 ，這個(gè)要看實(shí)際情況。

? ?聚簇索引和非聚簇索引的數(shù)據(jù)分布有區(qū)別，主鍵索引和二級(jí)索引的數(shù)據(jù)分布也有區(qū)別，通常會(huì)讓人感到困擾和以外，下面通過一個(gè)列子來講解InnoDB和MyISAM是如何存儲(chǔ)數(shù)據(jù)的：

? ?該表的主鍵取值1~10000，按照隨機(jī)順序插入并使用optimize table命令做了優(yōu)化。換句話說，數(shù)據(jù)在磁盤上的存儲(chǔ)方式已是最優(yōu)，但行的順序是隨機(jī)的。列col2的值是從1~100之間隨機(jī)賦值，所以有很多重復(fù)的值。

? ?MyISAM的數(shù)據(jù)分布很簡(jiǎn)單，所以先介紹它。MyISAM按照數(shù)據(jù)插入的順序存儲(chǔ)在磁盤上，如下圖所示：

在行的旁邊顯示行號(hào)，從0開始遞增。因?yàn)樾惺嵌ㄩL(zhǎng)的，所以MyISAM可以從表的開頭跳過所需的字節(jié)找到需要的行。

col2上的索引

? ?事實(shí)上，MyISAM中主鍵索引和其他索引在結(jié)構(gòu)上沒有什么不同。主鍵索引就是一個(gè)名為PRIMARY的唯一非空索引。

? ?InnoDB支持聚簇索引，所以使用不同的方式存儲(chǔ)同樣的數(shù)據(jù)。

? ?第一眼看上去，感覺和前面的沒什么區(qū)別，但是該圖顯示了整個(gè)表，而不是只有索引。因?yàn)樵贗nnoDB中，聚簇索引就是表，所以不像MyISAM那樣需要獨(dú)立的行存儲(chǔ)，這也是為什么MyISAM索引和數(shù)據(jù)結(jié)構(gòu)是分開的。

? ?聚簇索引的每一個(gè)葉子節(jié)點(diǎn)都包含了主鍵值。事務(wù)ID、用于事務(wù)和MVCC的回滾指針以及所有的剩余列。如果主鍵是一個(gè)列前綴索引，InnoDB也會(huì)包含完整的主鍵列和剩下的其他列。

? ?還有一點(diǎn)和MyISAM不同的是，InnoDB的二級(jí)索引和聚簇索引很不相同。InnoDB的二級(jí)索引的葉子節(jié)點(diǎn)中存儲(chǔ)的不是“行指針”，而是主鍵值，并以此作為指向行的“指針”。這樣的策略減少了當(dāng)出現(xiàn)行移動(dòng)或者數(shù)據(jù)頁分裂時(shí)二級(jí)索引的維護(hù)工作。使用主鍵值當(dāng)作指針會(huì)讓二級(jí)索引占用更多的空間，換來的好處是，InnoDB在移動(dòng)時(shí)無需更新二級(jí)索引中的這個(gè)“指針”。

? ?我們?cè)趤砜匆幌?col2索引 。

? ?每一個(gè)葉子節(jié)點(diǎn)包含了索引列（這里是col2），緊接著是主鍵值（col1），上圖我們省略了非葉子節(jié)點(diǎn)這樣的細(xì)節(jié)。InnoDB非葉子節(jié)點(diǎn)包含了索引列和一個(gè)指向下一級(jí)節(jié)點(diǎn)的指針。

? ?最后，以一張圖表示InnoDB和MyISAM保存數(shù)據(jù)和索引的區(qū)別。

? ?前面講過，最好使用AUTO_INCREMENT自增列來聚集數(shù)據(jù)，避免隨機(jī)的、不連續(xù)的、值分布范圍大的列做聚簇索引，特別是對(duì)于I/O密集型的應(yīng)用。例如，從性能角度考慮，使用UUID來作為聚簇索引則會(huì)很糟糕：他使得聚簇索引的插入變得完全隨機(jī)，這是最壞的情況，使得數(shù)據(jù)沒有任何聚集特性。

? ?為了演示這一點(diǎn)，我們做兩個(gè)基準(zhǔn)測(cè)試：

1、使用證書ID插入userinfo表，和uuid作為主鍵的userinfo_uuid表

? ?userinfo_uuid表跟userinfo表除了主鍵給為UUID，其他字段都一樣

? ?測(cè)試這兩個(gè)表的設(shè)計(jì)，首先在一個(gè)有足夠內(nèi)存容納索引的服務(wù)器上向這兩個(gè)表各插入100萬條記錄。然后向兩個(gè)表繼續(xù)插入300萬數(shù)據(jù)，使索引的大小超過服務(wù)器的內(nèi)存容量。測(cè)試結(jié)果如下：

? ?向UUID主鍵插入行不僅花費(fèi)的時(shí)間更長(zhǎng)，而且索引占用的空間也更大。這一方面是由于主鍵字段更長(zhǎng)，另一方面毫無疑問是由于頁分裂和碎片導(dǎo)致的。

? ?為了明白為什么會(huì)這樣，來看看往第一個(gè)表中插入數(shù)據(jù)時(shí)，索引發(fā)生了什么變化。

自整型主鍵插入

? ?因?yàn)橹麈I是順序的，所以InnoDB把每一條記錄都存在上一條記錄的后面。當(dāng)達(dá)到頁的最大容量后，下一條記錄就會(huì)寫入到新的頁中。一旦數(shù)據(jù)按照這種順序的方式加載，主鍵頁就會(huì)近似于被順序的記錄填滿，這也正是所期望的結(jié)果。

UUID插入

? ?因?yàn)樾滦械闹麈I值不一定比之前插入的大，所以InnoDB無法簡(jiǎn)單的總是把新行插入到索引的最后，而是需要為新的行尋找合適的位置，通常是已有數(shù)據(jù)的中間位置，并且分配空間。這會(huì)正價(jià)很多的額外工作，并導(dǎo)致數(shù)據(jù)分布不夠優(yōu)化。

缺點(diǎn)：

把這些隨機(jī)值載入到聚簇索引后，也許需要做一次OPTIMIZE TABLE來重建表并優(yōu)化頁的填充。

結(jié)論 ：使用InnoDB時(shí)應(yīng)盡可能地按主鍵順序插入數(shù)據(jù)，并且盡可能地單調(diào)增加聚簇鍵的值來插入新行。

徹底搞懂MySql的B+Tree

官方定義：一種能為mysql提高查詢效率的數(shù)據(jù)結(jié)構(gòu)，索引是為了加速對(duì)表中數(shù)據(jù)行的檢索而創(chuàng)建的一種分散存儲(chǔ)的數(shù)據(jù)結(jié)構(gòu)。好比如，一本書，你想找到自己想看的章節(jié)內(nèi)容，直接查詢目錄就行。這里的目錄就類似索引的意思。

如上圖中，如果現(xiàn)在有一條sql語句 select * from user where id = 40，如果沒有索引的條件下，我們要找到這條記錄，我們就需要在數(shù)據(jù)中進(jìn)行全表掃描，匹配id = 40的數(shù)據(jù)。

如果有了索引，我們就可以通過索引進(jìn)行快速查找，如上圖中，可以先在索引中通過id = 40進(jìn)行二分查找，再根據(jù)定位到的地址取出對(duì)應(yīng)的行數(shù)據(jù)。

現(xiàn)在看來，索引是不是也不過如此。咋們接著往下看。

那么有的同學(xué)可能會(huì)問，既然索引缺點(diǎn)這么多，那我為什么還要用索引啊？也就是提高了查詢速度而已。

提高了查詢速度呀，這個(gè)絕對(duì)是個(gè)大優(yōu)勢(shì)，在數(shù)據(jù)量龐大的情況下，我們通過命中索引，能大大的提高查詢速度，增刪改基本消耗忽略不計(jì)。摘抄阿里P3C開發(fā)規(guī)范。

我們先來看一個(gè)sql

執(zhí)行完后：

奇怪？為什么數(shù)據(jù)和我插入的順序不一致呢，竟然給我自動(dòng)排序好了！！！我們接著看

其實(shí)mysql每條數(shù)據(jù)的存儲(chǔ)是這樣子的（圖自己畫的，—_—,將就下）

mysql給我們提供了頁的概念，并且有頁目錄，頁目錄數(shù)據(jù)為葉族節(jié)點(diǎn)每頁的第一條數(shù)據(jù)id，頁目錄和每頁大小均默認(rèn)為16KB，如下圖：

舉個(gè)例子：

那么有的小伙伴可能會(huì)問，你這樣也存不了多少數(shù)據(jù)呀，那假如我數(shù)據(jù)量非常多呢，這顆數(shù)怎么存呢。

以上表而言，一個(gè)id占用8個(gè)字節(jié)（long類型），name 20個(gè)字節(jié)，p指針也要占用字節(jié)的（大概4~8個(gè)字節(jié)），我們以最大8來算，那么一條數(shù)據(jù)大概就是：8+20+8=36，36個(gè)字節(jié)，那么一頁換算一下是 16x1024 = 16384 個(gè)字節(jié)，那么葉子節(jié)點(diǎn)一頁可以存儲(chǔ)數(shù)據(jù)量為：16384/36 = 455 條數(shù)據(jù)。那么頁目錄又存著id，一個(gè)id8個(gè)字節(jié)，能存儲(chǔ)16x1024/8 =2048，2048x455 = 931,840 ...粗略的算了下3層數(shù)，能存儲(chǔ)數(shù)據(jù)量為1,908,408,320個(gè) 很多了，可能表的字段很多的話，存儲(chǔ)數(shù)據(jù)量稍微少點(diǎn)，但是也很多了。

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

這個(gè)時(shí)候有個(gè)問題思考下？為什么mysql推薦ID自增呢？這個(gè)時(shí)候是不是心里有了答案呢？或許自己可以先想想再看。

我們?nèi)粘９ぷ髦校鶕?jù)實(shí)際情況自行添加的索引都是輔助索引，輔助索引就是一個(gè)為了需找主鍵索引的二級(jí)索引，現(xiàn)在找到主鍵索引再通過主鍵索引找數(shù)據(jù)； （這就是所謂的回表查詢）

聚簇索引就是按照每張表的主鍵構(gòu)造一顆B+樹，同時(shí)葉子節(jié)點(diǎn)中存放的就是整張表的行記錄數(shù)據(jù)，也將聚集索引的葉子節(jié)點(diǎn)稱為數(shù)據(jù)頁。這個(gè)特性決定了索引組織表中數(shù)據(jù)也是索引的一部分，每張表只能擁有一個(gè)聚簇索引。

聚簇索引并不是一種單獨(dú)的索引類型，而 是一種數(shù)據(jù)存儲(chǔ)方式 。具體細(xì)節(jié)依賴于其實(shí)現(xiàn)方式。

聚簇索引的優(yōu)缺點(diǎn)

優(yōu)點(diǎn)：

1.數(shù)據(jù)訪問更快，因?yàn)榫鄞厮饕龑⑺饕蛿?shù)據(jù)保存在同一個(gè)B+樹中，因此從聚簇索引中獲取數(shù)據(jù)比非聚簇索引更快

2.聚簇索引對(duì)于主鍵的排序查找和范圍查找速度非常快 缺點(diǎn)：

1.插入速度嚴(yán)重依賴于插入順序，按照主鍵的 順序插入 是最快的方式，否則將會(huì)出現(xiàn)頁分裂，嚴(yán)重影響性能。因此，對(duì)于InnoDB表，我們一般都會(huì)定義一個(gè) 自增的ID列為主鍵 2. 更新主鍵的代價(jià)很高 ，因?yàn)閷?huì)導(dǎo)致被更新的行移動(dòng)。因此，對(duì)于InnoDB表，我們一般定義主鍵為不可更新。 3.二級(jí)索引訪問需要兩次索引查找，第一次找到主鍵值，第二次根據(jù)主鍵值找到行數(shù)據(jù)。

在 聚簇索引之上創(chuàng)建的索引稱之為輔助索引 ，輔助索引訪問數(shù)據(jù)總是需要二次查找。輔助索引葉子節(jié)點(diǎn)存儲(chǔ)的不再是行的物理位置，而是主鍵值。通過輔助索引首先找到的是主鍵值，再通過主鍵值找到數(shù)據(jù)行的數(shù)據(jù)頁，再通過數(shù)據(jù)頁中的Page Directory找到數(shù)據(jù)行。

--以上可能沒有說完整，或者有遺漏的地方，歡迎補(bǔ)充！！！

網(wǎng)站題目：mysql葉子節(jié)點(diǎn)怎么存,mysql葉子節(jié)點(diǎn)最大數(shù)據(jù)大小
文章地址：http://chinadenli.net/article11/dseopdd.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供面包屑導(dǎo)航、微信小程序、外貿(mào)網(wǎng)站建設(shè)、品牌網(wǎng)站設(shè)計(jì)、移動(dòng)網(wǎng)站建設(shè)、網(wǎng)站策劃

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)