前言

在 MySQL 官方提到市栗，改善操作性能的最佳方法 [SELECT](https://dev.mysql.com/doc/refman/5.7/en/select.html)在查詢中測試的一個(gè)或多個(gè)列上創(chuàng)建索引鲫骗。索引條目的作用類似于指向表行的指針，從而使查詢可以快速確定哪些行與WHERE子句中的條件匹配杠人，并檢索這些行的其他列值勋乾。所有MySQL數(shù)據(jù)類型都可以建立索引。

盡管可能會(huì)為查詢中使用的每個(gè)可能的列創(chuàng)建索引搜吧，但不必要的索引會(huì)浪費(fèi)空間和時(shí)間市俊，使MySQL難以確定要使用的索引。索引還會(huì)增加插入滤奈，更新和刪除的成本摆昧，因?yàn)楸仨毟旅總€(gè)索引。您必須找到適當(dāng)?shù)钠胶庋殉蹋拍苁褂米罴阉饕瘉韺?shí)現(xiàn)快速查詢绅你。

那么，索引到底是什么昭躺？透過現(xiàn)象看本質(zhì)：

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

另外领炫，阿里巴巴《Java 開發(fā)手冊(cè)》提出單表行數(shù)超過 500 萬行或者單表容量超過 2GB偶垮，才推薦進(jìn)行分庫分表。對(duì)此，有阿里的黃金鐵律支撐似舵，所以脚猾，很多人設(shè)計(jì)大數(shù)據(jù)存儲(chǔ)時(shí)，多會(huì)以此為標(biāo)準(zhǔn)砚哗，進(jìn)行分表操作龙助。

以及，阿里巴巴《Java 開發(fā)手冊(cè)》補(bǔ)充到：如果預(yù)計(jì)三年后的數(shù)據(jù)量根本達(dá)不到這個(gè)級(jí)別蛛芥，請(qǐng)不要在創(chuàng)建表時(shí)就分庫分表提鸟。

為了更深入理解索引的本質(zhì)，這里我們先了解一下磁盤相關(guān)知識(shí)仅淑。

外存儲(chǔ)器-磁盤

計(jì)算機(jī)一般有兩種存儲(chǔ)的方式：內(nèi)存儲(chǔ)器(main memory)和外存儲(chǔ)器(external memory)

• 內(nèi)存:讀寫速度非吵蒲快，但是容量很小漓糙，而且造價(jià)非常貴铣缠，在不通電的情況下會(huì)數(shù)據(jù)會(huì)丟失，不能長期存儲(chǔ)數(shù)據(jù)昆禽；
• 外存：磁盤是相對(duì)常見的外存儲(chǔ)設(shè)備，它是以存取時(shí)間變化不大為特征的蝇庭∽肀睿可以直接存取任何字符組，且容量大哮内、速度較其它外存設(shè)備更快盗棵。

磁盤的構(gòu)造

磁盤是一個(gè)扁平的圓盤(與電唱機(jī)的唱片類似)。盤面上有許多稱為磁道的圓圈北发，數(shù)據(jù)就記錄在這些磁道上纹因。磁盤可以是單片的，也可以是由若干盤片組成的盤組琳拨，每一盤片上有兩個(gè)面瞭恰。

一般磁盤分為固定頭盤(磁頭固定)和活動(dòng)頭盤颜启。

固定頭盤的每一個(gè)磁道上都有獨(dú)立的磁頭，它是固定不動(dòng)的浪讳，專門負(fù)責(zé)這一磁道上數(shù)據(jù)的讀/寫缰盏。

活動(dòng)頭盤的磁頭是可移動(dòng)的。每一個(gè)盤面上只有一個(gè)磁頭(磁頭是雙向的，因此正反盤面都能讀寫)口猜。它可以從該面的一個(gè)磁道移動(dòng)到另一個(gè)磁道形葬，所有磁頭都裝在同一個(gè)動(dòng)臂上，因此不同盤面上的所有磁頭都是同時(shí)移動(dòng)的(行動(dòng)整齊劃一)暮的，當(dāng)盤片繞主軸旋轉(zhuǎn)的時(shí)候笙以，磁頭與旋轉(zhuǎn)的盤片形成一個(gè)圓柱體，各個(gè)盤面上半徑相同的磁道組成了一個(gè)圓柱面冻辩，我們稱為柱面猖腕。因此，柱面的個(gè)數(shù)也就是盤面上的磁道數(shù)恨闪。

磁盤的讀/寫原理和效率

磁盤上數(shù)據(jù)必須用一個(gè)三維地址唯一標(biāo)示：柱面號(hào)倘感、盤面號(hào)、塊號(hào)(磁道上的盤塊)咙咽。

讀/寫磁盤上某一指定數(shù)據(jù)需要下面3個(gè)步驟：

1. 首先移動(dòng)臂根據(jù)柱面號(hào)使磁頭移動(dòng)到所需要的柱面上老玛，這一過程被稱為定位或查找。
2. 如上圖6盤組示意圖中钧敞，所有磁頭都定位到了10個(gè)盤面的10條磁道上(磁頭都是雙向的)蜡豹，這時(shí)根據(jù)盤面號(hào)來確定指定盤面上的磁道。
3. 盤面確定以后溉苛，盤片開始旋轉(zhuǎn)镜廉，將指定塊號(hào)的磁道段移動(dòng)至磁頭下。

經(jīng)過上面三個(gè)步驟愚战，指定數(shù)據(jù)的存儲(chǔ)位置就被找到娇唯，這時(shí)就可以開始讀/寫操作了。

