大綱

1.什么是執(zhí)行計劃

2.執(zhí)行計劃包含哪些內(nèi)容

3.SQL語句和執(zhí)行計劃的總結(jié)

4.SQL語句使用多個二級索引

5.多表關(guān)聯(lián)的SQL語句如何執(zhí)行

6.全表掃描執(zhí)行計劃的成本計算方法

7.索引的成本計算方法

8.MySQL如何優(yōu)化執(zhí)行計劃

9.explain的參數(shù)說明

1.什么是執(zhí)行計劃

(1)什么是SQL優(yōu)化

一般開發(fā)一個系統(tǒng)，都是先設(shè)計表結(jié)構(gòu)，表結(jié)構(gòu)必須滿足業(yè)務(wù)需求。然后寫代碼，代碼寫完后，根據(jù)代碼是如何查詢表來設(shè)計表的索引，需要考慮設(shè)計幾個索引，選擇哪些字段作索引，是不是聯(lián)合索引，以及如何排列索引字段的順序才能讓查詢語句都用上索引。

普通的SQL優(yōu)化就是設(shè)計好索引，讓不太復(fù)雜的普通查詢都能用上索引。但針對復(fù)雜表結(jié)構(gòu)和大數(shù)據(jù)量的上百行的復(fù)雜的SQL優(yōu)化，必須要理解復(fù)雜SQL是怎么執(zhí)行的，而這就涉及到執(zhí)行計劃。

(2)什么是執(zhí)行計劃

執(zhí)行SQL語句時，面對磁盤上的大量數(shù)據(jù)表、聚簇索引和二級索引：如何檢索查詢、如何篩選過濾、如何使用函數(shù)、如何進(jìn)行排序、如何進(jìn)行分組、怎樣把數(shù)據(jù)按照SQL查出來，這個過程就是執(zhí)行計劃。

也就是說，每次提交一個SQL語句給MySQL。它的查詢優(yōu)化器，都會針對這個SQL語句的語義去生成一個執(zhí)行計劃，這個執(zhí)行計劃會包含如何查各個表、用到哪些索引、如何做排序和分組，一般基于執(zhí)行計劃來進(jìn)行SQL優(yōu)化。

(3)什么是SQL調(diào)優(yōu)

根據(jù)SQL語句執(zhí)行計劃，去想辦法改寫SQL語句，改良表的索引設(shè)計，進(jìn)而優(yōu)化SQL語句的執(zhí)行計劃，最終提升SQL的執(zhí)行性能。

2.執(zhí)行計劃包含哪些內(nèi)容

(1)const

如果可以直接通過聚簇索引或者二級索引+回表聚簇索引，輕松查出數(shù)據(jù)。那么這種根據(jù)主鍵索引就能直接查出數(shù)據(jù)的過程，在執(zhí)行計劃里稱為const。類似如下的SQL，建立的索引是key(id)和key(name)。

mysql>?select?*?from?table?where?id=x;
mysql> select?*?from?table?where?name=x;

執(zhí)行計劃里的count，代表的意思是性能超高的常量級。所以執(zhí)行計劃里出現(xiàn)const，表明SQL會通過索引查詢數(shù)據(jù)，速度極快。

但需要注意的是：二級索引必須是唯一索引才是屬于const方式。即必須建立unique key唯一索引，保證二級索引的每一個值都是唯一的，這時根據(jù)二級索引查詢才是const。

(2)ref

如果查詢時使用的普通的二級索引只有一列，并且不是唯一索引，那么查詢的速度也很快，它在執(zhí)行計劃里叫做ref。類似如下SQL，建立的索引是key(name)。

mysql>?select?*?from?table?where?name=x;

如果查詢時使用的普通的二級索引包含多列，那么必須從索引最左側(cè)開始連續(xù)多個列都是等值比較才是屬于ref方式。類似如下SQL，建立的索引是key(name,age,sex)。

mysql>?select?*?from?table?where?
name=x?and?age=x?and?sex=x;

如果使用name is null這種查詢，即使name是主鍵或者唯一索引，還是只能通過ref方式來查詢。

(3)ref_or_null

如果查詢時使用了普通的二級索引而且還限定了is null，類似如下SQL，那么在執(zhí)行計劃里就是ref_or_null。

