SQL 601회를 2회로 줄이고 확인한 것

한 명을 고르기 위해 5만 명을 읽고 있었다

Comatching5의 매칭은 사용자가 선택한 MBTI, 취미, 나이, 연락 빈도에 따라 후보의 점수를 계산하고 상대 한 명을 고르는 기능입니다. 이미 매칭된 상대와 필수 조건에 맞지 않는 후보는 제외합니다.

기존에는 반대 성별 후보를 500명씩 조회하고, Java에서 점수를 계산했습니다. 최고점 후보가 여러 명이면 그중 한 명을 무작위로 선택했습니다. 페이지 단위로 나누어 읽고 있었지만, 최종적으로는 후보 전체를 확인하는 구조였습니다.

사용자 10만 건, 반대 성별 후보 5만 건을 넣고 조회 과정을 살펴보니 한 번의 매칭을 위해 SQL이 601회 실행됐습니다. 먼저 점수 계산을 SQL로 옮겼고, 이어서 평가할 후보 수를 줄였습니다. 그 과정에서 표본을 줄이고도 더 느려진 이유를 실행 계획으로 확인했습니다.

단계	후보를 고르는 방식	SQL 실행 횟수	SQL 서버 시간
기존	전체 후보를 읽고 Java에서 평가	601회	261.1ms
개선 A	전체 후보를 SQL에서 평가	2회	101.1ms
개선 B	5,000명 표본을 SQL에서 평가	2회	18.8ms

위 표는 같은 시드 데이터에서 세 방식을 비교한 SQL 벤치마크 결과입니다. 개선 A에서는 조회 왕복을 줄였습니다. 개선 B에서는 평가할 후보 수를 줄인 뒤, 반복되는 취미 조회의 인덱스도 조정했습니다.

601회가 어디에서 나왔는지 나누어 보기

한 번의 매칭에서 어떤 SQL이 반복되는지 나누어 확인했습니다.

조회	실행 횟수	발생 이유
기존 매칭 상대 조회	1회	이미 매칭된 회원을 제외
후보 페이지 조회	100회	5만 명을 500명씩 조회
취미 컬렉션 조회	500회	후보의 취미를 100명 단위로 묶어 로딩
합계	601회	후보 전체를 평가하는 동안 누적

취미는 @ElementCollection(fetch = FetchType.LAZY)로 관리했습니다. 후보 엔티티를 가져온 뒤 취미 점수를 계산하는 순간 추가 조회가 발생했습니다. @BatchSize(100)이 적용되어 있어 후보마다 한 번씩 조회하는 상황은 줄어 있었지만, 5만 명의 취미를 읽으려면 여전히 500번의 조회가 필요했습니다.

최초 진단 회차에서 취미 조회는 SQL 서버 시간 281.7ms 중 225.5ms를 차지했습니다. 약 80%가 취미 컬렉션을 읽는 데 쓰인 셈입니다. 최종 비교 표의 261.1ms는 이후 세 방식을 함께 다시 측정한 값입니다.

후보 페이지 크기를 500에서 5,000으로 키우는 실험도 했습니다. 읽는 후보 수는 같았는데 시간이 절반 수준으로 줄었습니다. 조회할 데이터의 양뿐 아니라 SQL을 반복 실행하고 결과를 전달받는 비용도 컸습니다.

결과로 필요한 후보는 한 명이었습니다. 전체 후보와 취미를 애플리케이션으로 가져오지 않고 DB에서 점수를 계산해 한 명을 고르는 방법을 검토했습니다.

개선 A: 점수 계산과 선택을 SQL로 옮겼다

Java에서 수행하던 점수 계산을 SQL의 CASE 식으로 옮겼습니다. 점수 내림차순으로 정렬하고, 동점자는 RAND()로 섞은 뒤 LIMIT 1로 한 명만 가져오도록 했습니다.

ORDER BY (점수 계산식) DESC, RAND()
LIMIT 1