訪問某一具體信息寂玲，由3部分時(shí)間組成：

• 查找時(shí)間(seek time) Ts: 完成上述步驟(1)所需要的時(shí)間塔插。這部分時(shí)間代價(jià)最高，最大可達(dá)到0.1s左右拓哟；
• 等待時(shí)間(latency time) Tl: 完成上述步驟(3)所需要的時(shí)間想许。由于盤片繞主軸旋轉(zhuǎn)速度很快，一般為7200轉(zhuǎn)/分(電腦硬盤的性能指標(biāo)之一, 家用的普通硬盤的轉(zhuǎn)速一般有5400rpm(筆記本)彰檬、7200rpm幾種)伸刃，因此一般旋轉(zhuǎn)一圈大約0.0083s；
• 傳輸時(shí)間(transmission time) Tt: 數(shù)據(jù)通過系統(tǒng)總線傳送到內(nèi)存的時(shí)間逢倍，一般傳輸一個(gè)字節(jié)(byte)大概0.02us=2*10^(-8)s捧颅。

尋道時(shí)間Ts :
　　　　

n : 跨越n條磁道的時(shí)間； s： 啟動(dòng)磁臂的時(shí)間较雕，約為2ms 碉哑； m：與磁盤驅(qū)動(dòng)器速度有關(guān)的常數(shù)挚币，約為0.2ms。

延遲時(shí)間Tr :
　　　　

r ： 磁盤的旋轉(zhuǎn)速度

傳輸時(shí)間Tt :
　　 　

r ： 磁盤的旋轉(zhuǎn)速度扣典； N：為一個(gè)磁道上的字節(jié)數(shù)妆毕；b：每次所讀/寫的字節(jié)數(shù)b

總平均存取時(shí)間 :
　　

磁盤讀取數(shù)據(jù)是以盤塊(block)為基本單位的。位于同一盤塊中的所有數(shù)據(jù)都能被一次性全部讀取出來贮尖。而磁盤IO代價(jià)主要花費(fèi)在查找時(shí)間Ts上笛粘，因此我們應(yīng)該盡量將相關(guān)信息存放在同一盤塊，同一磁道中湿硝，或者至少放在同一柱面或相鄰柱面上薪前，以求在讀/寫信息時(shí)盡量減少磁頭來回移動(dòng)的次數(shù)，避免過多的查找時(shí)間Ts关斜。

所以示括，在大規(guī)模數(shù)據(jù)存儲(chǔ)方面，大量數(shù)據(jù)存儲(chǔ)在外存磁盤中痢畜，而在外存磁盤中讀取/寫入塊(block)中某數(shù)據(jù)時(shí)垛膝，首先需要定位到磁盤中的某塊，如何有效地查找磁盤中的數(shù)據(jù)丁稀，需要一種合理高效的外存數(shù)據(jù)結(jié)構(gòu)吼拥。

索引的本質(zhì)

索引是幫助MySQL高效獲取數(shù)據(jù)的排好序的數(shù)據(jù)結(jié)構(gòu)

索引數(shù)據(jù)結(jié)構(gòu)，主要包含以下幾類二驰，我們來對(duì)比下

• 二叉樹
• 平衡二叉樹
• 紅黑樹
• Hash表
• B-Tree

二叉樹

定義：每個(gè)結(jié)點(diǎn)最多有兩個(gè)子樹扔罪，左子樹比父節(jié)點(diǎn)小，右子樹比父節(jié)點(diǎn)大桶雀。

缺點(diǎn)：會(huì)出現(xiàn)極端情況導(dǎo)致整棵樹只有左子樹或只有右子樹。

平衡二叉樹（AVL Tree）

定義：平衡二叉樹又稱AVL樹唬复，是一種特殊的二叉查找樹矗积，其左右子數(shù)都是平衡二叉樹，且左右子樹高度差的絕對(duì)值不超過1敞咧。

缺點(diǎn)：AVL樹是高度平衡的棘捣，頻繁的插入和刪除，會(huì)引起頻繁的rebalance休建，導(dǎo)致效率下降乍恐。

紅黑樹

定義：

• 性質(zhì)1. 節(jié)點(diǎn)是紅色或黑色。
• 性質(zhì)2. 根節(jié)點(diǎn)是黑色测砂。
• 性質(zhì)3 每個(gè)紅色節(jié)點(diǎn)的兩個(gè)子節(jié)點(diǎn)都是黑色茵烈。(從每個(gè)葉子到根的所有路徑上不能有兩個(gè)連續(xù)的紅色節(jié)點(diǎn))
• 性質(zhì)4. 從任一節(jié)點(diǎn)到其每個(gè)葉子的所有路徑都包含相同數(shù)目的黑色節(jié)點(diǎn)。

缺點(diǎn)：數(shù)據(jù)量大會(huì)導(dǎo)致樹層數(shù)比較多砌些，這樣就會(huì)造成查找數(shù)據(jù)慢呜投。

Hash數(shù)據(jù)結(jié)構(gòu)

定義：散列表（Hash table加匈，也叫哈希表），是根據(jù)關(guān)鍵碼值(Key value)而直接進(jìn)行訪問的數(shù)據(jù)結(jié)構(gòu)仑荐。也就是說雕拼，它通過把關(guān)鍵碼值映射到表中一個(gè)位置來訪問記錄，以加快查找的速度粘招。這個(gè)映射函數(shù)叫做散列函數(shù)啥寇，存放記錄的數(shù)組叫做散列表。 對(duì)目標(biāo)值進(jìn)行hash運(yùn)算得到hash值和數(shù)據(jù)磁盤指針地址保存到hash表洒扎，這樣就達(dá)到快速定位數(shù)據(jù)位置辑甜。

