php - 如何优化在php中将mysql关系数据导出为json

Question

我现在正在开发一个模块，它可以从 mysql 中生成如下结构的数据到 json 中。

 {"employee":
   [
      {"id":1,
       "name":"jhon doe",
       "register_date":"2011-05-11",
       "education":
             [ 
                {"degree":"B.A","description":"History"},
                {"degree":"M.A","description":"History"}
             ]
       },
       {"id":2,
       "name":"Smith",
       "register_date":"2011-06-11",
       "education":
             [ 
                {"degree":"B.E","description":"Mechnical"},
                {"degree":"M.E","description":"Mechnical"}
             ]
       }
   ]
 }

为此，首先，我按 register_date 范围检索员工数据，如下所示。

 $result=mysql_query("SELECT * FROM employee WHERE register_date>'2011-04-31' AND register_date<'2011-07-01'");

然后我迭代结果中的每一行并从教育表中检索教育信息，如下所示:

 while($row = mysql_fetch_assoc($result)){
   $id=$row["id"];
   $education=mysql_query("SELECT degree,description from education where emp_id=$id");
   // assigning education array as educaiton field in $row
   // write json_encode($row) to output buffer
} 

这个项目的数据结构不是我设计的，我知道在 education 表中将 employee'id 设置为外键不是一个好主意，相反，应该将 education id 设置为 employee 表中的外键。我的问题是(通过使用这种数据结构)为每一行员工检索教育列表是一个巨大的性能问题，因为一个月可能有大约 500000 条员工记录，对于这个数量，mysql select 查询必须处理 500000 次每个员工的教育数据检索。我应该如何优化。

1.我应该改变数据结构吗？
2. 是否应该创建直接从数据库生成json字符串的mysql存储过程？
3. employee 表中的教育数据是否应该反规范化？
哪种方法最有效或有什么建议？

请帮帮我。

Answer 1

您可以在获取员工并在 php 循环中分配他们之后查询教育数据。

$result = mysql_query("select * from employee where register_date > '2011-04-31' and register_date < '2011-07-01'");

$employees = [];

while ($row = mysql_fetch_assoc($result)) {
    $id = $row['id'];
    $employees[$id] = $row;
}

$ids = join(', ', array_keys($employees));

$result = mysql_query(sprintf('select degree, description, employee.id as employee_id from education left join employee on emp_id = employee.id where employee.id in (%s)', $ids));

while ($row = mysql_fetch_assoc($result)) {
    $id = $row['employee_id'];
    unset($row['employee_id']);

    if (!isset($employees[$id]['education'])) {
        $employees[$id]['education'] = [];
    }

    $employees[$id]['education'][] = $row;
}