1.using filesort

mariadb默認采用aria存儲引擎處理臨時表，參數aria_sort_buffer_size

一次掃描：將查詢的所有列(含排序列)放入緩存區排序  MySQL 5.0以后引入二次掃描：只將排序列和主鍵放入緩存區排序，然后再根據主鍵讀取數據  mysql5.0之前的

需要使用兩次掃描：記錄大小比max_length_for_sort_data設置大時查詢包含blob或text類型的數據列時

排序處理方法：

使用索引排序 extra無法顯示內容只對驅動表排序(含無連接情形)，然后再連接 extra顯示using filesort將連接結果保存到臨時表后，在臨時表中排序   extra顯示using temporary、using filesort

前一種為流處理:檢索到一條，就返回客戶端一條后兩種為緩沖處理:選取符合條件的所有記錄進行排序或分組，再一起返回給客戶端

與排序相關變量:show status like 'Sort%';sort_merge_passes 多次合并的處理次數sort_range 對通過索引范圍掃描的結果進行排序的次數sort_scan，通過全表掃描檢索的結果進行排序的次數sort_rows 目前為止已排序的全部記錄數

group by 處理

1.索引處理 --與order by 處理方式相似

2.松散索引處理  using index for group-by  只對單個表查詢的group by ，group by 后面字段順序要與組合索引一致，且select 后面字段順序也要與group by 字段一致，select后支持min或max函數

3.臨時表處理using temporary、using filesort

distinct處理:

1.不含集合函數的distinct，類似group by，能用索引2.帶集合函數，如count(distinct a)，需要臨時表，不能用索引，但extra不顯示using temporary3.不帶where條件的可走全索引或范圍掃描

臨時表：

1.mariadb在內存中使用memory引擎，在磁盤中使用aria引擎，而Mysql在磁盤中使用myisa引擎下面查詢需要臨時表：1.1 order by 與group by的數據列不同1.2 order by 或group by的數據列不在驅動表上1.3 同時有distinct與order by時，或無法使用索引處理distinct時1.4 使用union時，select_type顯示union result1.5 使用union all時，select_type顯示union result1.6 執行計劃select_type為derived時

后三種是使用了臨時表，但不顯示using temporary

2 在磁盤中創建臨時表什么情況下會創建？2.1 存儲的內容包括了blob或text類型數據2.2 所有記錄全部大小或從union、union all查詢的列中存在大于512字節的數據列時2.3 group by或distinct數據列含用大于512字節的數據列時2.4 要存儲數據的全部大小超過了tmp_table_size或max_heap_table_size設置值時前三種直接在磁盤中創建臨時表，后一種先是在內存中建臨時表，超過大小，再轉換到磁盤中。

max_heap_table_size--用戶可以創建內存表的大小tmp_table_size--臨時表最大值

3.狀態變量show status like 'Created_tmp%tables';Created_tmp_tables --內存和磁盤中的Created_tmp_disk_tables---磁盤中的Created_tmp_disk_tables/Created_tmp_tables<5%

4.帶索引的內部臨時表臨時表自動創建索引，通過下面命令開啟:set optimizer_switch='derived_with_keys=on';

5 ICP (using index condition）索引條件下推  將索引條件推下到inndodb引擎層過濾數據，而不是在mysql/mariadb層過濾。

6 rowid-ordered scan、key-ordered scanMRR(多范圍讀)---先通過索引讀取符合where條件的記錄，然按主鍵排序，再從實際數據文件中讀取其余數據列，減少隨機IO

Mysql 5.6顯示using MRRmairaDB顯示rowid-ordered scan和key-ordered scan其中mysql 5.6的using MRR只相當于rowid-ordered scankey-ordered scan是用于主鍵與連接的查詢中,多表連接中，其中有一個表的連接字段為主鍵，對符合條件的記錄按連接字段進行排序，然后再連接。

開啟方法：set optimizer_switch='mrr=on';set optimizer_switch='mrr_sort_keys=on';set join_cache_level=8;

rowid-order scan: 從驅動表讀取記錄，再按連接列讀取被驅動表,在讀取被驅動表的索引后，根據索引的rowid(myisam或aria引擎)或主鍵(xtradb或innodb)進行排序，再根據排序好的順序讀取被驅動表的記錄。key_order scan: 從驅動表讀取記錄，再對連接列進行排序，再根據排序順序讀取被驅動表，被驅動表是xtradb或innodb引擎且主鍵為連接列

set optimizer_switch='mrr=on';set optimizer_switch='mrr_sort_keys=on';set join_cache_level=8;set optimizer_switch='join_cache_hashed=on'; ---開啟hash joinset optimizer_switch='join_cache_bka=on'; --開啟bka (批量主鍵訪問連接)

bka (批量主鍵訪問連接):先讀取驅動表，再將所需數據列與連接列存儲到連接緩沖，當填滿時，會將連接緩沖的內容傳送給多范圍讀引擎

基于rowid-order scan和key-ordered scan，在bka中同時出現.

mrr大小由mrr_bffer_size決定，bak中的mrr是由join_buffer_size

相關狀態變量：show status like 'Handler_mrr%';

只讀取少量記錄時，使用mrr性能反而下降，使用mrr后，可能排序無法用上索引。

索引合并  index_merge

using union:并運算，or

using sort_union

using intersect :交集，and

using sort_intersect

set optimizer_switch='index_merge_sort_intersection=on';默認為off

inner join的結果取決于inner表

outer join的結果取決于outter表

left outer join左邊的表為外表(驅動表)，right outer join右邊的表為外表(驅動表)。

連接算法：簡單嵌套循環塊嵌套循環塊嵌套循環散列塊索引(bka)塊索引散列(bkah)

join_cache_incremental 、join_cache_hashed、join_cache_bka  默認都有打開，通過optimizer_switch

join_cache_level（8種取值 1-8)