缺點(diǎn)：精確查找十分快速，但范圍查找就碰壁了逊笆。

BTree

定義：

• 一個(gè)節(jié)點(diǎn)可以存儲(chǔ)多個(gè)數(shù)據(jù)栈戳，這樣可以避免黑紅樹的缺點(diǎn)，樹的層數(shù)很變心疡伞子檀；
• 葉節(jié)點(diǎn)具有相同的深度，葉節(jié)點(diǎn)的指針為空乃戈；
• 所有索引元素不重復(fù)褂痰；
• 節(jié)點(diǎn)中的數(shù)據(jù)索引從左到右遞增排列。

缺點(diǎn)：節(jié)點(diǎn)里面數(shù)組數(shù)據(jù)：每個(gè)數(shù)據(jù)的結(jié)構(gòu)=索引數(shù)據(jù)+數(shù)據(jù)記錄（即葉子節(jié)點(diǎn)存儲(chǔ)鍵值和數(shù)據(jù)記錄）症虑。

每個(gè)節(jié)點(diǎn)占用一個(gè)盤塊的磁盤空間缩歪，一個(gè)節(jié)點(diǎn)上有兩個(gè)升序排序的關(guān)鍵字和三個(gè)指向子樹根節(jié)點(diǎn)的指針，指針存儲(chǔ)的是子節(jié)點(diǎn)所在磁盤塊的地址谍憔。兩個(gè)關(guān)鍵詞劃分成的三個(gè)范圍域?qū)?yīng)三個(gè)指針指向的子樹的數(shù)據(jù)的范圍域匪蝙。以根節(jié)點(diǎn)為例，關(guān)鍵字為17和35习贫，P1指針指向的子樹的數(shù)據(jù)范圍為小于17逛球，P2指針指向的子樹的數(shù)據(jù)范圍為17~35，P3指針指向的子樹的數(shù)據(jù)范圍為大于35苫昌。

模擬查找關(guān)鍵字29的過程：

1. 根據(jù)根節(jié)點(diǎn)找到磁盤塊1颤绕，讀入內(nèi)存∷钌恚【磁盤I/O操作第1次】
2. 比較關(guān)鍵字29在區(qū)間（17,35）奥务，找到磁盤塊1的指針P2。
3. 根據(jù)P2指針找到磁盤塊3袜硫，讀入內(nèi)存氯葬。【磁盤I/O操作第2次】
4. 比較關(guān)鍵字29在區(qū)間（26,30）父款，找到磁盤塊3的指針P2溢谤。
5. 根據(jù)P2指針找到磁盤塊8瞻凤，讀入內(nèi)存域帐∈嵝樱【磁盤I/O操作第3次】
6. 在磁盤塊8中的關(guān)鍵字列表中找到關(guān)鍵字29抓半。

分析上面過程片择，發(fā)現(xiàn)需要3次磁盤I/O操作惨险，和3次內(nèi)存查找操作句各。由于內(nèi)存中的關(guān)鍵字是一個(gè)有序表結(jié)構(gòu)寞缝，可以利用二分法查找提高效率符欠。而3次磁盤I/O操作是影響整個(gè)B-Tree查找效率的決定因素所刀。B-Tree相對(duì)于AVLTree縮減了節(jié)點(diǎn)個(gè)數(shù)衙荐，使每次磁盤I/O取到內(nèi)存的數(shù)據(jù)都發(fā)揮了作用，從而提高了查詢效率浮创。

B+Tree

定義：B+Tree是在B-Tree基礎(chǔ)上的一種優(yōu)化忧吟。節(jié)點(diǎn)里面數(shù)組數(shù)據(jù)：每個(gè)數(shù)據(jù)只存儲(chǔ)鍵信息，這樣不存數(shù)據(jù)可以騰出空間放更多的鍵信息斩披，讓樹層數(shù)越小

• 非葉子節(jié)點(diǎn)不存儲(chǔ)data溜族，只存儲(chǔ)索引(冗余)，可以放更多的索引
• 葉子節(jié)點(diǎn)包含所有索引字段
• 葉子節(jié)點(diǎn)用指針連接垦沉，提高區(qū)間訪問的性能

將上一節(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ì)于主鍵的范圍查找和分頁查找，另一種是從根節(jié)點(diǎn)開始组民，進(jìn)行隨機(jī)查找坏挠。

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

InnoDB存儲(chǔ)引擎中頁的大小為16KB，一般表的主鍵類型為 INT（占用4個(gè)字節(jié)）或 BIGINT（占用8個(gè)字節(jié)）对竣，指針類型也一般為4或8個(gè)字節(jié)庇楞，也就是說一個(gè)頁（B+Tree中的一個(gè)節(jié)點(diǎn)）中大概存儲(chǔ)16KB/(8B+8B)=1K個(gè)鍵值。（因?yàn)槭枪乐捣裎常瑸榉奖阌?jì)算吕晌，這里的K取值為〖10〗^{3）。也就是說一個(gè)深度為3的B+Tree索引可以維護(hù)10}3 * 10^3 * 10^3 = 10億 條記錄临燃。

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

