zcn126的博客

本次想分享的是举一反三思路，null和or要思考之前or改写的案例，这样遇到同类场景就有优化的招数。还记得之前分享过形如（列 is null or 列等值）改写成nvl，此时利用举一反三思维，C1=1结果赋值为6，null结果赋值为6，其他还是c1，最终满足等于6的条件。最终其实就是求c1=5,1,null,然后null可以用nvl，最终。改成nvl(c1,6) in (1,5,6)首先语句主要的问题在于or条件。最近又遇到新鲜的语句写法。首先理解decode函数。

它只是判断主查询是否有相匹配的记录，因此exsits只做判断，如果转换成inner join，inner join是获取相匹配的记录，如果子查询结果有重复，与主查询做关联，那会重复输出结果，因此需要子查询去重才能成功转换成inner join。原因是因为exists中select存在主查询条件作为查询项得出结果后还要通过where去过滤，因此只能主查询一条一条去判断，主查询数据量增加，效率急剧下降。对于这种写法，主要思路是拆解，子查询能获取的先获取，然后找与主查询的关联关系关联主查询，最后做条件过滤。

引入临时表，将每次要操作的rowid值进行记录，并且能够精准限制其每次操作10w，解决之前表多次删除数据后，此时rowid这个值保留，插入新值没有继续使用rowid，使用的是递增的rowid，造成一些rowid空缺，就如1，2，3，2本身被删除后，原本根据rowid相减获取要操作数据是三条，但2空缺，实际只有2条。使用层次查询，查询SYSOBJECTS可以获取关于表层次信息，如果是分区表会将其子表一并查询出来，因此如果大于1意味着该表是分区表，等于1是普通表这样很好区分分区表和普通表。

计划做成nest loop semi join2（嵌套循环），相当于一条一条去模糊匹配筛选排除，由此性能消耗高。根据对语句的逻辑上理解，排除t2不在t1中的单位，且code的长度为4，“不在”可以转换成not exists，like关联转换成substr方式。关联列一旦先合并处理之后，和其他表做关联时做成like关联匹配，这样的效率会低，因此不宜合并处理。关联列一旦先合并处理之后，和其他表做关联时做成like关联匹配效率低，不宜这样处理。最近遇到这样有趣的写法。

这里语句意思是每次主查询的id传入到标量子查询中求出其层次查询中相关的id，再去求其文件数据量。因此每次都要一次一次作为变量进行层次查询再统计文件数据量，因此我会思考能否一次性处理，层次查询关系能否先求其根级（即其顶头上司）？文件能否先分类统计，然后二者之间再关联统计？层次查询也是先分类的思路，求根节点即把每个节点分组归类于根节点；文件分类统计就很明显的分组统计做法。最近在项目中遇到这样的一个场景，根据文件类型分类统计本级及其下级的文件总数量，即本级的统计的总数包含下级的数据，这里文件分类统计数量。

计划是全表扫，‘||‘拼接条件的写法无法使用索引，‘||’是拼接符，合并，相当于and,我们可以把条件做一下转换。‘||’拼接符相当于and，此时我们将拼接条件转换为and条件。其实in条件是or的逻辑运算，因此可以一步到位进行改写。效率提升几十倍以上。

exists中存在标量子查询作为查询项后作为过滤条件的写法，此写法一般会把标量子查询模块作为非相关查询表达式，会转化为一行一行处理，此做法比较低效，建议exists中的标量子查询改写为简单的表关联后条件过滤方式。在子查询中套用标量子查询生成的查询项又作为过滤条件，优化器对于此等情况会做成一行一行处理，效率较为低下，建议是改为简单的表关联过滤处理。最近在项目中遇到以下这种写法。

EXISTS 是一个用于判断子查询是否返回至少一行数据的逻辑运算符，只关心子查询是否有返回行，而不关心返回的具体数据。这里存在exists中，按照理论来讲，exists只做存在判断，性能会比inner join更快，在exists中inner join的结果集对主查询没有影响，因此我们也可以转换成exists。这里的计划会把exists做成index join，当转换成内连接时，exists会隐性去重，（即distinct操作符），去重之后再关联，减少关联次数提升性能。公众号：小周的工作笔记。

项目中做报表统计时，经常使用union al合并放到一个视图里，然而为了方便总是喜欢使用select * ，导致消耗不必要的性能。这里T.*,字节长度292，这里的select T.*是冗余的，直接查询select count(1)即可。好习惯的养成，要查询真实的列，避免select *消耗不必要的性能。这里的字节长度为8，从原来的2.8s下降至0.191s。于是我们把select T.*去掉。为了便于报表统计，创建以下视图。

