mysql 树目录查询语句优化，提高查询效率

╰+哭是因爲堅強的太久メ 2022-04-25 09:28 881阅读 0赞

在做mysql树目录查询的时候遇到了一个很奇妙的现象  
我是先创建了一个树目录查询的函数叫 `getDeptList()`  
然后单独执行查询函数的效率很快  
![树目录函数][20190531082042911.png]  
![时间][20190531082147706.png]  
但当我放到sql语句里加个判断执行就会很慢了  
![执行语句][20190531082415693.png]  
![时间][20190531082438714.png]  
累计查询出20条数据竟然要了0.5秒，那如果查出几百条不就1秒了 -\_-||  
后来想了一下可能是每次查询self\_dept表的时候都会再执行一遍树目录的函数  
然后就把sql语句改成了这样  
![sql][]  
![速度][20190531082708722.png]  
可以看到，速度大幅度降低直接到了0.06  
这里的优化思路是先计算好函数的结果，让函数只执行一遍，而不是根据self\_dept表的判断进行多次计算

这里顺便记录一下mysql树目录排序的方法

sql:

select dept_id from (
        select t1.dept_id,
        if((find_in_set(parent_id, @pids) > 0 or find_in_set(dept_id, @pids) > 0), @pids := concat(@pids, ',', dept_id), 0) as ischild
        from (
            select dept_id,parent_id,dept_name from basis_sys_dept order by parent_id, dept_id
             ) t1,
    			(select @pids := id) t2
    		) t3 where ischild != 0

这个是可以直接写在sql语句内的，不过有个缺点：完全依靠排序，如果有子级在父级上面就会搜不到

sql:

delimiter // 
    CREATE FUNCTION `getLst`(rootId INT) 
     RETURNS varchar(1000) 
     BEGIN 
       DECLARE sTemp VARCHAR(1000); 
      DECLARE sTempChd VARCHAR(1000); 
     
      SET sTemp = '$'; 
      SET sTempChd =cast(rootId as CHAR); 
    
      WHILE sTempChd is not null DO 
        SET sTemp = concat(sTemp,',',sTempChd); 
        SELECT group_concat(dept_id) INTO sTempChd FROM self_dept where FIND_IN_SET(parent_id,sTempChd)>0; 
      END WHILE; 
      RETURN sTemp; 
    END 
    //

这个是在mysql里创建一个函数，可以暂无发现什么bug（如有发现bug我会回来修改的）

（在复制使用的时候记得修改里面的表名和表字段 我的表：self\_dept，id：dept\_id，父级id：parent\_id）

[20190531082042911.png]: /images/20220125/90a558e644d4460fae0d215b31a46cdb.png
[20190531082147706.png]: /images/20220125/72e0bf2a216949719a839257e2162c96.png
[20190531082415693.png]: /images/20220125/af495e27bdf14e9bac4c21d8cc18699c.png
[20190531082438714.png]: /images/20220125/06855b7d4b9a44a1b248cc6d0bdf39f9.png
[sql]: /images/20220125/c80a505ab30740fd80176bf5fe207caf.png
[20190531082708722.png]: /images/20220125/c4b94ad375cc46ceb7b027e1395fd23a.png