기존의 선택 규칙인 ‘최고점 그룹 안에서 무작위 한 명 선택’을 유지하면서, 최고점 후보 목록 전체를 Java로 가져오는 과정을 없앤 것입니다. 제외 목록 조회 한 번과 후보 선택 한 번으로 정상 조회 경로를 구성했습니다.

취미는 존재 여부보다 개수가 중요했다

취미 점수는 해당 카테고리를 하나 보유하면 10점, 둘이면 15점, 셋 이상이면 20점이었습니다. 단순히 취미가 있는지만 표현하는 비트마스크로 바꾸면 이 차이가 사라집니다.

그래서 취미 원본 테이블을 유지하고 필요한 카테고리의 개수를 SQL에서 계산했습니다. 모든 후보의 모든 취미를 읽는 대신, 요청한 카테고리에 해당하는 데이터만 점수 계산에 사용했습니다.

CASE
    WHEN COUNT(h.member_id) >= 3 THEN 20
    WHEN COUNT(h.member_id) = 2 THEN 15
    WHEN COUNT(h.member_id) = 1 THEN 10
    ELSE 0
END

이 코드는 현재 후보 조회의 취미 점수 식입니다. MBTI, 나이, 연락 빈도는 요청에 포함된 조건에 따라 점수 항을 추가합니다. 계산된 점수를 따로 저장하는 대신 조회 시 원본으로부터 계산하기 때문에, 별도 점수 컬럼을 동기화하는 작업도 줄일 수 있었습니다.

네이티브 SQL을 선택한 이유

필수 조건과 점수 조건이 요청에 따라 달라지고, MBTI 조건의 개수에 따라서도 SQL 식이 달라집니다. 이를 조합하는 Repository 구현에서 네이티브 SQL을 사용했습니다. 생성된 SQL을 그대로 EXPLAIN ANALYZE에 넣어 실행 계획과 실제 처리 시간을 확인하기도 편했습니다.

SQL 구조만 코드에서 조립하고 사용자 입력은 파라미터로 바인딩했습니다.

Query query = em.createNativeQuery(sql, MatchingCandidate.class);
params.forEach(query::setParameter);

조회는 두 번이 됐지만 여전히 전체를 정렬했다

개선 A를 적용한 최초 회차에서는 SQL이 601회에서 2회로 줄었고, SQL 서버 시간은 281.7ms에서 114.1ms로 줄었습니다. 하지만 실행 계획에는 다음 항목이 남아 있었습니다.

Using temporary; Using filesort

MySQL은 여전히 후보 5만 명의 점수를 계산하고 정렬했습니다. LIMIT 1은 반환할 행을 한 개로 제한하지만, 최고점 후보를 알아내기 위한 계산까지 한 명으로 줄여주지는 않았습니다. RAND()가 포함된 점수 정렬을 기존 인덱스의 순서로 대신하기도 어려웠습니다.

다음 실험에서는 점수를 계산할 후보 수를 줄여보기로 했습니다.

개선 B: 전체 최고점에서 표본 안의 최고점으로

점수 분포를 확인해 보니, 네 가지 조건을 모두 설정한 시드 데이터에서 후보 5만 명 중 최고점인 90점은 4명뿐이었습니다. 85점은 15명, 80점은 52명이었습니다.

이 분포에서는 같은 조건의 요청이 소수의 최고점 후보에게 집중될 수 있었습니다. 전체 조회 비용과 후보 선택의 분포를 함께 고려해, 표본을 뽑고 그 안에서 최고점 한 명을 선택하도록 규칙을 바꿨습니다. 개선 A까지는 기존 선택 규칙을 유지했지만, 이 단계에서는 선택 규칙 자체가 달라졌습니다.

당시 비교 실험에는 표본 5,000명을 사용했습니다. 표본 크기를 줄이면 조회 비용이 줄지만, 높은 점수의 후보를 포함할 가능성도 낮아집니다. 시드의 점수 분포를 바탕으로 계산한 확률은 다음과 같았습니다.

