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

我想从 EAV(实体-属性-值)表中提取结果,或者更具体地说是实体-元数据表(像 wordpress wp_postswp_postmeta)作为格式良好的关系表",为了进行一些排序和/或过滤.

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

当我说最有效"时,我的意思是类似以下场景:

<块引用>

获取姓氏为 XYZ 的所有实体

返回按生日排序的实体列表

<小时>

例如转这个:

<前>** 实体 **-----------------------身份证 |姓名 |任何-----------------------1 |鲍勃 |等等2 |简|等等3 |汤姆 |等等** 元 **---------------------身份证 |实体 ID |钥匙 |价值---------------------1 |1 |名字 |鲍勃2 |1 |姓氏 |鲍森3 |1 |生日 |1983-10-10.|2 |名字 |简.|2 |姓氏 |简斯多特.|2 |生日 |1983-08-10.|3 |名字 |汤姆.|3 |姓氏 |汤臣.|3 |生日 |1980-08-10

进入这个:

<前>** 结果 **-----------------------------------------------开斋节 |姓名 |名字 |姓氏 |生日-----------------------------------------------1 |鲍勃 |鲍勃 |博森 |1983-10-102 |简|简|简斯多特 |1983-08-103 |汤姆 |汤姆 |汤臣 |1980-08-10

所以我可以按任何元字段排序或过滤.

<小时>

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

选项:

  1. GROUP_CONCAT:<前>SELECT e.*, GROUP_CONCAT( CONCAT_WS('||', m.KEY, m.VALUE) ORDER BY m.KEY SEPARATOR ';;' )从`ENTITY` e JOIN `META` m ON e.ID = m.EntityID

  2. 多连接:<前>SELECT e.*, m1.VALUE 为名字",m2.VALUE 为姓氏",m3.VALUE 为生日"从`实体` e左加入`META` m1ON e.ID = m1.EntityID AND m1.meta_key = '名字'左加入`META` m2ON e.ID = m2.EntityID AND m2.meta_key = 'last name'左加入`META` m3ON e.ID = m3.EntityID AND m3.meta_key = '生日'

  3. 合并:<前>选择 e.*, MAX( IF(m.KEY= 'first name', m.VALUE, NULL) ) 为 'first name', MAX( IF(m.KEY= 'last name', m.VALUE, NULL) ) 为 'last name', MAX( IF(m.KEY= '生日', m.VALUE, NULL) ) 为 '生日'从`实体` e加入`META`米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. 绑定 EAV 结果
  2. 如何透视一个 MySQL 实体

解决方案

任何使用数据透视或聚合的方法可能会更快,因为它们不需要自联接表.基于连接的方法将要求优化器执行多个子查询操作,然后将结果连接在一起.对于小数据集,这可能无关紧要,但如果您对更大的数据集进行分析查询,这可能会显着降低性能,

I want to extract results from EAV (entity-attribute-value) tables, or more specifically entity-metadata tables (think like wordpress wp_posts and wp_postmeta) as a "nicely formatted relational table", in order to do some sorting and/or filtering.

I've found some examples of how to format the results within the query (as opposed to writing 2 queries and joining the results in code), but I would like to know the "most efficient" method for doing so, especially for larger result sets.

And when I say "most efficient", I mean for something like the following scenarios:

Get all Entities with last name like XYZ

Return a list of Entities sorted by birthday


e.g. turn this:

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

into this:

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

so I can sort or filter by any of the meta fields.


I found some suggestions here, but I can't find any discussion of which performs better.

Options:

  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

Which is better in general, and for filtering/sorting?

Related questions:

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

解决方案

Anything using pivot or aggregates will probably be faster, as they don't require the table to be self-joined. The join based approaches will require the optimiser to perform several sub-query operations and then join the results together. For a small data set this might not matter so much, but this could significantly degrade performance if you're doing an analytic query on a larger data set,

相关文章