統(tǒng)計信息只包括記錄條數(shù)和索引唯一值個數(shù)

永久統(tǒng)計信息：MySQL 5.6show index stats或查看表innodb_table_stats和innodb_index_stats

create table   engine=innodb stats_persistent={DEFAULT|0|1}

為1表示統(tǒng)計信息保存在上面兩個表中，為0表示不保存，用5.5前的管理方式，default時由下面參數(shù)來決定.

show global variables like 'innodb_stats_persistent';---由這個參數(shù)控制，默認(rèn)為on，表示

每當(dāng)遇到下列情形時，會重新收集：新打開數(shù)據(jù)表時;大量修改表的記錄時（在數(shù)據(jù)表的全部記錄中執(zhí)行1/16的update、insert、delete時)執(zhí)行analyze tables時執(zhí)行show table status或show index from命令時激活innodb監(jiān)視器時

innodb_stats_on_metadata設(shè)置為on并執(zhí)行show table status時

建表時,stats_auto_recalc為1表示自動收集  為0表示只能用analyze table收集，default時由參數(shù) innodb_stats_auto_recalc（默認(rèn)為on）值決定。innodb_stats_persistent_sample_pages參數(shù)默認(rèn)為20，表示執(zhí)行analyze table時取20個頁面收集，并將結(jié)果保存為永久統(tǒng)計信息innodb_stats_transient_sample_pages 默認(rèn)為8個，取8個頁塊，分析結(jié)果作為統(tǒng)計信息mariadb:

使用綜合統(tǒng)計信息：table_stats、index_stats、column_stats

當(dāng)use_stat_tables 為never時，使用永久統(tǒng)計信息為PReferably時，使用綜合統(tǒng)計信息為complementary，優(yōu)先使用各引擎的統(tǒng)計信息，信息不足時，才使用綜合信息;當(dāng)use_stat_tables 為never時，執(zhí)行analyze table，只收集存儲引擎的統(tǒng)計信息，為preferably或complementary時，才會同時收集引擎和綜合統(tǒng)計信息。

analyze table用法：analyze table tb1 persistent for columns (col1,col2) indexes (idx1,idx2);analyze table tb1 persistent for columns (col1,col2) indexes ();analyze table tb1 persistent for columns () indexes (idx1,idx2);analyze table tb1 persistent for columns () indexes ();analyze table tb1 persistent for all;

直方圖： 高度均衡直方圖：先對所有列值 進(jìn)行排序，再按相同的記錄個數(shù)將其劃分為幾個組，然后取各組的最后一個值（排序后的最大值)保存到直方圖

histogram_size默認(rèn)為0，表示不使用直方圖

使用直方圖：set histogram_size=20;set use_stat_tables='preferably';set histogram_type='double_prec_hb';

優(yōu)化器連接優(yōu)化參數(shù)：

optimizer_prune_level  默認(rèn)為1，表示用Greedy檢索算法，為0表示用exhaustive檢索算法(n!種執(zhí)行計劃選擇最優(yōu)的一個)。

optimizer_search_depth=64  對greedy算法有用

查看具體時間使用：set profiling=1select ...show profiles;show profile cpu for query 1;

set session  optimizer_search_depth=10   ---當(dāng)表比較多時，可以降低這個值 。

select_type列：

