达梦数据库查询优化与SQL优化学习

全表扫描：指从基表中检索数据时，扫描该表中所有的数据。用于访问表的大部分数据。
索引扫描：指通过指定语句中的索引列进行遍历来检索表中的数据。
聚集索引包含了表中所有的列值，所以检索数据时只需要扫描这一个索引就可以得到所有需要的数据。
二级索引只包含索引列以及对应的 ROWID ，如果查询列不在二级索引中则还需要扫描聚集索引来得到所需要的数据。

2.3连接

查询语句中 FROM 子句包含多个表时称为连接查询

生成连接查询的执行计划，会考虑以下三方面因素：

访问路径:查询优化器会估算每种扫描方式的代价，选择代价较小的访问路径。
连接方式：连接方式有哈希连接、嵌套连接、归并连接和外连接

嵌套连接：
两张表进行非等值连接时会选择嵌套连接。相当于两张表进行笛卡尔集操作。此时，优化器会选择一张代价较小的表作为外表( 驱动表) ，另一张表作为内表，外表的每条记录与内表进行一次连接操作。
哈希连接：
两张表进行等值连接时会选择哈希连接。以一张表的连接列为哈希键，构造哈希表，另张表的连接列进行哈希探测，找到满足条件的记录。
归并连接：
两张表的连接列均为索引列，则可以按照索引顺序进行归并，一趟归并就可以找出满足条件的记录。
外连接：
外连接分为左外连接、右外连接、全外连接。作为外表的数据会全部返回，如果没有与外表匹配的记录，则填充 NULL 值。右外连接与左外连接的处理过程类似，只是外表不同，一个是左表，一个是右表。全外连接是进行左外连接和右外连接，返回两次外连接的 union 结果集。

注意：

等值连接条件一般会选择哈希连接；非等值连接条件会采用嵌套连接；连接列均为索引列时，会采用归并连接。

子查询会转化为半连接，共有四种半连接方式：

哈希半连接：
以外表的连接列为 KEY 构造哈希表，内表的连接列进行探测来查找满足连接条件的记录。
索引半连接：
如果子查询的连接列为索引前导列，可采用索引半连接。处理过程为外表的数据对子查询使用索引查找，返回满足条件的记录。
归并半连接：
如果相关子查询的连接条件列均为索引列，可采用归并半连接。按照索引顺序，对外表、内表进行同步扫描，返回满足条件的记录。
嵌套半连接：
如果连接条件为非等值，可转换为嵌套半连接。处理过程为外表的每条记录去遍历内表，返回满足条件的记录。

2.4执行计划

执行计划是 SQL 语句的执行方式，由查询优化器为语句设计的执行方式，交给执行器去执行。在 SQL 命令行使用 EXPLAIN 可以打印出语句的执行计划。

执行以下语句，查看执行计划

CREATE TABLE T1(C1 INT,C2 CHAR);
CREATE TABLE T2(D1 INT,D2 CHAR);
CREATE INDEX IDX_T1_C1 ON T1(C1);
INSERT INTO T1 VALUES(1,'A');
INSERT INTO T1 VALUES(2,'B');
INSERT INTO T1 VALUES(3,'C');
INSERT INTO T1 VALUES(4,'D');
INSERT INTO T2 VALUES(1,'A');
INSERT INTO T2 VALUES(2,'B');
INSERT INTO T2 VALUES(5,'C');
INSERT INTO T2 VALUES(6,'D');

EXPLAIN SELECT A.C1+1,B.D2 FROM T1 A, T2 B WHERE A.C1 = B.D1;

执行计划的执行过程为：控制流从上向下传递，数据流从下向上传递。

常见的执行操作符如下：

