Mysql Lateral Query

[MySQL] Mysql Lateral Query

by

안녕하세요 오늘은 베스핀글로벌 D&A실 왕승준님이 작성해 주신 Mysql Lateral Query에 대해 알아보겠습니다 궁금하신 부분이 있으시면 댓글을 달아주세요 🙂

— Maria -> Mysql Explain 변경점

Maria에선 자동으로 외부 조인 조건을 인라인뷰로 가져와 참조 가능하나 My는 해당 기능을 지원하지 않음.
Emart Smartorder Service Maria -> Mysql8.0으로 마이그레이션 진행하면서 생긴 이슈 공유 

[AS – IS]

  FROM
                XX_TRANS_MST TRANS,
                XX_PRCHS_MST MST left outer join XX_CANCEL_HISTORY HIS on MST.PRCHS_SEQ = HIS.PRCHS_SEQ   
                XX_PRCHS_DTL DTL,
                XX_SO_PAYMENT_DTL PAY,
                (
                 SELECT
                      TSPD.PRCHS_SEQ
                      , REPLACE(SUM(PLU_PRICE * QTY), ‘,’, ”)AS ORDER_AMT
                 FROM
                     XX_PRCHS_DTL TSPD                                        
                  GROUP BY
TSPD.PRCHS_SEQ
) SRC
WHERE
TRANS.TRANS_SEQ = MST.TRANS_SEQ
AND MST.PRCHS_SEQ = DTL.PRCHS_SEQ
AND MST.PRCHS_SEQ = SRC.PRCHS_SEQ
AND MST.PRCHS_SEQ = PAY.PRCHS_SEQ
AND MST.PAYMENT_NAME IS NOT NULL

id|select_type|table     |partitions|type  |possible_keys                                          |key                            |key_len|ref                        |rows  |filtered|Extra                                       |
--+-----------+----------+----------+------+-------------------------------------------------------+-------------------------------+-------+---------------------------+------+--------+--------------------------------------------+
 1|PRIMARY    |<derived2>|          |ALL   |                                                       |                               |       |                           |     8|    12.5|Using where                                 |
 2|DERIVED    |TRANS     |          |ref   |PRIMARY,XX_trans_mst_idx01                             |XX_trans_mst_idx01             |603    |const                      |     2|   100.0|Using index; Using temporary; Using filesort|
 2|DERIVED    |MST       |          |ref   |PRIMARY,XX_prchs_mst_idx01,XX_prchs_mst_PRCHS_SEQ_IDX  |XX_prchs_mst_idx01             |93     |smart_order.TRANS.TRANS_SEQ|     1|     9.0|Using where                                 |
 2|DERIVED    |HIS       |          |ref   |XX_cancel_history_PRCHS_SEQ_IDX                        |XX_cancel_history_PRCHS_SEQ_IDX|63     |smart_order.MST.PRCHS_SEQ  |     1|   100.0|Using where                                 |
 2|DERIVED    |PAY       |          |eq_ref|XX_so_payment_dtl_idx01,XX_so_payment_dtl_PRCHS_SEQ_IDX|XX_so_payment_dtl_PRCHS_SEQ_IDX|93     |smart_order.MST.PRCHS_SEQ  |     1|   100.0|Using where                                 |
 2|DERIVED    |<derived3>|          |ref   |<auto_key0>                                            |<auto_key0>                    |93     |smart_order.MST.PRCHS_SEQ  |    10|   100.0|                                            |
 2|DERIVED    |DTL       |          |ref   |XX_prchs_dtl_idx01                                     |XX_prchs_dtl_idx01             |93     |smart_order.MST.PRCHS_SEQ  |     2|   100.0|                                            |
 3|DERIVED    |TSPD      |          |index |XX_prchs_dtl_idx01                                     |XX_prchs_dtl_idx01             |99     |                           |210540|   100.0|                                            |

[TO-BE]

  FROM
                XX_TRANS_MST TRANS,
                XX_PRCHS_MST MST left outer join XX_CANCEL_HISTORY HIS on MST.PRCHS_SEQ = HIS.PRCHS_SEQ   
                XX_PRCHS_DTL DTL,
                XX_SO_PAYMENT_DTL PAY,
      LATERAL         (
                 SELECT
                      TSPD.PRCHS_SEQ
                      , REPLACE(SUM(PLU_PRICE * QTY), ',', '')AS ORDER_AMT
                 FROM
                     XX_PRCHS_DTL TSPD                                        
     WHERE MST.PRCHS_SEQ = TSPD_PRCHS_SEQ
                  GROUP BY
TSPD.PRCHS_SEQ
) SRC
WHERE
TRANS.TRANS_SEQ = MST.TRANS_SEQ
AND MST.PRCHS_SEQ = DTL.PRCHS_SEQ

AND MST.PRCHS_SEQ = SRC.PRCHS_SEQ <- 불필요 제거
AND MST.PRCHS_SEQ = PAY.PRCHS_SEQ
AND MST.PAYMENT_NAME IS NOT NULL

lateral 과 where절에 있던 조인조건을 안에 기술함으로서 참조가능

 id|select_type      |table     |partitions|type  |possible_keys                                          |key                            |key_len|ref                        |rows|filtered|Extra                                       |
--+-----------------+----------+----------+------+-------------------------------------------------------+-------------------------------+-------+---------------------------+----+--------+--------------------------------------------+
 1|PRIMARY          |<derived2>|          |ALL   |                                                       |                               |       |                           |   2|    50.0|Using where                                 |
 2|DERIVED          |TRANS     |          |ref   |PRIMARY,XX_trans_mst_idx01                             |XX_trans_mst_idx01             |603    |const                      |   2|   100.0|Using index; Using temporary; Using filesort|
 2|DERIVED          |MST       |          |ref   |PRIMARY,XX_prchs_mst_idx01,XX_prchs_mst_PRCHS_SEQ_IDX  |XX_prchs_mst_idx01             |93     |smart_order.TRANS.TRANS_SEQ|   1|     9.0|Using where; Rematerialize (<derived3>)     |
 2|DERIVED          |PAY       |          |eq_ref|XX_so_payment_dtl_idx01,XX_so_payment_dtl_PRCHS_SEQ_IDX|XX_so_payment_dtl_PRCHS_SEQ_IDX|93     |smart_order.MST.PRCHS_SEQ  |   1|   100.0|Using where                                 |
 2|DERIVED          |HIS       |          |ref   |XX_cancel_history_PRCHS_SEQ_IDX                        |XX_cancel_history_PRCHS_SEQ_IDX|63     |smart_order.MST.PRCHS_SEQ  |   1|   100.0|Using where                                 |
 2|DERIVED          |DTL       |          |ref   |XX_prchs_dtl_idx01                                     |XX_prchs_dtl_idx01             |93     |smart_order.MST.PRCHS_SEQ  |   2|   100.0|                                            |
 2|DERIVED          |<derived3>|          |ref   |<auto_key0>                                            |<auto_key0>                    |93     |smart_order.MST.PRCHS_SEQ  |   2|   100.0|                                            |
 3|DEPENDENT DERIVED|TSPD      |          |ref   |XX_prchs_dtl_idx01                                     |XX_prchs_dtl_idx01             |93     |smart_order.MST.PRCHS_SEQ  |   2|   100.0|                                            |

감사합니다 🙂

문의: info@bespinglobal.com | 대표번호: 02-1668-1280

Leave a Comment