MySQL左加入顺序，如何理解正确的算法

Question

我真的很困惑写左联接的正确顺序，事实上，如果我将一个左联接从A位置移动到Z，查询速度会快3倍，但为什么呢？为什么优化器本身没有得到正确的订单？

如何理解用N个左联接写入查询的正确顺序？

示例：

慢速查询：

  SELECT
    c.idRichiesta, i.idImmobile
FROM anagrafica AS cli
INNER JOIN richieste AS c  ON c.idCliente = cli.idCliente
INNER JOIN cat_richieste AS cr ON cr.idRichiesta = c.idRichiesta
LEFT JOIN (
    SELECT tmr.* FROM (
        (
            SELECT idRichiesta, id_comune, id_frazione, id_nazione
            FROM comuni_richieste
            WHERE idRichiesta = '129563'
        )
        UNION ALL
        (
            SELECT mr.idRichiesta, m.id_comune, m.id_frazione, m.id_nazione
            FROM macro_aree_richieste AS mr
            INNER JOIN macro_aree_dettaglio AS m ON m.id_macro=mr.id_macro
            WHERE mr.idRichiesta = '129563'
        )
    ) AS tmr
    GROUP BY tmr.idRichiesta, tmr.id_comune, tmr.id_frazione
) AS tcr ON tcr.idRichiesta = c.idRichiesta
LEFT JOIN richieste_coordinate_p AS tcrp ON tcrp.idRichiesta = c.idRichiesta
LEFT JOIN richieste_coordinate_c AS tcrc ON tcrc.idRichiesta = c.idRichiesta
INNER JOIN immobili i on i.idCategoria = cr.idCategoria
LEFT JOIN loc_geo as Ltg on Ltg.idImmobile = i.idImmobile
LEFT JOIN stato_mediazione AS s  ON s.id=i.stato_mediazione
WHERE
    i.proposta_in_corso!='2'
    AND (
        (
            (i.Motivazione = 'Vendita')
            AND (  i.Prezzo_Richiesto >= ( c.Prezzo_Min * (100 - 30)/100 ) )
            AND (( ( i.Prezzo_Richiesto * (100 - 30)/100 ) <= ( c.Prezzo_Max * (100 + 30)/100 ) ) or c.Prezzo_Max = 0 )
        )
        OR
        (
            (i.Motivazione ='Affitto/Vendita')
            AND (
                (( i.Prezzo_Richiesto2 >= ( c.Prezzo_Min  * (100 - 30)/100 )) AND ((( i.Prezzo_Richiesto2 * (100 - 30)/100 )  <= ( c.Prezzo_Max * (100 + 30)/100) ) or c.Prezzo_Max=0) AND c.Motivo_Richiesta ='Affitto')
                OR
                (( i.Prezzo_Richiesto >= ( c.Prezzo_Min  * (100 - 30)/100 )) AND ((( i.Prezzo_Richiesto * (100 - 30)/100 )  <= ( c.Prezzo_Max * (100 + 30)/100) ) or c.Prezzo_Max=0)  AND c.Motivo_Richiesta ='Acquisto')
            )
        )
    )
    AND (
        ( i.Citta = tcr.id_comune  AND ((i.ZonaCitta=tcr.id_frazione) OR (tcr.id_frazione = 0) ) )
        OR (
            Ltg.latitudine is not null and Ltg.longitudine is not null
            AND ( tcrp.poligono is not null AND
                ( ST_CONTAINS(tcrp.poligono, POINT(Ltg.latitudine, Ltg.longitudine)) )
                OR ( tcrc.centro is not null AND
                    round (((acos( sin(( X(tcrc.centro) * 0.0174532925)) * sin((Ltg.latitudine * 0.0174532925)) + cos(( X(tcrc.centro) * 0.0174532925)) * cos((Ltg.latitudine * 0.0174532925)) * cos((( Y(tcrc.centro) - Ltg.longitudine) * 0.0174532925)))) * 57.2957795131) * 111.18957696, 2) <= (tcrc.raggio / 1000)
                )
            )
        )
    )
    AND i.Citta>0
    AND (c.idRichiesta = '129563')
    AND (s.id IS NULL OR s.vendibile='si')

GROUP BY i.idImmobile

解释

