mysql - LEFT JOIN 的位置如何影响查询和优化器？

Question

当我启动两个不同的查询时，Mysql 出现了一种奇怪的行为，其中一个微小的差异是一个左连接的位置。

慢速查询:

SELECT i.id_affiliate, i.id_franchising, i.Codice
FROM network_configuration_affiliate AS c
INNER JOIN franchising AS fr ON fr.id = c.id_franchising
INNER JOIN network_selected_car AS i ON c.id_affiliate = i.id_affiliate
INNER JOIN (
  select T1.id_car, T1.id_network, T1.id_franchising, T1.id_agencie
  from network_car_destinations as T1
  where T1.id_network='12' and ( T1.id_franchising = 968 or T1.id_franchising = 974 )
) AS n ON n.id_franchising=i.id_franchising AND n.id_car=i.id_car AND c.id_network=n.id_network
INNER JOIN affiliate_tipologies AS t ON t.id_tipology_ag=i.idCategory AND t.id_franchising=i.id_franchising
INNER JOIN network_assoc_tipologies AS p ON p.id_network=c.id_network AND p.id_default=t.id_tipology_net
LEFT JOIN  network_conf_users as ce on ce.id_affiliate = i.id_affiliate and ce.id_user = i.id_user
WHERE c.id_network='12' and c.code_affiliate='69842' AND c.configured = 'yes' AND i.Code_car not like '' and p.code != '0'
GROUP BY i.Code_car

快速:

select T2.* from (
  SELECT i.`id_affiliate`, i.id_franchising, i.Code_car, i.id_user
  FROM network_configuration_affiliate  AS c
  INNER JOIN franchising AS fr ON fr.id = c.id_franchising
  INNER JOIN network_selected_car  AS i ON c.`id_affiliate` = i.`id_affiliate`
  INNER JOIN (
    select T1.id_car, T1.id_network, T1.id_franchising, T1.id_agencie
    from network_car_destinations  as T1
    where T1.id_network='12' and ( T1.id_franchising = 968 or T1.id_franchising = 974 )
  ) AS n ON n.id_franchising=i.id_franchising AND n.id_car=i.id_car AND c.id_network=n.id_network
  INNER JOIN affiliate_tipologies  AS t ON t.id_tipology_ag=i.idCategory AND t.id_franchising=i.id_franchising
  INNER JOIN network_assoc_tipologies AS p ON p.id_network=c.id_network AND p.id_default=t.id_tipology_net

  WHERE c.id_network='12' and c.code_affiliate='69842' AND c.configured = 'yes' AND i.Code_car not like ''
  GROUP BY i.Code_car
) as T2
LEFT JOIN  network_conf_users as ce on ce.`id_affiliate` =  T2.`id_affiliate` and ce.id_user = T2.id_user

WHERE c.id_network='12' and c.code_affiliate='69842' AND c.configured = 'yes' AND i.Code_car not like '' and p.code != '0'
GROUP BY i.Code_car

EXPLAIN几乎是一样的，两个查询的结果是相同的，但是第一个查询需要20秒才能结束，第二个查询需要0.02秒，左连接的位置如何影响查询的执行？

解释 - 慢速查询:

id  select_type     table       type        possible_keys                                       key                         key_len ref                 rows    filtered    Extra
1   PRIMARY         c           index_merge id_affiliate,id_network,configured,code_affiliate   code_affiliate,id_network   203,5   NULL                1       100.00  Using intersect(code_affiliate,id_network); Using where; Using temporary; Using filesort
1   PRIMARY         fr          eq_ref      PRIMARY                                             PRIMARY                     4       c.id_franchising    1       100.00  Using index
1   PRIMARY         <derived2>  ref         <auto_key1>                                         <auto_key1>                 5       const               10      100.00  Using where
1   PRIMARY         t           ref         id_franchising,id_tipology_ag,id_tipology_net       id_franchising              5       n.id_franchising    13      100.00  Using where
1   PRIMARY         p           ref         id_network,id_default                               id_default                  5       t.id_tipology       26      100.00  Using where
1   PRIMARY         i           ref         id_car,id_affiliate,id_franchising,idCategory       id_car                      5       n.id_car            3       100.00  Using where
1   PRIMARY         ce          ALL         id_affiliate,id_user                                NULL                        NULL    NULL                4       75.00   Using where; Using join buffer (Block Nested Loop)
2   DERIVED         T1          ref         id_franchising,id_network                           id_network                  5       const           136952      100.00  Using where

显示警告:

