PHP+MySQL构建的MLM系统会员层级分配及大数据查询优化咨询

一、先解决核心性能瓶颈：数据库层面优化 #

1. 给关联字段加索引 #

你原来的SELECT * FROM table WHERE refferal='A'在大数据下是全表扫描，这是卡顿的核心原因之一。给refferal字段加普通索引，能把查询速度提升几个数量级：

ALTER TABLE `your_table_name` ADD INDEX `idx_refferal` (`refferal`);

2. 新增child_count字段避免重复计数 #

每次用mysql_num_rows统计子级数量是非常低效的操作，尤其是当用户有大量子级时。新增一个INT类型的child_count字段，默认0，用来实时记录每个用户的子级数量：

ALTER TABLE `your_table_name` ADD COLUMN `child_count` INT DEFAULT 0 NOT NULL;

然后用MySQL触发器自动维护这个字段（避免PHP代码漏写逻辑）：

DELIMITER //
CREATE TRIGGER after_member_insert AFTER INSERT ON your_table_name
FOR EACH ROW
BEGIN
    UPDATE your_table_name SET child_count = child_count + 1 WHERE user_id = NEW.refferal;
END //
DELIMITER ;

这样以后判断某个用户是否满员，直接查child_count < 10即可，不用再统计行数。

二、高效查找可用父级：用递归CTE替代PHP循环 #

原来的PHP循环逐个查询子级是否有空位，会产生N次数据库请求，在会员量上去后完全没法用。用MySQL 8.0+支持的递归CTE，可以一次性查询出目标父级下第一个可用的空位节点：

递归CTE查询示例 #

WITH RECURSIVE available_nodes AS (
    -- 初始节点：用户指定的推荐人
    SELECT user_id, child_count, 1 AS level
    FROM your_table_name
    WHERE user_id = 'A' -- 替换为用户提交的推荐人ID
    UNION ALL
    -- 递归遍历子节点：只有父级满员时才继续往下找
    SELECT t.user_id, t.child_count, an.level + 1
    FROM your_table_name t
    JOIN available_nodes an ON t.refferal = an.user_id
    WHERE an.child_count >= 10 
      AND an.level < 10 -- 限制最大层级为10
)
-- 优先找层级最浅、ID最靠前的可用节点
SELECT user_id
FROM available_nodes
WHERE child_count < 10
ORDER BY level ASC, user_id ASC
LIMIT 1;

这个SQL会从目标父级开始，先检查它自己是否有空位；如果满了，就自动遍历它的子节点，找到第一个未满的节点，一次查询搞定所有逻辑，不用PHP循环。

三、PHP代码优化与并发处理 #

1. 替换废弃的mysql_*函数 #

mysql_query等函数早在PHP 7.0就被废弃了，改用mysqli或者PDO，不仅更安全，性能也更好。下面用mysqli做示例：

2. 完整代码实现（带事务防并发） #

并发注册时可能出现多个用户抢同一个空位，所以必须用事务加行锁，避免冲突：

// 用户提交的推荐人ID和新用户ID
$referrerId = 'A';
newUserId = 'A11';

// 初始化mysqli连接
$conn = new mysqli('localhost', 'db_user', 'db_pass', 'db_name');
if ($conn->connect_error) {
    die("数据库连接失败: " . $conn->connect_error);
}

// 开启事务
$conn->begin_transaction();

try {
    // 查找可用父级（加行锁，防止其他事务修改）
    $sql = "WITH RECURSIVE available_nodes AS (
        SELECT user_id, child_count, 1 AS level
        FROM your_table_name
        WHERE user_id = ?
        FOR UPDATE -- 行锁，避免并发争抢空位
        UNION ALL
        SELECT t.user_id, t.child_count, an.level + 1
        FROM your_table_name t
        JOIN available_nodes an ON t.refferal = an.user_id
        WHERE an.child_count >= 10
          AND an.level < 10
        FOR UPDATE
    )
    SELECT user_id
    FROM available_nodes
    WHERE child_count < 10
    ORDER BY level ASC, user_id ASC
    LIMIT 1;";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("s", $referrerId);
    $stmt->execute();
    $result = $stmt->get_result();
    
    if ($result->num_rows === 0) {
        throw new Exception("该推荐人下所有层级已无可用空位");
    }
    
    $row = $result->fetch_assoc();
    $parentId = $row['user_id'];
    
    // 插入新会员
    $insertSql = "INSERT INTO your_table_name (user_id, refferal) VALUES (?, ?)";
    $insertStmt = $conn->prepare($insertSql);
    $insertStmt->bind_param("ss", $newUserId, $parentId);
    $insertStmt->execute();
    
    // 提交事务
    $conn->commit();
    echo "注册成功，自动分配父级：" . $parentId;
} catch (Exception $e) {
    // 回滚事务
    $conn->rollback();
    echo "注册失败：" . $e->getMessage();
}

// 关闭连接
$conn->close();

四、额外性能建议 #

• 定期清理无效数据：比如删除未激活的会员，减少数据库数据量；
• 分表优化：如果会员数量突破千万级，可以考虑按用户ID分表，进一步降低单表查询压力；
• 缓存热门节点：把热门推荐人的可用空位情况缓存到Redis，减少高频查询的数据库请求次数。

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