如何编写高性能MySQL查询，基于加权动作与时间衰减对用户排名？

Hey there! Let's break down how to write a high-performance SQL query for this user ranking problem, step by step.

First, let's recap the scoring rule clearly:

• Action A contributes 60% weight to the user's total score, action B contributes 40%
• The score for a single action is calculated as: Action Weight * 1000000 / (Current Timestamp - Action Timestamp)
• We sum these values per user, then rank users by their total score in descending order.

Here's a straightforward query that implements this logic:

SELECT
    user_id,
    SUM(
        CASE action
            WHEN 'A' THEN 0.6 * 1000000 / (UNIX_TIMESTAMP() * 1000 - timestamp)
            WHEN 'B' THEN 0.4 * 1000000 / (UNIX_TIMESTAMP() * 1000 - timestamp)
        END
    ) AS total_score,
    RANK() OVER (ORDER BY total_score DESC) AS user_rank
FROM user_actions
GROUP BY user_id
ORDER BY total_score DESC;

• UNIX_TIMESTAMP() * 1000 gets the current timestamp in milliseconds (matching your sample data's format)
• The CASE statement maps each action to its respective weight
• SUM() aggregates the scores per user
• RANK() window function handles ranking (users with the same score get the same rank, and the next rank skips accordingly)

If you're dealing with large datasets, these tweaks will help the query run faster:

1. Add a Targeted Index #

Create a composite index to speed up grouping and timestamp lookups:

CREATE INDEX idx_user_action_timestamp ON user_actions (user_id, action, timestamp);

This index allows MySQL to quickly group rows by user_id and access the timestamp value without scanning the entire table. If you only care about recent actions (e.g., last 30 days), add a WHERE clause to filter old data:

WHERE timestamp >= (UNIX_TIMESTAMP() * 1000 - 86400000 * 30) -- Keep only actions from the last 30 days

2. Avoid Repeating Current Timestamp Calculations #

Calculating UNIX_TIMESTAMP() * 1000 for every row is inefficient. Instead, compute it once and reuse it:

Using Session Variables #

SET @current_ts = UNIX_TIMESTAMP() * 1000;

SELECT
    user_id,
    SUM(
        CASE action
            WHEN 'A' THEN 0.6 * 1000000 / (@current_ts - timestamp)
            WHEN 'B' THEN 0.4 * 1000000 / (@current_ts - timestamp)
        END
    ) AS total_score,
    RANK() OVER (ORDER BY total_score DESC) AS user_rank
FROM user_actions
WHERE timestamp < @current_ts -- Prevent division by zero (future timestamps)
GROUP BY user_id
ORDER BY total_score DESC;

Using a CTE (MySQL 8.0+) #

WITH current_time AS (
    SELECT UNIX_TIMESTAMP() * 1000 AS ts
)
SELECT
    ua.user_id,
    SUM(
        CASE ua.action
            WHEN 'A' THEN 0.6 * 1000000 / (ct.ts - ua.timestamp)
            WHEN 'B' THEN 0.4 * 1000000 / (ct.ts - ua.timestamp)
        END
    ) AS total_score,
    RANK() OVER (ORDER BY total_score DESC) AS user_rank
FROM user_actions ua
CROSS JOIN current_time ct
WHERE ua.timestamp < ct.ts
GROUP BY ua.user_id
ORDER BY total_score DESC;

3. Handle Edge Cases #

• Division by zero: Always filter out actions where timestamp >= current timestamp (future actions don't make sense for scoring) using the WHERE clause above.
• Large datasets: If your table has millions of rows, consider partitioning it by timestamp (e.g., monthly partitions) to limit the data scanned during queries.

4. Compatibility with Older MySQL Versions (Pre-8.0) #

If you're using MySQL 5.7 or earlier (which doesn't support window functions), use session variables to implement ranking:

SET @current_ts = UNIX_TIMESTAMP() * 1000;
SET @rank = 0;
SET @prev_score = NULL;

SELECT
    user_id,
    total_score,
    CASE
        WHEN total_score = @prev_score THEN @rank
        ELSE @rank := @rank + 1
    END AS user_rank
FROM (
    SELECT
        user_id,
        SUM(
            CASE action
                WHEN 'A' THEN 0.6 * 1000000 / (@current_ts - timestamp)
                WHEN 'B' THEN 0.4 * 1000000 / (@current_ts - timestamp)
            END
        ) AS total_score
    FROM user_actions
    WHERE timestamp < @current_ts
    GROUP BY user_id
    ORDER BY total_score DESC
) AS ranked_users
CROSS JOIN (SELECT @rank := 0, @prev_score := NULL) AS vars;

This subquery first calculates and sorts total scores, then uses variables to assign ranks (matching the behavior of RANK()).

Using your sample data and assuming a current timestamp of 1525600000000:

• User 1's total score ≈ 0.512 (from action A) + 2.374 (from action B) = 2.886
• User 2's total score ≈ 18.29 (from action B)
  The query will correctly rank User 2 first, followed by User 1.

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