如何高效从MySQL的100个字段表中获取数据？

针对你这种Drupal式EAV（实体-属性-值）设计下的多字段查询问题，推荐两种无冗余、高性能的单查询方案：

方案1：条件聚合（适合批量实体查询） #

利用GROUP BY结合MAX(CASE)将分散在多个字段表中的属性聚合为一行数据，完全避免数据冗余，且单查询完成所有字段获取。

原生SQL示例 #

假设核心实体表为entities，每个字段对应独立表（如field_name、field_age等，均含entity_id和value字段）：

SELECT
    e.id,
    MAX(CASE WHEN fn.entity_id = e.id THEN fn.value END) AS name,
    MAX(CASE WHEN fa.entity_id = e.id THEN fa.value END) AS age,
    MAX(CASE WHEN fe.entity_id = e.id THEN fe.value END) AS email
    -- 依次添加所有需要的字段CASE语句
FROM entities e
LEFT JOIN field_name fn ON e.id = fn.entity_id
LEFT JOIN field_age fa ON e.id = fa.entity_id
LEFT JOIN field_email fe ON e.id = fe.entity_id
WHERE e.id = 123 -- 目标实体ID
GROUP BY e.id

Laravel Query Builder实现 #

$entityId = 123;
// 可从配置/模型中动态获取所有需要的字段列表
$fields = ['name', 'age', 'email', /* ... 其他100+字段 */];

$query = DB::table('entities')->select('entities.id');

foreach ($fields as $field) {
    $table = "field_{$field}";
    $query->leftJoin($table, "entities.id", "=", "{$table}.entity_id")
          ->addSelect(DB::raw("MAX(CASE WHEN {$table}.entity_id = entities.id THEN {$table}.value END) AS {$field}"));
}

// 获取单个实体的完整数据
$result = $query->where('entities.id', $entityId)
                ->groupBy('entities.id')
                ->first();

优势：支持批量查询多个实体，结果无冗余，所有聚合逻辑在MySQL端完成，无需PHP额外处理。只要每个字段表的entity_id有索引，JOIN和聚合操作的性能会非常高效。

方案2：子查询关联（适合单个实体查询） #

针对单个实体查询，用子查询直接从每个字段表中获取对应值，避免多表JOIN的开销，同样返回无冗余的单行数据。

原生SQL示例 #

SELECT
    e.id,
    (SELECT value FROM field_name WHERE entity_id = e.id) AS name,
    (SELECT value FROM field_age WHERE entity_id = e.id) AS age,
    (SELECT value FROM field_email WHERE entity_id = e.id) AS email
FROM entities e
WHERE e.id = 123

Laravel Query Builder实现 #

$entityId = 123;
$fields = ['name', 'age', 'email', /* ... 其他100+字段 */];

$query = DB::table('entities')->select('entities.id');

foreach ($fields as $field) {
    $query->addSelect(DB::raw("(SELECT value FROM field_{$field} WHERE entity_id = entities.id) AS {$field}"));
}

$result = $query->where('entities.id', $entityId)->first();

优势：每个子查询都是基于entity_id索引的单表快速查询，MySQL会优化为批量执行这些子查询，相比100+独立请求级别的查询，节省了连接建立、SQL解析的开销，性能提升明显。

关键优化建议 #

• 所有字段表的entity_id必须建立唯一索引（因为每个实体对应一个字段值），这是两种方案性能的基础。
• 在Laravel中可将字段列表抽象为配置项或模型常量，动态生成查询语句，避免硬编码。
• 对于访问频繁的实体，可将完整数据缓存至Redis等内存存储，进一步降低数据库查询压力。

内容的提问来源于stack exchange，提问作者Matt Sawyer