查询优化

数据库执行一条语句有多种方式,为了选择最优的执行方式,产生了查询优化器。查询优化器分析语句运行时的所有因素,选择最优的方式去执行,提高了查询效率。因此,查询优化是数据库执行SQL语句的重要过程,决定了数据库的查询性能。

22.1 优化目标

达梦数据库查询优化器的优化目标为最快响应时间。通过设置参数FIRST_ROWS来决定优先返回多少条记录给用户,而不需要等待全部结果确定后再输出,FIRST_ROWS设置范围为1~1000,单位为行。例如:FIRST_ROWS = 10,意思是查询出10条结果就立即返回给用户。可以根据实际情况,调整参数值。

22.2 查询优化器

查询优化器通过分析可用的执行方式和查询所涉及的对象统计信息来生成最优的执行计划。此外,如果存在HINT优化提示,优化器还需要考虑优化提示的因素。

查询优化器的处理过程包括:

  1. 优化器生成所有可能的执行计划集合;
  2. 优化器基于字典信息的数据分布统计值、执行语句涉及到的表、索引和分区的存储特点来估算每个执行计划的代价。代价是指SQL语句使用某种执行方式所消耗的系统资源的估算值。其中,系统资源消耗包括I/O、CPU使用情况、内存消耗等;
  3. 优化器选择代价最小的执行方式作为该条语句的最终执行计划。

优化器所做的操作有:查询转换、估算代价、生成计划。

22.2.1 查询转换

查询转换是指把经过语法、语义分析的查询块之间的连接类型、嵌套关系进行调整,生成一个更好的查询计划。常用的查询转换技术包括过滤条件的下放、相关子查询的去相关性。

  1. 过滤条件下放:在连接查询中,把部分表的过滤条件下移,在连接之前先过滤,可以减少连接操作的数据量,提升语句性能;
  2. 相关子查询的去相关性:把与子查询相关的外表与内表采用半连接的方式执行,放弃默认采取的嵌套连接方式,对性能有较大提升。

22.2.2 估算代价

估算代价是指对执行计划的成本进行估算。执行节点之间的代价值相关性较强,一个执行节点的代价包括该节点包含的子节点代价。代价衡量指标包括选择率、基数、代价。

选择率是指满足条件的记录占总记录数的百分比。记录集可以是基表、视图、连接或分组操作的结果集。选择率和查询谓词相关,如name = ‘韩梅梅’;或者是谓词的连接,如name =‘韩梅梅’ and no =‘0123’。一个谓词可以看作是一个过滤器,过滤掉结果集中不满足条件的记录。选择率的范围从0到1。其中,0表示没有记录被选中,1表示行集中所有记录都被选中。

如果没有统计信息,则优化器依据过滤条件的类型来设置对应的选择率。例如,等值条件的选择率低于范围条件选择率。这些假定是根据经验值,认为等值条件返回的结果集最少。

如果有统计信息,则可以使用统计信息来估算选择率。例如,对于等值谓词(name =‘韩梅梅’),如果name列有N个不同值,那么,选择率是N分之一。

基数是指整个行集的行数,该行集可以是基表、视图、连接或分组操作的结果集。

代价表示资源的使用情况。查询优化器使用磁盘I/O、CPU占用和内存使用作为代价计算的依据,所以代价可以用I/O数、CPU使用率和内存使用一组值来表示。所有操作都可以进行代价计算,例如扫描基表、索引扫描、连接操作或者对结果集排序等。

访问路径决定了从一个基表中获取数据所需要的代价。访问路径可以是基表扫描、索引扫描等。在进行基表扫描或索引扫描时,一次I/O读多个页,所以,基表扫描或索引全扫描的代价依赖于表的数据页数和多页读的参数值。二级索引扫描的代价依赖于B树的层次、需扫描的叶子块树以及根据rowid访问聚集索引的记录数。

连接代价是指访问两个连接的结果集代价与连接操作的代价之和。

22.2.3 生成计划

生成计划指计划生成器对给定的查询按照连接方式、连接顺序、访问路径生成不同的执行计划,选择代价最小的一个作为最终的执行计划。

