检索MySQL EAV结果作为关系表的最佳性能是什么？

Question

我想从EAV（实体-属性-值）表中提取结果，或者更具体地说，实体元数据表（类似于wordpress的wp_posts和wp_postmeta），以“漂亮排列的关系表”的形式，以便进行一些排序和/或过滤。

我找到了一些如何在查询内格式化结果的示例（而不是编写2个查询并在代码中连接结果），但我想知道最有效的方法，特别是对于更大的结果集。

当我说“最有效”时，我的意思是适用于以下情况：

获取所有姓氏为XYZ的实体

按生日排序返回实体列表

例如将这样：

** 实体 **
-----------------------
ID  | NAME | 其他
-----------------------
 1  | bob  | etc
 2  | jane | etc
 3  | tom  | etc
** 元数据 **
------------------------------------
ID | EntityID | KEY         | VALUE
------------------------------------
 1 |   1      | first name  | Bob
 2 |   1      | last name   | Bobson
 3 |   1      | birthday    | 1983-10-10
 . |   2      | first name  | Jane
 . |   2      | last name   | Janesdotter
 . |   2      | birthday    | 1983-08-10
 . |   3      | first name  | Tom
 . |   3      | last name   | Tomson
 . |   3      | birthday    | 1980-08-10

变成这样：

** 结果 **
-----------------------------------------------
EID | NAME | first name | last name    | birthday
-----------------------------------------------
 1  | bob  | Bob        | Bobson       | 1983-10-10
 2  | jane | Jane       | Janesdotter  | 1983-08-10
 3  | tom  | Tom        | Tomson       | 1980-08-10

这样我就可以通过任何元字段进行排序或过滤了。

我在这里找到了一些建议（如何旋转EAV），但我找不到任何关于哪个表现更好的讨论。

选项：

1. GROUP_CONCAT:

   SELECT e.*, GROUP_CONCAT( CONCAT_WS('||', m.KEY, m.VALUE) ORDER BY m.KEY SEPARATOR ';;' )
   FROM `ENTITY` e JOIN `META` m ON e.ID = m.EntityID
   

2. Multi-Join:

   SELECT e.*, m1.VALUE as 'first name', m2.VALUE as 'last name', m3.VALUE as 'birthday'
   FROM `ENTITY` e
   LEFT JOIN `META` m1
       ON e.ID = m1.EntityID AND m1.meta_key = 'first name'
   LEFT JOIN `META` m2
       ON e.ID = m2.EntityID AND m2.meta_key = 'last name'
   LEFT JOIN `META` m3
       ON e.ID = m3.EntityID AND m3.meta_key = 'birthday'
   

3. Coalescing:

   SELECT e.*
      , MAX( IF(m.KEY= 'first name', m.VALUE, NULL) ) as 'first name'
      , MAX( IF(m.KEY= 'last name', m.VALUE, NULL) ) as 'last name'
      , MAX( IF(m.KEY= 'birthday', m.VALUE, NULL) ) as 'birthday'
   FROM `ENTITY` e
   JOIN `META` m
       ON e.ID = m.EntityID
   

4. Code:

   SELECT e.* FROM `ENTITY` e WHERE e.ID = {whatever};
   

   in PHP, create a placeholder object from result

   SELECT m.* FROM `META` m WHERE m.EntityID = {whatever};
   

   in PHP, loop through results and attach to entity object like: $e->{$result->key} = $result->VALUE

一般情况下，哪种方法更好，并适用于过滤/排序？

相关问题：

1. 绑定EAV结果
2. 如何在MySQL中旋转实体（Pivot EAV）

- drzaus

---

如果要在性能上打赌，只有一次机会，我会选择多重连接。 - ypercubeᵀᴹ

在选项1和3中，您需要一个GROUP BY e.ID。 - ypercubeᵀᴹ

请看一下dba.se上的这个问题。 - ConcernedOfTunbridgeWells

选项5. SELECT列表中的相关子查询... SELECT e.id, (SELECT m.val FROM meta m WHERE m.key = 'fi' AND m.e_id = e.id ORDER BY m.id LIMIT 1) AS fi, (SELECT n.val FROM meta n WHERE n.key = 'fo' AND n.e_id = e.id ORDER BY n.id LIMIT 1) AS fo FROM entity e - spencer7593

Answer 1

最好的方法是进行测试，当然了。答案可能会因数据集的大小、不同元键的数量、它们的分布（所有实体是否都有所有元键的值？还是只有其中几个？）、数据库服务器的设置以及可能的许多其他因素而有所不同。

如果我要猜测，我会说选项2中的JOIN操作成本将比选项1和3中需要的GROUP BY和聚合函数成本更小。

因此，我预计选项2比1和3更快。

要衡量选项4，您必须考虑更多因素，因为应用程序可能在另一个服务器上，因此必须考虑这两个服务器（数据库和应用程序）的负载以及将请求这些结果的客户端数量。

---

附注：在选项1和3中需要使用GROUP BY e.ID。

- ypercubeᵀᴹ

Answer 2

使用pivot或聚合函数的任何操作都可能更快，因为它们不需要对表进行自我连接。基于联接的方法将需要优化器执行多个子查询操作，然后将结果连接在一起。对于小的数据集，这可能影响不大，但是如果你要对较大的数据集进行分析查询，则可能会导致性能显著下降。

- ConcernedOfTunbridgeWells

---

根据您在评论中提供的链接，我将其标记为答案，尽管我希望得到更明确的回答 :) - drzaus

我有一个 EAV 系统，它正在不断增长，我该如何将其移植到另一个保存模块以保存我的系统？问题是你更喜欢哪种方式来保存（JSON）？ - babak faghihian