1.什么是索引？
索引：加速查詢的數(shù)據(jù)結(jié)構(gòu)。
2.索引常見數(shù)據(jù)結(jié)構(gòu)：
#1.順序查找: 最基本的查詢算法-復(fù)雜度O（n）,大數(shù)據(jù)量此算法效率糟糕。
#2.二叉樹查找（binary tree search）: O(log2n)
左邊是數(shù)據(jù)表，一共有兩列七條記錄，最左邊的是數(shù)據(jù)記錄的物理地址（注意邏輯上相鄰的記錄在磁盤上也并不是一定物理相鄰的）。為了加快Col2的查找，可以維護(hù)一個(gè)右邊所示的二叉查找樹，每個(gè)節(jié)點(diǎn)分別包含索引鍵值和一個(gè)指向?qū)?yīng)數(shù)據(jù)記錄物理地址的指針，這樣就可以運(yùn)用二叉查找在O(log2n)O(log2n)的復(fù)雜度內(nèi)獲取到相應(yīng)數(shù)據(jù)。
#3.hash索引 無法滿足范圍查找。
#4.二叉樹、紅黑樹 [復(fù)雜度O(h)]導(dǎo)致樹高度非常高(平衡二叉樹一個(gè)節(jié)點(diǎn)只能有左子樹和右子樹),邏輯上很近的節(jié)點(diǎn)（父子）物理上可能很遠(yuǎn)，無法利用局部性，IO次數(shù)多查找慢,效率低。todo 邏輯上相鄰節(jié)點(diǎn)沒法直接通過順序指針關(guān)聯(lián)，可能需要迭代回到上層節(jié)點(diǎn)重復(fù)向下遍歷找到對(duì)應(yīng)節(jié)點(diǎn)，效率低
B-Tree 和 B+Tree數(shù)據(jù)結(jié)構(gòu)：
#4.B-Tree:
結(jié)構(gòu)：B-TREE 每個(gè)節(jié)點(diǎn)都是一個(gè)二元數(shù)組: [key, data]，所有節(jié)點(diǎn)都可以存儲(chǔ)數(shù)據(jù)。key為索引key,data為除key之外的數(shù)據(jù)。結(jié)構(gòu)如下：
檢索原理：首先從根節(jié)點(diǎn)進(jìn)行二分查找，如果找到則返回對(duì)應(yīng)節(jié)點(diǎn)的data，否則對(duì)相應(yīng)區(qū)間的指針指向的節(jié)點(diǎn)遞歸進(jìn)行查找，直到找到節(jié)點(diǎn)或未找到節(jié)點(diǎn)返回null指針。
缺點(diǎn)：1.插入刪除新的數(shù)據(jù)記錄會(huì)破壞B-Tree的性質(zhì)，因此在插入刪除時(shí)，需要對(duì)樹進(jìn)行一個(gè)分裂、合并、轉(zhuǎn)移等操作以保持B-Tree性質(zhì)。造成IO操作頻繁。2.區(qū)間查找可能需要返回上層節(jié)點(diǎn)重復(fù)遍歷，IO操作繁瑣。
#5.B+Tree: B-Tree的變種
與B-Tree相比，B+Tree有以下不同點(diǎn)：非葉子節(jié)點(diǎn)不存儲(chǔ)data，只存儲(chǔ)索引key；只有葉子節(jié)點(diǎn)才存儲(chǔ)data。結(jié)構(gòu)如下圖：
Mysql中B+Tree：在經(jīng)典B+Tree的基礎(chǔ)上進(jìn)行了優(yōu)化，增加了順序訪問指針。在B+Tree的每個(gè)葉子節(jié)點(diǎn)增加一個(gè)指向相鄰葉子節(jié)點(diǎn)的指針，就形成了帶有順序訪問指針的B+Tree。這樣就提高了區(qū)間訪問性能：如果要查詢key為從18到49的所有數(shù)據(jù)記錄，當(dāng)找到18后，只需順著節(jié)點(diǎn)和指針順序遍歷就可以一次性訪問到所有數(shù)據(jù)節(jié)點(diǎn)，極大提到了區(qū)間查詢效率(無需返回上層父節(jié)點(diǎn)重復(fù)遍歷查找減少IO操作)。
結(jié)構(gòu)如下：
3.為什么Mysql選擇B+TREE索引? B+TREE索引有什么好處?
? 索引本身也很大，不可能全部存儲(chǔ)在內(nèi)存中，因此索引往往以索引文件的形式存儲(chǔ)的磁盤上。這樣的話，索引查找過程中就要產(chǎn)生磁盤I/O消耗，相對(duì)于內(nèi)存存取，I/O存取的消耗要高幾個(gè)數(shù)量級(jí)，所以索引的結(jié)構(gòu)組織要盡量減少查找過程中磁盤I/O的存取次數(shù)，提升索引效率。
磁盤存取原理：
索引一般以文件形式存儲(chǔ)在磁盤上，索引檢索需要磁盤I/O操作。與主存不同，磁盤I/O存在機(jī)械運(yùn)動(dòng)耗費(fèi)，因此磁盤I/O的時(shí)間消耗是巨大的。
一個(gè)磁盤由大小相同且同軸的圓形盤片組成，磁盤可以轉(zhuǎn)動(dòng)（各個(gè)磁盤必須同步轉(zhuǎn)動(dòng)）。在磁盤的一側(cè)有磁頭支架，磁頭支架固定了一組磁頭，每個(gè)磁頭負(fù)責(zé)存取一個(gè)磁盤的內(nèi)容。磁頭不能轉(zhuǎn)動(dòng)，但是可以沿磁盤半徑方向運(yùn)動(dòng)（實(shí)際是斜切向運(yùn)動(dòng)），每個(gè)磁頭同一時(shí)刻也必須是同軸的，即從正上方向下看，所有磁頭任何時(shí)候都是重疊的（不過目前已經(jīng)有多磁頭獨(dú)立技術(shù)，可不受此限制）。
磁盤結(jié)構(gòu)：
磁道: 每個(gè)同心環(huán)叫做一個(gè) 扇區(qū): 磁盤的最小存儲(chǔ)單元。 當(dāng)需要從磁盤讀取數(shù)據(jù)時(shí)，系統(tǒng)會(huì)將數(shù)據(jù)邏輯地址傳給磁盤，磁盤的控制電路按照尋址邏輯將邏輯地址翻譯成物理地址，即確定要讀的數(shù)據(jù)在哪個(gè)磁道，哪個(gè)扇區(qū)。為了讀取這個(gè)扇區(qū)的數(shù)據(jù)，需要將磁頭放到這個(gè)扇區(qū)上方，為了實(shí)現(xiàn)這一點(diǎn)，磁頭需要移動(dòng)對(duì)準(zhǔn)相應(yīng)磁道，這個(gè)過程叫做尋道，所耗費(fèi)時(shí)間叫做尋道時(shí)間，然后磁盤旋轉(zhuǎn)將目標(biāo)扇區(qū)旋轉(zhuǎn)到磁頭下，這個(gè)過程耗費(fèi)的時(shí)間叫做旋轉(zhuǎn)時(shí)間。
局部性原理與磁盤預(yù)讀:
由于存儲(chǔ)介質(zhì)的特性，磁盤本身存取就比主存慢很多，再加上機(jī)械運(yùn)動(dòng)耗費(fèi)，磁盤的存取速度往往是主存的幾百分分之一，因此為了提高效率，要盡量減少磁盤I/O。為了達(dá)到這個(gè)目的，磁盤往往不是嚴(yán)格按需讀取，而是每次都會(huì)預(yù)讀，即使只需要一個(gè)字節(jié)，磁盤也會(huì)從這個(gè)位置開始，順序向后讀取一定長度的數(shù)據(jù)放入內(nèi)存。預(yù)讀可以提高I/O效率。預(yù)讀的長度一般為頁（page:計(jì)算機(jī)管理存儲(chǔ)器的邏輯塊-通常為4k）的整倍數(shù). 主存和磁盤以頁為單位交換數(shù)據(jù)。當(dāng)程序要讀取的數(shù)據(jù)不在主存中時(shí)，會(huì)觸發(fā)一個(gè)缺頁異常，此時(shí)系統(tǒng)會(huì)向磁盤發(fā)出讀盤信號(hào)，磁盤會(huì)找到數(shù)據(jù)的起始位置并向后連續(xù)讀取一頁或幾頁載入內(nèi)存中。
B-/+Tree索引的性能優(yōu)勢： 一般使用磁盤I/O次數(shù)評(píng)價(jià)索引優(yōu)劣。
1.結(jié)合操作系統(tǒng)存儲(chǔ)結(jié)構(gòu)優(yōu)化處理： mysql巧妙運(yùn)用操作系統(tǒng)存儲(chǔ)結(jié)構(gòu)(一個(gè)節(jié)點(diǎn)分配到一個(gè)存儲(chǔ)頁中->盡量減少IO次數(shù)) & 磁盤預(yù)讀(緩存預(yù)讀->加速預(yù)讀馬上要用到的數(shù)據(jù)).
2.B+Tree 單個(gè)節(jié)點(diǎn)能放多個(gè)子節(jié)點(diǎn)，相同IO次數(shù)，檢索出更多信息。
3.B+TREE 只在葉子節(jié)點(diǎn)存儲(chǔ)數(shù)據(jù) & 所有葉子結(jié)點(diǎn)包含一個(gè)鏈指針 & 其他內(nèi)層非葉子節(jié)點(diǎn)只存儲(chǔ)索引數(shù)據(jù)。只利用索引快速定位數(shù)據(jù)索引范圍，先定位索引再通過索引高效快速定位數(shù)據(jù)。
詳解：Mysql設(shè)計(jì)利用了磁盤預(yù)讀原理，將一個(gè)B+Tree節(jié)點(diǎn)大小設(shè)為一個(gè)頁大小，在新建節(jié)點(diǎn)時(shí)直接申請一個(gè)頁的空間，這樣就能保證一個(gè)節(jié)點(diǎn)物理上存儲(chǔ)在一個(gè)頁里，加之計(jì)算機(jī)存儲(chǔ)分配都是按頁對(duì)齊的，這樣就實(shí)現(xiàn)了每個(gè)Node節(jié)點(diǎn)只需要一次I/O操作。
B-Tree索引、B+Tree索引： 單個(gè)節(jié)點(diǎn)能放多個(gè)子節(jié)點(diǎn)，查詢IO次數(shù)相同(mysql查詢IO次數(shù)最多3-5次-所以需要每個(gè)節(jié)點(diǎn)需要存儲(chǔ)很多數(shù)據(jù))
B+TREE 只在葉子節(jié)點(diǎn)存儲(chǔ)數(shù)據(jù) & 所有葉子結(jié)點(diǎn)包含一個(gè)鏈指針 & 其他內(nèi)層非葉子節(jié)點(diǎn)只存儲(chǔ)索引數(shù)據(jù)。只利用索引快速定位數(shù)據(jù)索引范圍，先定位索引再通過索引高效快速定位數(shù)據(jù)。
B+Tree更適合外存索引，原因和內(nèi)節(jié)點(diǎn)出度d有關(guān)。從上面分析可以看到，d越大索引的性能越好，而出度的上限取決于節(jié)點(diǎn)內(nèi)key和data的大小：
B+Tree內(nèi)節(jié)點(diǎn)去掉了data域，因此可以擁有更大的出度，擁有更好的性能。只利用索引快速定位數(shù)據(jù)索引范圍，先定位索引再通過索引高效快速定位數(shù)據(jù)。
dmax=floor(pagesize/(keysize+datasize+pointsize))
Mysql 索引實(shí)現(xiàn)-MyISAM & InnoDB： important
聚簇索引： 索引 和 數(shù)據(jù)文件為同一個(gè)文件。非聚簇索引： 索引 和 數(shù)據(jù)文件分開的索引。
MyISAM & InnoDB 都使用B+Tree索引結(jié)構(gòu)。但是底層索引存儲(chǔ)不同，MyISAM 采用非聚簇索引，而InnoDB采用聚簇索引。?
MyISAM索引原理：采用非聚簇索引-MyISAM myi索引文件和myd數(shù)據(jù)文件分離，索引文件僅保存數(shù)據(jù)記錄的指針地址。葉子節(jié)點(diǎn)data域存儲(chǔ)指向數(shù)據(jù)記錄的指針地址。(底層存儲(chǔ)結(jié)構(gòu)： frm -表定義、 myi -myisam索引、 myd-myisam數(shù)據(jù))
MyISAM索引按照B+Tree搜索，如果指定的Key存在，則取出其data域的值，然后以data域值-數(shù)據(jù)指針地址去讀取相應(yīng)數(shù)據(jù)記錄。輔助索引和主索引在結(jié)構(gòu)上沒有任何區(qū)別，只是主索引要求key是唯一的，而輔助索引的key可以重復(fù)。MyISAM索引樹如下：
InnoDB優(yōu)勢：高擴(kuò)展性，充分發(fā)揮硬件性能、?Crash Safe、 支持事務(wù)、 可以在線熱備份
InnoDB特性：
1.?事務(wù)支持（ACID）2. 擴(kuò)展性優(yōu)良 3. 讀寫不沖突 4. 緩存加速
2. 功能組件: redo/undo & ?異步IO & ?MVCC & 行級(jí)別鎖 & Page Cache（LRU）
InnoDB物理存儲(chǔ)結(jié)構(gòu)如下圖：
InnoDB物理存儲(chǔ)文件結(jié)構(gòu)說明：
? ? InnoDB以表空間Tablespace(idb文件)結(jié)構(gòu)進(jìn)行組織，每個(gè)Tablespace 包含多個(gè)Segment段，每個(gè)段(分為2種段：葉子節(jié)點(diǎn)Segment&非葉子節(jié)點(diǎn)Segment), 一個(gè)Segment段包含多個(gè)Extent，一個(gè)Extent占用1M空間包含64個(gè)Page（每個(gè)Page 16k），InnoDB B-Tree 一個(gè)邏輯節(jié)點(diǎn)就分配一個(gè)物理Page,一個(gè)節(jié)點(diǎn)一次IO操作。,一個(gè)Page里包含很多有序數(shù)據(jù)Row行數(shù)據(jù)，Row行數(shù)據(jù)中包含F(xiàn)iled屬性數(shù)據(jù)等信息。
? 表空間(ibd文件)
? 段(一個(gè)索引2段：葉子節(jié)點(diǎn)Segment & 非葉子節(jié)點(diǎn)Segment）
? Extent（1MB）：一個(gè)Extent(1M) 包含64個(gè) Page(16k),一個(gè)Page里包含很多有序行數(shù)據(jù) , InnoDB B-Tree 一個(gè)邏輯節(jié)點(diǎn)就分配一個(gè)物理Page,一個(gè)節(jié)點(diǎn)一次IO操作。
? Page(16KB)
? Row
? Field
表插入數(shù)據(jù)擴(kuò)展原理： 一次擴(kuò)張一個(gè)Extent空間（1M），64個(gè)Page,按照順序結(jié)構(gòu)向每個(gè)page中插入順序。
InnoDB邏輯組織結(jié)構(gòu)：
每個(gè)索引一個(gè)B+樹， 一個(gè)B+樹節(jié)點(diǎn) = 一個(gè)物理Page（16K）
? 數(shù)據(jù)按16KB切片為Page 并編號(hào)， 編號(hào)可映射到物理文件偏移(16K * N）， B+樹葉子節(jié)點(diǎn)前后形成雙向鏈表， 數(shù)據(jù)按主鍵索引聚簇， 二級(jí)索引葉節(jié)點(diǎn)存儲(chǔ)主鍵值， 通過葉節(jié)點(diǎn)主鍵值回表查找數(shù)據(jù)。
InnoDB索引原理：?
? 采用聚簇索引- InnoDB數(shù)據(jù)&索引文件為一個(gè)idb文件，表數(shù)據(jù)文件本身就是主索引，相鄰的索引臨近存儲(chǔ)。 葉節(jié)點(diǎn)data域保存了完整的數(shù)據(jù)記錄(數(shù)據(jù)[除主鍵id外其他列data]+主索引[索引key:表主鍵id])。 葉子節(jié)點(diǎn)直接存儲(chǔ)數(shù)據(jù)記錄，以主鍵id為key,葉子節(jié)點(diǎn)中直接存儲(chǔ)數(shù)據(jù)記錄。(底層存儲(chǔ)結(jié)構(gòu):?frm -表定義、 ibd: innoDB數(shù)據(jù)&索引文件)
注：由于InnoDB采用聚簇索引結(jié)構(gòu)存儲(chǔ)，索引InnoDB的數(shù)據(jù)文件需要按照主鍵聚集，因此InnoDB要求表必須有主鍵(MyISAM可以沒有)。如果沒有指定mysql會(huì)自動(dòng)選擇一個(gè)可以唯一表示數(shù)據(jù)記錄的列作為主鍵，如果不存在這樣的列，mysql自動(dòng)為InnoDB表生成一個(gè)隱含字段(6個(gè)字節(jié)長整型)作為主鍵。 InnoDB的所有 輔助索引 都引用 數(shù)據(jù)記錄的主鍵 作為data域。
? 聚簇索引這種實(shí)現(xiàn)方式使得按主鍵的搜索十分高效，但是輔助索引搜索需要檢索兩遍索引：首先檢索輔助索引獲得數(shù)據(jù)記錄主鍵，然后用主鍵到主索引中檢索獲得數(shù)據(jù)記錄。InnoDB聚簇索引結(jié)構(gòu)：
索引查找流程：
#1.索引精確查找: 確定定位條件, 找到根節(jié)點(diǎn)Page No, 根節(jié)點(diǎn)讀到內(nèi)存, 逐層向下查找, 讀取葉子節(jié)點(diǎn)Page,通過 二分查找找到記錄或未命中。（select * from user_info where id = 23）
#2.索引范圍查找：讀取根節(jié)點(diǎn)至內(nèi)存, 確定索引定位條件id=18, 找到滿足條件第一個(gè)葉節(jié)點(diǎn)
, 順序掃描所有結(jié)果, 直到終止條件滿足id >=22 （select * from user_info where id >= 18 and id < 22）
#3.全表掃描：直接讀取葉節(jié)點(diǎn)頭結(jié)點(diǎn)， 順序掃描， 返回符合條件記錄， 到最終節(jié)點(diǎn)結(jié)束
（select * from user_info where name = 'abc'）
#4.二級(jí)索引查找
Create table table_x(int id primary key, varchar(64) name,key sec_index(name) )
? Select * from table_x where name = “d”;
通過二級(jí)索引查出對(duì)應(yīng)主鍵，拿主鍵回表查主鍵索引得到數(shù)據(jù)， 二級(jí)索引可篩選掉大量無效記錄，提高效率
Innodb對(duì)索引的優(yōu)化 Insert Buffer ?todo
Innodb Buffer Pool： todo
Innodb 異步IO框架:
Innodb ACID如何保證:
?原子性 redo + undo ? 一致性 redo ? 隔離性 鎖 + MVCC ? 持久性 redo
Innodb Redo日志:
Innodb 行級(jí)別鎖:
參考：
http://blog.codinglabs.org/articles/theory-of-mysql-index.html
https://segmentfault.com/a/1190000004690721
Mysql索引和查詢優(yōu)化