mysql> select * from table where 
name=x or name is null;

所以，當(dāng)我們在分析執(zhí)行計劃時：看到了const，表明肯定是通過主鍵/唯一索引訪問的，速度超高?？吹搅藃ef，表明用了普通索引，或主鍵/唯一索引使用is not null?？吹搅藃ef_or_null，表明用了普通索引，而且還限定了is null。

(4)range

如果SQL里面對索引有范圍查詢，那么就會用這個range方式。類似如下SQL語句，假設(shè)age就是一個普通索引，此時必然通過索引來進(jìn)行范圍篩選。一旦利用索引范圍篩選，那么這種方式就是range。

mysql>?select?*?from?table?where?
age?>?x?and?age?<?y;

(5)index

假設(shè)有一個表，里面有個聯(lián)合索引key(x1, x2, x3)，現(xiàn)在有如下SQL語句。這個SQL是沒辦法直接從聯(lián)合索引的索引樹根節(jié)點開始二分查找的。但這個SQL里要查的幾個字段，恰好就是聯(lián)合索引里的幾個字段。

mysql>?select?x1,?x2,?x3?from?table?where?x2=xxx;

因為聚簇索引的葉子節(jié)點放的是完整的數(shù)據(jù)頁，聯(lián)合索引的葉子節(jié)點放的數(shù)據(jù)頁只包含索引字段的值和主鍵的值。所以這種SQL的查詢，會直接遍歷這個聯(lián)合索引的索引樹的葉子節(jié)點。一個個遍歷，找到x2=xxx的那條數(shù)據(jù)。然后把x1,x2,x3三個字段的值提取出來即可，不需要回源到聚簇索引。

遍歷二級索引的過程，要比遍歷聚簇索引快得多。畢竟二級索引葉子節(jié)點只包含幾個字段值，比聚簇索引葉子節(jié)點小很多。

這種只需要遍歷二級索引就可獲取想要查詢的數(shù)據(jù)，而不需要回表到聚簇索引的查詢方式，就叫做index。

(6)all

全表掃描，掃描聚簇索引的所有子節(jié)點。

(7)總結(jié)

const、ref和range，都是基于索引樹進(jìn)行二分查找和多層跳轉(zhuǎn)來查詢的。所以const、ref和range的性能一般都很高，然后index的速度就比前面這三種要差一些，因為index是通過遍歷二級索引的葉子節(jié)點的方式來執(zhí)行，所以index肯定比二分查找慢但比全表掃描好。

3.SQL語句和執(zhí)行計劃的總結(jié)

(1)const、ref和range本質(zhì)都是基于索引查詢

只要索引查出來的數(shù)據(jù)量不是特別大，一般性能都極為高效。

(2)index稍微次一點，需要遍歷某個二級索引

但是因為二級索引比較小，所以遍歷性能也還可以。

(3)最差的就是all，意味著全表掃描

即掃描聚簇索引的所有葉子節(jié)點，一個表一行一行數(shù)據(jù)去掃描。如果數(shù)據(jù)量很大，全表掃描就很危險了。

(4)SQL語句的執(zhí)行計劃案例

案例一：

mysql> select * from table where 
x1 = xxx and x2 > xx;

這個SQL語句要查一個表，用了x1和x2兩個字段。如果給x1和x2建立聯(lián)合索引，那么是可以直接通過索引去掃描的。但如果現(xiàn)在建了只有(x1, x3)和(x2, x4)這兩個聯(lián)合索引，此時MySQL只能選擇其中一個索引去用，會選哪個？這時MySQL負(fù)責(zé)生成執(zhí)行計劃的查詢優(yōu)化器，一般會選擇在索引里掃描行數(shù)比較少的那個。

比如x1 = xx，在索引里只要做等值比較，掃描數(shù)據(jù)比較少。那么可能就會挑選x1的索引，然后基于其索引樹進(jìn)行查找。在執(zhí)行計劃里，對應(yīng)于ref的方式，找到幾條數(shù)據(jù)后再接著進(jìn)行回表?；氐骄鄞厮饕锶ゲ槌雒織l數(shù)據(jù)的完整信息，然后把這些信息加載到內(nèi)存，根據(jù)x2 > xx條件進(jìn)行篩選。

