百万级MySQL用户积分表每日更新排名的高效实现方案咨询

你的问题核心是当前循环单条更新的方式时间复杂度太高（O(N²)），且每次查询都可能触发全表扫描，导致百万级数据下效率极低。以下是针对性的优化方案：

1. 先添加关键索引 #

首先给user_points表创建联合索引，这是所有优化的基础，能大幅提升排序和查询速度：

CREATE INDEX idx_project_points ON user_points (project_id, points DESC);

这个索引会让数据库快速按项目分组、按积分降序排序，避免无索引下的全表扫描开销。

2. 使用批量更新替代循环单条操作 #

根据你的MySQL版本选择对应的方案，一次SQL操作完成所有排名计算和更新：

方案一：MySQL 8.0+（推荐，支持窗口函数） #

利用ROW_NUMBER()/RANK()/DENSE_RANK()窗口函数，一次性计算所有用户的排名，再通过JOIN批量更新：

WITH ranked_users AS (
    SELECT 
        user_id,
        project_id,
        -- 三种排名函数选其一，根据业务需求决定：
        -- ROW_NUMBER(): 相同积分也会分配不同排名（按数据默认顺序）
        -- RANK(): 相同积分同排名，后续排名跳号（比如两个第1名后是第3名）
        -- DENSE_RANK(): 相同积分同排名，后续排名连续（比如两个第1名后是第2名）
        ROW_NUMBER() OVER (PARTITION BY project_id ORDER BY points DESC) AS new_rank
    FROM user_points
)
UPDATE user_points up
JOIN ranked_users ru ON up.user_id = ru.user_id AND up.project_id = ru.project_id
SET up.rank = ru.new_rank;

方案二：MySQL 5.x（无窗口函数） #

使用用户变量模拟排名计算，同样通过JOIN批量更新：

UPDATE user_points up
JOIN (
    SELECT 
        user_id,
        project_id,
        @rank := IF(@current_project = project_id, 
                    IF(@current_points = points, @rank, @rank + 1), 
                    1) AS new_rank,
        @current_project := project_id,
        @current_points := points
    FROM user_points,
         (SELECT @rank := 0, @current_project := -1, @current_points := -1) AS init_vars
    ORDER BY project_id, points DESC
) ru ON up.user_id = ru.user_id AND up.project_id = ru.project_id
SET up.rank = ru.new_rank;

3. 分批次处理（可选，避免锁表） #

如果单条更新操作会导致长时间锁表影响业务，可以按project_id分批次处理，每次更新一个或几个项目的排名：

-- 示例：仅更新project_id=1的用户排名
WITH ranked_users AS (
    SELECT 
        user_id,
        ROW_NUMBER() OVER (ORDER BY points DESC) AS new_rank
    FROM user_points
    WHERE project_id = 1
)
UPDATE user_points up
JOIN ranked_users ru ON up.user_id = ru.user_id
SET up.rank = ru.new_rank
WHERE up.project_id = 1;

你可以在PHP中遍历所有project_id，逐个执行上述SQL，降低单操作锁表时间。

4. 代码层面的额外优化 #

• 移除原来的循环逻辑，直接执行上述批量SQL即可，避免PHP与数据库的多次交互开销。
• 原来的代码存在SQL注入风险（直接拼接$user['points']），批量方案完全避免了这个问题。

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