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中取出对应用户的积分数据了
..