前言

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

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

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

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

另外，阿里巴巴《Java 開發(fā)手冊(cè)》提出單表行數(shù)超過(guò) 500 萬(wàn)行或者單表容量超過(guò) 2GB，才推薦進(jìn)行分庫(kù)分表。對(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í)就分庫(kù)分表。

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

外存儲(chǔ)器-磁盤

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

• 內(nèi)存:讀寫速度非?？欤侨萘亢苄?，而且造價(jià)非常貴，在不通電的情況下會(huì)數(shù)據(jù)會(huì)丟失，不能長(zhǎng)期存儲(chǔ)數(shù)據(jù)；
• 外存：磁盤是相對(duì)常見(jiàn)的外存儲(chǔ)設(shè)備，它是以存取時(shí)間變化不大為特征的?？梢灾苯哟嫒∪魏巫址M，且容量大、速度較其它外存設(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)到所需要的柱面上，這一過(guò)程被稱為定位或查找。
2. 如上圖6盤組示意圖中，所有磁頭都定位到了10個(gè)盤面的10條磁道上(磁頭都是雙向的)，這時(shí)根據(jù)盤面號(hào)來(lái)確定指定盤面上的磁道。
3. 盤面確定以后，盤片開始旋轉(zhuǎn)，將指定塊號(hào)的磁道段移動(dòng)至磁頭下。

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

訪問(wèn)某一具體信息，由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ù)通過(guò)系統(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ù)都能被一次性全部讀取出來(lái)。而磁盤IO代價(jià)主要花費(fèi)在查找時(shí)間Ts上，因此我們應(yīng)該盡量將相關(guān)信息存放在同一盤塊，同一磁道中，或者至少放在同一柱面或相鄰柱面上，以求在讀/寫信息時(shí)盡量減少磁頭來(lái)回移動(dòng)的次數(shù)，避免過(guò)多的查找時(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)，主要包含以下幾類，我們來(lái)對(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ì)值不超過(guò)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)行訪問(wèn)的數(shù)據(jù)結(jié)構(gòu)。也就是說(shuō)，它通過(guò)把關(guān)鍵碼值映射到表中一個(gè)位置來(lái)訪問(wèn)記錄，以加快查找的速度。這個(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的過(guò)程：

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)存?！敬疟PI/O操作第2次】
4. 比較關(guān)鍵字29在區(qū)間（26,30），找到磁盤塊3的指針P2。
5. 根據(jù)P2指針找到磁盤塊8，讀入內(nèi)存?！敬疟PI/O操作第3次】
6. 在磁盤塊8中的關(guān)鍵字列表中找到關(guān)鍵字29。

分析上面過(guò)程，發(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ū)間訪問(wè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)開始，進(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ù)。

查看mysql文件頁(yè)大?。?6K）：SHOW GLOBAL STATUS like 'Innodb_page_size’;

MySQL存儲(chǔ)引擎

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

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

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

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

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

MySQL 支持多種類型的數(shù)據(jù)庫(kù)引擎，可分別根據(jù)各個(gè)引擎的功能和特性為不同的數(shù)據(jù)庫(kù)處理任務(wù)提供各自不同的適應(yīng)性和靈活性。在 MySQL 中，可以利用 SHOW ENGINES 語(yǔ)句來(lái)顯示可用的數(shù)據(jù)庫(kù)引擎和默認(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語(yǔ)句查看系統(tǒng)所支持的引擎類型，結(jié)果如圖所示。

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

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

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

• 如果要提供提交、回滾和恢復(fù)的事務(wù)安全（ACID 兼容）能力，并要求實(shí)現(xiàn)并發(fā)控制，InnoDB 是一個(gè)很好的選擇。
• 如果數(shù)據(jù)表主要用來(lái)插入和查詢記錄，則 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ù)庫(kù)中多個(gè)表可以使用不同的引擎以滿足各種性能和實(shí)際需求。使用合適的存儲(chǔ)引擎將會(huì)提高整個(gè)數(shù)據(jù)庫(kù)的性能。

