MySQL 查询左连接、分组和排序非常慢

Question

我有以下查询，执行大约需要 15 秒。如果我删除 ORDER BY，则需要 3 秒，这仍然太长了。

SELECT
 pages.id AS id,
 pages.page_title AS name,
 SUM(visitors.bounce) AS bounce,
 SUM(visitors.goal) AS goal,
 count(visitors.id) AS volume
FROM
 pages
LEFT JOIN visitors ON pages.id = visitors.page_id
GROUP BY pages.id
ORDER BY volume DESC

为了便于阅读，我稍微简化了应用程序中使用的查询，但我一直在使用这个简化的查询进行测试，问题仍然存在。所以问题出在这一部分。

表页:大约 3K 条记录。表访问者:大约 300K 条记录。

我做了什么:

• 我在访问者.page_id 上有索引(具有链接到pages.id 的外部键)。
• 显然我的 ID 字段已设置为主键。

我尝试过的:

• 我已将 read_buffer_size、sort_buffer_size、read_rnd_buffer_size 增加至 64M。
• 解释带排序的查询(15 秒):
• 解释查询而不排序(3秒，仍然很长，这不是我想要的输出):
• 删除 SUM 和 Count 计算，它们对执行时间没有真正的影响。

有什么想法可以改进此查询吗？

Answer 1

首先，尝试一下我的第一个建议是在连接之前进行聚合:

SELECT p.id, p.page_title AS name,
       v.bounce, v.goal,v.volume
FROM pages p LEFT JOIN
     (SELECT page_id, sum(v.bounce) as bounce, sum(v.goal) as goal,
             count(*) as volumn
      FROM visitors v
      GROUP BY page_id
     ) v
     ON pages.id = v.page_id
ORDER BY volume DESC;

但是，您的查询需要同时进行聚合和排序——并且您没有过滤。我不确定您能否更快地获得它。