1.什么是索引警儒？

索引：加速查詢的數(shù)據(jù)結(jié)構(gòu)冀膝。

2.索引常見數(shù)據(jù)結(jié)構(gòu)：

#1.順序查找: 最基本的查詢算法-復雜度O（n）,大數(shù)據(jù)量此算法效率糟糕。

#2.二叉樹查找（binary tree search）: O(log2n)

圖1

左邊是數(shù)據(jù)表积暖，一共有兩列七條記錄风喇，最左邊的是數(shù)據(jù)記錄的物理地址（注意邏輯上相鄰的記錄在磁盤上也并不是一定物理相鄰的）。為了加快Col2的查找樊破，可以維護一個右邊所示的二叉查找樹愉棱，每個節(jié)點分別包含索引鍵值和一個指向?qū)獢?shù)據(jù)記錄物理地址的指針，這樣就可以運用二叉查找在O(log2n)O(log2n)的復雜度內(nèi)獲取到相應數(shù)據(jù)哲戚。

#3.hash索引 無法滿足范圍查找奔滑。

#4.二叉樹、紅黑樹 [復雜度O(h)]導致樹高度非常高(平衡二叉樹一個節(jié)點只能有左子樹和右子樹),邏輯上很近的節(jié)點（父子）物理上可能很遠顺少，無法利用局部性朋其，IO次數(shù)多查找慢,效率低。todo 邏輯上相鄰節(jié)點沒法直接通過順序指針關聯(lián)脆炎，可能需要迭代回到上層節(jié)點重復向下遍歷找到對應節(jié)點梅猿，效率低

B-Tree 和 B+Tree數(shù)據(jù)結(jié)構(gòu)：

#4.B-Tree:

結(jié)構(gòu)：B-TREE 每個節(jié)點都是一個二元數(shù)組: [key, data]，所有節(jié)點都可以存儲數(shù)據(jù)秒裕。key為索引key,data為除key之外的數(shù)據(jù)袱蚓。結(jié)構(gòu)如下：

圖2

檢索原理：首先從根節(jié)點進行二分查找，如果找到則返回對應節(jié)點的data几蜻，否則對相應區(qū)間的指針指向的節(jié)點遞歸進行查找喇潘，直到找到節(jié)點或未找到節(jié)點返回null指針。

缺點：1.插入刪除新的數(shù)據(jù)記錄會破壞B-Tree的性質(zhì)梭稚，因此在插入刪除時颖低，需要對樹進行一個分裂、合并弧烤、轉(zhuǎn)移等操作以保持B-Tree性質(zhì)忱屑。造成IO操作頻繁。2.區(qū)間查找可能需要返回上層節(jié)點重復遍歷，IO操作繁瑣莺戒。

#5.B+Tree: B-Tree的變種

與B-Tree相比粱栖，B+Tree有以下不同點：非葉子節(jié)點不存儲data，只存儲索引key脏毯；只有葉子節(jié)點才存儲data。結(jié)構(gòu)如下圖：

圖3

Mysql中B+Tree：在經(jīng)典B+Tree的基礎上進行了優(yōu)化幔崖，增加了順序訪問指針食店。在B+Tree的每個葉子節(jié)點增加一個指向相鄰葉子節(jié)點的指針，就形成了帶有順序訪問指針的B+Tree赏寇。這樣就提高了區(qū)間訪問性能：如果要查詢key為從18到49的所有數(shù)據(jù)記錄吉嫩，當找到18后，只需順著節(jié)點和指針順序遍歷就可以一次性訪問到所有數(shù)據(jù)節(jié)點嗅定，極大提到了區(qū)間查詢效率(無需返回上層父節(jié)點重復遍歷查找減少IO操作)自娩。

結(jié)構(gòu)如下：

圖4

3.為什么Mysql選擇B+TREE索引? B+TREE索引有什么好處?

? 索引本身也很大，不可能全部存儲在內(nèi)存中渠退，因此索引往往以索引文件的形式存儲的磁盤上忙迁。這樣的話，索引查找過程中就要產(chǎn)生磁盤I/O消耗碎乃，相對于內(nèi)存存取姊扔，I/O存取的消耗要高幾個數(shù)量級，所以索引的結(jié)構(gòu)組織要盡量減少查找過程中磁盤I/O的存取次數(shù)梅誓，提升索引效率恰梢。

磁盤存取原理：

