create table t1 (
a int primary key,
b int,
c int,
d int,
e varchar(20)
) engine = innodb;
insert into t1 value(4, 3, 1, 1, 'd');
insert into t1 value(1, 1, 1, 1, 'a');
insert into t1 value(8, 8, 8, 8, 'h');
insert into t1 value(2, 2, 2, 2, 'b');
insert into t1 value(5, 2, 3, 5, 'e');
insert into t1 value(3, 3, 2, 2, 'c');
insert into t1 value(7, 4, 5, 5, 'g');
insert into t1 value(6, 6, 4, 4, 'f');
1、頁
頁是InnoBD存儲引摯管理數據庫的最小磁盤單位（邏輯單位）未状，遵循計算機的局部性原理
1窥妇、局部性原理
局部性原理又表現為：時間局部性和空間局部性。
- 時間局部性是指如果程序中的某條指令一旦執(zhí)行娩践，則不久之后該指令可能再次被執(zhí)行活翩；如果某數據被訪問，則不久之后該數據可能再次被訪問翻伺。
- 空間局部性是指一旦程序訪問了某個存儲單元材泄，則不久之后，其附近的存儲單元也將被訪問吨岭。
2拉宗、磁盤IO與預讀
考慮到磁盤IO是非常高昂的操作，計算機操作系統(tǒng)做了一些優(yōu)化，當一次IO時旦事，不光把當前磁盤地址的數據魁巩，而是把相鄰的數據也都讀取到內存緩沖區(qū)內，因為局部預讀性原理告訴我們姐浮，當計算機訪問一個地址的數據的時候谷遂，與其相鄰的數據也會很快被訪問到。每一次IO讀取的數據我們稱之為一頁(page)卖鲤。具體一頁有多大數據跟操作系統(tǒng)有關肾扰，一般為4k或8k，也就是我們讀取一頁內的數據時候蛋逾，實際上才發(fā)生了一次IO集晚，這個理論對于索引的數據結構設計非常有幫助。（在InnoDB一頁的大小是16KB）
3区匣、InnoDB數據頁由以下7個部分組成
- FIle Header（文件頭）
- Page Header（頁頭）
- Infimun 和 Supremum Records
- User Records（用戶記錄偷拔，即行記錄）
- Free Space（空閑空間）
- Page Directory（頁目錄）
- File Trailer（文件結尾信息）
特別說明：
①：主鍵索引，b+樹中會存儲完整記錄亏钩，即每一個頁的每個數據項記錄的是某一個行的完整信息
②：二級索引条摸，b+數中不會存在完整記錄，即每一頁的每個數據項記錄的是某一行的主鍵索引（注意铸屉，這里記錄的是一個索引钉蒲，還需要回表到主鍵索引對應的B+樹去查）
③：頁目錄的作用：通過槽對用戶數據區(qū)域的數據項進行劃分，以多少個數據項為一組彻坛，而槽記錄的是該組第一個數據項的地址偏移量顷啼。當查找一個數據時，具體的執(zhí)行流程在下面
④：用戶數據區(qū)域的數據項是通過某個值進行排序的昌屉，主鍵索引通過主鍵值排序钙蒙，其他二級索引通過特定的索引值排序
4、在某一頁中的查找數據的執(zhí)行流程
通過某個索引值去查找數據時间驮，找到該頁時躬厌，通過二分法在頁目錄中找到對應的槽，在槽維護的區(qū)間中逐個遍歷尋找竞帽，找到后返回
例如：
select * from t1 where a = 3
插入操作
在圖中扛施，加入用戶數據區(qū)域只能存4條數據，當插入(5,2,3,5,'e')時屹篓，會把(5,2,3,5,'e')插入到該頁中疙渣，而將(8,8,8,8,'h')調整到下一頁
兩頁數據的存儲情況
2、InnoDB 存儲引擎使用B+樹結構
如果數據中數據極少堆巧，只有一兩條記錄妄荔，完全沒有必要用B+樹泼菌，B+樹是需要去維護的，需要一定的成本啦租，占用一定的內存哗伯，B+樹是隨著數據的增加慢慢生成出來的，當數據進行插入的時候篷角，是根據B+樹的特性進行插入焊刹，慢慢呈現出一棵B+樹
其中，每個大框框都是代表一個頁内地，指針指向的是一個頁
1、索引的本質是什么
索引是mysql高效去獲取數據并排好序的數據結構赋除，數據結構指的是組織數據的方式阱缓。例如，主鍵值數據是以B+樹進行組織的举农，因此這個B+樹就是一個主鍵索引荆针。
每一個索引在 InnoDB 里面對應一棵 B+ 樹。
**颁糟，該B+樹的葉結點是存放具體數據的航背，
2、聚簇索引 與 非聚簇索引
（1）聚簇索引
1棱貌、以 InnoDB 作為存儲引擎的表玖媚，表中的數據都會有一個主鍵，即使你不創(chuàng)建主鍵婚脱，系統(tǒng)也會幫你創(chuàng)建一個隱式的主鍵今魔。這是因為 InnoDB 是把數據存放在 B+ 樹中的，而 B+ 樹的鍵值就是主鍵障贸，在 B+ 樹的葉子節(jié)點中错森，存儲了表中所有的數據。
2篮洁、即每個表都會有一棵通過主鍵值建立出來的B+樹（主鍵索引）涩维，主鍵索引的葉子節(jié)點存的是整行數據。在 InnoDB 里袁波，主鍵索引也被稱為聚簇索引
（2）非聚簇索引
以主鍵以外的列值作為鍵值構建的 B+ 樹索引瓦阐，且非主鍵索引的葉子節(jié)點內容是主鍵的值，在 InnoDB 里篷牌，我們稱之為非聚集索引垄分。也稱 非主鍵索引 或者 二級索引
3、當某個表中如果沒有主鍵怎么辦娃磺？
如果沒有主鍵索引薄湿，就把唯一索引作為主鍵索引；如果沒有唯一索引，則會對每一行生成一個隱式字段row_id豺瘤，通過他們作為主鍵索引
注意：必須需要一個唯一確定該表的主鍵索引吆倦，整棵B+樹是通過主鍵索引的排序規(guī)律進行插入，尋找的
4坐求、聚簇索引（主鍵索引）例子
（1）精確查詢
explain select * from t1 where a = 1;
分析：由于a是主鍵蚕泽，因此可以通過主鍵索引進行查詢
（2）范圍查詢
explain select * from t1 where a > 1;
分析：也是通過主鍵索引進行查詢，查到a = 1的位置對應的葉結點桥嗤，通過葉結點繼續(xù)往后找就可以了
5须妻、非聚簇索引（主鍵索引）例子
（1）在只有主鍵索引的情況下，查找如下
explain select * from t1 where b = 2 and c = 3 and d = 5;
分析：會通過全表掃描的方式查詢泛领，即通過主鍵索引搜到最小的頁荒吏，從左往右掃描
（2）如果上面的查詢語句是我們經常使用的查詢，我們可以添加一個索引渊鞋，再通過該索引idx_t1_bcd進行查詢绰更，該索引idx_t1_bcd也會生成一棵B+樹，存的數據是該行的主鍵锡宋。同時數據的排序規(guī)則是儡湾，先對b排序，相同再對c排序执俩，相同再對d排序徐钠。如圖所示
create index idx_t1_bcd on t1(b, c, d);//建立一個bcd索引
explain select * from t1 where b = 2 and c = 3 and d = 5;
分析：通過索引idx_t1_bcd找到b = 2，c = 3役首，d = 5對應是數據是主鍵值是5丹皱，即a = 5，再通過該主鍵值繼續(xù)通過主鍵索引去尋找具體的行
注意：非聚簇索引存儲的是索引的數據（b宋税，c摊崭，d）以及主鍵是數據（a），要尋找其他的數據需要繼續(xù)通過聚簇索引（主鍵索引）查找杰赛，這樣的操作叫做回表（特別重要）
覆蓋索引
InnoDB存儲引擎支持覆蓋索引（covering index呢簸，或稱索引覆蓋），即從輔助索引中就可以得到查詢記錄乏屯，而不需要查詢聚集索引中的記錄根时。
使用覆蓋索引的一個好處是：輔助索引不包含整行記錄的所有信息，故其大小要遠小于聚集索引辰晕，因此可以減少大量的IO操作
（3）如果條件也是b蛤迎，c，d含友，只不過順序不同替裆，會怎么樣查詢呢校辩？
explain select * from t1 where c = 1 and d = 1 and b = 1;
分析：也是會通過索引idx_t1_bcd進行查詢，因為查詢的時候在Server層會經過一個優(yōu)化器辆童，該優(yōu)化器會對條件c宜咒，d，b進行調優(yōu)再決定使用哪個索引去查詢把鉴，最終選擇索引idx_t1_bcd進行查詢（例如故黑，可以通過條件順序調整成固定順序b，c庭砍，d的形式）
（4）如果條件只是c和d场晶，會怎么樣查詢呢？怠缸？
explain select * from t1 where c = 1 and d = 1 ;
分析：會通過全表掃描進行查詢诗轻，原因是因為b不知道，導致如果用索引idx_t1_bcd去查詢的時候凯旭，不知道是向左邊找還是向右邊找概耻，因此只能全表掃描
這就是最左前綴原則
B+ 樹這種索引結構使套，可以利用索引的“最左前綴”罐呼，來定位記錄。如果想使用哪個索引侦高，一定要提供這個字段的最左邊的字段嫉柴，因為是按照最左邊的字段順序排序的
（5）如果條件只是b和d，會怎么樣查詢呢奉呛？计螺？
explain select * from t1 where b = 1 and d = 1 ;
分析：先通過索引idx_t1_bcd尋找到b=1的第一個頁，因為哪些符合還不知道瞧壮，因此在b=1的第一個頁中開始往后遍歷登馒，當每一次都滿足d也等于1（例如111），則拿到該數據項的主鍵值去回表查詢咆槽，直到b不等于1為止就停止
這就是索引下推
在找到最左前綴對應的ID時陈轿，在非聚簇索引中開始往后找，滿足條件的則進行回表（并不是每個都回表秦忿，只有滿足條件才回表查詢）
在 MySQL 5.6 之前麦射，只能從 ID3 開始一個個回表。到主鍵索引上找出數據行灯谣，再對比字段值潜秋。
而 MySQL 5.6 引入的索引下推優(yōu)化（index condition pushdown)， 可以在索引遍歷過程中胎许，對索引中包含的字段先做判斷峻呛，直接過濾掉不滿足條件的記錄罗售，減少回表次數。
（6）如果條件只是b和e杀饵，會怎么樣查詢呢莽囤？？
explain select * from t1 where b = 1 and e = 1 ;
分析：先通過索引idx_t1_bcd尋找到 b=1 的第一個頁的第一個主鍵值切距，然后再在索引idx_t1_bcd中往后掃朽缎，每當滿足b = 1的就回表查詢，回表查詢后發(fā)現e=1則返回數據谜悟，
下面的分析是錯誤的话肖，由于主鍵索引是以a為鍵值，b = 1并不是在主鍵索引中是連續(xù)的葡幸，因此是錯誤的
先通過索引idx_t1_bcd尋找到 b=1 的第一個頁的第一個主鍵值最筒，再進行回表，找到主鍵索引中第一個滿足條件的頁的第一項蔚叨，從左往右掃描床蜘，直到b不等于1為止
（7）如果條件只是b > 1，會怎么樣查詢呢蔑水？邢锯？
explain select * from t1 where b > 1;
分析：優(yōu)化器會分析情況一和情況二的性能，再做出決策
情況一：
先通過索引idx_t1_bcd尋找到b=1的第一個頁的第一個主鍵值搀别，再在索引idx_t1_bcd中往后掃丹擎，每當滿足b > 1的就回表查詢，（和6一樣）
情況二：
可以通過主鍵索引進行全表查詢
（8）如果條件如下
explain select c from t1 where b = 1;
分析：直接在索引idx_t1_bcd找到第一個b = 1的數據項歇父，從左往右掃描蒂培，然后直接返回即可（覆蓋索引）
（9）如果條件如下
create index idx_t1_e on t1(e);//建立一個e索引
explain select * from t1 where a = 1; (1)
explain select * from t1 where a = '1'; (2)
explain select * from t1 where e = 1; (3)
explain select * from t1 where e = '1'; (4)
可以很明確指定（1）會走a的索引，（4）會走e的索引
引理：字符串可以自動轉數字榜苫，而數字不能自動轉字符串
因此a = '1'會自動轉換成a = 1护戳，因此（2）也是走a的索引
然而e = 1不會自動轉成‘1’，因此（4）只能e = 1去全表掃描垂睬，將查到的字符串e先將它轉成數字媳荒，再和1比較
字符串作為索引去建一棵B+樹，是通過某一個特定的規(guī)律進行字符串排序的羔飞，也就是說e = 1不能走idx_t1_e索引去搜肺樟，可能idx_t1_e索引的順序是1,3,2，并非按照1,2,3的順序排列
