이력 매칭 로직 성능 개선 정리

• 대상: 주문 품목을 과거 이력과 매칭하는 배치 로직 (레거시 PHP)
• 목적: 품목당 반복 조회로 인한 성능 저하 개선
• 제약: 품목 상세 조회 함수(getProductDetail())는 별도 모듈 소유라 수정 불가

1. 초기 상태

총 실행시간: 121.19초 (품목수 159)
init=0.14s | 자체이력=2.92s | 타지점이력=97.01s | 검증체크=2.16s | 상세조회=15.29s(호출:144,캐시hit:0,miss:144) | UPDATE=0.11s

전체의 80%가 “타 지점 이력 조회” 구간에 몰려 있었음.

2. 진단 과정

2-1. 구간별 타이밍 계측

각 단계(자체이력/타지점이력/검증체크/상세조회/UPDATE)에 microtime() 기반 누적 타이머를 삽입해 실제 병목 구간을 특정.

→ 예상(상세조회 반복 호출)과 달리 타 지점 이력 조회가 압도적 1위로 확인됨.

2-2. EXPLAIN (ANALYZE, BUFFERS)로 원인 규명

타 지점 이력 쿼리를 직접 분석한 결과:

• 주문품목 → 주문 → 지점그룹 → 지점 3-way JOIN이 품목 159개마다 처음부터 재계산됨
• 상품명 비교에 쓰이던 normalize_name()(내부적으로 regexp_replace 정규화 비교)가 인덱스를 못 타는 함수 기반 조건이라 매번 순차 스캔 발생
• 1차 개선(아래 3-1) 후에도 EXPLAIN을 다시 떠보니 대상 범위를 좁힌 주문품목 테이블이 여전히 약 60만 행이었고, 이 60만 행에 대한 정규화 계산이 품목마다 반복(159회) 되고 있었음 (Rows Removed by Filter: 599177)

즉 진짜 문제는 “JOIN 자체”가 아니라 **“동일한 60만 행을 159번 재스캔하며 정규식을 매번 계산”**하는 구조였음.

3. 적용한 개선 사항

3-1. 대상 범위 사전 조회 (1차 개선)

“같은 지역 + 우리 지점 아님” 조건으로 걸러지는 주문 후보 목록을, 품목 루프에 진입하기 전 딱 1번만 조회하도록 분리.

$sqlValidOrderIds = "
    SELECT o.order_id
    FROM retail.branch b
    JOIN retail.branch_group bg ON b.group_id = bg.group_id
    JOIN retail.orders o ON o.branch_id = b.branch_id
    WHERE bg.region_code = $current_region_code
      AND b.branch_id != $current_branch_id
";
$validOrderRows = $db->rows($sqlValidOrderIds);
$validOrderIds = array_column($validOrderRows, 'order_id');
$validOrderIdString = $validOrderIds ? implode(',', $validOrderIds) : '-1';

타 지점 이력 쿼리는 이 목록을 order_id IN ($validOrderIdString)으로 재사용.

결과: 97.01s → 83.66s (약 14% 감소, 기대보다 적어 추가 조사 진행)

3-2. 정규화 배치 매칭 (핵심 개선)

기존 정규식을 확인:

regexp_replace($1, E'[ #&+\\-\\%@=/\\:;,.\'"^\`~_|!?*$#<>()\\[\\]{}\\r\\n\\t]', '', 'g')

이를 PHP로 동일 구현하여 검증:

function normalizeName(string $name): string {
    return preg_replace(
        '/[ #&+\-%@=\/:;,.\'"^`~_|!?*$#<>()\[\]{}\r\n\t]/u',
        '',
        $name
    );
}

자체 이력 매칭에 실패한 품목들만 모아서, 정규화된 이름을 VALUES 리스트로 구성 → CTE + JOIN 한 번으로 전체 배치 매칭:

WITH targets(target_item_id, norm_name) AS (
    VALUES (101, '정규화명1'), (102, '정규화명2'), ...
),
matched AS (
    SELECT t.target_item_id, oi.item_id AS matched_item_id,
           oi.order_id, oi.unit
    FROM retail.order_items oi
    JOIN targets t
        ON normalize_name(oi.item_name) = t.norm_name
    WHERE oi.order_id IN ($validOrderIdString)
      AND oi.order_id != $current_order_id
      AND (oi.matched_id1 IS NOT NULL OR oi.matched_id2 IS NOT NULL OR oi.matched_id3 IS NOT NULL)
),
ranked AS (
    SELECT *,
        COUNT(*) OVER (PARTITION BY target_item_id, matched_item_id) AS duplicate_count,
        ROW_NUMBER() OVER (PARTITION BY target_item_id ORDER BY order_id DESC) AS rn
    FROM matched
)
SELECT target_item_id, matched_item_id, order_id, unit, duplicate_count
FROM ranked
WHERE rn <= 10

핵심 효과: regexp_replace 계산이 60만 행 기준 159번 → 1번으로 축소됨.

결과: 83.66s → 1.75s (타 지점 이력 배치 구간, 약 47배 개선)

3-3. 상세 정보 캐시

같은 상품이 여러 품목의 매칭 후보로 겹칠 때 상세조회 함수 재호출을 막기 위해, 실행 동안 유지되는 로컬 캐시 추가.

if (isset($detailCache[$matchedItemId])) {
    $detail = $detailCache[$matchedItemId];
} else {
    // ... 파라미터 세팅 ...
    $detail = getProductDetail();
    $detailCache[$matchedItemId] = $detail;
}

결과: 이번 데이터셋(품목 159개)에서는 캐시 hit 5회 / miss 144회로 효과 미미. 겹치는 상품이 적은 구성이었음.

4. 최종 결과

전체 실행시간 추이

구간별 비교 (원본 vs 최종)

5. 남은 병목 및 다음 단계

상세조회 함수 호출 — 16.18s (전체의 59%)

• 수정 권한이 없는 외부 함수라 캐싱 외에 직접 손댈 수 없는 영역
• 다음으로 확인이 필요한 것: 이 함수가 여러 상품 ID를 배열/콤마목록으로 한 번에 조회하는 다중조회 경로를 지원하는지
  • 지원한다면: 품목당 매칭 후보(최대 5개)를 한 번의 호출로 가져와서 애플리케이션 레벨에서 필터링 → 호출 횟수를 144회에서 대폭 축소 가능
  • 지원하지 않는다면: 현재 27.63s가 사실상의 하한선에 근접

6. 얻은 교훈

• 추측한 병목(상세조회 반복 호출)과 실제 병목(타 지점 이력 조회의 반복 스캔)이 달랐음.
• microtime() 구간 계측으로 병목 구간을 좁히고, EXPLAIN (ANALYZE, BUFFERS)로 그 구간 내부의 정확한 원인을 규명하는 2단계 접근이 정확한 진단에 결정적이었음.
• 인덱스를 직접 생성할 수 없는 제약 하에서도, “반복 계산을 배치로 묶어 계산 횟수 자체를 줄이는” 쿼리 재구성만으로 실질적 개선이 가능했음.