连接顺序指不同连接项的处理顺序。连接项可以是基表、视图、或者是一个中间结果集。例如表t1、t2、t3的连接顺序是先访问t1,再访问t2,然后对t1与t2做连接生成结果集r1,最后把t3与r1做连接。一个查询语句可能的计划数量是与FROM语句中连接项的数量成正比的,随着连接项的数量增加而增加。

22.3 数据访问路径

访问路径指从数据库中检索数据的方法。一般情况下,索引访问用于检索表的小部分数据,全表扫描用于访问表的大部分数据。OLTP应用中,一般使用索引访问路径,因为OLTP中包含了许多高选择率的SQL语句。而决策支持系统则倾向于执行全表扫描来获取数据。从数据库中定位和检索数据的方法有:全表扫描、聚集索引扫描、二级索引扫描等。

全表扫描是指从基表中检索数据时,扫描该表中所有的数据。全表扫描方式适合检索表中大部分数据,这时比索引扫描更加有效率。

索引扫描是指通过指定语句中的索引列进行遍历来检索表中的数据。索引扫描是从基于一列或多列的索引中检索数据。索引不仅包含索引值,还包含对应表中数据的ROWID。如果需要访问的不是索引列,这时需要通过ROWID或聚集索引来找到表中的数据行。

索引扫描包含聚集索引扫描和二级索引扫描。由于在聚集索引中,包含了表中所有的列值,所以检索数据时只需要扫描这一个索引就可以得到所有需要的数据。如果是二级索引,由于只包含索引列以及对应的ROWID,如果查询列不在二级索引中则还需要扫描聚集索引来得到所需要的数据。

查询优化器选择访问路径基于以下几个因素:

  1. 执行语句中可能的访问路径;
  2. 估算每条执行路径的代价。为了选择一个访问路径,优化器首先会通过检查语句中的FROM子句和WHERE子句中的条件表达式来决定哪一个访问路径可以使用。优化器会根据可用的访问路径生成可能的执行计划集合,然后使用索引、列和表的统计信息来估算每个计划的代价。最后,优化器选择最小代价的那个执行计划。

影响优化器选择访问路径的因素有语句中的提示(HINT)和统计信息。用户可以在执行的语句中使用HINT来指定访问路径。而统计信息会根据表中数据的分布情况决定采用哪个访问路径会产生最小的代价。

22.4 连接

查询语句中FROM子句包含多个表时,我们称为连接查询。如SELECT * FROM t1,t2 就是连接查询。

生成连接查询的执行计划,需要考虑三方面因素:

  1. 访问路径

对于每张表采用何种方式来获取数据。例如:全表扫描、索引扫描等。

查询优化器会估算每种扫描方式的代价,选择代价较小的访问路径。

  1. 连接方式

确定两张表之间采用哪种连接方式。例如:哈希连接、嵌套连接、归并连接、外连接。

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

  1. 嵌套连接:两张表进行非等值连接时会选择嵌套连接。相当于两张表进行笛卡尔集操作。此时,优化器会选择一张代价较小的表作为外表(驱动表),另一张表作为内表,外表的每条记录与内表进行一次连接操作。
  2. 哈希连接:两张表进行等值连接时会选择哈希连接。以一张表的连接列为哈希键,构造哈希表,另张表的连接列进行哈希探测,找到满足条件的记录。由于哈希命中率高,因此,在大数据量情况下,哈希连接的效率较高。哈希连接的代价是建立哈希表和哈希探测的代价。
  3. 归并连接:两张表的连接列均为索引列,则可以按照索引顺序进行归并,一趟归并就可以找出满足条件的记录。如果查询列也属于索引列的子集,则归并连接只需扫描索引,会有更好的性能表现。在两表连接条件不是等值(如<,<=,>,>=)情况下时,归并排序连接很有用。
  4. 外连接:外连接分为左外连接、右外连接、全外连接。作为外表的数据会全部返回,如果没有与外表匹配的记录,则填充NULL值。右外连接与左外连接的处理过程类似,只是外表不同,一个是左表,一个是右表。全外连接是进行左外连接和右外连接,返回两次外连接的union结果集。

例1:左外连接:

  SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.c1=t2.d1;

例1中t1表为外表(左表),如果t2表中不存在与t1.c1相等的记录,

则t2表的该行记录用NULL填充。右外连接与左外类似。