操作符名称	说明	操作符名称	说明
AAGR2	简单聚集；如果没有分组（ group by ），则总的就一个组，直接计算聚集函数	SSCN	直接使用二级索引进行扫描
ASCN	数组当作表来扫描	SSEK2	二级索引数据定位
CSCN2	聚集索引扫描	UNION	UNION 计算
DSCN	动态视图表扫描	UNION ALL	UNION ALL 运算
CSEK2	聚集索引数据定位	NTTS2	临时表，临时存放数据
HAGR2	HASH 分组，并计算聚集函数	NSET2	结果集 (result set) 收集，一般是查询计划的顶层节点
HASH LEFT SEMI JOIN2	HASH 左半连接	PARALLEL	控制水平分区子表的扫描
HASH FULL JOIN2	HASH 全外连接	PRJT2	关系的 ― 投影 ‖(project) 运算，用于选择表达式项的计算
HASH LEFT JOIN2	HASH 左外连接	NEST LOOP FULL JOIN2	嵌套循环全外连接
HASH RIGHT JOIN2	HASH 右外连接	NEST LOOP INNER JOIN2	嵌套循环内连接
HASH RIGHT SEMI JOIN2	HASH 右半连接	NEST LOOP LEFT JOIN2	嵌套循环左外连接
HEAP TABLE	临时结果表	NEST LOOP SEMI JOIN2	嵌套循环半连接
HEAP TABLE SCAN	临时结果表扫描	INDEX JOIN SEMI JOIN2	索引半连接
INDEX JOIN LEFT JOIN2	索引左连接	NEST LOOP INDEX JOIN2	索引内连接

2.5使用索引

为了提高查询效率，用户一般会在表中创建索引。查询中的条件列为索引列时，如果索引扫描代价最小，优化器就会采用索引扫描。

常用的索引方式有唯一索引、组合索引、函数索引。其使用场景如下：

条件列具有 UNIQUE 约束，则可以创建唯一索引，减少索引扫描次数；
条件列是多个列，而且可以过滤掉大部分数据，可以在多个列上创建组合索引，把等值条件列作为组合索引的首列；
条件列使用确定性函数 ( 同样环境下多次执行得到相同的结果 ) ，可以创建函数索引，会把函数值进行存储，使用方式与普通索引一样；
在空间数据应用中，可以创建空间索引提高空间查询的效率

2.6并行查询

并行查询（ Parallel Query ， PQ ）是一种多个线程或进程间协作、共同完成 SQL 计划的优化技术。

根据参与执行的 DM 服务器实例个数，并行查询可以划分为多机并行查询和单机并行查询。单机并行查询仅利用了单机的 CPU 、磁盘、内存等机器资源，因此又称为本地并行查询。

三、SQL调优

SQL 调优作为数据库性能调优中的最后一个环节，对查询性能产生着直接的影响。在进行正式的 SQL 调优前，要关注下列几点：

1．达梦数据库安装时的配置参数是否符合应用场景需求；

2．达梦数据库的 INI 配置文件中各项参数是否已经处于最优配置；

3．应用系统中数据库设计是否合理。

3.1调优目标

SQL 调优的整体目标是使用最优的执行计划，这意味着 IO 以及 CPU 代价最小。具体而言调优主要关注下列方面：

表扫描 ：
如果计划中对某大表使用了全索引扫描，则需要关注是否存在着该表的某个查询条件使得过滤后可以淘汰至少一半的数据量。通过添加相应的索引，全索引扫描可能被转换为范围扫描或等值查找。添加的二级索引可以包含该表上所有被选择项以避免 BLKUP2 操作符的查找操作带来的第二次 IO 开销，但无疑这会增加二级索引的大小。

连接操作的顺序和类型 ：
多表连接时，不同的连接顺序会影响中间结果集数量的大小，这时调优的目标就是要找到一种能使中间结果保持最小的连接顺序。
分组操作：
分组操作往往要求缓存所有数据以找到属于同一组的所有数据，在大数据量情况下这会带来大量的 IO 。应该检查 SQL 查询和表上索引信息，如果可以利用包含分组列的索引，那么执行计划就会使用排序分组从而不用缓存中间结果。

3.2确定高负载SQL

在打开监控开关（ ENABLE_MONITOR=1 、 MONITOR_TIME=1 ）后，可以通过查询动态视图 V$LONG_EXEC_SQLS 或 V$SYSTEM_LONG_EXEC_SQLS 来确定高负载的 SQL 语句。前者显示最近 1000 条执行时间较长的 SQL 语句，后者显示服务器启动以来执行时间最长的20条 SQL 语句。

SELECT * FROM V$LONG_EXEC_SQLS;
#
SELECT * FROM V$SYSTEM_LONG_EXEC_SQLS;

3.3开发有效的SQL语句

为了使语句执行效率达到最优，需要参考以下原则以开发出有效的 SQL语句：

