mysql 關聯更新刪除不走索引優化

關於update in不走索引的：

首先select子查詢形式是走索引的如下所示：

select * from  acct_trans_payment  where  autopayflag='N' and objectno in(

select serialno from acct_loan where businessstatus='1' and accountingorgid='10080201')

執行計劃如下：

然後select連線的形式：

select * from  acct_trans_payment a,acct_loan b   where a.objectno=b.serialno  and a.autopayflag='N' and b.businessstatus='1' and b.accountingorgid='10080201'；

執行計劃如下：

至此可以看出來，select 的in子查詢的形式優化器發生了內部轉換，轉換成了join連結的形式，提高的效能！

然而update的卻沒有自動轉換成join連結的形式，如下所示：

update acct_trans_payment set autopayflag='Y'  where autopayflag='N' and objectno in(

select serialno from acct_loan where businessstatus='1' and accountingorgid='10080201')

下文中有解釋執行計劃中的select_type中的dependent subquery的檢索過程。

所以手動改寫成join形式：

update acct_trans_payment a,acct_loan b set a.autopayflag='Y'  where a.objectno=b.serialno  and a.autopayflag='N' and b.businessstatus='1' and b.accountingorgid='10080201'

效率提高了。。。。

關於delete的優化過程：

delete from cfs.acct_trans_payment   where serialno in(

select serialno from jd.jd_flow where repaymentstype='05'

);

首先我們來解釋一下圖中的dependent subquery是什麼意思：手冊上的解釋是，子查詢中的 jd.jd_flow表的select，取決於外面的查詢。就這麼一句話，其實它表達的意思是：子查詢中的查詢方式依賴於外部（cfs.acct_payment_log）的查詢。換句話說就是jd.jd_flow表的檢索方式依賴於cfs.acct_payment_log表的資料，如這裡 cfs.acct_payment_log表得到的記錄serialno （where serialno  in）剛好可以被 jd.jd_flow表作為unique_subquery方式來獲得它的相應的記錄；換種寫法如果此時cfs.acct_payment_log表掃描第一條記錄得到的serialno為10001的話，那麼後面子查詢的語句就類似於這樣的語句：

select serialno from jd.jd_flow where repaymentstype='05' and serialno='10001'。此時這個語句就會被優化拿來優化，變成了上面的子查詢的執行計劃，由於jd.jd_flow的主鍵是serialno，所以會走主鍵索引。

通過這個解釋我們可以知道：全表掃描cfs.acct_payment_log表，將cfs.acct_payment_log的每條記錄傳遞給jd.jd_flow表，jd.jd_flow表通過主鍵索引方式來獲得記錄判斷自身的條件，則找到一個滿足此查詢的語句。

總結：當看到 select_type為dependent subquery的時候，就說明外表走的全表，然後把where value in  中的外表中的每個value值給子查詢表，然後遍歷結果！

當子查詢結果比較小的時候可以先把子查詢查出來，然後寫成如下形式：

select *  from cfs.acct_trans_payment  where serialno in(

'101071256426871193705',

'101184648601257984005',

'101366238550600089605',

'101506423110987776005',

'101699991116782796905',

'101872867624796569705',

'99235027109713920005')

對應的執行計劃：

那麼當子查詢結果集比較大的時候，改怎麼優化呢？

一樣藉助連線的形式

delete  a from   cfs.acct_trans_payment a join  jd.jd_flow b  where a.serialno =b.serialno  and b.repaymentstype='05'

等價於

delete from cfs.acct_trans_payment   where serialno in(

select serialno from jd.jd_flow where repaymentstype='05'

);

如下是兩個的執行計劃，顯然效能提升了不少！

又如：

delete  a ，b from   cfs.acct_trans_payment a join  jd.jd_flow b  where a.serialno =b.serialno  and b.repaymentstype='05'

等價於

delete from cfs.acct_trans_payment   where serialno in(

select serialno from jd.jd_flow where repaymentstype='05'

);

同時

delete from jd.jd_flow where repaymentstype='05'  and serialno in (select serialno  from 

cfs.acct_trans_payment)

也就是說會把兩個表的符合條件的都刪除。。。。。

題外話：關於delete的join形式：

delete from left join

DELETE A FROM YSHA A LEFT JOIN YSHB B ON A.code=b.code WHERE b.code is NULL；

等同於

DELETE FROM YSHA WHERE NOT EXISTS(SELECT 1 FROM YSHB B WHERE YSHA.code=b.code )；

注意delete的時候不允許起別名，如下會報錯！！！！

delete from cfs.acct_trans_payment  a  where EXISTS ( select serialno from jd.jd_flow b where b.repaymentstype='05' and a.serialno=b.serialno );

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a  where EXISTS ( select serialno from jd.jd_flow b where b.repaymentstype='05' ' at line 1

可以需要這樣：

delete from cfs.acct_trans_payment     where EXISTS ( select serialno from jd.jd_flow b where b.repaymentstype='05' and cfs.acct_trans_payment.serialno=b.serialno )

總結 ：update和delete都可以寫成join的連線的形式，in子查詢的形式執行計劃中select_type中的dependent subquery的檢索過程是外表是全表掃描，效能不佳，可以改寫成join的連線的形式來提高效能。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29654823/viewspace-2214437/，如需轉載，請註明出處，否則將追究法律責任。