mysql中统计积分left join的优化

Question

需求是需要查询用户列表并统计积分，会员表目前有50W，积分表有100W左右的数据，现在这条查询语句需要花十几秒的时间，应该怎么优化？

SELECT
    m.username,
    m.truename,
    m.mobile,
    m.company,
    m.email,
    c.credit
FROM
    u_member m
LEFT JOIN (
    SELECT
        username,
        SUM(amount) AS credit
    FROM
        u_finance_credit
    GROUP BY
        username
) AS c ON m.username = c.username
ORDER BY
    m.userid DESC
LIMIT 0,10;

Answer 1

分两条sql，首先查出第一页的数据，然后获取username(what?用username做关联？..), 再去积分表查,where username in(第一页的username),username加索引即可。大概代码就是这样： $sql = 'SELECT m.username, m.truename, m.mobile, m.company, m.email FROM u_member ORDER BY m.userid DESC LIMIT 0,10'; $res = query($sql); // 获取所有user_names 作为下个查询的in条件 $user_names = []; foreach($res as $r){ $user_names[] = $r['username']; } $user_names = '"' . implode('","', $user_names) . '"'; $sql2 = " SELECT username, SUM(amount) AS credit FROM u_finance_credit where username in({$user_names}) GROUP BY username"; $user_points_res = query($sql2); $user_points_map = []; // 然后按照username作为数组键 foreach($user_points_res as $temp){ $user_points_map[$temp['user_name']] = $temp['credit']; } // 然后输出视图页面的时候就可以从这个$user_points_map中取出对应用户的积分数据了 ..