sql優(yōu)化常用的幾種方法

查看下慢查詢?nèi)罩九渲?，我們可以使用show variables like 'slow_query_log%'命令，如下：

slow query log 表示慢查詢開啟的狀態(tài)

slow_query_log_file 表示慢查詢?nèi)罩敬娣诺奈恢?/p>

我們還可以使用show variables like 'long_query_time'命令，查看超過多少時(shí)間，才記錄到慢查詢?nèi)罩?，如下?/p>

long_query_time 表示查詢超過多少秒才記錄到慢查詢?nèi)罩尽?/p>

我們可以通過慢查日志，定位那些執(zhí)行效率較低的SQL語(yǔ)句，重點(diǎn)關(guān)注分析。

1.2 explain查看分析SQL的執(zhí)行計(jì)劃

當(dāng)定位出查詢效率低的SQL后，可以使用explain查看SQL的執(zhí)行計(jì)劃。

當(dāng)explain與SQL一起使用時(shí)，MySQL將顯示來(lái)自優(yōu)化器的有關(guān)語(yǔ)句執(zhí)行計(jì)劃的信息。即MySQL解釋了它將如何處理該語(yǔ)句，包括有關(guān)如何連接表以及以何種順序連接表等信息。

一條簡(jiǎn)單SQL，使用了explain的效果如下：

一般來(lái)說，我們需要重點(diǎn)關(guān)注type、rows、filtered、extra、key。

1.2.1 type

type表示連接類型 ，查看索引執(zhí)行情況的一個(gè)重要指標(biāo)。以下性能從好到壞依次：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system：這種類型要求數(shù)據(jù)庫(kù)表中只有一條數(shù)據(jù)，是const類型的一個(gè)特例，一般情況下是不會(huì)出現(xiàn)的。

const：通過一次索引就能找到數(shù)據(jù)，一般用于主鍵或唯一索引作為條件，這類掃描效率極高，，速度非常快。

eq_ref：常用于主鍵或唯一索引掃描，一般指使用主鍵的關(guān)聯(lián)查詢

ref : 常用于非主鍵和唯一索引掃描。

ref_or_null：這種連接類型類似于ref，區(qū)別在于MySQL會(huì)額外搜索包含NULL值的行

index_merge：使用了索引合并優(yōu)化方法，查詢使用了兩個(gè)以上的索引。

unique_subquery：類似于eq_ref，條件用了in子查詢

index_subquery：區(qū)別于unique_subquery，用于非唯一索引，可以返回重復(fù)值。

range：常用于范圍查詢，比如：between ... and 或 In 等操作

index：全索引掃描

ALL：全表掃描

1.2.2 rows

該列表示MySQL估算要找到我們所需的記錄，需要讀取的行數(shù)。對(duì)于InnoDB表，此數(shù)字是估計(jì)值，并非一定是個(gè)準(zhǔn)確值。

1.2.3 filtered

該列是一個(gè)百分比的值，表里符合條件的記錄數(shù)的百分比。簡(jiǎn)單點(diǎn)說，這個(gè)字段表示存儲(chǔ)引擎返回的數(shù)據(jù)在經(jīng)過過濾后，剩下滿足條件的記錄數(shù)量的比例。

1.2.4 extra

該字段包含有關(guān)MySQL如何解析查詢的其他信息，它一般會(huì)出現(xiàn)這幾個(gè)值：

Using filesort：表示按文件排序，一般是在指定的排序和索引排序不一致的情況才會(huì)出現(xiàn)。一般見于order by語(yǔ)句

Using index ：表示是否用了覆蓋索引。

Using temporary: 表示是否使用了臨時(shí)表,性能特別差，需要重點(diǎn)優(yōu)化。一般多見于group by語(yǔ)句，或者union語(yǔ)句。

Using where : 表示使用了where條件過濾.

