MySQL优化将键值对过滤为记录 [英] MySQL optimization on filtering key-value pairs as records

问题描述

我有一个数据库结构，该结构旨在以易于扩展的方式存储特定对象的属性.
有一个对象"表.

I've got a DB structure that is designed to store attributes for specific objects in an easily extendable manner.
There is an "Objects" table.

+----+----------------------
| id | ....(name, type, etc)
+----+----------------------

接下来，我有一个属性"表.

Next, I have an "Attributes" table.

+----+------+
| id | Name |
+----+------+

最后，还有一个关系"表，用于将所有数据作为具有相应值的属性-对象对(作为主键)保存.

And finally, a "Relations" table, used to keep all the data as the attribute-object pairs (as a primary key) with the corresponding values.

+--------+---------+-------+
| id_obj | id_attr | value |
+--------+---------+-------+

我需要一次获取满足多个条件的对象的ID.例如，我有一个名为类型"和城市"的属性，我需要获取这些属性的对应值为公寓"和城市b"的对象的ID.

I need to fetch IDs for objects that meet several conditions at once. For example, I have attributes named "Type" and "City", and I need to fetch IDs for the objects where the corresponding values for these attributes are "Apartment" and "City b".

自昨天以来，我把头撞在墙上之后，我设法提出了最好的解决方案(好吧，关于此查询的唯一好处是它实际上有效并获取了所需的记录):

The best solution I managed to come up with after banging my head against the wall since yesterday (well, the only good thing about this query is that it actually works and fetches the records needed):

SELECT objects.id
FROM (attributes INNER JOIN relations ON attributes.id = relations.id_attr) 
  INNER JOIN objects ON relations.id_obj = objects.id
WHERE objects.id
IN (
 SELECT objects.id
 FROM (attributes INNER JOIN relations ON attributes.id = relations.id_attr) 
  INNER JOIN objects ON relations.id_obj = objects.id
 WHERE attributes.name = 'Type' AND relations.value = 'Apartment'
)
AND objects.id
IN (
 SELECT objects.id
 FROM (attributes INNER JOIN relations ON attributes.id = relations.id_attr) 
  INNER JOIN objects ON relations.id_obj = objects.id
 WHERE attributes.name = 'City' AND relations.value = 'City b'
)
GROUP BY objects.id ASC
LIMIT 0 , 20

问题是，存储的数据量可能会变得有些大，而且我担心所有这些子查询(可能需要指定一些10-15个过滤器)，每个子查询都解析整个数据库，可能会导致严重的问题.性能问题(并不是说，即使我的SQL技能有限，我也可以肯定必须有一种更好的方法来完成我需要做的事情).
另一方面，对数据库进行大幅更改并不是真正的选择，因为与其一起使用的代码在很大程度上取决于当前的数据库结构.
有没有一种方法可以在单个查询中以有限的数量或不更改存储的数据结构的方式检查我需要的属性?

The thing is, the amount of data stored may potentially become somewhat large and I'm afraid that all these subqueries (there might be need to specify some 10-15 filters), each one parsing the whole DB, may cause serious performance issues (not to say that even with my limited SQL skills I'm pretty sure that there must be a better way of doing what I need to do).
On the other hand, drastic changes to the DB are not really an option since the code working with it depends heavily on the current DB structure.
Is there a way to check the attributes the way I need it in a single query, with a limited amount or no changes to the stored data structure?

有效的查询，与上面的查询等效，但优化效果更好，归功于 DRapp :

The working query, equivalent to the one above but much better optimized, credits to DRapp :

SELECT STRAIGHT_JOIN
      rel.id_obj
   from 
      relations rel
         join attributes atr
            on rel.id_attr = atr.id
   where 
         ( rel.value = 'Apartment' AND atr.name = 'Type'  )
      or ( rel.value = 'City b' AND atr.name = 'City' )
   group by 
      rel.id_obj
   having
      count(*) = 2
   limit
      0, 20

推荐答案

这应该为您提供所需的...每个"OR" d where子句条件，您都可以继续添加为合格项目.然后，只需将"Having"子句调整为与您允许的条件相同的数字即可... 我把关系表放在第一位，因为这样会在City的值"或类型值上有一个较小的匹配集.请确保在关系表的"VALUE"列上有一个索引.

This should get you what you need... Each of the "OR"d where clause conditions, you can just keep adding to as a qualified item. Then, just adjust the "Having" clause to meet the same number as criteria you are allowing... I've put the relations table first as that would have a smaller matched set on the "Value" of City or type values... Ensure you have an index on the relations table on the "VALUE" column.

SELECT STRAIGHT_JOIN
      rel.id_obj
   from 
      relations rel
         join attributes atr
            on rel.id_addr = atr.id
   where 
         ( rel.value = 'Apartment' AND atr.name = 'Type'  )
      or ( rel.value = 'Some City' AND atr.name = 'City' )
   group by 
      atr.id_obj
   having
      count(*) = 2
   limit
      0, 20

如果要从该结果中获取所有实际的对象数据，则可以将其包装为...

If you want all the actual object data FROM this results, you would wrap it something like...

select obj.*
   from 
      ( complete SQL statement above ) PreQuery
         join Object obj on PreQuery.id_obj = obj.id

这篇关于MySQL优化将键值对过滤为记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！