热门搜索
分布式
数据库一体机
云数据库
建设银行
保险核心
密集交易
注册
通过索引搞定一切 | 一个语句的多重分析 | 和优化器做朋友
专栏/大惊鱼的数据库学习之旅/ 文章详情 /

通过索引搞定一切 | 一个语句的多重分析 | 和优化器做朋友

hql 2021/11/28 2210 5 0
摘要 通过索引，我们可以搞定百分之八十的优化问题；不对，是百分之九十。但是，基础的索引建立规则，我们真的学会了吗？

构造基础数据

drop table if exists tab1;
drop table if exists tab2;
drop table if exists tab3;

create table tab1(v1 int,v2 int,v3 int,v4 int,v5 int,v6 int,id int,v7 varchar(30000));
create table tab2(v1 int,v2 int,v3 int,v4 int,v5 int,v6 int,id int,v7 varchar(30000));
create table tab3(v1 int,v2 int,v3 int,v4 int,v5 int,v6 int,id int,v7 varchar(30000));

待分析的SQL语句

select /*+stat(a 1000) stat(b 100M) stat(c 100M) */
        a.*,b.v7 vv7
from
        tab1 a,
        tab2 b
where
        a.v1  =b.v1
    and a.v2  =?
    and a.v2  >?
    and a.v3  =?
    and a.v4 in (?, ?, ?, ?)
    and
        (
                a.v5 =?
             or a.v6 =？
        )
    and a.id NOT IN
        (
                SELECT
                        id
                from
                        tab3 c
                where
                        v1    =1
                    and c.v2  =?
                    and c.v2  >?
                    and c.v3  =?
                    and c.v4 in (?, ?, ?, ?)
                    and
                        (
                                c.v5 =?
                             or c.v6 =？
                        )
        );

他的原始计划

1   #NSET2: [3898026, 1, 128] 
2     #PRJT2: [3898026, 1, 128]; exp_num(9), is_atom(FALSE) 
3       #SLCT2: [3898026, 1, 128]; (A.V5 = exp_param(no:7) OR A.V6 = exp_param(no:8))
4         #NEST LOOP SEMI JOIN2: [3898026, 1, 128];  (ANTI), join condition(A.ID = DMTEMPVIEW_16782019.colname)
5           #SLCT2: [361906, 1, 128]; (A.V1 = B.V1 AND exp_param(no:0) > exp_param(no:1))
6             #NEST LOOP INNER JOIN2: [361906, 1, 128]; 
7               #SLCT2: [1, 1, 76]; (A.V2 = exp_param(no:0) AND A.V3 = exp_param(no:2) AND A.V4 IN LIST)
8                 #CSCN2: [1, 1000, 76]; INDEX33556325(TAB1 as A)
9               #CSCN2: [11555, 100000000, 52]; INDEX33556326(TAB2 as B)
10          #PRJT2: [12017, 1, 28]; exp_num(1), is_atom(FALSE) 
11            #SLCT2: [12017, 1, 28]; (C.V1 = 1 AND C.V2 = exp_param(no:9) AND C.V3 = exp_param(no:11) AND exp_param(no:9) > exp_param(no:10) AND C.V4 IN LIST AND (C.V5 = exp_param(no:16) OR C.V6 = exp_param(no:17)))
12              #CSCN2: [12017, 100000000, 28]; INDEX33556327(TAB3 as C)

知识点1：我们要在条件列上建索引

优化操作

create index idx_tab1_v5 on tab1(v5);
create index idx_tab3_v5 on tab3(v5);

新的计划

