如何提高MySQL join的效率？

Question

我正在尝试将表df1与df2连接起来。问题是，df2是一个非常长的表，有4000万个观察值，在等待超过48小时后，我无法在MySQL中获得连接的结果。因此，我想问一下，是否有任何方法可以提高此连接的效率。我已经在两个表的标记列上添加了索引。

df1和df2具有相同的结构，下面是一个示例：

|Id        |Tag
| -------- | --------------------------------------------
|1         |c#
|1         |winforms
|1         |type-conversion
|1         |decimal
|2         |.net
|2         |decimal
|3         |.net
|3         |math

这两个表都有两列: Id和Tag。但是，Id和标签都不是唯一标识的键。只有Id+Tag可以是唯一标识的密钥。我需要的是省略在列标记上加入df1和df2。下面是我的代码：

CREATE TABLE matched_outcome AS
SELECT df1.Id AS df1_Id, df2.Id AS df2_Id, COUNT(df2.Tag) AS overlapping
FROM df1
LEFT JOIN df2 ON df2.Tag=df1.Tag
GROUP BY df1.Id, df2.Id;

Answer 1

我将按此顺序对两个表的(tag，id)尝试复合索引。

如果此索引被选取，请检查是否使用执行计划。

对标记列上的表进行分区可能会有所帮助，因为分区会在内部创建多个较小的表。

此外，您还应该运行此命令并检查进程在哪里卡住了SHOW FULL PROCESSLIST

这可能会给你进一步的线索。

Answer 2

当你使用JOIN...GROUP BY时，就会发生“膨胀-放气综合征”。而且它通常会导致聚合(COUNT、SUM等)的值错误(高)。

我对这个查询应该做些什么来帮助你重写它感到困惑。你能详细说明一下吗？为什么是LEFT？什么是“重叠”？

有时，解决方法是执行以下操作：

SELECT df1.some_stuff,
       ( SELECT COUNT(*) FROM df2 WHERE Tag = df1.Tag ) AS overlapping
    FROM df1;

这接近于你想要的吗？