例2:全外连接

  SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.c1=t2.d1;

例2中分别以t1为左表进行左外和右外连接,两次结果进行union,返回最终结果。

子查询会转换成半连接。共有四种半连接方式:哈希半连接、索引半连接、嵌套半连接、归并半连接。等值连接条件会选择哈希\索引\归并半连接,非等值连接条件会选择嵌套半连接。

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

当超过2张表进行连接时,就需要考虑表之间的连接顺序。不合适的连接顺序对执行效率有较大影响。一般原则是,经过连接可以产生较小结果集的表优先处理。

一个连接查询通常会对应多个执行计划,查询优化器会根据优化规则、代价估算挑选最优的执行计划。

22.5 统计信息

对象统计信息描述数据是如何在数据库中存储的。统计信息是优化器的代价计算的依据,可以帮助优化器较精确地估算成本,对执行计划的选择起着至关重要的作用。

达梦数据库的统计信息分三种类型:表统计信息、列统计信息、索引统计信息。通过直方图来表示。统计信息生成过程分以下三个步骤:

  1. 确定采样的数据:根据数据对象,确定需要分析哪些数据。

    1. 表:计算表的行数、所占的页数目、平均记录长度
    2. 列:统计列数据的分布情况
    3. 索引:统计索引列的数据分布情况
  2. 确定采样率

    根据数据对象的大小,通过内部算法,确定数据的采样率。采样率与数据量成反比。

  3. 生成直方图

    有两种类型的直方图:频率直方图和等高直方图。根据算法分析表的数据分布特征,确定直方图的类型。频率直方图的每个桶(保存统计信息的对象)的高度不同,等高直方图每个桶的高度相同。例如,对列生成统计信息,当列值分布比较均匀时,会采用等高直方图,否则,采用频率直方图。

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

用户也可以通过修改OPTIMIZER_DYNAMIC_SAMPLING参数值在缺乏统计信息时进行动态统计信息收集。

22.6 执行计划

执行计划是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;

执行计划如下:

    1   ##NSET2: [0, 16, 9] 
    2     ##PRJT2: [0, 16, 9]; EXP_NUM(2), IS_ATOM(FALSE) 
    3       ##NEST LOOP INDEX JOIN2: [0, 16, 9] 
    4         ##CSCN2: [0, 4, 5]; INDEX33555535(B)
    5         ##SSEK2: [0, 4, 0]; SCAN_TYPE(ASC), IDX_T1_C1 (A), SCAN_RANGE[T2.D1,T2.D1]

这个执行计划看起来就像一棵树,执行过程为:控制流从上向下传递,数据流从下向上传递。其中,类似[0, 16, 9]这样的三个数字,分别表示估算的操作符代价、处理的记录行数和每行记录的字节数。同一层次中的操作符,如本例中的CSCN2和SSEK2,由父节点NEST LOOP INDEX JOIN2控制它们的执行顺序。

该计划的大致执行流程如下:

  1. CSCN2: 扫描T2表的聚集索引,数据传递给父节点索引连接;
  2. NEST LOOP INDEX JOIN2: 当左孩子有数据返回时取右侧数据;
  3. SSEK2: 利用T2表当前的D1值作为二级索引IDX_T1_C1定位查找的KEY,返回结果给父节点;
  4. NEST LOOP INDEX JOIN2: 如果右孩子有数据则将结果传递给父节点PRJT2,否则继续取左孩子的下一条记录;
  5. PRJT2: 进行表达式计算C1+1, D2;
  6. NSET2: 输出最后结果;
  7. 重复过程1) ~ 4)直至左侧CSCN2数据全部取完。

用户如果想了解更多关于操作符的知识,请查看动态视图V$SQL_NODE_NAME,手册的附录3给出了常用操作符的说明。

22.6.1 自适应计划

在DM优化器进行代价估算时,如果子节点是一个复杂查询,可能使得对于子节点的代价估算不准确,导致最终选择的计划在执行时并非最优计划。因此,DM引入了自适应计划机制。

将INI参数OPTIMIZER_MODE和ADAPTIVE_NPLN_FLAG都置为1,启用自适应计划机制。此时,优化器会自动判断在某些情况下,复杂查询的子节点可能导致代价估算不准确,则会为该节点生成一个备用计划节点。在实际执行到该节点时,根据准确的代价信息确定是否需要采用备用计划。