Using index condition：MySQL5.6之后新增的索引下推。在存儲(chǔ)引擎層進(jìn)行數(shù)據(jù)過濾，而不是在服務(wù)層過濾，利用索引現(xiàn)有的數(shù)據(jù)減少回表的數(shù)據(jù)。

1.2.5 key

該列表示實(shí)際用到的索引。一般配合possible_keys列一起看。

注意 :有時(shí)候，explain配合show WARNINGS; （可以查看優(yōu)化后,最終執(zhí)行的sql），效果更佳哦。

1.3 profile 分析執(zhí)行耗時(shí)

explain只是看到SQL的預(yù)估執(zhí)行計(jì)劃，如果要了解SQL真正的執(zhí)行線程狀態(tài)及消耗的時(shí)間 ，需要使用profiling。開啟profiling參數(shù)后，后續(xù)執(zhí)行的SQL語(yǔ)句都會(huì)記錄其資源開銷，包括IO，上下文切換，CPU，內(nèi)存等等，我們可以根據(jù)這些開銷進(jìn)一步分析當(dāng)前慢SQL的瓶頸再進(jìn)一步進(jìn)行優(yōu)化。

profiling默認(rèn)是關(guān)閉，我們可以使用show variables like '%profil%'查看是否開啟，如下：

可以使用set profiling=ON開啟。開啟后，可以運(yùn)行幾條SQL，然后使用show profiles查看一下。

show profiles會(huì)顯示最近發(fā)給服務(wù)器的多條語(yǔ)句，條數(shù)由變量profiling_history_size定義，默認(rèn)是15。如果我們需要看單獨(dú)某條SQL的分析，可以show profile查看最近一條SQL的分析，也可以使用show profile for query id（其中id就是show profiles中的QUERY_ID）查看具體一條的SQL語(yǔ)句分析。

除了查看profile ，還可以查看cpu和io，如上圖。

1.4 Optimizer Trace分析詳情

profile只能查看到SQL的執(zhí)行耗時(shí)，但是無(wú)法看到SQL真正執(zhí)行的過程信息，即不知道MySQL優(yōu)化器是如何選擇執(zhí)行計(jì)劃。這時(shí)候，我們可以使用Optimizer Trace，它可以跟蹤執(zhí)行語(yǔ)句的解析優(yōu)化執(zhí)行的全過程。

我們可以使用set optimizer_trace="enabled=on"打開開關(guān)，接著執(zhí)行要跟蹤的SQL，最后執(zhí)行select * from information_schema.optimizer_trace跟蹤，如下：

大家可以查看分析其執(zhí)行樹，會(huì)包括三個(gè)階段：

join_preparation：準(zhǔn)備階段

join_optimization：分析階段

join_execution：執(zhí)行階段

1.5 確定問題并采用相應(yīng)的措施

最后確認(rèn)問題，就采取對(duì)應(yīng)的措施。

多數(shù)慢SQL都跟索引有關(guān)，比如不加索引，索引不生效、不合理等，這時(shí)候，我們可以優(yōu)化索引 。

我們還可以優(yōu)化SQL語(yǔ)句，比如一些in元素過多問題（分批），深分頁(yè)問題（基于上一次數(shù)據(jù)過濾等），進(jìn)行時(shí)間分段查詢

SQl沒辦法很好優(yōu)化，可以改用ES的方式，或者數(shù)倉(cāng)。

如果單表數(shù)據(jù)量過大導(dǎo)致慢查詢，則可以考慮分庫(kù)分表

如果數(shù)據(jù)庫(kù)在刷臟頁(yè)導(dǎo)致慢查詢，考慮是否可以優(yōu)化一些參數(shù)，跟DBA討論優(yōu)化方案

如果存量數(shù)據(jù)量太大，考慮是否可以讓部分?jǐn)?shù)據(jù)歸檔

2. 慢查詢經(jīng)典案例分析

2.1 案例1：隱式轉(zhuǎn)換

我們創(chuàng)建一個(gè)用戶user表