1.避免使用 OR 子句 ： OR 子句在实际执行中会被转换为类似于UNION 的查询。如果某一个 OR 子句不能利用上索引则会使用全表扫描造成效率低下，应避免使用。

2.避免使用困难的正则表达式：在 SQL 语言中，LIKE 关键字支配通配符匹配，含通配符的表达式被称为正则表达式。有的正则表达式可以自动优化为非匹配的。所谓困难的正则表达式是指开头和结尾都为通配符的正则表达式，如'_L%'、'%L_'，优化器没办法缩小它们的匹配范围，也不可能用到索引而必须使用全表扫描。因此要尽可能避免这样的正则表达式。

3.SELECT 项避免‘*’：除非用户确实要选择表中所有列，否则 SELECT *这种写法将让执行器背上沉重的负荷。因为每一列的数据不得不自下往上层层向上传递，因此要避免直接使用SELECT *。

4.避免功能相似的重复索引：索引太多时，如果表上增删改操作频繁，那么索引的维护将会成为大麻烦。

5.使用 COUNT(*)统计结果行数：如果对单表查询 COUNT(*)且没有过滤条件，那么 DM 优化器会直接读取相关索引中存储的行数信息，加以回滚段中其他事务插入或删除元组的行数修正，迅速地给出最终结果而避免对实际数据的读取。相比之下，COUNT(列名)会对数据进行读操作，执行效率远低于COUNT(*)。

6.UNION 和 UNION ALL 的选择：UNION 和 UNION ALL 的区别是前者会过滤掉值完全相同的元组，为此 UNION 操作符需要建立 HASH 表缓存所有数据并去除重复，当 HASH 表大小超过了 INI 参数指定的限制时还会做刷盘。因此如果应用场景并不关心重复元组或者不可能出现重复，那么 UNION ALL 无疑优于UNION。

7.优化 GROUP BY ... HAVING：GROUP BY 最常见的实现有 HASH 分组（HAGR）和排序分组（SAGR）。前者需要缓存中间结果；如果用户在 GROUP BY 的列上建立索引，那么优化器就会判断并可能使用上该索引，这时的 GROUP BY 就会变为 SAGR。HAVING 是分组后对结果集进行的过滤，如果过滤条件无关集函数操作，用户可以考虑将过滤条件放在 WHERE 而不是 HAVING 中。

8. 使用优化器提示（HINT）：利用经验对优化器的计划选择进行调整，HINT 是 SQL 调整不可或缺的一步。

3.4索引提示

目前 DM 提供的 HINT 为表索引的选择 HINT ，它指示使用指定索引进行数据检索。

1.使用索引

一个语句中最多指定 8 个索引。在后一种语法格式中，如果查询中给出了表的别名那么必须使用别名。索引格式如下：

表名 + INDEX + 索引名
或
/*+ INDEX (表名[,] 索引名) {INDEX (表名[,] 索引名)} */

2. 不适用索引

可以指定多个索引，则这些索引都不能被使用。一个语句中最多指定 8 个索引。不使用索引格式如下：

/*+ NO_INDEX (表名[,] 索引名) { NO_INDEX (表名[,] 索引名)} */

3.5连接方法提示

DBA 可以通过指定两个表间的连接方法来检测不同连接方式的查询效率，指定的连接可能由于无法实现或代价过高而被忽略。如果连接方法提示中的表名( 别名 ) 或索引名无效也会被自动忽略。

指定连接方法有如下几种：

方法名	作用
USE_HASH	强制两个表间使用指定顺序的哈希连接
NO_USE_HASH	强制两个表间不能使用指定顺序的哈希连接
NO_USE_NL	强制两个表间不能使用嵌套循环连接
USE_NL	强制两个表间使用嵌套循环连接
USE_NL_WITH_INDEX	当连接情况为左表 + 右表索引时，强制两个表间使用索引连接
NO_USE_NL_WITH_INDEX	当连接情况为左表 + 右表索引时，强制两个表间不能使用索引连接
USE_MERGE	强制两个表间使用归并连接。归并连接所用的两个列都必须是索引列
NO_USE_MERGE	强制两个表间不能使用归并连接
SEMI_GEN_CROSS	优先采用半连接转换为等价的内连接，仅 OPTIMIZER_MODE=1 有效
NO_SEMI_GEN_CROSS	不采用半连接转换为等价的内连接，仅 OPTIMIZER_MODE=1 有效
USE_CVT_VAR	优先采用变量改写方式实现连接，适合驱动表数据量少而另一侧计划较复杂的场景，仅 OPTIMIZER_MODE=1 有效。
NO_USE_CVT_VAR	不考虑变量改写方式实现连接，仅 OPTIMIZER_MODE=1 有效。
ENHANCED_MERGE_JOIN	一般情况下，归并连接需要左右孩子的数据按照连接列有序，使用此优化器提示时，优化器将考虑通过插入排序操作符的方式实现归并连接，仅 OPTIMIZER_MODE=1 有效

