mysql執(zhí)行計(jì)劃
在企業(yè)的應(yīng)用場(chǎng)景中江解，為了知道優(yōu)化SQL語(yǔ)句的執(zhí)行设预，需要查看SQL語(yǔ)句的具體執(zhí)行過(guò)程鳖枕，以加快SQL語(yǔ)句的執(zhí)行效率。
可以使用explain+SQL語(yǔ)句來(lái)模擬優(yōu)化器執(zhí)行SQL查詢(xún)語(yǔ)句灭翔，從而知道m(xù)ysql是如何處理sql語(yǔ)句的魏烫。
官網(wǎng)地址： https://dev.mysql.com/doc/refman/5.5/en/explain-output.html
1、執(zhí)行計(jì)劃中包含的信息
| Column | Meaning |
|---|---|
| id | The SELECT identifier |
| select_type | The SELECT type |
| table | The table for the output row |
| partitions | The matching partitions |
| type | The join type |
| possible_keys | The possible indexes to choose |
| key | The index actually chosen |
| key_len | The length of the chosen key |
| ref | The columns compared to the index |
| rows | Estimate of rows to be examined |
| filtered | Percentage of rows filtered by table condition |
| extra | Additional information |
id
select查詢(xún)的序列號(hào)肝箱，包含一組數(shù)字哄褒，表示查詢(xún)中執(zhí)行select子句或者操作表的順序
id號(hào)分為三種情況：
1、如果id相同狭园，那么執(zhí)行順序從上到下
2读处、如果id不同，如果是子查詢(xún)唱矛，id的序號(hào)會(huì)遞增罚舱，id值越大優(yōu)先級(jí)越高，越先被執(zhí)行
3绎谦、id相同和不同的管闷，同時(shí)存在：相同的可以認(rèn)為是一組，從上往下順序執(zhí)行窃肠，在所有組中包个，id值越大，優(yōu)先級(jí)越高冤留，越先執(zhí)行
select_type
主要用來(lái)分辨查詢(xún)的類(lèi)型碧囊，是普通查詢(xún)還是聯(lián)合查詢(xún)還是子查詢(xún)
select_type Value |
Meaning |
|---|---|
| SIMPLE | Simple SELECT (not using UNION or subqueries) |
| PRIMARY | Outermost SELECT |
| UNION | Second or later SELECT statement in a UNION |
| DEPENDENT UNION | Second or later SELECT statement in a UNION, dependent on outer query |
| UNION RESULT | Result of a UNION. |
| SUBQUERY | First SELECT in subquery |
| DEPENDENT SUBQUERY | First SELECT in subquery, dependent on outer query |
| DERIVED | Derived table |
| UNCACHEABLE SUBQUERY | A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query |
| UNCACHEABLE UNION | The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY) |
<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="sql" cid="n91" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 30px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">--sample:簡(jiǎn)單的查詢(xún)，不包含子查詢(xún)和union
explain select * from emp;
--primary:查詢(xún)中若包含任何復(fù)雜的子查詢(xún)纤怒，最外層查詢(xún)則被標(biāo)記為Primary
explain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;
--union:若第二個(gè)select出現(xiàn)在union之后趁啸，則被標(biāo)記為union
explain select * from emp where deptno = 10 union select * from emp where sal >2000;
--dependent union:跟union類(lèi)似迅脐，此處的depentent表示union或union all聯(lián)合而成的結(jié)果會(huì)受外部表影響
explain select * from emp e where e.empno in ( select empno from emp where deptno = 10 union select empno from emp where sal >2000)
--union result:從union表獲取結(jié)果的select
explain select * from emp where deptno = 10 union select * from emp where sal >2000;
--subquery:在select或者where列表中包含子查詢(xún)
explain select * from emp where sal > (select avg(sal) from emp) ;
--dependent subquery:subquery的子查詢(xún)要受到外部表查詢(xún)的影響
explain select * from emp e where e.deptno in (select distinct deptno from dept);
--DERIVED: from子句中出現(xiàn)的子查詢(xún)雄卷，也叫做派生類(lèi)优床，
explain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;
--UNCACHEABLE SUBQUERY：表示使用子查詢(xún)的結(jié)果不能被緩存
explain select * from emp where empno = (select empno from emp where deptno=@@sort_buffer_size);
--uncacheable union:表示union的查詢(xún)結(jié)果不能被緩存：sql語(yǔ)句未驗(yàn)證</pre>
table
對(duì)應(yīng)行正在訪(fǎng)問(wèn)哪一個(gè)表像寒，表名或者別名，可能是臨時(shí)表或者union合并結(jié)果集 1瓜贾、如果是具體的表名诺祸，則表明從實(shí)際的物理表中獲取數(shù)據(jù)，當(dāng)然也可以是表的別名
2祭芦、表名是derivedN的形式筷笨，表示使用了id為N的查詢(xún)產(chǎn)生的衍生表
3、當(dāng)有union result的時(shí)候实束，表名是union n1,n2等的形式奥秆，n1,n2表示參與union的id
type
type顯示的是訪(fǎng)問(wèn)類(lèi)型，訪(fǎng)問(wèn)類(lèi)型表示我是以何種方式去訪(fǎng)問(wèn)我們的數(shù)據(jù)咸灿，最容易想的是全表掃描构订，直接暴力的遍歷一張表去尋找需要的數(shù)據(jù)，效率非常低下避矢，訪(fǎng)問(wèn)的類(lèi)型有很多悼瘾，效率從最好到最壞依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般情況下，得保證查詢(xún)至少達(dá)到range級(jí)別审胸，最好能達(dá)到ref
<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="sql" cid="n100" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 30px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">--all:全表掃描亥宿，一般情況下出現(xiàn)這樣的sql語(yǔ)句而且數(shù)據(jù)量比較大的話(huà)那么就需要進(jìn)行優(yōu)化。
explain select * from emp;
--index：全索引掃描這個(gè)比all的效率要好砂沛，主要有兩種情況烫扼，一種是當(dāng)前的查詢(xún)時(shí)覆蓋索引，即我們需要的數(shù)據(jù)在索引中就可以索取碍庵，或者是使用了索引進(jìn)行排序映企，這樣就避免數(shù)據(jù)的重排序
explain select empno from emp;
--range：表示利用索引查詢(xún)的時(shí)候限制了范圍，在指定范圍內(nèi)進(jìn)行查詢(xún)静浴，這樣避免了index的全索引掃描堰氓，適用的操作符： =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN()
explain select * from emp where empno between 7000 and 7500;
--index_subquery：利用索引來(lái)關(guān)聯(lián)子查詢(xún)，不再掃描全表
explain select * from emp where emp.job in (select job from t_job);
--unique_subquery:該連接類(lèi)型類(lèi)似與index_subquery,使用的是唯一索引
explain select * from emp e where e.deptno in (select distinct deptno from dept);
--index_merge：在查詢(xún)過(guò)程中需要多個(gè)索引組合使用苹享，沒(méi)有模擬出來(lái)
--ref_or_null：對(duì)于某個(gè)字段即需要關(guān)聯(lián)條件双絮，也需要null值的情況下，查詢(xún)優(yōu)化器會(huì)選擇這種訪(fǎng)問(wèn)方式
explain select * from emp e where e.mgr is null or e.mgr=7369;
--ref：使用了非唯一性索引進(jìn)行數(shù)據(jù)的查找
create index idx_3 on emp(deptno);
explain select * from emp e,dept d where e.deptno =d.deptno;
--eq_ref ：使用唯一性索引進(jìn)行數(shù)據(jù)查找
explain select * from emp,emp2 where emp.empno = emp2.empno;
--const：這個(gè)表至多有一個(gè)匹配行得问，
explain select * from emp where empno = 7369;
--system：表只有一行記錄（等于系統(tǒng)表）囤攀，這是const類(lèi)型的特例，平時(shí)不會(huì)出現(xiàn)</pre>
possible_keys
顯示可能應(yīng)用在這張表中的索引宫纬，一個(gè)或多個(gè)抚岗，查詢(xún)涉及到的字段上若存在索引，則該索引將被列出哪怔，但不一定被查詢(xún)實(shí)際使用
<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="sql" cid="n103" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 30px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;</pre>
key
實(shí)際使用的索引宣蔚，如果為null，則沒(méi)有使用索引认境，查詢(xún)中若使用了覆蓋索引胚委，則該索引和查詢(xún)的select字段重疊。
<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="sql" cid="n106" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 30px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;</pre>
key_len
表示索引中使用的字節(jié)數(shù)叉信，可以通過(guò)key_len計(jì)算查詢(xún)中使用的索引長(zhǎng)度亩冬，在不損失精度的情況下長(zhǎng)度越短越好。
<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="sql" cid="n109" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 30px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;</pre>
ref
顯示索引的哪一列被使用了硼身，如果可能的話(huà)硅急，是一個(gè)常數(shù)
<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="sql" cid="n112" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 30px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;</pre>
rows
根據(jù)表的統(tǒng)計(jì)信息及索引使用情況，大致估算出找出所需記錄需要讀取的行數(shù)佳遂，此參數(shù)很重要营袜，直接反應(yīng)的sql找了多少數(shù)據(jù)，在完成目的的情況下越少越好
<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="sql" cid="n115" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 30px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">explain select * from emp;</pre>
extra
包含額外的信息丑罪。
<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="sql" cid="n118" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 30px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">--using filesort:說(shuō)明mysql無(wú)法利用索引進(jìn)行排序荚板，只能利用排序算法進(jìn)行排序，會(huì)消耗額外的位置
explain select * from emp order by sal;
--using temporary:建立臨時(shí)表來(lái)保存中間結(jié)果吩屹，查詢(xún)完成之后把臨時(shí)表刪除
explain select ename,count(*) from emp where deptno = 10 group by ename;
--using index:這個(gè)表示當(dāng)前的查詢(xún)時(shí)覆蓋索引的跪另，直接從索引中讀取數(shù)據(jù)，而不用訪(fǎng)問(wèn)數(shù)據(jù)表煤搜。如果同時(shí)出現(xiàn)using where 表名索引被用來(lái)執(zhí)行索引鍵值的查找免绿，如果沒(méi)有，表面索引被用來(lái)讀取數(shù)據(jù)擦盾，而不是真的查找
explain select deptno,count(*) from emp group by deptno limit 10;
--using where:使用where進(jìn)行條件過(guò)濾
explain select * from t_user where id = 1;
--using join buffer:使用連接緩存嘲驾，情況沒(méi)有模擬出來(lái)
--impossible where：where語(yǔ)句的結(jié)果總是false
explain select * from emp where empno = 7469;</pre>