CREATETABLEuser(
idint(11)NOTNULLAUTO_INCREMENT,
userIdvarchar(32)NOTNULL,
agevarchar(16)NOTNULL,
namevarchar(255)NOTNULL,
PRIMARYKEY(id),
KEYidx_userid(userId)USINGBTREE
)ENGINE=InnoDBDEFAULTCHARSET=utf8;

userId字段為字串類型，是B+樹的普通索引，如果查詢條件傳了一個(gè)數(shù)字過去，會(huì)導(dǎo)致索引失效。如下：

如果給數(shù)字加上'',也就是說，傳的是一個(gè)字符串呢，當(dāng)然是走索引，如下圖：

為什么第一條語(yǔ)句未加單引號(hào)就不走索引了呢？這是因?yàn)椴患訂我?hào)時(shí)，是字符串跟數(shù)字的比較，它們類型不匹配，MySQL會(huì)做隱式的類型轉(zhuǎn)換，把它們轉(zhuǎn)換為浮點(diǎn)數(shù)再做比較。隱式的類型轉(zhuǎn)換，索引會(huì)失效。

2.2 案例2：最左匹配

MySQl建立聯(lián)合索引時(shí)，會(huì)遵循最左前綴匹配的原則，即最左優(yōu)先。如果你建立一個(gè)（a,b,c）的聯(lián)合索引，相當(dāng)于建立了(a)、(a,b)、(a,b,c)三個(gè)索引。

假設(shè)有以下表結(jié)構(gòu)：

CREATETABLEuser(
idint(11)NOTNULLAUTO_INCREMENT,
user_idvarchar(32)NOTNULL,
agevarchar(16)NOTNULL,
namevarchar(255)NOTNULL,
PRIMARYKEY(id),
KEYidx_userid_name(user_id,name)USINGBTREE
)ENGINE=InnoDBDEFAULTCHARSET=utf8;

假設(shè)有一個(gè)聯(lián)合索引idx_userid_name，我們現(xiàn)在執(zhí)行以下SQL，如果查詢列是name，索引是無(wú)效的：

explainselect*fromuserwherename='撿田螺的小男孩';

因?yàn)椴樵儣l件列name不是聯(lián)合索引idx_userid_name中的第一個(gè)列，不滿足最左匹配原則，所以索引不生效。在聯(lián)合索引中，只有查詢條件滿足最左匹配原則時(shí)，索引才正常生效。如下，查詢條件列是user_id

2.3 案例3：深分頁(yè)問題

limit深分頁(yè)問題，會(huì)導(dǎo)致慢查詢，應(yīng)該大家都司空見慣了吧。

limit深分頁(yè)為什么會(huì)變慢呢？ 假設(shè)有表結(jié)構(gòu)如下：

CREATETABLEaccount(
idint(11)NOTNULLAUTO_INCREMENTCOMMENT'主鍵Id',
namevarchar(255)DEFAULTNULLCOMMENT'賬戶名',
balanceint(11)DEFAULTNULLCOMMENT'余額',
create_timedatetimeNOTNULLCOMMENT'創(chuàng)建時(shí)間',
update_timedatetimeNOTNULLONUPDATECURRENT_TIMESTAMPCOMMENT'更新時(shí)間',
PRIMARYKEY(id),
KEYidx_name(name),
KEYidx_create_time(create_time)//索引
)ENGINE=InnoDBAUTO_INCREMENT=1570068DEFAULTCHARSET=utf8ROW_FORMAT=REDUNDANTCOMMENT='賬戶表';

以下這個(gè)SQL，你知道執(zhí)行過程是怎樣的呢？

selectid,name,balancefromaccountwherecreate_time>'2020-09-19'limit100000,10;

這個(gè)SQL的執(zhí)行流程醬紫：

通過普通二級(jí)索引樹idx_create_time，過濾create_time條件，找到滿足條件的主鍵id。

通過主鍵id，回到id主鍵索引樹，找到滿足記錄的行，然后取出需要展示的列（回表過程）