案例二：

mysql> select * from table where 
x1=xx and c1=xx and c2>xx and c3 is not null;

我們經(jīng)常會寫出類似上述這樣的SQL，就是SQL的所有篩選條件里，只有一個x1有索引，其他字段都沒索引。這種情況還是很常見的，因為不可能針對所有SQL的where字段都加索引，我們一般只能抽取部分經(jīng)常在where里用到的字段來設(shè)計兩三個聯(lián)合索引。

這種SQL語句，where后的條件有好幾個，但只有一個字段可用到索引。此時查詢優(yōu)化器生成的執(zhí)行計劃，只會針對x1字段執(zhí)行ref方式的查詢，也就是通過x1字段的索引樹快速找到符合x1=xx的一大堆數(shù)據(jù)。接著會根據(jù)這一大堆數(shù)據(jù)回表到聚簇索引里，查出每條數(shù)據(jù)的完整字段。然后將這些包含完整字段的數(shù)據(jù)加載到內(nèi)存里去。接著就可以在內(nèi)存針對這些數(shù)據(jù)的c1,c2,c3字段按條件進(jìn)行篩選和過濾。最后便可以拿到符合條件的數(shù)據(jù)。

因此為了保證后續(xù)的查詢性能比較高，所以針對x1索引的設(shè)計，需要盡可能讓x1=xx這個條件在索引樹里查找出來的數(shù)據(jù)量比較少。

4.SQL語句使用多個二級索引

一般一個SQL語句只會用到一個二級索引，但是一些特殊的情況下，可能一個SQL語句會用到多個二級索引。比如有SQL語句：

mysql> select?*?from?table?where?x1=xx?and?x2=xx;

其中x1和x2分別有一個索引，查詢優(yōu)化器會生成如下這樣的執(zhí)行計劃：先對x1的索引樹查找出一批數(shù)據(jù)，再對x2的索引樹查找出另一批數(shù)據(jù)，然后兩批數(shù)據(jù)按主鍵值做交集，這個交集就是符合兩個條件的數(shù)據(jù)了，最后再回表到聚簇索引去獲取完整的數(shù)據(jù)。

什么情況下會對兩個字段的兩個索引一起查，然后取交集再回表呢？什么情況下會查多個索引樹呢？

如果同時查兩個索引樹再取交集后的數(shù)據(jù)量很小，那么根據(jù)這少量數(shù)據(jù)回表到聚簇索引查詢，就可以提升性能。所以是否會查多個索引樹的標(biāo)準(zhǔn)是，能否提升性能。因此執(zhí)行計劃里出現(xiàn)了intersection交集、union并集等，意思就是查詢時使用了多個索引，最后對結(jié)果集做交集或并集。

5.多表關(guān)聯(lián)的SQL語句如何執(zhí)行

(1)多表關(guān)聯(lián)的基本原理

如下SQL語句在from后接了兩個表，表示對兩個表的數(shù)據(jù)關(guān)聯(lián)起來查詢。如果多表關(guān)聯(lián)查詢時沒有限定多表連接條件，那么會直接進(jìn)行笛卡爾積。比如"select * from t1,t2;"就會使用笛卡爾積，但一般會加限定關(guān)聯(lián)條件。???????

mysql> select * from t1,t2 where 
t1.x1=xx and t1.x2=t2.x2 and t2.x3=xx;

上面SQL語句的關(guān)聯(lián)條件是"t1.x2=t2.x2"，所以其執(zhí)行過程是：首先根據(jù)t1.x1=xx這個篩選條件去t1表里查詢，可能使用了const、ref、index、all，具體要看索引如何建的。然后將篩選出來的結(jié)果，根據(jù)結(jié)果中x2的值，去t2表查詢，也就是去t2表里查找t2.x2等于這些x2的值以及t2.x3=xx都匹配的數(shù)據(jù)。

這就是多表關(guān)聯(lián)的基本原理，先查的表叫驅(qū)動表，根據(jù)先查出的數(shù)據(jù)再去查的另外一張表叫被驅(qū)動表。

(2)幾種連接