索引一般以文件形式存儲在磁盤上，索引檢索需要磁盤I/O操作梗掰。與主存不同嵌言，磁盤I/O存在機械運動耗費，因此磁盤I/O的時間消耗是巨大的及穗。

圖5

一個磁盤由大小相同且同軸的圓形盤片組成摧茴，磁盤可以轉(zhuǎn)動（各個磁盤必須同步轉(zhuǎn)動）。在磁盤的一側(cè)有磁頭支架拥坛，磁頭支架固定了一組磁頭蓬蝶，每個磁頭負責存取一個磁盤的內(nèi)容。磁頭不能轉(zhuǎn)動猜惋，但是可以沿磁盤半徑方向運動（實際是斜切向運動）丸氛，每個磁頭同一時刻也必須是同軸的，即從正上方向下看著摔，所有磁頭任何時候都是重疊的（不過目前已經(jīng)有多磁頭獨立技術缓窜，可不受此限制）。

磁盤結(jié)構(gòu)：

圖6

磁道: 每個同心環(huán)叫做一個 扇區(qū): 磁盤的最小存儲單元。 當需要從磁盤讀取數(shù)據(jù)時禾锤，系統(tǒng)會將數(shù)據(jù)邏輯地址傳給磁盤私股，磁盤的控制電路按照尋址邏輯將邏輯地址翻譯成物理地址，即確定要讀的數(shù)據(jù)在哪個磁道恩掷，哪個扇區(qū)倡鲸。為了讀取這個扇區(qū)的數(shù)據(jù)，需要將磁頭放到這個扇區(qū)上方黄娘，為了實現(xiàn)這一點峭状，磁頭需要移動對準相應磁道，這個過程叫做尋道逼争，所耗費時間叫做尋道時間优床，然后磁盤旋轉(zhuǎn)將目標扇區(qū)旋轉(zhuǎn)到磁頭下，這個過程耗費的時間叫做旋轉(zhuǎn)時間誓焦。

局部性原理與磁盤預讀:

由于存儲介質(zhì)的特性胆敞，磁盤本身存取就比主存慢很多，再加上機械運動耗費杂伟，磁盤的存取速度往往是主存的幾百分分之一移层，因此為了提高效率，要盡量減少磁盤I/O赫粥。為了達到這個目的幽钢，磁盤往往不是嚴格按需讀取，而是每次都會預讀傅是，即使只需要一個字節(jié)匪燕，磁盤也會從這個位置開始，順序向后讀取一定長度的數(shù)據(jù)放入內(nèi)存喧笔。預讀可以提高I/O效率帽驯。預讀的長度一般為頁（page:計算機管理存儲器的邏輯塊-通常為4k）的整倍數(shù). 主存和磁盤以頁為單位交換數(shù)據(jù)。當程序要讀取的數(shù)據(jù)不在主存中時书闸，會觸發(fā)一個缺頁異常尼变，此時系統(tǒng)會向磁盤發(fā)出讀盤信號，磁盤會找到數(shù)據(jù)的起始位置并向后連續(xù)讀取一頁或幾頁載入內(nèi)存中浆劲。

B-/+Tree索引的性能優(yōu)勢： 一般使用磁盤I/O次數(shù)評價索引優(yōu)劣嫌术。

1.結(jié)合操作系統(tǒng)存儲結(jié)構(gòu)優(yōu)化處理： mysql巧妙運用操作系統(tǒng)存儲結(jié)構(gòu)(一個節(jié)點分配到一個存儲頁中->盡量減少IO次數(shù)) & 磁盤預讀(緩存預讀->加速預讀馬上要用到的數(shù)據(jù)).

2.B+Tree 單個節(jié)點能放多個子節(jié)點，相同IO次數(shù)牌借，檢索出更多信息度气。

3.B+TREE 只在葉子節(jié)點存儲數(shù)據(jù) & 所有葉子結(jié)點包含一個鏈指針 & 其他內(nèi)層非葉子節(jié)點只存儲索引數(shù)據(jù)。只利用索引快速定位數(shù)據(jù)索引范圍膨报，先定位索引再通過索引高效快速定位數(shù)據(jù)磷籍。

詳解：Mysql設計利用了磁盤預讀原理适荣，將一個B+Tree節(jié)點大小設為一個頁大小，在新建節(jié)點時直接申請一個頁的空間院领，這樣就能保證一個節(jié)點物理上存儲在一個頁里弛矛，加之計算機存儲分配都是按頁對齊的，這樣就實現(xiàn)了每個Node節(jié)點只需要一次I/O操作比然。