數(shù)據(jù)庫中的B+Tree索引可以分為聚集索引（clustered index）和 輔助索引（secondary index）。上面的B+Tree示例圖在數(shù)據(jù)庫中的實(shí)現(xiàn)即為聚集索引蹬跃，聚集索引的B+Tree中的葉子節(jié)點(diǎn)存放的是整張表的行記錄數(shù)據(jù)匙瘪。

輔助索引與聚集索引的區(qū)別在于輔助索引的葉子節(jié)點(diǎn)并不包含行記錄的全部數(shù)據(jù)，而是存儲(chǔ)相應(yīng)行數(shù)據(jù)的聚集索引鍵蝶缀，即主鍵丹喻。當(dāng)通過輔助索引來查詢數(shù)據(jù)時(shí)，InnoDB存儲(chǔ)引擎會(huì)遍歷輔助索引找到主鍵翁都，然后再通過主鍵在聚集索引中找到完整的行記錄數(shù)據(jù)碍论。

查看mysql文件頁大小（16K）：SHOW GLOBAL STATUS like 'Innodb_page_size’;

MySQL存儲(chǔ)引擎

什么是存儲(chǔ)引擎柄慰？

數(shù)據(jù)庫存儲(chǔ)引擎是數(shù)據(jù)庫底層軟件組件鳍悠，數(shù)據(jù)庫管理系統(tǒng)使用數(shù)據(jù)引擎進(jìn)行創(chuàng)建、查詢先煎、更新和刪除數(shù)據(jù)操作贼涩。不同的存儲(chǔ)引擎提供不同的存儲(chǔ)機(jī)制、索引技巧薯蝎、鎖定水平等功能遥倦，使用不同的存儲(chǔ)引擎還可以獲得特定的功能。

現(xiàn)在許多數(shù)據(jù)庫管理系統(tǒng)都支持多種不同的存儲(chǔ)引擎占锯。MySQL 的核心就是存儲(chǔ)引擎袒哥。

• InnoDB 事務(wù)型數(shù)據(jù)庫的首選引擎，支持事務(wù)安全表（ACID）消略，支持行鎖定和外鍵堡称。MySQL 5.5.5 之后，InnoDB 作為默認(rèn)存儲(chǔ)引擎艺演。
• MyISAM 是基于 ISAM 的存儲(chǔ)引擎却紧，并對(duì)其進(jìn)行擴(kuò)展，是在 Web胎撤、數(shù)據(jù)倉儲(chǔ)和其他應(yīng)用環(huán)境下最常使用的存儲(chǔ)引擎之一晓殊。MyISAM 擁有較高的插入、查詢速度伤提，但不支持事務(wù)巫俺。
• MEMORY 存儲(chǔ)引擎將表中的數(shù)據(jù)存儲(chǔ)到內(nèi)存中，為查詢和引用其他數(shù)據(jù)提供快速訪問肿男。

MySQL 5.7 支持的存儲(chǔ)引擎

MySQL 支持多種類型的數(shù)據(jù)庫引擎介汹，可分別根據(jù)各個(gè)引擎的功能和特性為不同的數(shù)據(jù)庫處理任務(wù)提供各自不同的適應(yīng)性和靈活性却嗡。在 MySQL 中，可以利用 SHOW ENGINES 語句來顯示可用的數(shù)據(jù)庫引擎和默認(rèn)引擎嘹承。

MySQL 提供了多個(gè)不同的存儲(chǔ)引擎窗价，包括處理事務(wù)安全表的引擎和處理非事務(wù)安全表的引擎。在 MySQL 中赶撰，不需要在整個(gè)服務(wù)器中使用同一種存儲(chǔ)引擎舌镶，針對(duì)具體的要求，可以對(duì)每一個(gè)表使用不同的存儲(chǔ)引擎豪娜。

MySQL 5.7 支持的存儲(chǔ)引擎有 InnoDB餐胀、MyISAM、Memory瘤载、Merge否灾、Archive、Federated鸣奔、CSV墨技、BLACKHOLE 等。

可以使用SHOW ENGINES語句查看系統(tǒng)所支持的引擎類型挎狸，結(jié)果如圖所示扣汪。

如何選擇 MySQL 存儲(chǔ)引擎

不同的存儲(chǔ)引擎都有各自的特點(diǎn)，以適應(yīng)不同的需求锨匆，如表所示崭别。為了做出選擇，首先要考慮每一個(gè)存儲(chǔ)引擎提供了哪些不同的功能恐锣。

可以根據(jù)以下的原則來選擇 MySQL 存儲(chǔ)引擎：

• 如果要提供提交茅主、回滾和恢復(fù)的事務(wù)安全（ACID 兼容）能力，并要求實(shí)現(xiàn)并發(fā)控制土榴，InnoDB 是一個(gè)很好的選擇诀姚。
• 如果數(shù)據(jù)表主要用來插入和查詢記錄，則 MyISAM 引擎提供較高的處理效率玷禽。
• 如果只是臨時(shí)存放數(shù)據(jù)赫段，數(shù)據(jù)量不大，并且不需要較高的數(shù)據(jù)安全性矢赁，可以選擇將數(shù)據(jù)保存在內(nèi)存的 MEMORY 引擎中瑞佩，MySQL 中使用該引擎作為臨時(shí)表，存放查詢的中間結(jié)果坯台。
• 如果只有 INSERT 和 SELECT 操作，可以選擇Archive 引擎瘫寝，Archive 存儲(chǔ)引擎支持高并發(fā)的插入操作蜒蕾，但是本身并不是事務(wù)安全的稠炬。Archive 存儲(chǔ)引擎非常適合存儲(chǔ)歸檔數(shù)據(jù)，如記錄日志信息可以使用 Archive 引擎咪啡。