1   #NSET2: [7969415, 2, 144] 
2     #PRJT2: [7969415, 2, 144]; exp_num(9), is_atom(FALSE) 
3       #UNION FOR OR2: [7969415, 2, 144]; key_num(2)
4         #NEST LOOP SEMI JOIN2: [3984702, 1, 144];  (ANTI), join condition(A.ID = DMTEMPVIEW_16782035.colname)
5           #SLCT2: [365217, 1, 144]; (A.V1 = B.V1 AND exp_param(no:0) > exp_param(no:1))
6             #NEST LOOP INNER JOIN2: [365217, 1, 144]; 
7               #SLCT2: [1, 1, 84]; (A.V2 = exp_param(no:0) AND A.V3 = exp_param(no:2) AND A.V4 IN LIST)
8                 #BLKUP2: [1, 25, 84]; IDX_TAB1_V5(A)
9                   #SSEK2: [1, 25, 84]; scan_type(ASC), IDX_TAB1_V5(TAB1 as A), scan_range[exp_param(no:7),exp_param(no:7)]
10              #CSCN2: [11719, 100000000, 60]; INDEX33556326(TAB2 as B)
11          #PRJT2: [14653, 2, 36]; exp_num(1), is_atom(FALSE) 
12            #UNION FOR OR2: [14653, 2, 36]; key_num(1)
13              #SLCT2: [2470, 1, 36]; (C.V1 = 1 AND C.V2 = exp_param(no:9) AND C.V3 = exp_param(no:11) AND exp_param(no:9) > exp_param(no:10) AND C.V4 IN LIST)
14                #BLKUP2: [2470, 2500000, 36]; IDX_TAB3_V5(C)
15                  #SSEK2: [2470, 2500000, 36]; scan_type(ASC), IDX_TAB3_V5(TAB3 as C), scan_range[exp_param(no:16),exp_param(no:16)]
16              #SLCT2: [12181, 1, 36]; (C.V1 = 1 AND C.V2 = exp_param(no:9) AND C.V3 = exp_param(no:11) AND C.V6 = exp_param(no:17) AND exp_param(no:9) > exp_param(no:10) AND C.V4 IN LIST)
17                #CSCN2: [12181, 100000000, 36]; INDEX33556327(TAB3 as C)
18        #NEST LOOP SEMI JOIN2: [3984711, 1, 144];  (ANTI), join condition(A.ID = DMTEMPVIEW_16782036.colname)
19          #SLCT2: [365218, 1, 144]; (A.V1 = B.V1 AND exp_param(no:0) > exp_param(no:1))
20            #NEST LOOP INNER JOIN2: [365218, 1, 144]; 
21              #SLCT2: [1, 1, 84]; (A.V6 = exp_param(no:8) AND A.V2 = exp_param(no:0) AND A.V3 = exp_param(no:2) AND A.V4 IN LIST)
22                #CSCN2: [1, 1000, 84]; INDEX33556325(TAB1 as A)
23              #CSCN2: [11719, 100000000, 60]; INDEX33556326(TAB2 as B)
24          #PRJT2: [14653, 2, 36]; exp_num(1), is_atom(FALSE) 
25            #UNION FOR OR2: [14653, 2, 36]; key_num(1)
26              #SLCT2: [2470, 1, 36]; (C.V1 = 1 AND C.V2 = exp_param(no:9) AND C.V3 = exp_param(no:11) AND exp_param(no:9) > exp_param(no:10) AND C.V4 IN LIST)
27                #BLKUP2: [2470, 2500000, 36]; IDX_TAB3_V5(C)
28                  #SSEK2: [2470, 2500000, 36]; scan_type(ASC), IDX_TAB3_V5(TAB3 as C), scan_range[exp_param(no:16),exp_param(no:16)]
29              #SLCT2: [12181, 1, 36]; (C.V1 = 1 AND C.V2 = exp_param(no:9) AND C.V3 = exp_param(no:11) AND C.V6 = exp_param(no:17) AND exp_param(no:9) > exp_param(no:10) AND C.V4 IN LIST)
30                #CSCN2: [12181, 100000000, 36]; INDEX33556327(TAB3 as C)

知识点2：or的两边都要建立

优化操作

create index idx_tab1_v6 on tab1(v6);
create index idx_tab3_v6 on tab3(v6);

新的计划

