继承关系存储树状结构数据--查询优化

继承关系存储树状结构数据--查询优化

关于树状结构数据在关系型数据库中的存储，上一篇博文：《树状结构数据库表设计》   中有过介绍和讨论，也给出了查询方法。但关于采用继承关系存储时，写的查询函数查询效率很低，数据在5000条时遍历出某个节点的全部父辈节点时，需要耗时2分钟。这个查询的节点在第十一层，并不深，而且数据量也不大，这个耗时是不能接受的。

         这是上一篇博文中提到的查询函数：

BEGIN
DECLARE sParentList varchar(1000); 
DECLARE sParentTemp varchar(1000);
SET sParentTemp =cast(rootId as CHAR);  
WHILE sParentTemp is not NULL DO 
IF (sParentList is not null) THEN 
SET sParentList = concat(sParentTemp,',',sParentList);
ELSE 
SET sParentList = concat(sParentTemp);
END IF; 
SELECT GROUP_CONCAT(parent_dir_id) INTO sParentTemp FROM tb_directory where FIND_IN_SET(dir_id,sParentTemp)>0; 
END WHILE; 
RETURN sParentList; 
END

查询语句：select * from tb_directory wherefind_in_set(dir_id,getParentListTest(4208));

上面的查询函数中，是将查询结果父id拼接到sParentTemp中，而实际中我们用到的树结构中的节点只有一个父节点，这里我就将查询函数修改了。如下：

BEGIN
DECLARE sParentList varchar(1000); 
DECLARE sParentTemp INT;
SET sParentTemp =rootId;  
WHILE sParentTemp !=0 DO 
IF (sParentList is not null) THEN 
SET sParentList = concat(sParentTemp,',',sParentList);
ELSE 
SET sParentList = concat(sParentTemp);
END IF; 
SELECT parent_dir_id INTO sParentTemp FROM tb_directory where dir_id=sParentTemp; 
END WHILE; 
RETURN sParentList; 
END

查询语句：select * from tb_directory wherefind_in_set(dir_id,getParentList(4208));

测试了下查询时间，在上述同样的数据环境下，遍历出同一节点全部父辈节点，上述查询耗时1s以内，这个就完全可以接受了。

疑问：

         分开查询：我使用如下两条语句执行，查询速度都很快

                   selectgetParentListTest(4208) into @temp;

                   select * from tb_directorywhere find_in_set(dir_id,@temp);

简单查了下关于sql的查询原理，还是没太弄明白原因。初步觉的应该跟where的搜索方式有关。