项目上接到业务是这样的，一个分区表5千万，然后要根据时间条件去分批更新数据。这里我们可以看到ctime条件套用函数，结果计划中只能用索引全扫描，一般我们更希望条件上不要套用函数，这样可以用到普通索引。时间条件我们总是习惯在上面套用函数，这样可能导致用不上普通索引。要养成习惯，不要在时间条件上套用函数。改写后从原来的4.5s变成0.013s。

索引组织表：有且仅有一个聚簇索引键，数据按照聚簇索引键排序，所以数据是有序的，插入也是有序的。项目中一般情况下主键是非聚集索引，因此rowid是有序且是聚集索引，因此有些大表dml操作可以借用rowid去批量做来提升性能。

该语句最终是获取去重后rn=1（即每组第一行）的数据，而我们分析到t13表数据量很多，这里简单的关联后再去重，性能消耗较多，而优化思路是先t11表和t12表先过滤条件和去重之后，以及t13表去重过滤后，两个小的结果集进行关联，这样性能会提升。如果有了先过滤后关联的思维，大部分语句的性能会获得提升。最终性能提升了几倍，在项目中的数据较多，原本语句也复杂，改写后从原来跑20分钟，到最终秒级执行完。我们一般要先过滤出小结果集再关联，相当于我们先分组减去重复的部分，减少对比次数，从而提升效率。

这个语句实际上并不需要用到union，因为t1.c2=t2.c1这一个条件排除了t1.c2 为空值的情况，而t2中先对c1去重（distinct）也避免了产生重复值，最后求的是t1.*包含了表的主键，主键具有唯一性，因此原本逻辑上不会产生重复值，这样就不用使用union 对结果集去重了。而UNION ALL则对两个结果集进行并集操作，包括重复行，即所有结果全部显示，不管是否重复。union有对结果集去重的效果，如果结果集的查询项中列长较长，那么所用的时间也会增加，因此，慎用union。

也印证了我们前面解释的not in的概述。主查询id是主键，因此已经可以知道主查询结果集最多为1行，此时改成not exists，意味着与子查询执行一次，可想而知效率大大提升。not exists: 通常使用关联子查询，这意味着子查询对于主查询的每一行都会执行一次（或者数据库优化器可能会将其转换为连接操作）。not in: 数据库通常会执行子查询，将结果集物化（除非优化器能够优化），然后进行主查询和子查询结果的比较。对于主查询数据量较小的，not exists的效率往往会比not in高。

首先获取到0301部门且是C1、C2、C3、C4、C5的证书记录，并且按员工、证书分组记录，再按员工分组记录他获取证书情况，然后再求获取5个证书的员工，再和员工表进行关联。TEST1表记录员工数据，现在需要筛选0301部门同时获取到C1、C2、C3、C4、C5这五个证书的员工。（2）再分组，先按员工和证书分组，再按员工分组；总体来讲，先过滤筛选数据，先分组后关联，当整理成一个满足需求再关联另一张表，这样的方式会更加高效。（1） 先过滤数据，获取满足0301部门和证书C1、C2、C3、C4、C5的集合;

回顾前面分享的改写，大概可以用一张图做个年终小结改写的思维逻辑，我个人的理解是首先对语句的理解，理解后找到平替高效的方法去实现。通过前面的经验，可以快速知道or关联可以考虑用union all去替换实现，left join和标量子查询之间的转换，like关联可以考虑用substr平替。如果还是没找到合适的改写方法，那么可以想想从业务上去改写，比如前面讲过计算每个人超过他自己分数的人数，可以理解为自己的排名，这样就可以改写成排序。我们可能知道了如何改写？

这里的in子查询+or+in子查询做成表达式，无法先过滤，这里in子查询里面的条件过滤性较好，可以利用索引先过滤，之前的文章中有提过or条件可以改写成union all，这里可以考虑改写成union all。or条件过滤性很好就比较适合做union all，这个例子也是对前面or关联案例的补充。这里能够很好的利用索引先过滤，性能提升几十倍。

1、 等价转换instr：字符串查找函数substr: 字符串截取函数LIKE:模糊匹配其三者之间的转换2、 问题语句这里问题在于驱动表为t2，index join次数100万次，前面说过与substr和instr可以等价转换，这里想要转换驱动表，那么就需要用substr。3、 改写这里与预期的一样驱动表变为t1表，index join次数降下来，另外注意的是，susbtr获取定长的情况下才做index join，因此这里结合业务可以知道是获取4个字节长度。