一.內(nèi)連接inner join

兩個表里的數(shù)據(jù)必須是完全能關(guān)聯(lián)上，才能將數(shù)據(jù)返回來。

二.左外連接left join

左側(cè)表的某條數(shù)據(jù)在右側(cè)表關(guān)聯(lián)不到任何數(shù)據(jù)，也把左側(cè)表該數(shù)據(jù)返回。

三.右外連接right join

右側(cè)表的某條數(shù)據(jù)在左側(cè)表關(guān)聯(lián)不到任何數(shù)據(jù)，也把右側(cè)表該數(shù)據(jù)返回。

四.語法限制

如果是內(nèi)連接，那么連接條件可放在where語句里。如果是外連接，那么連接條件需放在on字句里。

(3)嵌套循環(huán)關(guān)聯(lián)

假設(shè)有兩個表要一起執(zhí)行關(guān)聯(lián)，此時會先在一個驅(qū)動表里根據(jù)它的where篩選條件找出一批數(shù)據(jù)。接著對這批數(shù)據(jù)進(jìn)行循環(huán)，用每條數(shù)據(jù)都到另外一個被驅(qū)動表里，根據(jù)ON連接條件和where里的被驅(qū)動表篩選條件去查找數(shù)據(jù)。

假設(shè)從驅(qū)動表找出1000條數(shù)據(jù)，那么就要到被驅(qū)動表查詢1000次。所以很多時候多表關(guān)聯(lián)是很慢的。

因此針對多表查詢的語句，盡量給兩個表都加上索引。索引要確保從驅(qū)動表里查詢是通過驅(qū)動表的索引去查找，接著對被驅(qū)動表查詢也是通過被驅(qū)動表的索引去查找。

6.全表掃描執(zhí)行計劃的成本計算方法

(1)MySQL如何根據(jù)成本估算選擇執(zhí)行計劃

(2)執(zhí)行一個SQL語句的IO成本

(3)執(zhí)行一個SQL語句的CPU成本

(4)評估SQL語句執(zhí)行成本的案例

(1)MySQL如何根據(jù)成本估算選擇執(zhí)行計劃

MySQL在執(zhí)行單表查詢時：對應(yīng)的一些執(zhí)行計劃是諸如const、ref、range、index、all之類的。

MySQL在執(zhí)行多表關(guān)聯(lián)時：本質(zhì)就是先查驅(qū)動表，接著根據(jù)連接條件再去被驅(qū)動表循環(huán)查詢。

MySQL是如何對一個查詢語句的多個執(zhí)行計劃評估成本的？MySQL如何根據(jù)成本評估選擇一個成本最低的執(zhí)行計劃的？

執(zhí)行一個SQL語句的成本一般分成兩部分：IO成本和CPU成本。

(2)執(zhí)行一個SQL語句的IO成本

首先這些數(shù)據(jù)需要從磁盤里讀出來，從磁盤讀數(shù)據(jù)到內(nèi)存就是IO成本。而且MySQL里都是一頁一頁讀的，讀一頁的IO成本約定為1.0。

(3)執(zhí)行一個SQL語句的CPU成本

然后內(nèi)存拿到數(shù)據(jù)后，需要對數(shù)據(jù)進(jìn)行操作，比如驗證是否符合搜索條件或者排序分組等，這些屬于CPU成本。一般約定讀取和檢測一條數(shù)據(jù)是否符合條件的成本是0.2。

(4)評估SQL語句執(zhí)行成本的案例

比如執(zhí)行如下SQL語句：

mysql> select?*?from?t?where?x1=xx?and?x2=xx;

步驟一：假設(shè)該表有兩個索引分別是針對x1和x2建立的，那么MySQL會先看這個SQL可以用到哪幾個索引。由于發(fā)現(xiàn)x1和x2都有可能，于是possible_keys。

步驟二：接著會針對這個SQL計算一下全表掃描的成本，全表掃描需要進(jìn)行磁盤IO把聚簇索引里的葉子節(jié)點上的數(shù)據(jù)頁讀到內(nèi)存，所以磁盤文件上有多少的數(shù)據(jù)頁就會耗費多少的IO成本。然后還需要對內(nèi)存里的每一條數(shù)據(jù)都判斷是否符合搜索條件，讀取到內(nèi)存里有多少條數(shù)據(jù)就需要耗費多少CPU成本。

