MySQL检索两级分层数据的最优方法

Question

MySQL on SO 中似乎不乏分层数据问题，但似乎他们主要讨论的是在数据库中管理此类数据或实际检索递归分层数据。我的情况两者都不是。我有一个需要显示的项目网格。每个项目还可以有 0 个或多个与其关联的评论。现在，该项目及其数据以及属于该项目的任何评论都显示在网格中。通常需要某种向下钻取、对话框或其他用户操作来查看网格项的子数据，但在这种情况下，我们在同一网格中显示父数据和子数据。可能不符合事实上的标准，但事实就是如此。

现在，注释是通过单独的 MySQL 查询为网格中的每个父项检索的。当我意识到单页加载必须运行所有完全独立的数据库查询时，我立即感到畏缩。我还没有进行过分析，但如果这是我们有时看到的缓慢页面加载的一部分，我不会太惊讶。理想情况下，我希望将其减少到一个或两个查询。但是，我很难想出一个听起来比当前正在做的更好的解决方案。

我的第一个想法是使用某种分隔符（如“|”）来展平每行的注释子项然后在渲染页面时在 PHP 中将它们分解回去。问题是，它变得越来越复杂，必须分隔注释中的每个字段，然后分隔每个注释，然后考虑数据中分隔符的可能性。只是感觉维护和调试很混乱。

我的下一个想法是保留项目注释的外部连接，并仅考虑 PHP 中的项目重复项。我正在使用 Codeigniter 的数据库库，该库返回数据库数据的 PHP 数组。这听起来像是结果数组中可能存在大量重复数据，这可能会对较大的结果集造成系统负担。我认为在大多数情况下这不会太糟糕，所以这个选项目前位于我的可能性列表的顶部。理想情况下，如果我正确理解 MVC，我应该将数据库、业务逻辑和视图/显示尽可能分开。因此，理想情况下，模型返回的数据中不应存在任何明显的数据库“怪癖”（因为缺乏更好的词）。也就是说，无论从该模型方法调用什么数据，都不应该关心这样的重复数据。因此，我必须添加一个额外的循环，以某种方式消除重复的项目数组条目，但前提是我检索了所有子注释并将它们放入自己的数组中。

两个查询是另一个想法，但随后我必须在 SQL 语句中传递大量项目 ID 以获取注释，然后在 PHP 中手动检查所有数据并将其压缩在一起。

我的目标不是停止在这里工作，但我希望有一些我还没有想到的更优化的（资源密集程度较低且对编码人员来说不那么混乱）方法。

Answer 1

正如您在问题中所述，使用联接会带回大量重复信息。在 PHP 中删除它应该很简单，但为什么首先要把它带回来呢？

使用从项目列表的查询中检索到的 ID 列表来编译 SQL 语句应该不是问题（请参阅 cwallenpoole的答案）。或者，您可以创建一个子查询，以便 MySQL 为您重新创建 ID 列表 - 这取决于子查询的密集程度。

选择您的项目：

SELECT * FROM item WHERE description = 'Item 1';

然后选择这些项目的评论：

SELECT * FROM comment WHERE item_id IN (
    SELECT id FROM item WHERE description = 'Item 1'
);

Answer 2

在大多数情况下，我使用某种 ORM 延迟加载来解决此类问题系统，但看起来你没有这个选项。

您是否考虑过：

1. 选择所有顶级项目。
2. 根据顶级集中的 ID 选择所有二级项目。
3. 在 PHP 中将 2 中检索到的对象与 1 中找到的项目关联起来。

基本上（在伪代码中）

$stmt = $pdo->query("SELECT ID /*columns*/ FROM ENTRIES");
$entries = array();
foreach( $row as $stmt->fetchAll(PDO::FETCH_ASSOC) ) 
{
   $row['child-entities'] = array();
   $entries[$row['id']] = $row;
}

$ids = implode(',',array_keys($entries));
$stmt = $pdo->query("SELECT PARENT_ID  /*columns*/ FROM children WHERE PARENT_ID IN ($ids)");

foreach( $row as $stmt->fetchAll(PDO::FETCH_ASSOC) ) 
{
   $entries[$row['parent_pid']]['child-entities'][] = $row;
}

$entries 现在将是一个关联数组，其中父项直接与子项关联。除非需要递归，否则这应该是两个查询中的所有内容。