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

Question

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

3

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

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

假设主数据看起来像这样:

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

理解了吗？如何查询这些数据并且每个匹配的主记录只返回一行，但包含所有相关的主数据信息。我已经搜索并找到了几个示例，但它们在我们的数据上执行时间太长。我根据之前提到的巨大表中的现有数据创建了一个测试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';

再次强调，我们的目标是能够以Master中列的形式检索MasterData中的所有数据。这种操作是否可行？

非常感谢您的帮助！

- mrceolla

我更喜欢第二个查询而不是第一个（虽然不使用隐式连接语法），但我宁愿不使用像你所做的EAV表（存在许多固有问题）。1500列实在太多了。这些数据的范围是什么？您可能拥有更多的多列表，而不是您到目前为止展示的内容。 - Clockwork-Muse

你能具体说明使用场景吗？我真的很想知道这种方法应该解决哪个问题。 - PepperBob

2

那不是一个规范化的设计，你应该知道。它也不高效，也不容易查询。 - HLGEM

在21世纪，您不应该使用隐式连接。它们是SQL反模式。 - HLGEM

"规范化"。我认为它的意思不是你想象的那样。 - Mike Sherrill 'Cat Recall'

显示剩余2条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Mike Sherrill 'Cat Recall' · Accepted Answer

我们的目标是将MasterData中的所有数据作为Master中的一列检索出来，就像它本身就是Master中的一列。这个目标实现起来在严格意义上可能是可能的，但从实际角度来看，这是不太可能的。即使在最好的情况下（仅有一两个属性），性能也极差；而在可能的情况下（约 30 至 500 个属性），您可能会彻底拖垮服务器。

“规范化”并不意味着“创建第二个表，该表将为初始表中存在的每个列/行创建记录”。它甚至不意味着任何类似的内容。但是规范化实际上可能解决您的问题。（根据我的经验，大多数数据库问题都是结构性问题。）

您提出的解决方案并不能很好地解决您未说明的问题。要充分利用StackOverflow上的专业知识，请说明您试图解决的问题以及您尝试过的解决方案。有关数据库规范化的维基百科文章如果您从这样的表开始……

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);

如果您的所有属性都是布尔类型，那么您可以通过简单的查询获取所有物品的所有属性。

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

应用程序代码可以非常简单地循环执行。您可能能够删除所有属性值为false的行。

这种结构允许每个事物具有无限数量的属性。但是，您需要在一个单独的行中返回任意数量的属性，并且尽量减少对应用程序代码的更改。这些要求必须进行更改。没有其他方法。

如果您的表包含以下行...

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)

就目前而言，规范化仍然是长期维护和性能的最佳选择。这种结构（如上所示）并未经过规范化；使用大量数据时性能通常较差。（PostgreSQL带有可选的hstore模块，可能比MySQL更好。）