提示：使用哪一種引擎要根據(jù)需要靈活選擇首启，一個(gè)數(shù)據(jù)庫中多個(gè)表可以使用不同的引擎以滿足各種性能和實(shí)際需求。使用合適的存儲(chǔ)引擎將會(huì)提高整個(gè)數(shù)據(jù)庫的性能撤摸。

使用下面的語句可以修改數(shù)據(jù)庫臨時(shí)的默認(rèn)存儲(chǔ)引擎
SET default_storage_engine=< 存儲(chǔ)引擎名 >
注意：將 MySQL 數(shù)據(jù)庫的臨時(shí)默認(rèn)存儲(chǔ)引擎修改為 其他的存儲(chǔ)引擎時(shí) 毅桃，但是當(dāng)再次重啟客戶端時(shí)，默認(rèn)存儲(chǔ)引擎仍然是 InnoDB准夷。

MyISAM存儲(chǔ)引擎

數(shù)據(jù)存儲(chǔ)形式

MyISAM采用的是索引與數(shù)據(jù)分離的形式钥飞，將數(shù)據(jù)保存在三個(gè)文件中.frm 、.MYD衫嵌、.MYI读宙。

• .frm : 存儲(chǔ)表結(jié)構(gòu)
• .MYD ： 存儲(chǔ)表數(shù)據(jù)
• .MYI ：存儲(chǔ)表索引

鎖的粒度

MyISAM不支持行鎖，所以讀取時(shí)對(duì)表加上共享鎖楔绞，在寫入是對(duì)表加上排他鎖结闸。由于是對(duì)整張表加鎖，相比InnoDB酒朵，在并發(fā)寫入時(shí)效率很低桦锄。

事務(wù)

MyISAM不支持事務(wù)。

數(shù)據(jù)的存儲(chǔ)特點(diǎn)

MyISAM是基于非聚簇索引進(jìn)行存儲(chǔ)的蔫耽。

索引實(shí)現(xiàn)

MyISAM索引文件和數(shù)據(jù)文件是分離的(非聚集)

其他

MyISAM提供了大量的特性结耀，包括全文索引，壓縮针肥，空間函數(shù)饼记，延遲更新索引鍵等。

• 進(jìn)行壓縮后的表是不能進(jìn)行修改的慰枕，但是壓縮表可以極大減少磁盤占用空間具则，因此也可以減少磁盤IO，從而提供查詢性能具帮。

• 全文索引博肋，是一種基于分詞創(chuàng)建的索引，可以支持復(fù)雜的查詢蜂厅。

• 延遲更新索引鍵匪凡，不會(huì)將更新的索引數(shù)據(jù)立即寫入到磁盤，而是會(huì)寫到內(nèi)存中的緩沖區(qū)中掘猿，只有在清除緩沖區(qū)時(shí)候才會(huì)將對(duì)應(yīng)的索引寫入磁盤病游，這種方式大大提升了寫入性能。

InnoDB存儲(chǔ)引擎

數(shù)據(jù)存儲(chǔ)形式

使用InnoDB時(shí)，會(huì)將數(shù)據(jù)表分為.frm 和 .ibd 兩個(gè)文件進(jìn)行存儲(chǔ)衬衬。

• .frm : 存儲(chǔ)表結(jié)構(gòu)
• .ibd : 存儲(chǔ)表數(shù)據(jù)和索引

innodb的所有數(shù)據(jù)文件（后綴為ibd的文件）买猖，他的大小始終都是16384（16k）的整數(shù)倍。

鎖的粒度

InnoDB采用MVCC(多版本并發(fā)控制)來支持高并發(fā)滋尉，InnoDB實(shí)現(xiàn)了四個(gè)隔離級(jí)別玉控，默認(rèn)級(jí)別是REPETABLE READ，并通過間隙鎖策略防止幻讀的出現(xiàn)狮惜。它的鎖粒度是行鎖高诺。【MVCC在稍后會(huì)進(jìn)行介紹】

事務(wù)

InnoDB是典型的事務(wù)型存儲(chǔ)引擎碾篡，并且通過一些機(jī)制和工具虱而，支持真正的熱備份。

數(shù)據(jù)的存儲(chǔ)特點(diǎn)

InnoDB表是基于聚簇索引建立的耽梅，聚簇索引對(duì)主鍵的查詢有很高的性能薛窥，不過他的二級(jí)索引（非主鍵索引）必須包含主鍵列，索引其他的索引會(huì)很大眼姐。

索引實(shí)現(xiàn)

InnoDB索引實(shí)現(xiàn)(聚簇)

• 表數(shù)據(jù)文件本身就是按B+Tree組織的一個(gè)索引結(jié)構(gòu)文件
• 聚簇索引-葉節(jié)點(diǎn)包含了完整的數(shù)據(jù)記錄
• 為什么InnoDB表必須有主鍵诅迷，并且推薦使用整型的自增主鍵？
• 為什么非主鍵索引結(jié)構(gòu)葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵值众旗？(一致性和節(jié)省存儲(chǔ)空間)

聯(lián)合索引

聯(lián)合索引的底層存儲(chǔ)結(jié)構(gòu)長什么樣罢杉？

比較相等時(shí)，先比較第一列的值贡歧，如果相等滩租，再繼續(xù)比較第二列，以此類推利朵。

