提高MySQL连接两个大表的速度

Question

我需要在MySQL查询中连接两个大表，但这个过程非常耗时——大约需要180秒。有没有什么优化合并的技巧？

我的表有10个字段，但在查询中只使用了4个字符串字段。该表大约有60万行数据，查询结果应该只有50行。

我所使用的4个字段是：标题、变量、位置和日期。

以下是我的查询语句：

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')

- Brian

---

对于初学者，使用索引。 - Lukman

使用“EXPLAIN”在查询前生成一个查询计划，MySQL将使用该计划来帮助调查。 - mjsabby

Answer 1

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

(位置, 日期) 或 (日期, 位置) (用于 WHERE 子句) 和 (标题, 变量) 或 (变量, 标题) (用于连接条件, 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优化器错误而导致类似但不同的“智能”比较表现糟糕的情况。所以我的观点是，在遇到问题时，请尝试简化语法，你可能已经碰到了一个bug。

- Roland Bouman

---

感谢报价。确实，我错过了来自联接的基数约束。但是 Having count(*) 应该大于或等于 2，而不是等于。 - Benoît Vidis

Answer 2

是的。根据涉及表的查询创建适当的索引。

- Mitch Wheat

---

在我的查询中，我已经在WHERE逻辑中使用的字段上建立了索引。除此之外，我还能做些什么吗？ - Brian

阅读EXPLAIN的输出并根据其添加索引。 - Lukáš Lalinský

Answer 3

确保您匹配的字段已经建立索引。

匹配数字值比字符串更快。

但是，直接编写代码不是更简单吗？

SELECT DISTINCT 
  Title, 
  Variables 
FROM `MyTABLE`
WHERE 
  Location = 'Location1' AND Date = 'Date1' 
  OR
  Location = 'Location2' AND Date = 'Date2'

- Benoît Vidis

---

几乎...你必须确保返回的是两行...但是我确实忽略了。在我的回答中提到了你。 - Roland Bouman

Answer 4

这可能有点作弊，但是我发现在查询之后在 PHP 中将这两个查询 JOIN 在一起更容易。这只适用于我选择了两个不同的变量。

$query = "SELECT DISTINCT Title, Variables FROM 
MyTABLE WHERE Location='Location1' AND Variable='Variable1'";

$result = mysql_result($query);
while ($row = mysql_array_assoc($result)) {
    $Title = $row['Title'];
    $Variables = $row['Variables'];
    $Array_result1[$Title] = $Variables;
}


$query = "SELECT DISTINCT Title, Variables FROM 
MyTABLE WHERE Location='Location2' AND Variable='Variable2'";

$result = mysql_result($query);
while ($row = mysql_array_assoc($result)) {
    $Title = $row['Title'];
    $Variables = $row['Variables'];
    $Array_result2[$Title] = $Variables;
}

$Array_result = array_intersect($Array_result1, $Array_result2);

我喜欢只使用一个MySQL查询来合并这两个查询的想法，但这样做速度更快。

- Brian

Answer 5

你能在你的SQL语句前加上"EXPLAIN"并重新运行它吗？这很可能是因为你连接的列缺少索引。

同时尝试使用STRAIGHT_JOIN，并将较慢的表放在左侧，更大的表放在右侧，以提示MySQL选择第一个表。

- mjsabby

---

另外，查看哪个表更小（这很天真，但仍然如此），然后使用STRAIGHT_JOIN告诉MySQL按顺序读取表（从左到右）例如：EXPLAIN SELECT tb1.X FROM tb2 STRAIGHT_JOIN tb1 WHERE ....默认情况下，INNER JOIN（笛卡尔积）会发生，这可能是您想要的，但您可能会尝试使用OUTER JOIN。 - mjsabby

mjsabby，他正在使用自连接。根据定义，它们的大小完全相同。至于INNER JOIN是否是笛卡尔积：这是无稽之谈。如果您有一个适当的索引可以用来解决连接操作（在本例中，一个具有（Title，Variables）的索引），MySQL肯定不会计算笛卡尔积，而是使用嵌套循环连接。最后，外连接如果有任何作用，很可能会使事情变得更糟。 - Roland Bouman

Answer 6

如果没有表格和查询的描述，我们无法提供太多帮助。

有几个因素可以决定连接速度。

• 数据库引擎：您使用InnoDB还是MyISAM？还是其他任何引擎？一些比其他引擎更快，这会影响连接速度。
• 索引：适当的匹配列是否已经建立了索引？
• 分区索引：也许您可以通过索引对表进行分区以使其更快？

此外，查看 EXPLAIN query 将查看mysql执行查询所需的所有步骤。这可能会对您有很大帮助。

- Tor Valamo

Answer 7

尝试在where子句中使用复合索引，并将所有其他列放入包含列中，这将节省传统的查找成本。

- Nitin Midha

Answer 8

我进行了两个单独的连接，并使用联合运算符将结果组合起来。我在时间上得到了很好的改进。

SELECT t1.Title, t1.Variables FROMMyTABLE t1 JOINMyTABLEt2 on (t1.Location, t1.Date) = ('Location1', 'Date1') UNION SELECT t1.Title, t1.Variables FROMMyTABLE t1 JOINMyTABLEt2 on (t2.Location, t2.Date) = ('Location2', 'Date2');

确保两个查询具有相同数量的列和每个列的相同数据类型。此外，请检查选择子句的顺序。

- Mohit Satish Pawar