问题描述

       星空列表查询，使用单据上的分录号排序，如果没有日期范围的过滤条件，可能导致语句无法执行出来结果。

l  问题SQL

l  执行计划

    执行计划中t_AR_receivableEntry采用聚集索引，然后使用fseq字段进行排序，占了整个计划的绝大部分成本。

    排序操作的详情

    然后再对sort的结果进行Table Spool，从IO统计中可以看到产生了Worktable。

    可以看到由于排序和Table Spool带来的Worktable的逻辑读达到了69429876次，一次读操作是8kb，那么总共带来的IO为(69429876*8/1024/1024)=529GB，即这个操作产生的中间表的数据量达到529GB，最后导致耗时376秒。

说明：（Worktable： worktable是一个内部的关系表，用于存储中间结果集。关系引擎在执行 order by、group by或者union操作时，会创建Worktable）

l  检查索引定义情况

    发现fseq字段非索引字段，对fseq字段增加索引后(idx_fseq)，执行计划如下

    可以看到，执行计划使用到了新增加的索引，但需要从t_AR_receivableEntry中循环获取表中的其他字段，其循环的次数非常大。

    idx_fseq中满足的行数为5101120条，每个执行估计的行为5052680行，循环次数，将达到5101120*5052680=25774332054280次，单纯这个操作耗时就已经占整个计划的89%。也就是耗时的主要成本。

    说明这样增加索引是无法解决问题的。由于表的数据量非常庞大，嵌套循环的次数，导致循环次数太高，耗费大量的CPU时间，从而导致需要非常长的时间。

    如果机器的配置稍差，如tempdb说在的磁盘IO能力不够，CPU的处理能力不够，这个语句可能将无法执行出结果，并且导致其他相关的SQL查询也变慢。

解决方式

• 由于是嵌套循环成本过高而引起的问题，我们可以考虑使用hash的关联方式来避免，因为按照过滤的条件，属于全表扫描。在SQL语句最后，加上option(hash join)指定走哈希连接方式。

    执行计划发生了改变，T_BD_MATERIAL和T_AR_RECEIVABLEENTRY表右关联后，跟T_AR_RECEIVABLE关联，但总共的开销成本在整个计划中只有3%左右。

     从HASH Match可以看到，Hash键是以T_BD_MATERIAL的FMATERIAL字段，而这个字段属于主键，所以hash key生成的成本并不高，而FMATERIAL在T_AR_RECEIVABLEENTRY为索引字段，所以关联的总共成本并不高。

    整个执行时间花费28秒左右，比采用嵌套连接有明显改善。

• 由于排序使用了t0.fid和t1.fseq，t1表也有fid，可以考虑在t1表上，增加组合索引fid,fseq。由于t1.fid=t0.fid，执行引擎对T1表会自动fid和fseq进行排序，由于只有两个字段，并且存储在索引表中，所以排序的成本将极大减少

    使用组合索引后，可以看到数据量，极大减少，说明IO得到明显下降，只需要593毫秒。

总结

       基于列表查询的功能，如果选择了按单据分录的行号排序时，出现执行时间很长或者无法返回结果后，可以比对是否跟上述的写法类型，如果是，需要在表体上增加fid和fseq为组合索引。但如果排序的条件不相同，还是需要具体问题具体分析。

       另外需要注意，如果两个表的比较的字段非常多的时候，这种组合索引也将带来大量的IO开销，不一定能解决问题，这个时候，如果不改写SQL写法，可能需要采用option(hash join)来解决问题。