问一个数据库循环读取信息的问题

cloud226

简单的说来就是

数据库里有一个主题
有A1, A2, A3等回复主题
有B1, B2, B3等回复A1
有C1, C2, C3等回复B1; C4,C5,C6等回复B2。。。。
。。。
。。。

反正就是一层一层都会回复

现在需要找到A1下面所有相关回复

如果用什么1000层loop 是可以解决问题 （因为我这里基本不可能有这么多层）

但想问一下有没有什么比较不这么暴力的解决方法

psaux

你每个回复主题都应该有parent_id吧，不考虑performance的情况下，用recursion就行了。

workinvm

你要在数据表的设计中加一个字段，表示当前路径，比如 A1/B1/C1 , A1/B2
然后查的时候 like 'A1*" 就OK了。
缺点是每次你的节点位置发生变化都要跟新一次这个路径，不过如果不是非常频繁的更新节点位置，应该问题不大。

cloud226

psaux 发表于 2013-5-31 12:13
你每个回复主题都应该有parent_id吧，不考虑performance的情况下，用recursion就行了。 ...

语言是Mysql 所以本身不提供recursive query

但google了一下 有蛮多alternatives的

这个好像不错
http://dba.stackexchange.com/que ... hout-ctes/7161#7161

感谢

cloud226

workinvm 发表于 2013-5-31 12:34
你要在数据表的设计中加一个字段，表示当前路径，比如 A1/B1/C1 , A1/B2
然后查的时候 like 'A1*" 就OK了。 ...

数据库preset了

否则我肯定会用类似的结构来简化

huaxianz

Recursive CTE(COMMON TABLE EXPRESSION)

只要是支持ANSI sql-99标准的产品都可以，虽然我不懂Mysql

Google一下就可以了，很简单

cloud226

根据上面的链接自己修改的 (例子里是从尾到头 我这个是从头到尾)

DELIMITER $$
DROP FUNCTION IF EXISTS GetChildren $$
CREATE FUNCTION GetChildren (GivenID INT) RETURNS VARCHAR(10240)
DETERMINISTIC
BEGIN
    DECLARE rv VARCHAR(10240);
    DECLARE cm CHAR(1);
    DECLARE ch BLOB;

    SET rv = '';
    SET cm = '';
    SET ch = CONCAT(GivenID);
   
    WHILE LENGTH(ch) > 0 DO
        SELECT IFNULL(qc,'') INTO ch
        FROM (SELECT GROUP_CONCAT(id) qc FROM discussionMessage WHERE find_in_set(parent_id, ch)) A;
        
        IF LENGTH(ch) > 0 THEN
            SET rv = CONCAT(rv,cm,ch);
            SET cm = ',';
        END IF;
    END WHILE;
   
    RETURN rv;
END $$
DELIMITER ;

cloud226

但这种非native support的recursive query好像特别慢啊？

Fernando

看上去效率是挺低的，说不定还没index
不支持connect by吗？

cloud226

Fernando 发表于 2013-5-31 19:22
看上去效率是挺低的，说不定还没index
不支持connect by吗？

MySQL 不支持recursive query

所以必须用粗暴的循环

cloud226

Fernando 发表于 2013-5-31 19:22
看上去效率是挺低的，说不定还没index
不支持connect by吗？

像这种function怎么做index啊？

北风

cloud226 发表于 2013-5-31 16:33
根据上面的链接自己修改的 (例子里是从尾到头 我这个是从头到尾)

DELIMITER $$

function会很慢
如果要经常调用，建议你把结果变成新的表

cloud226

北风 发表于 2013-6-4 14:55
function会很慢
如果要经常调用，建议你把结果变成新的表

每运行一次 cpu 100% 用时大概2秒 是不是不可忍受啊？

北风

cloud226 发表于 2013-6-4 14:59
每运行一次 cpu 100% 用时大概2秒 是不是不可忍受啊？

要看你是不是经常用

cloud226

北风 发表于 2013-6-4 15:03
要看你是不是经常用

网站最重要页面之一 基本上每个用户都会访问至少一次

但并不是每个页面都会耗时那么久 大部分没有那么深的层次 所以只是10%的耗时 但cpu每次都会达到100%

北风

cloud226 发表于 2013-6-4 15:11
网站最重要页面之一 基本上每个用户都会访问至少一次

但并不是每个页面都会耗时那么久 大部分没有那么深 ...

个人不会在这样的页面运行2秒的query的
你需要tuning或者甚至改变设计思路

lisabeth

鱼羊鲜

可以增加一个表么？
要么就干脆缓存一个序列化的路径的数组，或者直接分行的文本到文件里。

cloud226

鱼羊鲜 发表于 2013-6-4 15:42
可以增加一个表么？
要么就干脆缓存一个序列化的路径的数组，或者直接分行的文本到文件里。 ...

