mysql優(yōu)化系列 DELETE子查詢改寫優(yōu)化
1、問(wèn)題描述
朋友遇到一個(gè)怪事，一個(gè)用子查詢的DELETE，執(zhí)行效率非常低。把DELETE改成SELECT后執(zhí)行起來(lái)卻很快，百思不得其解。
下面就是這個(gè)用了子查詢的DELETE了：
[yejr@imysql.com]mydb > EXPLAIN delete from trade_info where id in ( select id from ( select a.id from trade_info a, order_info b, user c where b.buyer = c.id and c.itv_account='90000248′ and a.order_id = b.id) temp)\G
幾個(gè)表的DDL是這樣的：
上面這個(gè)SQL的執(zhí)行耗時(shí)是：31.74秒
Query OK, 5 rows affected (31.74 sec)
如果我們把DELETE改寫成SELECT的話，執(zhí)行耗時(shí)僅是：0秒，來(lái)對(duì)比看下執(zhí)行計(jì)劃：
[yejr@imysql.com]mydb >EXPLAIN select id from trade_info where id in ( select id from ( select a.id from trade_info a, order_info b, user c where b.buyer = c.id and c.itv_account='90000248′ and a.order_id = b.id) temp)\G
可以看到，trade_info 表從的全表掃描（type=ALL）變成了基于主鍵的等值查詢（type=eq_ref），計(jì)劃掃描數(shù)據(jù)量也從571萬(wàn)變成了1條，而且還可以避免回表，這2個(gè)SQL對(duì)比代價(jià)相差巨大。
2、優(yōu)化思路
既然這個(gè)SQL把DELETE改成SELECT后執(zhí)行效率就可以獲得很大提升，除此外沒(méi)特別區(qū)別，可能是查詢優(yōu)化器方面有些不足，導(dǎo)致無(wú)法直接優(yōu)化，就得另想辦法了。
我們的思路是把基于子查詢的DELETE簡(jiǎn)化改寫成多表JOIN后DELETE（一般來(lái)說(shuō)，子查詢效率比較低的話，可以考慮改寫成JOIN），多表DELETE的語(yǔ)法課參考：https://dev.mysql.com/doc/refman/5.7/en/delete.html#idm140469624466800，例如這樣的：
DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
參照上面的形式，改寫之后的SQL變成了下面這樣：
DELETE trade_info FROM trade_info, ( SELECT a.id FROM trade_info a JOIN order_info b ON a.order_id = b.id JOIN user c ON b.buyer = c.id WHERE c.itv_account = ‘90000248' ) t2 where trade_info.id = t2.id;
可以看到新的SQL執(zhí)行效率相對(duì)就高很多了，不需要再掃描571萬(wàn)條記錄，執(zhí)行耗時(shí)只需：0.01秒。
Query OK, 5 rows affected (0.01 sec)
3、其他建議
雖然MySQL 5.6及以上的版本對(duì)子查詢做了優(yōu)化，但從本案例的結(jié)果來(lái)看，在一些情況下還是不如意。
因此，如果發(fā)現(xiàn)有些子查詢SQL效率比較差的話，可以嘗試改寫成JOIN形式，看看是否有所提升。此外，也要勇于懷疑查詢優(yōu)化器個(gè)別情況下存在不足，想辦法繞過(guò)這些坑。
- MySQL優(yōu)化之使用連接(join)代替子查詢
- MYSQL子查詢和嵌套查詢優(yōu)化實(shí)例解析
- mysql in語(yǔ)句子查詢效率慢的優(yōu)化技巧示例
- mysql關(guān)聯(lián)子查詢的一種優(yōu)化方法分析
- Oracle數(shù)據(jù)庫(kù)中基本的查詢優(yōu)化與子查詢優(yōu)化講解
- MySQL的子查詢及相關(guān)優(yōu)化學(xué)習(xí)教程
- 對(duì)MySQL子查詢的簡(jiǎn)單改寫優(yōu)化
- 淺談MySQL中的子查詢優(yōu)化技巧
- MySQL查詢優(yōu)化:用子查詢代替非主鍵連接查詢實(shí)例介紹
- 數(shù)據(jù)庫(kù)查詢優(yōu)化之子查詢優(yōu)化
相關(guān)文章
mysql中l(wèi)imit查詢踩坑實(shí)戰(zhàn)記錄
在MySQL中我們常常用order by來(lái)進(jìn)行排序,使用limit來(lái)進(jìn)行分頁(yè),下面這篇文章主要給大家介紹了關(guān)于mysql中l(wèi)imit查詢踩坑的相關(guān)資料,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-03-03
這篇文章主要介紹了MySQL索引優(yōu)化實(shí)例分析，文章圍繞主題展開(kāi)詳細(xì)的內(nèi)容介紹，具有一定的參考價(jià)值，需要的朋友可以參考一下2022-07-07
淺談Mysql insert on duplicate key 死鎖問(wèn)
本文介紹了在并發(fā)場(chǎng)景下的 insert on duplicate key update sql 出現(xiàn)的死鎖，經(jīng)過(guò)分析發(fā)現(xiàn)這種sql確實(shí)比較容易造成死鎖，這篇文章就從分析死鎖展開(kāi)，到最終如何解決這樣的問(wèn)題 分享相應(yīng)的思路，感興趣的可以了解一下2022-05-05
這篇文章主要給大家介紹了關(guān)于MySQL如何優(yōu)雅的刪除大表的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2020-12-12
這篇文章主要介紹了MySQL如何修改字段類型和字段長(zhǎng)度，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-06-06
mysql服務(wù)1067錯(cuò)誤多種解決方案分享
今天我的mysql服務(wù)器突然出來(lái)了1067錯(cuò)誤提示，無(wú)法正常啟動(dòng)了，我今天從網(wǎng)上找尋了大量的解決mysql服務(wù)1067錯(cuò)誤的辦法，有需要的朋友可以看看2012-03-03
這篇文章主要介紹了mysql 登錄時(shí)閃退的問(wèn)題解決方法的相關(guān)資料,mysql 出現(xiàn)閃退問(wèn)題，很是棘手在做項(xiàng)目的時(shí)候，這里對(duì)解決這樣的問(wèn)題提供了解決方案，需要的朋友可以參考下2016-11-11
SELECT語(yǔ)句是SQL中最常用的語(yǔ)句。它的用途是從一個(gè)或多個(gè)表中檢索信息。為了使用SELECT檢索表數(shù)據(jù)，必須至少給出兩條信息：想選擇什么、從什么地方選擇2022-10-10
MySQL 常見(jiàn)的數(shù)據(jù)表設(shè)計(jì)誤區(qū)匯總
雖然會(huì)有一些常規(guī)意義上的數(shù)據(jù)表錯(cuò)誤設(shè)計(jì)和優(yōu)秀設(shè)計(jì)原則，但是同樣也會(huì)有 MySQL 特定的一些情況，這會(huì)導(dǎo)致我們犯一些 MySQL 特定的錯(cuò)誤。本篇討論常見(jiàn)的設(shè)計(jì)誤區(qū)。2021-06-06
使用JDBC連接Mysql數(shù)據(jù)庫(kù)會(huì)出現(xiàn)的問(wèn)題總結(jié)
這篇文章主要給大家介紹了關(guān)于使用JDBC連接Mysql數(shù)據(jù)庫(kù)會(huì)出現(xiàn)的問(wèn)題的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2018-10-10