优化PHP代码，从大型数据集构建树形结构

经验法则:PHP对每个标量值有40字节的开销。这些加起来很快。

不要使用已弃用的mysql_*接口;使用mysqli_*或PDO

解决方案:将所有数据放入临时MySQL表中，然后使用SQL语句重新排列成分层格式。啊;你似乎已经把数据放在一个表中了。

SQL将涉及每个层次结构级别的SQL语句，可能在单个语句中处理该级别的所有记录。PHP代码将控制级别，因为MySQL没有层次结构的概念。

很有可能这个改变甚至会比PHP运行得更快——你最终得到的查询数量很少，而不是你目前设计的20000(或更多)个。

这是一种转换代码的方法。从最内层的for循环开始。考虑一下如何将该循环转换为一个 SQL语句。那就去做吧。(这一步之后你可能会很开心)

显然在内存中重新创建树不起作用。对于在关系数据库中描述树的问题，简单的自引用表确实是一个简单而明显的解决方案，但是正如您所发现的，它也非常有限。如果您的数据存储在邻接表中，这将相对容易。

如果我们有一个可以更新的字段，那么可以迭代地标记树，例如....

 run_query('update yourtable set flag=0');
 foreach ($root_id as $root) {
     $d=1;
     run_query('update yourtable set flag=$d where id=$root');
     do {
        $rows_changed=run_query('update yourtable y1 
           set y1.flag=$d+1
           where y1.parent in (select y2.id from yourtable y2
             where y2.flag=$d');
        ++$d;
     } while ($rows_changed);
     run_query('update yourtable set root=$root, flag=0
        where flag>0');
 }

(注代码未测试)。

当然，这可以在mysql过程中实现，它将比php/mysql混合解决方案运行得稍微快一些。

首先为每个子节点的根ID添加一列:

ALTER TABLE targets ADD COLUMN Root_ID INT;

然后将根ID设置为管理员的ID:

$sql = "UPDATE targets SET Root_ID = Manager_ID";
if (!$conn->query($sql)) {
    error_log($conn->error);
    die("Database error occurred, see log.");
}

然后迭代地将根ID更新为该员工的经理ID。当没有进行任何更改时循环停止，因为不存在根ID本身具有管理器的子节点。此时，所有记录的根ID将被设置为没有管理员的员工记录(即根员工)。

$affectedRows = 1;
while ($affectedRows > 0) {
    $sql = "UPDATE targets AS child
        INNER JOIN targets AS parent
          ON child.Root_ID = parent.Emp_ID
        SET child.Root_Id = parent.Manager_ID";
    if (!$conn->query($sql)) {
        error_log($conn->error);
        die("Database error occurred, see log.");
    }
    $affectedRows = $conn->affected_rows;
    echo "Changed $affectedRows rows'n"; // demo only, remove this echo
}

现在您可以逐行打印出员工及其根，而不必同时存储所有数据:

$sql = "(SELECT Emp_ID, Root_ID FROM targets)
    UNION
    (SELECT t1.Manager_ID, 'Root' FROM targets AS t1
     LEFT OUTER JOIN targets AS t2 ON t1.Manager_ID = t2.Emp_ID
     WHERE t2.Emp_ID IS NULL)
    ORDER BY Emp_ID";
if (($result = $conn->query($sql, MYSQLI_USE_RESULT)) === false) {
    error_log($conn->error);
    die("Database error occurred, see log.");
}
printf("%-10s %-10s'n", "Emp_ID", "Root_ID");
while ($row = $result->fetch_assoc()) {
    printf("%-10s %-10s'n", $row["Emp_ID"], $row["Root_ID"]);
}
$result->free();

Emp_ID     Root_ID   
AAA        Root      
BBB        AAA       
CCC        AAA       
DDD        AAA       
EEE        AAA       
FFF        AAA       
GGG        AAA       
HHH        AAA       
III        Root      
JJJ        III       
KKK        III       
LLL        III       

你的评论:

为了优化这一点，我添加了索引PRIMARY KEY (Emp_ID, Manager_ID)。下面是我的表定义:

CREATE TABLE `targets` (
  `Manager_Id` char(3) NOT NULL,
  `Emp_Id` char(3) NOT NULL,
  `Root_ID` char(3) DEFAULT NULL,
  PRIMARY KEY (`Emp_Id`,`Manager_Id`)
) ENGINE=InnoDB;

下面是UPDATE:

*************************** 1. row ***************************
               id: 1
      select_type: UPDATE
            table: child
       partitions: NULL
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 10
         filtered: 100.00
            Extra: Using where
*************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: parent
       partitions: NULL
             type: ref
    possible_keys: PRIMARY
              key: PRIMARY
          key_len: 3
              ref: test.child.Root_ID
             rows: 1
         filtered: 100.00
            Extra: Using index

下面是SELECT UNION的EXPLAIN:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: targets
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: UNION
        table: t1
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 6
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: Using index
*************************** 3. row ***************************
           id: 2
  select_type: UNION
        table: t2
   partitions: NULL
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 3
          ref: test.t1.Manager_Id
         rows: 1
     filtered: 100.00
        Extra: Using where; Not exists; Using index
*************************** 4. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union1,2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Using temporary; Using filesort