(5)如何計算執(zhí)行成本

可以使用命令show table status like "表名"拿到表的統(tǒng)計信息。MySQL在對表進(jìn)行增刪改的時候，MySQL會維護(hù)這個表的統(tǒng)計信息。比如rows記錄表的記錄數(shù)，data_length記錄表的聚簇索引的字節(jié)數(shù)大小。

使用data_length除以1024就是KB大小，再除以16就是數(shù)據(jù)頁的數(shù)量。通過估算數(shù)據(jù)頁的數(shù)量和rows記錄數(shù)，就可以計算全表掃描的成本了。

IO成本就是：數(shù)據(jù)頁數(shù)量 * 1.0 + 微調(diào)值；CPU成本就是：行記錄數(shù) * 0.2 + 微調(diào)值；兩者相加就是一個總成本。比如一個表有100個數(shù)據(jù)頁，記錄數(shù)有2萬條。那么執(zhí)行總成本值大致就是 100 + 4000 = 4100。

7.索引的成本計算方法

如果是根據(jù)主鍵查，那么直接通過聚簇索引查詢就可以了。

如果是根據(jù)非主鍵字段查，該字段也建了索引。那么一般會首先從二級索引查一批數(shù)據(jù)，然后再根據(jù)這批數(shù)據(jù)的主鍵去聚簇索引回表查。最后對比全表掃描的估算成本和索引的估算成本，選成本低的執(zhí)行計劃。所以有時候出現(xiàn)不用索引而用全表掃描，就是因為索引的估算成本更高。

8.MySQL如何優(yōu)化執(zhí)行計劃

(1)優(yōu)化SQL語句的清晰語義

(2)子查詢的優(yōu)化

(1)優(yōu)化SQL語句的清晰語義

從而方便后續(xù)在索引和數(shù)據(jù)頁里進(jìn)行查找，比如類似"i=5 and j>i"這樣的會常量替換成"i=5 and j>5"，比如類似"x=y and y=k and k=3"會常量替換成"x=3 and y=3 and k=3"，比如類似"b=b and a=a"這種沒意義的就直接刪掉條件了。

(2)子查詢的優(yōu)化

如下SQL執(zhí)行時會分兩步：先執(zhí)行子查詢，再執(zhí)行select * from t1 where...???????

mysql>?select?*?from?t1?where?
x1=(select?x1?from?t2?where?id?=?xxx);

像上述這種單表查詢可以直接用上索引還好，但有時用不上索引就會基于內(nèi)存或者臨時文件執(zhí)行。

如下SQL會先通過子查詢先查一批結(jié)果，然后判斷t1表里哪些數(shù)據(jù)的x1值在這個結(jié)果集里。

mysql>?select?*?from?t1?where?
x1?in?(select?x2?from?t2?where?x3?=?xxx);

如果先執(zhí)行子查詢，然后對t1表再進(jìn)行全表掃描，而全表掃描會判斷每條數(shù)據(jù)是否在該子查詢的結(jié)果集里，那么效率就會非常低。

因此對于上述子查詢，執(zhí)行計劃會被優(yōu)化為：先執(zhí)行子查詢，然后再把子查詢查出來的數(shù)據(jù)寫入臨時表。臨時表也叫物化表，即把中間結(jié)果集進(jìn)行物化。

這個物化表可能會基于memory存儲引擎來通過內(nèi)存存放。如果結(jié)果集太大，則可能采用普通B+樹聚簇索引的方式放在磁盤里。這個物化表都會建立索引，所以這種中間結(jié)果寫入物化表都是有索引的。

如果t1表的數(shù)據(jù)量很大比如10萬，但物化表結(jié)果集的數(shù)據(jù)量只有500條。那么此時會由全表掃描t1表改成全表掃描物化表，這也是其中一種子查詢的優(yōu)化。

9.explain的參數(shù)說明

(1)id

select查詢的序列號，包含一組數(shù)字，表示查詢中執(zhí)行select子句或者操作表的順序。

(2)select_type

