mysql - 提高两个大表的 MySQL JOIN 速度

Question

我必须在 MySQL 查询中连接到大型表，这需要很长时间 - 大约 180 秒。有什么优化合并的技巧吗？

我的表有 10 个字段。我只在查询中使用 4 - 所有字符串。表有大约 600,000 行，结果应该有大约 50 行。

使用的四个行是:标题、变量、位置、日期

这是我的查询:

SELECT DISTINCT t1.Title, t1.Variables FROM `MyTABLE` t1 JOIN `MyTABLE` t2  
USING (Title,  Variables) 
WHERE (t1.Location, t1.Date) = ('Location1', 'Date1') 
AND (t2.Location, t2.Date) = ('Location2', 'Date2')

Answer 1

正如其他人指出的那样，您需要适当的索引。对于此特定查询，您可以从以下索引中受益:

(Location, Date) 或 (Date, Location)(对于 WHERE 子句)和(Title, Variables) 或 (Variables, Title) (对于join 条件，ON 子句)

准确了解位置、日期、标题和变量列的大小(即数据类型)会很有帮助，因为大索引可能比小索引慢。

最后，提示:我不会像您那样使用花哨的比较结构。

USING (Title,  Variables) 

可能没问题，但我肯定会检查一下

(t1.Location, t1.Date) = ('Location1', 'Date1') 

和

(t2.Location, t2.Forecast_date) = ('Location2', 'Date2')

表现如你所料。所以我肯定会在其上运行 EXPLAIN，并将输出与“常规”老式比较进行比较，如下所示:

    t1.Location      = 'Location1'
AND t1.Date          = 'Date1'
AND t2.Location      = 'Location2'
AND t2.Forecast_date = 'Date2'

您可能会从逻辑上争辩说，它们是一样的，应该无关紧要 - 您是对的。但话又说回来，MySQL 的优化器不是很聪明，而且总是有可能出现错误，尤其是对于不常使用的功能。我认为这是一个特点。所以我至少会尝试 EXPLAIN 并查看这些替代符号的评估是否相同。

但正如 BenoKrapo 指出的那样，做这样的事情会不会更容易:

SELECT Title, Variables 
FROM   MyTABLE
WHERE  Location = 'Location1' AND Date = 'Date1' 
OR     Location = 'Location2' AND Date = 'Date2'
GROUP BY Title, Variables
HAVING COUNT(*) >= 2

编辑:我将 HAVING COUNT(*) = 2 更改为 HAVING COUNT(*) >= 2。查看评论(再次感谢 BenoKrapo)

编辑:发布此答案几天后，我发现了 Facebook MySQL 架构师 Mark Callaghan 的这篇帖子:http://www.facebook.com/note.php?note_id=243134480932本质上，他描述了相似但不同的“智能”比较如何因 MySQL 优化器错误而导致糟糕的性能。所以我的观点是，当你受苦时，试着不喜欢你的语法，你可能遇到了一个错误。