用LEFT JOIN优化JOIN

| 我在优化此查询时遇到问题：

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中的一个时，查询花费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中最多有1个匹配行。如果MySQL从获取与c1文字匹配的b和c行开始，然后基于id加入a，它将以a开始，它将更快。细节：我的ISAM 所有列都有索引（_unique是UNIQUE）所有列都不为空我尝试过的更改联接的顺序将WHERE条件移至ON子句子选择b.c1和c.c1（WHERE b.id =（SELECT b.id FROM b WHERE c1 = \'12345 \'）） b和c的使用索引我知道我可以使用带有UNION的两个SELECT来做到这一点，但是由于查询的生成方式，我需要尽可能避免这种情况。编辑：添加创建表带有相关列的16个字符。

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;

已邀请:

3 个回复

呢率篓舍烫

不是肯定的，但是我很确定更改联接顺序并将条件移动到on子句无关紧要。我不确定这里是否有足够的信息可以肯定知道，但是我猜\“所有列都有索引\”是您的问题。对于任何特定查询，每个表将仅使用一个索引。因此，如果您在a.id上有一个索引，在a.c2上有一个单独的索引，而在a.c4上有一个第三个索引。好吧，它只会用一个。索引中似乎有几列。因此，您只需要联接2个表，就可以免费使用“有用的”索引。我的建议是检查您的索引，并使它们覆盖此查询正在使用的适当字段（如果可能）。索引ID＆C2＆C4 b在id＆c1＆c3上的索引 ID和C1上的C索引

唤副埂侧壬

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

坊岔埠绵

OP在这里回答。我确定的是，MySQL首先读取效率较低的表的行为是所有LEFT JOIN的固有问题，其中效率较低的表位于左侧。根据MySQL手册中的LEFT JOIN和RIGHT JOIN Optimization： MySQL实现了“ 19”，如下所示：表B被设置为依赖于表A及其相关的所有表所以：

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

即使查询计划显示读取c效率更高，也总是会先读取a。切换表会使MySQL首先从ѭ2读取：

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 from提供一个视图。我没有测试。 TL; DR：更改表的顺序以将最有效率的表放在首位（如果结果集相同，而不考虑顺序）。或者将b和c合并到一个表中。或者可能创建一个结合了b和c的视图。

要回复问题请先登录或注册

用LEFT JOIN优化JOIN

3 个回复

发起人

mysql

sql

query_optimization

left_join

join

问题状态

用LEFT JOIN优化JOIN

与内容相关的链接

3 个回复

发起人

mysql

sql

query_optimization

left_join

join

问题状态