mysql - 提高 LEFT JOIN/GROUP BY 的性能

Question

我有两个表snippets 和platforms。每个片段都属于一个平台(fork_id 可为空并链接到同一表上的另一条记录)。结构:

PLATFORMS (id, name, slug, syntax)
SNIPPETS (id, platform_id, fork_id, private etc.) 

我现在尝试运行查询来获取每个平台的片段总数。当代码片段表有 100 万条记录时，查询速度很慢(10 - 20 秒)。

SELECT platforms.id, name, slug, syntax, COUNT(*) AS total FROM platforms 
LEFT JOIN snippets on platforms.id = snippets.platform_id
WHERE fork_id IS NULL
AND private = 0
GROUP BY platforms.id, name
ORDER BY total DESC, name asc;

一些附加信息:

• snippets.id 和 platform.id 具有主键索引。
• fork_id 和 platform_id 有外键索引。
• private 和platforms.name 有索引。

运行 EXPLAIN 查询提供以下内容:

如何才能使性能达到可接受的水平？谢谢!

Answer 1

在 MySQL 中，使用相关子查询，这种类型的查询可以更快:

SELECT p.id, p.name, p.slug, p.syntax,
       (SELECT COUNT(*)
        FROM snippets s
        WHERE p.id = s.platform_id AND
              s.fork_id IS NULL AND
              s.private = 0
       ) AS total
FROM platforms  p
ORDER BY total DESC, name asc;

然后，您需要在 snippets(platform_id, fork_id, private) 上建立索引。

我应该注意，您的原始查询相当于:

SELECT p.id, p.name, p.slug, p.syntax, COUNT(*) AS total
FROM platforms p JOIN
     snippets s
     on p.id = s.platform_id
WHERE s.fork_id IS NULL AND s.private = 0
GROUP BY p.id, p.name
ORDER BY total DESC, name asc;

因为WHERE子句将LEFT JOIN变成了INNER JOIN。对于此查询，您可以尝试在 snippets(private, fork_id, platform_id) 上建立索引。