Level   Code    Message
Note    1003    /* select#1 */
select  `i`.`id_affiliate` AS `id_affiliate`,
        `i`.`id_franchising` AS `id_franchising`,
        `i`.`Code_car` AS `Code_car`
    from  `network_configuration_affiliate` `c`
    join  `franchising` `fr`
    join  `network_selected_car` `i`
    join  (/* select#2 */ 
        select  `T1`.`id_car` AS `id_car`,
                `T1`.`id_network` AS `id_network`,
                `T1`.`id_franchising` AS `id_franchising`,
                `T1`.`id_agencie` AS `id_agencie`
            from  `network_car_destinations` `T1`
            where  ((`T1`.`id_network` = '12')
                      and  ((`T1`.`id_franchising` = 968)
                              or  (`T1`.`id_franchising` = 974)))
          ) `n`
    join  `affiliate_tipologies` `t`
    join  `network_assoc_tipologies` `p`
    left join  `network_conf_users` `ce` on(((`ce`.`id_user` = `i`.`id_user`)
                      and  (`ce`.`id_affiliate` = `c`.`id_affiliate`))
                          )
    where  ((`fr`.`id` = `c`.`id_franchising`)
              and  (`i`.`id_affiliate` = `c`.`id_affiliate`)
              and  (`i`.`id_car` = `n`.`id_car`)
              and  (`t`.`id_franchising` = `n`.`id_franchising`)
              and  (`i`.`id_franchising` = `n`.`id_franchising`)
              and  (`i`.`idCategory` = `t`.`id_tipology_ag`)
              and  (`p`.`id_default` = `t`.`id_tipology_net`)
              and  (`n`.`id_network` = `c`.`id_network`)
              and  (`p`.`id_network` = `c`.`id_network`)
              and  (`c`.`configured` = 'yes')
              and  (`c`.`code_affiliate` = '69842')
              and  (`c`.`id_network` = '12')
              and  (not((`i`.`Code` like '')))
              and  (`p`.`code` <> '0')
           )
    group by  `i`.`Code_car`

快速查询:

id  select_type     table       type            possible_keys                                                   key                                     key_len     ref                 rows    filtered    Extra
1   PRIMARY         <derived2>  ALL             NULL                                                            NULL                                    NULL        NULL                7280    100.00      NULL
1   PRIMARY         ce          ALL             id_affiliate,id_user                                            NULL                                    NULL        NULL                4       75.00       Using where; Using join buffer (Block Nested Loop)
2   DERIVED         c           index_merge     id_affiliate,id_network,configured,code_affiliate               code_affiliate,id_network               203,5       NULL                1       100.00      Using intersect(codice_affiliato,id_network); Using where; Using temporary; Using filesort
2   DERIVED         fr          eq_ref          PRIMARY                                                         PRIMARY                                 4           c.id_franchising    1         100.00    Using index
2   DERIVED         <derived3>  ref             <auto_key1>                                                     <auto_key1>                             5           const               10      100.00      Using where
2   DERIVED         i           ref             id_car,id_affiliate,id_franchising,idCategory                   id_car                                  5           n.id_car            2       100.00      Using where
2   DERIVED         t           ref             id_franchising,id_tipology_ag,id_tipology_net                   id_franchising                          5           n.id_franchising    14      100.00      Using where
2   DERIVED         p           ref             id_network,id_default                                           id_default                              5           t.id_tipology_net   26      100.00      Using where
3   DERIVED         T1          ref             id_franchising,id_network                                       id_network                              5           const               133324  100.00      Using where

显示警告:

Level   Code    Message
Note    1003    /* select#1 */
select  `T2`.id_affiliate AS id_affiliate,
        `T2`.`id_franchising` AS `id_franchising`,
        `T2`.Code_car AS Code_car,`T2`.id_user AS id_user
    from  (/* select#2 */ 
        select  `i`.id_affiliate AS id_affiliate,
                `i`.`id_franchising` AS `id_franchising`,
                `i`.Code_car AS Code_car,`i`.id_user AS id_user
            from  network_configuration_affiliate `c`
            join  `franchising` `fr`
            join  network_selected_car `i`
            join  (/* select#3 */ 
                select  `T1`.id_car AS id_car,
                        `T1`.`id_network` AS `id_network`,
                        `T1`.`id_franchising` AS `id_franchising`,
                        `T1`.`id_agencie` AS `id_agencie`
                    from  network_car_destinations `T1`
                    where  ((`T1`.`id_network` = '12')
                              and  ((`T1`.`id_franchising` = 968)
                                      or  (`T1`.`id_franchising` = 974)))
                  ) `n`
            join  affiliate_tipologies `t`
            join  network_assoc_tipologies `p`
            where  ((`fr`.`id` = `c`.`id_franchising`)
                      and  (`i`.id_affiliate = `c`.id_affiliate)
                      and  (`i`.id_car = `n`.id_car)
                      and  (`i`.`id_franchising` = `n`.`id_franchising`)
                      and  (`t`.`id_franchising` = `n`.`id_franchising`)
                      and  (`t`.`id_tipology_ag` = `i`.`idCategory`)
                      and  (`p`.`id_default` = `t`.`id_tipology_net`)
                      and  (`n`.`id_network` = `c`.`id_network`)
                      and  (`p`.`id_network` = `c`.`id_network`)
                      and  (`c`.`configured` = 'yes')
                      and  (`c`.`code_affiliate` = '69842')
                      and  (`c`.`id_network` = '12')
                      and  (not((`i`.Code_car like '')))
                   )
            group by  `i`.Code_car
          ) `T2`
    left join  network_conf_users `ce` on(((`ce`.id_affiliate = `T2`.id_affiliate)
                      and  (`ce`.id_user = `T2`.id_user))
                          )
    where  1 

Answer 1

左连接查找左侧的字段并从右侧检索这些字段，而内连接将查找所有字段。通过不需要组织额外的列来保持简单，查询完成得更快。