掃描滿足條件的100010行，然后扔掉前100000行，返回。

因此，limit深分頁(yè)，導(dǎo)致SQL變慢原因有兩個(gè)：

limit語(yǔ)句會(huì)先掃描offset+n行，然后再丟棄掉前offset行，返回后n行數(shù)據(jù)。也就是說limit 100000,10，就會(huì)掃描100010行，而limit 0,10，只掃描10行。

limit 100000,10 掃描更多的行數(shù)，也意味著回表更多的次數(shù)。

如何優(yōu)化深分頁(yè)問題?

我們可以通過減少回表次數(shù)來(lái)優(yōu)化。一般有標(biāo)簽記錄法 和延遲關(guān)聯(lián)法 。

標(biāo)簽記錄法

就是標(biāo)記一下上次查詢到哪一條了，下次再來(lái)查的時(shí)候，從該條開始往下掃描。就好像看書一樣，上次看到哪里了，你就折疊一下或者夾個(gè)書簽，下次來(lái)看的時(shí)候，直接就翻到啦。

假設(shè)上一次記錄到100000，則SQL可以修改為：

selectid,name,balanceFROMaccountwhereid>100000limit10;

這樣的話，后面無(wú)論翻多少頁(yè)，性能都會(huì)不錯(cuò)的，因?yàn)槊辛薸d索引。但是這種方式有局限性：需要一種類似連續(xù)自增的字段。

延遲關(guān)聯(lián)法

延遲關(guān)聯(lián)法，就是把條件轉(zhuǎn)移到主鍵索引樹 ，然后減少回表。如下

selectacct1.id,acct1.name,acct1.balanceFROMaccountacct1INNERJOIN(SELECTa.idFROMaccountaWHEREa.create_time>'2020-09-19'limit100000,10)ASacct2onacct1.id=acct2.id;

優(yōu)化思路 就是，先通過idx_create_time二級(jí)索引樹查詢到滿足條件的主鍵ID，再與原表通過主鍵ID內(nèi)連接，這樣后面直接走了主鍵索引了，同時(shí)也減少了回表。

2.4 案例4：in元素過多

如果使用了in，即使后面的條件加了索引，還是要注意in后面的元素不要過多哈。in元素一般建議不要超過200個(gè)，如果超過了，建議分組，每次200一組進(jìn)行哈。

反例:

selectuser_id,namefromuserwhereuser_idin(1,2,3...1000000);

如果我們對(duì)in的條件不做任何限制的話，該查詢語(yǔ)句一次性可能會(huì)查詢出非常多的數(shù)據(jù)，很容易導(dǎo)致接口超時(shí)。尤其有時(shí)候，我們是用的子查詢，in后面的子查詢 ，你都不知道數(shù)量有多少那種，更容易采坑.如下這種子查詢：

select*fromuserwhereuser_idin(selectauthor_idfromartilcewheretype=1);

如果type = 1有1一千，甚至上萬(wàn)個(gè)呢？肯定是慢SQL。索引一般建議分批進(jìn)行，一次200個(gè)，比如：

selectuser_id,namefromuserwhereuser_idin(1,2,3...200);

in查詢?yōu)槭裁绰兀?/p>

這是因?yàn)閕n查詢?cè)贛ySQL底層是通過n*m的方式去搜索，類似union。

in查詢?cè)谶M(jìn)行cost代價(jià)計(jì)算時(shí)（代價(jià) = 元組數(shù) * IO平均值），是通過將in包含的數(shù)值，一條條去查詢獲取元組數(shù)的，因此這個(gè)計(jì)算過程會(huì)比較的慢，所以MySQL設(shè)置了個(gè)臨界值(eq_range_index_dive_limit)，5.6之后超過這個(gè)臨界值后該列的cost就不參與計(jì)算了。因此會(huì)導(dǎo)致執(zhí)行計(jì)劃選擇不準(zhǔn)確。默認(rèn)是200，即in條件超過了200個(gè)數(shù)據(jù)，會(huì)導(dǎo)致in的代價(jià)計(jì)算存在問題，可能會(huì)導(dǎo)致Mysql選擇的索引不準(zhǔn)確。