3.6连接顺序提示

多表连接时优化器会考虑各种可能的排列组合顺序。使用 ORDER HINT 指定连接顺序提示可以缩小优化器试探的排列空间，进而得到接近 DBA 所期望的查询计划。如果连接顺序和连接方法提示同时指定且二者间存在自相矛盾，优化器会以连接顺序提示为准。

语法如下：

/*+ ORDER (T1, T2 , T3, … tn ) */

3.7统计信息提示

优化器在计划优化阶段会自动获取基表的行数。但是一些特殊类型的表行数估算并不准确，或者 DBA 希望了解表大小对计划影响的时候，需要手动设置表的行数。统计信息提示只能针对基表设置，视图和派生表等对象设置无效。如果表对象存在别名则必须使用别名。行数只能使用整数，或者整数+K（千），整数+M（百万），整数+G（十亿）。行数提示设置后，统计信息的其它内容也会做相应的调整。

语法如下：

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

3.8MPP本地对象提示

MPP 环境下，提供一种将用户表或动态视图作为本地对象处理的方法，通过指示符LOCAL_OBJECT(对象名 / 别名 ) 进行处理。对于系统表当做本地对象的处理，本方法不适用，系统表只能在主站点才能做本地对象处理。

/*+LOCAL_OBJECT(对象名/别名)*/

3.9忽略重复键值提示

当执行 INSERT 操作时，如果存在 UNIQUE 索引，那么发生了重复键值冲突。使用 HINTIGNORE_ROW_ON_DUPKEY_INDEX 则可以忽略该冲突，冲突数据既不进行插入也不会报错，其他非冲突插入正常进行。

语法如下：

/*+IGNORE_ROW_ON_DUPKEY_INDEX(<表名>(<列名>{,<列名>}))*/

3.10禁用缓存计划提示

使用 HINT PLAN_NO_CACHE 禁用计划缓存，当前语句的执行计划将不会被缓存。

语法如下：

/*+PLAN_NO_CACHE*/

3.11DMDPC数据分发方式提示

DMDPC 环境下提供了一种对指定的连接、分组、排序、去重操作符数据分发方式进行人工干预的优化器提示。该优化器提示被采纳的前提是指定的分发路径有效，因为代价原因没有被优化器选中。

语法如下：

/*+DPC(分发方式探测序号 分发方式字符串)*/

以上就是本文的主要内容，如果想了解更多关与DM数据库的知识，可以浏览以下平台：

达梦在线服务平台：达梦数据库 - 新一代大型通用关系型数据库 | 达梦在线服务平台 (dameng.com)

总结：本文主要讲述了DM数据库查询优化和SQL优化的内容，如有错误，欢迎指出。

闻醉

关注

1
点赞
踩
5

收藏

觉得还不错? 一键收藏
1
评论
达梦数据库查询优化与SQL优化学习

本文主要讲述了DM数据库查询优化和SQL优化的内容，如有错误，欢迎指出。
复制链接

扫一扫

达梦数据库查询优化与SQL优化学习

一、概述

二、查询优化

2.1查询优化器

2.1.1查询转换

2.1.2估算代价

2.1.3生成计划

2.2数据访问路径

2.3连接

2.4执行计划

2.5使用索引

2.6并行查询

三、SQL调优

3.1调优目标

3.2确定高负载SQL

3.3开发有效的SQL语句

3.4索引提示

3.5连接方法提示

3.6连接顺序提示

3.7统计信息提示

3.8MPP本地对象提示

3.9忽略重复键值提示

3.10禁用缓存计划提示

3.11DMDPC数据分发方式提示

“相关推荐”对你有帮助么？