MySQL最佳实践:SELECT子级尽可能地递归执行? [英] MySQL best practice: SELECT children recursive as performant as possible?

问题描述

我想选择一个根项目，它是尽可能多的孩子.我更喜欢使用嵌套集模型，但是这次表结构遵循邻接模型. 有关嵌套集和邻接模型的更多信息.

I'd like to select a root item and it's children as much performant as possible. I prefer using the nested sets model, but this time the table structure is following the adjacency model. More about nested sets and adjancency model.

我有一个dependencies-table和一个items-table.

相关性表

dependency_id | item_id | child_id 
            1 |       1 |        4
            2 |       2 |        5
            3 |       4 |        7
            4 |       7 |        3
            5 |       9 |        3
            6 |       1 |        2

项目表

item_id | name   | info
      1 | Item A | 1st Item
      2 | Item D | 2nd Item
      3 | Item C | 3rd Item
      4 | Item D | 4th Item
      5 | Item E | 5th Item
      6 | Item F | 6th Item

SQL，请先尝试

# selecting children (non-recursive)
# result: 4, 2
SELECT 
   child_id AS id 
  FROM `dependencies_table`
 WHERE item_id = 1

我需要这个SELECT递归.

I need this SELECT recursive.

所需的输出

# children of item #1
dependency_id | item_id | child_id
            1 |       1 |        4 // 1st level
            6 |       1 |        2 // 1st level
            2 |       2 |        5 // 2nd level, 1->2->5

这种情况应该很常见，但是我想知道我现在找不到最佳实践.请注意:它是MySQL，因此我无法使用CTE ！

This case should be very common, but I'm wondering I couldn't find a best-practice for now. Please note: it's MySQL, so I'm not able using CTE!

您将如何解决此问题?预先感谢！

How would you solve this problem? Thanks in advance!

我找到了

I found an interesting thread, but my problem isn't solved yet. So, please don't close this Question.

这是一个有趣的PHP解决方案，但不幸的是，这并不是我真正想要的

Edit 2: Here's an interesting PHP solution, but unfortunately not what I actually want.

推荐答案

作为NoSQL Person，我不得不说那是什么图形.但是，是的，我明白了..有使用SQL的原因，但是这个特定的例子并不是这些数据库的目的，特别是当您可以拥有n个子级时，MySQL的执行速度会非常慢，实际上有一个查询为此，即使是n级，但这也有些疯狂. (如果我没记错的话，大约有42个内部联接)

As a NoSQL Person i have to say thats what graphs are for. But yeah i get it..there are reasons for using SQL, but this particular example is just not what these Databases are made for, especially when you can have n level of children mysql is going to perform extremly slow, there is actually a query for this, even for n levels, but thats some crazy shit. (something about 42 Inner Joins if i remember correctly)

是的，您想获取表并在php中做子级的事情.

So Yeah, you want to fetch the tables and do the children stuff in php.

这里是获取完整的依赖表后如何在php中获得结果的方法，

Here is how you get your result in php once you have fetched the entire Dependencies Table,

$dep = array();
$dep[] = array('item_id' =>'1', 'child_id' =>'4');
$dep[] = array('item_id' =>'2', 'child_id' =>'5');
$dep[] = array('item_id' =>'4', 'child_id' =>'7');
$dep[] = array('item_id' =>'7', 'child_id' =>'3');
$dep[] = array('item_id' =>'9', 'child_id' =>'3');
$dep[] = array('item_id' =>'1', 'child_id' =>'2');

function getchilds($dependencies, $id) {

    $ref = array();
    foreach($dependencies as $dep){
        $item_id = $dep['item_id'];
        $child = $dep['child_id'];
        if(!is_array($ref[$item_id])) $ref[$item_id] = array('id' => $item_id);
        if(!is_array($ref[$child])) $ref[$child] = array('id' => $child);
        $ref[$item_id]['children'][] = &$ref[$child];
    }
    return $ref[$id];
}

getchilds($dep,1);

这使用引用仅对每个项目进行一次检查，我无法对任何性能更高的图像进行成像，并且它适用于无数个级别. 实际上，我敢打赌，这比固定级别的SQL查询要快.

This uses References to go through every item only once, i cant image anything more performant and it works for infinite number of levels. Actually i bet this is faster than any SQL Query for fixed number of levels.

第一个项目基本上会给您

for the first item this will basically give you

1 - 2 - 5
 \ 
  4 - 7 - 3

这篇关于MySQL最佳实践:SELECT子级尽可能地递归执行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！