MySQL中对于not in和minus使用的优化
优化前：
selectcount(t.id) fromtestt wheret.status=1 andt.idnotin(selectdistincta.app_id fromtest2a wherea.type=1 anda.rule_idin(152,153,154)) 17:20:57laojiu>@plan PLAN_TABLE_OUTPUT ————————————————————————————————————————- Planhashvalue:684502086 —————————————————————————————- |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time| —————————————————————————————- |0|SELECTSTATEMENT||1|18|176K(2)|00:35:23| |1|SORTAGGREGATE||1|18||| |*2|FILTER|||||| |*3|TABLEACCESSFULL|test|1141|20538|845(2)|00:00:11| |*4|TABLEACCESSFULL|test2|1|12|309(2)|00:00:04| —————————————————————————————- PredicateInformation(identifiedbyoperationid): ————————————————— 2–filter(NOTEXISTS(SELECT/*+*/0FROM“test2″“A”WHERE “A”.”type”=1AND(“A”.”RULE_ID”=152OR“A”.”RULE_ID”=153OR “A”.”RULE_ID”=154)ANDLNNVL(“A”.”APP_ID”<>:B1))) 3–filter(“T”.”status”=1) 4–filter(“A”.”type”=1AND(“A”.”RULE_ID”=152OR“A”.”RULE_ID”=153OR “A”.”RULE_ID”=154)ANDLNNVL(“A”.”APP_ID”<>:B1)) Statistics ———————————————————- 0recursivecalls 0dbblockgets 1762169consistentgets 0physicalreads 0redosize 519bytessentviaSQL*Nettoclient 492bytesreceivedviaSQL*Netfromclient 2SQL*Netroundtripsto/fromclient 0sorts(memory) 0sorts(disk) 1rowsprocessed 21rowsselected.
优化后：
selectcount(*)from( selectt.id fromtestt wheret.status=1 minus selectdistincta.app_id fromtest2a wherea.type=1 anda.rule_idin(152,153,154)) 17:23:33laojiu>@plan PLAN_TABLE_OUTPUT ————————————————————————————————————————- Planhashvalue:631655686 ————————————————————————————————– |Id|Operation|Name|Rows|Bytes|TempSpc|Cost(%CPU)|Time| ————————————————————————————————– |0|SELECTSTATEMENT||1|||1501(2)|00:00:19| |1|SORTAGGREGATE||1||||| |2|VIEW||1141|||1501(2)|00:00:19| |3|MINUS||||||| |4|SORTUNIQUE||1141|20538||846(2)|00:00:11| |*5|TABLEACCESSFULL|test|1141|20538||845(2)|00:00:11| |6|SORTUNIQUE||69527|814K|3632K|654(2)|00:00:08| |*7|TABLEACCESSFULL|test2|84140|986K||308(2)|00:00:04| ————————————————————————————————– PredicateInformation(identifiedbyoperationid): ————————————————— 5–filter(“T”.”status”=1) 7–filter(“A”.”type”=1AND(“A”.”RULE_ID”=152OR“A”.”RULE_ID”=153OR “A”.”RULE_ID”=154)) 21rowsselected. Statistics ———————————————————- 1recursivecalls 0dbblockgets 2240consistentgets 0physicalreads 0redosize 516bytessentviaSQL*Nettoclient 492bytesreceivedviaSQL*Netfromclient 2SQL*Netroundtripsto/fromclient 2sorts(memory) 0sorts(disk) 1rowsprocessed
在优化sql的时候，我们需要转变一下思路，等价的改写sql；
改写后的sql由于逻辑读得到了天翻地覆的改变，很快得到结果。
第一条sql执行计划中有一个函数，LNNVL(“A”.”APP_ID”<>:B1)，lnnvl(exp)
如果exp的结果是false或者是unknown,那么lnnvl返回true;
如果exp的结果是true,返回false.