DM执行计划

1. 引言

理解执行计划对于优化查询性能、诊断慢查询问题至关重要。本文将从基础概念入手，逐步深入探讨执行计划的各个组成部分，并通过设计用例来验证所学知识。

2. SQL 执行计划基础

SQL 执行计划是数据库引擎在执行 SQL 语句时生成的一个操作步骤的详细说明。它描述了数据库如何访问数据、如何连接表、如何过滤数据等。执行计划通常以树形结构展示，每个节点代表一个操作步骤。以如下 SQL 语句执行计划为例：
SELECT * FROM SYSOBJECTS;

1 #NSET2: [0, 1282, 396]
2 #PRJT2: [0, 1282, 396]; exp_num(17), is_atom(FALSE)
3 #CSCN2: [0, 1282, 396]; SYSINDEXSYSOBJECTS(SYSOBJECTS as SYSOBJECTS)
执行计划的每行即为一个计划节点，主要包含三部分信息：

1. 第一部分 NEST2、PRJT2、CSCN2 为操作符及数据库具体执行了什么操作。
2. 第二部分的三元组为该计划节点的执行代价，具体含义为[代价，记录行数，字节数]。
3. 第三部分为操作符的补充信息。
   例如：第三个计划节点表示操作符是 CSCN2（即全表扫描），代价估算是 0 ms，扫描的记录行数是 1282 行，输出字节数是 396 个。
   各计划节点的执行顺序为：缩进越多的越先执行，同样缩进的上面的先执行，下面的后执行，上下的优先级高于内外。缩进最深的，最先执行；缩进深度相同的，先上后下。

3. 执行计划的组成

3.1 操作符（Operators）

操作符是执行计划中的基本单元，代表数据库引擎执行的一个操作。
准备测试表及数据如下：
//创建测试表
CREATE TABLE T1(C1 INT ,C2 CHAR(1),C3 VARCHAR(10) ,C4 VARCHAR(10) );
CREATE TABLE T2(C1 INT ,C2 CHAR(1),C3 VARCHAR(10) ,C4 VARCHAR(10) );

//向 T1 和 T2 中各插入了 10000 条数据，数据内容完全一致
INSERT INTO T1
SELECT LEVEL C1,CHR(65+MOD(LEVEL,57)) C2,‘TEST’,NULL FROM DUAL
CONNECT BY LEVEL<=10000;

INSERT INTO T2
SELECT LEVEL C1,CHR(65+MOD(LEVEL,57)) C2,‘TEST’,NULL FROM DUAL
CONNECT BY LEVEL<=10000;

//在 T1 的 C1 列上创建了索引 IDX_C1_T1，并初始化了索引 IDX_C1_T1 的统计信息
CREATE INDEX IDX_C1_T1 ON T1(C1);
SP_INDEX_STAT_INIT(USER,‘IDX_C1_T1’);

DM常见的操作符包括：

1. NSET：结果集收集,是用于结果集收集的操作符，一般是查询计划的顶层节点
   

2. PRJT：投影,关系的【投影】 (project) 运算，用于选择表达式项的计算
   

3. SLCT：选择,是关系的【选择】运算，用于查询条件的过滤
   

4. AAGR：简单聚集,用于没有 GROUP BY 的 COUNT、SUM、AGE、MAX、MIN 等聚集函数的计算
   

5. FAGR：快速聚集,用于没有过滤条件时，从表或索引快速获取 MAX、MIN、COUNT 值
   

6. HAGR：HASH 分组聚集,用于分组列没有索引只能走全表扫描的分组聚集
   

7. SAGR：流分组聚集,用于分组列是有序的情况下
   

8. BLKUP：二次扫描 (回表),先使用二级索引索引定位 rowid，再根据表的主键、聚集索引、rowid 等信息获取数据行中其它列
   

9. CSCN：全表扫描, CSCN2 是 CLUSTER INDEX SCAN 的缩写即通过聚集索引扫描全表
   

10. SSEK、CSEK、SSCN：索引扫描
    SSEK2 是二级索引扫描即先扫描索引，再通过主键、聚集索引、rowid 等信息去扫描表。
    

CSEK2 是聚集索引扫描只需要扫描索引，不需要扫描表，即无需 BLKUP 操作
– 创建所需索引
CREATE CLUSTER INDEX IDX_C1_T2 ON T2(C1);
CREATE INDEX IDX_C1_C2_T1 ON T1(C1,C2);

SSCN 是索引全扫描，不需要扫描表。