join_cache_space_limit--限制處理查詢時最大可分配的連接緩沖的最大大小set optimizer_switch='optimize_join_buffer_size=on';

簡單嵌套循環:利用索引，大部分是這種連接方式塊嵌套循環與簡單嵌套循環最大不同是在于使用連接緩存，讀取驅動表數據放到join_buffer中 using join buffer塊嵌套循環散列:塊表示使用連接緩存，嵌套循環表示可能會多次反復執行創建(建立hash表)與探測(掃描、計算與檢索)階段。用于數據量大的情況。塊索引連接(批量主鍵訪問連接 bka)---先讀取驅動表，再將所需數據列與連接列存儲到連接緩沖，連接緩沖填滿時，就會將連接緩沖中的內容傳送給"多范圍讀"引擎。rowid-order scankey-order scan兩種結合+bkah塊索引散列(bkah)--使用多范圍讀來讀取驅動表，生成hash表;讀取被驅動表，并返回結果。

哪些情況適用于半連接優化：1.查詢中使用in或=ANY形式的條件時2.子查詢不帶有聚合函數與having子句時3.子查詢不包含union的單一select時4.子查詢的where條件與外部查詢的其它條件通過and運算符進行連接時5.子查詢不是使用連接的update或delete語句時6.不使用事先制定的執行計劃時7.外部查詢與子查詢都使用實際存在的數據表時8.外部查詢與子查詢都不使用straight_join提示時

1.Table pullout優化 就是將子查詢改成表連接的優化，執行計劃中id值相同，extra字段未顯示任何信息，最好方法是查看explain extend....  然后show warnings查看

子查詢使用unique或主鍵檢索結果只有一條時，子查詢的表會作為外表(驅動表)

沒有單獨參數關閉，要關閉只能將semijoin關閉。

2.firstmatch優化執行計劃中id相同，主表為外表(驅動表),extra有firstmatch(主表名)，只要檢測到子查詢第一個符合條件的記錄，就不再繼續檢索。無法用于group by或聚合函數的子查詢優化.

optimizer_switch中的semijoin和fristmatch默認為ON

3.materialization優化

將子查詢全部具體化.具體化后的臨時表在連接中用作被驅動表，并且使用distinct_key進行檢索經，叫materialization-lookup，具體化后的臨時表在連接中用作驅動表，并且進行全表掃描，稱為materializtion-scan.限制：必需不是關聯子查詢，可以用于group by或聚合函數的子查詢，具體化情形下使用內部臨時表

optimizer_switch中的semijoin和materialization默認為ON.執行計劃中的select_type為materialized

4 loosescan(m..n)--用于in子查詢中可能產生重復記錄時先使用using index scan訪問方法讀取子查詢內容，然后刪除重復記錄時，要使用lossescan優化方法，不需要使用臨時表。使用方法：  set optimizer_switch=default;  set optimizer_switch='firstmatch=off';  set optimizer_switch='materialization=off';

執行計劃中有loosescan

5.duplicate weedout優化半連接子查詢轉換為一般inner join查詢執行，最后再刪除重復記錄，可改寫成inner join+group by

關聯子查詢也可用這種優化方法，不用group by 或聚合函數，執行計劃中有start temporary和end temporary

將連接的記錄放在臨時表，然后刪除重復記錄

使用方法：  set optimizer_switch='loosescan=off'  set optimizer_switch='firstmatch=off';  set optimizer_switch='materialization=off';

非半連接子查詢的優化：

materialization

in-to-exists(show warnings查看)