1   #NSET2: [7577084, 2, 144] 
2     #PRJT2: [7577084, 2, 144]; exp_num(9), is_atom(FALSE) 
3       #UNION FOR OR2: [7577084, 2, 144]; key_num(2)
4         #NEST LOOP SEMI JOIN2: [3788541, 1, 144];  (ANTI), join condition(A.ID = DMTEMPVIEW_16782052.colname)
5           #SLCT2: [365217, 1, 144]; (A.V1 = B.V1 AND exp_param(no:0) > exp_param(no:1))
6             #NEST LOOP INNER JOIN2: [365217, 1, 144]; 
7               #SLCT2: [1, 1, 84]; (A.V2 = exp_param(no:0) AND A.V3 = exp_param(no:2) AND A.V4 IN LIST)
8                 #BLKUP2: [1, 25, 84]; IDX_TAB1_V5(A)
9                   #SSEK2: [1, 25, 84]; scan_type(ASC), IDX_TAB1_V5(TAB1 as A), scan_range[exp_param(no:7),exp_param(no:7)]
10              #CSCN2: [11719, 100000000, 60]; INDEX33556326(TAB2 as B)
11          #PRJT2: [4942, 2, 36]; exp_num(1), is_atom(FALSE) 
12            #UNION FOR OR2: [4942, 2, 36]; key_num(1)
13              #SLCT2: [2470, 1, 36]; (C.V1 = 1 AND C.V2 = exp_param(no:9) AND C.V3 = exp_param(no:11) AND exp_param(no:9) > exp_param(no:10) AND C.V4 IN LIST)
14                #BLKUP2: [2470, 2500000, 36]; IDX_TAB3_V5(C)
15                  #SSEK2: [2470, 2500000, 36]; scan_type(ASC), IDX_TAB3_V5(TAB3 as C), scan_range[exp_param(no:16),exp_param(no:16)]
16              #SLCT2: [2470, 1, 36]; (C.V1 = 1 AND C.V2 = exp_param(no:9) AND C.V3 = exp_param(no:11) AND exp_param(no:9) > exp_param(no:10) AND C.V4 IN LIST)
17                #BLKUP2: [2470, 2500000, 36]; IDX_TAB3_V6(C)
18                  #SSEK2: [2470, 2500000, 36]; scan_type(ASC), IDX_TAB3_V6(TAB3 as C), scan_range[exp_param(no:17),exp_param(no:17)]
19        #NEST LOOP SEMI JOIN2: [3788541, 1, 144];  (ANTI), join condition(A.ID = DMTEMPVIEW_16782053.colname)
20          #SLCT2: [365217, 1, 144]; (A.V1 = B.V1 AND exp_param(no:0) > exp_param(no:1))
21            #NEST LOOP INNER JOIN2: [365217, 1, 144]; 
22              #SLCT2: [1, 1, 84]; (A.V2 = exp_param(no:0) AND A.V3 = exp_param(no:2) AND A.V4 IN LIST)
23                #BLKUP2: [1, 25, 84]; IDX_TAB1_V6(A)
24                  #SSEK2: [1, 25, 84]; scan_type(ASC), IDX_TAB1_V6(TAB1 as A), scan_range[exp_param(no:8),exp_param(no:8)]
25              #CSCN2: [11719, 100000000, 60]; INDEX33556326(TAB2 as B)
26          #PRJT2: [4942, 2, 36]; exp_num(1), is_atom(FALSE) 
27            #UNION FOR OR2: [4942, 2, 36]; key_num(1)
28              #SLCT2: [2470, 1, 36]; (C.V1 = 1 AND C.V2 = exp_param(no:9) AND C.V3 = exp_param(no:11) AND exp_param(no:9) > exp_param(no:10) AND C.V4 IN LIST)
29                #BLKUP2: [2470, 2500000, 36]; IDX_TAB3_V5(C)
30                  #SSEK2: [2470, 2500000, 36]; scan_type(ASC), IDX_TAB3_V5(TAB3 as C), scan_range[exp_param(no:16),exp_param(no:16)]
31              #SLCT2: [2470, 1, 36]; (C.V1 = 1 AND C.V2 = exp_param(no:9) AND C.V3 = exp_param(no:11) AND exp_param(no:9) > exp_param(no:10) AND C.V4 IN LIST)
32                #BLKUP2: [2470, 2500000, 36]; IDX_TAB3_V6(C)
33                  #SSEK2: [2470, 2500000, 36]; scan_type(ASC), IDX_TAB3_V6(TAB3 as C), scan_range[exp_param(no:17),exp_param(no:17)]

知识点3：关联列上也要建立索引

优化操作

create index idx_tab2_v2 on tab2(v1);

新的计划