例如:

建表、插入数据并建索引:

    DROP TABLE T1;
    DROP TABLE T2;

    CREATE TABLE T1(C1 INT,C2 INT);
    CREATE TABLE T2(D1 INT,D2 INT);

    INSERT INTO T1 VALUES(1,1);
    INSERT INTO T1 VALUES(1,2);
    INSERT INTO T2 VALUES(1,1);
    INSERT INTO T2 VALUES(2,1);
    INSERT INTO T2 VALUES(3,1);
    INSERT INTO T2 VALUES(4,1);
    INSERT INTO T2 VALUES(5,1);
    COMMIT;

    CREATE INDEX IND1 ON T1(C2);

打印执行计划:

  EXPLAIN SELECT * FROM T1 ,T2 ,T1 T3 WHERE T2.D1= T3.C1 AND T1.C2=T2.D2;

执行计划如下:

    1   ##NSET2: [1, 2, 24]
    2     ##PRJT2: [1, 2, 24]; exp_num(6), is_atom(FALSE)
    3       ##HASH2 INNER JOIN: [1, 0, 0];  KEY_NUM(1);
    4         ##NEST LOOP INDEX JOIN2: [1, 2, 24]
    5           ##ACTRL: [1, 2, 24];
    6             ##HASH2 INNER JOIN: [0, 5, 16];  KEY_NUM(1);
    7               ##CSCN2: [0, 2, 8]; INDEX33555445(T1 as T3)
    8               ##CSCN2: [0, 5, 8]; INDEX33555446(T2)
    9           ##BLKUP2: [0, 1, 0]; IND1(T1)
    10            ##SSEK2: [0, 1, 0]; scan_type(ASC), IND1(T1), scan_range[T2.D2,T2.D2]
    11        ##CSCN2: [0, 2, 8]; INDEX33555445(T1)

可以看到执行计划中有一个ACTRL操作符,它说明优化器为这一条SQL语句生成了备用计划。ACTRL是控制备用计划转换的操作符,其上面一层NEST LOOP INDEX JOIN2为默认的主计划,再上面一层HASH2 INNER JOIN则为备用计划。ACTRL操作符计算下层孩子节点的代价,决定采用默认主计划还是备用计划。

需要说明的是,MPP和并行查询不支持自适应计划。

22.7 使用索引

为了提高查询效率,用户一般会在表中创建索引。查询中的条件列为索引列时,如果索引扫描代价最小,优化器就会采用索引扫描。索引扫描有多种方式,例如,索引等值查询、索引范围查询。如果查询列属于索引列的子集,则通过索引扫描就可以获得数据,否则,还需要根据ROWID或者PK在聚集索引中定位记录。

常用的索引类型有唯一索引、组合索引、函数索引。各自有不同的使用场景。

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

22.8 并行查询

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

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

DM支持本地并行和多机并行,并行查询具体实现上采用对称并行技术,即每一个执行者(进程或线程)都执行相同的计划。主要执行者负责向其他并行执行者分发计划、搜集数据并向客户返回最终结果。不管是主执行者,还是从执行者,它们执行的计划都是完全相同的。

22.8.1 多机并行查询

多机并行查询是指在分布式数据库系统中,利用集群中的多台机器同时完成用户请求。

例如,DM MPP就是一个支持多机并行的数据库系统。用户登录到某个EP执行查询操作时,查询计划会被自动打包发送给集群中其它EP,多个EP协同合作完成用户请求。为了使得数据能够在多个EP间流动,MPP引入了专门的数据分发操作符如MPP GATHER(MGAT)、MPP COLLECT(MCLCT)、MPP BROADCAST(MBRO)和MPP SCATTER(MSCT)。详情请参考《DM8大规模并行处理MPP》。

22.8.2 本地并行查询

本地并行查询(Local Parallel Query, LPQ)是指利用一台数据库服务器的多个线程同时执行查询操作。只有当主机具有多个CPU内核时,开启LPQ才能提高查询任务的性能。

22.8.2.1 本地并行步骤