or关联无论是把or拆分还是整体做，并不高效，在优化中我们一般是把or用union all去做等价替代。这里有几点要注意（1） 下面情况不等价commit;commit;commit;AA AA AAAA BB BBCC AA AA不等价于union all) t1AA AA AABB BB BBAA AA AA。

tt2.id is null说明是要判断没有在tt2表里面的数据，而ll列重复数据较多，如果没有先过滤少量的数据再关联，中间结果集就会过多引起性能问题。根据前面的推断，先和过滤性较好的c2列关联减少关联数据量，再和ll列关联优化处理。)和(select ll from tt2 group by ll)减少了left join关联的左右表结果集，从而提升性能。原始语句问题在于tt.ll=t2.ll重复值很多，中间结果集tt1表数据量。tt2表数据量，因此想办法减少关联前左右表结果集数据量，这里(

因此要解决语句性能问题，就要解决case when这一部分问题。Case when作为条件判断，如果将每一分支做成一个集合，最后汇总结果集，可以用union all去实现。Like关联做成笛卡尔积，右表利用不上索引，每获取驱动表一条记录，都要到右表扫描state=1的结果集，扫描右表范围比较大，每次都要case when判断，性能消耗高。like case when一般是拆解处理，分支拆分，能够让其利用索引，原始语句wcode上是case when计算，所以无法使用索引造成性能耗时。

语句中的性能问题在于like case when做成笛卡尔积。case when用于条件判断，语句用业务角度理解，假设ll是层级的意思，第一（基层）层级就获取本级别的数据；第二层级有权限获取下级的数据，做like关联获取本级及下级数据。最终展示两个层级的所有数据，就是层级1+层级2数据，因此从这个角度分析可以用union all来改写。

最近项目中遇到这样有趣的例子，原本是希望外面的where条件能过滤出少量数据然后再和set 里面的表关联获取少量数据,这里可以看到计划的第5行做成hash join，实际上没有达到我们预期的效果。这个计划和上面解释的一样符合我们的预期，外层tt2.c1条件过滤出少量的数据后下推给tt3.c3过滤出少量数据再和tt1表关联获取相关数据更新。一般情况下merge into处理表关联和子查询下推上更加高效。Merge into 中on (tt2.c1=t3.c1) 能够更好处理条件传递达到性能提升。

语句中可以分析到t2表存储着使用记录，使用一次就记录一次，t1表是有效的设备信息，再根据业务需求分析，这里主要是要从有效的信息中获取未使用的设备信息，t1表总数是有效的，T2表中没有t1表使用记录的设备数据就是未使用的。那么整个需求主要依赖于t1表，t2表作用是获取未使用的记录。在项目中遇到这样的一个需求，从设备信息表和设备使用记录表中统计每个单位有效的设备信息和未被使用过的设备数量，开发商做成了full join方式，下面模拟一下场景，t1表是设备信息表，t2表是设备使用记录表，code是单位code。

我们可以把它具象化，t1表记录的是用户信息，c1是编号，t2表记录着打卡信息，早上打卡中午下班打卡，下午上班打卡，傍晚下班打卡，求出当前正常打卡人数。像语句这样子，对t2表全扫描与t1表进行关联求数据，性能优化更希望先过滤数据后少量数据进行关联。我们可以把逻辑重新调整，先把t2表打卡信息进行分组汇总后再与t1表关联获取数据。这里改写后计划有两个地方变化，首先是先过滤>=4的数据再关联，然后关联从hash join变成index join，相当于t2表变瘦后与t1表关联，这样一来关联计算减少从而性能提升。

这个语句标量子查询查询的是test2表，主查询中又有test2表，且标量子查询中关联关系和主查询的test2进行关联，所不同的在于查询条件c4的值。整个语句需求逻辑：主查询test2表和test1表进行内连接筛选满足条件的，然后根据test2表的c4分类汇总c1列。主要是语句的逻辑上存在重复计算，解决的核心在于如何减少冗余计算，通过对语句逻辑理解，利用case when来处理。根据case when的理解，使用其可以根据c4表判断汇总c1列，从而消除标量子查询的计算。

语句求count时，我们从left join的理解中可以知道，返回的是左表数据以及右表满足的数据，如果右表关联列没有重复值，数据量其实求的是左表的结果数据量，因此达梦数据库计划中直接去掉右表，而求左表的全部数据就是求counter，就迅速返回结果。语句的右表(select sum(c2) sumc2,c1 from t2 group by c1) t2与t1表关联的列是c1,已经做分组去重，是具有唯一性，求的是每组c1对应的c2总和（sum）相当于。标量子查询：子查询返回的是单一值的标量。

从语句中可以看到主查询查询的是标量子查询为0，就是计数为0的数据相当于t2不存在t3表的数据。语句表示不存在的语法是not exists，那么我们可以将语句改写成查t2不存在于t3表，然后和主查询关联。这里是从语句逻辑分析出发，用更优的方式去取得想要的数据。原语句中需要计算t3表所有数据，整体没有过滤性较好的条件，此时表关联查询会比标量子查询方式效率更高。最耗时在PRJT2，说明在查询项中，seq列是对应计划第9行，我们看看第9行是哪里的查询项，发现是t2表中的查询项，那么可以知道是标量子查询耗时。

于是我开始对语句进行解读，两张表进行关联，其中关联条件中出现and t2.id=(select top 1 id from t2 where t1.id=t2.b_id order by itime desc)，这里面的t1.id=t2.b_id是t1和t2的关联关系，然后求t2的top 1 id，相当于每组的t2.b_id中获取第一个b_id,也就是分组去重，那就好办了，可以让t2表按b_id分组去重再与t1关联。改写思路就是先分组后关联，一次性分组去重后再和t1关联，从而大大减少计算量。

前面大体上讲诉了单表查询的一些常规优化思路和改写。现做一个小结。

计划中可以看到对表扫描两次,而从结果中可以明白是对group by总计，那我们其实可以改写成将前面20行进行sum。我们有时候在项目上看到group by rollup用法，其实就是对group by分组进行合计。这里将temp做成临时表，然后只扫描表t1一次，达到优化效果。从计划中解读亦是如此，另外可以从结果上进行分析。第21行的count其实就是前面20行的总和。这里主要利用的优化思维就是减少扫描次数。

这样的语句主查询越大，即（test0824表数据量越大）查询越慢，这里相当于每次主查询查出一条数据，就要和子查询比较a.score>b.score，子查询有多少条数据就比较多少次，实际上的效率就是主查询条数。最近遇到一个有趣的优化问题，本身的需求是统计每个人超过他自己分数的人数，并显示自身分数情况。这个需求到底是什么意思，实际上就是分数排名，这里我们可以用到rank()over()进行处理。（2）当拿到一个需求，应该合理分析需求，寻求更快地效率去实现，而非直译。排完序后减1就是计算超过分数的人数。

（1） 通配符在后面，如a like ‘L’||’%’,这种情况大部分是可以用索引去优化，打开LIKE_OPT_FLAG参数可以优化成a>=‘L’ AND a <‘M’。可以用LIKE ‘张%’去实现。通过例子验证，数据库在做模糊匹配的时候，尽量选择通配符在后面的情况，这样like匹配的效率更高。（3） 前后都有通配符，这种情况在匹配数据时候也是需要扫描全表数据后才能匹配到数据。（2） 通配符在前面，这种情况在匹配数据时候需要扫描全表数据后才能匹配到数据。1、使用like一般建议通配符在后。

对于where中只带有分区列，此时相当于查询分区子表的数据量，我们也是希望能够利用上counter来提升性能。这里可以看到，当开启counter属性时，执行计划时FAGR2，能够快速返回数据量。当我们开启counter维护表上的行数时，在做count时可以快速获取到表的数据量。在with counter情况下：alter table T1 with counter;达梦数据库中表上有个with counter属性，用来维护当前表内的行数。从下面的例子，我们可以真实感受一下counter优化的效果。

当我们遇到同一列or is null时，首先判断它是否可以过滤少量数据，可以的话就使用索引优化，另外optimizer_or_nbexp是or条件的优化参数。那其实它可以是任何值，或者可以不是任何值，所以它不能比较（=，<,>），对于缺失值可以用nvl赋予实际值便于比较计算。这不仅仅是数值上的0、布尔值上的false或字符串上的空字符串（“”），而是一种特殊的"空"状态。该语句or条件是同一列，但计划对表扫描两次，且可以看到使用索引后回表较大，这个计划的生成主要是受两个因素的影响。OR 表达式的优化方式。

大表分页查询优化思考