MySQL存储过程与多个选择

时间:2022-10-18 21:52:23

Here's my scenario:

这是我的情景:

I've got a table of (let's call them) nodes. Primary key on each one is simply "node_id".

我有一个(让我们称之为)节点的表。每个主键都只是“node_id”。

I've got a table maintaining a hierarchy of nodes, with only two columns: parent_node_id and child_node_id.

我有一个维护节点层次结构的表,只有两列:parent_node_id和child_node_id。

The hierarchy is maintained in a separate table because nodes can have an N:N relationship. That is to say, one node can have multiple children, and multiple parents.

层次结构在单独的表中维护,因为节点可以具有N:N关系。也就是说,一个节点可以有多个子节点和多个父节点。

If I start with a node and want to get all of its ancestors (i.e. everything higher up the hierarchy), I could either do several selects, or do it all in one stored procedure.

如果我从一个节点开始并想要获得它的所有祖先(即层次结构中更高的一切),我可以做多次选择,或者在一个存储过程中完成所有操作。

Anyone with any practical experience with this question know which one is likely to have the best performance? I've read things online that recommend both ways.

任何对这个问题有实际经验的人都知道哪一个可能有最好的表现?我在线阅读了两种推荐方式。

3 个解决方案

#1


5  

"which one is likely to have the best performance? " : No one can know ! The only thing you can do is try both and MEASURE. That's sadly enough the main answer to all performance related questions... except in cases where you clearly have a O(n) difference between algorithms.

“哪一个可能有最好的表现?”:没有人能够知道!你唯一能做的就是尝试和MEASURE。令人遗憾的是,所有与性能相关的问题的主要答案......除非您在算法之间明显存在O(n)差异。

And, by the way, "multiple parents" does not make a hierarchy (otherwise I would recommend to read some books by Joe Celko) but a DAG (Direct Acyclic Graph) a much harder beast to tame...

顺便说一句,“多个父母”并不构成一个等级(否则我会建议阅读Joe Celko的一些书)但是DAG(直接无环图)是一个更难以驯服的野兽......

#2


1  

If performance is your concern, then that schema design is not going to work as well for you as others could.

如果您关注性能,那么该架构设计对您来说不会像其他人那样有效。

See More Trees & Hierarchies in SQL for more info.

有关详细信息,请参阅SQL中的更多树和层次结构。

#3


0  

I think a general statements could lead into problem, because it depends on how you your queries respectively the stored procedure make of usage of the indices. To make a helpful declaration it would be necessary to compare the SQL of your selects and the stored procedure.

我认为一般性陈述可能导致问题,因为它取决于您的查询分别是存储过程如何使用索引。要做一个有用的声明,有必要比较您的选择的SQL和存储过程。

#1


5  

"which one is likely to have the best performance? " : No one can know ! The only thing you can do is try both and MEASURE. That's sadly enough the main answer to all performance related questions... except in cases where you clearly have a O(n) difference between algorithms.

“哪一个可能有最好的表现?”:没有人能够知道!你唯一能做的就是尝试和MEASURE。令人遗憾的是,所有与性能相关的问题的主要答案......除非您在算法之间明显存在O(n)差异。

And, by the way, "multiple parents" does not make a hierarchy (otherwise I would recommend to read some books by Joe Celko) but a DAG (Direct Acyclic Graph) a much harder beast to tame...

顺便说一句,“多个父母”并不构成一个等级(否则我会建议阅读Joe Celko的一些书)但是DAG(直接无环图)是一个更难以驯服的野兽......

#2


1  

If performance is your concern, then that schema design is not going to work as well for you as others could.

如果您关注性能,那么该架构设计对您来说不会像其他人那样有效。

See More Trees & Hierarchies in SQL for more info.

有关详细信息,请参阅SQL中的更多树和层次结构。

#3


0  

I think a general statements could lead into problem, because it depends on how you your queries respectively the stored procedure make of usage of the indices. To make a helpful declaration it would be necessary to compare the SQL of your selects and the stored procedure.

我认为一般性陈述可能导致问题,因为它取决于您的查询分别是存储过程如何使用索引。要做一个有用的声明,有必要比较您的选择的SQL和存储过程。