达梦数据库通过三个步骤来完成本地并行查询:首先,确定并行任务数;其次,确定并行工作线程数;最后,执行查询。并行查询相关参数见下表:

表22.1 并行查询相关参数
参数名 缺省值 属性 说明
MAX_PARALLEL_DEGREE 1 动态,会话级 用来设置单个查询默认的最大并行任务个数。取值范围:1~128。缺省值1,表示无并行任务。当PARALLEL_POLICY值为1时该参数值才有效。
PARALLEL_POLICY 0 静态 用来开启或关闭并行。取值范围:0、1和2,缺省为0。其中,0表示关闭并行;1表示开启自动并行模式;2表示开启手动并行模式。
PARALLEL_THRD_NUM 10 静态 用来设置并行工作线程总个数。取值范围:1~1024。

22.8.2.1.1 启用本地并行查询

使用PARALLEL_POLICY开启或关闭LPQ。0表示禁止LPQ。1采用自动并行,即系统对任何一个SQL都试图使用LPQ,除非使用了HINT限制并行任务个数。2采用手动并行,即除非使用了HINT指定了并行任务个数,否则都不采用LPQ。

22.8.2.1.2 确定最大并行任务个数

当开启自动并行(PARALLEL_POLICY=1)时,参数MAX_PARALLEL_DEGREE生效,控制单个查询采用并行查询时最多使用的线程数。MAX_PARALLEL_DEGREE缺省值为1,表示不并行。此时若指定参数对应的HINT “PARALLEL”,则使用HINT值;当开启手动并行(PARALLEL_POLICY=2)时,参数MAX_PARALLEL_DEGREE失效,用户需要在语句中使用此参数对应的HINT “PARALLEL”指定语句的最大并行任务数,否则不并行。

MAX_PARALLEL_DEGREE要小于等于PARALLEL_THRD_NUM。如果MAX_PARALLEL_DEGREE过大,会自动调整为等于PARALLEL_THRD_NUM。

1. 在INI参数中设置默认值

INI参数MAX_PARALLEL_DEGREE设置单个查询最大并行任务个数。取值范围:1~128。缺省值1,表示无并行任务,此参数仅在PARALLEL_POLICY值为1时才有效。

例如,在INI参数中将MAX_PARALLEL_DEGREE设置为3的格式如下:

MAX_PARALLEL_DEGREE   3

然后,使用一般的SQL语句查询即可执行并行查询,不需要使用HINT。如:

SELECT * FROM SYSOBJECTS;

2. 在SQL语句中使用“PARALLEL”关键字特别指定

当PARALLEL_POLICY=2时,需要在SQL语句中通过“PARALLEL”HINT指定并行度,否则不并行。若PARALLEL_POLICY=1,则SQL语句中使用的“PARALLEL”HINT总是优先于MAX_PARALLEL_DEGREE参数设置。

“PARALLEL”关键字的用法是在数据查询语句的SELECT关键字后,增加HINT子句来实现。

HINT语法格式如下:

/*+ PARALLEL([<表名>] <并行任务个数>) */

例如,下面的例子中,即使已经设置了MAX_PARALLEL_DEGREE默认值3,但实际使用的为PARALLEL指定的任务个数4:

SELECT /*+ PARALLEL(4) */ * FROM SYSOBJECTS;

另外,每个语句中仅能设置一次并行任务个数,如果设置了多次,则以最后一次设置为准,而且任务个数在全语句中生效。

例如,下面的例子中,使用的并行任务个数为2。

SELECT /*+ PARALLEL(1) *//*+ PARALLEL(2) */ * FROM SYSOBJECTS;

这种方式能够为单条查询语句设置额外的并行任务个数,以此来提高某些特殊查询任务的性能。

22.8.2.1.3 确定并行工作线程总个数

在执行并行查询任务之前,您需要指定完成并行查询的并行工作线程总个数。并行工作线程总数是系统启动时,创建的并行工作线程的总个数,一般不超过CPU内核个数的2倍。

值得注意的是,实际使用的线程数并非总是等于并行工作线程总数。并行工作线程总数是在INI参数中设定的,实际使用并行工作线程数是根据系统的实际状况确定的。

1. 并行工作线程数,在INI参数中设定

