mysql左连接顺序，如何理解正确的算法

gupuwyp2  于 2021-06-19  发布在  Mysql
关注(0)|答案(1)|浏览(507)

我真的很困惑，写左连接的正确顺序是什么，事实上，如果我把一个左连接从位置a移到z，查询速度会快3倍，但是为什么呢？为什么优化器不能自己获得正确的顺序？
如何理解用n个左连接编写查询的正确顺序？
例子：
慢速查询：

  1. SELECT
  2.     c.idRichiesta, i.idImmobile
  3. FROM anagrafica AS cli
  4. INNER JOIN richieste AS c  ON c.idCliente = cli.idCliente
  5. INNER JOIN cat_richieste AS cr ON cr.idRichiesta = c.idRichiesta
  6. LEFT JOIN (
  7.     SELECT tmr.* FROM (
  8.         (
  9.             SELECT idRichiesta, id_comune, id_frazione, id_nazione
  10.             FROM comuni_richieste
  11.             WHERE idRichiesta = '129563'
  12.         )
  13.         UNION ALL
  14.         (
  15.             SELECT mr.idRichiesta, m.id_comune, m.id_frazione, m.id_nazione
  16.             FROM macro_aree_richieste AS mr
  17.             INNER JOIN macro_aree_dettaglio AS m ON m.id_macro=mr.id_macro
  18.             WHERE mr.idRichiesta = '129563'
  19.         )
  20.     ) AS tmr
  21.     GROUP BY tmr.idRichiesta, tmr.id_comune, tmr.id_frazione
  22. ) AS tcr ON tcr.idRichiesta = c.idRichiesta
  23. LEFT JOIN richieste_coordinate_p AS tcrp ON tcrp.idRichiesta = c.idRichiesta
  24. LEFT JOIN richieste_coordinate_c AS tcrc ON tcrc.idRichiesta = c.idRichiesta
  25. INNER JOIN immobili i on i.idCategoria = cr.idCategoria
  26. LEFT JOIN loc_geo as Ltg on Ltg.idImmobile = i.idImmobile
  27. LEFT JOIN stato_mediazione AS s  ON s.id=i.stato_mediazione
  28. WHERE
  29.     i.proposta_in_corso!='2'
  30.     AND (
  31.         (
  32.             (i.Motivazione = 'Vendita')
  33.             AND (  i.Prezzo_Richiesto >= ( c.Prezzo_Min * (100 - 30)/100 ) )
  34.             AND (( ( i.Prezzo_Richiesto * (100 - 30)/100 ) <= ( c.Prezzo_Max * (100 + 30)/100 ) ) or c.Prezzo_Max = 0 )
  35.         )
  36.         OR
  37.         (
  38.             (i.Motivazione ='Affitto/Vendita')
  39.             AND (
  40.                 (( 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')
  41.                 OR
  42.                 (( 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')
  43.             )
  44.         )
  45.     )
  46.     AND (
  47.         ( i.Citta = tcr.id_comune  AND ((i.ZonaCitta=tcr.id_frazione) OR (tcr.id_frazione = 0) ) )
  48.         OR (
  49.             Ltg.latitudine is not null and Ltg.longitudine is not null
  50.             AND ( tcrp.poligono is not null AND
  51.                 ( ST_CONTAINS(tcrp.poligono, POINT(Ltg.latitudine, Ltg.longitudine)) )
  52.                 OR ( tcrc.centro is not null AND
  53.                     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)
  54.                 )
  55.             )
  56.         )
  57.     )
  58.     AND i.Citta>0
  59.     AND (c.idRichiesta = '129563')
  60.     AND (s.id IS NULL OR s.vendibile='si')
  62. GROUP BY i.idImmobile

解释

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

快速查询：

  1. SELECT
  2.     c.idRichiesta, i.idImmobile
  3. FROM anagrafica AS cli
  4. INNER JOIN richieste AS c  ON c.idCliente = cli.idCliente
  5. INNER JOIN cat_richieste AS cr ON cr.idRichiesta = c.idRichiesta
  7. LEFT JOIN richieste_coordinate_p AS tcrp ON tcrp.idRichiesta = c.idRichiesta
  8. LEFT JOIN richieste_coordinate_c AS tcrc ON tcrc.idRichiesta = c.idRichiesta
  9. INNER JOIN immobili i on i.idCategoria = cr.idCategoria
  10. LEFT JOIN loc_geo as Ltg on Ltg.idImmobile = i.idImmobile
  11. LEFT JOIN stato_mediazione AS s  ON s.id=i.stato_mediazione
  12. LEFT JOIN (
  13.     SELECT tmr.* FROM (
  14.         (
  15.             SELECT idRichiesta, id_comune, id_frazione, id_nazione
  16.             FROM comuni_richieste
  17.             WHERE idRichiesta = '129563'
  18.         )
  19.         UNION ALL
  20.         (
  21.             SELECT mr.idRichiesta, m.id_comune, m.id_frazione, m.id_nazione
  22.             FROM macro_aree_richieste AS mr
  23.             INNER JOIN macro_aree_dettaglio AS m ON m.id_macro=mr.id_macro
  24.             WHERE mr.idRichiesta = '129563'
  25.         )
  26.     ) AS tmr
  27.     GROUP BY tmr.idRichiesta, tmr.id_comune, tmr.id_frazione
  28. ) AS tcr ON tcr.idRichiesta = c.idRichiesta
  29. WHERE
  30.     i.proposta_in_corso!='2'
  31.     AND (
  32.         (
  33.             (i.Motivazione = 'Vendita')
  34.             AND (  i.Prezzo_Richiesto >= ( c.Prezzo_Min * (100 - 30)/100 ) )
  35.             AND (( ( i.Prezzo_Richiesto * (100 - 30)/100 ) <= ( c.Prezzo_Max * (100 + 30)/100 ) ) or c.Prezzo_Max = 0 )
  36.         )
  37.         OR
  38.         (
  39.             (i.Motivazione ='Affitto/Vendita')
  40.             AND (
  41.                 (( 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')
  42.                 OR
  43.                 (( 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')
  44.             )
  45.         )
  46.     )
  47.     AND (
  48.         ( i.Citta = tcr.id_comune  AND ((i.ZonaCitta=tcr.id_frazione) OR (tcr.id_frazione = 0) ) )
  49.         OR (
  50.             Ltg.latitudine is not null and Ltg.longitudine is not null
  51.             AND ( tcrp.poligono is not null AND
  52.                 ( ST_CONTAINS(tcrp.poligono, POINT(Ltg.latitudine, Ltg.longitudine)) )
  53.                 OR ( tcrc.centro is not null AND
  54.                     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)
  55.                 )
  56.             )
  57.         )
  58.     )
  59.     AND i.Citta>0
  60.     AND (c.idRichiesta = '129563')
  61.     AND (s.id IS NULL OR s.vendibile='si')
  63. GROUP BY i.idImmobile

解释

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

正如您所看到的，唯一的区别是派生查询的左连接的位置。但是为什么第二个比第一个快呢？如何在解释输出中看到这一点？
随时问我你需要什么（索引列表，创建表等）。谢谢大家。

mysqloptimizationleft-joinexplainsql-execution-plan

1条答案

按热度按时间
gywdnpxw

gywdnpxw1#

你用的是什么版本？较新的版本将具体化派生表，并为它们发现一个合适的索引，如 <auto_key1> . 这有时是一种性能优势。旧版本将简单地重新执行 SELECT （和 UNION ，在您的情况下）每次它到达 LEFT JOIN .
不要使用 LEFT 除非你需要。以后一定要买 LEFT 即使缺少行(else下降 LEFT .) ORs 优化不好。看看你能不能摆脱他们。

赞(0）分享  回复(0）举报  2021-06-20
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com