前言
之前在文章【從I/O到索引的那些事】筆者討論了索引在資料庫查詢中體現的作用，主要表現為降低查詢的次數來提高執行效率，根本原因是消減I/O的成本。本文將針對Mysql資料庫做一次相關優化的例證，把查詢和索引做好聯絡，增強實際應用的能力！
關於Mysql
一旦涉及到查詢優化，就離不開索引的應用，本文選取mysql常用的引擎InnoDB作為研究物件，針對InnoDB引擎利用的索引結構B+樹做個簡單說明。
InnoDB的B+樹
假設我們建立表Student，主鍵為id：
CREATE TABLE `Student` (
`id` int(16) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;複製程式碼插入12條資料：
insert into Student(id,name) valuse(1,'XiaoHong')
insert into Student(id,name) valuse(2,'XiaoMing')
insert into Student(id,name) valuse(3,'XiaoFang')
....
insert into Student(id,name) valuse(12,'XiaoYou')
複製程式碼此時，Innodb引擎將會根據主鍵id自行建立一個B+樹的索引結構，我們有如下圖的抽象：
如何理解圖中結構的形態？
表資料首先會根據主鍵id的順序儲存在磁碟空間，圖中葉節點存放表中每行的真實資料，可以認識到表資料本身屬於主鍵索引的一部分，如下圖，每行資料根據主鍵id按序存放：
我們設定id為Int型別佔據4個位元組，name欄位為固定10位元組的Char型別，Student表每行將佔據14個位元組的磁碟空間。在理想狀況下，我們可以簡化成這樣的一個認識：假定圖中第一行（1，XiaoHong）在磁碟地址0x01，那麼第二行（2，XiaoMing）則在磁碟地址0x0f（0x01+14=0x0f），以此類推下去。
非葉節點存放索引值和對應的指標，我們看到這12行資料根據主鍵id分成了五個節點（一個非葉節點四個葉節點），真實環境下Mysql利用磁碟按塊讀取的原理設定每個磁碟塊（也可理解為頁，一般為4kb，innodb中將頁大小設定為16kb）為一個樹節點大小，這樣每次一個磁碟I/O產生的內容就可以獲取對應節點所有資料。
對於非葉節點每個索引值左邊的指標指向小於這個索引值的對應資料的節點地址，索引值右邊的指標指向大於或等於該索引值的對應資料的節點地址：
如上圖，索引值為4的左邊指標的指向結點資料必定都是小於4的，對應右指標指向節點範圍必定是大於或等於4的。而且，在索引值數目一定的情況下，B+樹為了控制樹的高度儘可能小，會要求每個非頁節點儘可能存放更多資料，一般要求非葉節點索引值的個數至少為（n-1)/2，n為一個頁塊大小最多能容納的值個數。按照上圖假設的構造形態，我們知道每個頁塊最多隻能容納三個索引值或三行資料（實際會大很多），在這樣的前提下，如果繼續插入行資料，那麼首先是葉節點將沒有空間容納新資料，此時葉節點通過分裂來增加一個新葉節點完成儲存：
可以想象的是，我們試圖繼續插入2條資料：
insert into Student(id,name) valuse(13,'XiaoRui')
insert into Student(id,name) valuse(14,'XiaoKe')複製程式碼最終將會變成如下形態：
因為每個非頁節點最多容納3個索引值和對應的4個指標（扇出），整個查詢的複雜度為O(log4N)，N為表的行數。對於擁有1000個學生資料的Student表來說，根據id查詢的複雜度為log41000=5，在這裡，查詢複雜度在B+樹中可以直觀地理解為樹的高度，通過非葉節點一層層的遞進判斷最終定位到目標資料所在的頁塊地址。
因此，innodb引擎的表資料是通過主鍵索引結構來組織的，葉節點存放著行資料，是一種B+樹檔案組織，如果通過主鍵定位行資料將擁有極大的效率，所以在建立表時無論有沒明確定義主鍵索引，引擎內部都會自動為表建立一個主鍵索引繼而構造出一個B+樹檔案組織。在實際應用中，當通過主鍵去查詢某些資料時，首先是通過B+樹定位到具體的葉節點地址，因為葉節點剛好設定為磁碟塊連續地址的整數倍大小，所以通過連續地址的快速I/O將整個節點內容載入到記憶體，然後從記憶體中對節點內容進行篩選找出目標資料！
但innodb引擎還允許我們對錶其它欄位單獨構建索引，也就是常說的輔助索引，比如我們這樣建立Student表：
CREATE TABLE `Student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;複製程式碼插入示例資料：
insert into Student(id,name) valuse(1,'A')
insert into Student(id,name) valuse(2,'A')
insert into Student(id,name) valuse(3,'B')
......
......
insert into Student(id,name) valuse(12,'F')複製程式碼如何理解name欄位索引結構存在的形式？直接上圖：
可見，輔助索引同樣會構建一個B+樹索引結構，只不過葉節點存放的是主鍵id值，非數字的索引在索引結構中按照預先設定的字符集排序規則進行排序，比如name=A在對應排序規則中是比B要小的。
按照上圖的結構，假定我們進行如下操作：
select * from Student where name='A';複製程式碼那麼首先會利用輔助索引定位到葉節點1，然後載入到記憶體，在記憶體中檢索發現有兩個主鍵id：1、2 符合條件，然後通過主鍵id再從主鍵索引進行檢索，把行資料全部載入出來！
在輔助索引中，innodb還支援組合索引的形式，把多個欄位按序組合而成一個索引，比如我們建立如下Student表：
CREATE TABLE `StudentTmp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;複製程式碼name和age組合構成一個索引，對應B+樹索引結構有如下形式：
在該組合索引中，葉節點內容先是按照name欄位進行排序，在name欄位值相同情況下再按照age欄位進行排序，這樣在對name和age作為組合條件查詢時將充分利用兩個欄位的排序關係實現多級索引的定位。
好的，我們不在糾結B+樹的更多細節，我們只需先在腦海中構建索引結構的大體形態，想象著索引的查詢是在一個樹狀結構中層層遞進最終定位到目標資料的過程，並且認識到查詢複雜度和B+樹非葉節點中指標個數存在著聯絡，這對於我們形成查詢成本的敏感性是非常有幫助的。
通過Explain方法來了解查詢成本
體會了索引構造帶來的查詢效率的提升，在實際應用中我們又該如何瞭解每個查詢Sql對索引的利用情況呢？Explain方法可以在執行前輔助我們進行判斷，通過相關引數特別是對於多層巢狀或連線的複雜查詢語句具有非常大的幫助。
通過在查詢sql前面加上explain關鍵字就可以完成計劃分析：
explain select id from Student where id=1;
執行後有如下結果：
我們看到結果表單有id、table、select_type...等10個引數，每個引數有對應的結果值，接下來我們一步步做好認識。
id:用於標識各個子查詢的執行順序，值越大執行優先順序越高
上文查詢只是一個簡單查詢，故id只有一個1，我們現在增加一個子查詢後：
explain select name from Student where id=(select max(id) from Student);
有:
可以看到有兩個結果行，說明這個sql有兩個查詢計劃，table欄位用於指明該查詢計劃對應的表名，而id值的作用在於提示我們哪個查詢計劃是優先執行的。
table:指定對應查詢計劃關聯的表名
上文關於id欄位的示例說明中，我們發現id=2的查詢計劃（select max(id) from Student）對應表名是空的，這似乎不符合常規，難道這個查詢計劃不涉及到表操作？我們在Extra欄位中找到了這樣一個說明：Select tables optimized away這個語句告訴我們，引擎對該查詢計劃做了優化，基於索引層面的優化像min/max操作或者count(*)操作，不需要等到執行階段對錶進行檢索，該值可能預先儲存在某些地方直接讀取。筆者猜想的一種情況是，因為id欄位本身屬於Student表的主鍵索引，引擎本身實時儲存著min(id)、max(id)的值供查詢，或者直接讀取主鍵索引樹第一個、最後一個葉節點資料來獲取，所以類似查詢計劃在實際執行中具有極大的執行效率。
select_type:標識查詢計劃的型別
select_type主要有如下幾種不同型別：
- SIMPLE：簡單SELECT,不使用UNION或子查詢等
- PRIMARY：查詢中若包含任何複雜的子部分,最外層的select被標記為PRIMARY
- UNION：UNION中的第二個或後面的SELECT語句
- SUBQUERY：子查詢中的第一個SELECT
DERIVED(派生表的SELECT, FROM子句的子查詢)
對於 explain select id from Student where id=1;
select_type為SIMPLE，表示該sql是最簡單形式的查詢
對於 explain select name from Student union select name from Course;有：
我們看到有兩個查詢計劃，對於最外層Student表的查詢為PRIMARY，表示該語句是複雜語句，包含著其它查詢計劃，而這個包含的查詢計劃就是Course查詢計劃，Course查詢計劃的select_type為UNION，印證了上面對UNION型別的說明。結合id欄位代表的意義，我們瞭解到引擎先是執行Course表計劃再是執行Student表計劃。
對於 explain select id,(select count(*) from Course) as count from Student; 有：
對於 explain select name from (select name from Student where id=1) tb;有：
這個語句的特別之處在於對Student表的子查詢計劃被外面包裹了一層，因此對應的select_type為DERIVED。
到這裡，我們認識到一個sql在執行過程中會被拆分一個以上的查詢計劃，計劃間有一定的執行優先順序，而select_type則很好地定義了不同計劃存在的形式，這使得我們可以把複雜sql進行結構上的拆解，針對不同的查詢計劃一個個分析最後完成整體的優化。
接下來我們開始重點關注explian分析表單的其它幾個欄位：
- type
- possible_keys：查詢計劃可能用到的索引
- key：查詢計劃實際採用的索引
- rows：查詢複雜度，亦可簡單理解為查詢計劃需要處理的行數
這些欄位和索引緊密聯絡，將真正為我們查詢成本的分析提供參考，我們可以通過這些欄位很好地判斷索引的利用情況了。
type:對錶進行資料查詢時所利用的檢索方式
type指明瞭該查詢計劃是否利用了索引結構，以及檢索上存在的具體特點，具體類別有：
- ALL：沒用到索引， MySQL將遍歷全表以找到匹配的行
- index: 只利用索引結構，在innodb可以理解為只在B+樹上進行全域性檢索，不直接對錶進行操作
- range:只檢索給定範圍的行，使用一個索引來選擇行
- ref: 通過索引檢索，只不過該索引是非唯一索引，可能檢索出多個相同值的記錄
- eq_ref: 類似ref，區別就在使用的索引是唯一索引，對於每個索引鍵值，表中只有一條記錄匹配，簡單來說，就是多表連線中使用primary key或者 unique key作為關聯條件
- const、system: 當MySQL對查詢某部分進行優化，並轉換為一個常量時，使用這些型別訪問。如將主鍵置於where列表中，MySQL就能將該查詢轉換為一個常量，system是const型別的特例，當查詢的表只有一行的情況下，使用system
- NULL: MySQL在優化過程中分解語句，執行時甚至不用訪問表或索引，例如從一個索引列裡選取最小值可以通過單獨索引查詢完成
對於 explain select name from Student where name='學生1';有：
type 為 ALL，即name在Student表中不是索引，為了查詢name為'學生1'的資料，資料庫將必要地對錶資料進行全域性檢索，其中rows說明了需要檢索的量級，我們可以理解為查詢複雜度，因為資料庫需要對錶資料一行行處理，上面rows=699323我們可以判斷出Student表大概是70萬行的量級。
對於 explain select name from Student; 有：
type 為 index，因為我們對name已經事先構建了輔助索引，所以查詢表中所有的name資訊只需在name對應的B+樹上掃描即可：
如上圖，直接在輔助索引樹的最左葉節點開始掃描，查詢出所有name資訊，查詢出來的資料本身是按序排好的，如果你對sql剛好有排序需求：
select name from Student order by name asc;複製程式碼那麼查詢速度相較於從表資料結構獲取將有大幅的提升！
對於 explain select * from Student where id>1 and id<5;有：
type 為 range，這說明這個查詢是先通過索引結構進行範圍確定的，如下圖：
對於 explain select name from Student where name='A'; 有：
type 為 ref，表明 name 索引是非唯一索引，即表中可能存在多個name相同的記錄，在通過name索引結構檢索資料時會把匹配條件的所有記錄都檢索出來。
對於 explain select Student.name,Score.score from Score join Student on Score.s_id=Student.id 有：
我們注意到在此連線查詢中，關於Student的主鍵id作為連線條件時，對應Student表的查詢計劃型別為eq_ref，指明利用的是唯一索引的特性，每次對Student的一次查詢都將最終定位到一條結果。
對於 explain select id from Student where id=1; 有：
type 為 const，一般sql對應查詢條件是唯一索引時才出現此情況，說明引擎內部對語句做了特殊處理，在計劃執行前將結果先查詢出來並轉化為一個常量，這樣在實際執行過程中直接引用常量可免去重複的查詢過程。我們再給個例子：
explain select Student.name,Score.score from Score join Student on Score.s_id=Student.id where Student.id=1；有：
對於此連線查詢，在不考慮執行前const優化的情況下可利用虛擬碼表示成如下執行邏輯：
outerIterator=select A.s_id,A.score from Score as A;
//對Score表進行全域性的行掃描
while (outerRow=outerIterator.next){
innerIterator=select A.id,A.name from Student as A where A.id=1;
innerRow=innerIterator.next;
if (innerRow.id=outerRow.s_id){
//將符合條件的結果記錄輸出
print(outerRow.score,innerRow.name);
}
}複製程式碼如上所示，首先是對Score表進行全域性查詢，期間每一行都需要和Student表對應id的資料進行比對，但每次比對都是Student表的一次查詢消耗，因此可以優化成如下邏輯：
//將Student表的查詢計劃優先執行，並將結果賦值到常量
constIterator=select A.id,A.name from Student as A where A.id=1;
constRow=constIterator.next;constId=constRow.id;constName=constRow.name;
//查詢計劃執行過程
outerIterator=select A.s_id,A.score from Score as A;
while (outerRow=outerIterator.next){
//計劃執行過程中只需和對應常量比較，大大提高執行效率
if (innerRow.id=constId){
print(outerRow.score,constName);
}
}複製程式碼通過把Student表計劃的結果提取到常量將避免迴圈檢索中帶來的查詢消耗，由此帶來的效能提升是非常可觀的。
目前為止我們把Explain方法做了個基本的介紹，通過對sql查詢計劃的劃分和索引利用程度的判定已經能提供大部分優化的思路，接下來我們將結合真實的資料進行一次測試，我們將重點關注rows欄位的變化來判定我們優化的效果並希望能在整個過程引申更多思考。
實戰優化
之前本人在論壇看到一同學討論關於資料庫的優化過程，這裡我們參照人家當時面對的表情況進行演示，我們假定基於mysql 5.5版本對一個龐大的教務系統的資料庫進行優化，其中涉及3個表：
學生表（id:學生id；name:學生名）
CREATE TABLE `Student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;複製程式碼Student表初始化有10萬行資料：
INSERT INTO `Student` (`id`, `name`)
VALUES
(1, '學生0'),
(2, '學生1'),
(3, '學生2'),
.....
.....
.....
(700000,'學生699999')複製程式碼課程表（id:課程id；name:課程名稱）
CREATE TABLE `Course` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;複製程式碼課程表初始化有100行資料：
INSERT INTO `Course` (`id`, `name`)
VALUES
(1, '課程0'),
(2, '課程1'),
(3, '課程2'),
.....
.....
.....
(100,'課程99')
複製程式碼成績表（id:記錄id；s_id:學生id；c_id:課程id；score:分數)
CREATE TABLE `Score` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`s_id` int(11) DEFAULT NULL,
`c_id` int(11) DEFAULT NULL,
`score` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;複製程式碼成績表記錄著不同學生對應課程的分數，我們在表裡初始化了10萬學生其中20門課程總共200萬行的資料：
INSERT INTO `Score` (`id`, `s_id`, `c_id`, `score`)
VALUES
(1, 1, 1, 63),
(2, 2, 1, 67),
(3, 3, 1, 40),
.....
.....
(20000000,100000,20,95)複製程式碼實際應用中有這麼個需求，需要查詢出某門課程考了100分的所有同學的名字，寫了如下語句：
select Student.name from Student where id in (select s_id from Score where c_id = 1 and score = 100 );
該執行計劃包含兩個查詢計劃，我們注意到對應的rows分別為十萬級和百萬級，對應的查詢複雜度分別為O(100000)、O(2000000），剛好和表對應行數一樣，說明都進行了全表掃描，我們看type欄位為ALL，印證了我們的設想。接下來對子查詢嘗試建立索引：
alter table Score add index index_cid(c_id)複製程式碼筆者再嘗試了執行一下，經過長達幾分鐘的等待又放棄了，繼續檢視Explain分析結果：
看到Score表的查詢複雜度降為O(200000)，只帶來了10倍的效能優化，通過type=ref我們知道這是一個非唯一索引，說明c_id在表中含有大量相同值其優化效果並不可觀，我們再嘗試對c_id和score建立一個二級索引：
alter table Score add index_cid_score (c_id,score)複製程式碼這次我們總共獲取了1566條結果，總共耗時103s：
對應Explain分析結果：
通過組合索引我們把Score表的查詢複雜度降到了O(1565)，較單個索引有了較大幅提升，但總體的執行時間依舊不能滿意。我們再把目光投向Student表，發現對應的查詢計劃並沒有利用到索引，根據Explain結果，Score表查詢計劃的id值為2，Student表的id值為1，按照優先順序規則，應該是先執行Score計劃：
select s_id from Score where c_id = 1 and score = 100複製程式碼總共1566個結果，耗時45ms：
再執行Student計劃：
select Student.name from Student where id in (55,68,104,243......99688)複製程式碼總共1566個結果，耗時1.06s：
我們預想兩個查詢計劃總共的執行時間應該是1.06s+0.045s=1.105s，這與實際的103s卻有很大的差距，如何解釋？仔細觀察Score表計劃的select_type為DEPENDENT SUBQUERY，上文介紹過SUBQUERY的形式，即表示子查詢中的第一個SELECT，這裡的DEPENDENT標識區別於普通的子查詢在於說明該計劃存在依賴關係，即Score表計劃的執行過程依賴於外面計劃（Student表）的執行結果，整個邏輯過程用虛擬碼表示有：
//外部先對Student表進行全域性的行掃描
outerIterator=select Student.id,Student.name from Student
while (outerRow=outerIterator.next){
//內部Score的執行過程依賴於外部Student表的執行結果
innerIterator=select Score.s_id from Score where c_id = 1 and score = 100
innerRow=innerIterator.next;
if (innerRow.s_id=outerRow.id){
//將符合條件的結果記錄輸出
print(outerRow.name);
}
}複製程式碼先是Student表進行全表掃描，然後內部Score的查詢次數取決於Student表的結果行數，由此得出的查詢複雜度為O(總）=O(Student)*O(Score)，此處例項需要Mysql承擔的計算成本為：O(157337275)=O(100535)*O(1565)，這是需要大量查詢時間的原因！那麼有沒有辦法讓Student表的id索引也發揮作用，至少理論上按照我們前面的設想，我們可以讓整個查詢控制在1s左右呢？
從執行邏輯上看我們可以設想這樣的情況：
//外部先對Score表按條件查詢
outerIterator=select Score.s_id from Score where c_id = 1 and score = 100
while (outerRow=outerIterator.next){
//內部Student的執行過程依賴於外部Score表的執行結果
innerIterator=select Student.id,Student.name from Student where id=outerRow.s_id
innerRow=innerIterator.next;
if (innerRow!=null){
//將符合條件的結果記錄輸出
print(innerRow.name);
}
}複製程式碼先是利用Score表的組合索引檢索出c_id=1、score=100的資料，然後在迴圈匹配中利用Student表的id索引檢索name資訊，在查詢複雜度上：
explain select Score.s_id from Score where c_id = 1 and score = 100有：
explain select name from Student where id=? 有：
猜想理論上有：O(1565)=O(1565)*O(1)，我們試圖將sql用連線查詢來表示：
select Student.name from Student inner join Score on Student.id=Score.s_id where Score.c_id=1 and Score.score=100；
只花費0.048s！看下Explain分析：
果然滿足了我們的期望，Student表計劃用到了唯一索引、Score表用到了組合索引，最後的查詢複雜度也控制在了O(1565)，區別於開始示例的子查詢，連線查詢又為何充分利用了索引呢？內部的執行邏輯該如何去理解？
我們這裡先理一下關於連線查詢的問題，在mysql中，連線的實現本質是笛卡爾積的過程，笛卡爾積中兩個表的所有行都將一一對應得到一次連線，比如語句：
select * from Student,Course;複製程式碼對應的邏輯過程：
//外部先對Student表進行全域性的行掃描
outerIterator=select Student.id,Student.name from Student;
while (outerRow=outerIterator.next){
//迴圈中外部結果每一行都將和Course表每一行進行一次連線
innerIterator=select Course.id,Course.name from Course;
while (innerRow=innerIterator.next){
//獲取對應連線結果
print(outerRow.id,outerRow.name,innerRow.id,innerRow.name)
}
}複製程式碼在mysql中我們一般這樣表示：
select Student.id,Student.name,Course.id,Course.name from Student join Course;複製程式碼可知連線查詢的複雜度最大可達到O(Student錶行數)*O(Course錶行數)，加入n個表進行連線查詢，那麼複雜度模型有O=O(表2行數)*O(表2行數)......*O(表n行數)，這將是一個接近指數級的爆發增長！而在實際應用中，往往會通過關鍵字on和where來控制資料連線規模，具體為根據實際的資料篩選條件對結果行先進行過濾，然後在內部查詢中結合索引完成優化。
好了，回來上面的問題：
select Student.name from Student inner join Score on Student.id=Score.s_id where Score.c_id=1 and Score.score=100；
筆者之前看過資料，一般資料庫進行join連線時會進行笛卡爾積過程，on欄位作為行連線時的判斷條件，最後再利用where條件進行結果行的篩選，具體邏輯過程為：
//外部先對Student表進行全域性的行掃描
outerIterator=select Student.id,Student.name from Student;
while (outerRow=outerIterator.next){
//內部Score的執行過程依賴於外部Student表的執行結果
innerIterator=select Score.s_id,Score.c_id,Score.score from Score;
while(innerRow=innerIterator.next){
//on欄位條件在此處決定是否進行連線
if (outerRow.id=innerRow.s_id){
//將符合連線條件的結果儲存
tmpArr[]=(outerRow.name,innerRow.c_id,innerRow.score);
}
}
}
//接下來開始where條件的結果過濾
for i:=0;i<n;i++{
if (tmpArr[i].c_id=1&&tmpArr[i].score=100){
resultArr[]=tmpArr[i];
}
}
//完成最後的結果輸出
print(resultArr)複製程式碼按照上述過程，我們預測的查詢複雜度應該為O=O(Student錶行數)*O(Score錶行數)；但mysql可沒這麼簡單，通過上文連線查詢的Explain分析，我們看到執行過程都利用了兩個表的索引結構：
Score表利用了組合索引index_cid_score，我們可以猜想到引擎是先嚐試對where條件進行了先行判斷，然後再對結果集和Student表進行連線操作，此連線過程中我們發現Student表有利用到主鍵索引，所以同樣猜測on關鍵字的匹配條件被應用到Student表的查詢計劃中，邏輯過程這樣描述：
//外部先對Score表進行where條件篩選，查詢中利用到組合索引
outerIterator=select Score.s_id,Score.c_id,Score.score from Score where c_id=1 and score=100;
while (outerRow=outerIterator.next){
//內部Student的執行過程利用到主鍵索引，on欄位的判斷條件此時體現在Student查詢計劃的where條件中
innerIterator=select Student.name from Student where id=outerRow.s_id
//如果存在對應行則保留
if(innerRow=innerIterator.next){
resultArr[]=(innerRow.name,outerRow.c_id,outerRow.score);
}
}
//完成最後的結果輸出
print(resultArr)複製程式碼很明顯，上訴邏輯過程的複雜度取決於Score表條件檢索後的行數，也符合我們實際Explain分析的結果。
然而，筆者思考的一個問題是，對於Mysql來說並不是說join連線就一定能滿足優化需求，一方面不同的引擎、不同的Mysql版本所採用的優化手段都可能存在差異，這沒有一個固定標準，應對於這種變化在實際的業務處理中還得多結合Explain進行分析。
筆者針對本次示例在Mysql 5.6版本也嘗試執行了一下，發現對於sql：
select Student.name from Student where Student.id in (select s_id from Score where c_id = 1 and score = 100 )
在同樣的索引構建下，Explain 分析結果為：
Score查詢計劃的id值為2，擁有更高的執行優先順序，但select_type出現了之前在Mysql5.5沒有過的字眼：MATERIALIZED，我們先看下執行結果：
這和我們在Mysql5.5版本關於join連線的結果是一樣的！回到MATERIALIZED的思考，MATERIALIZED的官方描述是：
The optimizer uses materialization to enable more efficient subquery processing. Materialization speeds up query execution by generating a subquery result as a temporary table, normally in memory. The first time MySQL needs the subquery result, it materializes that result into a temporary table. Any subsequent time the result is needed, MySQL refers again to the temporary table. The optimizer may index the table with a hash index to make lookups fast and inexpensive. The index is unique, which eliminates duplicates and makes the table smaller.
大致意思是，優化控制器為了讓子查詢更高效，會將子查詢的結果生成一個臨時表，一般放置在記憶體中，同時對臨時表生成相應的雜湊索引來提高記憶體查詢效率，示例的邏輯過程可以這樣描述：
//先對子查詢的Score表進行物化操作，即查詢結果放置記憶體中
materalizedRows=select Score.s_id from Score where c_id = 1 and score = 100
for i=0;i<n;i++{
//記憶體中取出對應資料
materalizedRow=materalizedRows[i]
//內部Student的執行過程利用到主鍵索引，on欄位的判斷條件此時體現在Student查詢計劃的where條件中
innerIterator=select Student.name from Student where id=materalizedRow.s_id
//如果存在對應行則保留
if(innerRow=innerIterator.next){
resultArr[]=(innerRow.name,outerRow.c_id,outerRow.score);
}
}
//完成最後的結果輸出
print(resultArr)複製程式碼這個邏輯過程和上文join連線是相似的，這裡介紹一種方法用於檢視sql進行優化後的表達形式，在控制檯一次輸入兩個語句：
explain select s.name from Student s where s.id in (select s_id from Score sc where sc.c_id = 1 and sc.score = 100 );
show warnings;複製程式碼得到優化後的sql形式：
select `test`.`Student`.`name` AS `name` from `test`.`Student` semi
join (`test`.`Score`)
where (
(`test`.`Student`.`id` = `<subquery2>`.`s_id`)
and (`test`.`Score`.`score` = 100)
and (`test`.`Score`.`c_id` = 1)
)複製程式碼果然，Mysql5.6中，會對子查詢轉化為join連線形式，而所謂的MATERIALIZED優化，筆者猜想不過是借用join連線所採用的優化形式而已，這說明不同mysql版本對sql語句的結構還會進行調整，筆者建議在面對複雜查詢的時候可以利用此方法先進行了解，然後結合Explain方法進行分析！
到這裡，整個示例的優化過程告一段落了，無論實際環境的查詢需求多麼複雜我們都可以先嚐試進行查詢計劃的劃分，觀察各個計劃的執行優先順序，然後瞭解出引擎內部的執行邏輯，最後算出整體的查詢成本一步步調整優化，大部分情況下筆者屢試不爽！
總結
全文一開始，我們先是瞭解innodb引擎的索引構造，目的在於形成查詢成本的敏感性，具備查詢複雜度判斷的理論支撐，而Explain方法則具體到實際應用的過程中，這是筆者所能想到的最乾脆的優化手段。最後的例項演示體現了優化過程的靈活性，這個靈活體現在Mysql不同版本的支援上，這些都需要在實際應用中積累經驗更好應對。筆者需要提醒的是，索引結構同時在影響著資料庫的維護成本，除了提高查詢效率外，在資料刪改和插入上都增加了資料庫的負擔，這個需要結合實際情況做好權衡！