达梦8数据库优化

1.执行计划是什么？

一条SQL语句在数据库中执行过程或访问路径的描述。（举个例子如一个地方到达另一个地方最优的路线：徒步、骑行、自驾、飞机等不同的交通工具不同的路线，CBO选择最小的代价路线）

2.如何查看达梦数据库执行计划？

通过explain命令：

EXPLAIN +执行的SQL语句，如

SQL> EXPLAIN SELECT * FROM TEST1;

1   #NSET2: [1, 1113, 602] 
2     #PRJT2: [1, 1113, 602]; exp_num(16), is_atom(FALSE) 
3       #CSCN2: [1, 1113, 602]; INDEX33555810(TEST1)

已用时间: 0.810(毫秒). 执行号:0.

图形工具展示：

执行顺序：从里到外，从右到左/从下到上

执行计划包含内容：

一个执行计划包含若干个计划节点，上图1,2,3

每个计划节点中包含操作符（CSCN2）和它的代价（1,1113,602）等信息

代价是由一个三元组组成【代价，记录行数，字节数】

  代价单位是毫秒，记录行数表示计划节点输出的行数，字节数表示该计划节点输出的字节数

此执行计划解读：操作符CSCN2全表扫描，代价估算是1ms，扫描行数1113行，输出字节602个。

3.常见操作符

 收集结果集：NSET 用于结果集收集操作符，也就是查询计划的顶层节点。

投影：PRJT 关系的“投影”(project)运算，用于选择表达式项的计算,如果头像耗时大一般跟子查询有关。

选择：SLCT 关系的“选择”（select）运算，用于查询条件的过滤。

简单聚集：AAGR 用于没有group by 的count sum age max min等聚集函数的计算。

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

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

流分组聚集：SAGR 用于分组列是有序情况下，可以使用流分组聚集，条件列已经有索引：SAGR性能优于HAGR

二次扫描:BLKUP先使用二级索引定位，在根据表的主键、聚集索引、ROWID等信息等位数据行。

全表扫描：CSCN是cluster index scan的缩写通过聚集索引扫描全表，全表扫描是最基本的查询，如果没有为此过滤或者索引则系统一般只能进行全表扫描。生产环境避免全表扫描。

索引扫描：SSEK CSEK SSCN

SSEK二级索引扫描，先扫描索引，在通过主键、聚集索引、ROWID等信息去扫描表 

CSEK聚集索引扫描，只扫描索引，不需要扫描表

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

4.表连接

嵌套循环连接（NEST LOOP）

两层嵌套循环结构，有驱动表和被驱动表之分，选定一张表作为驱动表，遍历驱动表中的每一行，根据条件去匹配第二张表的行。驱动表满足条件的行数就是循环的次数。

建议：小表作为驱动表，驱动表有好的过滤条件，表连接条件有索引，结果集比较小的场景使用。

hint强制走嵌套 /*+use_nl(tab1,tab2) */

哈希连接（HASH JOIN）

一般没有索引或索引用不上的情况使用该连接，选择小表做HASH表，以一张表的连接列为哈希键，构造哈希表，另张表的连接列进行哈希探测，找到满足条件的记录。由于哈希命中率高，因
此，在大数据量情况下，哈希连接的效率较高。哈希连接的代价是建立哈希表和哈希探测的代价只能适用于等值连接。HASH连接比较消耗内存。

如果系统有很多这种连接时，需调整以下3个参数:

HJ_BUF_GLOBAL_SIZE

HJ_BUF_SIZE

HJ_BLK_SIZE

归并连接（MERGE SORT）

无驱动表之分，随机读很少，两个表都需要按照连接列进行排序，需要消耗大量的CPU和额外额内存。如果查询列属于索引的子集，CBO就会优先考虑这种连接方式。

hint强制走归并/*+use_merge(tab1,tab2)*/

5.查询转换

  查询转换是优化器自动做额，在生产执行计划之前，等价改写查询语句的形式，以便提升效率和产生更好的执行计划。它决定是否重写用户的查询，常见的转换有谓词传递、视图拆分、谓词推进、关联/非关联子查询改写等。了解优化器查询的特性，会帮助我们更好的看懂执行计划，也会对我们优化SQL起到指导作用。优化器的查询转换有很多限制条件，我们可以根据类似原理举一反三，进行手工SQL改写，从而得到更好的执行计划。

谓词传递:根据A=B,B=C

select * from a inner join b on a.c1=b.c1 where a.c2=100 and a.c2=b.c2;

--CBO转换后

select * from a inner join b on a.c1=b.c1 where a.c2=100 and a.c2=b.c2 and b.c2=100;

