[Troubleshooting]JSON_TABLE과 LEFT JOIN으로 인한 카테시안 곱

문제

프로젝트를 진행하며 MBTI 기반 매칭 시스템을 작업하던 중 문제가 발생했다.

사용자의 기술 스택 정보를 조회하는 쿼리에서 한 사용자의 기술 스택이 계속 중복되어 나타나는 것이다.

예를 들어 '춘식이'라는 사용자의 기술 스택이 4번씩 반복되고 있었다.

 

 

똑같은 기술 스택이 4번씩 반복되고 있었다.

 

원인 분석

문제의 원인을 찾기 위해 쿼리를 자세히 살펴봤다. 

 

LEFT JOIN JSON_TABLE( 
    #{companyMbti}, 
    "$[*]" COLUMNS(company_mbti CHAR(4) PATH "$") 
) jt ON JSON_VALID(#{companyMbti}) AND jt.company_mbti IS NOT NULL
LEFT JOIN mbti_compat compat ON compat.code1 = umt.type_code
    AND compat.code2 = jt.company_mbti

 

여기서 문제가 발생한 것이다.

기업이 원하는 MBTI가 ["INFP","INFJ","INTP","INTJ"] 이렇게 4개의 값을 가지고 있었는데,

JSON_TABLE이 배열의 각 요소마다 행을 생성하고, 이것이 LEFT JOIN으로 연결되면서 카테시안 곱이 발생한 것이다.

즉, 한 사용자가 4개의 MBTI 타입과 모두 매칭되어 중간 결과셋에 4번 나타나고,

GROUP BY와 GROUP_CONCAT으로 인해 기술 스택도 4번 반복된 것이다.

 

 

해결책 1 : 스칼라 서브쿼리

 

카테시안 곱 문제를 해결하기 위해

스칼라 서브쿼리를 사용, MBTI 매칭 점수 계산을 별도의 서브쿼리로 분리하여 메인 쿼리에서 행이 복제되는 것을 방지했고,

GROUP_CONCAT(DISTINCT ...) 를 사용하여 이미 그룹화된 사용자 내에서 기술스택들을 중복 제거하였다.

SELECT
    false AS chk,
    u.name AS name,
    ui.career AS career,
    ui.current_position AS currentPosition,
    GROUP_CONCAT(DISTINCT ts.tech_stack_name SEPARATOR ', ') AS techStack,
    umt.type_code AS typeCode,
    ui.resume_file_name AS resumeFileName,
    u.email AS email,
    u.user_id AS accountId,
    <if test="companyMbti != null and companyMbti != ''">
    (
        SELECT IFNULL(MAX(compat.match_cnt), 0)
        FROM JSON_TABLE(
            #{companyMbti},
            "$[*]" COLUMNS(company_mbti CHAR(4) PATH "$")
        ) jt
        JOIN mbti_compat compat ON compat.code1 = umt.type_code 
             AND compat.code2 = jt.company_mbti
        WHERE JSON_VALID(#{companyMbti}) AND jt.company_mbti IS NOT NULL
    ) AS mbtiMatchScore
    </if>
    <if test="companyMbti == null or companyMbti == ''">
    0 AS mbtiMatchScore
    </if>
FROM users u
JOIN users_info ui ON u.user_id = ui.user_id
LEFT JOIN users_mbti_types umt ON u.user_id = umt.user_id
LEFT JOIN job_applications ja ON u.user_id = ja.user_id AND ja.job_posting_id = #{jobPostingId}
LEFT JOIN scout_requests sr ON u.user_id = sr.user_id AND sr.job_posting_id = #{jobPostingId}
LEFT JOIN users_tech_stack_relation tsr ON u.user_id = tsr.user_id
LEFT JOIN tech_stack ts ON tsr.tech_stack_id = ts.tech_stack_id
WHERE ja.user_id IS NULL
    AND umt.type_code IS NOT NULL
    <if test="scoutStatus == 'NOT_SCOUTED'">AND sr.user_id IS NULL</if>
    <if test="scoutStatus == 'SCOUTED'">AND sr.user_id IS NOT NULL</if>
GROUP BY u.user_id, u.name, ui.career, ui.current_position, umt.type_code, ui.resume_file_name, u.email
<if test="mbtiMatchFilter != null and mbtiMatchFilter != '' and mbtiMatchFilter != '0'">
HAVING IFNULL(MAX(compat.match_cnt), 0) >= CAST(#{mbtiMatchFilter} AS UNSIGNED)
</if>
ORDER BY 
    <if test="companyMbti != null and companyMbti != ''">mbtiMatchScore DESC,</if>
    u.user_id DESC
LIMIT #{pageSize} OFFSET ${(pageIndex - 1) * pageSize}

 

 

 

해결책 2 : 파생 테이블

 

다른 최선의 방법이 존재하는지 찾아본 결과, 찾을 수 있었던 방식이다.

SELECT main_query.*
FROM (
    -- 1. 먼저 사용자별로 그룹화하여 필요한 모든 값을 계산한다
    SELECT
        false AS chk,
        u.name AS name,
        ui.career AS career,
        ui.current_position AS currentPosition,
        GROUP_CONCAT(DISTINCT ts.tech_stack_name SEPARATOR ', ') AS techStack,
        umt.type_code AS typeCode,
        ui.resume_file_name AS resumeFileName,
        u.email AS email,
        u.user_id AS accountId,
        -- 스칼라 서브쿼리로 mbtiMatchScore를 계산
        (
            SELECT IFNULL(MAX(compat.match_cnt), 0)
            FROM JSON_TABLE(
                #{companyMbti},
                "$[*]" COLUMNS(company_mbti CHAR(4) PATH "$")
            ) jt
            JOIN mbti_compat compat ON compat.code1 = umt.type_code 
                                   AND compat.code2 = jt.company_mbti
            WHERE JSON_VALID(#{companyMbti}) AND jt.company_mbti IS NOT NULL
        ) AS mbtiMatchScore
    FROM users u
    JOIN users_info ui ON u.user_id = ui.user_id
    LEFT JOIN users_mbti_types umt ON u.user_id = umt.user_id
    LEFT JOIN job_applications ja ON u.user_id = ja.user_id AND ja.job_posting_id = #{jobPostingId}
    LEFT JOIN scout_requests sr ON u.user_id = sr.user_id AND sr.job_posting_id = #{jobPostingId}
    LEFT JOIN users_tech_stack_relation tsr ON u.user_id = tsr.user_id
    LEFT JOIN tech_stack ts ON tsr.tech_stack_id = ts.tech_stack_id
    WHERE ja.user_id IS NULL
        AND umt.type_code IS NOT NULL
        <if test="scoutStatus == 'NOT_SCOUTED'">AND sr.user_id IS NULL</if>
        <if test="scoutStatus == 'SCOUTED'">AND sr.user_id IS NOT NULL</if>
    GROUP BY u.user_id, u.name, ui.career, ui.current_position, umt.type_code, ui.resume_file_name, u.email
) AS main_query
-- 2. 계산된 mbtiMatchScore를 이용해 바깥에서 필터링한다
<if test="mbtiMatchFilter != null and mbtiMatchFilter != '' and mbtiMatchFilter != '0'">
WHERE main_query.mbtiMatchScore >= CAST(#{mbtiMatchFilter} AS UNSIGNED)
</if>
-- 3. 최종 결과를 정렬하고 페이징한다
ORDER BY
    <if test="companyMbti != null and companyMbti != ''">main_query.mbtiMatchScore DESC,</if>
    main_query.accountId DESC
LIMIT #{pageSize} OFFSET ${(pageIndex - 1) * pageSize}

 

이 방법의 장점은 다음과 같다:

1. 실행 순서가 명확하다 - 먼저 내부 쿼리에서 필요한 모든 계산을 완료하고, 바깥 쿼리에서 필터링과 정렬을 수행한다
2. SQL이 더 읽기 쉽다 - HAVING 대신 WHERE을 사용할 수 있어 의도가 더 명확하다
3. 복잡한 집계나 계산 결과에 대한 필터링에 유용하다 - 집계 함수의 결과를 바탕으로 추가 조건을 쉽게 적용할 수 있다

 

 

이번 문제를 통해 몇 가지를 배웠다:

1. JSON_TABLE과 LEFT JOIN 조합 시 주의해야 한다 - 배열의 각 요소마다 행이 생성되므로 카테시안 곱이 발생할 수 있다
2. GROUP_CONCAT에서 DISTINCT는 필수다 - 중복 제거를 위해 반드시 사용해야 한다
3. 스칼라 서브쿼리는 카테시안 곱 문제의 좋은 해결책이다 - 복잡한 조인 대신 서브쿼리로 분리하면 행 복제를 방지할 수 있다
4. 파생 테이블은 복잡한 쿼리를 단순화한다 - 실행 순서를 명확하게 하고 가독성을 높인다

 

SQL 최적화는 끝이 없는 것 같다. 하나의 문제를 해결하는 방법도 여러 가지가 있고, 상황에 따라 최적의 해법이 달라진다. 앞으로도 계속 공부하고 경험을 쌓아가야겠다.