MYSQL查询树表某个根节点的子树 - alvin_010的博客 - CSDN博客

安宇雨 - 随手采集
2019-11-22 11:58:49
随手采集
0000-未整理-等待研究

前言：项目开发的过程中我留意到了其中一个页面的树的查询十分的慢，用户体验较差，于是便去看看原因。

发现这里查找树的sql语句是这样的：

select * from dept where find_in_set(id,getDeptChildList(1))

getDeptChildList是函数，内容如下（navicat）：
参数：rootId VARCHAR(1000)
`BEGIN
DECLARE sChildList mediumtext;
DECLARE sChildTemp mediumtext;
SET sChildTemp =CAST(rootId AS CHAR);
WHILE sChildTemp IS NOT NULL DO
IF (sChildList IS NOT NULL) THEN
SET sChildList = CONCAT(sChildList,',',sChildTemp);
ELSE
SET sChildList = CONCAT(sChildTemp);
END IF;
SELECT GROUP_CONCAT(id) INTO sChildTemp FROMdeptWHERE FIND_IN_SET(parent_id,sChildTemp)>0 and state='T';
END WHILE;
RETURN sChildList;
END`

由于mysql函数是不能返回结果集的，所以这里写了个函数查找id=1根节点其下的子树id字符串（用，分割），然后使用mysql的find_in_set函数进行查询。getDeptChildList函数直接运行的速度并不算慢，试运行几次，查询时间在50~60ms。

运行情况：
[SQL]SELECT * FROM dept WHERE FIND_IN_SET(id,getDeptChildList(1))
受影响的行: 0
时间: 18.690s

关键在于find_in_set函数，查询过慢—–总数据约700条，耗时15~18s。这函数其实就是in操作符，当in操作数据太多时，效率是十分低下的。

于是这里我弃用了find_in_set这种方法，使用了临时表来查询子树。

临时表将在你连接MySQL期间存在。当你断开时，MySQL将自动删除表并释放所用的空间。当然你可以在仍然连接的时候删除表并释放空间。

重写了一个mysql存储过程来获取子树并储存在临时表：getDListTable

（注：GROUP_CONCAT函数一开始坑了一下，“在MySQL配置文件(my.ini)中默认无该配置项，使用默认值时，值为1024，可在客户端修改/修改方法”，因为我的表使用uuid，字段很长，1024不够用。）

参数是：rootId VARCHAR(1000)

BEGIN   
      DECLARE sChildTemp MEDIUMTEXT;
      /*存在同名临时表就删除*/
      DROP TEMPORARY TABLE IF EXISTS table_tmp;
      /*创建临时表*/
      create temporary table table_tmp (id varchar(100),name varchar(100),parent_id varchar(100),layer varchar(10),type varchar(10),state varchar(2));
      SET sChildTemp = rootId ;
      /*存在子树则继续循环*/
      WHILE sChildTemp IS NOT NULL DO
    /*将子树记录插入临时表*/
        insert into table_tmp (id,name,parent_id,layer,type,state) select d.id,d.name,d.parent_id,d.layer,d.type,d.state from dept d WHERE FIND_IN_SET(d.id,sChildTemp)>0 and d.state!='F'; 
    /*查询子id*/           
        SELECT GROUP_CONCAT(id) INTO sChildTemp FROM `dept` WHERE FIND_IN_SET(parent_id,sChildTemp)>0 and state!='F';

      END WHILE;
END

sql查询语句则是：

call getDListTable(1);
select * from table_tmp;

查询时间为100ms~180ms，可见查询是大大被优化的。
运行情况：查询时间 0.172s
[SQL]CALL getDListTable(1);
受影响的行: 1
时间: 0.083s

[SQL]
SELECT * from table_tmp

受影响的行: 0
时间: 0.089s

存储过程中的循环可替换成递归，我也测试过然而似乎（0.0，有可能递归的姿势不对，有待研究）查询速度比直接循环慢一些
pro_cre_childlist：参数是：rootId VARCHAR(1000)：

BEGIN
    DECLARE done INT DEFAULT 0;   
    DECLARE sChildTemp MEDIUMTEXT;   
    DECLARE cur1 CURSOR FOR SELECT id FROM dept WHERE parent_id=rootId and state!='F';   
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;   
    SET max_sp_recursion_depth=12;   
    insert into table_tmp(id, name,parent_id,layer,type,state) select d.id,d.name,d.parent_id,d.layer,d.type,d.state from dept d WHERE d.id=rootId;
      OPEN cur1;       
      FETCH cur1 INTO sChildTemp;   
      WHILE done=0 DO   
              CALL pro_cre_childlist(sChildTemp);   
              FETCH cur1 INTO sChildTemp;   
      END WHILE;   
      CLOSE cur1;   
END

sql语句：

DROP TEMPORARY TABLE IF EXISTS table_tmp;

create temporary table table_tmp 
(id varchar(100),name varchar(100),parent_id varchar(100),layer varchar(10),type varchar(10),state varchar(2));

CALL pro_cre_childlist(1);

SELECT * from table_tmp;

运行情况：查询总时间1.047s
[SQL]DROP TEMPORARY TABLE IF EXISTS table_tmp;
受影响的行: 0
时间: 0.043s

[SQL]
create temporary table table_tmp
(id varchar(100),name varchar(100),parent_id varchar(100),layer varchar(10),type varchar(10),state varchar(2));
受影响的行: 0
时间: 0.028s

[SQL]
CALL pro_cre_childlist(1);
受影响的行: 0
时间: 0.887s

[SQL]
SELECT * from table_tmp

受影响的行: 0
时间: 0.089s

参考网址：http://blog.csdn.net/mchdba/article/details/39277301

Original url: Access
Created at: 2019-11-22 11:58:49
Category: default
Tags: none

未标明原创文章均为采集，版权归作者所有，转载无需和我联系，请注明原出处，南摩阿彌陀佛，知识，不只知道，要得到

请先后发表评论