如何优化MySQL中订阅者当前可用金币的排名查询？

嘿，先帮你理清楚当前的问题：咱们的系统需要计算指定订阅者基于可用金币的排名，但现有的查询不仅慢（34秒+），还存在逻辑漏洞，得一步步来优化。

背景与原查询的问题 #

咱们的核心计算逻辑是：

可用金币 = 该订阅者所有发放金币总和 - 该订阅者所有已用金币总和

但现有的查询犯了一个关键错误：它只对比了其他用户的发放金币总和，没有减去他们的已用金币，这会导致排名结果不准确！比如如果有用户发了50金币但用了40，可用只有10，原查询会因为他的发放总和大于目标用户的可用金币（比如20），错误地把他算到排名前面。

除此之外，查询慢的核心原因还有两个：

• 全表无索引分组：外层对user_rewards做GROUP BY subscription_id时没有索引支撑，MySQL得全表扫描后再分组计算，80万数据量下这个操作特别耗时。
• 嵌套子查询冗余：内部重复查询目标用户的发放和已用金币，虽然是单条查询，但外层的全表分组才是最大的性能杀手。

优化步骤 #

1. 先添加必要的索引 #

索引是提升聚合查询性能的核心，给两张表分别加联合覆盖索引，避免回表查询：

-- 给user_rewards添加索引，支持分组和求和操作
CREATE INDEX idx_user_rewards_sub_coins ON user_rewards(subscription_id, coins);

-- 给coins_history添加同样的覆盖索引
CREATE INDEX idx_coins_history_sub_coins ON coins_history(subscription_id, coins);

2. 修复逻辑并优化查询语句 #

根据你的MySQL版本，给你两种实用方案：

方案A：适配所有MySQL版本（包括5.x）

先单独计算目标用户的可用金币，再统计所有可用金币比它多的用户数，加1就是目标用户的排名：

-- 先计算目标用户的可用金币，存到变量中
SET @target_available = (
    SELECT IFNULL(SUM(coins), 0) - IFNULL((SELECT SUM(coins) FROM coins_history WHERE subscription_id = 525252), 0)
    FROM user_rewards
    WHERE subscription_id = 525252
);

-- 统计可用金币大于目标值的用户数，加1得到最终排名
SELECT COUNT(*) + 1 AS aboveRank
FROM (
    SELECT ur.subscription_id,
           IFNULL(SUM(ur.coins), 0) - IFNULL(SUM(ch.coins), 0) AS available_coins
    FROM user_rewards ur
    LEFT JOIN coins_history ch ON ur.subscription_id = ch.subscription_id
    GROUP BY ur.subscription_id
    HAVING available_coins > @target_available
) t;

方案B：适配MySQL 8.0+（用窗口函数更简洁）

如果你的MySQL版本是8.0及以上，用RANK()窗口函数可以直接计算排名，逻辑更清晰易维护：

WITH user_available AS (
    -- 先计算所有用户的可用金币
    SELECT ur.subscription_id,
           IFNULL(SUM(ur.coins), 0) - IFNULL(SUM(ch.coins), 0) AS available_coins
    FROM user_rewards ur
    LEFT JOIN coins_history ch ON ur.subscription_id = ch.subscription_id
    GROUP BY ur.subscription_id
)
-- 直接查询目标用户的排名
SELECT subscription_id, RANK() OVER(ORDER BY available_coins DESC) AS rank
FROM user_available
WHERE subscription_id = 525252;

3. 终极优化：预计算缓存（实时性要求不高时用） #

如果排名不需要实时更新（比如每天更新一次就行），可以定时预计算所有用户的排名，存到一个新表中，查询时直接读取，性能会极大提升：

-- 创建预计算表
CREATE TABLE IF NOT EXISTS user_coins_rank (
    subscription_id VARCHAR(20) PRIMARY KEY,
    available_coins INT NOT NULL,
    rank INT NOT NULL
);

-- 每天定时执行这个脚本（可以用PHP脚本或MySQL事件）
TRUNCATE TABLE user_coins_rank;
INSERT INTO user_coins_rank
SELECT subscription_id,
       available_coins,
       RANK() OVER(ORDER BY available_coins DESC) AS rank
FROM (
    SELECT ur.subscription_id,
           IFNULL(SUM(ur.coins), 0) - IFNULL(SUM(ch.coins), 0) AS available_coins
    FROM user_rewards ur
    LEFT JOIN coins_history ch ON ur.subscription_id = ch.subscription_id
    GROUP BY ur.subscription_id
) t;

-- 查询时直接取数据，速度极快
SELECT rank FROM user_coins_rank WHERE subscription_id = 525252;

额外小建议 #

• 如果数据按日期增长，可以给user_rewards和coins_history按日期字段做分区，减少查询时扫描的数据量。
• 如果表中coins字段默认值是0，可以去掉IFNULL函数，减少一点函数调用开销。

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