一）基本內(nèi)容
1. 查看
- desc ...
- explain ...
2. 計(jì)劃類(lèi)型【參數(shù)】
mysql5.6:
desc extended | format=traditional ...
--extended 查看優(yōu)化過(guò)的SQL語(yǔ)句mysql5.7:
desc extended | format=traditional ...mysql8.0:
desc format=tree ...
--tree 因?yàn)閔ash,所以用tree 可以客觀(guān)體現(xiàn) join方式
show warnings\G;
3. 執(zhí)行計(jì)劃關(guān)鍵詞
- id： --單純的查詢(xún)，尖都是從1開(kāi)始烫扼，子查詢(xún)都會(huì)使id遞增曙求。
- select_type： --simple沒(méi)有關(guān)聯(lián)查詢(xún)
- type： --
- possible_keys：--可用的索引
- key： --使用的索引
- ref: --驅(qū)動(dòng)表的關(guān)聯(lián)字段
- rows： --預(yù)估行數(shù)
- filtered： -- 100是沒(méi)有二次過(guò)濾
- extra： --
4. 驅(qū)動(dòng)表：
選擇驅(qū)動(dòng)表時(shí)，應(yīng)選結(jié)果集小映企，因?yàn)殡p循環(huán)外循環(huán)（驅(qū)動(dòng)表為外循環(huán)）次數(shù)越少悟狱，越省資源，進(jìn)而查詢(xún)速度也相對(duì)快些卑吭。
驅(qū)動(dòng)表是跟最后一個(gè)join開(kāi)始執(zhí)行芽淡，然后倒數(shù)第二個(gè)，倒數(shù)第三個(gè)豆赏。。富稻。以此類(lèi)推掷邦。
二）select_type
1. simple:--不使用union或者subquery的簡(jiǎn)單query。
-
mysql5.7之后有 derived_merge
- 查：show variables like '%opt%'; optimizer_search_switch:derived_merge=on--子查詢(xún)合并（視圖合并）
- 關(guān)閉：set session optimizer_search_switch='derived_merge=off';
limit:--用在優(yōu)化時(shí)椭赋，使用時(shí)標(biāo)量子查詢(xún)視圖不合并情況之一抚岗。
2. subquery:標(biāo)量子查詢(xún)，位于from之前哪怔。
- 標(biāo)量子查詢(xún)宣蔚，跟外部表沒(méi)啥關(guān)聯(lián)向抢，相當(dāng)于一個(gè)常數(shù)。
- 子查詢(xún)不要嵌套子查詢(xún)胚委，影響性能挟鸠。
3. dependent subquery（標(biāo)量子查詢(xún)，子查詢(xún)都有可能亩冬。特別注意艘希，表的別名不要重復(fù)。）
- 必須依附于外面的值
- scala subquery（標(biāo)量子查詢(xún) ）
- exists
- mysql5.7及之前 depentend subquery
- mysql8.0.16之后的版本硅急，exists的語(yǔ)句會(huì)轉(zhuǎn)換成in,執(zhí)行計(jì)劃為simple覆享，如何使exists不改寫(xiě)為in，使執(zhí)行計(jì)劃為depentend subquery?
select straight_join * from t1 exists (select 1 from t2 where t1.id=t2.id);
4. primary:
- 用union
- 或者用subquery
5. union:
- union result去重復(fù)值
- 生成臨時(shí)表
- max_heap_table_size
- tmp_table_size
查詢(xún)時(shí) 使用的是union营袜。mysql5.6版本union all操作也去重撒顿。
6. union all:是union all操作，不去重荚板。
- 5.6 union result 產(chǎn)生臨時(shí)表
- 5.7 不產(chǎn)生臨時(shí)表
7. derived：派生表凤壁，位于from后 子查詢(xún)。
-
derived是生成在內(nèi)存或臨時(shí)表空間中的啸驯。
- max_heap_table_size
- tmp_table_size
derived 當(dāng)作驅(qū)動(dòng)表時(shí)客扎，要點(diǎn)是要減少數(shù)據(jù)量為目的。
derived 當(dāng)作被驅(qū)動(dòng)表時(shí)罚斗，產(chǎn)生auto_key索引（當(dāng)條件字段大于767時(shí)徙鱼，不產(chǎn)生auto_key），也要以減少數(shù)據(jù)量為目的针姿。
-
不視圖合并的情況：
- union/union all
from (select ...from ... union select ... from ...) - group
from (select ...from... group by ) - distinct
from (select distinct(xxx) from ...) - 聚合函數(shù)
from (select min(x),max(x),sum(x)... from...) - limit
from (select... from ... limit ...) - @
- select c1,(select @a:=0) x from ...
- union/union all
8. materialized：semi join,產(chǎn)生auto_key
- 使用in的時(shí)候
- 使用exists的時(shí)候 袱吆？？有可能
- 用hint強(qiáng)制使用materialized
select /*+ semi join(@sub materialization) / * from t1 where t1.c1 in(select /+ QB_NAME(sub) */) c2 from t2);
三）type
1. const
- 出現(xiàn)在單表距淫、驅(qū)動(dòng)表的pk绞绒、unique， 返回一條數(shù)據(jù)榕暇。
2. eq_ref
- join時(shí)蓬衡，被驅(qū)動(dòng)表連接條件有PK或unique時(shí)。
3. ref
- 索引列作為 "=" 值條件運(yùn)算時(shí)
- 當(dāng)有聯(lián)合索引跟單列索引時(shí)彤枢，可能會(huì)錯(cuò)用索引狰晚，可以用 force index(xx)解決
4. range
- 范圍查詢(xún)，只能用到驅(qū)動(dòng)表
5. index 絕大部分情況下是優(yōu)化對(duì)象缴啡，相當(dāng)于索引全掃描
- 不能使用range const ref時(shí)壁晒；
- 只查詢(xún)索引列，不回表時(shí)业栅；
- 使用索引進(jìn)行排序或聚合
- 聯(lián)合索引中秒咐，前導(dǎo)列不在where條件中谬晕，且查詢(xún)列在索引中。
- group by 字段 在索引或pk中
6. all
- 結(jié)果集超過(guò)一半的量的查詢(xún)效果更好携取。
- 沒(méi)有索引時(shí)攒钳，或類(lèi)型轉(zhuǎn)換時(shí)。
- like 字符 可走索引
- c1 like 'li%';
- all 關(guān)鍵字在執(zhí)行計(jì)劃中的位置越往下歹茶，出問(wèn)題的情況越高夕玩。
四）possible_keys：可用的索引
- 列出所有的索引
- 列出auto_key
- 當(dāng)子查詢(xún)當(dāng)作被驅(qū)動(dòng)表時(shí)，字段長(zhǎng)度不超過(guò)767時(shí)惊豺，自動(dòng)生成auto_key燎孟。
- auto_key 在臨時(shí)表，控制臨時(shí)表相關(guān)參數(shù)：
- tmp_table_size
- max_heap_table_size
五）key：實(shí)際使用的索引
- 查索引 show index from tb_name
- cardinality 去掉重復(fù)值的預(yù)估行數(shù)尸昧，參數(shù)：innodb_stats_persistent_sample_pages
- visible
六）key_len 使用到的索引長(zhǎng)度
七）ref: --驅(qū)動(dòng)表的關(guān)聯(lián)字段
八）rows： --預(yù)估行數(shù)
九）filtered：二次過(guò)濾預(yù)估值揩页，從引擎拿到數(shù)據(jù)再加工的比率，100是沒(méi)有二次過(guò)濾烹俗。
九）extra：
1. distinct
- join 中 兩表關(guān)聯(lián)爆侣，有一條符合記錄即停止，有點(diǎn)像semi join
- 條件：
- select 必須有distinct 關(guān)鍵字
- select 只能含有驅(qū)動(dòng)表字段
- 可以使用straight_join強(qiáng)制第一個(gè)表為驅(qū)動(dòng)表
2. select tables optimized away
- 8.0之前 聚合函數(shù)出現(xiàn)時(shí)
3. using filesort
- 進(jìn)行order by ,group by(8.0之后不排序) 且沒(méi)使用索引時(shí)幢妄；
- order by 時(shí)兔仰，select的列都會(huì)寫(xiě)入臨時(shí)表空間
4. using index
- 只使用索引，不回表
- 一種思路蕉鸳，可把常用列創(chuàng)建索引乎赴，達(dá)到垂直分表的效果。
5. using temporary
- 中間結(jié)果會(huì)使用臨時(shí)表,但無(wú)法判斷在內(nèi)存中潮尝，還是硬盤(pán)中榕吼。（設(shè)置參數(shù) temp_table_max_ram=1 測(cè)試）
- order by、group by 時(shí)勉失；
- 產(chǎn)生derived(派生表)時(shí)
6. using where
- 從存儲(chǔ)引擎中拿到一些數(shù)據(jù)羹蚣，然后再過(guò)濾。
7. using index condition（減少回表量）
- 必須二級(jí)索引乱凿，且有一部分無(wú)法使用索引時(shí)顽素。
- set session optimize_switch="index_condition_pushdown=on"
8. using mrr
- 二級(jí)索引取得PK之后，對(duì)PK進(jìn)行排序徒蟆，減少隨機(jī)IO戈抄。
- set session optimize_switch='mrr_cost_based=off'
9. range checked for each record
- 這時(shí)type肯定是all,沒(méi)用上索引
10. using join buffer:(block nested loop)
- 大部分情況是優(yōu)化對(duì)象
- 適用于被驅(qū)動(dòng)表沒(méi)有索引時(shí)且數(shù)據(jù)量較小時(shí)。
- set session optimizer_switch='block_nested_loop=on'
