情况
我们的系统有一个MySQL数据库,其中有一个product表,其参数不能修改。
为了添加可选的额外参数,开发人员设计了一个键值对表,它看起来有点像这样:
+----+--------------------+----------+------------+
| Id | key | value | product_id |
+----+--------------------+----------+------------+
| 1 | provider_id | 3 | 2 |
| 2 | appliable_discount | 20% | 2 |
| 3 | pay_mode | pre-pay | 2 |
| 4 | pay_mode | post-pay | 3 |
| 5 | appliable_discount | 15% | 3 |
| 6 | provider_id | 4 | 4 |
| 7 | provider_id | 3 | 5 |
| 8 | expires | 1 | 2 |
| 9 | expires | 0 | 4 |
| 10 | color | red | 2 |
| 11 | color | blue | 4 |
| 12 | foo | 1 | 2 |
+----+--------------------+----------+------------+钥匙,产品_id对是唯一的。同一产品的同一密钥不可能有两个不同的值。
value列上的更改很少。一旦设定好了,它们通常会保持原样。
在我们的应用程序的一个部分中,有一个用于提取所有产品的查询和如下所示的额外参数的子集:
SELECT p.*, sp.value AS hours, cp.value AS appliable_discount, cp2.value AS pay_mode, cp3.value AS provider_id, cp4.value AS expires, cp5.value AS foo, cp6.value AS bar, cp7.value AS etc
FROM products AS p
LEFT JOIN product_extra_parameters AS cp ON cp.product_id = p.id AND cp.key = 'appliable_discount'
LEFT JOIN product_extra_parameters AS cp2 ON cp2.product_id = p.id AND cp2.key = 'pay_mode'
LEFT JOIN product_extra_parameters AS cp3 ON cp3.product_id = p.id AND cp3.key = 'provider_id'
LEFT JOIN product_extra_parameters AS cp4 ON cp4.product_id = p.id AND cp4.key = 'expires'
LEFT JOIN product_extra_parameters AS cp5 ON cp5.product_id = p.id AND cp5.key = 'foo'
LEFT JOIN product_extra_parameters AS cp6 ON cp6.product_id = p.id AND cp6.key = 'bar'
LEFT JOIN product_extra_parameters AS cp6 ON cp7.product_id = p.id AND cp7.key = 'etc'
WHERE p.id > 1正如您可能想象的那样,这个查询的性能非常低,我们正在努力改进它。我们怀疑滥用左联接是主要问题。
注意:对于这个问题的效果,忽略它正在获取所有产品的事实。
短期内:如果存在,则采用新的策略来查询此表,以获得性能更好的相同信息。
长期:将这些信息存储在关系数据库中的更好策略,或者缓存这些信息以获得更好的性能。
由于24小时前的信息是可以接受的,所以可以使用“cron用键更新表:产品_id,name:所有额外的json_参数”行的解决方案。
编辑:对于长期的解决方案,我在这个堆栈溢出问题上找到了一些有价值的见解:https://stackoverflow.com/questions/2909448/best-pattern-for-storing-product-attributes-in-sql-server
发布于 2018-06-26 11:55:31
只需要获取所有参数并在应用程序端执行任何过滤逻辑如何?它将在给定的查询中将联接切割为单个连接,但您必须处理应用程序中的名称-值分配,而不是查询(这是一个优点,实际上,它更快、更容易)。
属性表的想法已经过时了(但这并不意味着每一代都不会重新发明它),但是基于数据库查询中的逻辑实现只是一个非常糟糕的想法。
发布于 2018-06-27 03:32:14
这是关系数据库的一个相对常见的问题,它会为每个参数/值添加令人讨厌的列。
一种解决方案是使用数据透视查询。在MySQL中,不幸的支点是不可能的。但您可以使用case语句。
另一个选项是保存或中的键值对。大多数db可以索引和查询这些。相当有效率。但是,如果其他用户需要使用这些值来查询/报告,则可能会发现需要使用这些属性。
发布于 2018-06-26 14:43:53
正如您可能想象的那样,这个查询的性能非常低,我们正在努力改进它。我们怀疑滥用左联接是主要问题。
在"product_extra_parameters“表上有什么索引?
联接的扩散,不管是左边的还是其他的,都不应该是一个主要的问题,除非这个表格的大小很大。
https://softwareengineering.stackexchange.com/questions/373159
复制相似问题