2.5 order by 走文件排序?qū)е碌穆樵?/p>

如果order by 使用到文件排序，則會(huì)可能會(huì)產(chǎn)生慢查詢。我們來(lái)看下下面這個(gè)SQL：

selectname,age,cityfromstaffwherecity='深圳'orderbyagelimit10;

它表示的意思就是：查詢前10個(gè)，來(lái)自深圳員工的姓名、年齡、城市，并且按照年齡小到大排序。

查看explain執(zhí)行計(jì)劃的時(shí)候，可以看到Extra這一列，有一個(gè)Using filesort，它表示用到文件排序。

order by文件排序效率為什么較低

大家可以看下這個(gè)下面這個(gè)圖:

order by排序，分為全字段排序和rowid排序。它是拿max_length_for_sort_data和結(jié)果行數(shù)據(jù)長(zhǎng)度對(duì)比，如果結(jié)果行數(shù)據(jù)長(zhǎng)度超過max_length_for_sort_data這個(gè)值，就會(huì)走rowid排序，相反，則走全字段排序。

2.5.1 rowid排序

rowid排序，一般需要回表去找滿足條件的數(shù)據(jù)，所以效率會(huì)慢一點(diǎn)。以下這個(gè)SQL，使用rowid排序，執(zhí)行過程是這樣：

selectname,age,cityfromstaffwherecity='深圳'orderbyagelimit10;

MySQL為對(duì)應(yīng)的線程初始化sort_buffer，放入需要排序的age字段，以及主鍵id；

從索引樹idx_city， 找到第一個(gè)滿足 city='深圳’條件的主鍵id,假設(shè)id為X；

到主鍵id索引樹拿到id=X的這一行數(shù)據(jù)， 取age和主鍵id的值，存到sort_buffer；

從索引樹idx_city拿到下一個(gè)記錄的主鍵id，假設(shè)id=Y；

重復(fù)步驟 3、4 直到city的值不等于深圳為止；

前面5步已經(jīng)查找到了所有city為深圳的數(shù)據(jù)，在sort_buffer中，將所有數(shù)據(jù)根據(jù)age進(jìn)行排序；遍歷排序結(jié)果，取前10行，并按照id的值回到原表中，取出city、name 和 age三個(gè)字段返回給客戶端。

2.5.2 全字段排序

同樣的SQL，如果是走全字段排序是這樣的：

selectname,age,cityfromstaffwherecity='深圳'orderbyagelimit10;

MySQL 為對(duì)應(yīng)的線程初始化sort_buffer，放入需要查詢的name、age、city字段；

從索引樹idx_city， 找到第一個(gè)滿足 city='深圳’條件的主鍵 id，假設(shè)找到id=X；

到主鍵id索引樹拿到id=X的這一行數(shù)據(jù)， 取name、age、city三個(gè)字段的值，存到sort_buffer；

從索引樹idx_city 拿到下一個(gè)記錄的主鍵id，假設(shè)id=Y；

重復(fù)步驟 3、4 直到city的值不等于深圳為止；

前面5步已經(jīng)查找到了所有city為深圳的數(shù)據(jù)，在sort_buffer中，將所有數(shù)據(jù)根據(jù)age進(jìn)行排序；

按照排序結(jié)果取前10行返回給客戶端。

sort_buffer的大小是由一個(gè)參數(shù)控制的：sort_buffer_size。

如果要排序的數(shù)據(jù)小于sort_buffer_size，排序在sort_buffer內(nèi)存中完成

如果要排序的數(shù)據(jù)大于sort_buffer_size，則借助磁盤文件來(lái)進(jìn)行排序。