표본 크기	80점 이상 포함	85점 이상 포함	90점 포함
1,000명	75.8%	31.6%	7.7%
2,000명	94.2%	53.2%	14.8%
5,000명	99.9%	85.0%	33.0%

이 확률은 시드의 점수 분포에서 무작위로 표본을 뽑는다고 가정해 계산했습니다. 실제 구현의 선택 분포나 사용자 만족도를 측정한 값은 아닙니다. 당시에는 높은 점수의 후보를 포함할 가능성과 조회 비용을 비교해 5,000명을 선택했습니다.

`random_key`와 복합 인덱스로 표본을 읽기

전체 후보를 ORDER BY RAND()로 섞고 5,000명을 고르면 표본을 만드는 단계부터 전체 정렬 비용이 발생합니다. 대신 후보를 생성할 때 random_key를 부여하고 다음 복합 인덱스를 사용했습니다.

(gender, is_matchable, random_key)

성별과 매칭 가능 여부를 고정한 뒤 무작위 시작점 이후의 키를 순서대로 읽는 방식입니다. 핵심 흐름을 단순화하면 다음과 같습니다.

SELECT mc.member_id
FROM matching_candidate mc
WHERE mc.gender = :targetGender
  AND mc.is_matchable = 1
  AND mc.random_key >= :randomStart
ORDER BY mc.random_key
LIMIT 5000;

실제 구현에서는 필수 나이·MBTI·취미 조건과 제외 회원 조건도 이 표본 조회 안에 적용합니다. 먼저 5,000명을 뽑고 조건을 거르면 남는 후보가 지나치게 적어질 수 있기 때문입니다.

member_id 대신 별도의 무작위 키를 사용한 이유도 있습니다. 회원 ID는 가입 순서와 연결될 수 있어, 연속 구간을 읽으면 비슷한 시기에 유입된 회원이 함께 선택될 수 있습니다. 무작위 키는 이런 가입 순서의 영향을 줄이는 데 목적이 있습니다. 다만 연속 키 구간을 읽는 방식의 실제 선택 분포는 필수 조건과 시작점에 따라서도 달라집니다.

표본 ID를 구한 뒤에는 후보 테이블과 취미 테이블을 연결하고, 표본 안에서 점수를 계산해 한 명을 반환합니다.

조건을 만족하는 표본 ID 조회
  → 후보 정보와 필요한 취미 카테고리 JOIN
  → 점수 계산
  → 점수 DESC, RAND() 정렬
  → 한 명 반환

표본을 줄였는데 처음에는 더 느렸다

첫 표본 조회의 SQL 서버 시간은 127.3ms였습니다. 전수 조회를 SQL로 옮긴 개선 A의 101.1ms보다 느렸습니다. 스캔 행 수는 줄었는데 시간이 늘어난 것입니다.

EXPLAIN ANALYZE로 나누어 보니 표본 추출 자체는 1.4ms로 동작했습니다. 시간을 쓰는 곳은 표본을 뽑은 뒤 반복되는 조회였습니다.

인덱스로 표본 추출           1.4ms
후보 PK 조회 × 5,000        약 13ms
취미 인덱스 조회 × 5,000     약 34ms
GROUP BY 임시 테이블        약 8ms

취미 조회는 의도한 커버링 인덱스 대신 member_id 단독 인덱스를 사용하고 있었습니다. 회원의 취미 행을 읽은 다음 요청한 카테고리인지 걸러내는 방식이었습니다. 작은 차이도 표본 5,000명에 반복되면 전체 시간에 영향을 줍니다.

이 단독 인덱스는 취미 컬렉션의 외래 키를 지원하기 위해 생성된 인덱스였습니다. 인덱스 구성을 바꿔 비교한 진단 실험에서는 다음 차이가 나타났습니다.

구성	선택된 접근 방식	SQL 시간
`member_id` 단독 인덱스 존재	회원의 취미를 읽은 뒤 카테고리 필터	43.8ms
단독 인덱스를 제거한 비교	카테고리·회원 복합 인덱스로 커버링 조회	18.9ms

