使用内连接的MySQL查询速度非常慢

Question

我有两个表，每个表包含6M行。我尝试使用inner连接这两个节点，但是查询运行了2天都没有完成。join是(请注意，我使用count(*)只是为了让我能够运行解释，我实际上是在CTAS中使用join )：

SELECT count(*)
FROM table1 t1,
     table2 t2
WHERE t1.col1 = t2.colA
  AND t1.col2 = t2.colB;

经过一些调查，我发现下面的查询运行得很好：

SELECT count(*)
FROM
  (SELECT *
   FROM table1) t1,

  (SELECT *
   FROM table2) t2
WHERE t1.col1 = t2.colA
  AND t1.col2 = t2.colB;

唯一的区别是，我使用子查询SELECT * FROM table而不是表；

运行explain plans显示，后一个查询在选择table2时正在构建索引。而第一个查询使用联接缓冲区(块嵌套循环)。

MySQL肯定足够聪明，能够计算出这两个查询实际上是相同的，并且对这两个查询执行相同的操作？我不明白为什么需要索引，因为无论如何都需要对两个表进行完全扫描。这些是临时表/临时表，所以如果我真的建立了索引，它实际上就是执行这个连接。

有没有办法通过MySQL配置来解决这个问题？

Answer 1

您至少需要一个表上的索引，甚至像这样

create index Temp1 on Table2 ( colA, colB )

因此，表1中的查询连接到表2，因此，即使表扫描是在整个表1上进行的，您也需要它来快速找到与表2中的记录匹配的记录。对于Table1中的每条记录，扫描表2中的所有记录，并获取与ColA、ColB匹配的所有记录。现在，回到表1，查找第二条记录...返回到表2中查找所有记录，直到找到匹配记录。

由于您有6M条记录，您几乎可以在性能上勒死一头牛(可以这么说)。通过建立索引，即使是在第二个表上，当查询是在第一个记录上时，它可以立即跳转到与ColA、ColB匹配的行，并且一旦这些A/B记录完成，它就会返回到第一个表。

现在，关于其他开销效率。如果两个表都分别在Col1，Col2和ColA，ColB上建立了索引，那么引擎将在其内存/缓存中为每个公共区域创建一整块记录，而不必重复返回到其他元素的原始数据页。

因此，即使您认为它可能不实用，但它仍然可以很好地处理大型表查询。此外，如果第一个表中的多个记录具有相同的Col1，Col2值，但表中的其他列具有不同的其他值，而第二个表中的多个ColA，ColB也具有类似的值，则您将获得笛卡尔结果。请考虑以下场景

Table1
Col1  Col2  OtherColumn
X     Y     blah1
X     Y     blah2
X     Y     blah3


Table2
ColA  ColB  OtherColumn
X     Y     second blah1
X     Y     second blah2
X     Y     second blah3

一个像您这样的简单查询

SELECT count(*)
FROM table1 t1,
     table2 t2
WHERE t1.col1 = t2.colA
  AND t1.col2 = t2.colB;

将导致计数为9。您有6M条记录和一个可能的笛卡尔结果？希望这能澄清你可能遇到的一些问题。