sql - 关闭表 root 节点查询性能,以 10s 个节点为

  显示原文与译文双语对照的内容
0 0

我目前有一个关闭表,用于分层数据,它的中有 5万个节点,结果在闭包表中。 使用 SQLite,由于关闭表的大小,我的查询时间呈指数增长。

CREATE TABLE `Closure` (`Ancestor` INTEGER NOT NULL, `Descendant` INTEGER NOT NULL, `Depth` INTEGER, PRIMARY KEY (`Ancestor`,`Descendant`) )
CREATE INDEX `Closure_AncestorDescendant` ON `Closure` (`Ancestor` ASC, `Descendant` ASC);
CREATE INDEX `Closure_DescendantAncestor` ON `Closure` (`Descendant` ASC, `Ancestor` ASC);
CREATE TABLE `Nodes` (`Node` INTEGER PRIMARY KEY NOT NULL, `Root` BOOLEAN NOT NULL, `Descendants` INTEGER NOT NULL);

我的查询查找根的节点需要大约 20分钟,尽管只有 5个或者 6个节点满足查询。

SELECT `Closure`.`Ancestor` FROM `Closure` 
LEFT OUTER JOIN `Closure` AS `Anc` ON `Anc`.`Descendant` = `Closure`.`Descendant` 
AND `Anc`.`Ancestor` <> `Closure`.`Ancestor` WHERE `Anc`.`Ancestor` IS NULL;

20分钟就是现在,如果节点是 root 并修改 Nodes,我就存储一个布尔值。 当节点移动时的Root 列。 我对重复的数据不太满意,但查询时间现在在每个查询的单个数字毫秒内。

我还有很多查询需要知道给定节点有多少个子节点。 我每次需要它,但是在一个巨大的数据库中,查询似乎需要很长的( 超过 1秒) 。 Descendants 列,每次移动节点时也会更新。 不幸的是,这是我想避免的数据重复。

我使用的查询如下所示。 如果有人能解释如何提高这个( 考虑到我已经有一个从祖先开始的索引)的性能,我很感激。

SELECT COUNT(*) FROM `Closure` WHERE `Ancestor`=@Node
时间:原作者:3个回答

0 0

你正在开发的SQLite版本是否支持外键? 如果是这样,闭包表设计应该有一个用于引用包含闭包表的层次结构表的oab 。 在T-SQL中:

constraint fk_a FOREIGN KEY (ancestor) REFERENCES <hierarchy_tablename> (nodeid)
constraint fk_d FOREIGN KEY (descendant) REFERENCES <hierarchy_tablename> (nodeid)

你将不得不查找相关的SQLite语法,抱歉。

由于你已经经维护了深度字段,这是子代和它的祖先之间的距离,可以以用它来描述一个节点。

select top 1 'EXPANDABLE' as whatever
from closure C
where exists (select ancestor from closure where depth> 0 and ancestor = C.ancestor)
and ancestor = @Node

无论关闭表的大小如何,它都应该相当快。 如果你得到了一个空的集合,那么你的给定节点就不能再展开了,因为它没有子节点。 Exists只在找到符合你条件的实例时返回 true,并且你只在第1 行中执行,所以你不为所传递的表中的每个行返回一行。

要提高查找根的性能,请尝试以下类似的内容。 这是我用来查找根的,但是我的闭包表只有 ~200,000 行。 我比较了为每个生成的计划,你的代码使用散列,这可能会影响由于设备( 我在这里假设sql埚是用于 iphone/ipad或者者sometype的小型分布式设备) 上的处理器需求而造成的性能。 下面的处理功能较少,更多的从索引中读取,并使用层次结构中的层次结构关系。 我不能确定它会改善你的性能灾难,但值得一试。

select a.node_name, a.node_id
from test.hier a left outer join 
 (select coo.descendant/* coo = CHILD OF OTHER */
 from test.closure_tree coo right outer join test.closure_tree ro
 on coo.ancestor <> ro.descendant/* ignore its self reference */
 and coo.descendant = ro.descendant/* belongs to another node besides itself */)lo 
 on a.node_id = lo.descendant
where lo.descendant is null
原作者:
...