数据库优化之执行计划

文章目录

• 一、什么是执行计划
• 二、执行计划的顺序
• 三、执行计划操作符介绍
• 四、统计信息提示

---

一、什么是执行计划

SQL的优化首先要知道SQL慢在哪里，此时我们就需要通过查看执行计划来了解SQL的具体执行情况。

执行计划是 SQL语句的执行方式，由查询优化器为语句设计的执行方式，交给执行器去执行。在 SQL 命令行使用 EXPLAIN 可以打印出语句的执行计划，也可以通过达梦管理工具查看。

例如下面就是一个查看执行计划的例子：

SQL> explain select * from SYSOBJECTS;

1   #NSET2: [1, 1193, 396]

2     #PRJT2: [1, 1193, 396]; exp_num(17), is_atom(FALSE)

3       #CSCN2: [1, 1193, 396]; SYSINDEXSYSOBJECTS(SYSOBJECTS as SYSOBJECTS)

从上面的执行计划中我们可以看到如下信息：

1）一个执行计划由若干个计划节点组成，如上面的1、2、3。

2）每个计划节点中包含操作符(CSCN2)和它的代价([1, 1193, 396])等信息。

3）代价由一个三元组组成[代价,记录行数,字节数]。

4）代价的单位是毫秒，记录行数表示该计划节点输出的行数，字节数表示该计划节点输出的字节数。拿上面第三个计划节点举例:操作符是CSCN2即全表扫描，代价估算是1ms，扫描的记录行数是1193行，输出字节数是396个。

二、执行计划的顺序

1）左叶子先于右叶子执行。

2）同一级的上比下先执行。

3）控制流从上向下传递，数据流从下向上传递。

4）单叶子操作符，从下往上执行。

5）双叶子操作符，先左后右，从下往上依次传递执行。

1   #NSET2: [1, 1, 104]

2     #PRJT2: [1, 1, 104]; exp_num(4), is_atom(FALSE)

3       #SLCT2: [1, 1, 104]; T2.ID > 1

4         #HASH2 INNER JOIN: [1, 1, 104];  KEY_NUM(1); KEY(T1.ID=T2.ID) KEY_NULL_EQU(0)

5           #SLCT2: [1, 1, 104]; T2.ID > 1

6             #NEST LOOP INDEX JOIN2: [1, 1, 104]

7               #ACTRL: [1, 1, 104];

8                 #BLKUP2: [1, 1, 52]; IDX_T1_ID(T1)

9                   #SSEK2: [1, 1, 52]; scan_type(ASC), IDX_T1_ID(T1), scan_range(1,max]

10              #BLKUP2: [1, 1, 0]; IDX_T2_ID(T2)

11                #SSEK2: [1, 1, 0]; scan_type(ASC), IDX_T2_ID(T2), scan_range[T1.ID,T1.ID]

12          #CSCN2: [1, 1, 52]; INDEX33555541(T2)

对于4节点，它有5和12两个节点。先执行5节点。5-->12-->4

5节点有一个6节点，先执行6节点。6-->5-->12-->4

6节点有两个7和10两个节点。7-->10-->6-->5-->12-->4

7节点有个8节点，8节点有个9节点,10有个节点11。

9-->8-->7-->11-->10-->6-->5-->12-->4

所以整个节点执行顺序是：

9-->8-->7-->11-->10-->6-->5-->12-->4-->3-->2-->1

三、执行计划操作符介绍

准备数据

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) );

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;

CREATE INDEX IDX_C1_T1 ON T1(C1);

SP_INDEX_STAT_INIT(USER,'IDX_C1_T1');

（1）收集结果集NEST

此操作符用来收集结果集，用于结果集收集的操作符，一般是查询计划的顶层节点。

explain select * from t1;

（2）投影PRJT

关系的“投影”(project)运算，用于选择表达式项的计算；广泛用于查询，排序，函数索引创建等。

例如上例中的2节点。

（3）选择SLCT

关系的“选择”运算，用于查询条件的过滤。

explain select * from t1 where c2='TEST';

（4）简单聚集AAGR

用于没有group by的count、sum、avg、max、min等聚集函数的计算。

explain select avg(c1) from t1;

（5）快速聚集FAGR

用于没有过滤条件时从表或索引快速获取 MAX/MIN/COUNT值。

EXPLAIN SELECT COUNT(*) FROM T1;

（6）HASH分组聚集HAGR

用于分组列没有索引只能走全表扫描的分组聚集。

EXPLAIN SELECT COUNT(*) FROM T1 GROUP BY C2;

（7）流分组聚集SAGR

用于分组列是有序的情况下，可以使用流分组聚集。SAGR2性能优于HAGR2。

EXPLAIN SELECT COUNT(*) FROM T1 GROUP BY C1;

（8）二次扫描BLKUP

先使用二级索引定位，再根据表的主键、聚集索引、 rowid等信息定位数据行。

EXPLAIN SELECT * FROM T1 WHERE C1=10;

 

（9）全表扫描CSCN

CSCN2是CLUSTER INDEX SCAN的缩写即通过聚集索引扫描全表，全表扫描是最简单的查询，如果没有选择谓词，或者没有索引可以利用，则系统一般只能做全表扫描。在一个高并发的系统中应尽量避免全表扫描。

EXPLAIN SELECT * FROM T1;

（10）索引扫描SSEK CSEK SSCN

SSEK2是二级索引扫描即先扫描索引，再通过主键、聚集索引、ROWID等信息去扫描表；CSEK2是聚集索引扫描只需要扫描索引，不需要扫描表；SSCN是索引全扫描，不需要扫描表。

EXPLAIN SELECT * FROM T1 WHERE C1=10;

CREATE CLUSTER INDEX IDX_C1_T2 ON T2(C1);

EXPLAIN SELECT * FROM T2 WHERE C1=10;

CREATE INDEX IDX_C1_C2_T1 ON T1(C1,C2);

EXPLAIN SELECT C1,C2 FROM T1;

四、统计信息提示

优化器在计划优化阶段会自动获取基表的行数。但是一些特殊类型的表行数估算并不准确，或者 DBA 希望了解表大小对计划影响的时候，需要手动设置表的行数。

语法： 

/*+ STAT (表名, 行数) */

统计信息提示只能针对基表设置，视图和派生表等对象设置无效。如果表对象存在别名则必须使用别名。行数只能使用整数，或者整数+K（千），整数+M（百万），整数+G（十亿）。行数提示设置后，统计信息的其它内容也会做相应的调整。

示例：

CREATE TABLE T_S(C1 INT);

INSERT INTO T_S SELECT LEVEL FROM DUAL CONNECT BY LEVEL<= 100;

COMMIT;

STAT 100 ON T_S(C1);

EXPLAIN SELECT /*+ STAT(T_S,1M) */ * FROM T_S WHERE C1 <= 10;

表中实际只有100行数据。不使用 HINT 时计划：

EXPLAIN SELECT  * FROM T_S WHERE C1 <= 10;

---

 更多资讯请上达梦技术社区了解： https://eco.dameng.com