前言

1.慢SQL優(yōu)化思路。

1.1 慢查詢日志記錄慢SQL

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

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

1.4 Optimizer Trace分析詳情

1.5 確定問題并采用相應的措施

2. 慢查詢經典案例分析

2.1 案例1：隱式轉換

2.2 案例2：最左匹配

2.3 案例3：深分頁問題

2.4 案例4：in元素過多

2.5 order by 走文件排序導致的慢查詢

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

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

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

2.9 group by使用臨時表

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

前言

SQL調優(yōu)這塊呢，大廠面試必問的。最近金九銀十嘛，所以整理了SQL的調優(yōu)思路，并且附幾個經典案例分析。

1.慢SQL優(yōu)化思路。

慢查詢日志記錄慢SQL

explain分析SQL的執(zhí)行計劃

profile 分析執(zhí)行耗時

Optimizer Trace分析詳情

確定問題并采用相應的措施

1.1 慢查詢日志記錄慢SQL

如何定位慢SQL呢、我們可以通過慢查詢日志 來查看慢SQL。默認的情況下呢，MySQL數(shù)據(jù)庫是不開啟慢查詢日志（slow query log）呢。所以我們需要手動把它打開。

查看下慢查詢日志配置，我們可以使用show variables like 'slow_query_log%'命令，如下：

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

slow_query_log_file 表示慢查詢日志存放的位置

我們還可以使用show variables like 'long_query_time'命令，查看超過多少時間，才記錄到慢查詢日志，如下：

long_query_time 表示查詢超過多少秒才記錄到慢查詢日志。

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

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

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

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

一條簡單SQL，使用了explain的效果如下：

一般來說，我們需要重點關注type、rows、filtered、extra、key。

1.2.1 type

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

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

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

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

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

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

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

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

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

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

index：全索引掃描

ALL：全表掃描

1.2.2 rows

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

1.2.3 filtered

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

1.2.4 extra

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

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

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

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

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

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

1.2.5 key

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

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

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

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

profiling默認是關閉，我們可以使用show variables like '%profil%'查看是否開啟，如下：

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

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

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

1.4 Optimizer Trace分析詳情

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

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

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

join_preparation：準備階段

join_optimization：分析階段

join_execution：執(zhí)行階段

1.5 確定問題并采用相應的措施

最后確認問題，就采取對應的措施。

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

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

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

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

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

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

2. 慢查詢經典案例分析

2.1 案例1：隱式轉換

我們創(chuàng)建一個用戶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+樹的普通索引，如果查詢條件傳了一個數(shù)字過去，會導致索引失效。如下：

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

為什么第一條語句未加單引號就不走索引了呢？這是因為不加單引號時，是字符串跟數(shù)字的比較，它們類型不匹配，MySQL會做隱式的類型轉換，把它們轉換為浮點數(shù)再做比較。隱式的類型轉換，索引會失效。

2.2 案例2：最左匹配

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

假設有以下表結構：

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;

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

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

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

2.3 案例3：深分頁問題

limit深分頁問題，會導致慢查詢，應該大家都司空見慣了吧。

limit深分頁為什么會變慢呢？ 假設有表結構如下：

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

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

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

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

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

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

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

因此，limit深分頁，導致SQL變慢原因有兩個：

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

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

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

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

標簽記錄法

就是標記一下上次查詢到哪一條了，下次再來查的時候，從該條開始往下掃描。就好像看書一樣，上次看到哪里了，你就折疊一下或者夾個書簽，下次來看的時候，直接就翻到啦。

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

selectid,name,balanceFROMaccountwhereid>100000limit10;

這樣的話，后面無論翻多少頁，性能都會不錯的，因為命中了id索引。但是這種方式有局限性：需要一種類似連續(xù)自增的字段。

延遲關聯(lián)法

延遲關聯(liá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二級索引樹查詢到滿足條件的主鍵ID，再與原表通過主鍵ID內連接，這樣后面直接走了主鍵索引了，同時也減少了回表。

2.4 案例4：in元素過多

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

反例:

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

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

select*fromuserwhereuser_idin(selectauthor_idfromartilcewheretype=1);

如果type = 1有1一千，甚至上萬個呢？肯定是慢SQL。索引一般建議分批進行，一次200個，比如：

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

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

這是因為in查詢在MySQL底層是通過n*m的方式去搜索，類似union。

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

2.5 order by 走文件排序導致的慢查詢

如果order by 使用到文件排序，則會可能會產生慢查詢。我們來看下下面這個SQL：

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

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

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

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

大家可以看下這個下面這個圖:

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

2.5.1 rowid排序

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

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

MySQL為對應的線程初始化sort_buffer，放入需要排序的age字段，以及主鍵id；

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

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

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

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

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

2.5.2 全字段排序

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

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

MySQL 為對應的線程初始化sort_buffer，放入需要查詢的name、age、city字段；

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

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

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

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

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

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

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

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

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

借助磁盤文件排序的話，效率就更慢一點。因為先把數(shù)據(jù)放入sort_buffer，當快要滿時。會排一下序，然后把sort_buffer中的數(shù)據(jù)，放到臨時磁盤文件，等到所有滿足條件數(shù)據(jù)都查完排完，再用歸并算法把磁盤的臨時排好序的小文件，合并成一個有序的大文件。

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

order by使用文件排序，效率會低一點。我們怎么優(yōu)化呢？

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

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

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

假設有表結構:

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;

單個name字段加上索引，并查詢name為非空的語句，其實會走索引的，如下:

單個card字段加上索引，并查詢name為非空的語句，其實會走索引的，如下:

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

很多時候，也是因為數(shù)據(jù)量問題，導致了MySQL優(yōu)化器放棄走索引。同時，平時我們用explain分析SQL的時候，如果type=range,要注意一下哈，因為這個可能因為數(shù)據(jù)量問題，導致索引無效。

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

假設有表結構：

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這些時，索引如同虛設。如下：

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

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

新建兩個表，一個user，一個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，還是會走索引。

2.9 group by使用臨時表

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

2.9.1 group by執(zhí)行流程

假設有表結構：

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

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

explainselectcity,count(*)asnumfromstaffgroupbycity;

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

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

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

selectcity,count(*)asnumfromstaffgroupbycity;

創(chuàng)建內存臨時表，表里有兩個字段city和num；

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

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

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

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

臨時表的排序是怎樣的呢？

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

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

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

2.9.2 group by可能會慢在哪里？

group by使用不當，很容易就會產生慢SQL問題。因為它既用到臨時表，又默認用到排序。有時候還可能用到磁盤臨時表。

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

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

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

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

方向1：既然它默認會排序，我們不給它排是不是就行啦。

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

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

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

group by 后面的字段加索引

order by null 不用排序

盡量只使用內存臨時表

使用SQL_BIG_RESULT

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

之前見到過一個生產慢SQL問題，當delete遇到in子查詢時，即使有索引，也是不走索引的。而對應的select + in子查詢，卻可以走索引。

MySQL版本是5.7，假設當前有兩張表account和old_account,表結構如下：

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

執(zhí)行的SQL如下：

deletefromaccountwherenamein(selectnamefromold_account);

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

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

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

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

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

結果如下：

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)，實際執(zhí)行的時候，MySQL對select in子查詢做了優(yōu)化，把子查詢改成join的方式，所以可以走索引。但是很遺憾，對于delete in子查詢，MySQL卻沒有對它做這個優(yōu)化。

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

參考資料

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