MySQL中对于not in和minus使用的优化

优化前：

select count（t.id） from test t where t.status = 1and t.id not in （select distinct a.app_id from test2 a where a.type = 1and a.rule_id in （152, 153, 154）） 17:20:57 laojiu>@planPLAN_TABLE_OUTPUT————————————————————————————————————————-Plan hash value: 684502086—————————————————————————————-| Id | Operation| Name | Rows | Bytes | Cost （%CPU）| Time |—————————————————————————————-|0 | SELECT STATEMENT| | 1 |18 |176K （2）| 00:35:23 ||1 | SORT AGGREGATE | | 1 |18 || ||* 2 |FILTER| |||| ||* 3 |TABLE ACCESS FULL| test | 1141 | 20538 |845（2）| 00:00:11 ||* 4 |TABLE ACCESS FULL| test2 | 1 |12 |309（2）| 00:00:04 |—————————————————————————————-Predicate Information （identified by operation id）:—————————————————2 – filter（ NOT EXISTS （SELECT /*+ */ 0 FROM “test2″ “A” WHERE “A”.”type”=1 AND （“A”.”RULE_ID”=152 OR “A”.”RULE_ID”=153 OR “A”.”RULE_ID”=154） AND LNNVL（“A”.”APP_ID”<>:B1）））3 – filter（“T”.”status”=1）4 – filter（“A”.”type”=1 AND （“A”.”RULE_ID”=152 OR “A”.”RULE_ID”=153 OR “A”.”RULE_ID”=154） AND LNNVL（“A”.”APP_ID”<>:B1））Statistics———————————————————- 0 recursive calls 0 db block gets1762169 consistent gets 0 physical reads 0 redo size519 bytes sent via SQL*Net to client492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts （memory） 0 sorts （disk） 1 rows processed21 rows selected.

优化后：

 select count（*） from（ select t.idfrom test t where t.status = 1 minus select distinct a.app_idfrom test2 a where a.type = 1and a.rule_id in （152, 153, 154））17:23:33 laojiu>@planPLAN_TABLE_OUTPUT————————————————————————————————————————-Plan hash value: 631655686————————————————————————————————–| Id | Operation | Name | Rows | Bytes |TempSpc| Cost （%CPU）| Time |————————————————————————————————–|0 | SELECT STATEMENT | | 1 ||| 1501（2）| 00:00:19 ||1 | SORT AGGREGATE| | 1 |||| ||2 |VIEW| | 1141 ||| 1501（2）| 00:00:19 ||3 |MINUS | ||||| ||4 | SORT UNIQUE| | 1141 | 20538 ||846（2）| 00:00:11 ||* 5 | TABLE ACCESS FULL| test | 1141 | 20538 ||845（2）| 00:00:11 ||6 | SORT UNIQUE| | 69527 |814K| 3632K|654（2）| 00:00:08 ||* 7 | TABLE ACCESS FULL| test2 | 84140 |986K||308（2）| 00:00:04 |————————————————————————————————–Predicate Information （identified by operation id）:—————————————————5 – filter（“T”.”status”=1）7 – filter（“A”.”type”=1 AND （“A”.”RULE_ID”=152 OR “A”.”RULE_ID”=153 OR “A”.”RULE_ID”=154））21 rows selected.Statistics———————————————————- 1 recursive calls 0 db block gets2240 consistent gets 0 physical reads 0 redo size516 bytes sent via SQL*Net to client492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts （memory） 0 sorts （disk） 1 rows processed

在优化sql的时候，我们需要转变一下思路，等价的改写sql；
改写后的sql由于逻辑读得到了天翻地覆的改变，很快得到结果。
第一条sql执行计划中有一个函数，LNNVL（“A”.”APP_ID”<>:B1），lnnvl（exp）
如果exp的结果是false或者是unknown,那么lnnvl返回true;
如果exp的结果是true,返回false.