+------+--------------+------------------+--------+-------------------------------------------------------+-------------+---------+---------------------------------------+------+---------------------------------+--+
|  id  | select_type  |      table       |  type  |                     possible_keys                     |     key     | key_len |                  ref                  | rows |              Extra              |  |
+------+--------------+------------------+--------+-------------------------------------------------------+-------------+---------+---------------------------------------+------+---------------------------------+--+
| 1    | PRIMARY      | tcrp             | system | idRichiesta                                           | NULL        | NULL    | NULL                                  | 0    | const row not found             |  |
| 1    | PRIMARY      | tcrc             | system | idRichiesta                                           | NULL        | NULL    | NULL                                  | 0    | const row not found             |  |
| 1    | PRIMARY      | c                | const  | PRIMARY,idCliente,Prezzo_Max,Motivo_Richiesta         | PRIMARY     | 4       | const                                 | 1    | Using temporary; Using filesort |  |
| 1    | PRIMARY      | cli              | const  | PRIMARY                                               | PRIMARY     | 4       | const                                 | 1    | Using index                     |  |
| 1    | PRIMARY      | <derived2>       | ref    | <auto_key1>                                           | <auto_key1> | 4       | const                                 | 0    | Using where                     |  |
| 1    | PRIMARY      | cr               | ref    | idRichiesta,idCategoria                               | idRichiesta | 4       | const                                 | 9    | Using index condition           |  |
| 1    | PRIMARY      | i                | ref    | PRIMARY,idProprietario,id_cantiere,codice_agenzia,... | idCategoria | 5       | test_import2.cr.idCategoria     | 2780 | Using where                     |  |
| 1    | PRIMARY      | Ltg              | ref    | idImmobile                                            | idImmobile  | 4       | test_import2.i.idImmobile       | 1    | Using where                     |  |
| 1    | PRIMARY      | s                | eq_ref | PRIMARY                                               | PRIMARY     | 4       | test_import2.i.stato_mediazione | 1    | Using where                     |  |
| 2    | DERIVED      | <derived3>       | ALL    | NULL                                                  | NULL        | NULL    | NULL                                  | 9    | Using temporary; Using filesort |  |
| 3    | DERIVED      | comuni_richieste | ref    | idRichiesta                                           | idRichiesta | 4       | const                                 | 7    | NULL                            |  |
| 4    | UNION        | mr               | ref    | idRichiesta,id_macro                                  | idRichiesta | 4       | const                                 | 1    | NULL                            |  |
| 4    | UNION        | m                | ref    | id_macro                                              | id_macro    | 4       | test_import2.mr.id_macro        | 1    | NULL                            |  |
| NULL | UNION RESULT | <union3,4>       | ALL    | NULL                                                  | NULL        | NULL    | NULL                                  | NULL | Using temporary                 |  |
+------+--------------+------------------+--------+-------------------------------------------------------+-------------+---------+---------------------------------------+------+---------------------------------+--+

快速查询

     SELECT
    c.idRichiesta, i.idImmobile
FROM anagrafica AS cli
INNER JOIN richieste AS c  ON c.idCliente = cli.idCliente
INNER JOIN cat_richieste AS cr ON cr.idRichiesta = c.idRichiesta

LEFT JOIN richieste_coordinate_p AS tcrp ON tcrp.idRichiesta = c.idRichiesta
LEFT JOIN richieste_coordinate_c AS tcrc ON tcrc.idRichiesta = c.idRichiesta
INNER JOIN immobili i on i.idCategoria = cr.idCategoria
LEFT JOIN loc_geo as Ltg on Ltg.idImmobile = i.idImmobile
LEFT JOIN stato_mediazione AS s  ON s.id=i.stato_mediazione
LEFT JOIN (
    SELECT tmr.* FROM (
        (
            SELECT idRichiesta, id_comune, id_frazione, id_nazione
            FROM comuni_richieste
            WHERE idRichiesta = '129563'
        )
        UNION ALL
        (
            SELECT mr.idRichiesta, m.id_comune, m.id_frazione, m.id_nazione
            FROM macro_aree_richieste AS mr
            INNER JOIN macro_aree_dettaglio AS m ON m.id_macro=mr.id_macro
            WHERE mr.idRichiesta = '129563'
        )
    ) AS tmr
    GROUP BY tmr.idRichiesta, tmr.id_comune, tmr.id_frazione
) AS tcr ON tcr.idRichiesta = c.idRichiesta
WHERE
    i.proposta_in_corso!='2'
    AND (
        (
            (i.Motivazione = 'Vendita')
            AND (  i.Prezzo_Richiesto >= ( c.Prezzo_Min * (100 - 30)/100 ) )
            AND (( ( i.Prezzo_Richiesto * (100 - 30)/100 ) <= ( c.Prezzo_Max * (100 + 30)/100 ) ) or c.Prezzo_Max = 0 )
        )
        OR
        (
            (i.Motivazione ='Affitto/Vendita')
            AND (
                (( i.Prezzo_Richiesto2 >= ( c.Prezzo_Min  * (100 - 30)/100 )) AND ((( i.Prezzo_Richiesto2 * (100 - 30)/100 )  <= ( c.Prezzo_Max * (100 + 30)/100) ) or c.Prezzo_Max=0) AND c.Motivo_Richiesta ='Affitto')
                OR
                (( i.Prezzo_Richiesto >= ( c.Prezzo_Min  * (100 - 30)/100 )) AND ((( i.Prezzo_Richiesto * (100 - 30)/100 )  <= ( c.Prezzo_Max * (100 + 30)/100) ) or c.Prezzo_Max=0)  AND c.Motivo_Richiesta ='Acquisto')
            )
        )
    )
    AND (
        ( i.Citta = tcr.id_comune  AND ((i.ZonaCitta=tcr.id_frazione) OR (tcr.id_frazione = 0) ) )
        OR (
            Ltg.latitudine is not null and Ltg.longitudine is not null
            AND ( tcrp.poligono is not null AND
                ( ST_CONTAINS(tcrp.poligono, POINT(Ltg.latitudine, Ltg.longitudine)) )
                OR ( tcrc.centro is not null AND
                    round (((acos( sin(( X(tcrc.centro) * 0.0174532925)) * sin((Ltg.latitudine * 0.0174532925)) + cos(( X(tcrc.centro) * 0.0174532925)) * cos((Ltg.latitudine * 0.0174532925)) * cos((( Y(tcrc.centro) - Ltg.longitudine) * 0.0174532925)))) * 57.2957795131) * 111.18957696, 2) <= (tcrc.raggio / 1000)
                )
            )
        )
    )
    AND i.Citta>0
    AND (c.idRichiesta = '129563')
    AND (s.id IS NULL OR s.vendibile='si')

