8.優化... 1
8.1 優化概述... 1
8.2 優化SQL語句... 1
8.2.1 優化SELECT語句... 1
8.2.1.1 SELECT語句的速度... 1
8.2.1.2 WHERE子句優化... 1
8.2.1.3 Range優化... 1
8.2.1.4 索引合並(Index Merge)優化... 1
8.2.1.5 引擎Pushdown條件優化... 1
8.2.1.6 索引條件Pushdown優化... 1
8.2.1.7 使用索引擴展... 1
8.2.1.8 IS NULL優化... 1
8.2.1.9 LEFT JOIN和RIGHT JOIN優化... 1
8.2.1.10 嵌套循環join算法... 1
8.2.1.11 嵌套連接優化... 1
8.2.1.12 外連的例子... 1
8.2.1.13 多區間讀優化(Multi-Range Read Optimization)1
8.2.1.14 塊嵌套循環和批量Key訪問連接(Block Nested-Loop and Batched Key Access Joins)1
8.2.1.15 ORDER BY優化... 1
8.2.1.16 GROUP BY優化... 1
8.2.1.17 DISTINCT 優化... 1
8.2.1.18 子查詢優化... 1
8.2.1.19 優化limit語句... 1
8.2.1.20 如何避免表掃描... 1
8.2.2 優化DML語句... 1
8.2.2.1 加快insert語句... 1
8.2.2.2 加快update語句... 1
8.2.2.3 加快delete語句... 1
8.2.3 優化數據庫權限... 1
8.2.4 優化INFORMATION_SCHEMA查詢... 1
8.2.5 其他優化提示... 1
8.3 優化和索引... 1
8.3.1 MySQL如何使用索引... 1
8.3.2 使用Primary Keys1
8.3.3 使用外鍵(Foreign Key)1
8.3.4 列索引(Column Indexes)1
8.3.4.1 Prefix Indexes1
8.3.4.2 全文索引... 1
8.3.4.3 空間索引... 1
8.3.4.4 MEMORY存儲引擎中的索引... 1
8.3.5 復合索引(Mutiple-column Indexes)1
8.3.6 驗證索引使用... 1
8.3.7 Innodb和MyISAM索引統計信息收集... 1
8.3.8 比較btree和hash索引... 1
8.3.8.1 btree索引特點... 1
8.3.8.2 Hash索引特點... 1
8.4 優化數據庫結構... 1
8.4.1 優化數據大小... 1
8.4.1.1 表列... 1
8.4.1.2 行格式(Row Format)1
8.4.1.3 索引... 1
8.4.1.4 Joins1
8.4.1.5 常規(Normalization)1
8.4.2 優化MySQL數據類型... 1
8.4.2.1 數值類型優化... 1
8.4.2.2 優化字符和字符串類型... 1
8.4.2.3 優化blob數據類型... 1
8.4.2.4 使用PROCEDURE ANALYSE. 1
8.4.3 表的優化... 1
8.4.3.1 mysql如何打開和關閉表... 1
8.4.3.2 不要再同一個數據庫中創建多個表... 1
8.4.4 MySQL如何使用內部臨時表... 1
8.5 優化innodb表... 1
8.5.1 優化innodb表存儲引擎的layout1
8.5.2 優化innodb事務管理... 1
8.5.3 優化innodb logging. 1
8.5.4 innodb表的批量數據導入... 1
8.5.5 innodb查詢優化... 1
8.5.6 優化innodb DDL操作... 1
8.5.7 優化innodb磁盤I/O.. 1
8.5.8 優化innodb配置變量... 1
8.5.9 優化innodb多表情況... 1
8.6 優化MyISAM表... 1
8.6.1 優化MyISAM查詢... 1
8.6.2 myisam表批量導入... 1
8.6.3 加快REPAIR TABLE. 1
8.7 MEMORY表的優化... 1
8.8 理解查詢執行計劃... 1
8.8.1 使用explain來優化查詢... 1
8.8.2 EXPLAIN輸出格式... 1
8.8.2.1 EXPLAIN輸出的列... 1
8.8.2.2 EXPLAIN join類型... 1
8.8.2.3 EXPLAIN 擴展信息... 1
8.8.2.4 EXPLAIN 輸出說明... 1
8.8.3 EXPLAIN EXTENDED輸出格式... 1
8.8.4 評估查詢性能... 1
8.8.5 控制查詢優化器... 1
8.8.5.1 控制執行計劃評估... 1
8.8.5.2 控制開關優化(Controlling Switchable Optimizations)1
8.9 Buffering and Caching. 1
8.9.1 innodb的buffer pool1
8.9.1.1 指導... 1
8.9.1.2 內部細節... 1
8.9.1.3 配置選項... 1
8.9.1.4 監控buffer pool1
8.9.2 MyISAM的Key Cache. 1
8.9.2.1 共享key cache訪問... 1
8.9.2.2 多個key cache. 1
8.9.2.3 Midpoint插入策略... 1
8.9.2.4 索引預載入... 1
8.9.2.5 key cache塊大小... 1
8.9.2.6 重新構建key cache. 1
8.9.3 MySQL查詢Cache. 1
8.9.3.1 Query Cache如何工作... 1
8.9.3.2 Query Cache的SELECT選項... 1
8.9.3.3 Query Cache配置... 1
8.9.3.4 Query Cache狀態和維護... 1
8.9.4 緩存prepared語句和存儲程序... 1
8.10 優化鎖操作... 1
8.10.1 內部鎖方法... 1
8.10.1.1 行級別鎖定... 1
8.10.1.2 表級別鎖定... 1
8.10.2 發生表鎖(Table Locking Issues)1
8.10.2.1 使用innodb的性能考慮... 1
8.10.2.2 鎖定的性能問題... 1
8.10.3 並發插入... 1
8.10.4 元數據鎖定... 1
8.10.5 外部鎖定... 1
8.11 優化MySQL服務... 1
8.11.1 系統因素和啟動參數調整... 1
8.11.2 優化服務參數... 1
8.11.3 優化磁盤I/O.. 1
8.11.3.1 使用符號鏈接(Using Symbolic Links)1
8.11.4 優化內存使用... 1
8.11.4.1 MySQL如何使用內存... 1
8.11.4.2 Large Page的支持... 1
8.11.5 優化網絡使用... 1
8.11.5.1 MySQL如何為客戶端連接使用線程... 1
8.11.5.2 Host Cache和DNS查找優化... 1
8.11.6 線程池插件... 1
8.11.6.1 線程池組件和安裝... 1
8.11.6.2 線程池操作... 1
8.11.6.3 調整線程池... 1
8.12 性能測試... 1
參考... 1
數據庫性能依賴於多個因素，在數據庫級別，如表，查詢，配置。硬件級別如cpu，IO。
一般的用戶可以使用現有工具來獲得最好的性能，高級用戶可以通過修改代碼來提高性能。
Ÿ 數據庫級別的優化
s 使用合適表結構，定義合適的字段類型，如果定義的太大，當數據量大的時候會出明顯的空間浪費。
s 使用合適的索引，來提高查詢性能
s 保證每個表使用了合適的引擎，要注意非事務引擎和事務引擎的區別
s 每個表是否有合適的行格式，如，壓縮表讀寫的時候比普通表耗的IO要小。
s 應用程序是否使用合適的鎖策略，innodb存儲引擎的鎖機制提供了很好的並發性。
s 所有用來緩存的內存，大小是否合適。主要是innodb的buffer pool和myisam的key cache和mysql的query cache。
Ÿ 硬件級別優化
任何db上的問題都是硬件造成的問題，而dba是調整應用程序，或者重新配置服務來避免。
s 磁盤查找（Disk seeks），現在的磁盤，平均時間是10ms，也就是說1s理論上能運行100次。如果出現瓶頸為了優化可以把數據放到不同的磁盤。
s 磁盤讀寫（Disk reading and writing），現在的磁盤吞吐量能夠達到10-20MB/s
s CPU
s 內存帶寬，當cpu需要的數據超過cpu cache，那麼主存的帶寬就會變成瓶頸，雖然對大多數系統來說不可能成為瓶頸，但是還是要注意。
Ÿ 可移植性和性能之間平衡
為了性能還可以使用/*! */對優化器進行提示。
主要考慮一下幾點來優化：
Ÿ 為了讓一個select….where…語句速度變快，首先檢查是否可以增加一個索引。按where子句來創建索引可以加快評估，過濾和結果獲取。為了避免索引而導致空間浪費，盡量使用小索引。
可以使用explain語句確定索引是否用戶查詢
Ÿ 隔離和跳轉查詢的任意部分，比如函數，在查詢中調用函數可能一個查詢調用一次，也可能每行調用一次。
Ÿ 最少化表掃描，特別是大表
Ÿ 定期使用ANALYZE TABLE語句，保持統計信息最新。
Ÿ 調整基數，索引技術和參數配置。
Ÿ 避免變化語句，而導致語句難以理解
Ÿ 如果性能問題不能通過簡單的調整解決，那麼就查看查詢內部，研究EXPLAIN語句返回並調整索引，where子句，join子句，等等。
Ÿ 調整mysql用來緩存的內存區的大小。主要是innodb的buffer pool和myisam的key cache。
Ÿ 盡管使用內存語句執行很快，但是也要調整減少內存使用，提高可擴展性。
Ÿ 處理鎖問題。
Where子句出現在delete，insert，update，select語句中，本節以select為例。
注意：Mysql的優化器是不斷在更新的，所以不是所有的優化被寫入文檔
你可能獲取優化語句，但是mysql優化器也會自動去做相同的工作，所以應該去做讓語句更加易懂的工作。一些優化方法：
Ÿ 刪除沒必要的括號
((a AND b) AND c OR (((a AND b) AND (c AND d))))
-> (a AND b AND c) OR (a AND b AND c AND d)
Ÿ 常亮合並
(a<b AND b=c) AND a=5
-> b>5 AND b=c AND a=5
Ÿ 刪除常量條件
(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
-> B=5 OR B=6
Ÿ 索引評估常量表達式只使用一次
Ÿ Count(*)一個簡單的表，沒有where直接來至於myisam和memory表信息。對於not null使用在單表上也是如此。
Ÿ 快速發現不可用的表達式，mysql會快速診斷出一些select語句永遠沒有返回行。
Ÿ 在沒有group by或者聚合函數情況下，having和where合並。
Ÿ 對於每個join的表，簡單的where可以快速的被評估，並且會盡快的跳過行。
Ÿ 所有的常量表都會被先讀入，常量表定義：
s 空的表或者只有一行的表
s 使用了primary key或者unique索引，的所有key進行對常量表達式的比較。
SELECT * FROM t WHERE primary_key=1;
SELECT * FROM t1,t2
WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
Ÿ 最好的join組合是通過嘗試所有join的可能找到的，如果所有的order by，group by列都來自同一個表，那麼這個表會被優先讀入。
Ÿ 如果啟動了SQL_SMALL_RESULT選項，那麼MySQL會使用in-memory臨時表
Ÿ 如果表的索引被查詢，最好的索引將會被使用，除非優化器堅信表掃描性能更好。
Ÿ 當索引覆蓋的時候，mysql可以從索引直接讀入數據，不需要去讀原始記錄。
Ÿ 在行輸入之前，哪些不滿足having子句的會被跳過。
range訪問是使用索引獲取表記錄的子集。
定義簡單索引Range條件如下:
Ÿ 對於BTREE和HASH索引，使用常量來比較key，使用=，<=>,in(),is null或者is not null。
Ÿ 對於BTREE還可以使用>,<,>=,<=,BETWEEN,!=(<>)或者LIKE操作。
Ÿ 對於對個range條件，通過使用or或者and組合成一個range條件
常量值：1.來自查詢，2.來自const或者system表，3.不相關子查詢的結果，4.前面幾種情況的表達式。
range條件的例子：
SELECT * FROM t1
WHERE key_col > 1
AND key_col < 10;
SELECT * FROM t1
WHERE key_col = 1
OR key_col IN (15,18,20);
SELECT * FROM t1
WHERE key_col LIKE 'ab%'
OR key_col BETWEEN 'bar' AND 'foo';
在常量生成階段，某些非常量值可以轉化成常量值。
mysql會視圖每個可能的索引獲取range條件，在獲取range條件時，不滿足的條件會被刪除(設為true)，重疊的range條件會被合並，空的條件會被刪除。
如:key1是索引列，nonkey不是索引
SELECT * FROM t1 WHERE
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
(key1 < 'bar' AND nonkey = 4) OR
(key1 < 'uux' AND key1 > 'z');
1.獲取原始where子句
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
(key1 < 'bar' AND nonkey = 4) OR
(key1 < 'uux' AND key1 > 'z')
2.刪除nonkey=4和key1 like ‘%b’因為不能用於range scan，刪除的做法是把這2個條件設為true，這樣條件就不會被減少。
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR
(key1 < 'bar' AND TRUE) OR
(key1 < 'uux' AND key1 > 'z')
3.折疊總是為true或者false的條件
(key1 LIKE 'abcde%' OR TRUE) 總是為true
(key1 < 'uux' AND key1 > 'z') 總為false
(key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)
刪除沒必要的true和flase條件
(key1 < 'abc') OR (key1 < 'bar')
4.合並條件
(key1 < 'bar')
通常，用於range scan的條件限制比較少，然後mysql執行檢查過濾出滿足range條件的記錄。而不是過濾整個where子句。
range條件提取算法可以處理任意深度的and/or嵌套，並且輸出和range條件在where子句中的位置無關。
目前，mysql還不支持在空間索引上進行range條件合並。因為這個限制，可以使用union2個獨立select語句，然後把空間每個空間謂詞放到各自的select中。
復合索引上的Range條件是簡單索引上的Range條件的擴展。在復合索引下，Range條件是使用key組合。
如：
key_part1 key_part2 key_part3
NULL 1 'abc'
NULL 1 'xyz'
NULL 2 'foo'
1 1 'abc'
1 1 'xyz'
1 2 'abc'
2 1 'aaa'
如：條件key_part1=1定義的區間為：
(1,-inf,-inf) <= (key_part1,key_part2,key_part3) < (1,+inf,+inf)
以下描述說明了在復合索引情況下range條件如何工作：
Ÿ 對於hash索引來說，每個區間表示相同的值，比較運算符只能是 =，<=>，is null,is not null。
Ÿ 對於btree，條件可以使用and連接，每個條件都可以使用>，<,>=,<=,!=,<>,between，like。
如條件：
key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10
轉化為區間後：
('foo',10,-inf) < (key_part1,key_part2,key_part3) < ('foo',+inf,+inf)
Ÿ 如果條件覆蓋的行是多個使用or連接的區間，那麼形成一個條件覆蓋的行是多個區間的並集。如果是用and連接的，那麼會形成一個條件覆蓋的行是多個區間的交集。
如：
(key_part1 = 1 AND key_part2 < 2) OR (key_part1 > 5)
區間是：
(1,-inf) < (key_part1,key_part2) < (1,2)
(5,-inf) < (key_part1,key_part2)
在explain中key_len是最大key前綴。key_len可以說嗎key使用的情況，但是可能和預期的不一樣。
多值等式的情況：
col_name IN(val1, ..., valN)
col_name = val1 OR ... OR col_name = valN
如果col_name等於其中的某一些值，serveral就為true。等式range讀取符合條件的行花費評估如下：
Ÿ 如果在col_name上有唯一索引，行評估為1，因為一個給定值只能有一行。
Ÿ 否則使用索引的統計信息。
使用index dive，優化器為每個range dive，使用range內的行數作為評估。
如col_name in (10,20,30)，三個等式range，然後為每個range做2個dive，生成一個行評估。每對dive為給定值生成評估。
index dive提供了准確的行評估，當比較值越多，評估時間也就越大，使用統計信息評估准確率稍微低一點，但是速度比index dive評估快。
eq_range_index_dive_limit變量，說明可以等式數在變量值內那麼可以使用。如果都要使用index dive那麼把值設為0。如果N個等式一下都使用index dive那麼設置為N+1。
使用ANALYZE TABLE來優化索引統計信息。
索引合並是通過合並多個range掃描為一個。合並可以產生交集，並集或者合並底層掃描的交集。這些合並方法只能合並單個表，不能合並多個表的掃描。
在EXPLAIN輸出，索引合並是在type列中值為index_merge，key列包含了使用的索引，key_len指使用的索引最長key。
如：
SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;
SELECT * FROM tbl_name
WHERE (key1 = 10 OR key2 = 20) AND non_key=30;
SELECT * FROM t1, t2
WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
AND t2.key1=t1.some_col;
SELECT * FROM t1, t2
WHERE t1.key1=1
AND (t2.key1=t1.some_col OR t2.key2=t1.some_col2);
索引合並有以下幾個算法：
Ÿ Using intersect()
Ÿ Using union()
Ÿ Using sort_union()
注意：索引合並優化算法有以下已知的不足：
Ÿ 如果你的查詢有復雜的where，and/or嵌套並且mysql沒有使用最有的計劃，嘗試使用以下的樣式：
(x AND y) OR z = (x OR z) AND (y OR z)
(x OR y) AND z = (x AND z) OR (y AND z)
Ÿ 索引合並不能使用在全文索引。
Ÿ 在5.6.6前，如果range scan在一些key，優化器不會考慮使用索引合並
SELECT * FROM t1 WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;
這個查詢計劃2種可能：
1.在(goodkey1 < 10 OR goodkey2 < 20) 上使用索引合並
2.在badkey < 30上使用range scan
當where子句轉為成了多個Range條件，用不同的key使用and連接，那麼這個算法可用。每個條件是以下：
Ÿ 這個方式，索引有N個部分(索引所有部分都被覆蓋)
key_part1=const1ANDkey_part2=const2... ANDkey_partN=constN
Ÿ 任何range條件在innodb表的primary key
如：
SELECT * FROMinnodb_tableWHEREprimary_key< 10 ANDkey_col1=20;
SELECT * FROM tbl_name
WHERE (key1_part1=1 ANDkey1_part2=2) ANDkey2=2;
索引交集合並訪問算法是同時scan多個索引按scan的接收順序為交叉行順序處理。
如果查詢中使用的列覆蓋了索引，那麼不需要去表上讀其他數據。EXPLAIN輸出中extra為using index。如：
SELECT COUNT(*) FROM t1 WHERE key1=1 AND key2=1;
當所有沒有覆蓋所有列，那麼就要去表上獲取其他需要的列。
若其中一個合並條件是primary key不會另外取表上取行，而是從條件中獲取。
當where子句轉化為多個range條件，並且條件之間使用or連接，那麼算法可用，每個條件以下：
Ÿ 這個方式，索引有N個部分(索引所有部分都被覆蓋)
key_part1=const1ANDkey_part2=const2... ANDkey_partN=constN
Ÿ 任何range條件在innodb表的primary key
Ÿ 索引交集合並算法可用的條件
如：
SELECT * FROM t1 WHERE key1=1 OR key2=2 OR key3=3;
SELECT * FROM innodb_table WHERE (key1=1 AND key2=2) OR
(key3='foo' AND key4='bar') AND key5=5;
當where子句轉化為多個range條件，並且條件之間使用or連接，但是不適用於索引並集合並訪問算法。
如：
SELECT * FROM tbl_name WHERE key_col1 < 10 OR key_col2 < 20;
SELECT * FROM tbl_name
WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col=30;
sort-union算法和union算法不同的是要在返回航之前先對行id進行排序。
引擎pushdown條件，是條件被pushed down到了存儲引擎來評估。這個優化只能在NDB引擎上有用。
對於mysql cluster來說，這個優化可以評估需要在cluster節點和mysql服務之間發送多少不必要的行，並且可以加快查詢速度。
條件pushdown可以用在非索引列和常量比較。
CREATE TABLE t1 (
a INT,
b INT,
KEY(a)
) ENGINE=NDB;
SELECT a, b FROM t1 WHERE b = 10;
mysql> EXPLAIN SELECT a,b FROM t1 WHERE b = 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10
Extra: Using where with pushed condition
但是不能用於以下兩種情況：
SELECT a,b FROM t1 WHERE a = 10;
SELECT a,b FROM t1 WHERE b + 1 = 10;
第一個語句因為a上有索引，所以不能用pushdown，因為用索引訪問更加有效。
第二個語句因為b並不是直接比較，所以也不能使用pushdown
其他可以pushdown的條件：
· column [NOT] LIKE pattern
· column IS [NOT] NULL
· column IN (value_list)
· column BETWEEN constant1 AND constant2
pattern必須是包含了模式匹配的字符串
value_list是常量或者字符常量
constant1和constant2必須是常量或者字符
引擎條件pushdown默認是啟動的，要disable掉，需要設置optimizer_switch系統變量
[mysqld]
optimizer_switch=engine_condition_pushdown=off
或者在啟動的時候
SET optimizer_switch='engine_condition_pushdown=off';
限制：引擎條件pushdown限制：
Ÿ 條件pushdown只支持NDB
Ÿ 列只能和常量比較或者常量表達式比較
Ÿ BLOB，TEXT類型不能用於列比較
Ÿ 如果是比較字符串必須使用相同的排序規則
Ÿ join不能直接支持，有可能的時候涉及到多個表會被分開push。
索引條件pushdown（icp）是mysql通過索引從表上獲取數據的另一種優化。
如果沒有使用icp存儲引擎比那裡索引來定位表中的行並且返回給mysql。
如果使用icp，若where的部分條件可以使用索引評估，mysql服務把這部分條件push到存儲引擎，然後存儲引擎通過索引項來評估這個push下來的條件。並且只有滿足的行才能被讀取。
icp可以減少存儲過程必須訪問基表的次數，和訪問存儲引擎的次數。
當索引條件pushdown不可用，索引掃描是如何處理的：
1.獲取下一行，先讀取索引組，然後使用索引組定位到表中的行。
2.然後測試where中的行，根據test結果，接受或者拒絕。
當索引條件pushdown可用，過程：
1.獲取下一行索引組。
2.test應用到這個表的部分where條件，通過索引給的列進行test，如果條件不滿足就獲取一行的索引組。
3.如果條件滿足使用索引組定位並且讀取表行。
4.測試where中剩下的部分。根據測試結果決定是接受或者拒絕。
當使用索引條件pushdown的時候，在explain輸出的時候extra列顯示Using index condition，不會顯示index only因為還會去表上讀記錄。
假設有個聯系方式的表，裡面有索引index(zipcode,lastname,firstname)。當你執行一下語句的時候：
SELECT * FROM people
WHERE zipcode='95054'
AND lastname LIKE '%etrunia%'
AND address LIKE '%Main Street%';
MySQL會通過zipcode掃描people表，lastname並不能夠使用來查找索引，所以沒有索引條件pushdown，查詢會獲取說有zipcode為95054的所有記錄。
使用了索引條件pushdown，mysql會先檢查lastname，避免獲取不能匹配lastname的記錄。
索引條件pushdown默認啟動，可以通過optimizer_switch系統變量設置index_condition_pushdown標記，來設置是否啟動。
參考閱讀：淺析index condition pushdown
innodb自動會為每個secondary索引擴展，為secondary key後面加上primary key。
CREATE TABLE t1 (
i1 INT NOT NULL DEFAULT 0,
i2 INT NOT NULL DEFAULT 0,
d DATE DEFAULT NULL,
PRIMARY KEY (i1, i2),
INDEX k_d (d)
) ENGINE = InnoDB;
primary key在i1,i2。也定義了索引k_d在列d上，innodb內部擴展之後實際索引列為d,i1,i2。
在mysql 5.6.9之前，優化器選擇索引的時候是不會去關心secondary上primary key列。
在mysql 5.6.9之後，被擴展的部分也會被考慮到，並且會產生很有效的查詢計劃和性能。
使用ref,range，index_merge索引訪問都可以使用擴展的secondary索引。
INSERT INTO t1 VALUES
(1, 1, '1998-01-01'), (1, 2, '1999-01-01'),
(1, 3, '2000-01-01'), (1, 4, '2001-01-01'),
(1, 5, '2002-01-01'), (2, 1, '1998-01-01'),
(2, 2, '1999-01-01'), (2, 3, '2000-01-01'),
(2, 4, '2001-01-01'), (2, 5, '2002-01-01'),
(3, 1, '1998-01-01'), (3, 2, '1999-01-01'),
(3, 3, '2000-01-01'), (3, 4, '2001-01-01'),
(3, 5, '2002-01-01'), (4, 1, '1998-01-01'),
(4, 2, '1999-01-01'), (4, 3, '2000-01-01'),
(4, 4, '2001-01-01'), (4, 5, '2002-01-01'),
(5, 1, '1998-01-01'), (5, 2, '1999-01-01'),
(5, 3, '2000-01-01'), (5, 4, '2001-01-01'),
(5, 5, '2002-01-01');
在不考慮擴展索引的情況下，因為k_d只被當做是d：
mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ref
possible_keys: PRIMARY,k_d
key: k_d
key_len: 4
ref: const
rows: 5
Extra: Using where; Using index
在考慮擴展擴展索引的情況下，k_d被當做是d,i1,i2所以查詢可以使用，d,i1
mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ref
possible_keys: PRIMARY,k_d
key: k_d
key_len: 8
ref: const,const
rows: 1
Extra: Using index
2個語句都是使用了k_d索引，但是explain語句輸出切實不同：
Ÿ key_len從4字節變成8字節，說明lookup使用了d,i1,而不是d。
Ÿ ref從const變成了const，const，索引是2個key而不是1個。
Ÿ rows從5變成了1，說明讀取的行更加少。
Ÿ extra值從using where;using index變成了using indexs說明只需要從index上獲取數據就可以滿足
是否考慮擴展索引，也可以沖show status上看出不同
FLUSH TABLE t1;
FLUSH STATUS;
SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';
SHOW STATUS LIKE 'handler_read%'
flush table，flush status刷新表cache和清理status計數。
沒有索引擴展，show status輸出
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 5 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
使用索引擴展，show status過程產生結果。Handler_read_next值從5到1，說明了索引擴展更加有效。
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 1 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
optimizer_switch系統變量use_index_extensions標記。默認是啟動的，可以通過禁用功能來檢查性能是否有提升。
SET optimizer_switch = 'use_index_extensions=off';
索引擴展搜到key個數的限制（16個）和可以長度的限制（3072字節）。
在MySQL中col_name is null 和col_name=constant_value使用相同的優化，mysql中可以使用is null來查找null值。
如果where子句中，對一個not null列使用了is null那麼就會被優化掉。這個優化就不可能會在有可能為null的列中出現，比如left join的右側表。
MySQL也能優化col_name=exp or col_name is null，當優化器使用的時候explain會顯示ref_or_null。
ref_or_null，先按給的值讀，然後讀值為null的。
注意，優化器只能處理一個is null，如下面情況，只能處理t1.a=t2.a and t2.a is null不能處理b上面的
SELECT * FROM t1, t2
WHERE (t1.a=t2.a AND t2.a IS NULL)
OR (t1.b=t2.b AND t2.b IS NULL);
Mysql實現A LEIFT JOIN B join_condition如下：
Ÿ 表B依賴於表A和表A依賴的表。
Ÿ 表A依賴於除了表B之外使用在LEFT JOIN上的表
Ÿ LEFT JOIN條件用來決定如何從表B上獲取數據
Ÿ 所有標准的join優化都會被執行，除了表一定要讀完依賴的表之後才讀。如果出現循環的依賴關系，mysql就會報錯。
Ÿ 所有的標准where都會被執行
Ÿ 如果A中有一行滿足where條件，但是沒有對應的B中的記錄，那麼就生產一個全是null的B的記錄
Ÿ 如果使用left join是為了發現哪些行不再另外一些表中，可以做以下測試，在where中使用col_name is null，col_name被定義為not null的。
RIGHT JOIN和LEFT JOIN類似，只是角色換了一下。
優化器會計算join的順序，但是如果使用left join或者straight join會幫助優化器更快的工作，因為已經強制了join順序，如果使用一下類型，mysql會全掃描b，因為left join強制要求在d之前讀取
SELECT *
FROM a JOIN b LEFT JOIN c ON (c.key=a.key)
LEFT JOIN d ON (d.key=a.key)
WHERE b.key=d.key;
可以通過調換 a和b的順序來調整
SELECT *
FROM b JOIN a LEFT JOIN c ON (c.key=a.key)
LEFT JOIN d ON (d.key=a.key)
WHERE b.key=d.key;
對於生產null來說where 條件總是錯誤的，那麼left join會轉化為 inner join
SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;
理論上來說轉化為inner join是沒問題的
SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;
一個簡單的嵌套循環連接算法，是從第一個表讀入然後一行一行的傳入嵌套內表的。
假設有表t1,t2,t3
Table Join Type
t1 range
t2 ref
t3 ALL
join執行類似於：
for each row in t1 matching range {
for each row in t2 matching reference key {
for each row in t3 {
if row satisfies join conditions,
send to client
}
}
}
因為嵌套循環連接每次傳遞一行到內表中，通常要在內表處理好多次。
塊嵌套循環連接算法，會對外表的行進行緩存來減少在內表的讀取時間。以數量級方式，減少了內表表讀取次數。
MySQL使用連接緩存(join buffer)有一下幾個條件：
Ÿ join_buffer_size系統變量決定了每個join buffer的大小
Ÿ 連接緩存可以用於ALL,index,range連接類型
Ÿ 每個join都有一個緩存，所以一個查詢可能有多個緩存。
Ÿ 連接緩存不會為第一個非常量表，甚至是類型為ALL或者index。
Ÿ 連接緩存在執行連接的時候被分配，然後在查詢完成後被釋放。
Ÿ 只有join感興趣的列會被保存在連接緩存，並不會保存所有的行
for each row in t1 matching range {
for each row in t2 matching reference key {
store used columns from t1, t2 in join buffer
if buffer is full {
for each row in t3 {
for each t1, t2 combination in join buffer {
if row satisfies join conditions,
send to client
}
}
empty buffer
}
}
}
if buffer is not empty {
for each row in t3 {
for each t1, t2 combination in join buffer {
if row satisfies join conditions,
send to client
}
}
}
如果t1,t2保存在連接緩存的組合大小為S，組合個數為C，那麼t3掃描次數是：
(S*C)/join_buffer_size + 1
t3的掃描次數會隨著join_buffer_si增加而減少，當連接緩存增加到了可以包含所有記錄，在大也不能減少掃描次數。
table_factor在標准sql上被擴展，不接受括號裡面都是表的方式。
SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
可以轉化為
SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
在mysql中 inner join和cross join是相等的，但是在標准的SQL是不相等的。
通常只有inner join操作括號可以忽略，對於out join括號不能去掉或者變化，都有可能帶來結果的不同。
准確的說是在left join的右邊不能忽略括號，在right join的左邊不能忽略括號。
外連接的算法和內連接的算法是相似的
SELECT * FROM T1 INNER JOIN T2 ON P1(T1,T2)
INNER JOIN T3 ON P2(T2,T3)
WHERE P(T1,T2,T3).
如果P(T1,T2,T3)可以做一下拆分：
P(T1,T2,T2) = C1(T1) AND C2(T2) AND C3(T3).
算法會變成：
(T2 LEFT JOIN T3 ON P2(T2,T3))
FOR each row t1 in T1 such that C1(t1) {
FOR each row t2 in T2 such that P1(t1,t2) AND C2(t2) {
FOR each row t3 in T3 such that P2(t2,t3) AND C3(t3) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
}
}
}
會發現C1(T1) ， C2(T2) ， C3(T3)被push出了循環，如果C1(T1)是很好的過濾條件，那麼會減少很多行到內表。
out join也類似。
具體看：http://dev.mysql.com/doc/refman/5.6/en/nested-join-optimization.html
具體看：http://dev.mysql.com/doc/refman/5.6/en/outer-join-simplification.html
在secondary索引上使用range掃描會導致很多磁盤隨機訪問。Mysql通過MRR優化來減少磁盤隨機讀寫：
1.先掃描所有手機相關的行
2.對key進行排序，然後通過primary key順序從表上獲取數據。
MRR算法是把隨機讀寫轉成順序讀寫，來降低隨機訪問次數。
MRR優化提供了以下好處：
Ÿ MRR讓數據根據索引組，順序訪問而不是隨機訪問。服務獲取一組符合查詢條件的索引組，根據ROWID排序(默認primary key)，然後根據這個順序獲取數據。
Ÿ MMR可以批量處理key訪問請求，通過索引組訪問數據行。如區間索引掃描，等式join以索引為join屬性。
以下場景演示了使用MRR優化的好處：
場景1：MRR被用來innodb和myisam表的索引區間掃描和等式join
1.部分索引組被存到buffer
2.這些組以rowid被排序
3.數據組根據排序好的索引組順序訪問。
場景2：MRR在NDB表上進行索引區間掃描和等式join
1.部分range被存在提交查詢節點的buffer中。
2.range被發送到需要執行的節點上
3.被訪問的行被打包並發送到查詢提交的節點
4.接受的包內的數據行被寫入到buffer
5.數據行從buffer中被讀取
當使用MMR，explain中的extra列為using MMR。
如果innodb和myisam表不需要訪問表上的行，那麼就不會使用MRR。因為數據可以從索引組上獲取，沒必要在訪問表。
使用MRR的例子，index(key_part1,key_part2):
SELECT * FROM t
WHERE key_part1 >= 1000 AND key_part1 < 2000
AND key_part2 = 10000;
若不是用MRR，會掃描1000到2000的所有記錄不管key_part2的值
若使用MRR，掃描會被分為多個range(1000,1001,…,1999)，每個單獨的值都會去找key_part2=10000,若有很多組都不是10000，那麼MRR只需要讀取很少的行。
optimizer_switch系統變量中的mrr標記控制MRR優化是否啟動。mrr_cost_based標記控制是否根據cost決定是否使用MRR，默認2個都是on。
對於MRR，read_rnd_buffer_size控制buffer的大小，read_rnd_buffer_size決定了一趟能夠處理多少range。
在MySQL5.6，join buffer可以用來執行 inner join，out join和semi join。
join buffer的管理是很簡潔有效的：對於NULL值buffer並不會為它分配跟多的字節，最小化分配給varchar類型的字節數。
代碼支持2個類型的buffer，常規的和增量的，假設buffer B1用於t1和t2的join，join結果和t3 join使用B2:
Ÿ 如果是常規join buffer，B2是常規join buffer，那麼每一行都包含B1的r1和t2感興趣列的r2的組合。
Ÿ 如果是增量join buffer，只包含下一個join生產的。如果B2是增量buffer，B2只包含了t2的r2和指向r1的指針。
增量join buffer總是以增量的方式對應到之間的join buffer，所以第一個join操作總是常規的。如例子中，B1總是常規join buffer。
增量buffer中用來join操作的每一行都只包含來自被join表的被感興趣的列。通過引用的方式。只要這些行可以匹配r，那麼一些增量buffer中的行可以指向同一個行r。
增量buffer可以減少復制之前join產生的列，這樣可以減少空間。因為減少了復制，也可以為處理節省時間。
optimizer_switch中的block_nested_loop和batched_key_access標記控制優化器使用這2個算法。默認block_nested_loop是啟動的，batched_key_access是關閉的。
變量optimizer_join_cache_level控制了join buffer管理。
MySQL 5.6之後，塊嵌套循環算法才支持外連接和半連接。
如果使用外連接，每一個有第二個操作產生的行和join buffer中的行匹配，如果能匹配到一個擴展行就產生，通過下一個join進一步擴展。剩下的沒有被擴展的行，都以null來擴展。
如果使用BNL，explain輸出中的extra包含using join buffer並且type職位all，index，range。
批量key訪問(BKA)被應用在，在訪問另外一個表時有索引可用。
1.BKA使用join buffer保存由join的第一個操作產生的感興趣的數據。
2.然後BKA算法，生產key來訪問被連接的表，並批量提交到數據庫index查找，使用MRR接口提交keys。
3.提交keys之後，MRR使用最佳的方式來獲取行。
MRR接口有2個應用場景：
場景1：應用於傳統的基於磁盤的存儲引擎(innodb,myisam)，對於這些引擎join buffer中keys是一次性提交到MRR，MRR通過key找到rowid，通過rowid來獲取數據
場景2：用於遠程存儲引擎(NDB)，來自join buffer上的部分key，從SQL NODE發送到DATA NODE，然後SQL NODE會收到通過相關關系匹配的行組合。然後使用這些行組合匹配出新行。
然後在發送新key，知道發完為止。
如果使用了BKA，explain的extra會顯示using join buffer(batch key access)並且type為ref或者eq_ref。
當MySQL使用索引能夠滿足ORDER BY的時候就不需要做任何處理。
可以使用索引解決排序的例子：
SELECT * FROM t1
ORDER BY key_part1,key_part2,... ;
SELECT * FROM t1
WHERE key_part1 = constant
ORDER BY key_part2;
SELECT * FROM t1
ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1
WHERE key_part1 = 1
ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1
WHERE key_part1 > constant
ORDER BY key_part1 ASC;
SELECT * FROM t1
WHERE key_part1 < constant
ORDER BY key_part1 DESC;
SELECT * FROM t1
WHERE key_part1 = constant1 AND key_part2 > constant2
ORDER BY key_part2;
因為索引本來就是排序的按 key_part1, key_part2, key_part3…key_partn如果違反了都需要另外排序。
可以使用explain語句查看mysql是否使用索引解決了排序問題，如果extra列有using filesort索引沒有解決這個問題。
mysql的filesort有2個算法，一個算法只是對需要order by的字段進行排序，另外一個修改後的方法是對所有字段進行排序。
通常優化器使用修改後的方法，除非有BLOB或者TEXT字段，不管什麼算法，排序緩存都是sort_buffer_size。
原始的filesort算法如下：
1.讀取匹配where條件的數據
2.對於每行，存排序的key和rowid到sort buffer
3.如果key和rowid都放在sort buffer中，就不需要創建臨時文件。當sort buffer滿了，運行快速排序，然後寫入到臨時文件。保留sorted block指針。
4.重復處理直到所有的行都被讀取
5.執行merge buffer到另外一個文件，直到所有的在第一個文件中的block在了第二個文件中。
6.一直merge buffer直到剩下2個block
7.最後一次merge，只寫入rowid到結果表
8.根據排序結果中的rowid順序讀取數據。（手冊中還提到了一個優化方案，但是我不認為能起到優化作用）。
這個方法的一個問題是需要讀2次數據，一次是讀排序列，一次是排好之後讀取數據輸出，而且都是隨機訪問。
修改後的filesort算法避免兩次讀取，會記錄排序規則，而不是rowid，也會記錄被查詢應用的列。算法如下：
1.讀取匹配where的數據
2.記錄排序列和查詢用到的列
3.當buffer滿，對排序列排序，放到臨時文件。
4.當合並完臨時文件之後，直接輸出。
修改後的方法，列長於原來的方法。很有可能會產生大量IO，讓排序變得很慢。為了避免這個問題，優化器會所有讀取列的長度小於max_length_for_sort_data系統變量，才會選擇修改後的算法。
當filesort完成，explain輸出中extra會有using filesort，優化器跟蹤輸出中filesort_summary塊：
"filesort_summary": {
"rows": 100,
"examined_rows": 100,
"number_of_tmp_files": 0,
"sort_buffer_size": 25192,
"sort_mode": "<sort_key, additional_fields>"
}
其中sort mode就說了算法：
<sort_key,rowid>表示原始的算法
<sort_key,addtitional_filed>表示是修改後的算法
為了提高排序速度，可以檢查是否可以使用索引，如果不能使用：
1.增加sort_buffer_size的大小
2.增加read_rnd_buffer_size的大小
3.通過表設計減少空間占用
4.修改tmpdir目錄指向專用文件系統
如果order by沒有使用索引，但是有limit子句，那麼優化器可能可以避免合並臨時文件，直接在內存中排序。
最簡單的方法，是GROUP BY子句然後掃描整個表，創建臨時表，臨時表中的數據是按分組順序的(按分組字段順序的)。
然後使用臨時表發現各個分組，然後應用聚合函數。如果可以使用索引那麼就可以避免創建臨時表。
GROUP BY使用索引的先決條件是，group by列必須來自於同一個索引。而且group by順序一定要和索引key順序一樣。
臨時表的處理方法是否可以被索引訪問替代取決於查詢中使用了哪部分索引。
通過索引訪問有2種方法：1.group和range謂詞一起使用，2.先做range掃描，然後做group。
最有效的方法處理是通過索引直接獲取group列。這樣可以直接在索引上查找分組不需要考慮key是否滿足where子句。
這樣的訪問只會考慮索引的一部分，所以稱為松散的索引掃描(loose index scan)。
如果沒有where子句，那麼松散掃描直接讀取需要的key，比讀全部key要小。
如果where子句包含range謂詞，松散掃描查找每個分組的第一個key，然後讀取最好有可能的key數量。需要滿足一下幾點：
Ÿ 在單個表上查詢
Ÿ group by中的列，一定要和索引前綴匹配，如index(c1,c2,c3)，那麼group by c1,c2可以適用，group by c2就不適用。
Ÿ 在select列表中可以使用的聚合函數是min，max而且只能用於同一列。這個列必須是在索引裡面，並且在group by字段之後的，如 group by c1,c2, max(c3)。
Ÿ 所有的其他部分，除了group by的之外，必須是常量。也就是說必須等式常量來引用他們。除了max，min之外。
Ÿ 對於索引列，所有列值必須被索引，不能前綴。比如c1 varchar(20),index(c1(10))這樣就不能使用松散索引掃描。
可以使用松散索引掃描的例子：
index(c1,c2,c3)，表t1(t1,t2,t3,t4)
SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;
不能使用的例子：
Ÿ 聚合函數不是max,min
SELECT c1, SUM(c2) FROM t1 GROUP BY c1;
Ÿ gourp by中的列不是索引前綴
SELECT c1, c2 FROM t1 GROUP BY c2, c3;
Ÿ 查詢應用了部分key，是來自group by字段的後面，但是沒有使用等式比較常量，如果使用了where c3=const那麼就可以適用
SELECT c1, c3 FROM t1 GROUP BY c1, c2;
松散索引掃描也可以使用在其他形式的聚合函數：
Ÿ AVG(DISTINCT),SUM(DISTINCT),COUNT(DISTINCT)支持。
Ÿ 沒有group by或者 distinct子句在查詢中
Ÿ 前面提到的限制依然適用
可以適用的情況：
SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;
SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;
不能適用的情況：
SELECT DISTINCT COUNT(DISTINCT c1) FROM t1;
SELECT COUNT(DISTINCT c1) FROM t1 GROUP BY c1;
緊湊索引掃描要不是全索引掃描要不是區間索引掃描。
當松散索引掃描不能使用的時候，依然可以避免創建臨時表。如果where子句中有range條件，那麼只讀取滿足條件的key。
否則執行全索引掃描。因為算法讀取所有滿足range條件的key，或者如果沒有條件掃描整個索引，我們成為緊湊索引掃描。
使用緊湊索引掃描，分組操作只有在所有key照完之後執行。
算法在使用等式比較所有查詢引用的列生效，只有等式常量能夠填上查詢key的間隙，才有可能形成索引的前綴，使用索引前綴來進行索引查找。
這樣mysql可以避免額外的排序操作直接可以從索引中順序獲取。
假設index(c1,c2,c3)在表table(c1,c2,c3,c4)，下面的查詢不支持松散索引掃描，但是支持緊湊索引掃描
Ÿ 雖然有空隙但是已經被where c2=’a’填補
SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;
Ÿ 雖然group by不是和索引第一位匹配，但是where中提供了和常量的比較
SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;
distinct配合order by 很多時候都需要創建臨時表。distinct是gourp的特例。所以在group上的優化也可以應用到distinct上。
如果使用了limit配合distinct，mysql會在到達limit的時候馬上停止。
如果查詢並沒有使用全部的列，Mysql就會在找到一個匹配之後停止掃描。
mysql查詢優化，有不同的策略來評估子查詢：
對於in或者any：
Ÿ semi-join
Ÿ 物化
Ÿ exists策略
對於not in子查詢：
Ÿ 物化
Ÿ exists策略
mysql 5.6.5之後優化器使用semi-join策略提高子查詢性能。
對於inner join 2張表，join返回和一行匹配的所有行。但是對於一些查詢，只需要知道是不是有匹配，不需要關心能匹配多少。
假設有2個表，class和roster，為了顯示有學生報名的class：
SELECT class.class_num, class.class_name
FROM class INNER JOIN roster
WHERE class.class_num = roster.class_num;
這樣出現很多重復的class，可以用distinct去重復，但是效率不怎麼樣。
也可以使用以下獲取：
SELECT class_num, class_name
FROM class
WHERE class_num IN (SELECT class_num FROM roster);
這樣的語句，查詢可以用semi-join執行，這個操作只為每個class中的行返回一個roster的實例。
在mysql中，子查詢只有滿足這些條件才能使用semi-join：
Ÿ 必須是in或者any子查詢在where或on子句中。
Ÿ 必須是一個select，沒有union
Ÿ 不能包含group by，having或者聚合函數
Ÿ 不能有使用order by配合limit
Ÿ join表個數不能多於join限制
子查詢可以是關聯的也可以是非關聯的。distinct和limit都是允許的，limit和order by一起用是不運行的。
若子查詢滿足上面那段條件，mysql會把它轉化成semi-join，根據cost有一下策略：
Ÿ 把子查詢傳化為join，或者使用table pullout，然後在子查詢表和外表之間使用inner join。
Ÿ 去重復，把semi join當做是join，並使用臨時表去除重復記錄
Ÿ firstmatch，當掃描內表，對給定值進行分組，然後在分組結果中選一個返回
Ÿ loosescan，使用索引掃描子查詢表，然後從子查詢執行結果的分組中選出一個值
Ÿ 物化，把子查詢物化到臨時表，然後使用索引和臨時表做join，索引可以用來去重復，或者join時用於lookup，如果沒有那麼就可能要表掃描了。
除了去除重復，其他的都可以通過設置optimizer_switch的semijoin標記來控制。如果semijoin為on，可以單獨通過firstmatch，loosescan，materialization。
控制firstmatch，loosescan，物化。這些標記默認為on。
semi-join策略會在explain中體現：
Ÿ explain extended，show warnings顯示了重寫的語句。如果子查詢被轉化為semi-join，會發現子查詢的謂詞合並到了外查詢的where中。
Ÿ 對於去重復，在extra劣種會出現start temporary，end temporary。
Ÿ firstmatch(tbl_name)在extra列中說明join shortcutting。
Ÿ loosescan(m..n)在extra列中說明LooseScan策略，m..n是key部分。
Ÿ 如果select_type為MATERIALIZED並且table為<subqueryN>說明。說明臨時表用於子查詢物化。
mysql 5.6.5，優化器使用子查詢物化策略，可以讓子查詢更加有效的被處理。
如果物化沒有被使用，優化器有時候會重寫非關聯子查詢變成關聯子查詢。如：
SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);
變成
SELECT * FROM t1
WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_condition AND t1.a=t2.b);
子查詢物化使用臨時表，避免子查詢執行多次。物化可以提高查詢執行，臨時表生產一搬都會在內存中。
物化產生的臨時表一般在內存中，如果太大會被放到磁盤。
是否使用子查詢物化，由optimizer_switch中的materialization標記決定，當謂詞滿足任一條件都可以使用：
Ÿ 謂詞是以下形式，並且oe_i或者ie_i沒有null
(oe_1, oe_2, ..., oe_N) [NOT] IN (SELECT ie_1, i_2, ..., ie_N ...)
Ÿ 在這個方式下oe,ie可以為null
oe [NOT] IN (SELECT ie ...)
Ÿ 謂詞是in或者not in結果為null和false一樣
通過查看explain可以說明子查詢是否物化。select_type會從DEPENDENT SUBQUERY變成SUBQUERY，這個說明會為外表每一行都執行一次。
物化可以讓子查詢只運行一次。另外explain extended，show warnings會包含，materialize materialize和materialize-subquery。
mysql 5.6.3優化器處理derived table更加有效：
Ÿ 物化from中的子查詢，會被延遲直到需要使用的時候
s 之前，from子句會被explain select的時候被物化，因為盡管是獲取執行計劃，但是還是有部分會被運行。這種情況下物化就不會發生，explain也更加快。
s 對於不explain的語句，延遲物化，有時候就不再需要物化。
Ÿ 在查詢執行的時候，優化器可能會對derived table加索引加快行的獲取
以下explain子句：
EXPLAIN SELECT * FROM (SELECT * FROM t1);
優化器延遲子句物化，知道需要使用的時候再物化。，這個情況下查詢時不會被執行的，所以不需要物化。
在查詢執行的情況下，延遲子查詢物化可以讓優化器不去物化它
SELECT * FROM t1
JOIN (SELECT t2.f1 FROM t2) AS derived_t2 ON t1.f2=derived_t2.f1
WHERE t1.f1 > 0;
如果優化器先處理t1,並且where生成的結果為空，join生成的一定是空，所以沒必要物化。
當子查詢需要物化的時候，優化器可能會增加索引來加快訪問速度。如果索引可以ref訪問表，那麼可以在查詢執行的時候會大大的減少數據讀取，如下：
SELECT * FROM t1
JOIN (SELECT * FROM t2) AS derived_t2 ON t1.f1=derived_t2.f1;
優化器如果在derived_t2的f1上增加了索引，如果允許ref訪問。增加索引之後優化器可以把derived表當成有索引的用戶表。
創建索引會帶來消耗，但是和沒有索引執行相比，創建索引的符合可以忽略。如果ref訪問可能比其他訪問開銷更大，那麼優化器就不會創建索引。
某些優化對於in或者any子查詢是適用的。這裡要討論不適用的情況。
outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)
mysql評估是從外到內的，先獲取外部表達式的值，然後執行子查詢獲取行。
一個很用的優化是轉化子查詢，把條件pushdown到了子查詢的where中
EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)
轉化之後，mysql可以把pushdown等式，來限制要檢查的行數。
(oe_1, ..., oe_N) IN
(SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)
轉化為
EXISTS (SELECT 1 FROM ... WHERE subquery_where
AND oe_1 = ie_1
AND ...
AND oe_N = ie_N)
轉化其實是有限制的，只有在忽略null的情況下才能使用，只有一下2個都成立才能使用pushdown：
Ÿ outer_expr和inner_expr不能為null
Ÿ 不需要區分null和false
如果任意一個條件不滿足，優化就變得很復雜。
當outer_expr不為null但是子查詢可能不會生成outer_expr=inner_expr記錄，outer_expr in(select…)評估：
Ÿ null，如果生成的任何記錄，inner_expr為null
Ÿ false，如果生成了一個非null值或沒有產生
在這個情況下，查找outer_expr=inner_expr就不可用了，在嗎，沒有找打行的情況下，還需要找inner_expr is null。
EXISTS (SELECT 1 FROM ... WHERE subquery_where AND
(outer_expr=inner_expr OR inner_expr IS NULL))
這時需要訪問is null條件，這也就是為什麼會有ref_or_null的訪問方式。（這樣轉化的話是否有語義上的問題）
如果type為index_subquery或者index_subquery也有or null但是explain沒有輸出，要使用explain extended，show warnings的輸出注意checking null。
or…is null讓查詢執行，稍微更加復雜一點。
當outer_expr可能為null那麼就會更加復雜，null in(select…)評估：
Ÿ null，如果select不產生任何行
Ÿ flase，如果select不產生行
所以為了正確的評估需要檢查，子查詢是否產生行。要先判斷select會不會產生行，所以outer_expr=inner_expr不能被pushdown到子查詢。
其實是要更具outer_expr的不同來不同處理。
如果outer_expr為null，那麼就要判斷select是否產生數據，這個時候就要執行select語句，不帶pushdown條件。
NULL IN (SELECT inner_expr FROM ... WHERE subquery_where)
如果outer_expr不為null，那麼就有必要做轉化pushdown條件。
outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)
轉化為
EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)
如果不轉化那麼速度就會很慢，為了解決這2難的問題，由一個觸發函數來決定是否觸發pushdown條件
outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)
轉化為
EXISTS (SELECT 1 FROM ... WHERE subquery_where
AND trigcond(outer_expr=inner_expr))
如果為：
(oe_1, ..., oe_N) IN (SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)
那麼轉化為
EXISTS (SELECT 1 FROM ... WHERE subquery_where
AND trigcond(oe_1=ie_1)
AND ...
AND trigcond(oe_N=ie_N)
)
每個trigcond是一個特殊的函數，評估如下：如果oe_i不為null，則為X，如果為null則為true。
對trigcond(x)，等式的觸發有一下優化：
Ÿ trigcond(x=y[or y is null])可以使用ref，eq_ref,ref_or_null。
Ÿ trigcond(x=y)可以訪問unique_subquery或者index_subquery。
Ÿ 如果子查詢是join多個表，觸發條件會盡快的檢查。
當觸發條件被調成off，必須要有個fallback策略，執行一個全表掃描。
null in select()可能會導致全表掃描。這個就是獲取正確結果需要的花費。
對於多個表的子查詢，執行null in select()可能會很慢，因為優化器不對外面為null的進行優化。對於外部表達式，如果null不出現，那麼就不會影響性能。
為了優化器更好的執行，有幾個tips：
Ÿ 如果可以盡量吧列定義成not null
Ÿ 如果不會去取分false和null的區別，可以使用not null 來避免
outer_expr IN (SELECT inner_expr FROM ...)
轉化為
(outer_expr IS NOT NULL) AND (outer_expr IN (SELECT inner_expr FROM ...))
subquery_materialization_cost_based會控制是否對in語句使用exists策略。
如果要指定查詢結果的行數，使用limit子句來限制結果集行數。
mysql有些時候會對有limit，沒有having子句優化：
Ÿ 如果limit值限制一些行，mysql在這個時候可能會用索引來代替表掃描。
Ÿ 如果使用order by配合limit，mysql會在sort直到結果出現limit限制的行數。
Ÿ 如果order by是根據索引順序，那麼會很快，如果要用filesort，那麼會選擇所有的行，然後排序，直到找到前幾行。之後排序就不會再運行。
有個現象加不加limit可能會導致order by順序不同。有一下幾個方面：
Ÿ 如果limit帶distinct，會在到達limit行數之後馬上停止。
Ÿ 某些情況，group by可以通過讀取key的順序，這個時候，limit可以限制不用計算不必要的group by值。
Ÿ 當客戶端收到指定行數的時候，會中斷查詢，除非使用了SQL_CALC_FOUND_ROWS。
Ÿ limit 0直接返回空集，可以用來檢查查詢是否合法。
Ÿ 當服務使用臨時表，會使用limit子句來計算需要多少空間。
在有相同排序值的時候，順序是不確定的。
mysql> SELECT * FROM ratings ORDER BY category;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
| 1 | 1 | 4.5 |
| 5 | 1 | 3.2 |
| 3 | 2 | 3.7 |
| 4 | 2 | 3.5 |
| 6 | 2 | 3.5 |
| 2 | 3 | 5.0 |
| 7 | 3 | 2.7 |
+----+----------+--------+
使用了limit
mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
| 1 | 1 | 4.5 |
| 5 | 1 | 3.2 |
| 4 | 2 | 3.5 |
| 3 | 2 | 3.7 |
| 6 | 2 | 3.5 |
+----+----------+--------+
若要確定順序，最好加個唯一列：
mysql> SELECT * FROM ratings ORDER BY category, id;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
| 1 | 1 | 4.5 |
| 5 | 1 | 3.2 |
| 3 | 2 | 3.7 |
| 4 | 2 | 3.5 |
| 6 | 2 | 3.5 |
| 2 | 3 | 5.0 |
| 7 | 3 | 2.7 |
+----+----------+--------+
mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
| 1 | 1 | 4.5 |
| 5 | 1 | 3.2 |
| 3 | 2 | 3.7 |
| 4 | 2 | 3.5 |
| 6 | 2 | 3.5 |
+----+----------+--------+
如果排序的個數N剛好能在sort buffer裡面，那麼服務就可以避免執行文件合並和並且把sort buffer當做一個優先級隊列來處理：
Ÿ 掃描表，把選中的行都插入隊列中，如果隊列滿了把最後一個剔除掉。
Ÿ 然後返回前N行，如果有跳過M，那麼先條過M行，然後返回之後的N行記錄。
之前使用的處理方法：
Ÿ 掃描表，重復下面的步驟直到結束
s 輸入select row直到sort buffer滿。
s 寫入前N行到buffer，然後把前N行合並到文件。
Ÿ 排序合並文件並返回前N行。
掃描表的花費和隊列和文件合並一樣，所以優化器在選擇的時候是根據其他花費的：
Ÿ 隊列的方法會使用很多cpu來插入到隊列。
Ÿ 合並文件會使用IO來讀寫文件，cpu來排序。
優化器在行數和不同值N之間平衡。
explain輸出type列為all，就說明mysql使用表掃描，如果發生表掃描考慮以下幾點：
Ÿ 表很小，表掃描比key lookup快。
Ÿ 沒有索引可用
Ÿ 有索引，和一個常量比較，但是常量覆蓋了很多行，可能造成比使用掃描慢。
Ÿ key的選擇度很低，也就是不同的值很少。這種情況下會導致一個key對於很多行，可能會造成比表掃描慢。
對於大的表，以下技術可能可以避免優化器錯誤的選擇表掃描：
Ÿ 使用ANALYZE TABLE tbl_name來更新統計信息
Ÿ 使用提示FORCE INDEX
Ÿ 使用參數--max-seek-for-key=1000，key seek不能多於這個系統變量。
優化insert的速度是，組合多個小的操作到一個大的操作。理想狀態是一個連接每次發對多個行的修改，並延遲索引更新和一致性檢查。
insert一行插入的時間由以下幾個因素決定：
Ÿ 連接(3)
Ÿ 把查詢發送的服務(2)
Ÿ 解析查詢(2)
Ÿ 插入行(1*行大小)
Ÿ 插入索引(1*索引個數)
Ÿ 關閉(1)
如果是Btree，那麼insert插入會showdown大概logN（N表的大小）。
你可以使用以下方法來提高insert速度：
Ÿ 如果在同一個客戶端插入多個行，可以使用insert values(),()，values列表同一時間插入多個行。
Ÿ 使用LOAD DATA INFILE，導入文本文件。這個比insert語句塊20倍。
Ÿ 對於有默認值的列，只指定和默認值不同的值。
Ÿ 關於innodb的批量導入：Section 8.5.4, “Bulk Data Loading for InnoDB Tables”
Ÿ 關於myisam的批量導入：Section 8.6.2, “Bulk Data Loading for MyISAM Tables”
update語句的優化和select語句類似，就是有額外的寫入，寫入的時間依賴於更新的數據和要更新的索引個數。
另外一個快速更新的方法是延遲更新，然後一次性對多個行做多次更新。
delete語句的時間主要是刪除個別行和index中的行成正比。為了更快的刪除，可以通過key_buffer_size系統變量增加key cache。
如果要刪除myisam表的所有行，truncate table tbl_name比delete from tbl_name快的多。
越多的權限，會造成越多的sql語句負荷。簡單的權限可以讓mysql在運行客戶端請求時，減少權限的檢查。
應用程序使用information_schema監控，數據庫流暢運行。目的是為了最小化文件操作來收集信息填充到動態表。
1.嘗試使用在where子句中使用常量來查找表或者數據庫。
2.最小化必須要打開的文件個數。
3.使用explain決定服務是否可以使用INFORMATION_SCHEMA優化。
Ÿ 為了避免創建連接時的負荷，使用長連接。
Ÿ 檢查查詢是否使用了索引。
Ÿ 盡量避免在更新比較頻繁的myisam表上使用復雜的select語句。
Ÿ 當沒有空閒中間空間時，myisam可以插入和select同時進行。這個行為主要由concurrent_insert變量控制。
Ÿ 使用optimize table來修復任何archive表的壓縮問題。
Ÿ 若你經常使用expr1,expr2…獲取數據，那麼使用alter table…order by expr1,expr2…
Ÿ 如果列窄，並且是唯一的，那麼使用hash作為索引比使用寬的索引更加高效。
Ÿ 對於更新頻繁的myisam表來說，盡量避免使用變長字段，如果有一個變成字段那麼就為動態行模式(因為動態行模式比較容易產生碎片)。
Ÿ 當行變多，把表分為多個表不是很管用。因為最大的性能消耗是尋找需要行的前幾個字節，而接下來讀取整行是很快的。
Ÿ 如果要是要通過很多行來計算的，如count，可以考慮創建一個中間表，然後實時更新。
UPDATE tbl_name SET count_col=count_col+1 WHERE key_col=constant;
Ÿ 如果你收集大日志表的統計信息，使用summary表來代替整個日志表。維護一個summaries比計算統計信息快的多。
Ÿ 通常，report分為live，statistic，當數據需要靜態report的時候，report從summary表被創建，而summary從live數據定期更新。
Ÿ 有些時候，封裝和存儲數據到blob列很方便，特別是要存不是行列模式的數據時。
Ÿ 通常，數據時不冗余的，但是看具體情況而定。
Ÿ 存儲過程和用戶函數有時候會增加性能。
Ÿ 可以通過緩沖查詢或者結果到應用程序來增加性能。
Ÿ 使用一個insert插入多個sql語句。
Ÿ 使用load data infile導入大量數據，比insert快。
Ÿ 使用auto_increment讓每行有個唯一標示
Ÿ 使用optimize table來避免myisam動態行模式的碎片問題。
Ÿ 如果有可能使用memory存儲引擎。
Ÿ 如果使用web server，圖片和其他二進制資源應該以文件方式存儲。
Ÿ 列在別的表定義時，用相同數據類型，這樣join的時候速度才快。
Ÿ 盡量讓列名簡單，考慮讓列名少於18字節。
Ÿ 如果要你要加快速度，應該先看不同的sql服務對低級別的數據存儲的支持。
Ÿ 如果使用數值類型，在很多情況下，會比文字形式訪問就要快。
Ÿ 復制在某些操作下提供性能的有點，比如讀寫分離
Ÿ mysql定義表使用delay_key_write=1會讓索引更新更快，因為在表關閉之前不會被刷新磁盤。
當表在打開的時候被kill，必須確保服務啟動使用了選項--myisam-recover-options,或者在啟動前使用myisamchk。
Ÿ 對於非事務表，給select高優先，那麼使用insert low_priority來降低insert優先級
Ÿ select high_priority可以讓在非事務表上訪問時直接跳過隊列。
low_priority和high_priority只適用於非事務表。
select語句性能最好的提升是在一個或者多個列上創建索引。
索引使用指定的列的行值速度會很快，如果沒有索引mysql會掃描整個表。mysql中的索引可以讓mysql快速的定位查詢數據文件，不需要掃描所有數據。
大多數mysql index保存在btree中，空間引擎是rtree，memory表也支持hash索引。
mysql在以下幾個途徑使用索引：
Ÿ 選擇符合where子句的索引。
Ÿ 減少預估的影響行數，選擇選擇度高的索引
Ÿ 當執行join的時候，mysql會選擇在列上長度和類型都一樣的。在大小一樣的情況下，varchar和char可以認為是同一個類型。
在非二進制字符串上比較，列的字符集必須一樣。在不同類型上做比較可能會倒是無法使用索引。
Ÿ 在索引的列上使用min和max。
Ÿ 在order by，group by下和索引左前綴匹配。
Ÿ 查詢獲取數據可以不獲取原始數據行。
索引在小的表，或者查詢需要很多行的大表上是沒啥用的。
primary key一般是表中最重要的查詢使用最常用的列。使用innodb存儲引擎，表是有primary key索引組織。加快基於primary key的lookup和sort的速度。
如果表很大，並沒有明顯的primary key，可以使用自增列充當primary key。
如果一個表有很多列，而查詢使用了不同列的組合。把不太使用的列放到另外一個獨立的表可能會很有效。表之間使用冗余id來關鍵2個表。
最常用的索引時簡單索引，是元數據行中的值的備份。btree數據結構可以查找指定的值，值的集合，值的區間更快，涉及的運算符，<,>,>=,<=,=,!=,<=>。
每個表的最大索引個數，和索引內最大的列數由每個存儲引擎定義。
對於列col_name，在創建的索引的時候可以指定前N個字符。對於blob和text，必須指定長度。
CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
N的最大個數是1000字節。（innodb為767，除非你設置了innodb_large_prefix）。
注意：prefix的限制是用字節數來衡量，需要注意多字節字符串的長度
你可以創建全文索引，只有innodb，myisam存儲引擎支持全文索引，並且全文索引只能用於列text，char，varchar。
查詢是有以下特性會特別高效：
Ÿ 全文索引查詢只返回文檔id或者文檔id和查詢rank
Ÿ 全文查詢以desc排序匹配的行並且使用limit來獲取前n行
Ÿ 全文查詢只使用count(1)返回匹配的行。
現在只有myisam支持rtree， Other storage engines use B-trees for indexing spatial types 。
memory存儲引擎默認使用hash索引，同時也支持btree索引。
mysql可以創建復合索引，一個索引可以由16個組成。對於比較大的列可以hash並最為索引，這樣可能會更快。
具體看: http://dev.mysql.com/doc/refman/5.6/en/multiple-column-indexes.html
使用explain語句可以查看是否使用了索引，具體看： Section 8.8.1, “Optimizing Queries with EXPLAIN”.
存儲引擎收集的統計信息讓優化器使用，表的統計信息是基於值分組的，值分組(value group)是多個行使用相同key前綴的值。為了優化的目的，一個重要的統計信息是值分組的平均大小。
mysql使用值分組的平均大小有以下幾個用途：
Ÿ 評估每個ref中有多少行要被讀取。
Ÿ 評估有多少行在join中產生。
值分組的平均值增加，會導致索引效率變差。（因為選擇度變低）。
innodb，myisam有一些控制統計信息收集的系統變量，innodb_stats_emthod,myisam_stats_method，這2個變量有3個可選值：
Ÿ nulls_equal，所有的null值都相等，默認。
Ÿ nulls_unequal，所有的null都不相等
Ÿ nulls_ignored，null值會被忽略
可以使用一下方法來更新統計信息：
使用myisamchk --stats_method=method_name --analyze
設置表讓統計信息過期，然後設置myisam_stats_method並且執行analyze table語句。
有一些注意點：
Ÿ 可以手動的更新統計信息，但是mysql也會自動收集統計信息。
Ÿ 沒有辦法提示，使用了什麼方法生成的統計信息
Ÿ 這些變量只能應用於innodb和myisam表
btree索引可以使用不等式比較。<,>,>=,<=,=,!=,<=>，between和開頭不帶通配符的like。
col_name is null 可以使用索引。如果and是符合索引前綴的就可以使用這個索引。
如：
... WHERE index_part1=1 AND index_part2=2 AND other_column=3
/* index = 1 OR index = 2 */
... WHERE index=1 OR A=10 AND index=2
/* optimized like "index_part1='hello'" */
... WHERE index_part1='hello' AND index_part3=5
/* Can use index on index1 but not on index2 or index3 */
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;
不能使用索引的情況：
/* index_part1 is not used */
... WHERE index_part2=1 AND index_part3=2
/* Index is not used in both parts of the WHERE clause */
... WHERE index=1 OR A=10
/* No index spans all rows */
... WHERE index_part1=1 OR index_part2=10
有時候可以使用索引但是優化器沒有使用，那是優化器認為使用索引會更慢。
hash索引有一下幾個特點：
Ÿ 只能使用=,<=>
Ÿ hash索引不能用於優化order by
Ÿ mysql無法通過hash評估2個值之間有多少行
Ÿ 只能搜索整個key來獲取行
設計表是為了最小化磁盤空間，減少數據讀寫的量。小的表需要的內存更小，空間減少表示表中的數據也減少也可以減少索引長度加快執行速度。
Ÿ 盡可能使用最小的數據類型。
Ÿ 盡可能給列寫上not null。
innodb現有文件格式再有行格式，目前innodb的文件格式antelope和barracuda。
antelope支持compact和redundant，barracuda支持compressed和dynamic。
具體看：http://dev.mysql.com/doc/refman/5.6/en/innodb-row-format.html
Ÿ innodb中可以使用compact存儲格式，來縮小存儲空間
Ÿ 為了最小化空間，也可以使用compressed格式
Ÿ 對於myisam如果沒有固定長度，執行會很快但是會有點空間浪費。如果使用了varchar但是想使用固定長度，可以在創建表的時候使用row_format=fixed
Ÿ primary key應該盡量的短，對於innodb表，primary key會被復制到其他secondary索引，所以短的primary key會減少一些secondary的空間。
Ÿ 索引雖然可以加快查詢的速度，但是會降低insert和update的數據庫，如果多數查詢時以組合的方式訪問，那麼創建一個復合索引，而不是2個單獨的索引。
Ÿ 如果有一個大字符串，頭幾個字符是唯一的前綴，那麼在該前綴上創建索引比較好。
Ÿ 在某些場景下，如果表總是掃描的把表分為2個表可能會提升性能。
Ÿ join字段使用相同的數據類型會提升性能。
Ÿ 盡量縮小列的名稱長度。
Ÿ 通常不會讓數據冗余。
Ÿ 如果速度遠遠比磁盤空間和維護副本的重要，可以放松一些規則，可以保留一些副本信息來加快數據訪問。
Ÿ 對於唯一的id，或者其他值都可以用數值或者字符串來代替，首選數值。因為數值類型比較節省空間，占的內存少。
Ÿ 如果使用數值類型，在很多情況下都比訪問文本的好，因為數值類型比文本類型窄。
Ÿ 使用binary排序規則，會讓排序和比較加快
Ÿ 在比較的列上使用相同的字符集和排序規則，避免字符串轉化
Ÿ 如果列的值小於8k考慮使用varchar來代替blob，如不包含blob，order by或者group by生成的臨時表可以使用mempry存儲引擎。
Ÿ 若表包含字符串，但是不經常使用可以考慮分表處理
Ÿ 當使用隨機生成的數據作為primary key，在前面加上一個唯一的列，這樣可以加快插入的速度(避免插入導致分頁)
Ÿ 如果是數值最好選擇數值類型
Ÿ 當使用blob保存數據，考慮壓縮blob
Ÿ 對於有很多列的表，考慮不適用blob或者把blob訪問到另外一個表
Ÿ 因為blob在顯示和獲取上的性能要求，和其他數據類型不一樣。可以考慮放到單獨的存儲設備上甚至另外一個數據庫實例上。
Ÿ 如果長度沒有超過varchar限制，最好使用varchar。
Ÿ 於其比較長的字符串，還不如比較字符串的hash值。
analyse()可以分析查詢結果給出建議的數據類型。
SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]])
如：
SELECT col1, col2 FROM table1 PROCEDURE ANALYSE(10, 2000);
結果會給出建議的最優的數據類型。
analyse 有2個參數：
Ÿ max_elements:默認256是analyse能夠注意到的最大的distinct值個數。
Ÿ max_memory:默認8192是analyse用來查找distinct值是可用的最大內存。
執行mysqladmin status會發現
Uptime: 426 Running threads: 1 Questions: 11082
Reloads: 1 Open tables: 12
當你只有6個表的時候會很疑惑為什麼open tables為12。
mysql是多線程的，所以很有可能很多查詢同時訪問一個表，為了最小化多個客戶端使用不同的狀態來訪問表，表為被每個獨立的會話打開。雖然會增加內存的時候，但是也提高了性能。
table_open_cache和max_connections系統變量，會影響文件的最大打開數。如果你增加一個或者2個變量，可能會到達每個處理可以打開文件描述符的個數。
table_open_cache和max_connections相關，如果有200個並發運行的連接，那麼指定的table cache至少要是200*N，N是每個join最大的執行個數。
當然也需要為臨時表和文件包裡文件描述符。
要保證你的操作徐彤可以打開文件描述符的個數。如果table_open_cache太大，會導致mysql超出閒置，查詢執行會失敗，並且會導致mysql可靠性降低。
可以通過--open-files-limit來增加mysql可以使用的文件描述符個數。
服務在啟動的時候會自動設置table_open_cache,也可以在服務啟動時候顯示設置。
以下情況mysql會關閉不適用的表，並從cache中刪除：
Ÿ 當cache滿了，但是一個線程有要打開一個不再cache上的表。
Ÿ 當cache包含的比table_open_cache更多的項，而且i zengjia mysql
Ÿ iang l 立的會話代考。有不再被使用的表。
Ÿ 當表發生flush。如flush tables語句。
服務使用一下過程來存放cache：
Ÿ 如果有沒有使用的表，以最少被使用的順序進行釋放
Ÿ 若打開一個表，但是cache滿了，沒有表可以被釋放，cache會被臨時性的擴展，當表被close不被使用的時候，就會被釋放。
MyISAM表會為每個並發訪問打開一次表，比如一個表被2個線程同時訪問那麼會被打開兩次，或者一個線程在查詢中訪問2次表。
打開一次myisam表需要2個描述符，一個是數據文件，一個是索引文件。索引文件的描述符是公用的。
如果一個表是通過HANDLE tbl_name OPEN打開，表對象不會和其他線程共享，只有調用了HANDLE tbl_name CLOSE才能關閉，或者中斷線程。
使用狀態變量opened_tables顯示了打開的表個數。
如果很多myisam表，在同一個數據庫目錄，打開，關閉和創建操作時很慢的，如果當cache滿了，select多個報表可能會造成一點負荷。因為每個表要打開都需要關閉表。
內部臨時表在處理查詢的時候被創建，這樣的臨時表可以以memory存儲引擎保存，或者以myisam保存。若臨時表太大，mysql服務會自動把內存中的表轉到磁盤中。
以下幾個條件下臨時表會被創建：
Ÿ union語句
Ÿ 某一些視圖會使用臨時表，如使用了temptable算法或者使用了union語句或者使用了聚合
Ÿ 如果order by和group by子句的key不同，或者order by， group by的來自於不同的表，那麼臨時表就會被創建。
Ÿ 查詢中出現order by和distinct子句，臨時表可能會被創建
Ÿ 如果使用了SQL_SMALL_RESULT選項，mysql會把臨時表存放到內存中，除非包含了元素只能存放到硬盤表上。
Ÿ from子句中的子查詢，即衍生表(derived table)。
Ÿ 為子查詢或者semi-join物化生成的表。
通過explain中的extra如果包含 using tempprary。
若一個臨時表變很大，mysql會自動傳化為硬盤表。內存表最大是tmp_table_size,max_heap_table_size的最小值。
對於create table 創建的memory表，是有max_heap_table_size來決定memory允許增長的最大值。
臨時表創建之後，狀態值created_tmp_tables會增長，若是硬盤表created_tmp_disk_tables會增長。
有一些因素無法讓表在內存臨時表中:
Ÿ 有blob，text列在表中
Ÿ group by，order by的長度超過512個字節
Ÿ 當使用union或者union all的時候字符串列最大值超過512個字節
Ÿ 當表的數據大小達到一定程度，可以考慮使用optimize table語句來重新組織優化表，收縮浪費的空間。optimize table復制表的數據部分然後重建索引，減少了表空間中的碎片。
Ÿ innodb有長的primary key，primary key會被復制到secondary key。使用小的primary key 可以減少secondary index 大小
Ÿ 使用varchar來代替char和會有null的列。
Ÿ 對於很大的表，而且包含很多重復的文本或者數值，可以考慮使用compressed壓縮格式。
有了優化innodb事務處理，找到到性能和負荷之間的平衡點。
Ÿ 默認使用autocommit=1會在繁忙的db服務器上強化性能的限制。innodb在每次commit之後必須刷新日志，當每個修改都跟了個commit，就很容易出現io瓶頸。
Ÿ 如果事務都是select，那麼設置成autocommit，innodb會自動識別只讀事務並優化他們。
Ÿ 避免在插入修改刪除大量數據之後去回滾，如果一個事務是的性能變差，那麼如果去回滾會變得更差。
kill掉服務也沒用，服務啟動的時候還是會去回滾。唯一的優化是增加buffer pool這樣修改全在buffer pool，不需要直接寫入硬盤。
設置innodb_change_buffering=all來緩存所有的寫入，這樣可以讓修改盡量順序的寫入
到磁盤。當處理大的dml要定期的運行checkpoint，把多個dml拆成多個小的。
Ÿ 如果可以容忍一些數據都是，那麼可以設置innodb_flush_log_at_trx_commit=0,這樣innodb會每秒一次刷新日志。
為了同步磁盤數據和binary log的次數可以使用innodb_support_xa=0。
Ÿ 當行被刪除或者更新的時候，相關的undo log不會被馬上物理刪除，甚至commit之後也不會馬上刪除。
老的數據會被一直保留，直到更早的事務，或者並發事務完成，這樣這些事務才能訪問到老的數據。
因此，長時間運行的事務在運行的時候，可以阻止innodb數據被清空。
Ÿ 當長運行事務中被修改刪除，其他事務使用讀提交，或者可持續讀，為了訪問到老的數據需要花很多功夫來重建
Ÿ 當長運行事務中修改了表，在其他事務中的查詢需要去讀原表，而不是用索引覆蓋。
當使用secondary索引查找的時候page_max_trx_id太新或者secondary索引中行被標記為了delete，innodb可能需要找聚集索引中的記錄。
Ÿ 保證日志文件大一點，甚至和buffer pool一樣大。當發生checkpoint必須把在buffer pool中的修改內容寫入到磁盤。小的日志文件會導致很多不必要的磁盤寫入。
Ÿ log buffer 越大越好。（8MB的倍數）
Ÿ 當插入數據到innodb，先關閉自動提交模式，因為自動提交會為每次插入都提交。反而會影響插入速度。
Ÿ 如果有唯一性約束，那麼為了提高性能可以先臨時的關閉唯一性約束，對於大的表可以節省一些io，因為innodb可以使用insert buffer，批量寫入到secondary索引
Ÿ 如果有foreign key約束，可以先關閉外鍵檢查。
Ÿ 使用一個insert多行數據來減少客戶端和server之間交互的成本。
Ÿ 對於有自動增長的列，可以先把innodb_autoinc_lock_mode設置為2.
Ÿ 當導入innodb全文索引的時候，為了優化性能可以使用一下步驟：
s 在創建表時候創建一個fts_doc_id，類型為bigint unsigned not null並在上面常見一個索引 如：
CREATE TABLE t1 (
FTS_DOC_ID BIGINT unsigned NOT NULL AUTO_INCREMENT,
title varchar(255) NOT NULL DEFAULT ”,
text mediumtext NOT NULL,
PRIMARY KEY (`FTS_DOC_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);
s 把數據導入到表。
s 創建全文索引。
注意：當創建表時添加fts_doc_id，要確保當全文索引被更新的時候，fts_doc_id也會被更新，也就是說當全文索引被修改是，fts_doc_id被增加。
如果沒有fts_doc_id列，那麼innodb會在創建全文索引的時候添加這個列，這樣會導致表被重建。
調整innodb查詢，在表上創建一個合適的索引，以下是innodb索引的一些原則：
Ÿ 因為每個innodb有primary key，使用在很重要的並且時間敏感的查詢的列上。
Ÿ 不要再primary key上指定太多或者太長的列，因為primary key會被保存在secondary
Ÿ 不要為每個列創建secondary索引，因為每個查詢只能使用一個索引。盡量少的創建索引，而不是大量的簡單索引。
Ÿ 如果索引列不包含null，那麼在創建表的時候定義為not null。
Ÿ 你可以優化單個查詢的事務，使用Section 14.13.14, “Optimizing InnoDB Read-Only Transactions”.
Ÿ 如果表讀多寫少，那麼就啟動query cache
[mysqld]
query_cache_type = 1
query_cache_size = 10M
Ÿ 對於ddl操作對於表和索引的操作，最重要的方面是innodb表創建和刪除索引。在mysql 5.5比mysql5.1要快的多，因為innodb引入了快速索引創建。
Ÿ 快速索引創建，可以讓刪除索引，導入數據後再創建索引更快
Ÿ 使用過truncate table清空表，不是delete from tbl_name
Ÿ 因為表是由primary key組織的，修改primary key的定義會重新組織整個表，primary key在create table語句中定義，不需要先alter或者drop primary key。
經過應用最佳實踐，io還是很高，那麼就查看cpu的使用，如果cpu使用低於70%，但是io使用很低，那麼服務的負荷就是磁盤密集型。
對IO的優化有一下幾種：
Ÿ 當表數據被緩存在innodb buffer pool，那麼查詢就不需要去讀取物理io，buffer pool的大小可以通過 innodb_buffer_pool_size來指定，一般為80%的物理內存。
Ÿ 在一些linux或者unix版本中，使用fsync函數來刷新文件到磁盤，相似的方法是很慢的。如果數據庫寫入性能有問題，可以嘗試使用innodb_flush_method=o_dsync進行基准測試看看性能。
Ÿ 如果innodb存儲引擎使用在Solaris 10 X86_64結構體系上(AMD處理器)。對innodb相關的文件使用直接io，來避免innodb性能降級。
為了讓整個存儲了innodb相關文件的ufs文件系統使用直接io，那麼在mount的時候加上選項forcedirectio。
如果直接io只針對innodb相關文件而不是整個文件系統，那麼使用innodb_flush_method=O_DIRECT，這個時候innodb會使用directio()來代替fcntl()。
Ÿ 當innodb使用了很大的innodb_buffer_pool_size，應該在Solaris 2.6以及以上平台，對innodb數據文件和日志文件所在的原設備，
或者獨立的直接IO UFS文件系統(使用forcedirectio)進行基准測試。
Ÿ 如果有額外的存儲設備使用RAID設置，或者symbolic連接到了別的磁盤，參考Section 8.11.3, “Optimizing Disk I/O”
Ÿ 如果在checkpoint期間，io吞吐量變小，那麼考慮把innodb_io_capacity設高，值越高說明flush越頻繁，值高可以避免工作積壓而導致的吞吐量問題。
Ÿ 如果system不會被innodb flush操作落下，那麼可以考慮使用低一點的innodb_io_capacity，通常盡量保持低的配置，
但是不能低到影響吞吐量，可以通過SHOW ENGINE INNODB STATUS查看：
s History list length low, below a few thousand.
s insert buffer合並和row inserted相近
s buffer pool中修改的也會低於buffer pool的innodb_max_dirty_pages_pct。
s Log sequence number-Last checkpoint小於7/8，或者理想的6/8的innodb日志文件大小。
Ÿ 其他的用來調整IO的選：
innodb_adaptive_flushing， innodb_change_buffer_max_size, innodb_change_buffering,innodb_flush_neighbors, innodb_log_buffer_size,
innodb_log_file_size, innodb_lru_scan_depth,innodb_max_dirty_pages_pct, innodb_max_purge_lag, innodb_open_files,
innodb_page_size,innodb_random_read_ahead, innodb_read_ahead_threshold, innodb_read_io_threads,innodb_rollback_segments,
innodb_write_io_threads, 和 sync_binlog.
不同的負荷有不同的設置。
innodb會自動執行優化，性能調整要配合性能監控保證數據庫有良好的性能。並在性能出問題的時候調整配置選項。
主要的配置步驟：
Ÿ 啟用innodb使用高性能內存分配。 Section 14.13.3, “Configuring the Memory Allocator for InnoDB”.
Ÿ 控制不同類型的DML操作對應的buffer，來避免頻繁的小磁盤寫入。Section 14.13.4, “Configuring InnoDB Change Buffering”
Ÿ 通過innodb_adaptive_hash_index來調整自適應hash index。Section 14.2.13.6, “Adaptive Hash Indexes”
Ÿ 如果上下文切換到達瓶頸，限制並發線程數。 Section 14.13.5, “Configuring Thread Concurrency for InnoDB”.
Ÿ 控制innodb預讀的讀取量。 Section 14.13.1.1, “Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)”.
Ÿ 若有高端的IO子系統，沒有被完全利用，可以增加後台的IO讀寫線程。Section 14.13.6, “Configuring the Number of Background InnoDB I/O Threads”.
Ÿ 控制後台I/O innodb執行的量。 Section 14.13.8, “Configuring the InnoDB Master Thread I/O Rate”.
Ÿ 控制，當innodb執行確定類型的後台寫入時用的算法。Section 14.13.1.2, “Configuring the Rate of InnoDB Buffer Pool Flushing”.
Ÿ 利用多核cpu和它們的cache配置。 Section 14.13.10, “Configuring Spin Lock Polling”.
Ÿ 防止一次表掃描而導致，而干擾buffer cache中的熱點數據。Section 14.13.1.3, “Making the Buffer Pool Scan Resistant”.
Ÿ 調整合適的log文件大小，保證crash恢復的可靠性。
Ÿ 配置buffer pool的個數和大小 Section 14.13.1.4, “Using Multiple Buffer Pool Instances”.
Ÿ 增加最大允許並發事務數量，可以大大增加繁忙事務的可擴展性。Section 14.13.12, “Better Scalability with Multiple Rollback Segments”
Ÿ 把清空操作移到後台線程。為了有效的評估設置的結果，先調整其他io相關，線程相關的配置。Section 14.13.13, “Configuring InnoDB Purge Scheduling”.
Ÿ 減少線程之間的上下文切換，對於強力的現代系統大概把innodb_thread_concurrency選項設置為32。把innodb_concurrency_tickets設置為5000，這個選項設置了線程個數和一次跳讀後可以執行的時間。
innodb在啟動後第一次訪問時，就會被表計算索引基數，而不是被存在表裡面。
這個操作會花很多時間，因此被轉移到了初始化表打開操作中，先對這個表預熱之後再使用。
預熱過程：在啟動之後會馬上通過select 1 from tbl_name limit 1的方式來運行。
MyISAM存儲引擎最好執行在大量讀數據或者地並發操作。因為表鎖會限制update並發。
Ÿ 為了幫助mysql更好的優秀的查詢，使用analyze table或者mysiamchk --analyze。更新一個相同值對應的平均行數。
在2個表基於非常量表達式的join，mysql使用這個來確定使用什麼索引。可以通過show index from tbl_name的cardinality來確定列的密度。
Ÿ 根據一個索引對索引或者數據進行排序。使用myisamchk --sort-index --sort-records=1
Ÿ 避免在更新頻繁的表上使用復雜的select語句。
Ÿ 如果一個表沒有空閒的block，myisam支持並發插入。通過修改concurrent_insert來讓插入的數據總是是追加的方式
Ÿ 對於myisam表如果修改很頻繁，試圖避免變長列。包含了一個可變長列，myisam就以動態行模式。
Ÿ 對於表很大的時候拆分表是沒有用的，在訪問一樣的時候，主要的性能花費是查找行的前幾行。
Ÿ 使用alter table…order by expr1,expr2,…讓表以這個順序組織。
Ÿ 如果要計算表的結果比如count，那麼可以生成一個中間表，實時更新。
Ÿ 當不需要知道什麼時候更新myisam表的時候，可以使用insert delayed。
Ÿ 定期使用optimize table可以避免myisam動態行產生的碎片。
Ÿ 定義一個myisam表使用delay_key_write=1，索引更新就會很快，因為修改知道表被close的時候會被更新到磁盤，如果服務崩潰必須使用myisam-recover-options選項修復，或者在啟動服務之前使用myisamchk。
Ÿ 在myisam的索引中字符串會被自動的把前綴和後綴的空間壓縮。
Ÿ 可以通過cache查詢和查詢結果在應用程序上，來提高性能。並且讓insert和update能夠批量執行
Ÿ 可以使用insert delayed語句來加快多客戶端數據插入
Ÿ 對於myisam表，如果沒有空閒空間，可以並發插入數據。
Ÿ 當有很多插入的時候load data infile會比insert快，步驟如下：
1.執行flush table或者mysqladmin flush-tables命令
2.使用myisamchk --keys-userd=0， -rq /path/to/db/tbl_name刪除所有的索引
3.使用load data infile導入數據
4.如果只有讀數據的請求，那麼使用myisampack壓縮
5.使用myisam -rq /path/to/db/tbl_name來重建索引
6.再執行flush table或者執行mysqladmin flush-table
當執行load data infile為一個空的表導入數據的時候，會被自動應用前面的優化。自動優化和顯示過程的區別是在執行創建索引的時候可以比重建索引分配的內存多。
也可以直接通過以下語句禁用和啟用索引，如果使用以下語句就可以跳過flush table：
ALTER TABLE tbl_name DISABLE KEYS;
ALTER TABLE tbl_name ENABLE KEYS;
Ÿ 可以使用如下方式加快非事務表的插入速度
LOCK TABLES a WRITE;
INSERT INTO a VALUES (1,23),(2,34),(4,33);
INSERT INTO a VALUES (8,26),(6,29);
...
UNLOCK TABLES;
好處是，在insert執行完成之後，索引buffer只被刷新一次。如果所有的行在一個insert語句上執行那麼不需要顯示的鎖定語句。
通過增加鎖可以讓數據庫寫入性能比較好。
Ÿ 通過增加key_buffer_size可以加快，myisam表的load data infile和insert的性能。
REPAIR TABLE和myisamchk的修復選項相同。有一些相同的性能優化點：
Ÿ myisamchk可以通過變量來分配內存。通過這些變量獲得更多內存。
Ÿ repair table可以有相同的配置，但是是通過系統變量，而不是myisamchk的選項。
假設myisamchk有以下一些選項：
--key_buffer_size=128M --myisam_sort_buffer_size=256M
--read_buffer_size=64M --write_buffer_size=64M
那麼對應到系統變量
myisamchk Variable
System Variable
key_buffer_size
key_buffer_size
myisam_sort_buffer_size
myisam_sort_buffer_size
read_buffer_size
read_buffer_size
write_buffer_size
none
myisam_sort_buffer_size和read_buffer_size有session級別和global級別的，session級別不會影響其他session，但是global級別會影響其他session，key_buffer_size是所有的session共享這塊內存。
repair table可以和myisamchk類似的使用：
SET SESSION myisam_sort_buffer_size = 256*1024*1024;
SET SESSION read_buffer_size = 64*1024*1024;
SET GLOBAL myisam_max_sort_file_size = 100*1024*1024*1024;
SET GLOBAL repair_cache.key_buffer_size = 128*1024*1024;
CACHE INDEX tbl_name IN repair_cache;
LOAD INDEX INTO CACHE tbl_name;
REPAIR TABLE tbl_name ;
SET GLOBAL repair_cache.key_buffer_size = 0;
如果變量只想在repair table起作用：
SET @old_myisam_sort_buffer_size = @@global.myisam_max_sort_file_size;
SET GLOBAL myisam_max_sort_file_size = 100*1024*1024*1024;
REPAIR TABLE tbl_name ;
SET GLOBAL myisam_max_sort_file_size = @old_myisam_max_sort_file_size;
這些變量可以在服務啟動的時候就配置，配置在my.cnf中
[mysqld]
myisam_sort_buffer_size=256M
key_buffer_size=1G
myisam_max_sort_file_size=100G
這裡沒有配置read_buffer_size，因為這個配置了之後每個session都會分配read_buffer_size的大小，會導致性能問題。
考慮把經常訪問，並且是讀多寫少，不是很重要的數據放到memory表。
在innodb或者myisam下進行模擬真實環境的基准測試，確定額外的性能增加是否可以允許內存丟失，和應用啟動的時候從磁盤中復制的負荷。
為了獲得更好的memory性能，檢查各種查詢對應的表，並且制定每個使用索引的索引類型，btree索引或者hash索引。
在create index的時候使用using btree，using hash來指定btree樹或者hash樹。btree在>,<,between比較快，hash在=，<=>比較快。
根據表的詳細信息，列，索引和where子句中的條件，mysql優化器會考慮很多技術來有效的執行sql查詢。
大表上的查詢可以不必讀取所有數據，join了多個表，沒必要比較所有的行組合。
優化器選擇的一些操作的集合被叫做查詢執行計劃，也被稱為explain plain。
我們的工作是通過plan來識別sql是否是足夠優秀的。
explain語句可以用來獲取mysql如何執行語句的執行信息：
Ÿ MySQL 5.6.3之後explain允許查看select,delete,insert,replace和update，在5.6.3之前只能查看select語句。
Ÿ 當explain用來解釋一個語句，mysql會顯示語句是如何執行的執行計劃。
Ÿ explain extended可以用來獲取額外的執行計劃信息。
Ÿ explain partitions用來檢查查詢設置分區表。
Ÿ MySQL 5.6.3之後，可以制動FORMAT選項來指定輸出格式。
根據explain的幫助，你可以看到那你你需要增加索引，這樣語句執行通過索引會更加快。
如果認為索引會被使用，但是有問題索引沒有被使用，可以使用analyze table來更新表的統計信息。
explain語句提供了語句的執行計劃信息。
explain會為每個表都返回一行記錄。輸出來的表的順序就是MySQL讀取處理語句的順序。MySQL通過nested-loop來解決所有的join問題。
當使用extended，explain產生額外信息，使用show warnings可以顯示。
Column
Meaning
id
Select標識符
select_type
Select 類型
table
輸出行的表
partitions
匹配的分區
type
Join類型
possible_keys
可以使用的索引
key
被選擇的索引
key_len
使用的key長度
ref
用來比較索引的列
rows
估計的響應行數
filtered
被過濾的百分比
Extra
額外信息
Ÿ id
select標識符，是一個是查詢中select的位置。如果是一個union的結果那麼可以為null，這樣table列會顯示<unionM,N>，M和N是select標識符。
Ÿ select_type
select類型，可以有以下一些值
select_typeValue
Meaning
SIMPLE
簡單 SELECT (not using UNION or subqueries)
PRIMARY
Outermost SELECT
UNION
Union中第二個查詢
DEPENDENT UNION
Union中第二個查詢，但是由外表決定
UNION RESULT
Union的結果
SUBQUERY
子查詢中第一個select
DEPENDENT SUBQUERY
子查詢中第一個select，依賴於外表查詢
DERIVED
From子查詢中的衍生表
MATERIALIZED
子查詢物化
UNCACHEABLE SUBQUERY
子查詢的結果不能被cache
UNCACHEABLE UNION
union的第二個查詢屬於uncacheable subquery
dependent通常表示使用相關子查詢。dependent subquery和uncacheable subquery評估方式不同。
對於dependent subquery，只查詢只會被不同的值進行評估。對於uncacheable subquery，子查詢會為外表每一行進行評估。
Ÿ table
行來至於一個表的表名，也可以是以下值：
<unionM,N>：行來至於select標識符為M和N的union
<derivedN>：行來至於標識符為N的衍生表
<subqueryN>：行來自於標識符為N的子查詢
Ÿ partitions
表示來自於某個的分區來匹配查詢，只有在partition關鍵字被指定的時候才會顯示。對於非分區表則為null。
Ÿ type
join類型
Ÿ possible_key
這個列表示，查詢可以使用的一些索引，如果為null表示沒有相關的索引可用。
Ÿ key
表示mysql要使用的索引。
當命名索引覆蓋了列，但是掃描筆索引查詢性能更好，就有可能key的名字不再possible_key中。
如果為null索引mysql沒有發現合適的索引應用到查詢。
對於使用force index，use index或者ignore index會忽略possible_keys的列表。
Ÿ key_len
key_len表示mysql使用的key的長度
Ÿ ref
表示那個列或者常量被用來比較索引的鍵值。
Ÿ rows
rows表示預估會響應的行數
Ÿ filtered
預估有百分之多少的數據會被過濾掉。
Ÿ extra
說明mysql解決查詢問題額外的信息。
join類型有一下幾個：
Ÿ system
表和只有一行，是const的特例
Ÿ const
表只能匹配到一行，在查詢開始就被讀取，const只會被讀取一次。當比較primary key和unique所有的部分；
SELECT * FROMtbl_nameWHEREprimary_key=1;
SELECT * FROM tbl_name
WHEREprimary_key_part1=1 ANDprimary_key_part2=2;
Ÿ eq_ref
為前表的每個行組合讀取一行數據，這個join類型是除了system，const之外最好的。
當索引的所有部分都被使用時，會使用eq_ref並且索引要是primary key或者unique not null索引。
eq_ref的比較值使用等號比較，比較值可以是常量或者使用了之前讀入表的列的表達式。以下情況會使用eq_ref
SELECT * FROMref_table,other_table
WHEREref_table.key_column=other_table.column;
SELECT * FROMref_table,other_table
WHEREref_table.key_column_part1=other_table.column
ANDref_table.key_column_part2=1;
Ÿ ref
為之前表的每個行組合，讀出所有匹配的索引行。ref只有在join負荷左前綴的時候使用，並且key不是primary key或者unique。如果key只匹配到了一些列，那麼這個join type是好的join type。
ref可以使用=或者<=>比較符
SELECT * FROMref_tableWHEREkey_column=expr;
SELECT * FROMref_table,other_table
WHEREref_table.key_column=other_table.column;
SELECT * FROMref_table,other_table
WHEREref_table.key_column_part1=other_table.column
ANDref_table.key_column_part2=1;
Ÿ fulltext
join使用fulltext執行
Ÿ ref_or_null
這個類型和ref的區別是，會另外找包含null值的。下面的情況會被應用：
SELECT * FROM ref_table
WHEREkey_column=exprORkey_columnIS NULL;
Ÿ index_merge
這個join類型索引使用了索引合並優化，這種情況下key中包含2個以上索引，key_len是使用的索引中最長的部分。
Ÿ unique_subquery
這個類型會在有in子句的情況下代替ref。
valueIN (SELECTprimary_keyFROMsingle_tableWHEREsome_expr)
Ÿ index_subquery
和unique_subquery類似，只是使用的是secondary索引
valueIN (SELECTkey_columnFROMsingle_tableWHEREsome_expr)
Ÿ range
只有在給定范圍內的行會被獲取，使用索引來獲取選中的行。key_len是使用的最長key長度。ref列為null。
range 可以在以下任意的表達式下使用：=,<=>,>,<,<>,>=,<=,is null,between,in()。
SELECT * FROM tbl_name
WHERE key_column = 10;
SELECT * FROM tbl_name
WHERE key_column BETWEEN 10 and 20;
SELECT * FROM tbl_name
WHERE key_column IN (10,20,30);
SELECT * FROM tbl_name
WHEREkey_part1= 10 ANDkey_part2IN (10,20,30);
Ÿ index
index和ALL一樣是指獲取所有的索引掃描，只有2中情況會發生：
s 只有當索引是覆蓋索引並且可以通過索引掃描來完成，這種情況下extra有using index。index通常比all要快，因為index的數據比all要少。
s 全表被掃描是通過索引按索引的順序讀取，using index不會出現在extra列中。
Ÿ all
為上一個表的每個組合做一次全表掃描，如果第一個表不是const這個情況並不是很好。通常可以通過創建索引，並且通過常量或者之前表的列值來獲取。
extra列包含了explain輸出的額外信息。
Ÿ Child of ‘table’ pushed join@1
在join中這個表被認為是table的子表，可以通過push down條件到ndb內核。只能應用在mysql集群
Ÿ const row not found
執行select…from tbl_name，這個表是空的。
Ÿ deleting all row
對於某些存儲引擎，可以簡單的刪除所有的行，這個值表示存儲引擎是否有這種優化。
Ÿ distinct
mysql查找distinct值，找到一個匹配之後就會立刻停止。
Ÿ FirstMatch(tbl_name)
對tbl_name進行了semi-join firstmatch優化。
Ÿ Full scan on NULL key
子查詢優化當優化器不能使用索引查找訪問的時候，采用回退策略。
Ÿ impossible having
having 條件都為flase，不會返回行
Ÿ impossible where
where子句為flase
Ÿ impossible where noticed after reading const tables
到讀取玩所有const和system表的時候才發現where條件為flase。
Ÿ loosescan(m…n)
semi-join loosescan策略，m和n是key的一部分
Ÿ materialize，scan
在mysql 5.6.7之前，這個只能用來表示物化臨時表。如果有scan表示在表讀取的時候沒有索引臨時表的索引。
在mysql 5.6.7之後，物化由select_type使用materialized說明，並且table的值為<subqueryN>。
Ÿ no matching min/max row
沒有滿足的查詢的條件，select min() from …where condition
Ÿ no matching row in const table
一個查詢使用了join，有空表，或者沒有匹配上的行。
Ÿ no matching rows after partition pruning
發生分區清理之後發現沒有東西能夠被delete或者update，和impossible where意思一樣。
Ÿ no tbales userd
查詢沒有from子句或者from dual。
Ÿ not exists
mysql可以使用left join優化，之前的行組合不在這個表中的數據。
Ÿ range checked for each reord(index map: N)
mysql發現沒有好的索引可以用，但是發現如果獲取了之前表的值可以一些索引就可以被使用了。盡管不是很快，比沒有索引的情況下還快。
Ÿ scanned N databases
表示當處理在information_schema上的查詢的時候有多少目錄掃描。
Ÿ select tables optimized away
當查詢只包含max，min聚合函數的時候，通過索引來解決。
Ÿ skip_open_table,open_frm_only,open_trigger_only,open_full_table
skip_open_table:表不需要被打開
open_frm_only：只需要打開frm文件
open_trigger_only：只有trg文件需要被打開
open_full_table：所有的文件都要被打開。
Ÿ start materialize，end materialize，scan
在mysql 5.6.7之前，這個只能用來表示物化臨時表。如果有scan表示在表讀取的時候沒有索引臨時表的索引。
在mysql 5.6.7之後，物化由select_type使用materialized說明，並且table的值為<subqueryN>。
Ÿ start temporary，end temporary
表示臨時表被用來semi-join去重復策略
Ÿ unique row not found
沒有滿足在primary key和unique上的行。
Ÿ using filesort
MySQL必須做一些事，讓數據以排序的順序被讀取。
Ÿ using index
只從index上讀取數據，不從表上讀取數據。如果extra還有using where，意味著用使用索引來做索引查找。如果沒有using where優化器會讀索引但是不表示是索引查找。
Ÿ using index condition
表通過上訪問索引和過濾索引的方式來確定是否要讀取所有的表數據。這種方式下，索引用來推遲訪問表數據，除非有需要。
Ÿ using index for group-by
和using index 類似，這個表名mysql發現有索引可以用來group by或者distinct
Ÿ using join buffer(block nested loop),using join buffer(batched key access)
block nested loop表名block nested-loop算法，batched key access表示使用batched key access算法
Ÿ using MRR
表示使用了multi-reange read優化策略
Ÿ using sort_union(),using union(),using intersect()
表示索引如何被合並
Ÿ using temporary
為了處理查詢，mysql需要創建一個臨時表來保存中間結果
Ÿ using where
where子句用來限制傳到下一個表或者輸出的記錄。
Ÿ using where with pushed condition
這個只能應用在ndb表上。吧where條件push到數據節點。
略，具體看：http://dev.mysql.com/doc/refman/5.6/en/explain-output.html
當使用了extended，輸出會增加filtered列，否則就不會輸出。這個列評估了有多少列會被過濾掉。使用extended然後show warnings可以輸出一下信息：
<auto_key>:自動為臨時表生成的key
<cache>(expr):表達式只會被執行一次，然後結果存放在內存中
<exists>(query fragment):子查詢被轉化為了exists謂詞
<in_optimizer>(query fragment):優化器內部對象
<index_lookup>(query fragment):query fragment通過索引查找來獲取記錄
<if>(condition,expr1,expr2):如果條件為true，返回expr1的結果，否則返回expr2的結果
<is _not_null_test>(expr):驗證表達式是不是為null
<materialize>(query fragment):子表達式被物化
`materialized-subquery`.col_name,`materialized subselect`.col_name:引用物化臨時表的列。
<primary_index_lookup>(query fragment):使用primary key來查找行。
<ref_null_helper>(expr):優化器內部對象
/* select#N*/ select_stmt:表示一個sql語句N是語句的id
outer_tables semi join(inner join):semi join操作。
<temporary table>:內部臨時表，用來保存中間結果。
很多時候，可以使用磁盤seek來評估查詢性能。對於小的表，訪問一行只需要一次查詢。
但是對於大表來說使用btree，查找一行的開銷是：log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1.
在mysql，索引塊是通常是1024個字節並且數據指針為4個字節，對於50萬行的表，key值的長度為3個字節，那麼花費就是：log(500,000)/log(1024/3*2/(3+4)) + 1 = 4
索引會占用的空間時500000*(3+4)*3/2=5.2MB，通常索引buffer的填充率是2/3，所以有可能很多索引在內存中，只需要1,2個讀取就可以找到行。
對於寫入，需要4個seek來查找定位索引的值，並且2個seek來更新索引並且寫入行。
當數據變大，不能夠放到內存，開始變慢知道全部依賴於硬盤為止。
mysql 提供了可以根據系統便來來控制和影響執行計劃。
優化器的任務時找到一個最優化執行計劃執行。當大的查詢被提交，可能會有很多時間需要花費在尋找查詢優化上。
有2個參數可以控制優化器評估計劃個數：
Ÿ optimizer_prune_level
這個變量通知優化器基於訪問表行數跳過計劃，而獲得要執行的計劃，這個參數為1會通過表的訪問行數來跳過計劃，如果參數為0那麼會全面的查找執行計劃，會導致編譯時間過長。
Ÿ optimizer_seach_depth
變量決定優化器的深度，值越小，說明編譯時間越短。如果不知道如何設置可以設置為0由mysql自己決定。
optimizer_switch系統變量可以控制優化器行為。查看當前的optimizer_switch設置：
mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on,
mrr=on,mrr_cost_based=on,
block_nested_loop=on,batched_key_access=off,
materialization=on,semijoin=on,loosescan=on,
firstmatch=on,
subquery_materialization_cost_based=on,
use_index_extensions=on
設置optimizer_switch
SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';
command為：
Command Syntax
Meaning
default
設置所有的都為默認
opt_name=default
設置某個優化為默認
opt_name=off
關閉某個優化
opt_name=on
啟動某個優化
可用的opt_name：
Optimization
Flag Name
Meaning
Batched Key Access
batched_key_access
控制BKA join算法
Block Nested-Loop
block_nested_loop
控制BNL join算法
Engine Condition Pushdown
engine_condition_pushdown
控制引擎條件 pushdown
Index Condition Pushdown
index_condition_pushdown
控制索引條件 pushdown
Index Extensions
use_index_extensions
控制使用索引擴展
Index Merge
index_merge
控制索引合並優化
index_merge_intersection
控制索引交集合並
index_merge_sort_union
控制索引排序合集合並
index_merge_union
控制索引合集合並
Multi-Range Read
mrr
控制多區間讀取優化
mrr_cost_based
控制基於花費的多區間讀取優化
Semi-join
semijoin
控制所有semijoin策略
firstmatch
控制firstmatch semijoin策略
loosescan
控制loosescan semijoin策略
Subquery materialization
materialization
控制物化，包括semi join物化
subquery_materialization_cost_based
使用基於花費的物化
設置實例：
mysql> SET optimizer_switch='index_merge_union=off,index_merge_sort_union=off';
mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=off,
index_merge_sort_union=off,
index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on,
mrr=on,mrr_cost_based=on,
block_nested_loop=on,batched_key_access=off,
materialization=on,semijoin=on,loosescan=on,
firstmatch=on,
subquery_materialization_cost_based=on,
use_index_extensions=on
innodb使用buffer pool在內存中來緩存數據和索引。
理想狀態下，把buffer pool設置成實際使用的量。buffer pool越大，就會想in-memory db一樣，只讀取一次數據，之後的所有讀取都在內存中完成。
所有的數據寫入也在buffer pool完成，然後批量刷新到磁盤，來提高性能。
對於64位大內存的系統，可以使用多個buffer pool實例來最小化buffer pool的使用沖突。
innodb管理了一組buffer pool，使用了變種的LRU算法。innodb有個中間點插入策略(midpoint insertion strategy)，有2個子隊列：
Ÿ 頭，包含了較新的塊，即被經常訪問的數據塊
Ÿ 尾，老的塊，不經常訪問的數據
默認的lru算法處理如下：
Ÿ buffer pool的3/8被劃為尾部
Ÿ midpoint是頭和尾的邊界
Ÿ 當innodb讀入到buffer pool的時候會先讓入到midpoint。
Ÿ 當訪問尾部的塊，會把它放入頭部分。如果是因為被請求而讀入，那麼會被放入頭部，如果是通過read-ahead讀入的，那麼請求就不會馬上發生。
Ÿ 如果塊沒有被訪問，那麼會根據塊age把塊移向buffer末尾。
默認，被請求的block會被馬上移動到buffer pool頭，意味著會在buffer pool內存在比較久的時間。如果大表掃描那麼就會導致page被逐出buffer pool，會導致一些性能問題。
Ÿ innodb_buffer_pool_size：用來控制buffer pool的大小，大可以提升性能減少IO。
Ÿ innodb_buffer_pool_instances：設置buffer pool實例個數，一般適用於大內存，當innodb_buffer_pool_size大於1GB的時候。
Ÿ innodb_old_blocks_pct：就是劃分頭尾的占比，默認為37（3/8）
Ÿ innodb_old_blocks_time：當在尾部的block被訪問之後，延遲多久才能被放入頭部。
1.若為0，只要block就馬上插入頭部（和原文不通被我改了），2.若不為0，表示指定時間過去之後會被插入到頭部。
(都是聯機文檔2邊的解釋不同，innodb_old_blocks_time)。
innodb_old_blocks_time>0是為了防止當對表進行掃描時讀入過多的數據，導致其他熱點數據被逐出。如果設置的時間比處理試講要大，那麼這些block會一直在尾部，減少了對其他熱點數據影響。
innodb_old_blocks_time可以在運行時設置：
SET GLOBAL innodb_old_blocks_time = 1000;
... perform queries that scan tables ...
SET GLOBAL innodb_old_blocks_time = 0;
如果想要預熱數據，那麼就不需要應用這個變量。
在show engine innodb status的BUFFER POOL AND MEMORY有關於buffer pool lru算法的監控：
Ÿ old database pages:buffer pool尾部擁有的page數量
Ÿ page made young，not young，指block從尾->頭的個數和在尾但是沒有被頭的個數。
Ÿ youngs/s non-youngs/s，在尾部被訪問，每秒頁面被放入頭部，和沒有被放入都不的次數。
Ÿ young-making rage：訪問導致block被移動到頭部的。
Ÿ not：因為innodb_old_block_time而導致訪問沒有的block沒有被放入頭部。
這裡 young-making rage 和not只是針對對尾部block訪問，並不統計頭部block的訪問。
調整lru算法：
Ÿ 當你看到在在沒有大量掃描的情況下，youngs/s很低，說明要減少delay，或者增加尾部占buffer pool的比率。
Ÿ 當有大量掃描，沒有看到很多non-youngs/s，但是有很多youngs/s需要增加delay(innodb_old_block_time)。
innodb_buffer_pool_stats和show engine innodb status 都輸出了innodb當前的buffer pool狀態。
為了減少IO，MyISAM存儲引擎使用了cache機制來保存經常訪問的數據：
Ÿ 對於索引塊，使用特別的結構key cache來維護
Ÿ 對於數據塊，mysql沒有使用cache，依賴於系統原生的文件系統cache。
當key_buffer_size系統變量來控制key cache控制，如果為0或者太小都無法使用。
當沒有key cache，index文件只能通過操作系統提供的原生文件系統buffer訪問。
索引塊是訪問MyISAM索引文件連續的單元。通常索引塊大小就是索引btree節點大小。
在key cache中的block大小都是相同的大小，但是可能會和index block大小不同。
當數據要被訪問，就需要先檢查key_cache，1.若數據在內存中，那麼就在key_cache上讀寫，2.若不在key_cache中，先選一些cache block可能存放了其他表的數據，覆蓋上去。
如果選擇的block是髒數據，那麼先寫入到表中。
當在選擇block的時候，以block策略選擇block。
線程可以同步訪問key cache，但是有以下條件：
Ÿ 一個buffer不是被更新，則可以被多個session訪問。
Ÿ 若是寫訪問要等到寫入完成，才能讓其他session訪問。
Ÿ 多個session可以在cache block上初始化替換請求，只要互相不干擾。
key cache無法避免訪問沖突，所以提供了多個key cache。默認所有myISAM表所有都cache在默認key cache上。
可以使用cache index來指定key_cache
mysql> CACHE INDEX t1, t2, t3 IN hot_cache;
+---------+--------------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+--------------------+----------+----------+
| test.t1 | assign_to_keycache | status | OK |
| test.t2 | assign_to_keycache | status | OK |
| test.t3 | assign_to_keycache | status | OK |
+---------+--------------------+----------+----------+
通過set global啟動key cache
mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;
通過設置為0來撤銷keycache
mysql> SET GLOBAL keycache1.key_buffer_size=0;
如果要撤銷默認key cache，會被忽略
mysql> SET GLOBAL key_buffer_size = 0;
mysql> SHOW VARIABLES LIKE 'key_buffer_size';
+-----------------+---------+
| Variable_name | Value |
+-----------------+---------+
| key_buffer_size | 8384512 |
+-----------------+---------+
keycache1是cache的名稱，key_buffer_size是cache的組件。
默認，表索引是被分配到默認key cache，key cache在服務啟動的時候被創建。
對於繁忙的服務上，你可以使用策略涉及到3個key cache：
Ÿ hot key cache 20%的所有key cache
Ÿ cold key cache 20%的所有key cache
Ÿ warm key cache 60%的所有key cache
語句被分配一個cache，不能和另外一個cache的語句沖突。性能增加有以下幾個原因：
Ÿ hot cache值用於讀取語句，所以數據不被修改。
Ÿ 對於一個索引被分配給了hot cache，若沒有索引掃描請求語句，那麼非葉子節點也很有可能在這個cache中。
Ÿ 如果更新的node在cache中，那麼臨時表的更新操作會更快。如果臨時表的索引大小和cold key cache一樣，那麼被更新的節點在這個cache的可能性很高。
cache index可以關聯表和cache，當重啟後關聯就會消失，若要不消失可以使用配置文件。
key_buffer_size = 4G
hot_cache.key_buffer_size = 2G
cold_cache.key_buffer_size = 2G
init_file=/path/to/data-directory/mysqld_init.sql
mysqld_init.sql裡面的語句為：
CACHE INDEX db1.t1, db1.t2, db2.t3 IN hot_cache
CACHE INDEX db1.t4, db2.t5, db2.t6 IN cold_cache
通過使用midpoint，lru策略把key cache分成了2份，hot子列表和warm子列表。midpoint不是固定的，可以通過key_cache_division_limit設置。
當索引被讀入，先放到warm的末尾，如果被固定次訪問，會被移動到hot列表。訪問次數是所有索引塊的次數。
轉入hot列表末尾，然後block在隊列中循環。如果block在隊列的開始處一段時間之後，就會被轉移到warm隊列。使用key_cache_age_threshold來控制時間。
若key_cache包含N個block，如果在N*key_cache_age_threshold/100的hit還沒被訪問那麼就會從hot隊列移動到warm隊列。
如果喜歡使用純的lru算法，可以吧key_cache_division_limit設置為100，默認為100。
midpoint插入策略使用key_cache_division_limit可以用來避免掃描而導致的熱點數據逐出。
如果key cache有足夠多的block來保存整個索引或者至少非葉子節點。在使用之前加載key cache是很有意義的。預加載可以讓索引讀取更加有效，都是通過磁盤順序讀取讀入的。
如果不適用預加載，那麼數據時從磁盤隨機讀取的，不是順序的。
為了能夠預加載索引數據到cache，可以使用load index ito cache語句
mysql> LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES;
+---------+--------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+--------------+----------+----------+
| test.t1 | preload_keys | status | OK |
| test.t2 | preload_keys | status | OK |
+---------+--------------+----------+----------+
ignore leaves來決定是否加載葉子。
通過key_cache_block_size可以來指定key cache 塊的大小。
當read buffer的大小和原生操作系統IO buffer一樣大的時候性能最好。
但是key node大小和IO的buffer一樣的時候並不能說明能夠達到最好的性能，當讀取大的葉子節點，系統拉了很多沒必要的數據，影響讀取其他節點。
為了控制myi文件block的大小可以在服務啟動的時候設置--myisam-block-size。
key cache可以在任何事件被修改
mysql> SET GLOBAL cold_cache.key_buffer_size=4*1024*1024;
當修改key_buffer_size或者key_cache_block_size任意一個都會重建key_cache若有髒頁都會被刷新到表中。
當重建是，key cache中的髒數據會被刷新到次哦按，之後cache會變得不可用，但是查詢可以直接使用文件系統的cache。
query cache會保存select文本和執行計劃。如果有相同的查詢被執行，服務會從query cache獲取結果，而不是去執行語句。query cache被所有session共享。
在讀多寫少的環境query cache是比較有用的。如果表被修改，任何相關的query cache都要被清理。
注意：在多個mysqld實例修改一個myisam表的環境下不能使用。
在某些條件下query cache被用來緩存prepared statements，Section 8.9.3.1, “How the Query Cache Operates”.
注意：在mysql5.6.5 query cache不能應用於分區表
query cache的一些性能數據，這些數據是由在mysql benchmark在linux 2*500MHz,2G內存，64MB的query cache：
Ÿ 若所有查詢都是簡單的但是據俄國都是不一樣的，query cache會導致10%的負荷
Ÿ 在當行表中，查找一行使用了query cache速度會快238%。
可以在啟動階段停止query cache，設置query_cache_size為0 。
使用一些query cache配置或者服務的負荷，會出現一些性能下降：
Ÿ 要謹慎query cache過於的大，太大反而讓cache維護成本增加。
Ÿ 服務負荷會對query cache效率有很多影響，sql_no_cahe可以讓防止更新頻繁的表cache select。
因為查詢之間的比較是一個字節一個字節比較的，所以以下2個查詢會被認為是不同的查詢：
SELECT * FROM tbl_name
Select * from tbl_name
查詢如果使用不同的數據，不同的協議版本，或者不同的字符集，會被認為是不同的查詢和cache。
對於以下查詢不能是有query cache：
Ÿ 如果查詢是外連接的子查詢
Ÿ 在存儲函數，觸發器，時間內的查詢。
在query從query cache獲取數據之前，先要檢查權限是否足夠
如果權限從query cache中獲取了結果，狀態變量qcache_hits就會自增，而不是com_select狀態變量。
如果表修改，那麼query cache關於這個表的都會不可用，需要被刪除，一下操作都會修改表：
INSERT, UPDATE, DELETE, TRUNCATE TABLE, ALTER TABLE, DROP TABLE, or DROP DATABASE.
包含了以下函數的查詢都不會被緩存：
AES_DECRYPT() (as of 5.7.4)
AES_ENCRYPT() (as of 5.7.4)
BENCHMARK()
CONNECTION_ID()
CONVERT_TZ()
CURDATE()
CURRENT_DATE()
CURRENT_TIME()
CURRENT_TIMESTAMP()
CURTIME()
DATABASE()
ENCRYPT() with one parameter
FOUND_ROWS()
GET_LOCK()
LAST_INSERT_ID()
LOAD_FILE()
MASTER_POS_WAIT()
NOW()
PASSWORD()
RAND()
RANDOM_BYTES()
RELEASE_LOCK()
SLEEP()
SYSDATE()
UNIX_TIMESTAMP() with no parameters
USER()
UUID()
UUID_SHORT()
查詢在以下一些條件下，也不能被緩存：
Ÿ 應用了自定義函數或者存儲函數。
Ÿ 引用了自定義變量或者本地過程變量。
Ÿ 對於，mysql，information_schema,performance_schema數據庫不能緩存
Ÿ 使用了分區表
Ÿ 以下格式的查詢不會被緩存：
SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE
SELECT ... INTO OUTFILE ...
SELECT ... INTO DUMPFILE ...
SELECT * FROM ... WHERE autoincrement_col IS NULL
使用了serializable隔離級別也無法被cache。
Ÿ 使用了零食表的
Ÿ 不使用任何表的
Ÿ 生成了warning的
Ÿ 涉及的表有列級別權限的
有2個關於query cache的select選項：
Ÿ SQL_CACHE：當query_cache_type為on或者demand的時候，查詢結果就會被保存。
Ÿ SQL_NO_CACHE：不對這個查詢進行緩存。
使用方法：
SELECT SQL_CACHE id, name FROM customer;
SELECT SQL_NO_CACHE id, name FROM customer;
having_query_cache系統變量說明了query cache是否啟動。
mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
如果使用標准版的mysql，就一直是yes，不能被disable。
其他的系統變量可以通過在配置文件中設置來控制query cache。query cache的系統變量都是以query_cahce_.開頭的。
把query_cache_size設置為0 來關閉query cache。默認query cache是關閉的，query_cache_size為1MB，query_cache_type為0.
為了減少負擔，可以直接把query_cache_type設置為0，關閉query cache。
如果query_cache_size非0那麼cache最小40KB來分配結構。如果設置的太小會有警告：
mysql> SET GLOBAL query_cache_size = 40000;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Warning
Code: 1282
Message: Query cache failed to set size 39936;
new query cache size is 0
mysql> SET GLOBAL query_cache_size = 41984;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| query_cache_size | 41984 |
+------------------+-------+
query_cache_size的大小必須和1024B對齊，如：
mysql> SET GLOBAL query_cache_size = 1000000;
Query OK, 0 rows affected (0.04 sec)
mysql> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| query_cache_size | 999424 |
+------------------+--------+
1 row in set (0.00 sec)
query_cache_type會影響cache如何工作：
Ÿ 如果為0，表示防止緩存或者讀取緩存。
Ÿ 如果為1，表示除了lect sql_no_cache之外其他都做緩存
Ÿ 如果為2，表示除了select sql_cache其他的都不會被緩存。
如果query_cache_size設置為0 了，應該也把query_cache_type設置為0，這樣query cache就不可能在runtime被使用，減少了查詢執行的負荷。
query_cache_type有全局變量和會話級變量。
使用query_cache_limit控制各個查詢結果cache的最大值。默認為1MB。
注意不要把cache設置的太大，因為對cache update的時候 thread會鎖定cache，這樣在大的cache會出現鎖沖突。
當查詢被cache的時候，他的結果在獲取的時候，結果被存在query cache。
因為數據不是總是被保存在一個大的塊內，query cache是按需分配的，若空間不夠，會再分配一個塊。
因為分配是需要花時間的，所以通過query_cache_min_res_unit來控制分配的最小的大小。
Ÿ 默認query_cache_min_res_unit的大小為4kb
Ÿ 如果很多查詢都是小結果，那麼默認的大小會導致內存碎片。
在這個時候可以減少query_cache_min_res_unit的值，會被清理的塊的個數有Qcache_free_blocks和Qcache_lowmem_prunes狀態變量控制。
Ÿ 如果查詢是大結果的，那麼可以把query_cache_min_res_unit調高。
通過一下語句查看是否有query cache
mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
可以通過flush query cache,來整理query cache獲得更好的內存使用。
通過RESET QUERY CACHE刪除所有query cache。
監控query cache性能，使用show status查看cache狀態變量：
mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| Qcache_free_blocks | 36 |
| Qcache_free_memory | 138488 |
| Qcache_hits | 79570 |
| Qcache_inserts | 27087 |
| Qcache_lowmem_prunes | 3114 |
| Qcache_not_cached | 22989 |
| Qcache_queries_in_cache | 415 |
| Qcache_total_blocks | 912 |
+-------------------------+--------+
可以在Section 5.1.6, “Server Status Variables”查看狀態變量的解釋。
所有的select=com_select+qcache_hits+queries with errors found by parser
com_select = qcache_inserts+qcache_not_cached+query with error found during the column privilees check。
qcache_total_blocks和qcache_free_blocks說明了query cache碎片，在執行flush query cache,只有一個free的block被剩下。
每個被緩存的查詢，都需要2個以上block，一個用來存語句，一個用來存結果。
Qcache_lowmem_prunes狀態變量，因為low memory導致查詢從query cache中刪除的次數。可以用來調整 query cache 的大小。
一個查詢可能在不同的客戶端被運行多次，在執行的時候服務會轉化語句變成一個內部結構，然後緩存這個結構。cache可以讓服務執行更快，因為避免了語句轉化帶來的負荷。
一下語句會發生轉化和緩存：
Ÿ Prepared語句，可以通過prepare語句，也可以通過client/server的驅動如（mysql_stmt_prepare() API），max_prepared_stmt_count系統變量說明了緩存的總語句數量。
Ÿ 存儲程序(存儲過程，函數，觸發器和事件)，服務會轉化和緩存整個程序體，stored_program_cache系統變量表示了每個會話大概存儲的過程數量。
mysql是基於每個會話緩存了prepare語句和存儲程序，不能被其他會話訪問。
當服務使用內部結構就要主要結構是否過期。如：
PREPARE s1 FROM 'SELECT * FROM t1';
如select *列出了所有的列，若列被修改那麼這個prepare語句就會過期了。如果沒有發現和修改，那麼下次執行的時候就會返回不正確的結果。
為了避免這個問題，服務會自動發現問題，並且重新解析語句。語句的重新解析也會在一下情況發生：
1.引用的表或者試圖從表定義的cache中被刷新，
2.影響了在cache分配空間，
3.顯示的執行了flush tables。
若存儲程序相關的對象被修改，服務會重新解析這些受影響的程序。
為了避免整個存儲程序解析，服務只解析影響的語句表達式：
Ÿ 假設只有表或者視圖的元數據被修改，只解析sou影響的語句。
Ÿ 當語句被影響呢，服務盡可能的減少解析的量
重新解析使用默認的數據庫和sql mode，服務最多試圖解析3次，如果發生錯誤全部會失敗。重新解釋是自動的，對於prepared語句，com_stmt_reprepare狀態變量表示重新解析的次數。
MySQL使用lock來管理沖突：
Ÿ mysql使用內部表來處理多線程的訪問表時的內容沖突問題。
Ÿ 當服務和其他程序協同炒作的時候會出現外部鎖。
內部鎖用於mysql內部，在整個服務內使用不涉及其他程序。
mysql的innodb表使用行級別鎖，來支持多會話，多用戶高並發的oltp系統。
為了避免多個並發寫入在同一個表上出現死鎖，可以先使用select…for update鎖定，然後再執行update。
因為innodb會自動排查死鎖，所以當發現死鎖之後會犧牲掉其中一個，所以mysql死鎖是包錯並不影響性能。
行級別鎖的幾個好處：
Ÿ 當訪問不同的行時，沖突很小。
Ÿ 回滾時修改很少。
Ÿ 可以在一行上鎖很久。
mysql中myisam，memory，merge表使用表級鎖，同一時間只能一個會話修改一個表。因此比較適合只讀，讀多，單用戶的程序。
這些引擎為了避免死鎖，會在先分配所有需要的鎖。這些策略會影響並發性。
mysql中申請寫鎖：
1.如果表上沒有lock，設置寫鎖
2.否則把lock請求放入lock隊列
mysql中申請讀鎖：
1.如果沒有寫鎖在表上，就設置讀鎖
2.否則把鎖請求放入lock隊列。
寫表優先級別比讀高，因此當鎖釋放，會先滿足寫鎖隊列中的請求，然後滿足讀鎖隊列中的請求。
這樣保證了在大量select情況下不會導致寫入數據“饑餓”的情況。同理目前的機制如果出現大量寫入，那麼會導致讀取“饑餓”。
對於鎖沖突可以檢查狀態變量 table_locks_immediate和table_locks_waited。分別表示可以馬上獲得鎖和必須要等待的數量。
mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited | 15324 |
+-----------------------+---------+
myisam當沒有空閒的block時，插入和select可以並發。
如果想要獲取一個表的讀鎖，應該使用read local，而不是read，local可以讓非沖突的insert語句並發執行。
如果要執行很多insert和select在表real_table，當並發insert不可能的時候，你可以插入很多行到temp_table然後更新真實表。如：
mysql> LOCK TABLES real_table WRITE, temp_table WRITE;
mysql> INSERT INTO real_table SELECT * FROM temp_table;
mysql> DELETE FROM temp_table;
mysql> UNLOCK TABLES;
表級鎖的好處：
Ÿ 內存開銷少
Ÿ 對於大表的鎖請求速度快，因為只有一個鎖被涉及
Ÿ 對於要scan的表比較靠。
通常表鎖適用於：
Ÿ 讀多的表
Ÿ 對於讀寫混合的表，寫入都是正對一行可以通過key獲取。
UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
DELETE FROM tbl_name WHERE unique_key_col=key_value;
Ÿ select和insert組合，update和delete很少。
Ÿ 有很多表掃描操作，但是沒有寫入。
innodb行級別鎖，多個會話可以對同一個表進行並發讀寫，若使用lock table會減少並發性。mysql會在innodb引擎之外的所有其他引擎使用表鎖。
是否使用innodb或使用其他存儲引擎有一下幾個點，考慮表鎖的壞處：
Ÿ 表鎖不能並發寫入同一個表
Ÿ 如果磁盤空間滿，沒有足夠的可用空間，那麼表鎖會一直鎖定這個表，直到有足夠的空間可用。
Ÿ 當一個select語句要執行很久，那麼會堵塞所有的寫入操作，會讓其他會話變慢甚至無響應。
以下是一些可以避免或者減少沖突的方法：
Ÿ 考慮把表切換到innodb存儲引擎下。
Ÿ 優化select語句，讓語句執行的更快減少鎖定時間。
Ÿ 啟動mysqld的時候使用--low-priority-updates，這樣所有的寫入都比select語句優先級低。
Ÿ 把所有的寫入優先級變低，low_priority_updates系統變量設置為1.
Ÿ 使用low_priority屬性來降低指定insert，update，delete語句的優先級別。
Ÿ 使用high_priority屬性來提高select語句的優先級別。
Ÿ 啟動mysqld的時候使用比較低的max_write_lock_count，這個變量是指在寫鎖到達變量的值後，允許讀鎖處理。
Ÿ 若有insert和select並發問題，可以考慮使用myisam存儲引擎，myisam支持select和insert並發。
Ÿ 如果在非事務表上混合了insert和delete，考慮使用insert delayed。
Ÿ 如果混合了select和delete，那麼可以考慮在delete語句上用limit選項。
Ÿ 使用select的SQL_BUFFER_RESULT可以減少表的鎖定時間。
Ÿ 把數據分散到多個表，可以增加並發度
Ÿ 對於某些場景，可以直接修改mysys/thr_lock.c讓讀寫在同一個隊列中，來提高性能。
在myisam，insert和select可以並發，如果有一批insert，而且select同時運行，那麼有可能insert結果不能馬上被看到。
concurrent_insert系統變量可以設置修改並發插入。
默認auto(1)，在沒有空的block的情況下允許insert和select並發，never(0)並發不可用，alywas(2)不管有沒有空的block都可以並發insert。
如果insert可以並發的情況下，就可以不適用insert delayed。
如果啟動了binlog，會把create…select或者insert…select傳化為普通的插入語句。是為了保證可以通過binlog重新創建表的副本。
使用load data infile，如果myisam表指定了concurrent，並且滿足並發條件，其他會話可以在load data執行的時候獲取數據。
使用了concurrent選項會影響load data性能，就算沒有session在表上面並發也會影響。
如果指定了high_priority，會覆蓋--low-priority-updates選項，也會導致並發查詢不可用。
對於lock table，read local和read 的不同是read local允許不沖突的insert語句執行。
MySQL會使用元數據鎖來管理對象，元數據鎖可以保證數據一致性，但是會增加一些負荷，對於多個查詢試圖訪問通過個對象會導致元數據沖突。
為了保證事務一致性，是不允許在沒有事務完成時針對這個對象執行ddl語句。
為了達到效果在事務開始時獲取元數據所，在事務結束後釋放。元數據鎖可以防止表結構被修改。
這個鎖可以應用在非事務表和事務表。
START TRANSACTION;
SELECT * FROM t;
SELECT * FROM nt;
元數據鎖可以同時應用t和nt。
prepare語句執行的時候會獲取元數據鎖，完成後釋放。
在mysql 5.5之前，會用去等量的元數據鎖，執行完之後就會釋放。壞處是若一個ddl語句執行，而另外一個語句在表上執行，那麼語句寫入binlog的順序會出錯。
外部鎖定是使用文件系統鎖定來管理myisam數據庫表被多個進程同時訪問的沖突。
在以下情況下可能使用外部鎖：
Ÿ 如果多服務使用同一個數據庫目錄，每個服務必須都要使用外部鎖。
Ÿ 如果myisamchk執行表myisam的維護，要確保服務沒有運行或者使用了外部鎖。
如果服務使用外部鎖，可以使用myisamchk在任何時候執行表檢查，如果服務視圖更新表，myisamchk鎖定的表，那麼服務會等待myisamchk先完成。
如果使用myisamchk執行寫操作，如修復或者優化表，那麼必須保證mysqld服務沒有運行。如果myisamchk和mysqld同時寫訪問可能會導致數據文件異常。
使用外部鎖時，如果進程對表有訪問需求，那麼會先請求文件系統鎖，然後再訪問表。如果不能被獲取那麼進程會被block直到可以獲取鎖為止。
外部鎖會嚴重影響性能，因為服務必須要等待其他進程訪問完之後才能訪問。
一個數據文件夾只對應一個服務，並且沒有其他程序如myisamchk訪問表，那麼是沒有必要使用外部鎖的。如果是只讀表也可以不適用外部鎖。
如果要在沒有外部鎖的情況下使用myisamchk，要不停止服務後運行，要不lock，flush後運行。
mysqld使用變量skip_external_locking來控制外部鎖，當為enable是外部鎖被禁用，反之被啟用。
在mysqld啟動時通過--external-locking或者--skip-external-locking來控制。
為了讓外部鎖可以讓多個mysqld修改同一個表，需要滿足一下條件：
Ÿ 不能使用query cache
Ÿ 不能使用--delay-key-write=ALL或者DELAY_KEY_WRITE=1表選項。
以下變量設置是滿足條件的，--external-locking配合delay-key-write=0並且--query-cache-size=0。
總之在使用mysql之前，先要對mysql進行測試。
Ÿ 如果有足夠的內存，那麼可以考慮刪除swap
Ÿ 避免外部鎖的使用
只有在多個服務要訪問同一個數據文件的時候才需要使用外部鎖或者沒有lock，flush的時候執行myisamchk。
lock tables和unlock tables用來加鎖和解鎖表。
shell> mysqld --verbose --help
會輸出mysql的選項和配置系統變量輸出。
如果mysqld在啟動狀態可以通過show variables查看系統變量，使用show status查看狀態變量。
也使用mysqladmin查看：
shell> mysqladmin variables
shell> mysqladmin extended-status
查看每個變量或者狀態變量的描述可以查看：see Section 5.1.4, “Server System Variables”, andSection 5.1.6, “Server Status Variables”.
在sql server中2個比較重要的是key_bufer_size和table_open_cache,在配置其他參數前應該先考慮這個參數。
以下配置說明了不同情況下不同的配置：
Ÿ 如果有大於256MB的內存，有很多表，需要最大化性能，可以使用：
shell> mysqld_safe --key_buffer_size=64M --table_open_cache=256 \
--sort_buffer_size=4M --read_buffer_size=1M &
Ÿ 如果只有128MB內存，只有少量的一些表可以如下使用：
shell> mysqld_safe --key_buffer_size=16M --sort_buffer_size=1M
Ÿ 如果只有少量的內存，但是連接很多，可以如下：
shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=100K \
--read_buffer_size=100K &
或者
shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=16K \
--table_open_cache=32 --read_buffer_size=8K \
--net_buffer_length=1K &
如果執行order by和group by，在可用內存還是比較多的情況下可以增加read_rnd_buffer_size來增加空間。提高行讀入sort操作的性能。
如果每次都要配置的可以寫入配置文件。
Ÿ 磁盤查詢時最主要的性能瓶頸，這個問題主要原因是數據變得太大，導致cache失效。
Ÿ 通過把文件使用鏈接的方式放到別的地方，來增加可用的磁盤主軸。
s 使用symbolic鏈接
對於myisam，可以把索引文件和數據文件放到其他盤，然後使用symlink鏈接到數據目錄
s 條帶化
條帶化意味著你有很多磁盤，然後把第一塊放入第一個磁盤，第二個放入第二個磁盤，第N塊放到第N個磁盤。
當數據大小小於條帶化是，性能是最好的，因為是對齊的。條帶化比較依賴操作系統和條帶大小，所以要對不同的條帶大小進行壓測。
條帶化的性能會根據參數而不同，根據設置不同的條帶化參數和磁盤個數，從來選擇最優的隨機或者順序訪問的配置。
Ÿ 為了可靠性，可以考慮使用RAID 0+1，這種情況需要2*N的空間來保存N的空間。
Ÿ 根據數據的特性來選擇RAID級別。
Ÿ 通過使用hdparm配置磁盤接口，來提高性能。
hdparm -m 16 -d 1
Ÿ 也可以修改設置文件系統的參數設置
如果不需要知道最後訪問的時間，可以通過mount的-o noatime，減少文件系統的最後訪問時間更新，可以減少disk seek
在很多操作系統可以通過-o async選項來異步的完成最後訪問時間的更新。
可以把數據庫或者表文件系統到其他地方，然後使用符號鏈接。
對於innodb可以再create table使用data directory子句來代替符號鏈接。
推薦吧整個數據庫使用symlink到其他磁盤
使用以下語句查看數據文件夾位置：
SHOW VARIABLES LIKE 'datadir';
在unix上使用符號鏈接，首先要創建目錄，然後使用soft link
shell> mkdir /dr1/databases/test
shell> ln -s /dr1/databases/test /path/to/datadir
當做了以下處理，那麼在db1上的tbl_a的修改液會出現在db2的tbl_a上，如果同時修改2個數據庫的tbl_a那麼就可能會出現問題。
shell> cd /path/to/datadir
shell> ln -s db1 db2
只有MyISAM表可以完全支持符號鏈接，對於innodb可以指定表空間來解決，Section 14.5.4, “Specifying the Location of a Tablespace”。
可以通過have_symlink查看系統是否支持符號鏈接
SHOW VARIABLES LIKE 'have_symlink';
MyISAM表的符號鏈接處理：
Ÿ 在數據文件夾中，總是有frm，myd，myi文件，數據文件和索引文件可以被符號鏈接替換，但是frm文件不行。
Ÿ 可以通過符號鏈接，把索引文件和數據文件指向其他數據文件夾
Ÿ 也可以通過create table的data directory和index directory來設置目錄。
Ÿ Myisamchk不會使用數據文件或者索引文件來替換符合鏈接，而是直接使用符號鏈接指向的文件。Alter table，optimize table，repair table 也一樣
注意：當使用符號鏈接的表被刪除了，符號鏈接所使用的符號鏈接和數據文件都會被刪除。
Ÿ 如果使用alter table rename或者rename table重命名表，但是不移動數據庫，重命名鏈接文件，並重命名數據文件和索引文件。
Ÿ 如果使用alter table rename或者rename table把表移動到另外一個數據庫，
但是文件目錄被移動到另外的數據庫文件，如果表名被修改，在新的目錄中的符號鏈接名被修改，並且數據文件和索引文件也被修改。
這些表的符號鏈接需要滿足：
Ÿ Alter table忽略data directory和index directory選項
Ÿ 只能支持數據文件和索引文件的符合鏈接，frm文件不支持符號鏈接
如果一個表tbl2符合鏈接到了同一個數據庫的tbl1,那麼一個線程讀取tbl1一個線程修改tbl2就會有如下問題：
shell> cd /path/to/datadir/db1
shell> ln -s tbl1.frm tbl2.frm
shell> ln -s tbl1.MYD tbl2.MYD
shell> ln -s tbl1.MYI tbl2.MYI
Ÿ Query cache無法辨認tbl1和tbl2
Ÿ Alter table tbl2會失敗
查看http://dev.mysql.com/doc/refman/5.6/en/symbolic-links.html
以下列表說明了mysqld如何使用內存。
Ÿ 所有線程共享myisam的key buffer。大小由key_buffer_size控制。
Ÿ 每個線程使用線程各自的空間來管理客戶端連接，
s 堆棧由變量thread_stack控制
s 連接buffer由變量net_buffer_length控制
s 結果buffer由變量net_buffer_length控制、
連接buffer和結果buffer起初都和net_buffer_length相同，會動態增長最大到max_allowed_packet。每個語句執行完之後都會把結果buffer收縮到net_buffer_length大小。
Ÿ 所有的線程共享同一個base memory
Ÿ 當thread不在被需要的時候，內存會被釋放到system，除非thread到了thread cache中，這個時候分配的內存會被保留。
Ÿ Myisam_use_mmap變量設置為1，表示為myisam表文件開啟內存映射。
Ÿ 每個順序掃描分配一個read buffer 由read_buffer_size控制。
Ÿ 當讀取是隨機讀取，隨機讀取buffer會被分配用來減少磁盤seek。
Ÿ 所有join都是single pass，大多數join是不需要臨時表的，大多數臨時表是存放hash表的，弱內存中臨時表太大那麼會被放到硬盤上。
Ÿ 大多數請求是一個sort buffer和0到2個臨時文件來排序，臨時文件個數由文件個數來決定。
Ÿ 所有解析和計算都是在thread 本地和可重用內存池中完成的。
Ÿ 對於每個被打開的myisam表，索引文件只會被打開一次，數據文件是每次請求就被打開一次。
Ÿ 對於有blob的表，buffer會增大到結果集中最大的blob一樣的大小。對於表掃描會增大到表中最大的blob的大小。
Ÿ 所有在使用的表的句柄結構是以先進先出的方式存放在cache中，cache的初始化大小為table_open_cache。如果有2個查詢要訪問同一個表，那麼會胃每個查詢都打開一個對象。
Ÿ Flush table語句或者MySQLadmin flush-tables命令關閉所有未使用的表，當所有在使用的表，當使用完畢之後也會被關閉。Flush table在所有的表都被關閉後返回。
Ÿ 當使用grant，create user，create server，install plugin語句的結果會被保存在內存中，revoke，drop user，drop server，uninstall plugin並不會刪除緩存，
之後當執行flush privileges才會被清理。
對於一些內存和操作系統體系結構支持內存頁大於默認的4kb，large page的支持依賴於硬件和操作系統，對於執行大量內存因為減少了TLB丟失，可以增加一些性能。
TLB是硬件中用來緩存，虛擬內存地址轉化為物理地址後的一個映射。下次使用虛擬地址訪問無需在轉化一遍虛擬地址。
在MySQL中，large page可以在InnoDB中被使用，可以用於buffer pool 和其他內存池。
在MySQL中標准的使用large page的大小為4mb，可以通過—super-large-pages或者—skip-super-large-pages控制。
MySQL也支持Linux對large page的支持(被叫做hugeTLB)。
在Linux下使用large page首先要在Linux內核中啟用。對於redhat默認是啟動large page的。
shell> cat /proc/meminfo | grep -i huge
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 4096 kB
輸出是非空的說明支持large page，0表示large page 沒有被配置使用。
如果已經支持了內核，可以使用以下代碼來配置讓MySQL使用，這個配置在每次啟動後都要被使用。
# Set the number of pages to be used.
# Each page is normally 2MB, so a value of 20 = 40MB.
# This command actually allocates memory, so this much
# memory must be available.
echo 20 > /proc/sys/vm/nr_hugepages
# Set the group number that is permitted to access this
# memory (102 in this case). The mysql user must be a
# member of this group.
echo 102 > /proc/sys/vm/hugetlb_shm_group
# Increase the amount of shmem permitted per segment
# (12G in this case).
echo 1560281088 > /proc/sys/kernel/shmmax
# Increase total amount of shared memory. The value
# is the number of pages. At 4KB/page, 4194304 = 16GB.
echo 4194304 > /proc/sys/kernel/shmall
通過以下代碼來驗證
shell> cat /proc/meminfo | grep -i huge
HugePages_Total: 20
HugePages_Free: 20
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 4096 kB
最後一步，讓MySQL使用Hugetlb_shm_group時，memlock為“unlimited”，可以通過/etc/security/limits.conf或者mysqld_safe增加腳本
ulimit -l unlimited
large page默認是禁用的如果要使用，可以在配置文件上配置
[mysqld]
large-pages
有了這個選項InnoDB會自動的在buffer pool和其他內存池上使用large page。如果沒有使用那麼會在error log上有警告
Warning: Using conventional memory pool
可以通過/proc/meminfo來驗證
shell> cat /proc/meminfo | grep -i huge
HugePages_Total: 20
HugePages_Free: 20
HugePages_Rsvd: 2
HugePages_Surp: 0
Hugepagesize: 4096 kB
連接管理器，為每個連接分配一個專用的線程。為了避免連接管理器創建一個關聯的線程，那麼會先查看線程cache看是否包含了一個可用的線程。
如果連接結束，如果cache沒滿，那麼會把thread返回給線程cache。
這種連接線程模式下，連接個數和線程個數一樣多，如果出現大量的連接，那麼會造成一些問題。
因為每個線程都會消耗服務，內核資源，stack，為了保證大並發，必須減少每個線程的stack大小。
當Mysql 5.6.10之後，引入了線程池插件提供了一個替換方案，來減少開銷提高性能。在大連接情況下，線程池通過有效的管理語句執行的線程，提高了服務性能。
已通過系統變量和狀態變量查看mysql是如何管理連接和線程的關系。
thread_cache_size表示thread cache的大小，如果為0（默認）表示沒有cache。這樣有新連接的時候會創建，在連接中斷後會被銷毀。
如果設置thread_cache_size為N就是說可以運行N個不活動的連接緩存線程。thread_cache_size可以在服務啟動的時候會在正在運行的時候被設置。
可以使用threads_cached和threads_created狀態來監控cache中的thread。
max_connections控制最大允許的連接數。
當thread stack他笑，限制了復雜的sql，遞歸的深度和一些內存消耗大的行為。
mysql服務維護了一個host cache保存了ip地址，host，錯誤信息。服務使用這個cache主要用於非本地的tcp連接。
不能用於回環網絡接口，unix socket file，命名管道和共享內存的訪問方式。
當有新連接，服務會檢查ip是否次年在在cache中，若沒有先解決ip到host的對應，服務會把信息保存在cache中，如果滿了，會把最後一個取消掉。
host_cache以表的當時在數據庫performance_schema下可以通過select查詢。
host cache的項處理方法如下：
Ÿ 當第一個tcp客戶端連接到達，先創建一個記錄，然後存入IP，host為null，標記為false
Ÿ 如果標記為false服務會試圖通過ip獲取host，若成功寫入host，修改flag。若失敗，如果是永久性錯誤，那麼host保持為null，flag設置為true，如果不是保持不變。
Ÿ 若在獲取時出錯服務會更新error信息。
服務在解決ip和host對應問題的時候，如果os支持，那麼會使用gethostbyaddr_r(),gethostbyname_r()，
否則使用gethostbyaddr(),gethostbyname()會有lock mutex可能會堵塞其他線程。
服務使用host cache有以下幾個目的：
Ÿ 通過緩存ip-host對可以避免為每個客戶端連接做dns查看。
Ÿ cache包含了連接處理過程中的錯誤。如果指定host出現太多錯誤，就會block不讓這個host再請求連接。
max_connect_errors如果被草果服務會block這個客戶端的連接。
為了緩解block的host，可以使用flush hosts語句或者執行mysqladmin flush-hosts
host cache默認是啟動的，可以通過把host_cache_size設置為0來禁用host cache。
可以通過--skip-name-resolve來禁止DNS查看host name，這樣服務都會使用ip來匹配grant表。
如果DNS非常慢，為了提高性能可以使用--skip-name-resolve或者增加host_cache_size的大小。
如果要限制tcp連接，可以通過--skip-networking選項。
有一些錯誤和tcp連接不相關，在連接之前就已經發生，對於這些錯誤可以查看狀態變量 Connection_errors_XXX
默認的線程處理模式是一個連接一個線程，如果客戶端連接越多會導致性能越爛。線程池提高了在大量客戶端連接的情況下的性能。
線程池主要解決了一下問題：
Ÿ 太多線程的stack導致cpu cache使用效率變低
Ÿ 如果線程太多會導致上下文切換邊頻繁
Ÿ 如果執行線程過多導致資源爭用增加
線程池是商用插件，不包含在通用發布版。（在mariadb上有thread pool功能，具體看：Thread pool in MariaDB 5.5）。
線程池包含以下組件：
Ÿ 插件library對象包含了插件的代碼和information_schema上的一些表(TP_THREAD_STATE,TP_THREAD_GROUP_STATE,TP_THREAD_GROUP_STATS)。
Ÿ 系統變量也有一些線程池相關的系統變量，當服務成功加載thread pool插件，thread_handling為loaded-dynamically。
其他相關的變量
Ÿ thread_pool_algorithm:用於調度的並發算法
Ÿ thread_pool_high_priority_connection:如何調度語句執行
Ÿ thread_pool_prio_kickup_timer:thread pool中優先級從低到高需要的時間
Ÿ thread_pool_max_unused_threads:允許的睡眠的線程個數
Ÿ thread_pool_size:線程池中thread group個數
Ÿ thread_pool_stall_limit:在執行前被stall的時間。
若變量設置有問題插件就無法被加載。
Ÿ performance schema中有相關的thread pool信息。
為了讓線程池插件被加載library必須在mysql插件目錄中，plugin_dir系統變量指示了這個值。
配置my.cnf加載插件：
只加載插件不加載information_schema表
[mysqld]
plugin-load=thread_pool.so
也可以逐個加載全部插件
[mysqld]
plugin-load=thread_pool.so
plugin-load=thread_pool=thread_pool.so;tp_thread_state=thread_pool.so;tp_thread_group_state=thread_pool.so;tp_thread_group_stats=thread_pool.so
如果要加載某一個表
[mysqld]
plugin-load=thread_pool=thread_pool.so;TP_THREAD_STATE=thread_pool.so
如果不加載information_schema表，那麼一些thread pool監控圖形就無法顯示。
可以通過information_schema.plugins表來驗證線程池插件安裝。
若加載成果，把thread_handling設置為dynamically-loaded。如果加載失敗會寫一個錯誤信息。
thread pool由thread group組成，每個組都管理一些客戶端連接。客戶端一連接就會以回環的方式分配給thread group。
thread group的個數由thread_pool_size決定，每個組內最大的線程個數是4096(或者4095其中一個線程會被內部使用。)
thread pool把連接和線程拆開，因此連接和線程之間沒有固定的關系。
thread group嘗試保證每個group裡面只有一個線程是運行的。但是有時候為了性能有時候一個thread group可能有多個運行的線程。算法如下：
Ÿ 每個thread group都有一個偵聽的線程用來把進來的語句分配給組。當語句到達，線程組要不馬上運行要不讓他等待之後運行
s 當語句是為唯一一個到達的並且沒有語句在排隊或者有運行的語句，那麼就會被馬上執行
s 如果不能被馬上執行，那麼就放入隊列等待。
Ÿ 當馬上執行是，由偵聽線程來執行，如果很快完成thread返回，繼續偵聽，如果不能馬上返回，那麼考慮stall啟動另外一個線程來偵聽。
使用偵聽線程可以快速的執行，不需要創建另外一個線程，在低並發下可以快速的執行sql
當thread pool啟動，會為每個thread group創建一個偵聽線程和一個後台線程。其他線程會根據運行的需要而創建。
Ÿ thread_pool_stall_limit系統變量決定了，語句需要等多久，如果超過了變量值就會啟動另外一個線程執行。
如果等待小避免死鎖出現，如果等待大避免並發量過大而導致的問題。
Ÿ thread pool會強制限制短查詢的並發量，在到達堵塞時間之前，會阻止其他語句執行，如果超過了堵塞時間，就允許其他語句執行不會再阻止。
這樣就保證了只有一個短運行語句，但是可能有多個長運行語句。
Ÿ 當語句發送io操作或者用戶級別鎖(行鎖或者表鎖)的時候就會被堵塞。堵塞可能會導致thread group不能使用，
所以有個回調函數，回調到thread pool，讓thread pool為這個group創建一個線程來執行其他的語句。
Ÿ 有2個隊列，一個是高優先級的，一個是低優先級的，第一個語句進入的時候進入低優先級。
當事務在運行時，事務內的語句都會被放入高優先級隊列。變量thread_pool_high_priority_connection可以讓會話產生的語句都進入高優先級隊列。
如果語句是非事務性語句或者是自動提交的語句，會被放入低優先級。
Ÿ 當thread group選擇一個語句執行的時候，會先去看高優先級隊列，再去看低優先級隊列。如果找到，就沖隊列中刪除。
Ÿ 如果語句在低優先級隊列很長時間，thread pool會把它移動到高優先級隊列中，thread_pool_prio_kuckup_timer來控制這個時間。
最大的移動速度是每個語句10ms或者1秒100個語句。
Ÿ thread pool會重用最活躍的線程，這樣可以讓cpu cache發揮更好的性能。
Ÿ 當thread執行來自連接的語句，performance schema會把線程活動記錄給這個連接。否則會記給thread pool
一下幾個例子說明什麼情況下，會出現多個執行的線程：
Ÿ 一個線程執行語句，但是運行的太長被認為堵塞了的時候，thread group會啟動一個線程來執行語句。
Ÿ 一個線程執行語句，被堵塞了並報給了thread pool，thread group會允許啟動另外一個線程來執行語句。
Ÿ 一個線程執行語句，被堵塞了，但是沒有報告因為不在可以報告的代碼裡面，那麼執行時間超過thread_pool_stall_limit的時候，thread group允許另外一個線程執行新的語句。
thread pool被設計成可以更具連接數擴展，並控制執行的線程數減少死鎖。對於無法報告的堵塞，這個很重要，因為這些可能會導致死鎖：
Ÿ 長運行語句。使用了大量資源導致其他語句無法訪問
Ÿ binary log導出線程讀取binary log並發送到slave，這類長運行的線程運行時間長，並且不能堵塞其他語句的運行。
Ÿ 語句堵塞在行鎖，表鎖，sleep，和其他堵塞類型，不能被mysql或者引擎報告。
以上各個情況，阻止死鎖，當語句沒有很快被完成是，會被認為堵塞，這樣thread group可以讓另外一個線程運行其他語句。
最大的線程數是max_connections和thread_pool_size的和，當所有連接語句都在執行，並且每個thread group 都有額外的偵聽線程是出現這情況。
thread_pool_size是thread pool最主要的參數之一，影響thread pool的性能。可以在服務啟動時調整，文檔的經驗值：
Ÿ 如果主要的存儲引擎是innodb，可以把這個值設置在16-36，最優的值一般在24-36，有些特殊的情況需要對於16.
Ÿ 如果主要的是myisam，值可以相對的低一點。
thread_pool_stall_limit，是另外一個比較重要的變量，特別是對堵塞和長運行語句的控制。block有2種，1種是thread會報告堵塞的。另外一個種是無法報告堵塞的。
變量保證了不至於讓整個服務都處於block狀態下，也可以減少死鎖風險。長運行查詢被允許堵塞thread group那麼被分配給這個group的所有語句都會被堵塞。
thread_pool_stall_limit設置的時候要比一般的語句要長，設置的太高會導致小運行語句會等待長運行的語句太長時間。減少等待時間可以加快語句執行，也可以減少死鎖。
假設99.9%的語句在100ms內完成，其他的語句分布在100ms-2h,那麼把thread_pool_stall_limit設置為10也就是100ms即可。
這個變量可以在服務運行時修改，如果啟用了TP_THREAD_GROUP_STATS表，就可以使用一下語句查看有多少語句被堵塞了：
SELECT SUM(STALLED_QUERIES_EXECUTED) / SUM(QUERIES_EXECUTED)
FROM information_schema.TP_THREAD_GROUP_STATS;
這個值應該越小越好。
假設一下場景那麼，最大的被執行時間是多少呢：
Ÿ 有200個語句在低優先級隊列
Ÿ 有10個在高優先級隊列
Ÿ thread_pool_prio_kickup_timer為10000(10s)
Ÿ thread_pool_stall_limit為100(1s)
那麼最爛的情況如下，若10個高優先級別都是長運行的，那麼10個高優先都要被運行到的時間是10s，在最壞的情況下，沒有語句被移動到高優先級隊列，因為都已經包含了等待運行的語句。
之後需要花2s的時間把語句移動到高優先級隊列因為100/s。這樣語句都在高優先級隊列了，那麼由要運行200個語句並且都是長運行的，那麼需要花200s。所以一共是222s。
（不是很理解，應該在高優先執行到之後，不需要在等10s才能移動語句到高優先級隊列）
具體看：http://dev.mysql.com/doc/refman/5.6/en/optimize-benchmarking.html
MySQL Internals-Index Merge優化
淺析index condition pushdown
淺析multi range read(MRR) & batch key access(BKA) & block nested loop(BNL)
Thread pool in MariaDB 5.5
select client_id,
COUNT(DISTINCT client_id), created, total, passed, hard_bin, soft_bin, ( passed / total) AS yield
from system_pref_report
GROUP BY client_id
order by id
你試試看，再研究
1、選取最適用的字段屬性
MySQL 可以很好的支持大數據量的存取，但是一般說來，數據庫中的表越小，在它上面執行的查詢也就會越快。因此，在創建表的時候，為了獲得更好的性能，我們可以將表中字段的寬度設得盡可能小。例如，在定義郵政編碼這個字段時，如果將其設置為CHAR(255),顯然給數據庫增加了不必要的空間，甚至使用VARCHAR這種類型也是多余的，因為CHAR(6)就可以很好的完成任務了。同樣的，如果可以的話，我們應該使用MEDIUMINT而不是BIGIN來定義整型字段。
另外一個提高效率的方法是在可能的情況下，應該盡量把字段設置為NOT NULL，這樣在將來執行查詢的時候，數據庫不用去比較NULL值。
對於某些文本字段，例如“省份”或者“性別”，我們可以將它們定義為ENUM類型。因為在MySQL中，ENUM類型被當作數值型數據來處理，而數值型數據被處理起來的速度要比文本類型快得多。這樣，我們又可以提高數據庫的性能。
2、使用連接（JOIN）來代替子查詢(Sub-Queries)
MySQL 從4.1開始支持SQL的子查詢。這個技術可以使用SELECT語句來創建一個單列的查詢結果，然後把這個結果作為過濾條件用在另一個查詢中。例如，我們要將客戶基本信息表中沒有任何訂單的客戶刪除掉，就可以利用子查詢先從銷售信息表中將所有發出訂單的客戶ID取出來，然後將結果傳遞給主查詢，如下所示：
DELETE FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的SQL操作，同時也可以避免事務或者表鎖死，並且寫起來也很容易。但是，有些情況下，子查詢可以被更有效率的連接（JOIN）.. 替代。例如，假設我們要將所有沒有訂單記錄的用戶取出來，可以用下面這個查詢完成：
SELECT * FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
如果使用連接（JOIN）.. 來完成這個查詢工作，速度將會快很多。尤其是當salesinfo表中對CustomerID建有索引的話，性能將會更好，查詢如下：
SELECT * FROM customerinfo
LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.
CustomerID
WHERE salesinfo.CustomerID IS NULL
連接（JOIN）.. 之所以更有效率一些，是因為 MySQL不需要在內存中創建臨時表來完成這個邏輯上的需要兩個步驟的查詢工作。
3、使用聯合(UNION)來代替手動創建的臨時表
MySQL 從 4.0 的版本開始支持 UNION 查詢，它可以把需要使用臨時表的兩條或更多的 SELECT 查詢合並的一個查詢中。在客戶端的查詢會話結束的時候，臨時表會被自動刪除，從而保證數據庫整齊、高效。使用 UNION 來創建查詢的時候，我們只需要用 UNION作為關鍵字把多個 SELECT 語句連接起來就可以了，要注意的是所有 SELECT 語句中的字段數目要想同。下面的例子就演示了一個使用 UNION的查詢。
SELECT Name, Phone FROM client
UNION
SELECT Name, BirthDate FROM author......余下全文>>
