Windows Linux/Shell Nginx UNIX
MySQL Ms SQL Server MS Access MS DOS命令 Oracle SQLite MongoDB Redis SSDB Memcached Cassandra Hibernate MariaDB MyBatis Slick Spark Kubernetes neo4j TiDB PostgreSQL InfluxDB Istio IoTDB
用户登录
用户注册

分享至

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

  • 作者: 冬瓜10263302
  • 来源: 51数据库
  • 2020-08-15
MySQL中对于not in和minus使用的优化,作者给出了实例和运行时间对比



优化前：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
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.

优化后：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
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.


推荐阅读
热点文章

Copyright 2018 51数据库(www.51sjk.com)

软件
前端设计
程序设计
Java相关