1   #NSET2: [199693, 2, 144] 
2     #PRJT2: [199693, 2, 144]; exp_num(9), is_atom(FALSE) 
3       #UNION FOR OR2: [199693, 2, 144]; key_num(2)
4         #NEST LOOP SEMI JOIN2: [99846, 1, 144];  (ANTI), join condition(A.ID = DMTEMPVIEW_16782067.colname)
5           #SLCT2: [1, 1, 144]; exp_param(no:0) > exp_param(no:1)
6             #NEST LOOP INDEX JOIN2: [1, 1, 144] 
7               #SLCT2: [1, 1, 84]; (A.V2 = exp_param(no:0) AND A.V3 = exp_param(no:2) AND A.V4 IN LIST)
8                 #BLKUP2: [1, 25, 84]; IDX_TAB1_V5(A)
9                   #SSEK2: [1, 25, 84]; scan_type(ASC), IDX_TAB1_V5(TAB1 as A), scan_range[exp_param(no:7),exp_param(no:7)]
10              #BLKUP2: [1, 1, 0]; IDX_TAB2_V2(B)
11                #SSEK2: [1, 1, 0]; scan_type(ASC), IDX_TAB2_V2(TAB2 as B), scan_range[A.V1,A.V1]
12          #PRJT2: [4942, 2, 36]; exp_num(1), is_atom(FALSE) 
13            #UNION FOR OR2: [4942, 2, 36]; key_num(1)
14              #SLCT2: [2470, 1, 36]; (C.V1 = 1 AND C.V2 = exp_param(no:9) AND C.V3 = exp_param(no:11) AND exp_param(no:9) > exp_param(no:10) AND C.V4 IN LIST)
15                #BLKUP2: [2470, 2500000, 36]; IDX_TAB3_V5(C)
16                  #SSEK2: [2470, 2500000, 36]; scan_type(ASC), IDX_TAB3_V5(TAB3 as C), scan_range[exp_param(no:16),exp_param(no:16)]
17              #SLCT2: [2470, 1, 36]; (C.V1 = 1 AND C.V2 = exp_param(no:9) AND C.V3 = exp_param(no:11) AND exp_param(no:9) > exp_param(no:10) AND C.V4 IN LIST)
18                #BLKUP2: [2470, 2500000, 36]; IDX_TAB3_V6(C)
19                  #SSEK2: [2470, 2500000, 36]; scan_type(ASC), IDX_TAB3_V6(TAB3 as C), scan_range[exp_param(no:17),exp_param(no:17)]
20        #NEST LOOP SEMI JOIN2: [99846, 1, 144];  (ANTI), join condition(A.ID = DMTEMPVIEW_16782068.colname)
21          #SLCT2: [1, 1, 144]; exp_param(no:0) > exp_param(no:1)
22            #NEST LOOP INDEX JOIN2: [1, 1, 144] 
23              #SLCT2: [1, 1, 84]; (A.V2 = exp_param(no:0) AND A.V3 = exp_param(no:2) AND A.V4 IN LIST)
24                #BLKUP2: [1, 25, 84]; IDX_TAB1_V6(A)
25                  #SSEK2: [1, 25, 84]; scan_type(ASC), IDX_TAB1_V6(TAB1 as A), scan_range[exp_param(no:8),exp_param(no:8)]
26              #BLKUP2: [1, 1, 0]; IDX_TAB2_V2(B)
27                #SSEK2: [1, 1, 0]; scan_type(ASC), IDX_TAB2_V2(TAB2 as B), scan_range[A.V1,A.V1]
28          #PRJT2: [4942, 2, 36]; exp_num(1), is_atom(FALSE) 
29            #UNION FOR OR2: [4942, 2, 36]; key_num(1)
30              #SLCT2: [2470, 1, 36]; (C.V1 = 1 AND C.V2 = exp_param(no:9) AND C.V3 = exp_param(no:11) AND exp_param(no:9) > exp_param(no:10) AND C.V4 IN LIST)
31                #BLKUP2: [2470, 2500000, 36]; IDX_TAB3_V5(C)
32                  #SSEK2: [2470, 2500000, 36]; scan_type(ASC), IDX_TAB3_V5(TAB3 as C), scan_range[exp_param(no:16),exp_param(no:16)]
33              #SLCT2: [2470, 1, 36]; (C.V1 = 1 AND C.V2 = exp_param(no:9) AND C.V3 = exp_param(no:11) AND exp_param(no:9) > exp_param(no:10) AND C.V4 IN LIST)
34                #BLKUP2: [2470, 2500000, 36]; IDX_TAB3_V6(C)
35                  #SSEK2: [2470, 2500000, 36]; scan_type(ASC), IDX_TAB3_V6(TAB3 as C), scan_range[exp_param(no:17),exp_param(no:17)]

这里涉及到一个数据库优化器参数：

参数名称 OPTIMIZER_OR_NBEXP
默认值 0
参数属性 动态 会话级
参数含义 OR 表达式的优化方式。
取值说明 0： 不优化； 1： 生成UNION_FOR_OR 操作符时， 优化为无 KEY 比较方式；
2： OR 表达式优先考虑整体处理方式； 4：相关子查询的 OR 表达也优考虑整体处理方式；
8： OR 布尔表达式的范围合并优化；
16： 同一列上同时存在常量范围过滤和 IS NULL 过滤时的优化，如 C1 > 5 OR C1 IS NULL。
支持使用上述有效值的组合值，如 7 表示同时进行 1、 2、 4 的优化

说明：1）OPTIMIZER_OR_NBEXP为0的时候还是比较聪明的，看CSCN的时候，就没拆成or了；但如果走索引的话，又拆or了；说明默认值0的时候，是有不错的智能效果的；
2）因此，也可以说明我们还是不要轻易调整优化器相关的任何参数，除非明确这个参数对某个SQL有正收益，那么我们可以仅仅只可以通过 inject 让这个参数变更，对特定语句生效。（这种情况下，哪怕有影响，影响也非常可控）。

上一篇：
inject的用法 | 和优化器做朋友
评论
后发表回复

作者

文章

阅读量

获赞

扫一扫
联系客服
logo
logo
logo
鄂Copyright © 达梦国产化应用创新实验室
达梦在线服务平台社区用户管理规范

鄂ICP备18017926号-1