這篇文章主要介紹了MySQL中對(duì)于not in和minus使用的優(yōu)化,作者給出了實(shí)例和運(yùn)行時(shí)間對(duì)比,需要的朋友可以參考下

優(yōu)化前：

1. select count(t.id) 
2. from test t 
3. where t.status = 1 
4. and t.id not in (select distinct a.app_id 
5. from test2 a 
6. where a.type = 1 
7. and a.rule_id in (152, 153, 154)) 
8.  
9. 17:20:57 laojiu>@plan 
10.  
11. PLAN_TABLE_OUTPUT 
12. ————————————————————————————————————————- 
13. Plan hash value: 684502086 
14.  
15. —————————————————————————————- 
16. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
17. —————————————————————————————- 
18. | 0 | SELECT STATEMENT | | 1 | 18 | 176K (2)| 00:35:23 | 
19. | 1 | SORT AGGREGATE | | 1 | 18 | | | 
20. |* 2 | FILTER | | | | | | 
21. |* 3 | TABLE ACCESS FULL| test | 1141 | 20538 | 845 (2)| 00:00:11 | 
22. |* 4 | TABLE ACCESS FULL| test2 | 1 | 12 | 309 (2)| 00:00:04 | 
23. —————————————————————————————- 
24.  
25. Predicate Information (identified by operation id): 
26. ————————————————— 
27.  
28. 2 – filter( NOT EXISTS (SELECT /*+ */ 0 FROM “test2″ “A” WHERE 
29. “A”.”type”=1 AND (“A”.”RULE_ID”=152 OR “A”.”RULE_ID”=153 OR 
30. “A”.”RULE_ID”=154) AND LNNVL(“A”.”APP_ID”<>:B1))) 
31. 3 – filter(“T”.”status”=1) 
32. 4 – filter(“A”.”type”=1 AND (“A”.”RULE_ID”=152 OR “A”.”RULE_ID”=153 OR 
33. “A”.”RULE_ID”=154) AND LNNVL(“A”.”APP_ID”<>:B1)) 
34. Statistics 
35. ———————————————————- 
36. 0 recursive calls 
37. 0 db block gets 
38. 1762169 consistent gets 
39. 0 physical reads 
40. 0 redo size 
41. 519 bytes sent via SQL*Net to client 
42. 492 bytes received via SQL*Net from client 
43. 2 SQL*Net roundtrips to/from client 
44. 0 sorts (memory) 
45. 0 sorts (disk) 
46. 1 rows processed 
47. 21 rows selected. 

優(yōu)化后：

1. select count(*) from( 
2. select t.id 
3. from test t 
4. where t.status = 1 
5. minus 
6. select distinct a.app_id 
7. from test2 a 
8. where a.type = 1 
9. and a.rule_id in (152, 153, 154)) 
10. 17:23:33 laojiu>@plan 
11.  
12. PLAN_TABLE_OUTPUT 
13. ————————————————————————————————————————- 
14. Plan hash value: 631655686 
15.  
16. ————————————————————————————————– 
17. | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | 
18. ————————————————————————————————– 
19. | 0 | SELECT STATEMENT | | 1 | | | 1501 (2)| 00:00:19 | 
20. | 1 | SORT AGGREGATE | | 1 | | | | | 
21. | 2 | VIEW | | 1141 | | | 1501 (2)| 00:00:19 | 
22. | 3 | MINUS | | | | | | | 
23. | 4 | SORT UNIQUE | | 1141 | 20538 | | 846 (2)| 00:00:11 | 
24. |* 5 | TABLE ACCESS FULL| test | 1141 | 20538 | | 845 (2)| 00:00:11 | 
25. | 6 | SORT UNIQUE | | 69527 | 814K| 3632K| 654 (2)| 00:00:08 | 
26. |* 7 | TABLE ACCESS FULL| test2 | 84140 | 986K| | 308 (2)| 00:00:04 | 
27. ————————————————————————————————– 
28.  
29. Predicate Information (identified by operation id): 
30. ————————————————— 
31.  
32. 5 – filter(“T”.”status”=1) 
33. 7 – filter(“A”.”type”=1 AND (“A”.”RULE_ID”=152 OR “A”.”RULE_ID”=153 OR 
34. “A”.”RULE_ID”=154)) 
35.  
36. 21 rows selected. 
37. Statistics 
38. ———————————————————- 
39. 1 recursive calls 
40. 0 db block gets 
41. 2240 consistent gets 
42. 0 physical reads 
43. 0 redo size 
44. 516 bytes sent via SQL*Net to client 
45. 492 bytes received via SQL*Net from client 
46. 2 SQL*Net roundtrips to/from client 
47. 2 sorts (memory) 
48. 0 sorts (disk) 
49. 1 rows processed 

在優(yōu)化sql的時(shí)候，我們需要轉(zhuǎn)變一下思路，等價(jià)的改寫sql;

改寫后的sql由于邏輯讀得到了天翻地覆的改變，很快得到結(jié)果。

第一條sql執(zhí)行計(jì)劃中有一個(gè)函數(shù)，LNNVL(“A”.”APP_ID”<>:B1)，lnnvl(exp)

如果exp的結(jié)果是false或者是unknown,那么lnnvl返回true;

如果exp的結(jié)果是true,返回false.