B-Tree索引丈氓、B+Tree索引： 單個節(jié)點能放多個子節(jié)點，查詢IO次數(shù)相同(mysql查詢IO次數(shù)最多3-5次-所以需要每個節(jié)點需要存儲很多數(shù)據(jù))

B+TREE 只在葉子節(jié)點存儲數(shù)據(jù) & 所有葉子結(jié)點包含一個鏈指針 & 其他內(nèi)層非葉子節(jié)點只存儲索引數(shù)據(jù)强法。只利用索引快速定位數(shù)據(jù)索引范圍扒寄，先定位索引再通過索引高效快速定位數(shù)據(jù)。

B+Tree更適合外存索引拟烫，原因和內(nèi)節(jié)點出度d有關。從上面分析可以看到迄本，d越大索引的性能越好硕淑，而出度的上限取決于節(jié)點內(nèi)key和data的大小：

B+Tree內(nèi)節(jié)點去掉了data域嘉赎，因此可以擁有更大的出度置媳，擁有更好的性能。只利用索引快速定位數(shù)據(jù)索引范圍公条，先定位索引再通過索引高效快速定位數(shù)據(jù)拇囊。

dmax=floor(pagesize/(keysize+datasize+pointsize))

Mysql 索引實現(xiàn)-MyISAM & InnoDB： important

聚簇索引： 索引 和 數(shù)據(jù)文件為同一個文件。非聚簇索引： 索引 和 數(shù)據(jù)文件分開的索引靶橱。

MyISAM & InnoDB 都使用B+Tree索引結(jié)構(gòu)寥袭。但是底層索引存儲不同，MyISAM 采用非聚簇索引关霸，而InnoDB采用聚簇索引传黄。?

MyISAM索引原理：采用非聚簇索引-MyISAM myi索引文件和myd數(shù)據(jù)文件分離，索引文件僅保存數(shù)據(jù)記錄的指針地址队寇。葉子節(jié)點data域存儲指向數(shù)據(jù)記錄的指針地址膘掰。(底層存儲結(jié)構(gòu)： frm -表定義、 myi -myisam索引佳遣、 myd-myisam數(shù)據(jù))

MyISAM索引按照B+Tree搜索识埋，如果指定的Key存在，則取出其data域的值零渐，然后以data域值-數(shù)據(jù)指針地址去讀取相應數(shù)據(jù)記錄窒舟。輔助索引和主索引在結(jié)構(gòu)上沒有任何區(qū)別，只是主索引要求key是唯一的诵盼，而輔助索引的key可以重復辜纲。MyISAM索引樹如下：

MyISAM非聚簇索引

InnoDB優(yōu)勢：高擴展性笨觅，充分發(fā)揮硬件性能、?Crash Safe耕腾、 支持事務见剩、 可以在線熱備份

InnoDB特性：

1.?事務支持（ACID）2. 擴展性優(yōu)良 3. 讀寫不沖突 4. 緩存加速

2. 功能組件: redo/undo & ?異步IO & ?MVCC & 行級別鎖 & Page Cache（LRU）

InnoDB物理存儲結(jié)構(gòu)如下圖：

InnoDB表空間管理

InnoDB物理存儲文件結(jié)構(gòu)說明：

? ? InnoDB以表空間Tablespace(idb文件)結(jié)構(gòu)進行組織，每個Tablespace 包含多個Segment段扫俺，每個段(分為2種段：葉子節(jié)點Segment&非葉子節(jié)點Segment), 一個Segment段包含多個Extent苍苞，一個Extent占用1M空間包含64個Page（每個Page 16k），InnoDB B-Tree 一個邏輯節(jié)點就分配一個物理Page,一個節(jié)點一次IO操作狼纬。,一個Page里包含很多有序數(shù)據(jù)Row行數(shù)據(jù)羹呵，Row行數(shù)據(jù)中包含F(xiàn)iled屬性數(shù)據(jù)等信息。

? 表空間(ibd文件)

