《Mysql必讀mysql優(yōu)化系列 DELETE子查詢改寫優(yōu)化》要點(diǎn):
本文介紹了Mysql必讀mysql優(yōu)化系列 DELETE子查詢改寫優(yōu)化,希望對您有用。如果有疑問,可以聯(lián)系我們。
MYSQL數(shù)據(jù)庫1、問題描述
MYSQL數(shù)據(jù)庫朋友遇到一個怪事,一個用子查詢的DELETE,執(zhí)行效率非常低.把DELETE改成SELECT后執(zhí)行起來卻很快,百思不得其解.
MYSQL數(shù)據(jù)庫下面就是這個用了子查詢的DELETE了:
MYSQL數(shù)據(jù)庫
[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
MYSQL數(shù)據(jù)庫
MYSQL數(shù)據(jù)庫幾個表的DDL是這樣的:
MYSQL數(shù)據(jù)庫
MYSQL數(shù)據(jù)庫上面這個SQL的執(zhí)行耗時是:31.74秒
Query OK, 5 rows affected (31.74 sec)
如果我們把DELETE改寫成SELECT的話,執(zhí)行耗時僅是:0秒,來對比看下執(zhí)行計(jì)劃:
MYSQL數(shù)據(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
MYSQL數(shù)據(jù)庫
MYSQL數(shù)據(jù)庫可以看到,trade_info 表從的全表掃描(type=ALL)變成了基于主鍵的等值查詢(type=eq_ref),計(jì)劃掃描數(shù)據(jù)量也從571萬變成了1條,而且還可以避免回表,這2個SQL對比代價相差巨大.
MYSQL數(shù)據(jù)庫2、優(yōu)化思路
MYSQL數(shù)據(jù)庫既然這個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,例如這樣的:
MYSQL數(shù)據(jù)庫DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
MYSQL數(shù)據(jù)庫參照上面的形式,改寫之后的SQL變成了下面這樣:
MYSQL數(shù)據(jù)庫
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;
MYSQL數(shù)據(jù)庫
MYSQL數(shù)據(jù)庫可以看到新的SQL執(zhí)行效率相對就高很多了,不需要再掃描571萬條記錄,執(zhí)行耗時只需:0.01秒.
MYSQL數(shù)據(jù)庫Query OK, 5 rows affected (0.01 sec)
MYSQL數(shù)據(jù)庫3、其他建議
MYSQL數(shù)據(jù)庫雖然MySQL 5.6及以上的版本對子查詢做了優(yōu)化,但從本案例的結(jié)果來看,在一些情況下還是不如意.
因此,如果發(fā)現(xiàn)有些子查詢SQL效率比較差的話,可以嘗試改寫成JOIN形式,看看是否有所提升.此外,也要勇于懷疑查詢優(yōu)化器個別情況下存在不足,想辦法繞過這些坑.
轉(zhuǎn)載請注明本頁網(wǎng)址:
http://www.snjht.com/jiaocheng/2089.html