基于PHP/MySQL的可折叠组织架构图算法性能优化求助

Hey Lucas, let's dive into solving this performance bottleneck—recursive PHP calls paired with repeated database queries are absolutely going to choke when dealing with 30k+ records, especially that supervisor with 4000 direct reports. I’ve tackled identical issues for enterprise org charts, so here are the most impactful fixes:

1. Optimize Database Queries (Cut Down Round-Trips) #

Recursive PHP code often hits the database once per node, which turns into thousands of slow queries. Instead, pull the entire tree structure in one go with MySQL Recursive CTEs (Common Table Expressions), and add indexes to speed up the joins.

Add Critical Indexes #

First, index the field you’re using to link subordinates to supervisors—this will make the CTE join blazingly fast:

CREATE INDEX idx_supervisor_position ON your_employee_table(`Supervisor Position #`);

Fetch Entire Tree with CTE #

Use a recursive CTE to grab every node’s hierarchy, depth, and path in a single query. This eliminates all those repeated PHP-to-database calls:

WITH RECURSIVE org_hierarchy AS (
  -- Anchor: Select root nodes (adjust the WHERE clause to match your root condition)
  SELECT 
    id, 
    `Name`, 
    `Position #`, 
    `Supervisor Position #`, 
    0 AS depth,
    CAST(`Position #` AS CHAR(255)) AS hierarchy_path
  FROM your_employee_table
  WHERE `Supervisor Position #` IS NULL OR `Supervisor Position #` = ''

  UNION ALL

  -- Recursive: Join subordinates to their supervisors
  SELECT 
    e.id, 
    e.`Name`, 
    e.`Position #`, 
    e.`Supervisor Position #`, 
    oh.depth + 1,
    CONCAT(oh.hierarchy_path, ',', e.`Position #`)
  FROM your_employee_table e
  JOIN org_hierarchy oh ON e.`Supervisor Position #` = oh.`Position #`
)
SELECT * FROM org_hierarchy ORDER BY hierarchy_path;

This query returns every employee with their depth in the tree and a path string (e.g., 100,200,300) that makes sorting and grouping trivial.

2. Build the Tree in PHP with Iteration (Not Recursion) #

If you still need to manipulate the tree in PHP, avoid recursive functions—they’re slow and risk stack overflow with deep/wide trees. Instead, load all data into memory first, then build the tree with a linear scan:

// Step 1: Fetch all employees in one query
$pdo = new PDO('mysql:host=localhost;dbname=your_db', 'username', 'password');
$stmt = $pdo->query("SELECT id, `Name`, `Position #`, `Supervisor Position #` FROM your_employee_table");
$allEmployees = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Step 2: Create a map for O(1) lookups (keyed by Position #)
$employeeMap = [];
foreach ($allEmployees as $emp) {
    $pos = $emp['Position #'];
    $employeeMap[$pos] = $emp;
    $employeeMap[$pos]['children'] = []; // Initialize empty child array
}

// Step 3: Build the tree iteratively
$orgTree = [];
foreach ($allEmployees as $emp) {
    $supervisorPos = $emp['Supervisor Position #'];
    // Add root nodes to the top-level tree
    if (empty($supervisorPos) || !isset($employeeMap[$supervisorPos])) {
        $orgTree[] = &$employeeMap[$emp['Position #']];
    } else {
        // Attach subordinates to their supervisor's children array
        $employeeMap[$supervisorPos]['children'][] = &$employeeMap[$emp['Position #']];
    }
}

// Clean up reference to save memory
unset($employeeMap);

This runs in O(n) time (linear) compared to recursive PHP’s O(n²) worst-case, which is night-and-day faster for large datasets.

3. Cache the Tree (Avoid Regenerating It) #

Org charts rarely change minute-to-minute. Cache the final tree structure to skip rebuilding it on every request:

Example with Redis (Persistent Cache) #

$redis = new Redis();
$redis->connect('localhost', 6379);
$cacheKey = 'organization_tree';

// Check if cache exists
if (!$cachedTree = $redis->get($cacheKey)) {
    // Run your tree-building logic here (from steps 1-2)
    $redis->set($cacheKey, json_encode($orgTree), 3600); // Cache for 1 hour
} else {
    $orgTree = json_decode($cachedTree, true);
}

For simpler setups, you could also use file-based caching (e.g., file_put_contents() with serialized data).

4. Lazy-Load Nodes in the Frontend #

Even if your backend is fast, rendering 30k+ nodes in the browser will lag. Implement lazy loading:

• Only render top-level nodes initially.
• When a user clicks to expand a supervisor, fetch their direct subordinates via an AJAX call (your backend can quickly retrieve these using the indexed Supervisor Position # field).

This keeps the frontend responsive and reduces initial load time.

These changes should take your org chart from "unusable" to "snappy" even with 30k+ records. The biggest wins are cutting database round-trips and ditching recursive PHP in favor of iterative tree-building.

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