simple:需要union或子查詢是的selectprimary:需要union或含有子查詢執(zhí)行計劃中，位于最外層的查詢union:由union組合成的查詢中，除第一個外，第二個以后的所有查詢都為union,由于有多個查詢用union或union all進(jìn)行聯(lián)合，并創(chuàng)建臨時表進(jìn)行使用，所以union的第一個查詢?yōu)镈ERIVED(

派生）,若是在from后的第一個為派生如：select * from a where ...uinon select * from b where ...dependent union和dependent subquery:當(dāng)union作為子查詢時，其中第二個union的select_type就是DEPENDENT UNION。第一個子查詢的select_type則是DEPENDENT SUBQUERY。當(dāng)含 有DEPENDENT關(guān)鍵字的子查詢時，外部查詢先執(zhí)行，子查詢后執(zhí)行,一般這種性能比較低.dependent 表示union或union all受單位查詢外部影響 ，若子查詢需要使用外部查詢中定義的列，則為dependent subquery.

如：select * from a where a.id in (select b.id from B where ....union select c.id from C where ....)

union result:包含union結(jié)果的數(shù)據(jù)表(臨時表)，因為不是一個查詢，所以沒有id值 。

subquery:除指from子句以外的子查詢derived:需要創(chuàng)建臨時表,from子句的子查詢

uncacheable subquery:  subquery:不受外層查詢影響 ，只執(zhí)行一次，將結(jié)果緩存，后面每次使用緩存中的結(jié)果。  dependent subquery:依賴外部查詢列值為單位進(jìn)行緩存

  下面三種無法使用緩存：  a.子查詢含有用戶變更時，b.子查詢含 用OT-DETERMINISTIC屬性的存儲列程時  c.子查詢使用每次調(diào)用有不同結(jié)果值的函數(shù)時(如uuid()或rand())

materialized:用于優(yōu)化from子句或in查詢中的子查詢，子查詢內(nèi)容具體化為臨時表，與derived類似.

table列：<derived N> <union M,N>，表示臨時表，數(shù)字表示執(zhí)行計劃中的idmaterialized執(zhí)行計劃中table列會出現(xiàn)<subquery N>，表示將子查詢結(jié)果具體化為臨時表.

from子句的子查詢必需要有別名。

type列：就是訪問方式

1.system --訪問僅有1條記錄或沒有記錄的空表時，只適應(yīng)myisam或memory引擎表中2.const --通過主鍵或唯一鍵等值條件訪問數(shù)據(jù)，有且只返回一行記錄，相當(dāng)于唯一索引掃描，常量化處理3.eq_ref --多表連接中，第一次讀取的表列值，作為讀取第二張表的主鍵或唯一鍵等值檢索條件，第二張表只返回一條記錄4.ref --使用等值條件檢索，不保證返回一條記錄。

執(zhí)行計劃中ref列中的const表示條件中的值是常量

5.fulltext---match ..against...條件查詢6.ref_or_null---是ref和is null的組合7.unique_subquery --in查詢中，in后面不返回重復(fù)值，所以不需要刪除重復(fù)值8.index_subquery --in查詢或in（常數(shù)列表)，in后面可能返回重復(fù)值 ，使用索引刪除重復(fù)值9.range ---范圍掃描10.index_merge ---使用兩個以上的索引獲得結(jié)果后，再將其進(jìn)行合并11.index --全索引掃描,排序時加上limit，獲得較好的性能12.all ---全表掃描

key_len列：1.可以根據(jù)執(zhí)行計劃的ken_len來判斷復(fù)合索引中字段是否有使用，計算規(guī)則如下： 當(dāng)索引字段為定長數(shù)據(jù)類型，比如char，int，datetime，需要有是否為空的標(biāo)記，這個標(biāo)記需要占用1個字節(jié)；對于變長數(shù)據(jù)類型，比如：varchar，除了是否為空的標(biāo)記外，還需要有長度信息，需要占用2個字節(jié)；同時還需要考慮表所使用的字符集，不同的字符集，gbk編碼的為一個字符2個字節(jié)，utf8編碼的一個字符3個字節(jié)，latin為1字節(jié)2.單表排序時，order by后面字段順序需要與復(fù)合索引中字段順序一致時，才能用索引，符合索引的最左原則，且select后面字段需為索引字段的部分或全部。3.order by 或group by 的字段不參與key_len長度計算。

integer類型：4字節(jié)date類型：3字節(jié)  若同時定義了nullable列，則需加1為4

ref列：提供了哪種值作為參考條件,常數(shù)值顯示為const，若為其它表的列值，則顯示數(shù)據(jù)表名或列名

當(dāng)為func時，表示經(jīng)過排序或運(yùn)算變換

rows:指處理查詢時要從磁盤讀取與檢查多少條記錄，不是返回記錄數(shù)，估計值。

