优化LEFT JOIN的JOIN操作

Question

我在优化这个查询时遇到了困难：

SELECT a.id
FROM a
JOIN b ON a.id=b.id
LEFT JOIN c ON a.id=c.id
WHERE
   (b.c1='12345' OR c.c1='12345')
   AND (a.c2=0 OR b.c3=1)
   AND a.c4='active'
GROUP BY a.id;

当只有b或c其中一个被连接时，查询只需0秒，但是当两者同时被连接时，查询需要7秒。以下是详细解释：

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: ref
possible_keys: PRIMARY(id),c4,c2
          key: c4
      key_len: 1
          ref: const
         rows: 80775
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
         type: ref
possible_keys: id_c1_unique,id
          key: id_c1
      key_len: 4
          ref: database.a.id
         rows: 1
        Extra: Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: ref
possible_keys: id_c1_unique,id,c1,c3
          key: id
      key_len: 4
          ref: database.a.id
         rows: 2
        Extra: Using where

从表b中总是恰好匹配1行，而从表c中最多只匹配1行。如果MySQL先获取与字面值匹配的b和c行，然后基于id连接，速度会更快，但它却从开始。

细节：

• MyISAM
• 所有列都有索引（_unique为唯一索引）
• 所有列都不为空

我尝试过：

• 更改JOIN的顺序
• 将WHERE条件移动到ON子句中
• 对b.c1和c.c1使用子查询（WHERE b.id = (SELECT b.id FROM b WHERE c1 = '12345'））
• 对b和c使用USE INDEX

我知道可以使用两个带UNION的SELECT来完成此操作，但如果可能的话，我需要避免这样做，因为查询是如何生成的。

编辑：添加CREATE TABLEs

使用相关列创建的CREATE TABLEs。

CREATE TABLE `a` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c2` tinyint(1) NOT NULL,
  `c4` enum('active','pending','closed') NOT NULL,
  PRIMARY KEY (`id`),
  KEY `c2` (`c2`)
  KEY `c4` (`c4`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `b` (
    `b_id` int(11) NOT NULL AUTO_INCREMENT,
    `id` int(11) NOT NULL DEFAULT '0',
    `c1` int(11) NOT NULL,
    `c3` tinyint(1) NOT NULL,
    PRIMARY KEY (`b_id`),
    UNIQUE KEY `id_c1_unique` (`id`,`c1`),
    KEY `c1` (`c1`),
    KEY `c3` (`c3`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `c` (
    `c_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `id` int(11) NOT NULL,
    `c1` int(11) NOT NULL,
    PRIMARY KEY (`c_id`),
    UNIQUE KEY `id_c1_unique` (`id`,`c1`),
    KEY `id` (`id`),
    KEY `c1` (`c1`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

- Byron

---

我有一个愚蠢的问题：你在连接的所有字段上都有索引吗？ - M.R.

@M.R. 是的，查询中的所有列都有索引。 - Byron

你能发布 a 的结构吗？a.id 是自增字段吗？ - Nemoden

查询没有问题。只是80k行和GROUP BY。如果这个表既用于读取又用于更新，你会遇到这样的性能问题。我希望这个表是InnoDB？这很糟糕：Using where; Using temporary; Using filesort。我会在一个临时表上执行这个查询，它将是SELECT * FROM a WHERE c4 = 'active'。我还会把c4转换为int，这样1就表示active状态。 - Nemoden

Answer 1

select STRAIGHT_JOIN 
      distinct a.ID
   from
      a
         join b
            on a.ID = b.ID
         left join c
            on a.id = c.id
            and c.c1 = '12345'
   where
          a.C4 = 'active'
      and ( a.c2 = 0 or b.c3 = 1 )
      and ( b.c1 = '12345' or c.c1='12345' )

- DRapp

---

你确定 c.c1='12345' 应该在 LEFT JOIN 条件中吗？原始查询似乎是在当 b.c1='12345'，a.id=b.id=c.id 且 c.c1 是任意值时从 c 中提取一行。 - Andrew Lazarus

@Andrew：我认为两个查询将产生相同的结果。从c中获取的行与外连接一起，所以如果b.c1 = '12345'，它们根本不重要。如果b.c1 <> '12345'，那么唯一重要的是c.c1 = '12345'。因此，在连接条件中添加这个条件是正确的。在WHERE子句中，我可能会将c.c1 = '12345'更改为c.id IS NOT NULL，但这可能只是个人口味问题。 - Andriy M

@Andrew Lazarus，@Andriy M在他的解释中是正确的。我刚醒来，所以无法早些时候回复 :) - DRapp

我想我现在明白了。在 distinct a.id 之后，您使用 c.c1 的条件得到了相同的集合，但如果是 SELECT * FROM…，情况可能不会这样。 - Andrew Lazarus

@Andrew Lazarus，我在两个表中都使用了相同的“c1”条件，因为这是您从该表中关心的全部内容。因此，如果“c”表中存在多个a.ID，而您不想要它们，并且我不想使结果集膨胀...我需要将其保留在最终的WHERE子句中，因为您对“b”表进行了“join”，但是“b”或“c”都可以符合条件。您缩写的数据隐藏了您想要获取的内容，这可能会更容易地通过真实的表/列引用来解决。 - DRapp

Answer 2

OP在这里回答。

我所确定的是，MySQL读取效率较低的表格的行为是所有LEFT JOIN的固有问题，其中效率较低的表格位于左侧。根据MySQL手册中的LEFT JOIN和RIGHT JOIN优化：

MySQL将A LEFT JOIN B join_condition实现如下：

• 表格B被设置为依赖于表格A和A所依赖的所有表格

因此：

SELECT a.id
FROM a
LEFT JOIN c ON a.id=c.id
GROUP BY a.id;

无论查询计划显示读取c更有效，MySQL始终会首先读取a。交换表格会导致MySQL首先从c读取：

SELECT a.id
FROM c
LEFT JOIN a ON c.id=a.id
GROUP BY a.id;

在我的情况下，两个查询返回相同的结果。显然，我错过了某些概念，需要在进行LEFT JOIN时始终先读取左侧表。对于某些查询而言，右侧表似乎也可以很容易地被首先读取，并且MySQL仍然可以生成相同的结果（并非所有LEFT JOIN都是如此）。如果可能的话，这种优化可能早就被添加了，所以我想我只是缺少了这个概念。

最终，交换表的顺序对我来说不是一个好的解决方案。我最终将b和c合并成一个单独的表，这简化了应用程序，并且本来就应该这样做。使用单个表，我可以执行JOIN而不是LEFT JOIN，从而完全避免了这个问题。

另一个可能的解决方案是创建一个包含两个表的视图，从而提供一个单一的视图进行JOIN。不过我没有测试过。

简而言之：更改表的顺序以使最有效的表排在前面（如果结果集无论顺序如何都相同）。或者将b和c合并成一个单独的表。或者可能创建一个结合b和c的视图。

- Byron

---

未来，看起来您在原始查询中使用了通用的“a”、“b”和“c”表隐藏了表的上下文。即使某些内容可能包含“机密”信息，大多数人也会使用通用的名称，例如“Customer.ID”或“Account.ID”等。不知道上下文，别人（包括我自己）帮助您变得更加困难。此外，通过了解数据并找到最有效的查询（子集/键基础），应始终首先列出... 此外，添加STRAIGHT_JOIN有助于强制执行您选择链接表的顺序。 - DRapp

Answer 3

不确定，但我相当肯定更改连接顺序并将where条件移动到on子句中不重要。

我不确定这里是否有足够的信息来确定，但我猜测“所有列都有索引”是您的问题。对于任何特定的查询，每个表只会使用一个索引。因此，如果您在a.id上有一个索引，在a.c2上有一个单独的索引，在a.c4上有第三个索引。好吧，它只会使用一个。

索引中可能有几列。因此，如果您仅加入2个表，则可以免费使用“有用”的索引。

我的建议是检查您的索引并使它们覆盖此查询正在使用的正确字段（如果可能）。

a索引id＆c2＆c4 b索引id＆c1＆c3 c索引id＆c1

- Jody