mysql - 如何正确查询规范化数据库

Question

我正在重新设计一个 MySQL 数据库，该数据库包含一个包含大约 1,500 列的表以及其他表。我们希望通过创建第二个表来规范该表中的数据，该表将为初始表中存在的每一列/行都有一条记录。我们称这些表为 Master 和 MasterData。 Master 将包含该表中所有记录所需的基本信息。 MasterData 将包含与 Master 表中的记录有关的一些附加数据的值。所以假设 Master 看起来像这样:

MasterID     Property1     Property2
1            Yes           No
2            No            Yes
3            Yes           Yes
4            No            No

假设 MasterData 看起来像这样:

MasterID     Property     Value
1            Property3    Yes
1            Property4    No
3            Property3    No
4            Property7    Yes

到现在为止？我如何查询此数据并且每个匹配的主行仅返回一行但包含所有相关的 MasterData 信息。我搜索并找到了几个示例，但它们对我们的数据执行时间太长。我已经根据前面提到的一个巨大表中的现有数据创建了一个测试 MasterData 表。这导致 MasterData 有大约 450 万条记录，并且以下查询执行时间太长并超时。

SELECT Property1, Property2, Master.MasterID,
    GROUP_CONCAT(case when Property = "Property3" then Value end) as Property3, 
    GROUP_CONCAT(case when Property = "Property7" then Value end) as Property7
FROM Master LEFT JOIN MasterData USING (MasterID) GROUP BY MasterID
HAVING Property3='Yes' OR Property7='Yes';

或

Select * FROM Master AS M, MasterData AS MD1, MasterData AS MD2 
WHERE M.MasterID=MD1.MasterID AND MD1.Property='Property3' AND MD1.Value='Yes' 
AND M.MasterID=MD2.MasterID AND MD2.VAR='Property7' AND MD2.Value='Yes';

同样，我们的目标是能够在一行中检索 MasterData 中的所有数据，就像它是 Master 中的一列一样。这可能吗？

非常感谢任何帮助!

Answer 1

Again, our goal is to be able to retrieve all data in MasterData in one row as if it were a column in Master. Is this possible?

在不完全理解您的目标的情况下，我会大胆地说，严格来说，这可能可能。但从任何实际意义上讲，这都不太可能。即使在最好的情况下(只有一两个属性)，性能也可能很糟糕；在可能的情况下(在 30 到 500 个属性之间)您可能会完全关闭服务器。

规范化 并不意味着“创建第二个表，该表将包含初始表中存在的每一列/行的记录”。它甚至没有任何意义像那样。但规范化实际上可以解决您的问题，这可能。 (根据我的经验，大多数数据库问题都是结构性的。)

您在此处提出的解决方案对于您未提及的问题效果不佳。要充分利用 StackOverflow 上的专业知识，请说明您要解决的问题以及您尝试过的解决方案。

Wikipedia article about database normalization

---

如果您从这样的表格开始。 . .

create table master_data (
  master_id integer not null,
  property_name varchar(30) not null,
  property_value boolean not null default true,
  primary key (master_id, property_name)
);

insert into master_data values
(1, 'Property3', true),
(1, 'Property4', false),
(3, 'Property3', false),
(4, 'Property7', true);

. . .然后您可以通过简单的查询获取所有事物的所有属性。 (假设您的所有属性都是 bool 值。)

select * 
from master_data
order by master_id, property_name
--
1   Property3   t
1   Property4   f
3   Property3   f
4   Property7   t

应用程序代码可以非常简单地遍历它。您也许能够删除 property_value 为 false 的所有行。

此结构允许每个事物具有无限数量的属性。但是您的要求是 a) 在一行中返回任意数量的属性，并且 b) 必须对应用程序代码进行最少的更改。没有办法解决这个问题。

---

如果您的表包含这些行。 . .

insert into master_data values
(1, 'Property3', true),
(1, 'Property4', false),
(3, 'Property3', false),
(4, 'Property7', true),
(1, 'Property7', true);

这是获取一组符合条件的“事物”并将该集合连接到主数据表的方法。

select md.* 
from master_data md
inner join (select master_id
            from master_data
            where (
              (property_name = 'Property3' and property_value = true) or
              (property_name = 'Property7' and property_value = true)
            )
            group by master_id 
            having count(*) = 2 ) cd
  on (md.master_id = cd.master_id)

就其值(value)而言，规范化可能仍然是长期维护和性能的最佳选择。这种结构(上图)没有归一化；大型数据集的性能通常很差。 (带有可选 hstore 模块的 PostgreSQL 在这方面可能比 MySQL 更好。)