MYSQL中递归存储过程获取分层数据的性能 [英] Performance of recursive stored procedures in MYSQL to get hierarchical data

问题描述

我有餐桌员工，
员工 ( emp_id int主键， emp_name varchar(50)， mngr_id int)

I have table employee like,
employee ( emp_id int primary key, emp_name varchar(50), mngr_id int)

，此处mngr_id将为null或包含有效的emp_id.这样就形成了组织中员工的层次结构.

and here mngr_id would either null or contain valid emp_id. This way it form the hierarchy of employees in the organization.

为了遍历整个层次结构，我必须编写递归存储过程. (在Oracle中，使用CONNECT BY .. START WITH很容易)

In order to traverse the entire hierarchy I had to write the recursive stored procedure. (in Oracle it's easy by using CONNECT BY .. START WITH)

所以问题是，如果层次结构的级别不超过10个级别，那么这种存储过程对性能的影响是什么！

So the question is that what is the performance impact of such stored procedure given that the level of hierarchy would not go beyond 10 levels !

还有其他方法可以实现相同目标吗?

Is there any other way to achieve the same ?

推荐答案

一个相当简单的迭代邻接表数据库服务器端解决方案: http://pastie.org/1056977

a fairly simple iterative adjacency list db server side solution: http://pastie.org/1056977

delimiter ;

drop procedure if exists employee_hier;

delimiter #

create procedure employee_hier
(
in p_emp_id smallint unsigned
)
begin

declare p_done tinyint unsigned default(0);
declare p_depth smallint unsigned default(0);

create temporary table hier(
 boss_id smallint unsigned, 
 emp_id smallint unsigned, 
 depth smallint unsigned
)engine = memory;

insert into hier values (null, p_emp_id, p_depth);

/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */

create temporary table emps engine=memory select * from hier;

while p_done <> 1 do

    if exists( select 1 from employee e inner join hier on e.boss_id = hier.emp_id and hier.depth = p_depth) then

        insert into hier select e.boss_id, e.emp_id, p_depth + 1 
            from employee e inner join emps on e.boss_id = emps.emp_id and emps.depth = p_depth;

        set p_depth = p_depth + 1;          

        truncate table emps;
        insert into emps select * from hier where depth = p_depth;

    else
        set p_done = 1;
    end if;

end while;

select 
 e.emp_id,
 e.name as emp_name,
 b.emp_id as boss_emp_id,
 b.name as boss_name,
 hier.depth
from 
 hier
inner join employee e on hier.emp_id = e.emp_id
inner join employee b on hier.boss_id = b.emp_id;

drop temporary table if exists hier;
drop temporary table if exists emps;

end #

delimiter ;


call employee_hier(1);
call employee_hier(3);

这篇关于MYSQL中递归存储过程获取分层数据的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！