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

I am now developing module that generate data as following structure into json from mysql.

我现在正在开发一个模块，它可以从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"}
             ]
       }
   ]
 }

To achieve this, firstly, I retrieve employee data by register_date range as follow.

为了实现这一点，首先，我通过register_date范围来检索员工数据。

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

Then I iterate each row from result and retrieve education information from education table as follow:

然后从result中迭代每一行，从education table中检索教育信息如下:

 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
}

This project's data structure is not my design and I know it's not a good idea setting employee'id as foreign key in education table, instead, It should be set education id as foreign key in employee table. My problem is that (by using this data structure) retrieving education list for each row of employee is huge performance issue because there may be about 500000 record of employee for a month and for that amount, mysql select queries have to be processed 500000 times for education data retrieval for each employee. how should I optimize.

这个项目的数据结构不是我设计的，我知道把员工id设置为教育表中的外键不是一个好主意，而是应该将教育id设置为员工表中的外键。我的问题是,(通过使用这个数据结构)检索教育员工列表的每一行是巨大的性能问题,因为可能会有大约500000名员工一个月的记录和金额,mysql select查询教育必须处理500000次数据检索为每一个员工。我该如何优化。

1.Should I change data structure?
2. Should I create mysql stored procedure that generate json string directly from database?
3. Should I denormalize education data in employee table?
which is most efficient approach or any suggestion?

1。我应该更改数据结构吗?2。我应该创建直接从数据库生成json字符串的mysql存储过程吗?3所示。我是否应该将教育数据规范化?什么是最有效的方法或建议?

Please help me.

请帮助我。

3 个解决方案

#1

As pointed out in the comments, you could probably just use some sort of JOIN.

正如评论中指出的，您可能只需要使用某种形式的连接。

Other than that I'd like introduce you to the following quick & dirty solution. This might use a lot of ram.

除此之外，我还想向您介绍下面的快速和肮脏的解决方案。这可能需要很多内存。

$arr_education = array();
$res_education = mysql_query("SELECT emp_id, degree, description FROM education");

while($row_education = mysql_fetch_assoc($res_education)) {
    $arr_education[$row_education["emp_id"]] = array("degree"=>$row_education["degree"], "description" => $row_education["description"]);
}

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

while($row=mysql_fetch_assoc($result)) {
    //$arr_education[$row["id"]] is an array containing the education-entries for the employee
    $tmp = $row;
    $tmp["education"] = $arr_education[$row["id"]];
    echo json_encode($tmp);
}

To your specific questions:

你的具体问题:

If emp -> education is a 1 to 1 relation you should change the data structure.

如果emp ->教育是1到1的关系，您应该改变数据结构。
Don't do that

不要那样做
Don't do that

不要那样做

Consider switching to PDO! http://php.net/manual/en/book.pdo.php It won't help you in this specific case but you're should never use mysql_* functions.

考虑切换到PDO !在这个特定的情况下，它不会帮助您，但是您永远不应该使用mysql_*函数。

#2

You can query education data after fetching employees and assigning them in php loop.

在获取员工并在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;
}

#3

Better you can use the SP's for processing the data and then to return JSON string. Let the DB system process and iterate. I think it will improve a lot compared with the current implementation.

最好使用SP来处理数据，然后返回JSON字符串。让DB系统进行进程和迭代。我认为它将比目前的实现有很大的改进。

#1