11. NEST LOOP：嵌套循环连接
    //强制优化器使用嵌套循环连接
    select /+use_nl(t1,t2)/* from t1 inner join t2 on t1.c1=t2.c1 where t1.c2=‘A’;
    （由于没有索引，执行效率会很差）
    

改进：针对 T1 和 T2 的连接列创建索引，并收集统计信息
//T2的索引在前面已创建
CREATE INDEX IDX_T1_C2 ON T1(C2);
DBMS_STATS.GATHER_INDEX_STATS(USER,‘IDX_T1_C2’);
DBMS_STATS.GATHER_INDEX_STATS(USER,‘IDX_C1_T2’);

查看执行计划可看出效率明显改善，代价有显著下降

12. HASH JOIN：哈希连接是在没有索引或索引无法使用情况下大多数连接的处理方式
    将T1和T2的索引删除
    

13. MERGE JOIN：归并排序连接，连接需要两张表的连接列都有索引，对两张表扫描索引后按照索引顺序进行归并
    CREATE INDEX IDX_T1_C1C2 ON T1(C1,C2);
    CREATE INDEX IDX_T2_C1C2 ON T2(C1,C2);
    select /+use_merge(t1 t2)/t1.c1,t2.c1 from t1 inner join t2 on t1.c1=t2.c1 where t2.c2=‘b’;
    

3.2 成本（Cost）

成本是数据库引擎对执行计划中每个操作符的资源消耗的估计。成本通常包括 CPU 成本、I/O 成本等。成本越低，执行计划的效率越高。

3.3 行数估计（Row Estimation）

行数估计是数据库引擎对每个操作符返回的行数的估计。准确的估计有助于优化查询性能。

3.4 访问路径（Access Path）

访问路径描述了数据库引擎如何访问表中的数据。常见的访问路径包括：

• 全表扫描（Full Table Scan）: 扫描整个表。
• 索引扫描（Index Scan）: 通过索引访问数据。
• 索引查找（Index Seek）: 通过索引查找特定行。

4. 执行计划的优化

4.1 索引的使用

索引是优化查询性能的重要手段。通过创建合适的索引，可以显著减少查询的响应时间。常见的索引类型包括：

• 单列索引
• 复合索引
• 唯一索引
• 覆盖索引

4.2 连接策略（Join Strategies）

连接策略是数据库引擎在执行连接操作时选择的算法。常见的连接策略包括：

• Nested Loop Join : 适用于小表连接。
• Hash Join : 适用于大表连接。
• Merge Join : 适用于已排序的表连接。

4.3 子查询优化

子查询是 SQL 查询中常见的操作，但不当使用子查询可能导致性能问题。优化子查询的方法包括：

• 将子查询转换为连接操作。
• 使用 EXISTS 替代 IN。
• 使用 WITH 子句（CTE）优化复杂子查询。

4.4 统计信息的更新

统计信息是数据库引擎优化查询的重要依据。统计信息包括表的行数、列的分布情况等。定期更新统计信息有助于数据库引擎生成更优的执行计划。

5. 设计用例验证

准备测试表和数据
//清除测试表
DROP TABLE T1;
DROP TABLE T2;

//创建测试表
CREATE TABLE T1(C1 INT ,C2 CHAR(1),C3 VARCHAR(10) ,C4 VARCHAR(10) );
CREATE TABLE T2(C1 INT ,C2 CHAR(1),C3 VARCHAR(10) ,C4 VARCHAR(10) );

//向 T1 和 T2 中各插入了 10000 条数据，数据内容完全一致
INSERT INTO T1
SELECT LEVEL C1,CHR(65+MOD(LEVEL,57)) C2,‘TEST’,NULL FROM DUAL
CONNECT BY LEVEL<=10000;

INSERT INTO T2
SELECT LEVEL C1,CHR(65+MOD(LEVEL,57)) C2,‘TEST’,NULL FROM DUAL
CONNECT BY LEVEL<=10000;
5.1 简单查询（全表扫描 vs 索引扫描）

EXPLAIN PLAN FOR
SELECT * FROM T1 WHERE C1 = 100;

全表扫描

索引扫描
CREATE INDEX IDX_T1_C1 ON T1(C1);

对比访问计划可以发现，使用索引 IDX_T1_C1 进行索引查找，成本较低，形成的结果集较小。
5.2 连接查询（嵌套循环连接 vs 哈希连接）
//两表单独建索引
CREATE INDEX IDX_T1_C1 ON T1(C1);
CREATE INDEX IDX_T1_C2 ON T1(C2);
CREATE INDEX IDX_T2_C1 ON T2(C1);
CREATE INDEX IDX_T2_C2 ON T2(C2);

SP_INDEX_STAT_INIT(USER,‘IDX_T1_C1’);
SP_INDEX_STAT_INIT(USER,‘IDX_T1_C2’);
SP_INDEX_STAT_INIT(USER,‘IDX_T2_C1’);
SP_INDEX_STAT_INIT(USER,‘IDX_T2_C2’);

嵌套循环连接（执行9ms）
EXPLAIN
SELECT /*+ USE_NL(T1, T2) */ *
FROM T1
INNER JOIN T2 ON T1.C1 = T2.C1
WHERE T1.C2 = ‘A’;

哈希连接（执行2ms）
EXPLAIN
SELECT /*+ USE_HASH(T1, T2) */ *
FROM T1
INNER JOIN T2 ON T1.C1 = T2.C1
WHERE T1.C2 = ‘A’;

其中，嵌套循环连接耗时9毫秒比哈希连接耗时2毫秒长，因为嵌套循环里驱动表的行数就是循环的次数，在很大程度上影响执行效率。

6. 总结与建议

理解 SQL 执行计划是优化查询性能的关键。通过分析执行计划，我们可以识别查询中的性能瓶颈，并采取相应的优化措施。建议在实际工作中：

• 定期分析慢查询的执行计划。
• 创建合适的索引。
• 更新统计信息。
• 避免不必要的子查询和临时表。

更多详细资料可前往达梦社区：https://eco.dameng.com