这个是随时变化的

所以每次产生新的回复 就在新旧两表里面同时加入数据？

鱼羊鲜

cloud226 发表于 2013-6-4 16:57
这个是随时变化的

所以每次产生新的回复 就在新旧两表里面同时加入数据？ ...

先把现有的所有回复整理成一个新的所谓的路径表，表结构就是

reply_id, path
A1        /
A2        /
A3        /
B1        /A1/
B2        /A1/
B3        /A1/
C1        /A1/B1/
C2        /A1/B1/
C3        /A1/B1/
C4        /A1/B2/
C5        /A1/B2/
C6        /A1/B2/

这个存到数据库也行，存为文本文件也行。

然后，每次回复，就新增一条数据到这个路径表即可，也可以更新，删除操作。

cloud226

鱼羊鲜 发表于 2013-6-4 17:10
先把现有的所有回复整理成一个新的所谓的路径表，表结构就是

reply_id, path

我理解你的意思：）

但你这个例子是从尾到头 因为有parent_id 即使是function也很快

如果是我需要的从头到尾

那每次有新回复比如E1回复了D1

那A1，B1，C1，D1都需要相应的更新 也是一个recursive query

而且这样maintain起来是一个噩梦 特别是如果有手动操作数据库的时候

Fernando

cloud226 发表于 2013-6-4 14:49
像这种function怎么做index啊？

只要你有select，就很可能需要index。具体要不要，看你怎么写

鱼羊鲜

cloud226 发表于 2013-6-4 18:16
我理解你的意思：）

但你这个例子是从尾到头 因为有parent_id 即使是function也很快

我怎么觉得只需要新增一条就可以了呢，不需要更新其他的啊

stevenbian

增加一个level的字段就可以动态组建SQL了.

cloud226

鱼羊鲜 发表于 2013-6-5 00:18
我怎么觉得只需要新增一条就可以了呢，不需要更新其他的啊

比如说我需要A1下面所有相关回复

如果按照你的例子 需要通过“搜索”所有和A1相关的row 才能得出想要的结果 这就有点违背新建表来直接读取数据的意义了 - A1的数据栏应该就是我需要的数据， B2的数据栏应该就是我需要的数据

这样的话表的内容只能是

A1                            B1，B2，C1，C2，C3，D1，D2。。。。
B1                            C1，C2，D1，D2，E1，E2。。。。
B2                            C3，D3，D4，E3，E4。。。。
C1                            D1，E1，E2，F4，F5。。。。
。。。。

这样比如新建一个F6 那理论上之前相关的所有数据都应该更新 这样才是为我的需求最优的存储吧

cloud226

stevenbian 发表于 2013-6-5 00:57
增加一个level的字段就可以动态组建SQL了.

你和Fernando说的都太专业了

我本身不是DBA 这些东西都不了解。。。

能不能详细说明你们这些有什么意义 大致如何操作（具体的东西 我可以google）

专业的术语请尽量用英文

jerryclark

http://www.codeproject.com/Artic ... e-using-nested-sets

cloud226

jerryclark 发表于 2013-6-5 11:14
http://www.codeproject.com/Articles/4155/Improve-hierarchy-performance-using-nested-sets

感谢分享

这种nested set是不是更适合固定的数据啊？

比如像我的例子 如果多了一个回复

如果是nested set 不仅和这个回复相关的left, right数字要改 甚至不相关的也全部要改啊 这比起存储楼上讨论的路径岂不是更加耗时耗力？

鱼羊鲜

cloud226 发表于 2013-6-5 10:43
比如说我需要A1下面所有相关回复

如果按照你的例子 需要通过“搜索”所有和A1相关的row 才能得出想要的 ...

这样存储很不合理。3楼已经说了，一个like就解决了，性能不会很差。

你要在数据表的设计中加一个字段，表示当前路径，比如 A1/B1/C1 , A1/B2
然后查的时候 like 'A1*" 就OK了。

stevenbian

cloud226 发表于 2013-6-5 10:46
你和Fernando说的都太专业了

我本身不是DBA 这些东西都不了解。。。

这和DBA没关系吧。
搞数据库开发的都懂呀。
专业论坛发言还要解释那么具体啊 ：-）

对这种典型的父子关系表，Oracle有专门的start with connect by直接组建一个查询。
如果mysql没有这功能的话，应该在表里增加一个level字段来表示层级关系，维护起来应该很方便。
比如A就是level 1 B回复A就是level 2 C回复B就是level 3
有了这个字段就可以知道多少个自身表连接在一起就可以用程序动态的组建一个大的查询语句查询到了。

优势就是相对于递归查询来说他就一个大查询，而不是几个小查询，感觉会快点。