首先,使用PARALLEL_POLICY参数来设置并行策略。取值范围:0、1和2,默认值0。其中,0表示不支持并行;1表示自动并行模式;2表示手动并行模式。

当开启本地并行(PARALLEL_POLICY>0)时,使用PARALLEL_THRD_NUM指定本地并行查询使用的线程数,取值范围为1~1024,缺省值为10。需要注意的是,若PARALLEL_POLICY=1,如果PARALLEL_THRD_NUM=1, 则按照CPU个数创建并行线程。

例如,设置并行策略PARALLEL_POLICY为2,即手动设置并行工作线程数;同时,设置并行工作线程数PARALLEL_THRD_NUM为4个。

PARALLEL_POLICY      2

PARALLEL_THRD_NUM    4

当然,并非所有的查询都适合使用并行查询。大量占用 CPU 周期的查询最适合采用并行查询的功能。例如,大型表的连接查询、大量数据的聚合和大型结果集的排序等都很适合采用并行查询。对于简单查询(常用于事务处理应用程序)而言,执行并行查询所需的额外协调工作会大于潜在的性能提升。所以,数据库管理员在确定是否需要使用并行策略的时候,需要慎重。

2. 实际使用的线程数,达梦数据库会根据每个并行查询操作自动检测

实际使用线程数是数据库在查询计划执行时初始化的时候确定的。也就是说,这不需要用户去干预,而是系统根据并行任务数和实际空闲的并行工作线程数来确定的。此操作所依据的条件如下:首先,检测达梦数据库是否运行在具有多个CPU的计算机上。只有具有多个 CPU 的计算机才能使用并行查询。这是一个硬性的限制条件。其次,检测可用的空闲工作线程是否足够。并行查询到底采用多少线程数,除了跟操作的复杂程度相关外,还跟当时的服务器状态相关,如是否有足够的可用的空闲工作线程数量等。每个并行查询操作都要求一定的工作线程数量才能够执行;而且执行并行计划比执行串行计划需要更多的线程,所需要的线程数量也会随着任务个数的提高而增加。当无法满足特定并行查询执行的线程要求时,数据库引擎就会自动减少任务个数,甚至会放弃并行查询而改为串行计划。所以,即使同一个操作在不同时候可能会采用不同的线程数。

例如,即使设置并行工作线程数为4。而实际使用的线程数可能只有3个,或者更少。

22.8.2.1.4 执行查询

当以上内容确定好之后,数据库就会执行具体的查询任务。例如,当开启了手动并行模式PARALLEL_POLICY=1,最大并行任务数为16,并行线程总个数为32。在这种搭配下,当以16路并行时,最多可以有2个SQL采用并行查询,并行线程总数不超过32,此时,如果有其他SQL,只能采用非并行查询。

22.8.2.2 本地并行操作符

为了使得数据在同一进程的不同线程间流动,引入了 4个用于本地数据分发的操作符:LOCAL GATHER(LGAT)、LOCAL DISTRIBUTE(LDIS)、LOCAL BROADCAST(LBRO)和LOCAL SCATTER(LSCT)。

表22.2 LPQ并行执行计划通讯操作符
操作符名称 功能
LOCAL GATHER (LGAT) 主线程收集所有线程数据,从线程将数据发送到主线程
LOCAL DISTRIBUTE (LDIS) 各线程间相互分发数据,按照分发列计算行数据的目标线程并发送过去,目标线程负责接收
LOCAL BROADCAST (LBRO) 收集数据到主线程,该操作符带有聚集函数运算功能,仅和FAGR配合使用
LOCAL SCATTER (LSCT) 主线程发送完整数据到所有从线程,保证每个线程数据都完整,一般和LGAT配合使用

22.8.2.3 使用场景

使用手动并行模式时,只需要在INI参数中设置好如下2个参数,然后执行并行SQL查询语句时,需手动指定当前并行任务个数。若不指定,将不使用并行。设置的2个参数如下:

PARALLEL_POLICY      2

PARALLEL_THRD_NUM    4

使用自动并行模式时,一般指定如下三个参数:

MAX_PARALLEL_DEGREE   3

PARALLEL_POLICY       1

PARALLEL_THRD_NUM     10

另外,当PARALLEL_POLICY为0时,即使有并行任务,也不支持并行。

