慢SQL优化实战报告

 1优化思路

随着国产化替代战略的深入推进，达梦数据库作为我国自主研发的关系型数据库管理系统，在政府、金融、能源、电信等关键行业得到广泛应用。数据库性能是衡量其能否承载真实业务系统的关键指标，而在影响数据库性能的诸多因素中，SQL的执行效率往往是最直接、最普遍的瓶颈来源，因为一条低效的SQL在高并发下可能拖垮整个系统。

优化思路如下：先通过ET进行分析，看时间占比最大的那几个，根据里面的行号，去trace里进行找到对应的行号，根据trace里的找到的行的内容，分析涉及到了哪些对象，最后去SQL里找到正确、相关的命令，通过分析SQL，进行类似于：走没走索引、走了索引效果还差、这个索引是否过滤性差、查询是否可以合并等的分析，进行优化SQL。

2改写SQL

2.1背景

在数据库查询中，SQL的写法直接影响执行计划的优劣：同样的查询逻辑，不同的写法可能导致完全不同的执行效率。

常见的SQL写法问题包括：索引列被函数包住导致索引失效、重复子查询扫同一张表导致代价翻倍、条件下推位置不当导致中间结果集过大等。这些问题往往不是索引本身的问题，而是SQL写法不当，导致优化器无法生成高效的执行计划。

通过改写SQL，可以去掉索引列上的函数转换、合并重复子查询、调整条件下推位置，从而让索引生效、减少扫描行数和回表次数、降低I/O代价，使查询速度从秒级降到毫秒级。

因此，对于因SQL写法不当导致的慢查询，改写SQL往往是最直接、最有效的优化手段。

2.2案例分析

拿到ET、trace以及SQL后，我们先看ET的内容：

从最下方往上看（因为越往下时间占比越大），可以看到占比较大的几条。内容如下：

BLKUP2 10225760 15.09% 4 53 3744 0 0 0 0 0

BLKUP2 10364374 15.3% 3 60 3744 0 0 0 0 0

PRJT2 21054173 31.07% 2 21 4 0 0 0 0 0

BLKUP2 24853719 36.68% 1 41 12830 0 0 0 0 0

先介绍一下这个ET的内容应该怎么看：

从左往右开始，第一列是操作符，比如BLKUP2的意思就是回表操作，PRJT2是投影操作；第二列是执行时间的意思；第三列是时间占比；第四列是占比等级；第五列是对应trace里的行数，后面几列分别代表着：调用次数、内存、磁盘、哈希等属性。

接下来我们进行具体分析。

（1）BLKUP2 24853719 36.68% 1 41 12830 0 0 0 0 0

我们可以看到，这里的时间占比最大，且对应的行号是41，于是我们去trace里看41行的具体执行计划的操作：

拎出来看：

#BLKUP2: [15, 1163->1229028, 48]; T_KE_JOURNALS_ORGID_IDX3(T_KE_JOURNALS)

可以看到这一步是进行了回表操作，且行数到了122万，代价极大。

这里插入对回表的说明：

回表的意思是，使用索引的时候，可能索引中的关键列和我们要查的列不一致，所以通过索引进行筛选后得到的数据，还需要通过返回到原表数据中进行查询，将查询到的原数据拿出来再进行投影等操作。

因此，如果说索引过滤的效果不好，需要进行回表的内容就会很多，相当于双倍大消耗。

我们可以看到上面那句用到了索引T_KE_JOURNALS_ORGID_IDX3，且表名为T_KE_JOURNALS

光通过trace计划看信息仍是不全的，目前我们只能怀疑是这个索引的效果不好导致的回表数据太多，进而使得时间消耗过大；因为，我们现在需要去SQL里找到这句操作对应的SQL命令。

那我们要怎么找到对应的SQL呢？

其实是通过上面说到的表名T_KE_JOURNALS，拿着这个信息去SQL里进行查找，可以看到有三处都提到了这个表：

究竟哪一个才是我们要找的内容：可以通过观察SQL命令，看到前两处用到的索引都是ACCOUNTID，只有第三处才是ORGID，因此得到真正的SQL内容：

from (select t.ORGID,

t.BANKID,

t.OPENBANKID,

t.ACCOUNTID

from T_KE_JOURNALS t

where t.ORGID in (select tm.org_id

from tsys_organization tm

start with tm.ORG_ID = ?

connect by prior tm.ORG_ID = tm.parent_id )

and t.CANCELFLAG = '0'

group by t.ORGID,

t.BANKID,

t.OPENBANKID,

t.ACCOUNTID) t

看到里面用到了两处条件，分别是ORGID和CANCELELAG，可以看到CANCELELAG这种条件无论值是多少，过滤出来的数据都会很大；同时经过索引对象的查询后，发现ORGID索引的过滤效果也不好，因此这部分无法进行很好的优化。

（2）PRJT2 21054173 31.07% 2 21 4 0 0 0 0 0

接着我们来分析第二等级的时间占比。这是一个投影操作，同样我们无法从这一句得到什么有效信息，只能通过给出的行号21，去Trace里看更详细的操作。

拎出来看：

#PRJT2: [1, 57->57, 78]; exp_num(4), is_atom(FALSE)

这句提供的信息有：

1）exp_num(4)：即投影需要输出4列，且

2）is_atom(FALSE)：即这个投影下，还挂着子节点，它还不是最底层