视图拆分：视图与表关联中将视图查询的计划拆分与表合成一个整体执行计划。优化器等价改写，将视图查询拆散了，和其它的部分作为一个整体来生成计划。视图拆分有很多限制，如果视图中包含了distinc、union、group by 等操作，优化器无法将视图拆分。SQL中如果使用过多的视图，会使SQL变得复杂，优化器也难以生成最佳的执行计划，不能过度依赖优化器进行视图拆分，开发时应减少视图的使用。

谓词推进：子查询字段无索引，本应该走全表扫描，但是子查询却走了另一个字段的索引，优化器对等价改写把索引的条件列推入到子查询中。如下：

原始SQL:子查询X相当于内联视图

select * from (select a1,a2 from t where a1=2) x where a2=20;

查询转换

select * from (select a1,a2 from t where a1=2 and a2=20 ) x;

非关联子查询的转换：子查询跟另一张表查询列无关联，完全可以把它生成独立的子计划，但是计划中却和另一个表做了关联，优化器进行了等价改写。如下：

原始SQL：select * from a where a1 in (select b1 from b) and a2='D';

查询转换：select * from a where exists (select 1 from b where a.a1=b.b1) and a2='D';

INI参数: REFED_EXISTS_OPT_FLAG，影响in和exists子查询的转换

外连接转换：SQL写法是外连接，执行计划却变成了内连接，说明优化器进行等价改写。例如：

原始SQL:select a.a1,b.b1 from a left join b on a.a1=b.b1 where b.b1=100 and a.a2='A';

查询转换：select a.a1,b.b1 from a inner join b on a.a1=b.b1 where b.b1=100 and a.a2='A';

6.统计信息

统计信息主要描述数据库中标，索引的大小，规模，数据分布情况等一类信息，如：表的行数、块数，评价每行的大小，索引的高度、叶子节点数，索引字段的行数，不同值得大小等。

有两种类型的直方图：频率直方图和等高直方图。

根据算法分析表的数据分布特征（以不同值的数据量1 万个为分界线），确定直方图的类型。

频率直方图的每个桶(保存统计信息的对象)的高度不同，等高直方图每个桶的高度相同。生成直方图时，如果不同值少于 1 万个则用频率直方图，否则用等高直方图。

统计信息作用：在执行查询时，如果数据对象存在统计信息，代价算法可以根据统计信息中的数据，比较精确地计算出操作所需花费的成本，以此来确定连接方式、对象访问路径、连接顺序，选
择最优的执行计划。

如果没有对基数低的列收集直方图统计信息，基于成本的优化器（CBO）会认为该列数据分布是均衡的，返回结果无论多少有可能都是走索引，导致执行计划错误。当列出现在 where 条件中，列的选择性小于 1%并且该列没有收集过直方图，这样的列就应该收集直方图。注意：千万不能对没有出现在 where 条件中的列收集直方图。对没有出现在 where 条件中的列收集直方图完全是做无用功，浪费数据库资源。

一般对小于 1GB 的表进行 100%采样，因为表很小，即使 100%采样速度也比较快。有时候小表有可能数据分布不均衡，如果没有 100%采样，可能会导致统计信息不准。因此建议对小表 100%采样。
一般对表大小在 1GB～5GB 的表采样 50%，对大于 5GB 的表采样 30%。如果表特别大，有几十甚至上百 GB，我们建议应该先对表进行分区，然后分别对每个分区收集统计信息。一般情况下，为了确保统计信息比较准确，建议采样率不要低于 30%。

7.索引创建，但执行计划不走索引的情况：

A.条件列非复合索引的首列；（USE_INDEX_SKIP_SCAN跳跃索引需要单独开启或HINT指定）

B.条件列上有函数或者计算（可以建立函数索引，不建议大规模使用）；

C.存在隐式转换（建议数据类型最好匹配）；

D.返回数据集超过表行数的20%；（返回数据多的情况不会走索引，一般低于5%行数走索引最优）

E.没有统计信息或者统计信息过期（目前达梦无法查看统计信息过期）

等等。。。。。。。。。。。。。。。。。。

注：

1.达梦数据库针对新建数据表后，即使创建索引也需要单独对表进行统计信息的收集，Oracle 11g及之后版本默认创建索引时会自动收集索引统计信息，达梦8需要手工执行;

2.现场生产环境能不动SQL情况尽量避免SQL的改变，例如收集统计信息（ini参数临时生效）执行计划最优效果也达到现场要求，其次考虑增加索引优化效果（避免在业务场景测试，业务繁忙期间创建），最后考虑SQL的修改（使用HINT）等方式。

3.适合应用场景的架构才是最优的结果（这个说的有些宽泛了，毕竟是业务上线之前就需要确定的方案）

总结优化的方式有多个层面的：

数据库架构优化

数据库参数优化

SQL 优化

统计信息

不足之处还请大家批评指正。

标题
在线服务平台地址：
https://eco.dameng.com