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

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.

(责任编辑：IT)

搜索

热门标签:

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