这就是我们知道的信息，根据“投影要输出4列”这个信息，去SQL命令里寻找，可以找到3处投影都要输出4列：

但结合我们的第二条信息“挂着子节点”，我们就可以排除掉另外两处（因为另外两处投影操作都是输出4个原子对象），只有剩下的一处投影才输出2个原子对象以及拥有两个子查询（如上图中蓝色框和红色框所示）。

因此我们现在来分析这段SQL命令：

from (select b.accountid,

b.balanceamount, (SELECT SUM(r.AMOUNT)

FROM T_KE_JOURNALS r

WHERE r.ACCOUNTID = b.ACCOUNTID

AND TO_CHAR(r.ACCOUNTDATE, 'yyyy-MM-dd') <= '2026-09-15'

AND r.DEBITCREDITFLAG = '2'

AND r.CANCELFLAG = '0'

and r.Isreconciliation = '1') RECEIVEAMOUNT, (SELECT SUM(p.AMOUNT)

FROM T_KE_JOURNALS p

WHERE p.ACCOUNTID = b.ACCOUNTID

AND TO_CHAR(p.ACCOUNTDATE, 'yyyy-MM-dd') <= '2026-09-15'

AND p.DEBITCREDITFLAG = '1'

AND p.CANCELFLAG = '0'

and p.Isreconciliation = '1') PAYAMOUNT

我们可以看到，b.accountid和b.balanceamount是两个原子对象，后面的两个投影对象是两个子查询，意思是对T_KE_JOURNALS_BALANCE里的每一个账户，算出它的"收款总额"和"付款总额"。

前两个原子对象没有可以优化的空间，后面的两个子查询才是时间占比的大头，将这两个子查询单拎出来：

1. 可以看到这两个子查询除了起的别名、方向条件DEBITCREDITFLAG不一样，其他都一模一样；
2. 同时，我们合理推测到底是不是这两个子查询的效率不高导致的这部分的时间占比大，所以需要回到trace里找到里面的子查询操作，查看里面扫描的行数。

我们看到trace里有五处地方有SPLT2，即子查询操作，但是蓝色框处正好是前面sql语句里提到的条件判断（<= '2026-09-15'），因此锁定前两处的SPLT2所在部分。

这里我们只分析其中一个（因为这两个子查询正如前面所说，基本没有区别），我们看到第一步SSEK2走的是范围扫描，并不是精确定位，但是这一步在SQL里对应的是：

WHERE r.ACCOUNTID = b.ACCOUNTID

AND TO_CHAR(r.ACCOUNTDATE, 'yyyy-MM-dd')

这里明明走了索引ACCOUNTID、ACCOUNTDATE缺还是范围扫描，说明索引出了问题，再看BLKUP2回表操作，它回表的行数仍是53万，产生巨大的代价。

随意严重怀疑索引有问题，回到SQL查看，发现时间判断这里，索引ACCOUNTDATE被外面的TO_CHAR给包住了，所以索引没法生效，于是只能一行一行进行对比。

这里的解释下为什么被包住了就无法生效：

因为ACCOUNTDATE是日期类型，外面包住TO_CHAR就会让索引的对象变成字符型，可能写命令的编程者是想要让左边的对象类型转换成字符型，使得和右边的判断条件对应上，同时可能是想要让不同数据库输出时的时间书写格式一致，所以才让外面包一层TO_CHAR。

但这就无意之间让索引派不上用场，我们想要让索引发挥作用，就需要将索引列ACCOUNTDATE外面的转换对象去掉；同时，如果真的想要让两边都对应上比较且一致时，可以选择将右边的时间条件转换成是日期类型。

此外，除了可以修改这里的索引，还可以将这两处子查询合并，因为通过前面的分析和查看，我们知道每个子查询中的回表操作都是53万行，而回表的代价很大，因为是回表查询是随机的。将两处子查询合并至少可以减少一半的时间消耗。

因此可以改写如下：

from T_KE_JOURNALS_BALANCE b

LEFT JOIN (

SELECT r.ACCOUNTID,

SUM(CASE WHEN R.DEBITCREDITFLAG = '2' THEN R.AMOUNT ELSE 0 END) AS RECEIVEAMOUNT,

SUM(CASE WHEN R.DEBITCREDITFLAG = '1' THEN R.AMOUNT ELSE 0 END) AS PAYAMOUNT

FROM T_KE_JOURNALS R

WHERE R.ACCOUNTDATE < TO_DATE('2026-09-16', 'YYYY-MM-DD')

AND R.CANCELFLAG = '0'

AND R.ISRECONCILIATION = '1'

GROUP BY r.ACCOUNTID

) B1 ON B.ACCOUNTID = B1.ACCOUNTID

1. 继续往后看ET的占比：

看到trace里对应的行号分别是60和53，于是我们去trace里查看：

发现这两处就是我们刚刚说的子查询中的回表操作，就是索引没用好导致的回表数据量大。通过上述的SQL改写进行优化解决。

其余的操作，时间占比都不大，可以不去看。

因此，这个案例，其慢SQL的主要原因是：两个SUM相关子查询，因为TO_CHAR(ACCOUNTDATE)把字段包住，导致索引失效、只能范围扫描，扫出53万行后触发53万次回表（随机 I/O）；而且两个子查询结构相同、扫的是同一张表，各扫一遍导致代价翻倍。

2.3总结

本案例是典型的“改写SQL”类型，这种类型的慢SQL，其特征通常是SQL写法不当，导致索引失效、重复扫描或执行计划不优。具体表现为索引列被函数包住、两个结构相同的子查询各扫同一张表、子查询逐行执行、OR 条件导致索引失效等。

优化思路是针对性改写SQL，如：去掉索引列上的函数转换、合并重复子查询、子查询改JOIN、EXISTS改JOIN、OR改写UNION ALL、INSTR改层次查询、条件下推等，通过改，回表行数大幅减少，性能提升明显。

3索引选择干预

3.1背景

在数据库查询中，优化器会基于统计信息为SQL选择执行计划，其中就包括选择哪个索引。

当表上存在多个索引时，优化器需要估算每个索引的代价，选出它认为最优的那个；但统计信息可能过时、不准确，或者优化器估算出现偏差，导致它选了一个过滤性较差的索引，从而引发大量回表、扫描行数过多，查询变慢。

此时就需要人工干预索引选择，让优化器走过滤性更好的索引，以减少扫描行数、减少回表次数、降低I/O代价，使查询速度从秒级降到毫秒级。

因此，对于因优化器选错索引导致的慢查询，干预索引选择往往是最直接、最有效的优化手段。

3.2案例分析

本处案例我们继续沿用上述案例中的分析思路，先看ET的信息：

可以看到最后一行的回表操作几乎占用了全部的时间，于是我们需要重点分析这这一操作符所对应的trace里的行的内容，可以知道是第25行，因此我们现在去trace中查看。

根据第25行，我们可以看到回表涉及到的表名是T_KE_JOURNALS，索引是T_KE_JOURNALS_ORGID_IDX3，于是合理猜测这个索引的过滤效果不好，于是回到上一步的操作，即看看第26行是怎么用的索引扫描。

将这两行单拎出来，内容如下：

#BLKUP2: [43, 1163->1229028, 48]; T_KE_JOURNALS_ORGID_IDX3(T_KE_JOURNALS)

#SSEK2: [43, 1163->1229028, 48]; scan_type(ASC), T_KE_JOURNALS_ORGID_IDX3(T_KE_JOURNALS), is_global(0), scan_range[DMTEMPVIEW_901437055.colname,DMTEMPVIEW_901437055.colname]

看到索引扫描走的等值扫描，即在表T_KE_JOURNALS上通过索引T_KE_JOURNALS_ORGID_IDX3对索引的内容进行筛选。

究竟筛选的是什么呢，我们需要进入SQL里查看对应的内容，用表名作为线索查找，看到只有一处涉及到这个表名：

可以看到这段SQL里，有几个条件（and下就是条件内容），先看第一个条件：

t.ORGID in (select tm.org_id

from tsys_organization tm

start with tm.ORG_ID = ?

connect by prior tm.ORG_ID = tm.parent_id )

通过看这段sql内容，尤其是start with以及prior = parent_id这种信息，我们就可以知道这里是进行了一个递归查询，最后得到的是想要的org_id列的内容。

所以再结合我们上面看到的trace的第26行中的等值扫描，大概率指的就是这个递归查询出来的值，只不过是将查询的结果用临时视图保存了而已，同时根据索引的最左匹配原则知道，这个索引所涉及到的列，要么就只包含ORGID列，要么ORGID列在索引结构的最左侧，同时索引包含的列是ACCOUNTDATE、TENANTID之外的列。

这里说明下我的想法：

首先我们讲下索引扫描这个书写形式，如果是范围扫描，同时索引包含的列有多列，只对其中一个列进行了范围要求，那么在书写的时候，也是需要写成[(col, min), (col, max)]这种形式的，即对不要求范围的对象需要从头扫描到尾的；如果是等值扫描，不同数据库的书写习惯可能不同，导致有的需要写成scan_range[(value, min), (value, max))形式，有的直接[value，value]

所以我们不知道这个索引的结构涉及到哪些列，只知道包含列ORGID，并且一定在最左侧。因为索引的最左匹配原则规定，从左往右开始生效，最左边的是第一个过滤条件。

根据trace的内容，我们可以看到25行的回表操作后，进行了CANCELFLAG和ACCOUNTDATE、TENANTID的过滤操作：

对应SQL里的内容如下：

很显然如果索引T_KE_JOURNALS_ORGID_IDX3的列包含以上几种，就不会出现先回表再过滤的情况，而是先过滤，将得到的行数尽可能精确、让输出的结果数量尽可能少，这样回表的行数就少。这就证明了我以上的猜想，即一种可能是这个索引只包含列ORGID，要么包含ORGID和这里提到的列之外的列名。

因此可以初步判断：该索引在此查询条件下过滤性不佳，导致回表行数过大、查询变慢，问题根源大概率是优化器选错了索引。

接下来需要确认表上有哪些可用索引、当前索引与候选索引的列结构差异。通过查询，发现T_KE_JOURNALS表上存在多个索引，包括T_KE_JOURNALS_ORGID_IDX、T_KE_JOURNALS_ORGID_IDX1、T_KE_JOURNALS_ORGID_IDX3等。

进一步查看索引列定义后确认：当前优化器选择的T_KE_JOURNALS_ORGID_IDX3不包含ACCOUNTDATE列，而T_KE_JOURNALS_ORGID_IDX包含ACCOUNTDATE列。这意味着如果走IDX，可以在回表之前先用ACCOUNTDATE条件过滤掉一部分数据，从而减少回表行数。

最后通过强制索引的方式，使得优化器走T_KE_JOURNALS_ORGID_IDX索引，这个索引包含的列与前一个索引相比多了ACCOUNTDATE列，使得在回表之前先将T.ACCOUNTDATE >= ... AND T.ACCOUNTDATE <= ...过滤掉，最后时间从10秒优化到了0.3秒，优化生效。

这里插一个我当时的疑惑——为什么不新建一个索引，并且让这个索引包含条件里涉及到的所有列，这样就可以在第一步直接过滤掉所有的条件，比如说让索引还包含TENANTID、CANCELFLAG列，最后直接用更少的数据回表就行。

在确定优化器选错索引后，我们遵循“优先使用已有索引，实在不行再新建索引”的原则，先查询该表上已有的索引，确认这些索引的列是否正好对应SQL条件中涉及的列；再结合统计信息（如不同键值数、行数等）判断这些索引的过滤性，从中选出过滤性更好的索引。

本案例中，由于已有索引T_KE_JOURNALS_ORGID_IDX已经满足需求，因此不需要新建索引，而是通过强制指定索引的方式，让优化器走这个已有索引。

之所以优先复用已有索引，而不直接新建，是因为在生产环境中新建索引有较大代价：建索引会消耗大量CPU、I/O和磁盘空间，建索引期间数据库可能对表加锁，导致其他业务操作被阻塞，甚至造成业务中断；同时，表上已有多个索引，再新增会导致DML变慢，优化器选择难度也会增加。

因此，索引优化的规范顺序是：先查已有索引，有合适的就直接用；没有合适的，才考虑新建索引。

3.3总结

本案例是典型的“索引选择干预”类型。这种类型的慢SQL，特征通常是表上有多个索引，优化器选错了索引，走了过滤性较差的那个。

具体表现为优化器走了索引，但是走的索引过滤性又很差，回表行数远超预期，这个时候我们就要怀疑是缺索引还是走错索引。先通过trace确认走的是哪个索引，然后回SQL中看条件涉及的列，再查表上已有索引，看哪个索引的列更匹配、过滤性更好，确定是优化器走错了索引导致的过滤性不好。

同时，我们优先用已有索引，通过强制指定索引改写；如果已有索引都不合适，我们才考虑新建索引，如下一个类型的内容。

4缺索引

4.1背景

在数据库查询中，索引是提升查询效率最直接的手段之一。当SQL的WHERE条件、连接条件涉及的列上没有索引时，数据库只能进行全表扫描，扫描整张表的数据，代价极大；尤其是当表数据量达到百万级时，全表扫描会产生大量逻辑读和物理读，导致查询耗时从毫秒级上升到秒级甚至更久。

而在过滤性好的列上建索引，则可以从全表扫描变成索引扫描，只扫描满足条件的数据，减少扫描行数；索引过滤后，回表行数大幅减少，逻辑读、物理读显著下降，查询速度从秒级降到毫秒级。

因此，对于因缺索引导致的慢查询，新建索引往往是最直接、最有效的优化手段。

4.2案例分析

根据ET、trace、SQL的分析顺序，过程如下所示。

先看ET，其内容如下：

根据从下往上的顺序，可以看到，时间占比等级第一的是哈希右外连接操作，占比40.61%，紧接着是全表扫描操作，占比33.53%，索引扫描操作，占比20.55%。

接下来我们具体分析。

（1）HRO2 1336 40.61% 1 9 15 12088 0 0 0 3557[564097] 14 0

先介绍一下这个操作符HRO2：H代表着Hash；R代表Right；O是out，表示哈希连接算法，做右外连接（右外连接的含义是保留右表的全部行，左表没匹配上的补NULL）。

从这个ET信息我们可以看到对应在trace里的行号是第9行，因此我们去trace信息里将第九行拎出来，其内容如下：

#HASH RIGHT JOIN2: [626, 97818->0, 410]; key_num(1); col_num(11); MEM_USED(12088KB), DISK_USED(0KB) KEY(D.ID_=T.PROC_DEF_ID_)

先分析这行的操作：

key_num(1)的意思是连接键的数量为1；col_num(11)的意思是哈希连接后输出的行，其包含的列有11列；MEM_USED(12088KB)的意思是哈希表的存储占用了12088KB内存空间大小；DISK_USED(0KB)的意思是没有用到磁盘空间，说明这个哈希表的存储还没有用到很大的空间（毕竟哈希表就是通过时间换时间这一特点进行加快匹配、搜索的）；KEY(D.ID_=T.PROC_DEF_ID_即连接条件，分别对应着连接表的连接键（连接键分别为D.ID_和T.PROC_DEF_ID_）。

补充下对哈希连接的介绍：

哈希连接的意思就是，让一张小表中的关键列建成一张哈希表，通过哈希算法，建立列的值和序号的对应关系，同时对应的这一行的数据也存在哈希表（要么通过显示写上，要么通过指针的方式指向这一行的值），大表想要和这张小表进行匹配，就需要对连接列进行哈希算法找到哈希表中对应的序号，然后取到对应的值，再让两张表的行进行拼接。

那既然可以通过哈希算法直接定位到想要找的行，为什么时间占比会这么大？

这个时候我们就需要联系上下文来进行判断，将第九行的前提操作一并取出来，内容如下：

第九行的哈希连接的子节点是第10行和第11行，第10行是进行了一个全表扫描，第11行是哈希左连接，两个输入分别是第12行和第15行；具体结构可以看下图：

因此，第九行的时间占比大主要看第10行和第11行，其HRO2要做的事是把第10行ACT_RE_PROCDEF读进来；把第11行HASH LEFT JOIN2读进来；再用D.ID_ = T.PROC_DEF_ID_做哈希连接。

但是根据三元组的行数可以看到，第11行的行数（97818）远大于第10行（3571），因此我们重点分析第11行的内容。

第11行是进行了哈希左连接，连接列分别是T.PROC_INST_ID_和P.PROC_INST_ID_，并且根据SQL内容，我们可以知道T是ACT_HI_TASKINST的别名，P是act_hi_procinst的别名：

因此可以得到结论：第12行是第11行的左输入，第15行是第11行的右输入。

根据trace计划，我们可以看到第15行得到的行数才300，相反，第12行的行数是978818行，同时第11行的行数也是91818，就是说，第11行的代价大主要是因为第12行这边带来的。

所以我们现在重点分析第12行以及后面的子节点的内容：

#SLCT2: [150, 97818->0, 410]; T.TENANT_ID_ = exp_param(no:0)

#BLKUP2: [150, 96015->0, 410]; ACT_HI_TASKINST_DM01(ACT_HI_TASKINST)

#SSEK2: [150, 96015->0, 410]; scan_type(ASC), ACT_HI_TASKINST_DM01(ACT_HI_TASKINST), is_global(0), scan_range[exp11,exp11]

（1）SSEK2：这里用索引ACT_HI_TASKINST_DM01对表ACT_HI_TASKINST进行了等值查询（从scan_range[exp11,exp11]看出来），但是显然过滤效果不好，因为后面的代价都没有发生变化。

（2）BLKUP2：通过索引扫描得到的结果进行回表操作，回表了96015行，代价很大，严重怀疑是这里导致的第11行的代价大。

（3）SLCT2：回表取回对应的行后，再对数据进行了某一列传参的过滤。

于是我们合理推测，是这个索引的等值查询用到的列的过滤性太差，返回的数据的数据量还是很多，需要考虑更换索引。

回到SQL里看目前用的索引是什么：

这里可以看到是TENANT_ID_列的过滤性不好，一个值对应大量行，导致的索引扫描后回表量依然巨大，于是考虑更换索引，通过回看SQL内容，查看剩余可用于索引的列，即START_TIME_

按照规范、完整的优化流程，我们应当先查询该表上已有的索引，确认这些索引的列是否正好对应SQL条件中涉及的列。

如果有对应索引，则需要查看统计信息，判断这些索引的过滤性，如果有过滤性比当前索引更好的索引，就可以直接更换索引；如果没有合适的索引，才考虑新建索引。新建索引后，还需再次查看新索引的过滤性是否有所提升，若提升明显，则说明优化有效。

但在本案例中，由于现有信息不够完整，我们无法完整执行上述流程，因此直接记录最终实际执行的操作，即针对START_TIME_列新建索引。

上述规范、全面的操作流程，才是更标准、更完整的优化方式。

新建索引列START_TIME_：

CREATE OR REPLACE INDEX "NTMS"."ACT_HI_TASKINST_DM01" ON "NTMS"."ACT_HI_TASKINST"("START_TIME_" ASC) STORAGE(ON "TS_NTMS", CLUSTERBTR) ;

然后重新执行SQL，并查看执行计划：

我们可以看到过滤后的行数从原本的97818行降到54094行，新索引过滤后还是涉及5w多数据，遂无法进一步优化。

4.3总结

本案例是典型的“缺索引”类型。这种类型的慢SQL，特征通常是过滤性好的列上没有索引，导致全表扫描或大量回表。

具体表现为ET里出现CSCN2全表扫描或BLKUP2大量回表；trace里能看到走的是全表扫描，或者走了索引但过滤性差（因为过滤性好的列上没有建索引）。

因此，优化思路是在trace里确认扫描方式，然后回SQL看WHERE条件涉及的列，再查表上已有索引，看这些列上有没有索引，没有则给这些过滤性好的列新建索引，让优化器不再走全表扫描，或者选择该索引后回表行数大大减少，以此来实现SQL优化。

5关联方式不对

5.1背景

在数据库查询中，优化器会基于统计信息为SQL选择执行计划，其中就包括选择关联方式。常见的关联方式有Nested Loop、Hash Join、Semi Join等。

例如，当SQL里有exists子查询时，正常情况下优化器会基于代价估算，自动把exists下推成 semi join，因为这样比逐行执行子查询更高效。但统计信息可能过时、不准确，或者优化器估算出现偏差，导致它选错了关联方式，从而引发大量回表、扫描行数过多，查询变慢。

此时就需要人工干预关联方式，让优化器走更优的关联方式，以减少扫描行数、减少回表次数、降低I/O代价，使查询速度从秒级降到毫秒级。

因此，对于因优化器选错关联方式导致的慢查询，干预关联方式往往是最直接、最有效的优化手段。

5.2案例分析

根据ET、trace、SQL的分析顺序，过程如下所示。

先看ET，其内容如下：

根据ET的内容，从最下方往上看，可以看到占比较大的几条。内容如下：

HLS2 460822 3.68% 6 13 5379 418300 0 0 0 3050[564097] 796255 0

HAGR2 636241 5.08% 5 12 3824 57960 0 0 0 97936[101429],147376[3039917] 119897,3691 0

HI3 952505 7.6% 4 15 5379 233993 0 0 0 7924[564097] 791462 0

HI3 1031245 8.23% 3 14 5399 326711 0 0 0 10254[564097] 789132 0

SSEK2 3101551 24.74% 2 65 333226 0 0 0 0 null null 0

SLCT2 4779131 38.12% 1 10 1163 0 0 0 0 null null 0

接下来我们进行具体分析：

（1）SLCT2 4779131 38.12% 1 10 1163 0 0 0 0 null null 0

时间占比最大的是这个过滤操作，占了38.12%，且对应trace里的第10行，取出对应的内容，如下所示：

#SLCT2: [260, 1->360, 576]; NOREFED_EXISTS_SSS[sss3]

1）SLCT2对应着SQL中的where，即过滤操作，表示对输入的数据行，按条件做筛选，符合条件的保留，不符合的丢弃；

2）NOREFED即No Referenced，意思是“未被引用/未下推”；

这里有两个概念：

引用：子查询里用到了外层的列，比如SQL里的：

其中ou.user_id = t.user_id、ou.org_id = t.org_id，子查询引用了外层的t.user_id、t.org_id，这种子查询就叫关联子查询。

下推：优化器发现子查询和外层有这种关联后，可以把子查询里的表和外层表直接做一次JOIN，比如：

这样就把子查询“下推”成了JOIN，不需要逐行执行子查询。

当优化器成功完成这个下推动作时，就叫“引用”；没做到，就叫NOREFED（未被引用/未下推）。

因此，NOREFED_EXISTS_SSS就表示这个exists子查询没被下推，只能逐行执行，对应到SQL中进行验证：

SQL中只有这一处有exists，内容如上，这个exists子查询用来判断外层每一行记录在tsys_org_user或tsys_role_user中是否存在匹配的用户-组织-角色关系。它包含两个分支，用union连接：

分支1查tsys_org_user，判断用户在该组织下是否有该角色；

分支2查tsys_role_user+tsys_user，同样判断用户在该组织下是否有该角色。

且两个分支都引用了外层的t.user_id、t.org_id，是关联子查询。

因为exists没被下推，所以外层每读一行t，就要执行一次这个子查询，并且子查询里还有union去重，每次都要扫描tsys_org_user和tsys_role_user，这才导致的执行慢。

这里解释下union为什么会导致时间消耗大：

union去重慢，是因为它不只是合并两个结果集，还要把重复行删掉。去重需要排序或建哈希表，要扫描整个结果集，额外付出代价，所以比union all慢。本案例里，exists没下推，使得外层每读一行t就要执行一次子查询，union去重也要执行N次。

（2）SSEK2 3101551 24.74% 2 65 333226 0 0 0 0 null null 0

时间占比第二的是索引扫描，占了24.74%，在trace里对应第65行，内容如下;

可见该操作包含在一个子查询中，且索引名是UK_SYSORGUSER，表名是TSYS_ORG_USER，从scan_range可以看出，这里扫描的是四个列，且四个列都是等值扫描（上下界相同）；根据表名我们回到SQL中找到对应的内容：

在SQL中有两处地方对应这个表名，但根据上面说的附加条件（有四个等值扫描），我们可以看到左侧图中只涉及到了一处条件，而右侧图中才涉及到了四个条件，因此可以确认以上操作对应的就是右侧的SQL内容；并且这部分正好是时间占比第一的SLCT2对应的exists子查询中的一部分。

也就是说，SSEK2的24.74%是exists子查询内部tsys_org_user的索引扫描代价，且估算输出357行，这个值并不算多，说明单次索引扫描的过滤效果还可以，而且该操作符下面没有BLKUP2，即没有回表操作，那就更不用花费更多代价。

那为什么它的时间占比还有24.74%？

因为它是exists子查询的一部分，前面说到：exists没被下推，外层每读一行t，就要执行一次这个子查询。假设外层有N行，SSEK2就要执行N次，N次累加，即使单次时间消耗不多，总的时间占比也会很高。

所以问题这个案例，其慢的根源不是“索引过滤效果差”，而是exists没被下推，导致子查询被反复执行。

按理来说，正常情况下，优化器会基于代价估算，自动把exists下推成semi join，但当前SQL里出现了NOREFED_EXISTS_SSS，说明优化器“本来想下推，但被拦住了”。

导致无法下推的原因可能有好几种：子查询本身有“硬伤”，比如NOT EXISTS、ROWNUM、聚合函数等；优化器参数被设置成了限制下推；统计信息不准确，导致优化器误判；SQL里有Hint干预。

我们需要回到SQL里逐一排查，子查询用的是exists，不是not exists；并且没有rownum、聚合函数、group by；关联条件是等值连接。

同时我们发现，SQL里带了/*+ enable_index_join(0) no_use_cvt_var */：

/*+*/是Hint的书写格式，是用于人为改变优化器决策的手段；enable_index_join(0)表示禁用索引连接，而索引连接是优化器把exists下推成semi join的一种方式。禁用了索引连接，优化器就没法通过索引连接下推exists，这就是导致无法下推的直接原因。

因此，该案例慢的根源是SQL里带了Hint enable_index_join(0)，禁用了索引连接，导致优化器无法把exists子查询下推成semi join，只能逐行执行子查询。同时，外层每读一行t，就要执行一次子查询，子查询里还有union去重，所以时间占比38.12%的SLCT2和24.74%的SSEK2都由此产生。

去掉Hint后，执行时间从8565ms至99ms，优化成功。

5.3总结

本案例是典型的“关联方式不对”类型。这种类型的慢SQL，特征通常是SQL里带了Hint，或者优化器估算偏差，导致选了不合适的关联方式。

具体表现为ET里SLCT2或HASH JOIN占比大；trace里能看到NOREFED_EXISTS_SSS、Hash Join中间结果巨大，或者本该走Nested Loop的走了Hash Join。优化思路是在trace里确认关联方式，然后回SQL看有没有Hint干预，或者是不是优化器估算偏差。

如果是Hint干扰，就去掉Hint；如果是优化器统计信息没有更新导致的计划错误则更新统计信息；同时，如果因为优化器的干扰信息太多导致的估算偏差，就需要人为用Hint固定关联方式或关联顺序。

6谓词未下放

6.1背景

谓词未下放，是指SQL里的过滤条件没有尽早应用到数据扫描阶段，而是等到回表之后才过滤。这种情况下，数据库会先扫描出大量数据，再回表，再过滤，导致扫描行数和回表行数都很大，查询变慢。

谓词未下放的常见原因是：索引列被函数包住、条件下推位置不当、子查询没有展开等。通过改写SQL，把谓词下推到索引扫描阶段，让过滤条件尽早生效，可以大幅减少扫描行数和回表次数，使查询速度从秒级降到毫秒级。

这里解释下什么叫谓词：

“谓词”是数据库里的一个术语，简单说就是用来过滤数据的条件。在SQL里，WHERE后面的条件就是“谓词”，每一个条件，都是一个谓词。谓词的作用是过滤数据，符合条件的行，保留；不符合条件的行则丢弃。在trace里，谓词通常对应：SLCT2过滤操作；scan_range索引扫描范围；或者SSEK2里的条件。

这里就要提到“谓词未下放”，“下放”指的是，把谓词尽早应用到数据扫描阶段，减少后续处理的数据量。例如我们这里的instr(m.org_path, n.org_path) > 0，因为被instr包住，不能在索引扫描阶段用上，只能在回表之后用，这种就是“未下放”。

谓词未下放，会导致扫描大量数据，回表大量数据。

6.2案例分析

同样的，拿到ET、trace以及SQL后，我们先看ET的内容：

我们看到回表操作占了91.79%，几乎所有的时间都花在了这一步上，所以我们重点分析这一操作。

BLKUP2 121659819 91.79% 1 31 646422 0 0 0 0 null null 0

从这句ET操作看到，对应着trace内容中的第31行，其trace内容如下：

#BLKUP2: [1, 144->89052326, 108]; IDX_TBG_ORGPATHTENIDSTATUS(T_BU_BUDGETORGANIZATION)

可以看到该操作回表了8900万行，涉及到的索引名是IDX_TBG_ORGPATHTENIDSTATUS，表名是T_BU_BUDGETORGANIZATION

由于回表行数非常多，且操作是无序的，因此确定这个案例慢的根本原因就在这一步。

那么为什么需要回表的行数这么多？

我们需要看trace中的前一步操作，其内容如下：

#SSEK2: [1, 144->89052326, 108]; scan_type(ASC), IDX_TBG_ORGPATHTENIDSTATUS(T_BU_BUDGETORGANIZATION), is_global(0), scan_range[(exp_cast(10001),exp_cast(0),min),(exp_cast(10001),exp_cast(0),max))

回表的前一步是进行了索引扫描操作，正是这一步的过滤效果不好，才使得后续回表耗时过多。通过scan_range操作，我们知道判断条件是两个等值扫描，最后一列进行了整列的范围扫描。

根据表名T_BU_BUDGETORGANIZATION，去SQL中找到对应的内容：

在SQL中有四处查询到该表名的存在，但是第一处没有Where条件，第二处、第四处都只有一个where条件内容，只有第三处才是我们要找的对应的内容，如下所示：

AND (

t.ORGID in (

SELECT m.org_id

FROM t_bu_budgetorganization m,

(SELECT org_id, org_path

FROM t_bu_budgetorganization

WHERE org_id IN ('bb27b9a506e44166a5cd495fc2e868e6')) n

WHERE instr(m.org_path, n.org_path) > 0

AND m.tenantid = 10001

AND m.status = 0

)

)

我们先分析这段SQL：

t_bu_budgetorganization是预算组织表，m.org_path即表示该表对应的“机构树”里，所有机构ID从根到当前机构的路径；同理，根据最里层select子查询我们知道，n.org_path代表机构ID为'bb27b9a506e44166a5cd495fc2e868e6'时的从根到当前机构的路径。

因此，instr(m.org_path, n.org_path) > 0则m.org_path包含n.org_path，即m对应的机构是n对应的机构的下属机构（或等于n本身）。

这段SQL的含义就是，在租户ID为10001、状态为0的机构中，找出目标机构（bb27b9a506e44166a5cd495fc2e868e6）的所有下属机构（或等于目标机构本身）。

这段SQL为什么过滤性差呢？

其实可以大概猜到是WHERE instr(m.org_path, n.org_path) > 0这一步出了问题，因为其他地方都是正常的条件判断和正常的投影操作，所以怀疑是这部分的索引没用好或者索引失效。

回看scan_range[(exp_cast(10001),exp_cast(0),min),(exp_cast(10001),exp_cast(0),max))，发现前两处条件分别对应着SQL里的m.tenantid = 10001和m.status = 0，最后一处范围扫描就是instr(m.org_path, n.org_path) > 0；同时根据索引名IDX_TBG_ORGPATHTENIDSTATUS也可以推理到正好是这三处的列名拼接得到的。

因此，这里就可以确定，本案例慢的根源是：instr(m.org_path, n.org_path) > 0这个谓词作用在org_path上，导致索引失效，优化器只能整列范围扫描org_path，加上tenantid、status的过滤性不够强，索引扫描输出8900万行，回表8900万行，所以慢。

因此，我们优化的思路就是，将该谓词下放，尽量让更少的行数进行回表。

通过前面对这段SQL的分析，我们知道它的业务目的是：找出目标机构（bb27b9a506e44166a5cd495fc2e868e6）的所有下属机构。

原SQL用instr(m.org_path, n.org_path) > 0来判断“某机构是否是另一机构的下属”，这个写法虽然能表达业务逻辑，但instr作用在org_path上，会导致索引失效，只能整列范围扫描;而t_bu_budgetorganization存的是机构树，机构表本身就是树形结构，每个机构都有ORG_ID、PARENT_ID、ORG_PATH等字段。

既然业务目的是“找出某机构的所有下属机构”，那么最自然的方式就是层次查询（START WITH ... CONNECT BY），可以直接沿着PARENT_ID往下找，比instr判断路径更直接、更高效。

因此，要找下属机构，应该用CONNECT BY PRIOR org_id = parent_id。正确的改写应该是：

t.ORGID in (

select tm.org_id

from T_BU_BUDGETORGANIZATION tm

start with tm.ORG_ID = 'bb27b9a506e44166a5cd495fc2e868e6'

connect by prior tm.ORG_ID = parent_id

and tm.status = 0

and tm.tenantid = 10001

)

注意：是CONNECT BY PRIOR tm.ORG_ID = parent_id，不是PRIOR parent_id = tm.ORG_ID。

改写后，用START WITH ... CONNECT BY层次查询，沿着PARENT_ID往下找下属机构，层次查询可以走IDX_BUDGETORG_PARENTID索引，不再用instr，即索引不会失效，进而使得过滤效果恢复，回表行数从8900万降到178，总耗时也随之从132769ms降到33ms。

这说明，谓词未下放时，过滤条件没有尽早生效，导致扫描大量数据；改写SQL，把instr改成层次查询，谓词在索引扫描阶段就用上了，回表行数大幅减少，性能提升约4000倍。

6.3总结

案例是典型的“谓词未下放”类型。这种类型的慢SQL，特征通常是过滤条件没有尽早应用到数据扫描阶段，而是等到回表之后才过滤。

具体表现为ET里BLKUP2回表行数巨大；trace里能看到过滤条件在SLCT2里，而SLCT2在BLKUP2的上层，说明是先回表后过滤。常见原因有：索引列被函数包住、条件下推位置不当、子查询没有展开等。

优化思路是通过ET找到瓶颈后再在trace中确认过滤条件和回表的先后顺序，然后回SQL定位到具体的写法问题，针对性改写，例如去掉索引列上的函数转换、把外层条件下推到里层、把exists下推成semi join，让过滤条件在索引扫描阶段生效，回表行数大幅减少，性能提升明显。

7慢SQL优化总结

本报告通过几种典型优化案例，总结了达梦数据库下慢SQL的常见类型、特征和优化思路。虽然这几种类型的问题本质和优化手段各不相同，但分析思路是一样的，都是先通过ET找到时间占比最大的操作，根据ET里的行号去trace定位执行计划，再根据trace里的表名、索引名去SQL定位命令，分析是索引问题、写法问题还是关联方式问题，最后针对性优化。

ET负责“哪一步最慢”，trace负责“这一步在干什么”，SQL负责“这段逻辑为什么这么写”，三份东西是从粗到细的关系。

优化SQL的手段虽然多样，但本质上可以归为几类：

（1）改写SQL：包括去掉索引列上的函数转换、合并重复子查询、子查询改JOIN、INSTR改层次查询等；

（2）索引选择干预：包括查已有索引、对比过滤性、强制指定过滤性更好的索引；

（3）缺索引：包括查已有索引、确认没有合适索引后新建索引；

（4）关联方式不对：包括去掉错误的Hint、用Hint固定关联方式或关联顺序、把EXISTS下推成SEMI JOIN；

（5）谓词未下放：包括去掉索引列上的函数转换、把谓词下推到索引扫描阶段、INSTR改层次查询等。

无论哪种手段，最终目的都是减少扫描行数和回表行数，降低I/O代价，使查询速度尽可能变快。

https://eco.dameng.com