在相关表上使用sum()时查询速度较慢

Question

我有两个表(users和xp_points)。我需要得到每个用户的xp点数的总和。但是我的查询似乎非常慢(380毫秒)。我的数据库很大。我尝试在users表上创建一个名为total_xp_points的列，然后查询时间降到只有4.6ms。

但我不想手动更新每个用户的xp总分。有没有一种方法可以优化这个查询的速度？

users表

+----+-------------+-----------------+
| id |  fullname   | total_xp_points |
+----+-------------+-----------------+
|  1 | John Adams  |              22 |
|  2 | Will Smith  |               0 |
|  3 | Bob McGee   |             125 |
|  4 | Andy Briggs |               0 |
|  5 | Linda James |              20 |
+----+-------------+-----------------+

xp_points表

+----+---------+------------+-----------+
| id | user_id |    date    | xp_points |
+----+---------+------------+-----------+
|  1 |       1 | 2019-01-05 |        17 |
|  2 |       1 | 2019-03-07 |         5 |
|  3 |       3 | 2019-03-07 |         0 |
|  4 |       3 | 2019-01-08 |       125 |
|  5 |       5 | 2019-01-19 |        20 |
+----+---------+------------+-----------+

这个查询花费了380.711毫秒的时间(慢得多)

SELECT
    users.id as user_id,
    users.fullname,
    sum(xp_points.xp_points) AS total_xp_points
FROM
    users
INNER JOIN xp_points
    ON users.id = xp_points.user_id
GROUP BY
    user_id,
    users.fullname
HAVING
    SUM(xp_points.xp_points) > 3
LIMIT 100

结果

+---------+-------------+-----------------+
| user_id |  fullname   | total_xp_points |
+---------+-------------+-----------------+
|       1 | John Adams  |              22 |
|       3 | Bob McGee   |             125 |
|       5 | Linda James |              20 |
+---------+-------------+-----------------+

下一个查询只需要4.641 ms (速度快得多，但我必须手动维护users表上的total_xp_points，这是我不想要的)

SELECT
    users.id as user_id,
    fullname,
    total_xp_points
FROM users
WHERE
    total_xp_points > 3
limit 100

它给出了与第一次查询相同的结果(但执行速度更快)

+---------+-------------+-----------------+
| user_id |  fullname   | total_xp_points |
+---------+-------------+-----------------+
|       1 | John Adams  |              22 |
|       3 | Bob McGee   |             125 |
|       5 | Linda James |              20 |
+---------+-------------+-----------------+

Answer 1

您的部分问题可能是GROUP BY。您可以尝试选择子查询，如下所示：

SELECT users.id AS user_id,
       users.fullname,
       (SELECT SUM(xp_points) FROM xp_points WHERE xp_points.user_id = users.id) AS total_xp_points
  FROM users
HAVING total_xp_points > 3
 LIMIT 100