把notin改成notexists后的優(yōu)化

 

把not in 改成not exists后的優(yōu)化

近期，OA數(shù)據(jù)庫(kù)里面存在一條慢SQL，其執(zhí)行時(shí)間為3分16秒。這條慢SQL語(yǔ)句每個(gè)月可能會(huì)運(yùn)行幾次，但其運(yùn)行后，總會(huì)導(dǎo)致數(shù)據(jù)庫(kù)CPU利用率飆升。然后我就對(duì)這個(gè)慢SQL語(yǔ)句進(jìn)行了改寫(xiě)測(cè)試，改寫(xiě)后的運(yùn)行時(shí)間降為13s（雖然還是很慢，但已經(jīng)速度提升了18倍）。

具體分析過(guò)程如下：

通過(guò)慢日志捕捉到的慢SQL及其運(yùn)行時(shí)間：

1 select id,start_member_id,start_date,modify_member_id,modify_date from formmain_0141 where id not in (select content_data_id from ctp_content_all where content_template_id='6890363387462501722' and content_data_id is not null  ) limit 20000, 10000\G

可見(jiàn)，生產(chǎn)中，該語(yǔ)句運(yùn)行時(shí)間是3分2秒。

我們來(lái)看看其執(zhí)行計(jì)劃，為什么這么慢：

2、我改寫(xiě)后的索引，用的是 not exists ，內(nèi)外交互式子查詢：

MySQL> select id,start_member_id,start_date,modify_member_id,modify_date from formmain_0141 where not exists (select 1 from ctp_content_all where content_data_id= formmain_0141.id and content_data_id is not null and content_template_id='6890363387462501722') limit 20000, 10000 ;
Empty set (13.84 sec)

看到用not exists后，執(zhí)行時(shí)間降到13秒，效率有顯著提升。

我們?cè)倏匆幌聝?yōu)化后語(yǔ)句的執(zhí)行計(jì)劃：

把not in改寫(xiě)為not exists快的原因，我想用mysql 5.6的新特性ICP的原理來(lái)解釋，在改寫(xiě)后的sql語(yǔ)句中，MySQL在從 ctp_content_all表中取出數(shù)據(jù)的同時(shí)，就開(kāi)始判斷是否可以在formmain_0141表中進(jìn)行id過(guò)濾，從而大大減少了上層對(duì)SQL層的記錄索引，提高數(shù)據(jù)庫(kù)整體性能。

反觀優(yōu)化前的那條sql語(yǔ)句，它是把 ctp_content_all 表里面所有符合條件的記錄都取出來(lái)后，再到 formmain_0141表里進(jìn)行id字段過(guò)濾，所以慢。