借助磁盤文件排序的話，效率就更慢一點(diǎn)。因?yàn)橄劝褦?shù)據(jù)放入sort_buffer，當(dāng)快要滿時(shí)。會(huì)排一下序，然后把sort_buffer中的數(shù)據(jù)，放到臨時(shí)磁盤文件，等到所有滿足條件數(shù)據(jù)都查完排完，再用歸并算法把磁盤的臨時(shí)排好序的小文件，合并成一個(gè)有序的大文件。

2.5.3 如何優(yōu)化order by的文件排序

order by使用文件排序，效率會(huì)低一點(diǎn)。我們?cè)趺磧?yōu)化呢？

因?yàn)閿?shù)據(jù)是無(wú)序的，所以就需要排序。如果數(shù)據(jù)本身是有序的，那就不會(huì)再用到文件排序啦。而索引數(shù)據(jù)本身是有序的，我們通過建立索引來(lái)優(yōu)化order by語(yǔ)句。

我們還可以通過調(diào)整max_length_for_sort_data、sort_buffer_size等參數(shù)優(yōu)化；

2.6 索引字段上使用is null， is not null，索引可能失效

假設(shè)有表結(jié)構(gòu):

CREATETABLE`user`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`card`varchar(255)DEFAULTNULL,
`name`varchar(255)DEFAULTNULL,
PRIMARYKEY(`id`),
KEY`idx_name`(`name`)USINGBTREE,
KEY`idx_card`(`card`)USINGBTREE
)ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=utf8;

單個(gè)name字段加上索引，并查詢name為非空的語(yǔ)句，其實(shí)會(huì)走索引的，如下:

單個(gè)card字段加上索引，并查詢name為非空的語(yǔ)句，其實(shí)會(huì)走索引的，如下:

但是它兩用or連接起來(lái)，索引就失效了，如下：

很多時(shí)候，也是因?yàn)閿?shù)據(jù)量問題，導(dǎo)致了MySQL優(yōu)化器放棄走索引。同時(shí)，平時(shí)我們用explain分析SQL的時(shí)候，如果type=range,要注意一下哈，因?yàn)檫@個(gè)可能因?yàn)閿?shù)據(jù)量問題，導(dǎo)致索引無(wú)效。

2.7 索引字段上使用（！= 或者 < >），索引可能失效

假設(shè)有表結(jié)構(gòu)：

CREATETABLE`user`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`userId`int(11)NOTNULL,
`age`int(11)DEFAULTNULL,
`name`varchar(255)NOTNULL,
PRIMARYKEY(`id`),
KEY`idx_age`(`age`)USINGBTREE
)ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=utf8;

雖然age加了索引，但是使用了！= 或者< >，not in這些時(shí)，索引如同虛設(shè)。如下：

其實(shí)這個(gè)也是跟mySQL優(yōu)化器有關(guān)，如果優(yōu)化器覺得即使走了索引，還是需要掃描很多很多行的哈，它覺得不劃算，不如直接不走索引。平時(shí)我們用！= 或者< >，not in的時(shí)候，留點(diǎn)心眼哈。

2.8 左右連接，關(guān)聯(lián)的字段編碼格式不一樣

新建兩個(gè)表，一個(gè)user，一個(gè)user_job

``

CREATETABLE`user`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`name`varchar(255)CHARACTERSETutf8mb4DEFAULTNULL,
`age`int(11)NOTNULL,
PRIMARYKEY(`id`),
KEY`idx_name`(`name`)USINGBTREE
)ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=utf8;

CREATETABLE`user_job`(
`id`int(11)NOTNULL,
`userId`int(11)NOTNULL,
`job`varchar(255)DEFAULTNULL,
`name`varchar(255)DEFAULTNULL,
PRIMARYKEY(`id`),
KEY`idx_name`(`name`)USINGBTREE
)ENGINE=InnoDBDEFAULTCHARSET=utf8;

user表的name字段編碼是utf8mb4，而user_job表的name字段編碼為utf8。

執(zhí)行左外連接查詢,user_job表還是走全表掃描，如下：

