连接性能: Oracle与MySQL

Question

给定一个简化为以下形式的查询：

select b.field1
from table_a a
    inner join table_b b on b.field1 = a.field1
    left join table_c c on c.field1 = a.field1
    left join table_d d on d.field1 = b.field1
    left join table_e e on e.field1 = b.field6
group by b.field1,
         b.field2,
         b.field3,
         b.field4,
         b.field5,

         e.field2,
         e.field3
;

在有一定数据量的情况下，它在Oracle中运行20秒。Oracle中没有任何内容被索引。迁移到MySQL后，查询不想完成(在几分钟内执行)。每个有问题的字段都会在MySQL中建立索引。Explain告诉我们一切都很好。

在仍然不起作用后，分组字段获得了多列索引。还是一无所获。

MySQL性能仍然存在巨大的漏洞，这会是什么问题？有没有什么方法可以加快速度呢？

Answer 1

Oracle能够进行散列联接和合并联接，而MySQL不能。

因为您的表没有以任何方式进行过滤，所以哈希连接将是执行连接的最有效的方法，特别是在您没有任何索引的情况下。

对于嵌套循环，即使所有连接字段都已被索引，MySQL也需要在循环中的前导表中的每个值上执行索引查找(每次从根索引页开始)，然后执行表查找以检索记录，然后对每个连接的表重复此操作。这涉及到大量的随机搜索。

另一方面，哈希连接需要扫描一次较小的表(构建一个哈希表)，然后扫描一次较大的表(搜索构建的哈希表)。这涉及到更快的顺序扫描。

此外，对于嵌套循环，左连接表只能被驱动(在内部循环中扫描)，而使用散列连接时，两端的表可以是前导的(扫描的)或驱动的(散列然后搜索)。这也会影响性能。

虽然MySQL的优化器确实支持一些其他引擎所缺乏的方便的技巧，但与其他引擎相比，它的能力非常有限，目前既不支持散列连接，也不支持合并连接。因此，这样的查询在MySQL上很可能很慢，即使它在相同数据的其他引擎上很快。