概述SQL优化的基本准则包括限制结果集大小、减少数据库IO次数、合理使用内存、避免网络传输速度降低、合理利用覆盖索引、以及优化特定SQL语句的操作。限制结果集大小通过使用WHERE子句或TOP语句来减少返回的结果行数和字段列数避免返回整个表的数据特别是当表数据量很大时。这有助于减少磁盘IO避免数据库缓冲区中的其他缓存数据被挤出从而提高系统性能。减少数据库IO次数查询的数据越大IO次数越多因此应尽量减少查询的数据量。这包括避免不必要的全表扫描和排序通过创建合适的索引来消除全表扫描和排序。合理使用内存查询的字段越多消耗的内存越大可能导致垃圾收集频繁降低应用性能。因此应尽量减少查询的字段数量以减少内存消耗。避免网络传输速度降低查询的数据量越大网络传输速度越慢。优化SQL语句以减少传输的数据量可以提高应用响应速度。合理利用覆盖索引当查询的字段能够被索引覆盖时可以避免额外的数据查找操作从而提高查询性能。设计索引时应考虑查询的字段和过滤条件确保能够充分利用覆盖索引。优化特定SQL语句的操作对于执行时间较长、消耗资源较多的SQL语句应分析其执行计划找到开销较高的部分并采取相应措施降低执行开销。这可能包括重新收集统计信息、改写SQL语句、创建或调整索引、固定执行计划、重新设计表或索引结构等执行计划执行计划是SQL语句的执行方式由查询优化器DM为CBO基于代价为语句设计的执行方式交给执行器去执行。操作符是SQL执行的基本单元所有的SQL语句最终都是转换成一连串的操作符最后在服务器上执行得到需要的结果操作符也是读懂执行计划的基础。简单介绍常见操作符下面列出操作符的具体含义CSCN2 聚集索引全扫描SSCN2 二级索引全扫描SSEK2 二级索引范围扫描 通过键值精准定位到范围或者单值CSEK2 聚簇索引范围扫描 通过键值精准定位到范围或者单值BLKUP2 根据二级索引的ROWID 回原表中取出全部数据NSET2结果集收集通常无需优化。PRJT2投影操作优化空间较小。SLCT2选择操作可以关注执行计划中的估算结果集一列AAGR2/FAGR2聚集函数主要出现在没有过滤条件或分组的情况下聚集函数的计算。HAGR2 HASH 分组聚集分组列没有索引只能走全表扫描若该处代价较高可以考虑对分组列添加索引。SAGR2有序的分组聚集性能高于HAGR可理解为HAGR加了索引后的表现。表关联常见操作符NEST LOOP FULL JOIN2 join_condition连接条件 嵌套循环全外连接NEST LOOP INDEX JOIN2 join_condition连接条件 索引内连接NEST LOOP INNER JOIN2 join_condition连接条件 嵌套循环内连接NEST LOOP LEFT JOIN2 join_condition连接条件 嵌套循环左外连接NEST LOOP SEMI JOIN2 join_condition连接条件, (ANTI)是否为反连接 嵌套循环全外连接HASH (INNER LEFT RIGHT SEMI) JOIN join_condition连接条件 哈希连接。INDEX (INNER LEFT RIGHT SEMI) JOIN join_condition连接条件 索引连接MERGE JOIN KEY等值连接条件 排序归并连接Nest loop inner join最基础的一种连接方式将一张表的每一个值分别与另一张表的所有值拼接形成一个大结果集再从大结果集中过滤出满足条件的行。两层嵌套循环结构有驱动表和被驱动表之分选定一张表作为驱动表遍历驱动表中的每一行根据连接条件去匹配第二张表中的行。驱动表的行数就是循环的次数这个很大程度影响了执行效率。需注意的问题一选择小表作为驱动表统计信息尽量准确保证优化器选对驱动表二大量的随机读如果没有索引随机读很致命每次循环只能读一块不能读多块。使用索引可以解决这个问题。使用场景驱动表有很好的过滤条件。表连接条件能使用索引。结果集比较小。Hash join没有索引的情况下大多数连接的处理方式是将一张表的连接列做成HASH表另一张表的数据向这个HASH表匹配满足条件的值返回。哈希连接是在没有索引或索引无法使用情况下大多数连接的处理方式。哈希连接使用关联列去重后结果集较小的表做HASH表另一张表的连接列在HASH 后向 HASH 表进行匹配这种情况下匹配速度极快主要开销在于对连接表的全表扫描以及 HASH 运算。hash join特点一般没索引或用不上索引时会使用该连接方式。选择小的表(或row source)做hash表。只适用等值连接中的情形。由于hash连接比较消耗内存如果系统有很多这种连接时需调整以下3个参数:HJ_BUF_GLOBAL_SIZEHJ_BUF_SIZEHJ_BLK_SIZEIndex join将一张表T1的数据拿出去另外一张表T2上进行范围扫描找出需要的数据行。索引连接需要右表的连接列上存在索引。Merge join需要两张表的连接列都有索引对两张表扫描索引后按照索引顺序进行归并。这里需要同时 SSCN 两条有序索引将其中满足条件的值输出到结果集效率比 NEST LOOP 要高很多不考虑其他条件如果 T1 和 T2 都很大的情况下跟 HASH JOIN 的效率相当HASH JOIN是CSCN两张基表MERGE JOIN 则 SSCN 相关索引简单案例构造和分析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),(2,B),(3,C),(4,D);INSERT INTO T2 VALUES(1,A),(2,B),(5,C),(6,D);Commit;EXPLAIN SELECT A.C11,B.D2 FROM T1 A, T2 B WHERE A.C1 B.D1;执行计划1 #NSET2: [1, 12, 56]2 #PRJT2: [1, 12, 56]; exp_num(2), is_atom(FALSE)3 #NEST LOOP INDEX JOIN2: [1, 12, 56]4 #CSCN2: [1, 4, 52]; INDEX33555676(T2 as B); btr_scan(1)5 #SSEK2: [1, 3, 4]; scan_type(ASC), IDX_T1_C1(T1 as A), scan_range[B.D1,B.D1], is_global(0)首先确认执行计划的解读顺序遵顼以下原则缩进越深的越先执行2、同样缩进的上面的先执行下面的后执行3、上下的优先级高于内外具体顺序为1. 步骤4 #CSCN2: [1, 4, 52]; INDEX33555676(T2 as B); btr_scan(1)2.步骤5#SSEK2: [1, 3, 4]; scan_type(ASC), IDX_T1_C1(T1 as A), scan_range[B.D1,B.D1], is_global(0)3. 步骤3NEST LOOP INDEX JOIN2嵌套循环连接4. 步骤2PRJT2投影操作5. 步骤1NSET2结果集输出备注CSCN2:[1, 4, 52]为三元组合3个数字分别表示【估算代价结果条数行数据的长度】如果是通过AUTOTRACE TRACE 查看执行计划时结果条数会有两个结果一个是预估值一个是实际值如果偏差较大说明该步骤可能会引起性能问题需要注意。ET工具ET是达梦数据库自带的SQL 性能分析工具能统计执行计划中每个操作符的时间花费将代价显示为具体的时间从而定位到有性能问题的操作指导我们去优化。ET是默认关闭的因此在使用ET之前需要先开启。开启和关闭ET的方法开启ET功能开启ET功能要尽可能的只开启会话级参数MONITOR_SQL_EXECSP_SET_PARA_VALUE(1,ENABLE_MONITOR,1);SP_SET_PARA_VALUE(1,MONITOR_SQL_EXEC,1); --不建议使用SF_SET_SESSION_PARA_VALUE(MONITOR_SQL_EXEC,1); ---会话级确认ET功能是否开启select * from v$parameter t where NAME IN( MONITOR_SQL_EXEC,ENABLE_MONITOR);其中ENABLE_MONITOR动态参数(系统级)MONITOR_SQL_EXEC动态参数(会话级)。因ET会对数据库性能有影响使用完后记得关闭以保证数据库性能不受影响。ET说明ET的应用说明在manager或者disql中执行sql之后会有一个对应的SQL执行号在开启et功能的情况下可以使用ET(SQL执行号)的方式来获取SQL的执行耗时情况。参考下图OP操作符TIME(US)时间开销单位为微秒PERCENT执行时间占总时间百分比RANK执行时间的耗时排序SEQ执行计划的节点号N_ENTER进入次数AUTOTRACE 工具AUTOTRACE工具主要用于执行计划和统计信息的跟踪。AUTOTRACE语法说明语法如下SET AUTOTRACE OFF(默认值) | NL | INDEX | ON | TRACE | TRACEONLYSET AUTOTRACE TRACE常用重要SET AUTOTRACE TRACE 时开启 AUTOTRACE 功能执行语句打印执行计划。此功能与服务器 EXPLAIN 语句的区别在于EXPLAIN 只生成执行计划并不会真正执行SQL 语句因此产生的执行计划有可能不准。而 TRACE 获得的执行计划是服务器实际执行的计划。select /*ENABLE_HASH_JOIN(0)*/* from DMHR.EMPLOYEE a,dmhr.DEPARTMENT b where a.manager_idb.MANAGER_ID;SET AUTOTRACE TRACEONLY常用重要SET AUTOTRACE TRACEONLY 时开启 AUTOTRACE 功能执行语句打印执行计划。此功能与 TRACE 区别在于对于查询语句集不打印结果集。select /*ENABLE_HASH_JOIN(0)*/* from DMHR.EMPLOYEE a,dmhr.DEPARTMENT b where a.manager_idb.MANAGER_ID;统计信息基于代价的优化器CBO可以根据统计信息选择最佳的查询执行计划。统计信息准确与否会影响SQL执行效率。DM支持自动、手动的方式采集统计信息。需要注意的是,采集统计信息时会消耗系统资源,需要在业务空闲的时间段谨慎执行。采集方式自动采集在 INI 参数 AUTO_STAT_OBJ 为 1 或 2 前提下执行 SP_CREATE_AUTO_STAT_ TRIGGER 过程实现自动收集。例如以下SQL表示 从2023/8/10开始,每天22:20自动执行统计信息收集。SP_CREATE_AUTO_STAT_TRIGGER(1, 1, 1, 1,22:20, 2023/8/10,0,1);手动采集通过存储过程收集#收集TEST_DB.TEST_TB的索引统计信息 SP_TAB_INDEX_STAT_INIT (TEST_DB, TEST_TB); #对TEST_DB的IDX_1索引收集 SP_INDEX_STAT_INIT (TEST_DB, IDX_1); #对TEST_DB.TEST_TB的ID字段收集 SP_COL_STAT_INIT(TEST_DB, TEST_TB,ID); #对TEST_DB.TEST_TB所有字段收集 SP_TAB_COL_STAT_INIT(TEST_DB, TEST_TB); #指定采用率对TEST_DB.TEST_TB所有字段收集 SP_STAT_ON_TABLE_COLS(TEST_DB, TEST_TB,10); #对某张表和索引生成统计信息 SP_TAB_STAT_INIT(TEST_DB, TEST_TB);通过stat语法收集对字段按百分比收集 STAT 30 ON TEST_DB.TEST_TB (ID); STAT 30 ON TEST_DB.TEST_TB (PID,NAME); 对索引按百分比收集 STAT 50 ON INDEX PURCHASING.S1; 对表生成统计信息 STAT ON SYS.SYSOBJECTS;执行计划相关参数动态采集参数OPTIMIZER_DYNAMIC_SAMPLING当统计信息不可用时是否启用动态统计信息。取值范围:0-12。0不启用1-10启用采用率 10%-100%11启用由优化器确定采样率0.1%-99.9%12同 11但收集的结果会持久化保存。默认值为0HINTINI 参数hint可以通过 HINT 方式对 INI 参数的值进行语句级的指定。语句中的 HINT 对 INI参数值的设置优先级高于 INI 文件中参数值的设置。通过 HINT 方式只会修改 INI 参数的在本会话中的值不会改变它在 INI 文件中的值。支持使用 HINT 的 INI 参数可通过 V$HINT_INI_INFO 动态视图查询。支持 HINT 的INI 参数分为两类一是 HINT_TYPE 为―OPT表示分析阶段使用的参数二是 HINT_TYPE为―EXEC表示运行阶段使用的参数运行阶段使用的参数对于视图无效。例如SELECT /ENABLE_HASH_JOIN(1)/ * FROM T1,T2 WHERE C1D1;上面的语句中使用了HINT指明在执行此SQL时参数ENABLE_HASH_JOIN被置为1。索引hint使用特定索引语法表名 INDEX 索引名 或 /* INDEX (表名[,] 索引名) {INDEX (表名[,] 索引名)}/示例SELECT * FROM T1 INDEX IDX_T1_ID WHERE ID 2011 AND NAME XXX;或SELECT /INDEX(T1, IDX_T1_ID)/ * FROM T1 WHERE ID 2011 AND NAME XXX;不使用特定索引/ NO_INDEX (表名[,] 索引名) { NO_INDEX (表名[,] 索引名)} */连接方式hint可以通过指定两个表间的连接方法来检测不同连接方式的查询效率指定的连接可能由于无法实现或代价过高而被忽略。如果连接方法提示中的表名(别名)或索引名无效也会被自动忽略。USE_HASH强制两个表间使用指定顺序的哈希连接例如EXPLAIN SELECT /* USE_HASH(T1, T2) */ * FROM T1, T2 WHERE T1.ID T2.ID;NO_USE_HASH强制两个表间不能使用指定顺序的哈希连接例如EXPLAIN SELECT /* NO_USE_HASH(T1, T2) */ * FROM T1, T2 WHERE T1.ID T2.ID;NO_USE_HASH(T1, T2)表示不允许 T1 作为左表 T2 作为右表的哈希连接但 T1 作为右表的哈希连接还是允许的。USE_NL强制两个表间使用嵌套循环连接例如EXPLAIN SELECT /* USE_NL(A, B) */ * FROM T1 A, T2 B WHERE A.ID B.ID;NO_USE_NL强制两个表间不能使用嵌套循环连接例如EXPLAIN SELECT /* NO_USE_NL(A, B) */ * FROM T1 A, T2 B WHERE A.ID B.ID;USE_NL_WITH_INDEX当连接情况为左表右表索引时强制两个表间使用索引连接例如EXPLAIN SELECT /* USE_NL_WITH_INDEX(T1, IDX_T2_ID) */ * FROM T1, T2 WHERET1.ID T2.ID;NO_USE_NL_WITH_INDEX当连接情况为左表右表索引时强制两个表间不能使用索引连接例如EXPLAIN SELECT /* NO_USE_NL_WITH_INDEX(T1, IDX_T2_ID) */ * FROM T1, T2 WHERE T1.ID T2.ID;USE_MERGE强制两个表间使用归并连接。归并连接所用的两个列都必须是索引列。例如EXPLAIN SELECT /* USE_MERGE(T1,T2) */ * FROM T1, T2 WHERE T1.ID T2.ID ANDT1.ID 1 AND T2.ID 1;当连接类型为外连接时无法使用归并连接此时即使指定 USE_MERGE也不起作用。NO_USE_MERGE强制两个表间不能使用归并连接例如EXPLAIN SELECT / NO_USE_MERGE(T1,T2)/ * FROM T1, T2 WHERE T1.ID T2.ID AND T1.ID 1 AND T2.ID 1;