如果把它們的name字段改為編碼一致，相同的SQL，還是會(huì)走索引。

2.9 group by使用臨時(shí)表

group by一般用于分組統(tǒng)計(jì)，它表達(dá)的邏輯就是根據(jù)一定的規(guī)則，進(jìn)行分組。日常開發(fā)中，我們使用得比較頻繁。如果不注意，很容易產(chǎn)生慢SQL。

2.9.1 group by執(zhí)行流程

假設(shè)有表結(jié)構(gòu)：

CREATETABLE`staff`(
`id`bigint(11)NOTNULLAUTO_INCREMENTCOMMENT'主鍵id',
`id_card`varchar(20)NOTNULLCOMMENT'身份證號(hào)碼',
`name`varchar(64)NOTNULLCOMMENT'姓名',
`age`int(4)NOTNULLCOMMENT'年齡',
`city`varchar(64)NOTNULLCOMMENT'城市',
PRIMARYKEY(`id`)
)ENGINE=InnoDBAUTO_INCREMENT=15DEFAULTCHARSET=utf8COMMENT='員工表';

我們查看一下這個(gè)SQL的執(zhí)行計(jì)劃：

explainselectcity,count(*)asnumfromstaffgroupbycity;

Extra 這個(gè)字段的Using temporary表示在執(zhí)行分組的時(shí)候使用了臨時(shí)表

Extra 這個(gè)字段的Using filesort表示使用了文件排序

group by是怎么使用到臨時(shí)表和排序了呢？我們來(lái)看下這個(gè)SQL的執(zhí)行流程

selectcity,count(*)asnumfromstaffgroupbycity;

創(chuàng)建內(nèi)存臨時(shí)表，表里有兩個(gè)字段city和num；

全表掃描staff的記錄，依次取出city = 'X'的記錄。

判斷臨時(shí)表中是否有為city='X'的行，沒有就插入一個(gè)記錄 (X,1);

如果臨時(shí)表中有city='X'的行，就將X這一行的num值加 1；

遍歷完成后，再根據(jù)字段city做排序，得到結(jié)果集返回給客戶端。這個(gè)流程的執(zhí)行圖如下：

臨時(shí)表的排序是怎樣的呢？

就是把需要排序的字段，放到sort buffer，排完就返回。在這里注意一點(diǎn)哈，排序分全字段排序和rowid排序

如果是全字段排序，需要查詢返回的字段，都放入sort buffer，根據(jù)排序字段排完，直接返回

如果是rowid排序，只是需要排序的字段放入sort buffer，然后多一次回表操作，再返回。

2.9.2 group by可能會(huì)慢在哪里？

group by使用不當(dāng)，很容易就會(huì)產(chǎn)生慢SQL問題。因?yàn)樗扔玫脚R時(shí)表，又默認(rèn)用到排序。有時(shí)候還可能用到磁盤臨時(shí)表。

如果執(zhí)行過程中，會(huì)發(fā)現(xiàn)內(nèi)存臨時(shí)表大小到達(dá)了上限（控制這個(gè)上限的參數(shù)就是tmp_table_size），會(huì)把內(nèi)存臨時(shí)表轉(zhuǎn)成磁盤臨時(shí)表。

如果數(shù)據(jù)量很大，很可能這個(gè)查詢需要的磁盤臨時(shí)表，就會(huì)占用大量的磁盤空間。

2.9.3 如何優(yōu)化group by呢

從哪些方向去優(yōu)化呢？

方向1：既然它默認(rèn)會(huì)排序，我們不給它排是不是就行啦。

方向2：既然臨時(shí)表是影響group by性能的X因素，我們是不是可以不用臨時(shí)表？

我們一起來(lái)想下，執(zhí)行g(shù)roup by語(yǔ)句為什么需要臨時(shí)表呢？group by的語(yǔ)義邏輯，就是統(tǒng)計(jì)不同的值出現(xiàn)的個(gè)數(shù)。如果這個(gè)這些值一開始就是有序的，我們是不是直接往下掃描統(tǒng)計(jì)就好了，就不用臨時(shí)表來(lái)記錄并統(tǒng)計(jì)結(jié)果啦?

