达梦数据库sql优化基础
1 查看执行计划
1.1在管理工具中查看
Explain + sql 可以查看执行计划，可以看到未实际执行的执行计划
explain select * from test;
1 #NSET2: [1, 1, 60]
2 #PRJT2: [1, 1, 60]; exp_num(3), is_atom(FALSE)
3 #CSCN2: [1, 1, 60]; INDEX33583779(TEST)
1.2disql命令行中查看
SQL>SET AUTOTRACE TRACE
---会话级别设置MONITOR_SQL_EXEC，只对当前会话生效
SQL>SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC',1);
SQL> sql xxxxxx
已用时间: 20.522(毫秒). 执行号:288231112.
SQL> SET PAGES 1000
SQL> ET(288231111); 查看每一步计划的代价排序
2 执行计划阅读
explain select * from test;
1 #NSET2: [1, 1, 60]
2 #PRJT2: [1, 1, 60]; exp_num(3), is_atom(FALSE)
3 #CSCN2: [1, 1, 60]; INDEX33583779(TEST)
1)一个执行计划由若干个计划节点组成，如上面的1、2、3。
2)每个计划节点中包含操作符(CSCN2)和它的代价([1, 1, 60])等信息。
3)代价由一个三元组组成[代价,估算行数->实际行数,字节数]。如：[1, 1->1, 60];
4)代价的单位是毫秒，记录行数表示该计划节点输出的行数，字节数表示该计划节点输出的字节数。
5)执行顺序是3->2->1,缩进越多的越先执行，同样缩进的上面的先执行，下面的后执行，上下的优先级高于内外。
3 执行计划操作符
在描述操作符之前先了解下达梦数据表的存储方式：
聚簇索引存储：是指索引的存储包括索引字段键值和整行数据
二级索引存储：是指索引的存储只包括索引字段的键值和rowid。
达梦的表默认是创建成索引组织表，并且在未指定聚簇索引列时，会默认使用rowid作为表的聚簇索引列，即rowid来组织表的数据，其他索引都是二级索引。
3.1单表操作符
操作符 参数说明 操作说明
CSCN2 idxname(tabname) ：索引名（表名） 聚簇索引扫描，从头到尾，全部扫描。
SSCN idxname(tabname) ：索引名（表名） 二级索引扫描, 从头到尾，全部扫描。
CSEK2 scan_type：扫描类型，idxname(tabname) ：索引名（表名），scan_range：扫描范围 聚簇索引范围扫描，通过键值精准定位到范围或者单值。
SSEK2 scan_type：扫描类型，idxname(tabname) ：索引名（表名），scan_range：扫描范围 直接使用二级索引进行扫描，通过键值精准定位到范围或者单值。
BLKUP2 idxname(tabname) ：索引名（表名） 根据二级索引的ROWID 回原表中取出全部数据。
SLCT2 (condition)：过滤条件 关系的选择（select）运算，用于查询条件的过滤
PRJT2 exp_num：映射列数，is_atom：是否要求单行数据。 关系的投影(project) 运算，用于选择表达式项的计算
3.2表关联操作符
操作符 参数说明 操作说明
NEST LOOP FULL JOIN2 join_condition：连接条件 嵌套循环全外连接
NEST LOOP INDEX JOIN2 join_condition：连接条件 索引内连接
NEST LOOP INNER JOIN2 join_condition：连接条件 嵌套循环内连接
NEST LOOP LEFT JOIN2 join_condition：连接条件 嵌套循环左外连接
NEST LOOP SEMI JOIN2 join_condition：连接条件, (ANTI)：是否为反连接 嵌套循环全外连接
HASH (INNER LEFT RIGHT SEMI) JOIN join_condition：连接条件 哈希连接。
INDEX (INNER LEFT RIGHT SEMI) JOIN join_condition：连接条件 索引连接
MERGE JOIN KEY：等值连接条件 排序归并连接
Nest loop inner join
最基础的一种连接方式，将一张表的每一个值分别与另一张表的所有值拼接，形成一个大结果集，再从大结果集中过滤出满足条件的行。
两层嵌套循环结构，有驱动表和被驱动表之分，选定一张表作为驱动表，遍历驱动表中的每一行，根据连接条件去匹配第二张表中的行。驱动表的行数就是循环的次数，这个很大程度影响了执行效率。
需注意的问题，一选择小表作为驱动表，统计信息尽量准确，保证优化器选对驱动表；二大量的随机读，如果没有索引，随机读很致命，每次循环只能读一块，不能读多块。使用索引可以解决这个问题。
使用场景：
- 驱动表有很好的过滤条件。
- 表连接条件能使用索引。
- 结果集比较小。
Hash join
没有索引的情况下，大多数连接的处理方式，是将一张表的连接列做成HASH表，另一张表的数据向这个HASH表匹配，满足条件的值返回。
哈希连接是在没有索引或索引无法使用情况下大多数连接的处理方式。哈希连接使用关联列去重后结果集较小的表做HASH表，另一张表的连接列在HASH 后向 HASH 表进行匹配，这种情况下匹配速度极快，主要开销在于对连接表的全表扫描以及 HASH 运算。
hash join特点：
- 一般没索引或用不上索引时会使用该连接方式。
- 选择小的表(或row source)做hash表。
- 只适用等值连接中的情形。
由于hash连接比较消耗内存，如果系统有很多这种连接时，需调整以下3个参数:
HJ_BUF_GLOBAL_SIZE
HJ_BUF_SIZE
HJ_BLK_SIZE
Index join
将一张表（T1）的数据拿出，去另外一张表（T2）上进行范围扫描找出需要的数据行。索引连接需要右表的连接列上存在索引。
Merge join
需要两张表的连接列都有索引，对两张表扫描索引后按照索引顺序进行归并。这里需要同时 SSCN 两条有序索引，将其中满足条件的值输出到结果集，效率比 NEST LOOP 要高很多，不考虑其他条件，如果 T1 和 T2 都很大的情况下跟 HASH JOIN 的效率相当（HASH JOIN是CSCN两张基表，MERGE JOIN 则 SSCN 相关索引）
4 优化器常用hint
4.1 INI 参数hint
DBA 可以通过 HINT 方式对 INI 参数的值进行语句级的指定。语句中的 HINT 对 INI参数值的设置优先级高于 INI 文件中参数值的设置。通过 HINT 方式只会修改 INI 参数的在本会话中的值，不会改变它在 INI 文件中的值。
支持使用 HINT 的 INI 参数可通过 V$HINT_INI_INFO 动态视图查询。支持 HINT 的INI 参数分为两类：一是 HINT_TYPE 为―OPT，表示分析阶段使用的参数；二是 HINT_TYPE
为―EXEC，表示运行阶段使用的参数，运行阶段使用的参数对于视图无效。
例如：
SELECT /+ENABLE_HASH_JOIN(1)/ * FROM T1,T2 WHERE C1=D1;
上面的语句中使用了HINT，指明在执行此SQL时参数ENABLE_HASH_JOIN被置为1。
4.2索引hint
使用特定索引
语法：
表名 + INDEX + 索引名 或 /*+ INDEX (表名[,] 索引名) {INDEX (表名[,] 索引名)} /
示例：
SELECT * FROM T1 INDEX IDX_T1_ID WHERE ID > 2011 AND NAME < 'XXX';
或
SELECT /+INDEX(T1, IDX_T1_ID) / * FROM T1 WHERE ID > 2011 AND NAME < 'XXX';
不使用特定索引
/+ NO_INDEX (表名[,] 索引名) { NO_INDEX (表名[,] 索引名)} */
4.3连接方式hint
DBA 可以通过指定两个表间的连接方法来检测不同连接方式的查询效率，指定的连接可
能由于无法实现或代价过高而被忽略。如果连接方法提示中的表名(别名)或索引名无效也会
被自动忽略。
- USE_HASH
强制两个表间使用指定顺序的哈希连接，例如：
EXPLAIN SELECT /*+ USE_HASH(T1, T2) */ * FROM T1, T2 WHERE T1.ID = T2.ID; - NO_USE_HASH
强制两个表间不能使用指定顺序的哈希连接，例如：
EXPLAIN SELECT /*+ NO_USE_HASH(T1, T2) */ * FROM T1, T2 WHERE T1.ID = T2.ID;
NO_USE_HASH(T1, T2)表示不允许 T1 作为左表， T2 作为右表的哈希连接，但 T1 作
为右表的哈希连接还是允许的。 - USE_NL
强制两个表间使用嵌套循环连接，例如：
EXPLAIN SELECT /*+ USE_NL(A, B) */ * FROM T1 A, T2 B WHERE A.ID = B.ID; - NO_USE_NL
强制两个表间不能使用嵌套循环连接，例如：
EXPLAIN SELECT /*+ NO_USE_NL(A, B) */ * FROM T1 A, T2 B WHERE A.ID = B.ID; - USE_NL_WITH_INDEX
当连接情况为左表+右表索引时，强制两个表间使用索引连接，例如：
EXPLAIN SELECT /*+ USE_NL_WITH_INDEX(T1, IDX_T2_ID) */ * FROM T1, T2 WHERE
T1.ID = T2.ID; - NO_USE_NL_WITH_INDEX
当连接情况为左表+右表索引时，强制两个表间不能使用索引连接，例如：
EXPLAIN SELECT /*+ NO_USE_NL_WITH_INDEX(T1, IDX_T2_ID) */ * FROM T1, T2 WHERE T1.ID
= T2.ID; - USE_MERGE
强制两个表间使用归并连接。归并连接所用的两个列都必须是索引列。例如：
EXPLAIN SELECT /*+ USE_MERGE(T1,T2) */ * FROM T1, T2 WHERE T1.ID = T2.ID AND
T1.ID < 1 AND T2.ID < 1;
当连接类型为外连接时，无法使用归并连接，此时即使指定 USE_MERGE，也不起作用。 - NO_USE_MERGE
强制两个表间不能使用归并连接，例如：
EXPLAIN SELECT /+ NO_USE_MERGE(T1,T2) / * FROM T1, T2 WHERE T1.ID = T2.ID AND
T1.ID > 1 AND T2.ID > 1;
4.4连接顺序hint
多表连接时优化器会考虑各种可能的排列组合顺序。使用 ORDER HINT 指定连接顺序
提示可以缩小优化器试探的排列空间，进而得到接近 DBA 所期望的查询计划。如果连接顺序和连接方法提示同时指定且二者间存在自相矛盾，优化器会以连接顺序提示为准。
语法：
/+ ORDER (T1, T2 , T3, … tn ) /
示例：
SELECT /+ ORDER(T1, T2, T3 )/* FROM T1, T2 , T3, T4 WHERE …
在指定上述连接顺序后，T4,T1,T2,T3 或 T1,T2,T4,T3 会被考虑；T3,T1,T2 或T1,T3,T2 不被考虑。
5 Sql优化案例
问题sql1
select /*+ENABLE_RQ_TO_NONREF_SPL(3) enable_index_filter(1)*/* from (select AB.* , to_char(col14 ,'yyyy-mm-dd hh24:mi:ss') col1
from ( select T_TABLE1.col1,
T_TABLE4.col2,
T_TABLE4.col3 col3,
T_TABLE4.col4,
T_TABLE1.col5,
T_TABLE1.col6,
T_TABLE3.col7,
T_TABLE3.col8,
T_TABLE1.col9,
T_TABLE1.col10 col10,
T_TABLE2.col11,
T_TABLE2.col12,
STATUS_t.col13 col13,
T_TABLE1.col14 || T_TABLE1.col14 col14
from T_TABLE1
inner join T_TABLE2
on T_TABLE1.col01 = T_TABLE2.col01
inner join T_TABLE3
on T_TABLE1.col01 = T_TABLE3.col01
inner join T_TABLE4
on T_TABLE1.col02 = T_TABLE4.col02
left join T_TABLE5 T_TABLE5
on T_TABLE2.col03 = T_TABLE5.col03
and STATUS_t.col04 = 'ISVIEWED'
where T_TABLE2.col01 = '2111'
and T_TABLE2.col05 in (1, 3)
and T_TABLE4.col06 = 1
and T_TABLE1.col03 >= 2) AB ) tab where 1 = 1 and col07 like '%' || ? || '%' order by col14 desc LIMIT 0 ,10 ;
执行计划和成本排序如下：
从信息中可以看出第16,17个操作符占用了很多时间，17操作符根据18索引扫描出来的数据去回表，把满足条件的整条记录找出来然后再去16操作做条件过滤，最后过滤数据为0条，如果索引能提前过滤出来数据为0，就不用大量数据回表查找再过滤，性能就能提升不少。
1 #NSET2: [66, 10, 852]
2 #PRJT2: [66, 10, 852]; exp_num(15), is_atom(FALSE)
3 #SORT3: [66, 10, 852]; key_num(1), is_distinct(FALSE), is_adaptive(0)
4 #PRJT2: [65, 372, 852]; exp_num(15), is_atom(FALSE)
5 #PRJT2: [65, 372, 852]; exp_num(14), is_atom(FALSE)
6 #HASH RIGHT JOIN2: [65, 372, 852]; key_num(1); col_num(12)
7 #BLKUP2: [1, 2->2, 144]; T_TABLE5_U(T_TABLE5)
8 #SSEK2: [1, 2->2, 144]; scan_type(ASC), T_TABLE5_U(T_TABLE5)
9 #HASH RIGHT SEMI JOIN2: [64, 372, 708]; key_num(1)
10 #CONST VALUE LIST: [1, 2, 30]; row_num(2), col_num(1),
11 #NEST LOOP INDEX JOIN2: [64, 372, 708]
12 #HASH2 INNER JOIN: [59, 372, 570]; RKEY_UNIQUE KEY_NUM(1);
13 #HASH2 INNER JOIN: [57, 372, 474]; LKEY_UNIQUE KEY_NUM(1);
14 #SLCT2: [1, 64->64, 126];
15 #CSCN2: [1, 404->404, 126]; INDEX33559636(T_TABLE4)
16 #SLCT2: [56, 1672->0, 348];
17 #BLKUP2: [56, 37856->1006380, 348]; T_TABLE1_I_TITLE(T_TABLE1)
18 #SSEK2: [56, 37856->1006380, 348]; scan_type(ASC), T_TABLE1_I_TITLE(T_TABLE1)
19 #CSCN2: [1, 6273, 96]; INDEX33559023(T_TABLE3)
20 #BLKUP2: [2, 1, 108]; INDEX33559960(T_TABLE2)
21 #SSEK2: [2, 1, 108]; scan_type(ASC), INDEX33559960(T_TABLE2)
已用时间: 00:00:12.058. 执行号:296129204.
SQL> et(296129204)
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER HASH_USED_CELLS HASH_CONFLICT
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- --------------------
1 PRJT2 0 0% 18 2 2 0 0
2 PRJT2 1 0% 17 4 2 0 0
3 DLCK 2 0% 15 0 2 0 0
4 PRJT2 2 0% 15 5 2 0 0
5 IJI2 3 0% 13 11 2 0 0
6 HI3 3 0% 13 12 2 0 0
7 HRS2 7 0% 12 9 2 0 0
8 SORT3 16 0% 11 3 2 0 0
9 BLKUP2 17 0% 10 7 4 0 0
10 SLCT2 21 0% 9 14 6 0 0
11 SSEK2 28 0% 8 8 2 0 0
12 NSET2 66 0% 7 1 2 0 0
13 CSCN2 181 0% 6 15 3 0 0
14 HI3 975 0.01% 5 13 5 64 0
15 HRO2 1045 0.01% 4 6 4 2 0
16 SSEK2 50518 0.42% 3 18 3356 0 0
17 SLCT2 169511 1.41% 2 16 3357 0 0
18 BLKUP2 11833814 98.16% 1 17 6712 0 0
优化思路是通过索引过滤，提前判断数据结果集为0，优化相关查询。
/+ENABLE_RQ_TO_NONREF_SPL(3)/ 相关查询表达式转化为非相关查询表达式
/+enable_index_filter(1)/ 开启索引过滤，从执行统计信息看到查询性能量级提升
优化后sql的执行计划和成本排名
-----加hint /+ENABLE_RQ_TO_NONREF_SPL(3) enable_index_filter(1)/---------
1 #NSET2: [66, 10, 852]
2 #PRJT2: [66, 10, 852]; exp_num(15), is_atom(FALSE)
3 #SORT3: [66, 10, 852]; key_num(1), is_distinct(FALSE), is_adaptive(0)
4 #PRJT2: [65, 372, 852]; exp_num(15), is_atom(FALSE)
5 #PRJT2: [65, 372, 852]; exp_num(14), is_atom(FALSE)
6 #HASH RIGHT JOIN2: [65, 372, 852]; key_num(1); col_num(12)
7 #BLKUP2: [1, 2->2, 144]; T_TABLE5_U(T_TABLE5)
8 #SSEK2: [1, 2->2, 144]; scan_type(ASC), T_TABLE5_U(T_TABLE5)
9 #NEST LOOP INDEX JOIN2: [64, 372, 708]
10 #HASH2 INNER JOIN: [59, 372, 570]; RKEY_UNIQUE KEY_NUM(1);
11 #HASH2 INNER JOIN: [57, 372, 474]; LKEY_UNIQUE KEY_NUM(1);
12 #SLCT2: [1, 64->64, 126];
13 #CSCN2: [1, 404->404, 126]; INDEX33559636(T_TABLE4)
14 #SLCT2: [56, 1672, 348];
15 #BLKUP2: [56, 37856, 348]; T_TABLE1_I_TITLE(T_TABLE1)
16 #SLCT2: [56, 37856->0, 348];
17 #SSEK2: [56, 37856->1006378, 348]; scan_type(ASC), T_TABLE1_I_TITLE(T_TABLE1)
18 #CSCN2: [1, 6273, 96]; INDEX33559023(T_TABLE3)
19 #BLKUP2: [2, 1, 108]; INDEX33559960(T_TABLE2)
20 #SLCT2: [2, 1, 108];
21 #SSEK2: [2, 1, 108]; scan_type(ASC), INDEX33559960(T_TABLE2)
已用时间: 213.509(毫秒). 执行号:296129202.
SQL> et(296129202);
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER HASH_USED_CELLS HASH_CONFLICT
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- --------------
1 PRJT2 1 0% 17 4 2 0 0
2 PRJT2 1 0% 17 5 2 0 0
3 IJI2 2 0% 16 9 2 0 0
4 SLCT2 3 0% 13 14 2 0 0
5 BLKUP2 3 0% 13 15 2 0 0
6 PRJT2 3 0% 13 2 2 0 0
7 DLCK 4 0% 12 0 2 0 0
8 HI3 10 0% 11 10 2 0 0
9 SORT3 15 0.01% 10 3 2 0 0
10 SLCT2 28 0.01% 9 12 6 0 0
11 BLKUP2 37 0.02% 8 7 4 0 0
12 SSEK2 62 0.03% 7 8 2 0 0
13 NSET2 65 0.03% 6 1 2 0 0
14 CSCN2 216 0.1% 5 13 3 0 0
15 HI3 990 0.47% 4 11 5 64 0
16 HRO2 1197 0.56% 3 6 4 2 0
17 SSEK2 100880 47.45% 2 17 3356 0 0
18 SLCT2 109108 51.31% 1 16 3357 0 0
18 rows got
作者
文章
阅读量
获赞
联系客服
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1