JOIN ON col IN (상관컬럼, 상관컬럼) 은 인덱스 ref 를 못 탄다

환불 거래를 원래 거래에 매칭하는 쿼리를 쓰면서, 티켓번호가 현재권이거나 원권 둘 중 하나와 맞으면 되니까 이렇게 묶었습니다.

⧉JOIN txn emd
  ON emd.txn_type IN ('EMDA','EMDS')
 AND emd.ticket_no IN (r.ticket_no, r.resolved_orig_ticket_no)

emd 쪽이 ALL 스캔으로 떨어졌습니다. 환불 1.4만 행 × 대상 7만 행이면 10억 건 평가입니다. 이 블록에서 집계 테이블 전체 재구축이 수 분간 멈춰 잡이 실패했습니다.

EXPLAIN 이 이렇게 나오면 이 형태입니다.

⧉type: ALL
key: NULL                      ← possible_keys 에는 떠 있는데 key 는 NULL
Extra: Range checked for each record (index map: 0x...)

원인

IN (컬럼, 컬럼) 은 옵티마이저 입장에서 상관 서브쿼리당 값이 여러 개라, 단일 ref 등가조건으로 환원되지 않습니다. idx (ticket_no, txn_type) 같은 복합 인덱스가 있어도 ticket_no = ? 형태의 단일 등가가 아니라서 ref 액세스를 못 탑니다.

OR 로 여러 등가를 묶은 경우도 같습니다.

해결

IN 가지를 등가조인 두 갈래로 분리해 UNION ALL 한 뒤, 우선순위로 1건만 고릅니다. 각 갈래가 col = corr_col 이라 lookup 당 1행 수준의 ref 액세스를 탑니다.

⧉direct_emd_candidates AS (
  -- 1차: emd.ticket_no = r.ticket_no
  SELECT r.id AS refund_id, emd.id AS emd_id, 1 AS match_priority
  FROM txn r
  JOIN txn emd
    ON emd.ticket_no = r.ticket_no
   AND emd.txn_type IN ('EMDA','EMDS')
  WHERE r.txn_type = 'RFND'
  UNION ALL
  -- 2차: emd.ticket_no = r.resolved_orig_ticket_no
  SELECT r.id, emd.id, 2
  FROM txn r
  JOIN txn emd
    ON emd.ticket_no = r.resolved_orig_ticket_no
   AND emd.txn_type IN ('EMDA','EMDS')
  WHERE r.txn_type = 'RFND'
),
direct_emd_targets AS (
  -- 현재권 매치 우선, 없으면 원권 매치 (기존 IN 버전의 COALESCE 의미 보존)
  SELECT refund_id,
         COALESCE(MIN(CASE WHEN match_priority=1 THEN emd_id END),
                  MIN(CASE WHEN match_priority=2 THEN emd_id END)) AS emd_id
  FROM direct_emd_candidates
  GROUP BY refund_id
)

emd.txn_type IN ('EMDA','EMDS') 는 그대로 둬도 됩니다. 이쪽은 상관컬럼이 아니라 상수 목록이라 복합 인덱스의 두 번째 컬럼으로 range 를 탑니다. 문제가 되는 건 IN 안에 다른 테이블의 컬럼이 들어간 경우입니다.

우선순위 1건 선택은 COALESCE(MIN(...), MIN(...)) 대신 ROW_NUMBER() OVER (PARTITION BY refund_id ORDER BY match_priority, emd_id) = 1 로 써도 결과가 같습니다.

바꾸기 전에 동등성부터 확인합니다

IN 버전과 UNION ALL 버전이 같은 결과인지 확인하고 교체했습니다. 두 매핑을 outer join 해서 행 수와 mismatched 를 봅니다.

⧉SELECT (SELECT COUNT(*) FROM old_t) old_rows,
       (SELECT COUNT(*) FROM new_t) new_rows,
       (SELECT COUNT(*) FROM old_t o JOIN new_t n
          ON o.rid = n.rid AND o.emd_id <=> n.emd_id) matched;

<=> 는 NULL 끼리도 같다고 보는 비교라서, 매칭이 없는 행까지 같이 검증됩니다. 2,747행 = 2,747행, mismatched 0 을 확인하고 교체했습니다. dev 기준 수 분간 멈추던 블록이 약 65초로 돌아왔습니다.

조인 우변이 IN (좌변 행의 여러 컬럼) 이거나 OR 로 여러 등가가 묶여 있으면 같은 형태입니다. 가지를 UNION ALL 로 펴서 각 가지를 단일 등가로 만들면 됩니다.