可以有這些優(yōu)化方案：

group by 后面的字段加索引

order by null 不用排序

盡量只使用內(nèi)存臨時(shí)表

使用SQL_BIG_RESULT

2.10 delete + in子查詢不走索引！

之前見到過一個(gè)生產(chǎn)慢SQL問題，當(dāng)delete遇到in子查詢時(shí)，即使有索引，也是不走索引的。而對(duì)應(yīng)的select + in子查詢，卻可以走索引。

MySQL版本是5.7，假設(shè)當(dāng)前有兩張表account和old_account,表結(jié)構(gòu)如下：

CREATETABLE`old_account`(
`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'主鍵Id',
`name`varchar(255)DEFAULTNULLCOMMENT'賬戶名',
`balance`int(11)DEFAULTNULLCOMMENT'余額',
`create_time`datetimeNOTNULLCOMMENT'創(chuàng)建時(shí)間',
`update_time`datetimeNOTNULLONUPDATECURRENT_TIMESTAMPCOMMENT'更新時(shí)間',
PRIMARYKEY(`id`),
KEY`idx_name`(`name`)USINGBTREE
)ENGINE=InnoDBAUTO_INCREMENT=1570068DEFAULTCHARSET=utf8ROW_FORMAT=REDUNDANTCOMMENT='老的賬戶表';

CREATETABLE`account`(
`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'主鍵Id',
`name`varchar(255)DEFAULTNULLCOMMENT'賬戶名',
`balance`int(11)DEFAULTNULLCOMMENT'余額',
`create_time`datetimeNOTNULLCOMMENT'創(chuàng)建時(shí)間',
`update_time`datetimeNOTNULLONUPDATECURRENT_TIMESTAMPCOMMENT'更新時(shí)間',
PRIMARYKEY(`id`),
KEY`idx_name`(`name`)USINGBTREE
)ENGINE=InnoDBAUTO_INCREMENT=1570068DEFAULTCHARSET=utf8ROW_FORMAT=REDUNDANTCOMMENT='賬戶表';

執(zhí)行的SQL如下：

deletefromaccountwherenamein(selectnamefromold_account);

查看執(zhí)行計(jì)劃，發(fā)現(xiàn)不走索引：

但是如果把delete換成select，就會(huì)走索引。如下：

為什么select + in子查詢會(huì)走索引，delete + in子查詢卻不會(huì)走索引呢？

我們執(zhí)行以下SQL看看：

explainselect*fromaccountwherenamein(selectnamefromold_account);
showWARNINGS;//可以查看優(yōu)化后,最終執(zhí)行的sql

結(jié)果如下：

select`test2`.`account`.`id`AS`id`,`test2`.`account`.`name`AS`name`,`test2`.`account`.`balance`AS`balance`,`test2`.`account`.`create_time`AS`create_time`,`test2`.`account`.`update_time`AS`update_time`from`test2`.`account`
semijoin(`test2`.`old_account`)
where(`test2`.`account`.`name`=`test2`.`old_account`.`name`)

可以發(fā)現(xiàn)，實(shí)際執(zhí)行的時(shí)候，MySQL對(duì)select in子查詢做了優(yōu)化，把子查詢改成join的方式，所以可以走索引。但是很遺憾，對(duì)于delete in子查詢，MySQL卻沒有對(duì)它做這個(gè)優(yōu)化。

日常開發(fā)中，大家注意一下這個(gè)場(chǎng)景哈

參考資料

[1] 慢SQL優(yōu)化一點(diǎn)小思路：https://juejin.cn/post/7048974570228809741#heading-7[2] SQL優(yōu)化萬(wàn)能公式：5 大步驟 + 10 個(gè)案例: https://developer.aliyun.com/article/980780