extra列： 1.const row not found --雖然使用const訪問方式讀取了數(shù)據(jù)表，但若實際數(shù)據(jù)表中沒有1條符合條件的記錄，建議先向表中存儲適當(dāng)?shù)臏y試數(shù)據(jù)，再查看執(zhí)行計劃.認(rèn)2.distinct--表示去重3.full scan on null key ---出現(xiàn)在col1 in (select col2 from ...)時，當(dāng)col1為null時，就會發(fā)生全表掃描，建議在定義col1為非空或sql中加入where col1 is not null來避免。一4.impossible having 不存在滿足having子句條件的記錄時，會顯示impossible having，出現(xiàn)這個，表示sql編寫錯誤5.impossible where --同impissible having一樣6.impossible where noticed after reading const tables --- 讀數(shù)據(jù)表后，發(fā)現(xiàn)記錄不存在7.no matching min/max row ---查詢中有mix和max，但沒有符合條件的記錄，返回空值 。8.no matching row in const table ---以const方式訪問連接中的數(shù)據(jù)表時，不存在符合條件的記錄.9.no tables used --不帶有from子句的查詢或from dual查詢的計劃中10.not exists ---使用外部連接進(jìn)行反連接的查詢中的extra會出現(xiàn)not exists11.range checked for each record(index map:n) ---在連接兩個表的查詢中，連接條件不是常量，而兩個變量或兩個字段，如where e2.emp_no>e1.emp_no  index map:n中的n為16進(jìn)制，換成二進(jìn)制，對應(yīng)位置為1表示備選索引在表中出現(xiàn)順序.12.scanned N database ---查看information_schema庫的信息時，才會有13. select tables optimizer away ---select只使用min()或max()，或者用group by訪問min或max時,若無法使用合適的索引，就會按升序或降序只讀取一個索引。14.unique row not found--兩個表的唯一鍵(含主鍵)執(zhí)行外部連接查詢，若外連接表中不存在一樣的記錄。15.using filesort ---當(dāng)沒有索引時，就會出現(xiàn)，先讀取數(shù)到排序緩存區(qū)，排序后返回到客戶端16 using index ---只讀取索引內(nèi)容，覆蓋索引17 using index for group-by --先用索引排序，然后再分組18 using join buffer(block nested loop)、using join buffer(batched key access) 讀取驅(qū)動的表的記錄放到臨時的內(nèi)存空間(join_buffer_size)19 using sort_union、using union、using intersect、using sort_intersect  都是index_merge操作的四種方式  using sort_union和using union是or操作,后兩個是and操作  有sort的表示先排序操作20 using temporary  臨時表使用21 use where ---表示mysql層進(jìn)行數(shù)據(jù)加工和過濾處理22 using where with pushed condition ---適合NDB引擎，條件下推23 deleting all rows ---刪除所有記錄24 FirstMatch(tbl_name)---是將in子查詢轉(zhuǎn)換成exists的優(yōu)化方式，tbl_name為外部表，表示從外部表讀一條記錄，再從子查詢檢索記錄，直到匹配到記錄為止若子查詢中一次就可找到符合條件的記錄，則執(zhí)行代價非常小，若不存在任何符合條件的記錄，則執(zhí)行代價變得非常高。mairadb 10采用是materialized優(yōu)化25 loosescan(m..n)--用于in子查詢中可能產(chǎn)生重復(fù)記錄時先使用using index scan訪問方法讀取子查詢內(nèi)容，然后刪除重復(fù)記錄時，要使用lossescan優(yōu)化方法，不需要使用臨時表。使用方法：  set optimizer_switch=default;  set optimizer_switch='firstmatch=off';  set optimizer_switch='materialization=off';26 materialize、scan mysql 5.6和mariadb 10前幾個版本的extra會顯示，后期版本沒有顯示，但會在select_type中顯示materialized，顯示scan，表示不為具體化內(nèi)容創(chuàng)建索引，走全表掃描，不顯示scan表示為具體化的臨時表建索引，并將其用作lookup27 start temporary、end temporary優(yōu)化子查詢先訪問子查詢，然后將與外部查詢數(shù)據(jù)表連接后的結(jié)果存儲到臨時表，之后再刪除重復(fù)記錄。子查詢表在執(zhí)行計劃中的extra顯示start temporay，外部表顯示end temporary。28 using index condition索引條件下推

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

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

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

4.3.11 explain extended(filtered列) filtered列表示過濾后剩下的記錄數(shù)占讀取總記錄數(shù)的百分比，估計值

4.3.12 explain extended(附加優(yōu)化器信息)show warnings 查看優(yōu)化器分析重組后的查詢語句

4.3.13 explain partitions,分增加partitions列，顯示查詢使用的分區(qū)目錄

4.4優(yōu)化器提示4.4.1 提示使用方法create /*!32302 temporary */  table ...當(dāng)版本大于等于3.23.02時，執(zhí)行create tmporary table，低于此版本，執(zhí)行create table..主版本取第一位數(shù)，次版本號取兩位，修訂版本號取兩位。

4.4.2 straight_join

select straight_join ...或select /*!straight_join*/ ....

強(qiáng)制指定from后表按順序連接。

或直接將inner join 改成straight_join

適用于普通表與臨時表之間、普通表與普通表之間、臨時表與臨時表之間，這里臨時表是批派生表或內(nèi)嵌視圖

4.4.3 use index/force index/ignore index在表名或表別名后加use index()

4.4.4sql_cache/sql_no_cache

與query_cache_type設(shè)置有關(guān)，無提示時，只有等于1(on)時才緩存;sql_cache時，等于1(on)或2(demand)時才緩存sql_no_cache時，都不緩存

若能選擇性的工作，當(dāng)設(shè)置為2，再加上sql_cache提示時即可，4.4.5 sql_calc_found_rows

當(dāng)加了這個提示時，mairadb查找到limit指定條數(shù)的記錄之后，還會繼續(xù)檢索，最終還是會返回limit指定記錄數(shù).用found_rows()返回符合條件的記錄數(shù)。

4.5 分析執(zhí)行計劃需要注意的事項

4.5.1 select_typederived，注意臨時表是否在磁盤中dependent subquery --去除子查詢對外部查詢的依賴。

4.5.2 type列all和index

4.5.3 key列為空時，注意增加索引4.5.4 rows列rows列不受limit限制若值比實際的記錄數(shù)多時，檢查索引使用情況4.5.5 extra列

查詢條件執(zhí)行計劃不佳:range checked for each record(indedx map:N)using filesortusing join bufferusing temporaryusing where