? 段(一個索引2段：葉子節(jié)點Segment & 非葉子節(jié)點Segment）

? Extent（1MB）：一個Extent(1M) 包含64個 Page(16k),一個Page里包含很多有序行數(shù)據(jù) , InnoDB B-Tree 一個邏輯節(jié)點就分配一個物理Page,一個節(jié)點一次IO操作疗琉。

? Page(16KB)

? Row

? Field

表插入數(shù)據(jù)擴展原理： 一次擴張一個Extent空間（1M）冈欢，64個Page,按照順序結(jié)構(gòu)向每個page中插入順序。

InnoDB邏輯組織結(jié)構(gòu)：

InnoDB索引樹結(jié)構(gòu)

每個索引一個B+樹盈简， 一個B+樹節(jié)點 = 一個物理Page（16K）

? 數(shù)據(jù)按16KB切片為Page 并編號凑耻， 編號可映射到物理文件偏移(16K * N）， B+樹葉子節(jié)點前后形成雙向鏈表柠贤， 數(shù)據(jù)按主鍵索引聚簇香浩， 二級索引葉節(jié)點存儲主鍵值， 通過葉節(jié)點主鍵值回表查找數(shù)據(jù)臼勉。

InnoDB索引原理：?

? 采用聚簇索引- InnoDB數(shù)據(jù)&索引文件為一個idb文件邻吭，表數(shù)據(jù)文件本身就是主索引，相鄰的索引臨近存儲宴霸。 葉節(jié)點data域保存了完整的數(shù)據(jù)記錄(數(shù)據(jù)[除主鍵id外其他列data]+主索引[索引key:表主鍵id])囱晴。 葉子節(jié)點直接存儲數(shù)據(jù)記錄，以主鍵id為key,葉子節(jié)點中直接存儲數(shù)據(jù)記錄瓢谢。(底層存儲結(jié)構(gòu):?frm -表定義速缆、 ibd: innoDB數(shù)據(jù)&索引文件)

注：由于InnoDB采用聚簇索引結(jié)構(gòu)存儲，索引InnoDB的數(shù)據(jù)文件需要按照主鍵聚集恩闻，因此InnoDB要求表必須有主鍵(MyISAM可以沒有)艺糜。如果沒有指定mysql會自動選擇一個可以唯一表示數(shù)據(jù)記錄的列作為主鍵，如果不存在這樣的列幢尚，mysql自動為InnoDB表生成一個隱含字段(6個字節(jié)長整型)作為主鍵破停。 InnoDB的所有 輔助索引 都引用 數(shù)據(jù)記錄的主鍵 作為data域。

? 聚簇索引這種實現(xiàn)方式使得按主鍵的搜索十分高效尉剩，但是輔助索引搜索需要檢索兩遍索引：首先檢索輔助索引獲得數(shù)據(jù)記錄主鍵真慢，然后用主鍵到主索引中檢索獲得數(shù)據(jù)記錄。InnoDB聚簇索引結(jié)構(gòu)：

InnoDB聚簇索引

索引查找流程：

#1.索引精確查找: 確定定位條件, 找到根節(jié)點Page No, 根節(jié)點讀到內(nèi)存, 逐層向下查找, 讀取葉子節(jié)點Page,通過 二分查找找到記錄或未命中理茎。（select * from user_info where id = 23）

索引精確查找過程

#2.索引范圍查找：讀取根節(jié)點至內(nèi)存, 確定索引定位條件id=18, 找到滿足條件第一個葉節(jié)點

, 順序掃描所有結(jié)果, 直到終止條件滿足id >=22 （select * from user_info where id >= 18 and id < 22）

索引范圍查找過程

#3.全表掃描：直接讀取葉節(jié)點頭結(jié)點黑界， 順序掃描管嬉， 返回符合條件記錄， 到最終節(jié)點結(jié)束

（select * from user_info where name = 'abc'）

全表掃描過程

#4.二級索引查找

二級索引查找過程

Create table table_x(int id primary key, varchar(64) name,key sec_index(name) )

? Select * from table_x where name = “d”;

通過二級索引查出對應主鍵朗鸠，拿主鍵回表查主鍵索引得到數(shù)據(jù)蚯撩， 二級索引可篩選掉大量無效記錄，提高效率

Innodb對索引的優(yōu)化 Insert Buffer ?todo

Innodb Buffer Pool： todo

Innodb 異步IO框架:

Innodb ACID如何保證:

?原子性 redo + undo ? 一致性 redo ? 隔離性 鎖 + MVCC ? 持久性 redo

Innodb Redo日志:

Innodb 行級別鎖:

參考：

http://blog.codinglabs.org/articles/theory-of-mysql-index.html

https://segmentfault.com/a/1190000004690721

Mysql索引和查詢優(yōu)化