求教如何优化这条SQL语句?

liujiduo 发布于 2015/02/13 09:28
阅读 547
收藏 2

SQL语句如下:

SELECT USER_NAME, PASSWORD, NICK_NAME FROM T_APP_USERS WHERE FIND_IN_SET(MERCHANT_ID, getChildList(getParentRoot(1)));

情况是这样的:我有一张商户表T_MERCHANT,每个商户可以有子商户,子商户下面还可以有子商户,所以T_MERCHANT是用树状结构存储数据的。getParentRoot和getChildList都是自定义函数,getParentRoot(MERCHANT_ID)是获取指定商户的根结点,getChildList(getParentRoot(MERCHANT_ID))是查询根结点下所有子节点,上面SQL语句的目的是查询某一个商户(可能是子商户)的总商户下所有子商户里的用户数据现在问题来了,我的T_APP_USERS 目前还不到3w条数据,现在执行一次上面的SQL竟然要15s以上的时间,有时甚至20s才反应过来。。。简直无法忍受!而我测试了一下,如果不加WHERE条件查询或者只执行WHERE里的条件语句,都只要不到1s的时间,但两个结合起来为什么就这么慢呢?

我猜想应该是Mysql在匹配每一条记录的时候都执行了一次getChildList(getParentRoot(1)),而getParentRoot和getChildList的实现都是通过while循环做的,不好再做优化了。理论上Mysql应该对WHERE条件语句进行优化,只需要执行一次getChildList(getParentRoot(1))就缓存起来的啊???

现在数据量这么小就已经慢成这样了,请教各位我该怎么解决这个问题啊?

以下是问题补充:

@liujiduo:用临时变量解决了! SELECT USER_NAME, PASSWORD, NICK_NAME FROM T_APP_USERS, (SELECT(@ids := getChildList(getParentRoot(4)))) tmp WHERE FIND_IN_SET(MERCHANT_ID ,@ids); 测试了一下,耗时从15s直降到0.06s,谢谢各位! (2015/02/13 14:41)
加载中
0
甘薯
甘薯

要解决这个问题不难吧,

使用临时变量.把getChildList(getParentRoot(1))放到临时变量里再在where中使用.

liujiduo
liujiduo
对,就是这样,请问该怎么用啊?
0
我已经报警了
我已经报警了
贴出你的ddl还有两个函数的定义
0
如痴拉风
如痴拉风
估计你嵌套查询耗时,表结构和函数拿出来大家分析下
0
hlevel
hlevel

tree 结构我给你条语句可以试试


DROP FUNCTION IF EXISTS queryChildrenDicInfo;
CREATE FUNCTION `queryChildrenDicInfo` (dId INT)
RETURNS VARCHAR(4000)
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);

SET sTemp = '$';
SET sTempChd = cast(dId as char);

WHILE sTempChd is not NULL DO
SET sTemp = CONCAT(sTemp,',',sTempChd);
SELECT group_concat(id) INTO sTempChd FROM c_dict where FIND_IN_SET(pid,sTempChd)>0;
END WHILE;
return sTemp;
END;


select queryChildrenDicInfo(1);
select * from c_dict where FIND_IN_SET(id, queryChildrenDicInfo(1));


c_dict 结构是这样的

id    pid   name

1     -1     aaa

2     1      bbb

3     2      ccc

4     2      ddd




0
maradona
maradona

可以冗余一个新的字段,每个商户都有一个code,最顶层的比如是001  下一层是 001001

下下一层 001001001 之类的,这个code的生成规则就由你自己定咯,增删改查和移动之类的操作都需要你自己考虑,查询的时候查某个商户的子商户就直接 like '001%' 之类的  建上索引 这种查询是比较快的

或许俺没理解你所说的...

maradona
maradona
回复 @liujiduo : 就怕数据再多点就撑不住了
liujiduo
liujiduo
你说的这种设计固然可行,但我现在修改表结构是不太现实了,我只是希望能找到一种办法让Mysql对WHERE条件里的表达式做一下缓存,不要每次匹配都执行一次,那样的话效率就能提升不少了。
0
huan
huan
这种树形结构可以用左右值存储的方式,网上查一下就行了。
返回顶部
顶部