主要用來分辨查詢的類型，是普通查詢還是聯(lián)合查詢還是子查詢。

(3)table

對應(yīng)行正在訪問哪一個表，表名或者別名，可能是臨時表或者union合并結(jié)果集。

(4)type

type顯示的是訪問類型，訪問類型表示以何種方式去訪問數(shù)據(jù)。比如全表掃描，直接遍歷一張表去尋找需要的數(shù)據(jù)，效率非常低下。

訪問的類型有很多，效率從最好到最壞依次是：system?>?const?>?eq_ref?>?ref?>?ref_or_null?>?index_merge?>?unique_subquery?>?index_subquery?>?range?>?index?>?all

一般情況下，得保證查詢至少達(dá)到range級別，最好能達(dá)到ref。

--all:全表掃描，一般情況下出現(xiàn)這樣的sql語句而且數(shù)據(jù)量比較大的話那么就需要進(jìn)行優(yōu)化。
explain select * from emp;--index：全索引掃描這個比all的效率要好；
--主要有兩種情況，一種是當(dāng)前的查詢時覆蓋索引，即我們需要的數(shù)據(jù)在索引中就可以索取，或者是使用了索引進(jìn)行排序，這樣就避免數(shù)據(jù)的重排序
explain  select empno from emp;--range：表示利用索引查詢的時候限制了范圍，在指定范圍內(nèi)進(jìn)行查詢，這樣避免了index的全索引掃描；
--適用的操作符：=, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN() 
explain select * from emp where empno between 7000 and 7500;--index_subquery：利用索引來關(guān)聯(lián)子查詢，不再掃描全表
explain select * from emp where emp.job in (select job from t_job);--unique_subquery:該連接類型類似與index_subquery,使用的是唯一索引 
explain select * from emp e where e.deptno in (select distinct deptno from dept); --index_merge：在查詢過程中需要多個索引組合使用，沒有模擬出來--ref_or_null：對于某個字段即需要關(guān)聯(lián)條件，也需要null值的情況下，查詢優(yōu)化器會選擇這種訪問方式
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：這個表至多有一個匹配行
explain select * from emp where empno = 7369; --system：表只有一行記錄(等于系統(tǒng)表)，這是const類型的特例，平時不會出現(xiàn)

(5)possible_keys

顯示可能應(yīng)用在這張表中的索引，一個或多個。查詢涉及到的字段上若存在索引，則該索引將被列出。但這些列出的索引，不一定會被查詢實際使用。

(6)key

實際使用的索引，如果為null，則沒有使用索引。查詢中若使用了覆蓋索引，則該索引和查詢的select字段重疊。

(7)key_len

表示索引中使用的字節(jié)數(shù)，可以通過key_len計算查詢中使用的索引長度，在不損失精度的情況下長度越短越好。

(8)ref

顯示索引的哪一列被使用了，如果可能的話，是一個常數(shù)。

(9)rows

根據(jù)表的統(tǒng)計信息及索引使用情況，大致估算找出所需記錄要讀取的行數(shù)。此參數(shù)很重要，直接反應(yīng)SQL找了多少數(shù)據(jù)，當(dāng)然其數(shù)值越少越好。

(10)extra

包含額外的信息：

--using filesort:說明mysql無法利用索引進(jìn)行排序，只能利用排序算法進(jìn)行排序，會消耗額外的位置
explain select * from emp order by sal;--using temporary:建立臨時表來保存中間結(jié)果，查詢完成之后把臨時表刪除
explain select ename,count(*) from emp where deptno = 10 group by ename;--using index:這個表示當(dāng)前的查詢時覆蓋索引的，直接從索引中讀取數(shù)據(jù)，而不用訪問數(shù)據(jù)表。
--如果同時出現(xiàn)using where 表名索引被用來執(zhí)行索引鍵值的查找，如果沒有，表面索引被用來讀取數(shù)據(jù)，而不是真的查找
explain select deptno,count(*) from emp group by deptno limit 10;--using where:使用where進(jìn)行條件過濾
explain select * from t_user where id = 1;--using join buffer:使用連接緩存，情況沒有模擬出來--impossible where：where語句的結(jié)果總是false
explain select * from emp where empno = 7469;