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

我有两个表snippets和platforms。每个片段都属于一个平台（fork_id可为空，并且链接到同一表上的另一条记录）。结构体：

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

我现在正在尝试运行查询以获取每个平台的片段总数。当摘要表具有一百万条记录时，查询速度很慢（在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和platform.name具有索引。


运行EXPLAIN查询可提供以下内容：



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

最佳答案

在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)上建立索引。

关于mysql - 提高LEFT JOIN/GROUP BY的性能，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/41650337/