查詢緩存：
在解析一個sql之前，如果查詢緩存是打開的，mysql會去檢查這個查詢(根據sql的hash作為key)是否存在緩存中，如果命中的話，那麼這個sql将會在解析，生成執行計劃之前傳回結果。
ps:在5.1版本之前，使用=?參數這種不能使用查詢緩存。
查詢優化器：
oracle使用基于cost的優化器。
可以使用last_query_cost來擷取目前回話的上一個查詢的cost：
selectSQL_NO_CACHE count(*) from t_person;
show status like 'last_query_cost';
傳回的結果10.499表示mysql查詢優化器認為大概需要10個資料頁的随機查找才能完成這個查詢。這個結果是根據一系列的資料得出的，如每個表或者索引的頁面個數，索引的基數，索引和資料行的長度，索引分布情況。
由于統計資訊的不準确，或者mysql本身的實作機制，有些情況下，計算的成本并不準确。
mysql能夠處理的優化有：重新定義關聯表的順序
将外連接配接轉換為内連接配接
使用等價變換規則 如(5=5 and a> 5)被改寫成(a>5)
優化count(),min(),max()，如對有索引的列取min隻需要取b-tree中找第一個節點就可以了。
預估并轉化為常數表達式。不會改變的函數如上面提到的min函數會被轉化為常數。
覆寫索引掃描
子查詢優化
提前終隻查詢
等值傳播
mysql對where條件的處理：
一般Mysql可以使用如下三種方式應用where條件，從好到壞依次為：
a. 在 索引 中使用WHERE條件來過濾不比對的記錄。這是在存儲引擎層完成的。如果沒有出現using where，那麼代表所有的條件都走了索引。如果出現了Using index condition那麼代表出現了索引條件下推。
b. 使用索引覆寫掃描(在Extra列中出現Using index)來傳回記錄，直接從索引中過濾不需要的記錄并傳回命中的結果。這是在MySQL伺服器層完成的，但無需再回表查詢記錄。
c. 從資料表中傳回資料，然後過濾掉不滿足條件的記錄(在Extra列中出現Using Where)。這在MySQL伺服器層完成，MySQL需要先從資料表讀取記錄然後過濾。
如果有如下表：CREATE TABLE `t_person` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(40) DEFAULT NULL,
`age` mediumint(9) DEFAULT NULL,
`address` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_t_person_name_age` (`age`,`name`)
)
對它執行查詢：
注意using where隻是辨別，是否在服務層進行了過濾，并不代表沒有走索引。因為存在走了索引條件之後，拿到資料到服務層進行其他條件的過濾的情況。雖然有了索引條件下推之後，一些低效的using where被避免了。
下面這個結果有點不能了解，我了解應該傳回：Using index ，Using index condition才合适：
重構查詢的方式1：切分查詢
在大量更新的時候，切分查詢：切分查詢的一個重要使用是在對大量資料進行delete的時候，按照條件切分成多個sql，切分之後可以減少每次持有的鎖。
如果一個查詢涉及多張表關聯，可以分解關聯查詢。
分解關聯查詢的方式重構查詢有如下的優勢：
a. 讓緩存的效率更高。許多應用程式可以友善地使用緩存單表查詢對應的結果集。
b. 将查詢分解後，執行單個查詢可以減少鎖的競争。(這個對非串行化事務隔離級别的innodb無效，因為它的查詢沒有鎖。)
c. 在應用層做關聯，可以更容易對資料庫進行拆分，更容易做到高性能和可擴充性。
d. 查詢本身效率也可能會有所提升。
e. 可以減少備援記錄的查詢。管理查詢中可能需要重複地通路一部分資料。
f. 更進一步，這樣做相當于在應用中實作了哈希關聯，而不是使用MySQL的嵌套循環關聯。某些場景哈希關聯的效率要高很多。(Hash關聯适用于兩張大表的關聯，應該不适合在應用伺服器的層面做這個)
但是分解關聯查詢這種方式我不太認可。要看具體的使用場景。比如查詢很大，涉及複雜的子查詢。我覺得可以切分。如果切分後需要在java應用層做大量的類似于join操作，也要考慮應用層的負載。
關聯優化器：
mysql暫時隻支援嵌套循環查詢。也是就nest loops。
本質上說mysql對所有類型的查詢都以同樣的方式(嵌套循環連接配接)運作。包括子查詢和union。
因為mysql隻支援嵌套循環連接配接，是以它不支援全外連接配接。
是以，對mysql來說，選擇最小表最為基表示非常重要的，mysql基于cost的優化器會選擇最小表，也可以通過STRAIGHT_JOIN關鍵字指定mysql按照sql語句中的順序來做join。
關聯優化器會嘗試所有的關聯順序，來計算成本。如果對于一個有n個表的join，那麼需要檢查n的階乘中關聯順序。如果有10張表，那麼共有3628800中不同的關聯順序！如果表太多，mysql會選擇"貪婪"方式。
排序優化
排序是一項成本非常高的操作，是以從性能的角度上，盡量避免對大量資料進行排序。
資料量小的排序在記憶體中進行的，資料量大要使用磁盤，不過mysql将這個過程統一稱為檔案(filesort)。
在關聯查詢的時候，如果需要排序，有兩種情況：如果order by字句中的所有列都來自第一章表，那麼mysql會在處理第一章表的時候就進行排序，如果是這樣，mysql的執行計劃的extra列會有using filesort的辨別。
如果不是都來自己第一張表，那麼mysql會将每一步join的結果放入臨時表，在所有join執行完之後，在這個臨時表進行排序，如果是這樣，mysql的執行計劃的extra列會有using temporary;using filesort的辨別。
關聯子查詢的局限性
書上提到mysql關聯子查詢局限性。
比如in操作，mysql5.5版本會将外部查詢作為基表，使用nl關聯去loop周遊内部查詢。這樣其實非常不合理，因為一般in的内部的資料是比較少的，外部的資料是比較多的。這樣loop性能肯定很慢。
可是我在實驗的時候發現5.6的版本優化了這個問題。
5.6版本會将in内部的查詢執行為一張臨時表，然後在跟外表關聯的時候，選擇資料量小的表作為基表。我的測試如下：
因為在5.5版本中的關聯子查詢性能很低，是以一般用inner join來改寫in和exist，用left join來改寫not in，not exist。
不過在5.6版本中，我建議先用子查詢，如果有性能問題在優化不遲。
union的局限性：
mysql無法将limit條件從外層下推到内層。
如：
可以優化為：
并行執行：
mysql無法利用多核特性來并行執行查詢。
hash關聯：
mysql不支援hash關聯。
跳躍索引掃描(skip index scan)
不支援。經過測試，在5.6版本支援了。
在同一張表上進行更新的限制：
MySQL不允許對同一張表同時進行查詢和更新。這其實并不是優化器的限制，下面的SQL無法運作，這個SQL嘗試将兩個表中相似行的數量記錄到字段cnt中：
可以通過生成表的形式繞過上面的限制，因為mysql隻會把這個表當作一個臨時表來處理。實際上，這執行了兩個查詢：一個是子查詢中的select語句，另一個是多表關聯update，隻是關聯的表是一個臨時表。子查詢會在update語句打開表之前就完成，是以下面的查詢會正常執行：
hint提示：
DELAYED：針對insert和replace。執行後立即傳回，然後在空閑的時候，資料才會寫入到硬碟。比較适合記錄日志。
STRAIGHT_JOIN：定義關聯順序。
SQL_SMALL_RESULT，SQL_BIG_RESULT：用于查詢，标志結果集的大小，引導排序操作在記憶體或者硬碟中執行。
SQL_BUFFER_RESULT：将查詢結果放入一個臨時表，盡快的釋放表鎖。
SQL_CACHE，SQL_NO_CACHE：是否緩存。
USE INDEX，IGNORE INDEX，FORCE INDEX：強制使用索引，和不适用索引。
優化關聯查詢：盡量確定on的列上有索引。
確定group by和order by隻涉及一張表的列。這樣才可以用到索引。ps(order by好了解，group by自己思考也會明白，如果group by上沒有索引，肯定要全表并排序，或者使用臨時表才能做group by)
mysql内部有可能會自動轉換等價的distinct和 group by文法。
使用者自定義變量：
這一章節是mysql的獨有的功能，不是sql标準，可以在查詢裡使用自定義變量，來實作行号、統計等功能。這裡我沒有細看，羅列了兩篇文章可以參考：