mysql優(yōu)化系列 DELETE子查詢改寫優(yōu)化
1、問題描述
朋友遇到一個(gè)怪事，一個(gè)用子查詢的DELETE，執(zhí)行效率非常低。把DELETE改成SELECT后執(zhí)行起來卻很快，百思不得其解。
下面就是這個(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秒，來對(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萬變成了1條，而且還可以避免回表，這2個(gè)SQL對(duì)比代價(jià)相差巨大。
2、優(yōu)化思路
既然這個(gè)SQL把DELETE改成SELECT后執(zhí)行效率就可以獲得很大提升，除此外沒特別區(qū)別，可能是查詢優(yōu)化器方面有些不足，導(dǎo)致無法直接優(yōu)化，就得另想辦法了。
我們的思路是把基于子查詢的DELETE簡化改寫成多表JOIN后DELETE（一般來說，子查詢效率比較低的話，可以考慮改寫成JOIN），多表DELETE的語法課參考：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萬條記錄，執(zhí)行耗時(shí)只需：0.01秒。
Query OK, 5 rows affected (0.01 sec)
3、其他建議
雖然MySQL 5.6及以上的版本對(duì)子查詢做了優(yōu)化，但從本案例的結(jié)果來看，在一些情況下還是不如意。
因此，如果發(fā)現(xiàn)有些子查詢SQL效率比較差的話，可以嘗試改寫成JOIN形式，看看是否有所提升。此外，也要勇于懷疑查詢優(yōu)化器個(gè)別情況下存在不足，想辦法繞過這些坑。
- MySQL優(yōu)化之使用連接(join)代替子查詢
- MYSQL子查詢和嵌套查詢優(yōu)化實(shí)例解析
- mysql in語句子查詢效率慢的優(yōu)化技巧示例
- mysql關(guān)聯(lián)子查詢的一種優(yōu)化方法分析
- Oracle數(shù)據(jù)庫中基本的查詢優(yōu)化與子查詢優(yōu)化講解
- MySQL的子查詢及相關(guān)優(yōu)化學(xué)習(xí)教程
- 對(duì)MySQL子查詢的簡單改寫優(yōu)化
- 淺談MySQL中的子查詢優(yōu)化技巧
- MySQL查詢優(yōu)化:用子查詢代替非主鍵連接查詢實(shí)例介紹
- 數(shù)據(jù)庫查詢優(yōu)化之子查詢優(yōu)化
相關(guān)文章
MySQL 日志相關(guān)知識(shí)總結(jié)
這篇文章主要介紹了MySQL 日志相關(guān)知識(shí)總結(jié)，幫助大家更好的理解和實(shí)用MySQL，感興趣的朋友可以了解下2021-02-02
在數(shù)據(jù)庫中，事務(wù)是指一組邏輯操作，這些操作要么全部執(zhí)行，要么全部不執(zhí)行，是一個(gè)不可分割的工作單位，這篇文章主要介紹了MySQL事務(wù)隔離機(jī)制,需要的朋友可以參考下2022-11-11
眾所周知MySQL 的主鍵可以是自增的,下面這篇文章主要給大家介紹了關(guān)于MySQL8自增主鍵變化的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-04-04
MySQL數(shù)據(jù)庫字符集修改中文UTF8(永久修改)
本文主要介紹了MySQL數(shù)據(jù)庫字符集修改中文UTF8，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-06-06
這篇文章主要介紹了MySQL數(shù)據(jù)庫的觸發(fā)器的使用，文章圍繞主題展開詳細(xì)的內(nèi)容介紹，具有一定的參考價(jià)值，感興趣的小伙伴可以參考一下2022-09-09
這篇文章主要介紹了MySQL如何使用授權(quán)命令grant，文中示例代碼非常詳細(xì)，幫助大家更好的理解和學(xué)習(xí)，感興趣的朋友可以了解下2020-07-07
這篇文章主要為大家詳細(xì)介紹了Win7下mysql5.5安裝的圖文教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-05-05
詳解Ubuntu Server下啟動(dòng)/停止/重啟MySQL數(shù)據(jù)庫的三種方式
本篇文章主要介紹了buntu Server下啟動(dòng)/停止/重啟MySQL數(shù)據(jù)庫的三種方式，具有一定的參考價(jià)值，有興趣的可以了解一下。2017-01-01
mysql怎么關(guān)閉sql_mode=ONLY_FULL_GROUP_BY模式
前段時(shí)間在項(xiàng)目開發(fā)過程中發(fā)現(xiàn)了系統(tǒng)異常,打開日志查看發(fā)現(xiàn)了如下的這個(gè)報(bào)錯(cuò),查找相關(guān)資料終于解決了,這篇文章主要給大家介紹了關(guān)于mysql怎么關(guān)閉sql_mode=ONLY_FULL_GROUP_BY模式的相關(guān)資料,需要的朋友可以參考下2024-01-01
DBeaver連接mysql數(shù)據(jù)庫錯(cuò)誤圖文解決方案
這篇文章主要給大家介紹了關(guān)于DBeaver連接mysql數(shù)據(jù)庫錯(cuò)誤解決方案的相關(guān)資料,DBeaver是免費(fèi)、開源、通用數(shù)據(jù)庫工具,是許多開發(fā)開發(fā)人員和數(shù)據(jù)庫管理員的所選,需要的朋友可以參考下2023-11-11