然后,执行语法格式类似“SELECT * FROM SYSOBJECTS;”的并行SQL语句即可,本条语句使用默认并行任务数3。

当然,如果单条查询语句不想使用默认并行任务数,可以通过在SQL语句中增加HINT,通过“PARALLEL”关键字来特别指定。此时,执行的并行SQL语句格式为“SELECT /*+ PARALLEL(SYSOBJECTS 4) */ * FROM SYSOBJECTS;”,本条语句使用的并行任务数为4。

22.8.3 多机和本地并行查询

在DM MPP系统中开启LPQ,计划执行时就会用到多机并行和本地并行。如下面TPCH 的Q1查询语句。

select

l_returnflag,  

l_linestatus,  

sum(l_quantity) as sum_qty,   

sum(l_extendedprice) as sum_base_price,   

sum(l_extendedprice*(1-l_discount)) as sum_disc_price,   

sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge,  

avg(l_quantity) as avg_qty,   

avg(l_extendedprice) as avg_price,  

avg(l_discount) as avg_disc,  

count(*) as count_order   

from lineitem  

where l_shipdate <= date' 1998-12-01' - interval '90' day  

group by l_returnflag, l_linestatus   

order by l_returnflag, l_linestatus;

该查询为单表过滤后的分组和排序。由于同一分组的数据可能分散在多个EP的不同线程间,计划中需要对分组结果进行多次处理。一个可能的计划形态如下:

1  ##NSET2: [23339, 1, 140] 

2   ##MPP COLLECT: [23339, 1, 140]; op_id(8) n_grp_by (0) n_cols(0) n_keys(0) for_sync(TRUE)

3    ##LOCAL COLLECT: [23339, 1, 140]; op_id(7) n_grp_by (0) n_cols(0) n_keys(0) for_sync(TRUE)

4     ##PRJT2: [23339, 1, 140]; exp_num(10), is_atom(FALSE) 

5      ##MPP GATHER: [23339, 6, 140]; op_id(6) n_grp_by (0) n_cols(10) n_keys(2) invoke_flag(0)

6       ##LOCAL GATHER: [23339, 1, 140]; op_id(5) n_grp_by (0) n_cols(10) n_keys(2) invoke_flag(0)

7        ##SORT3: [23339, 1, 140]; key_num(2), is_distinct(FALSE), top_flag(0), is_adaptive(0)

8         ##HAGR2: [23334, 6, 140]; grp_num(2), sfun_num(8); slave_empty(0)keys DMTEMPVIEW_16779645.TMPCOL0

9          ##LOCAL DISTRIBUTE: [23334, 6, 140]; op_id(4) n_keys(0) n_grp(1) flt_only(FALSE) flt_site_data(FALSE) n(0)

10          ##MPP DISTRIBUTE: [23334, 6, 140]; op_id(3) n_keys(0) n_grp(1) filter(FALSE) rowid_flag(0) n(0) slave_empty(FALSE)

11           ##LOCAL GATHER: [23334, 6, 140]; op_id(2) n_grp_by (0) n_cols(0) n_keys(0) invoke_flag(0)

12            ##HAGR2: [23334, 6, 140]; grp_num(2), sfun_num(9); slave_empty(0)keys DMTEMPVIEW_16779645.TMPCOL0

13              ##LOCAL DISTRIBUTE: [23334, 6, 140]; op_id(1) n_keys(0) n_grp(1) flt_only(FALSE) flt_site_data(FALSE) n(0)

14              ##HAGR2: [23334, 6, 140]; grp_num(2), sfun_num(9); slave_empty(0)keys DMTEMPVIEW_16779645.TMPCOL0

15                ##PRJT2: [21471, 147843240, 140]; exp_num(7), is_atom(FALSE) 

16                ##SLCT2: [21471, 147843240, 140]; LINEITEM.L_SHIPDATE <= var3

17                 ##HFSCN: [21471, 150020479, 140]; (LINEITEM)