GROUP BY i.idImmobile

解释

+------+--------------+------------------+--------+-------------------------------------------------------+-------------+---------+---------------------------------------+------+---------------------------------+--+
|  id  | select_type  |      table       |  type  |                     possible_keys                     |     key     | key_len |                  ref                  | rows |              Extra              |  |
+------+--------------+------------------+--------+-------------------------------------------------------+-------------+---------+---------------------------------------+------+---------------------------------+--+
| 1    | PRIMARY      | tcrp             | system | idRichiesta                                           | NULL        | NULL    | NULL                                  | 0    | const row not found             |  |
| 1    | PRIMARY      | tcrc             | system | idRichiesta                                           | NULL        | NULL    | NULL                                  | 0    | const row not found             |  |
| 1    | PRIMARY      | c                | const  | PRIMARY,idCliente,Prezzo_Max,Motivo_Richiesta         | PRIMARY     | 4       | const                                 | 1    | Using temporary; Using filesort |  |
| 1    | PRIMARY      | cli              | const  | PRIMARY                                               | PRIMARY     | 4       | const                                 | 1    | Using index                     |  |
| 1    | PRIMARY      | cr               | ref    | idRichiesta,idCategoria                               | idRichiesta | 4       | const                                 | 9    | Using index condition           |  |
| 1    | PRIMARY      | i                | ref    | PRIMARY,idProprietario,id_cantiere,codice_agenzia,... | idCategoria | 5       | test_import2.cr.idCategoria     | 2780 | Using where                     |  |
| 1    | PRIMARY      | Ltg              | ref    | idImmobile                                            | idImmobile  | 4       | test_import2.i.idImmobile       | 1    | NULL                            |  |
| 1    | PRIMARY      | s                | eq_ref | PRIMARY                                               | PRIMARY     | 4       | test_import2.i.stato_mediazione | 1    | Using where                     |  |
| 1    | PRIMARY      | <derived2>       | ref    | <auto_key1>                                           | <auto_key1> | 4       | test_import2.cr.idRichiesta     | 0    | Using where                     |  |
| 2    | DERIVED      | <derived3>       | ALL    | NULL                                                  | NULL        | NULL    | NULL                                  | 9    | Using temporary; Using filesort |  |
| 3    | DERIVED      | comuni_richieste | ref    | idRichiesta                                           | idRichiesta | 4       | const                                 | 7    | NULL                            |  |
| 4    | UNION        | mr               | ref    | idRichiesta,id_macro                                  | idRichiesta | 4       | const                                 | 1    | NULL                            |  |
| 4    | UNION        | m                | ref    | id_macro                                              | id_macro    | 4       | test_import2.mr.id_macro        | 1    | NULL                            |  |
| NULL | UNION RESULT | <union3,4>       | ALL    | NULL                                                  | NULL        | NULL    | NULL                                  | NULL | Using temporary                 |  |
+------+--------------+------------------+--------+-------------------------------------------------------+-------------+---------+---------------------------------------+------+---------------------------------+--+

如您所见，唯一的区别是左联接与派生查询的位置。但是为什么第二个比第一个更快呢？如何在解释输出中看到这一点？

请随便问我你需要什么(索引列表，创建表等)。多亏了所有人。

Answer 1

你用的是什么版本？较新的版本将实现派生表，并为它们找到一个合适的索引，如<auto_key1>所示。这有时是一种性能好处。旧版本将在每次SELECT到达LEFT JOIN时简单地重新执行它(在您的情况下是UNION )。

除非你需要LEFT，否则不要使用它。即使缺少行，也要确保在LEFT之后需要这些内容。(否则放弃LEFT。)

ORs不能很好地优化。看看你能不能摆脱他们中的任何一个。