最左前綴原理

使用聯(lián)合索引時(shí)律想，索引列的定義順序?qū)?huì)影響到最終查詢時(shí)索引的使用情況。例如聯(lián)合索引（a,b,c）绍弟，mysql會(huì)從最左邊的列優(yōu)先匹配技即，如果最左邊的帶頭大哥沒有使用到，在未使用覆蓋索引的情況下樟遣，就只能全表掃描而叼。
聯(lián)合底層數(shù)據(jù)結(jié)構(gòu)思考，mysql會(huì)優(yōu)先以聯(lián)合索引第一列匹配豹悬，此后才會(huì)匹配下一列葵陵，如果不指定第一列匹配的值，也就無法得知下一步查詢哪個(gè)節(jié)點(diǎn)瞻佛。
另外還有一種情況脱篙，如果遇到 > < between等這樣的范圍查詢，那B+樹中也就無法對(duì)下一列進(jìn)行等值匹配了。

淺談MVCC

MySQL大多數(shù)事務(wù)型存儲(chǔ)引擎實(shí)現(xiàn)的都不是簡單的行鎖涡尘∪坛冢基于提升并發(fā)性能的考慮，他們一般都同時(shí)實(shí)現(xiàn)了多版本并發(fā)控制(MVCC)考抄。

可以認(rèn)為MVCC是行級(jí)鎖的一個(gè)變種，它能在大多數(shù)情況下避免加鎖操作蔗彤，因此開銷更低川梅。無論怎樣實(shí)現(xiàn)，它們大都實(shí)現(xiàn)了非阻塞的讀操作然遏，寫操作也只鎖定制定的行贫途。

MVCC是通過保存數(shù)據(jù)在某一個(gè)時(shí)間點(diǎn)的快照來實(shí)現(xiàn)的，也就是說無論事務(wù)執(zhí)行多久待侵，每個(gè)事務(wù)看到的數(shù)據(jù)都是一致的丢早。InnoDB的MVCC，是通過在每行記錄后面保存兩個(gè)隱藏的列來實(shí)現(xiàn)秧倾，這兩個(gè)列一個(gè)保存了行的創(chuàng)建時(shí)間怨酝，一個(gè)保存了行的過期時(shí)間(或刪除時(shí)間)，當(dāng)然那先，并非存儲(chǔ)的是時(shí)間农猬，而是系統(tǒng)版本號(hào)。每開啟一個(gè)事務(wù)售淡，版本號(hào)都會(huì)遞增斤葱，事務(wù)開始時(shí)刻的系統(tǒng)版本號(hào)會(huì)作為事務(wù)的版本號(hào)。

以InnoDB存儲(chǔ)引擎的的REPEATABLE READ隔離級(jí)別來說：

SELECT

• 只查詢創(chuàng)建時(shí)間版本號(hào)小于當(dāng)前事務(wù)版本號(hào)的數(shù)據(jù)行（保證事務(wù)讀取的行要么在事務(wù)開始之前就存在揖闸，要么是事務(wù)本身插入的行）
• 行的刪除版本號(hào)要么未定義揍堕，要么大于當(dāng)前事務(wù)版本號(hào)，這樣可以確保事務(wù)讀取到的行汤纸，在開始事務(wù)之前未被刪除

只有復(fù)合上訴兩個(gè)條件的記錄才會(huì)作為結(jié)果返回

INSERT

為插入的數(shù)據(jù)保存當(dāng)前系統(tǒng)版本號(hào)作為行版本號(hào)

DELETE

保存當(dāng)前系統(tǒng)版本號(hào)作為刪除行版本號(hào)

UPDATE

插入一行數(shù)據(jù)衩茸，并將當(dāng)前系統(tǒng)版本號(hào)賦予行版本號(hào)；同時(shí)保存當(dāng)前系統(tǒng)版本號(hào)到原來的行作為刪除版本號(hào)蹲嚣。

注：MVCC只在REPEATABLE和READ COMMITTED兩個(gè)隔離級(jí)別下才能正常工作递瑰。

問題總結(jié)

InnoDB一棵B+樹可以存放多少行數(shù)據(jù)？

這個(gè)問題的簡單回答是：約2千萬隙畜。為什么是這么多呢抖部？因?yàn)檫@是可以算出來的，要搞清楚這個(gè)問題议惰，我們先從InnoDB索引數(shù)據(jù)結(jié)構(gòu)慎颗、數(shù)據(jù)組織方式說起。

我們都知道計(jì)算機(jī)在存儲(chǔ)數(shù)據(jù)的時(shí)候，有最小存儲(chǔ)單元俯萎，這就好比我們今天進(jìn)行現(xiàn)金的流通最小單位是一毛傲宜。在計(jì)算機(jī)中磁盤存儲(chǔ)數(shù)據(jù)最小單元是扇區(qū)，一個(gè)扇區(qū)的大小是512字節(jié)夫啊，而文件系統(tǒng)（例如XFS/EXT4）他的最小單元是塊函卒，一個(gè)塊的大小是4k，而對(duì)于我們的InnoDB存儲(chǔ)引擎也有自己的最小儲(chǔ)存單元——頁（Page）撇眯，一個(gè)頁的大小是16K报嵌。

下面幾張圖可以幫你理解最小存儲(chǔ)單元：

文件系統(tǒng)中一個(gè)文件大小只有1個(gè)字節(jié)，但不得不占磁盤上4KB的空間熊榛。