使用下面的語(yǔ)句可以修改數(shù)據(jù)庫(kù)臨時(shí)的默認(rèn)存儲(chǔ)引擎
SET default_storage_engine=< 存儲(chǔ)引擎名 >
注意：將 MySQL 數(shù)據(jù)庫(kù)的臨時(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ā)控制)來(lái)支持高并發(fā)，InnoDB實(shí)現(xiàn)了四個(gè)隔離級(jí)別，默認(rèn)級(jí)別是REPETABLE READ，并通過(guò)間隙鎖策略防止幻讀的出現(xiàn)。它的鎖粒度是行鎖?！綧VCC在稍后會(huì)進(jìn)行介紹】

事務(wù)

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

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

InnoDB表是基于聚簇索引建立的，聚簇索引對(duì)主鍵的查詢有很高的性能，不過(guò)他的二級(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)長(zhǎng)什么樣？

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

最左前綴原理

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

淺談MVCC

MySQL大多數(shù)事務(wù)型存儲(chǔ)引擎實(shí)現(xiàn)的都不是簡(jiǎn)單的行鎖?；谔嵘l(fā)性能的考慮，他們一般都同時(shí)實(shí)現(xiàn)了多版本并發(fā)控制(MVCC)。

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

MVCC是通過(guò)保存數(shù)據(jù)在某一個(gè)時(shí)間點(diǎn)的快照來(lái)實(shí)現(xiàn)的，也就是說(shuō)無(wú)論事務(wù)執(zhí)行多久，每個(gè)事務(wù)看到的數(shù)據(jù)都是一致的。InnoDB的MVCC，是通過(guò)在每行記錄后面保存兩個(gè)隱藏的列來(lái)實(shí)現(xiàn)，這兩個(gè)列一個(gè)保存了行的創(chuàng)建時(shí)間，一個(gè)保存了行的過(guò)期時(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í)別來(lái)說(shuō)：

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)到原來(lái)的行作為刪除版本號(hào)。

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

問(wèn)題總結(jié)

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

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

我們都知道計(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ǔ)存單元——頁(yè)（Page），一個(gè)頁(yè)的大小是16K。

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

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

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

在MySQL中我們的InnoDB頁(yè)的大小默認(rèn)是16k，當(dāng)然也可以通過(guò)參數(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ǔ)在頁(yè)中的，所以一個(gè)頁(yè)中能存儲(chǔ)多少行數(shù)據(jù)呢？假設(shè)一行數(shù)據(jù)的大小是1k，那么一個(gè)頁(yè)可以存放16行這樣的數(shù)據(jù)。

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

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

如select * from user where id=5;

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

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

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

那么回到我們開始的問(wèn)題，通常一棵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)說(shuō)明單個(gè)葉子節(jié)點(diǎn)（頁(yè)）中的記錄數(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類型，長(zhǎng)度為8字節(jié)，而指針大小在InnoDB源碼中設(shè)置為6字節(jié)，這樣一共14字節(jié)，我們一個(gè)頁(yè)中能存放多少這樣的單元，其實(shí)就代表有多少指針，即16384/14=1170。那么可以算出一棵高度為2的B+樹，能存放1170*16=18720條這樣的數(shù)據(jù)記錄。

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

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

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

在實(shí)際操作之前，你可以通過(guò)InnoDB元數(shù)據(jù)表確認(rèn)主鍵索引根頁(yè)的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ù)庫(kù)approval_db下的sys_config表、sys_config表主鍵索引根頁(yè)的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ì)選擇主鍵作為聚集索引，如果沒(méi)有顯式定義主鍵，則InnoDB會(huì)選擇第一個(gè)不包含有NULL值的唯一索引作為主鍵索引、如果也沒(méi)有這樣的唯一索引，則InnoDB會(huì)選擇內(nèi)置6字節(jié)長(zhǎng)的ROWID作為隱含的聚集索引(ROWID隨著行記錄的寫入而主鍵遞增)。
2. 如果表使用自增主鍵

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

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

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

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

聚簇索引的數(shù)據(jù)的物理存放順序與索引順序是一致的，即：只要索引是相鄰的，那么對(duì)應(yīng)的數(shù)據(jù)一定也是相鄰地存放在磁盤上的。如果主鍵不是自增id，那么可以想象，它會(huì)干些什么，不斷地調(diào)整數(shù)據(jù)的物理地址、分頁(yè)，當(dāng)然也有其他一些措施來(lái)減少這些操作，但卻無(wú)法徹底避免。但，如果是自增的，那就簡(jiǎn)單了，它只需要一頁(yè)一頁(yè)地寫，索引結(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ù)頁(yè)分裂時(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）。

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