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

优化前：

select count(t.id)
 from test t
 where t.status = 1
  and t.id not in (select distinct a.app_id
           from test2 a
           where a.type = 1
            and a.rule_id in (152, 153, 154))
      
 17:20:57 laojiu>@plan

plan_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 gets
  1762169 consistent gets
     0 physical reads
     0 redo size
    519 bytes sent via sql*net to client
    492 bytes received via sql*net from client
     2 sql*net roundtrips to/from client
     0 sorts (memory)
     0 sorts (disk)
     1 rows processed
21 rows selected.

优化后：

 select count(*) from(
 select t.id
  from test t
 where t.status = 1
 minus
 select distinct a.app_id
  from test2 a
 where a.type = 1
  and a.rule_id in (152, 153, 154))
17:23:33 laojiu>@plan

plan_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 gets
    2240 consistent gets
     0 physical reads
     0 redo size
    516 bytes sent via sql*net to client
    492 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.