innodb的所有數(shù)據(jù)文件（后綴為ibd的文件）锚国，他的大小始終都是16384（16k）的整數(shù)倍。

在MySQL中我們的InnoDB頁的大小默認(rèn)是16k煎楣，當(dāng)然也可以通過參數(shù)設(shè)置：

mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

數(shù)據(jù)表中的數(shù)據(jù)都是存儲(chǔ)在頁中的豺总，所以一個(gè)頁中能存儲(chǔ)多少行數(shù)據(jù)呢？假設(shè)一行數(shù)據(jù)的大小是1k转质，那么一個(gè)頁可以存放16行這樣的數(shù)據(jù)园欣。

如果數(shù)據(jù)庫只按這樣的方式存儲(chǔ)，那么如何查找數(shù)據(jù)就成為一個(gè)問題休蟹，因?yàn)槲覀儾恢酪檎业臄?shù)據(jù)存在哪個(gè)頁中沸枯，也不可能把所有的頁遍歷一遍，那樣太慢了赂弓。所以人們想了一個(gè)辦法绑榴，用B+樹的方式組織這些數(shù)據(jù)。如圖所示：

我們先將數(shù)據(jù)記錄按主鍵進(jìn)行排序盈魁，分別存放在不同的頁中（為了便于理解我們這里一個(gè)頁中只存放3條記錄翔怎，實(shí)際情況可以存放很多），除了存放數(shù)據(jù)的頁以外杨耙，還有存放鍵值+指針的頁赤套，如圖中page number=3的頁，該頁存放鍵值和指向數(shù)據(jù)頁的指針珊膜，這樣的頁由N個(gè)鍵值+指針組成容握。當(dāng)然它也是排好序的。這樣的數(shù)據(jù)組織形式车柠，我們稱為索引組織表√奘希現(xiàn)在來看下塑猖，要查找一條數(shù)據(jù)，怎么查谈跛？

如select * from user where id=5;

這里id是主鍵,我們通過這棵B+樹來查找羊苟，首先找到根頁，你怎么知道user表的根頁在哪呢感憾？其實(shí)每張表的根頁位置在表空間文件中是固定的蜡励，即page number=3的頁（這點(diǎn)我們下文還會(huì)進(jìn)一步證明），找到根頁后通過二分查找法阻桅，定位到id=5的數(shù)據(jù)應(yīng)該在指針P5指向的頁中巍虫，那么進(jìn)一步去page number=5的頁中查找，同樣通過二分查詢法即可找到id=5的記錄.

現(xiàn)在我們清楚了InnoDB中主鍵索引B+樹是如何組織數(shù)據(jù)鳍刷、查詢數(shù)據(jù)的，總結(jié)一下：

1. InnoDB存儲(chǔ)引擎的最小存儲(chǔ)單元是頁俯抖，頁可以用于存放數(shù)據(jù)也可以用于存放鍵值+指針输瓜，在B+樹中葉子節(jié)點(diǎn)存放數(shù)據(jù)，非葉子節(jié)點(diǎn)存放鍵值+指針芬萍。
2. 索引組織表通過非葉子節(jié)點(diǎn)的二分查找法以及指針確定數(shù)據(jù)在哪個(gè)頁中尤揣，進(jìn)而在去數(shù)據(jù)頁中查找到需要的數(shù)據(jù)；

那么回到我們開始的問題柬祠，通常一棵B+樹可以存放多少行數(shù)據(jù)北戏？

這里我們先假設(shè)B+樹高為2，即存在一個(gè)根節(jié)點(diǎn)和若干個(gè)葉子節(jié)點(diǎn)漫蛔，那么這棵B+樹的存放總記錄數(shù)為：根節(jié)點(diǎn)指針數(shù)單個(gè)葉子節(jié)點(diǎn)記錄行數(shù)*嗜愈。

上文我們已經(jīng)說明單個(gè)葉子節(jié)點(diǎn)（頁）中的記錄數(shù)=16K/1K=16。（這里假設(shè)一行記錄的數(shù)據(jù)大小為1k莽龟，實(shí)際上現(xiàn)在很多互聯(lián)網(wǎng)業(yè)務(wù)數(shù)據(jù)記錄大小通常就是1K左右）蠕嫁。

那么現(xiàn)在我們需要計(jì)算出非葉子節(jié)點(diǎn)能存放多少指針，其實(shí)這也很好算毯盈，我們假設(shè)主鍵ID為bigint類型剃毒，長度為8字節(jié)，而指針大小在InnoDB源碼中設(shè)置為6字節(jié)搂赋，這樣一共14字節(jié)赘阀，我們一個(gè)頁中能存放多少這樣的單元，其實(shí)就代表有多少指針脑奠，即16384/14=1170基公。那么可以算出一棵高度為2的B+樹，能存放1170*16=18720條這樣的數(shù)據(jù)記錄捺信。

根據(jù)同樣的原理我們可以算出一個(gè)高度為3的B+樹可以存放：1170*1170*16=21902400條這樣的記錄酌媒。所以在InnoDB中B+樹高度一般為1-3層欠痴，它就能滿足千萬級(jí)的數(shù)據(jù)存儲(chǔ)。在查找數(shù)據(jù)時(shí)一次頁的查找代表一次IO秒咨，所以通過主鍵索引查詢通常只需要1-3次IO操作即可查找到數(shù)據(jù)喇辽。

怎么得到InnoDB主鍵索引B+樹的高度？

