mysql - 以关系表形式检索 MySQL EAV 结果的最佳性能是什么

Question

我想从 EAV(实体属性值)表中提取结果，或者更具体地说，实体元数据表(想想 wordpress wp_posts 和 wp_postmeta)作为“格式良好的关系表”，为了进行一些排序和/或过滤。

我找到了一些如何在查询中格式化结果的示例(而不是编写 2 个查询并在代码中加入结果)，但我想知道这样做的“最有效”方法，尤其是对于更大的结果集。

当我说“最有效”时，我指的是类似以下场景:

Get all Entities with last name like XYZ

Return a list of Entities sorted by birthday

---

例如转动这个:

** ENTITY **-----------------------ID  | NAME | whatever----------------------- 1  | bob  | etc 2  | jane | etc 3  | tom  | etc** META **------------------------------------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

进入这个:

** RESULTS **-----------------------------------------------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

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

---

我找到了一些建议 here ，但我找不到任何关于哪个表现更好的讨论。

选项:

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. 多连接:

   SELECT e.*, m1.VALUE as 'first name', m2.VALUE as 'last name', m3.VALUE as 'birthday'FROM `ENTITY` eLEFT 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. 合并:

   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` eJOIN `META` m    ON e.ID = m.EntityID

4. 代码:

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

   在 PHP 中，根据结果创建一个占位符对象

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

   在 PHP 中，循环遍历结果并附加到实体对象，例如: $e->{$result->key} = $result->VALUE

对于筛选/排序，哪个更好？

相关问题:

1. Binding EAV results
2. How to Pivot a MySQL entity

Answer 1

当然，找出答案的最佳方法是进行测试。答案可能会有所不同，具体取决于数据集的大小、不同元键的数量、它们的分布(所有实体对所有元键都有值吗？还是只有其中的几个？)、数据库的设置服务器以及可能的许多其他因素。

如果我猜的话，我会说选项 2 中的 JOIN 操作的成本将小于 GROUP BY 的成本和所需的聚合函数在选项 1 和 3 中。

因此，我希望比 1 和 3 更快地找到选项 2。

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

---

旁注:您需要在选项 1 和 3 中使用 GROUP BY e.ID。