我真的很困惑，写左连接的正确顺序是什么，事实上，如果我把一个左连接从位置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 | |
+------+--------------+------------------+--------+-------------------------------------------------------+-------------+---------+---------------------------------------+------+---------------------------------+--+正如您所看到的，唯一的区别是派生查询的左连接的位置。但是为什么第二个比第一个快呢？如何在解释输出中看到这一点？
随时问我你需要什么（索引列表，创建表等）。谢谢大家。
1条答案
按热度按时间gywdnpxw1#
你用的是什么版本？较新的版本将具体化派生表，并为它们发现一个合适的索引，如
<auto_key1>. 这有时是一种性能优势。旧版本将简单地重新执行SELECT（和UNION，在您的情况下）每次它到达LEFT JOIN.不要使用
LEFT除非你需要。以后一定要买LEFT即使缺少行(else下降LEFT.)ORs优化不好。看看你能不能摆脱他们。