炫“库”行动-人大金仓有奖征文-KES层次查询调优实例

[本文正在参与炫“库”行动-人大金仓有奖征文]

活动地址：https://marketing.csdn.net/p/98bd30353e7cb998b6070a89e8b91edb

最近研究了一下KES的层次查询机制，对于具有层次关系的数据结构来说，层次查询是一个很实用的功能，而且恰好对一个带有ORDER SIBLINGS BY的层次查询SQL语句进行了一次SQL调优，现在把调优的思路和过程分享一下。

什么是层次查询？

在分享调优过程之前，我们先简单说一下什么是层次查询（Hierarchical Query），它是一种针对分层（树状）数据的查询，通过它能够将分层数据按照层次关系查询出来，返回给客户端。这种关系在我们的日常生活中十分常见，比如：

公司组织架构中：主任->组长->组员的关系

它的语法为：

SELECT [level], column, expr.. FROM table [WHERE condition] [ START WITH start_expression ]

CONNECT BY [NOCYCLE] { PRIOR child_expr = parent_expr }

[ ORDER SIBLINGS BY …] [ ORDER BY … ]...

其中：

START WITH后面指定的是根节点

CONNECT BY后面指定的是如何确定父节点和子节点的关系

ORDER SIBLINGS BY是说按照同一层级来进行排序

我们先来举一个例子理解ORDER SIBLINGS BY的含义，假设表中数据有如下层次关系：

如果此时我们对这一列使用ORDER SIBLINGS BY（默认升序排列），它的结果为（注意B和C，F和G的顺序）：A B D F G C E

如果此时用ORDER SIBLINGS BY DESC做降序排列，它的结果为（注意B和C，F和G的顺序）：A C E B D G F

下面我们来看一个实例，首先创建表test_tree：

其中id列的表示自己的id，而pid列表示其上层节点的id用来做层次关系，sort列是该行在本层的序号，最顶层的pid为0

现在我们使用PLSQL插入一些层次化的数据：

现在我们表里的数据一共是：20 + 20*30 + 20*30*50 = 30620条记录

共三个层次，它们的关系如下图所示（以id为1的做图示）：

 

 

数据准备完毕，我们来找一下id为1-1的所有的子节点，SQL语句：

SELECT t.id, LEVEL lv FROM test_tree t START WITH t.id = '1-1' CONNECT BY PRIOR t.id = t.pid ORDER  SIBLINGS BY t.Sort;

此时我们先看一下初始的执行计划和时间：

 

共1604ms左右，可以看到主要的时间花在了Hash Join上，而Hash Join的主要时间花在了Sort+SeqScan上，如果要节省运行时间，那么应该怎么调优呢？

我们继续看执行计划，Sort + SeqScan这个组合，如果有一个执行节点可以替代这个两个组合节点那么时间一定会更快，而index scan就可以替代掉这个组合，因此我们为sort列创建一个索引来试一下：

 观察建立索引后的执行计划，发现对比第一个执行计划，Sort+SeqScan这个组合节点被index scan替换掉了，并且现在时间缩短为1213ms

继续思考，在KES中有一个特性叫做index Only scan，它跟普通的index scan的区别是普通的index scan是通过索引去实际表中找到被索引的数据，而index Only scan是在索引文件中保存所索引的数据，因此会节省去实际表拿数据的IO，那么我们可否利用index Only scan来进一步调优呢？

顺着这个思路，我们要搞清楚，输出的列有哪些，然后在这些列上建立索引，以便应用index Only scan，我们先用verbose来打出所需要的列：

 

 我们发现需要id, sort, pid这三列，因此我们为它们创建索引并继续观察执行计划：

 

 

我们发现在为id, sort, pid这三列创建了索引后，index Only scan被选择了，从而节省了更多的时间，最终为754ms左右，对比一开始的1604ms，速度提升了53%左右

以上就是本次KES层次查询的SQL调优的思路和过程，希望对大家有所帮助。

[本文正在参与炫“库”行动-人大金仓有奖征文]

活动地址：https://marketing.csdn.net/p/98bd30353e7cb998b6070a89e8b91edb