MySQL关联users与user_activity表查询执行耗时过长原因求助

问题原因排查 #

1. WHERE条件逻辑错误，扫描行数指数级增加 #

MySQL中AND运算符优先级高于OR，你写的查询实际执行的过滤逻辑等价于：

WHERE 
    ua.target = 'Save Post' 
    OR ua.target = 'Send comment' 
    OR (
        ua.target = 'Send Reply' 
        AND ua.created_on >= 1630454401 
        AND ua.userid = u.id
    )

大量不满足时间范围、未和users表正确关联的user_activity行被扫描，甚至产生笛卡尔积，除了耗时久之外，查询返回的统计结果也是错误的。同时你使用的隐式内连接写法本身就容易出现关联条件遗漏的问题。

2. 缺少合适索引，触发全表扫描 #

没有对应索引的前提下，MySQL会全表扫描user_activity表，还要回表关联users表，数据量超过万级时性能会断崖式下降。

3. 分组排序无优化，产生额外性能开销 #

分组、排序字段没有索引支撑时，MySQL会创建临时表、执行文件排序，额外消耗大量内存和CPU资源。

优化方案 #

第一步：修正SQL逻辑，调整写法 #

改用显式JOIN，补全分组字段（MySQL开启ONLY_FULL_GROUP_BY模式时原始写法会直接报错）：

SELECT ua.userid, COUNT(ua.id) AS user_active, u.name 
FROM user_activity ua
INNER JOIN users u ON ua.userid = u.id
WHERE 
    ua.target IN ('Save Post', 'Send comment', 'Send Reply')
    AND ua.created_on >= 1630454401 
GROUP BY ua.userid, u.name
ORDER BY user_active DESC LIMIT 5

第二步：添加联合索引避免回表 #

给user_activity表添加覆盖索引，过滤、计算都直接走索引完成不需要回表：

CREATE INDEX idx_act_target_created_user ON user_activity(target, created_on, userid, id);

users表的id字段如果不是主键/唯一索引，补充索引即可：

ALTER TABLE users ADD PRIMARY KEY(id); -- 若id已经是主键可跳过

第三步：大表场景下优化关联逻辑 #

如果user_activity表数据量超过10万，可以先做预聚合再关联，减少关联行数：

SELECT t.userid, t.user_active, u.name
FROM (
    SELECT userid, COUNT(id) AS user_active
    FROM user_activity
    WHERE target IN ('Save Post', 'Send comment', 'Send Reply') AND created_on >= 1630454401
    GROUP BY userid
    ORDER BY user_active DESC LIMIT 5
) t
INNER JOIN users u ON t.userid = u.id

该写法只需要先在子查询中处理user_activity表，拿到前5条结果后再关联users表，关联行数仅为5行，性能提升非常明显。

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