mysql - group by 和 order by 使 mysql 中的查询非常慢？

Question

该查询执行速度很快:

SELECT SQL_NO_CACHE users.id, users.name
FROM users 
LEFT JOIN user_statistics ON users.id = user_statistics.user_id
WHERE users.is_deleted = 0
GROUP BY users.id
ORDER BY users.updated_at
LIMIT 50

这个慢慢来:

SELECT SQL_NO_CACHE users.id, users.name, MAX(user_statistics.access_time) AS user_access_time
FROM users 
LEFT JOIN user_statistics ON users.id = user_statistics.user_id
WHERE users.is_deleted = 0
GROUP BY users.id
ORDER BY user_access_time
LIMIT 50

这是慢速查询的解释输出:

*************************** 1. row ***************************
           id: 1   select_type: SIMPLE
        table: users
         type: ref possible_keys: fk_users_is_deleted
          key: fk_users_is_deleted
      key_len: 4
          ref: const
         rows: 107696
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1   select_type: SIMPLE
        table: user_statistics
         type: ref possible_keys: fk_user_statistics_user_id
          key: fk_user_statistics_user_id
      key_len: 4
          ref: users.id
         rows: 1
        Extra:

我在不同的表上使用GROUP BY 和ORDER BY。如何优化上面的慢查询？

Answer 1

尝试执行以下操作。首先，将查询重写为:

select u.id, u.name,
       (select max(us.access_time) from user_statistics us where u.id = us.user_id
       ) as user_access_time
from users u
where u.is_deleted = 0
order by user_access_time
limit 50;

创建以下索引:

user_statistics(user_id, access_time);
user(is_deleted);

这应该会提高性能。

注意:我不喜欢用嵌套子查询替换group by，但有时它们执行得更快。如果只有 SQL 引擎可以进行这种优化就好了。