엔티티의 @CollectionTable에는 점수 조회용 (hobby_categories, member_id)와 외래 키용 (member_id, hobby_categories) 인덱스를 명시했습니다. 스키마를 다시 만들 때에도 필요한 인덱스 구성이 재현되도록 하기 위해서입니다. 기존 DB에 적용할 때에는 실제로 남아 있는 단독 인덱스와 옵티마이저의 선택도 함께 확인해야 합니다.

표본 수를 줄였을 때도 취미 조회는 후보마다 반복됐습니다. 실행 계획에서 반복 횟수와 선택된 인덱스를 확인한 뒤에야 표본 조회의 시간을 줄일 수 있었습니다.

제외 목록 조회도 함께 바꿨다

이미 매칭된 상대를 찾는 쿼리는 member_id와 partner_id를 OR로 묶고 있었습니다. 당시 실행 계획에서는 이력 인덱스를 훑는 형태였고, 이력이 늘어날 때 비용이 커질 수 있었습니다.

각 방향의 조회를 UNION으로 나누고 (partner_id, member_id) 인덱스를 추가했습니다.

SELECT partner_id
FROM matching_history
WHERE member_id = :memberId
UNION
SELECT member_id
FROM matching_history
WHERE partner_id = :memberId;

요청자가 신청자였던 이력과 상대였던 이력을 각각 조회해 상대 회원 ID를 합칩니다. 각 분기가 자신의 조건에 맞는 인덱스를 사용할 수 있도록 구성했습니다.

무엇을 측정했고, 무엇이 달라졌나

측정에는 저장소의 tools/perf/matching_bench.py를 사용했습니다. 애플리케이션을 띄워 HTTP 요청을 보내는 방식 대신, 변경 전·전수 SQL·표본 SQL을 같은 데이터에서 재현했습니다. old, new, sample 모드를 비교해 쿼리 수와 SQL 서버 시간, 스캔 행 수를 확인했습니다.

항목	기존	개선 A	개선 B · 표본 5,000명
SQL 실행 횟수	601회	2회	2회
SQL 서버 시간	261.1ms	101.1ms	18.8ms
스캔 행 수	240,458	68,723	12,392

SQL 서버 시간은 기존 대비 약 92.8% 줄었습니다. 측정 대상은 낮은 동시성에서의 후보 조회 SQL이며, HTTP 왕복·인증·서비스 간 호출·Java 엔티티 생성 비용은 별도입니다. API 전체 성능은 이후 매칭 API 부하 실험에서 처리량과 지연 시간을 함께 확인했습니다.

이후 구현에는 작은 후보 풀에서 첫 표본 조회가 비면 randomStart를 0으로 낮춰 재조회하는 경로도 추가됐습니다. 그래서 위의 SQL 2회는 해당 벤치마크의 정상 경로를 기준으로 합니다. 현재 코드의 표본 크기는 후속 변경에서 2,000명으로 조정됐으며, 위 표의 18.8ms는 5,000명으로 측정한 결과입니다.

다음 조회를 볼 때도 확인할 것

601회의 SQL을 줄이는 과정에서 계산 위치, 후보 수, 인덱스 접근 경로를 차례로 바꿨습니다.

데이터를 어디에서 계산할 것인가: 후보 전체를 Java에 가져오는 대신 SQL에서 평가하고 최종 한 명만 가져왔습니다.
얼마나 많은 후보를 평가할 것인가: 전수 조회와 표본 조회의 비용·선택 기준을 비교했습니다.
각 후보를 어떤 경로로 읽을 것인가: 실행 계획을 통해 반복되는 취미 조회와 인덱스 선택을 확인했습니다.

개선 A와 B는 모두 SQL이 두 번 실행됐지만, SQL 서버 시간은 각각 101.1ms와 18.8ms였습니다. 이후 조회를 분석할 때도 실행 횟수만으로 판단하지 않고, 읽는 행 수와 반복 루프, 실제로 선택된 인덱스를 함께 확인하고 있습니다.