假定上述计划执行在EP1~EP4共四个EP上执行,每个EP设置的并行度为8,那么该计划一共有8 * 4= 32个线程协作完成。执行的大体流程是,首先每个EP的8路线程各自获取lineitem表数据,经过哈希分组(##14)后按照分组项l_returnflag, l_linestatus进行本地数据分发(##13),而后进行二次哈希分组处理(##12)。接下来,需要考虑同一分组分散在不同EP的情况,为此##11的本地汇总将8个线程的结果汇总到主线程x,由x按照分组项进行EP站点间的分发(##10)。每个EP的主线程x在接收到彼此间发来的数据后,再次进行分组的最终汇总处理(##8)。接下来处理排序,每个线程单独排序(##7),排序结果交由每个EP的主线程进行汇总(##6),这里的汇总用到了归并排序。最后一步是各EP将归并排序结果收集起来发送给主EP(##5),同样,这里也是一个归并排序。

22.9 查询计划重用

如果同一条语句执行频率较高,或者每次执行的语句仅仅是常量值不同,则可以考虑使用计划重用机制。避免每次执行都需要优化器进行分析处理,可以直接从计划缓存中获取已有的执行计划,减少了分析优化过程,提高执行率。

对于计划重用,达梦数据库提供了INI参数USE_PLN_POOL来控制,当置为非0时,会启用计划重用。

22.10 结果集重用

执行计划的生成与优化是一个非常依赖CPU的操作,而执行一个查询获得结果集也是一个非常消耗资源的操作。当系统连续执行两个完全相同的SQL语句,其执行计划和结果集很有可能是相同的,如果重新生成和执行计划,会大大浪费系统资源。这时如果使用计划重用和结果集重用,系统的响应速度可以大大提升。

结果集重用是基于计划重用的,如果查询的计划不能缓存,则其查询结果集必然不能缓存。此外,当语句的游标属性为FORWARD ONLY时,默认查询不会生成结果集。而参数BUILD_FORWARD_RS可以强制在此类查询中生成结果集,以便进行结果集重用。

可通过设置INI参数RS_CAN_CACHE来控制结果集重用。当置为0时表示手动模式(MANUAL),在此模式下默认不缓存查询结果集,但是DBA可以通过语句提示等方法指示系统对必要的查询结果集进行缓存;置为1时表示强制模式(FORCE),在此模式下默认缓存所有可缓存结果集,但是DBA也可以通过新增的配置参数以及语句提示等方法取消某些不合适的结果集缓存。

当RS_CAN_CACHE为1时,还可以通过设置INI参数RS_CACHE_TABLES和RS_CACHE_MIN_TIME对缓存的结果集进行限制和过滤。RS_CACHE_TABLES指定可以缓存结果集的基表清单,只有查询涉及的所有基表全部在参数指定范围内,此查询才会缓存结果集。RS_CACHE_MIN_TIME则指定了缓存结果集的查询语句执行时间的下限,只有实际执行时间不少于指定值的查询结果集才会缓存。

DBA可以通过在SQL语句中设置 “RESULT_CACHE”或“NO_RESULT_CACHE” HINT手动指示查询的结果集是否缓存。如:

  select /*+ RESULT_CACHE */ id, name from sysobjects;

或者

  select /*+ NO_RESULT_CACHE */ id, name from sysobjects;

在语句中使用HINT指定结果集缓存的优先级要高于INI中相关参数的设置。

还可以使用系统过程SP_SET_PLN_RS_CACHE来强制设置指定计划结果集缓存的生效及失效。这个系统过程对结果集缓存的指定高于其它所有结果集缓存的设置。

在以下情况下,DM不支持结果缓存:

  1. 必须是单纯的查询语句计划,PL脚本中包含查询语句也不能缓存结果集。

  2. 查询语句的计划本身必须是缓存的。

  3. 守护环境中的备库不支持结果集缓存。

  4. MPP等集群环境下不支持结果集缓存(3、4两点限制都是因为无法精确控制基表的数据更新时戳)。

  5. 查询语句中包含以下任意一项,其结果集都不能缓存:

    1. 包含临时表;
    2. 包含序列的CURVAL或NEXTVAL;
    3. 包含非确定的SQL函数或包方法(现有逻辑是不支持所有SQL函数或包方法);
    4. 包含RAND、SYSDATE等返回值实时变化的系统函数;
    5. 包含其它的一些实时要素。
微信扫码
分享文档
扫一扫
联系客服