上面我們通過推斷得出B+樹的高度通常是1-3雨席，下面我們從另外一個(gè)側(cè)面證明這個(gè)結(jié)論菩咨。在InnoDB的表空間文件中，約定page number為3的代表主鍵索引的根頁陡厘，而在根頁偏移量為64的地方存放了該B+樹的page level抽米。如果page level為1，樹高為2糙置，page level為2云茸，則樹高為3。即B+樹的高度=page level+1谤饭；下面我們將從實(shí)際環(huán)境中嘗試找到這個(gè)page level标捺。

在實(shí)際操作之前，你可以通過InnoDB元數(shù)據(jù)表確認(rèn)主鍵索引根頁的page number為3揉抵，你也可以從《InnoDB存儲(chǔ)引擎》這本書中得到確認(rèn)亡容。

SELECT
b.name, a.name, index_id, type, a.space, a.PAGE_NO
FROM
information_schema.INNODB_SYS_INDEXES a,
information_schema.INNODB_SYS_TABLES b
WHERE
a.table_id = b.table_id AND a.space <> 0;

執(zhí)行結(jié)果：

可以看出數(shù)據(jù)庫approval_db下的sys_config表、sys_config表主鍵索引根頁的page number均為3冤今，而其他的二級(jí)索引page number為4闺兢。

聚簇索引和非聚簇索引的區(qū)別

• 聚簇索引：將數(shù)據(jù)存儲(chǔ)與索引放到了一塊，索引結(jié)構(gòu)的葉子節(jié)點(diǎn)保存了行數(shù)據(jù)
• 非聚簇索引：將數(shù)據(jù)與索引分開存儲(chǔ)戏罢，索引結(jié)構(gòu)的葉子節(jié)點(diǎn)指向了數(shù)據(jù)對(duì)應(yīng)的位置

為什么InnoDB表必須有主鍵屋谭，并且推薦使用整型的自增主鍵？

1. 如果設(shè)置了主鍵帖汞，那么InnoDB會(huì)選擇主鍵作為聚集索引戴而，如果沒有顯式定義主鍵，則InnoDB會(huì)選擇第一個(gè)不包含有NULL值的唯一索引作為主鍵索引翩蘸、如果也沒有這樣的唯一索引所意，則InnoDB會(huì)選擇內(nèi)置6字節(jié)長的ROWID作為隱含的聚集索引(ROWID隨著行記錄的寫入而主鍵遞增)。
2. 如果表使用自增主鍵

那么每次插入新的記錄催首，記錄就會(huì)順序添加到當(dāng)前索引節(jié)點(diǎn)的后續(xù)位置扶踊，主鍵的順序按照數(shù)據(jù)記錄的插入順序排列，自動(dòng)有序郎任。當(dāng)一頁寫滿秧耗，就會(huì)自動(dòng)開辟一個(gè)新的頁

3. 如果使用非自增主鍵（如果身份證號(hào)或?qū)W號(hào)等）

由于每次插入主鍵的值近似于隨機(jī)，因此每次新紀(jì)錄都要被插到現(xiàn)有索引頁得中間某個(gè)位置舶治，此時(shí)MySQL不得不為了將新記錄插到合適位置而移動(dòng)數(shù)據(jù)分井，甚至目標(biāo)頁面可能已經(jīng)被回寫到磁盤上而從緩存中清掉车猬，此時(shí)又要從磁盤上讀回來，這增加了很多開銷尺锚，同時(shí)頻繁的移動(dòng)珠闰、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結(jié)構(gòu)瘫辩，后續(xù)不得不通過OPTIMIZE TABLE來重建表并優(yōu)化填充頁面伏嗜。

MySQL為什么用整型自增作為索引比較好。而UUID作為索引效率比較低伐厌？

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

• 索引存儲(chǔ)在磁盤艰额，而且樹的每個(gè)節(jié)點(diǎn)分配的空間有大小。整型占空間比較小椒涯，這樣可以存放多個(gè)鍵值柄沮。反之然后UUID占空間比較大。
• 整型比較方便废岂，UUID比較需要先轉(zhuǎn)成ASCII在進(jìn)行比較祖搓。

為什么非主鍵索引結(jié)構(gòu)葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵值？(一致性和節(jié)省存儲(chǔ)空間)

1. 減少了出現(xiàn)行移動(dòng)或者數(shù)據(jù)頁分裂時(shí)二級(jí)索引的維護(hù)工作（當(dāng)數(shù)據(jù)需要更新的時(shí)候湖苞，二級(jí)索引不需要修改拯欧，只需要修改聚簇索引，一個(gè)表只能有一個(gè)聚簇索引财骨，其他的都是二級(jí)索引镐作，這樣只需要修改聚簇索引就可以了藏姐，不需要重新構(gòu)建二級(jí)索引）；
2. 聚簇索引也稱為主鍵索引该贾，其索引樹的葉子節(jié)點(diǎn)中存的是整行數(shù)據(jù)羔杨，表中行的物理順序與鍵值的邏輯（索引）順序相同。一個(gè)表只能包含一個(gè)聚集索引靶庙。因?yàn)樗饕夸洠┲荒馨凑找环N方法進(jìn)行排序问畅；
3. 非聚簇索引（普通索引）的葉子節(jié)點(diǎn)內(nèi)容是主鍵的值。在 InnoDB 里六荒，非主鍵索引也被稱為二級(jí)索引（secondary index）护姆。

部分圖片來源于網(wǎng)絡(luò)，版權(quán)歸原作者掏击，侵刪卵皂。