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

這篇文章給大家分享的是有關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 )

感謝各位的閱讀！關于“mysql如何關聯更新刪除不走索引優化”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！