mysql - 使用 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;

查询耗时7s，而b或c中只有一个JOIN时耗时0s。解释:

*************************** 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 中至多有一个匹配行。如果 MySQL 首先获取与 c1 文字匹配的 b 和 c 行，然后加入 a 基于 id，但它以 a 开头。

详细信息:

• MyISAM
• 所有列都有索引(_unique 是 UNIQUE)
• 所有列都不是 NULL

我尝试过的:

• 更改 JOIN 的顺序
• 将 WHERE 条件移动到 ON 子句
• 子选择 b.c1 和 c.c1 (WHERE b.id=(SELECT b.id FROM b WHERE c1='12345'))
• 为 b 和 c 使用索引

我知道我可以使用带有 UNION 的两个 SELECT 来做到这一点，但由于查询的生成方式，我需要尽可能避免这种情况。

编辑:添加 CREATE TABLE

CREATE TABLE 包含相关列。

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;

Answer 1

不是肯定的，但我很确定更改联接顺序并将条件移动到 on 子句并不重要。

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

索引中似乎有几列。因此，您只需连接 2 个表，就可以免费使用“有用”的索引。

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

一个索引id & c2 & c4id & c1 & c3 上的 b 索引id & c1 上的 c 索引