여는 글

제가 담당하는 시스템은 초기 구축 업체가 만든 DAO 라이브러리를 사용합니다. 개인적으로 느끼는 이 DAO 라이브러리의 단점들은 뒤로 한다고 해도 잦은 문제를 발생시키는 것은 바로 개발 편의를 위해 남용된 VIEW TABLE과 INDEX 설계의 부재입니다.

시스템 전반적으로 여러 모듈에서 사용되는 데이터 조회에 VIEW TABLE을 사용하는 것이었죠. 어찌보면, 자주 사용되는 긴 쿼리를 쉽게 사용할 수 있으니 편하다고 생각할 수 있는데 성능 측면에서 보면 최악의 Latency 만들어 냅니다.

이번 포스팅에서는 제가 겪은 두 가지 대표적인 성능 저하 사례와 이를 어떻게 해결했는지 공유해보려 합니다.

CASE1. 매입내역

첫 번째 문제는 여러개의 테이블을 조인해서 보여주는 ‘매입내역’ 조회였습니다.

거래건이 수백건도 안되던 시절에는 전혀 문제가 없었습니다. 하지만, 가맹점이 늘어나고 거래건이 하루 수천건을 넘어가는 순간부터 점점 느려지더니 4만건을 조회하는데에 30초가 소요되었던 것이죠.

문제는 개발 편의성을 위해 수많은 조인이 걸린 쿼리를 VIEW로 만들어두고 사용하고 있었다는 것입니다.

문제는 VIEW가 실제 데이터를 저장하는 테이블이 아닙니다. “저장된 SELECT 문”에 가까운데요.

-- VIEW 생성
CREATE VIEW v_purchase_summary AS
SELECT p.id, p.date, m.name, ... 
FROM purchases p
JOIN merchants m ON p.m_id = m.id
JOIN ... (여러개 테이블 조인) ... ;

이 VIEW를 조회하면 어떤 일이 벌어질까요?

느립니다.

왜 느릴까? (Predicate Pushdown 실패)

일반적으로 스마트한 DB 옵티마이저는 VIEW 쿼리와 바깥의 WHERE 절을 합쳐서 최적화(View Merge)를 시도합니다. 하지만 집계 함수(SUM, COUNT), DISTINCT, UNION, 혹은 지나치게 복잡한 JOIN이 포함된 VIEW의 경우, 옵티마이저는 뷰 병합을 포기합니다.

대신 임시 테이블(Materialization) 방식을 택합니다.

  1. 일단 조인하고자 하는 테이블들을 다 조인해서 전체 데이터를 메모리(혹은 디스크)에 임시 테이블로 만듭니다. (Full Scan)
  2. 그 다 만들어진 결과에서 날짜 필터링을 수행합니다.

즉, 나는 ‘11월 데이터’만 필요한데, DB는 ‘전체 기간 데이터’를 일단 다 조립한 뒤에 11월을 걸러내는 비효율을 범하게 되는 것입니다. 인덱스를 타고 싶어도 탈 수 없는 구조가 되어버린 것이죠.

해결: VIEW 해체

저는 이 VIEW를 걷어내고, 필요한 테이블만 직접 명시하여 조인하는 방식으로 쿼리를 재작성했습니다. 이를 통해 각 테이블에 설정된 INDEX가 의도대로 정상 동작하게 만들었죠.

N+1 쿼리 문제

사실 VIEW가 해결되고도 바로 조회 시간이 단축 되었던 것은 아닙니다. 문제는 이렇게 조회된 데이터를 가지고 for문을 돌며, 매입에대한 매입 취소 여부를 조회하고 있었던 것인데요. 이게 말로만 듣던 N+1 문제입니다. 예를들어 4만건의 데이터를 조회한다고 했을 때, 4만건에 대해 4만번 조회 쿼리를 수행하니까. 4만 1번 조회를 해버리는거죠. 요구사항에 대해 빠르게 처리를 하려다 보니 이런 코드가 존재했던게 아닐까 위로해봅니다.

해결 : Table JOIN으로 해결

매입에대한 매입 취소 여부는 여전히 알고 싶고, 조회 시간도 단축 하고 싶다면 JOIN을 통해 해당 매입이 취소된 이력이 있는지 간단하게 확인할 수 있다는 것입니다. SQL 쿼리 상에 간단한 조건분기로 확인할 수 있겠죠.

CASE2. 승인내역

두 번째 문제는 더 황당했습니다. 여러개를 조인하는 매입내역보다, 더 적은 소수의 테이블을 조인하는 ‘승인내역’이 더 느렸습니다 (43초 소요).

JOIN 키에 인덱스가 없다

승인 테이블과 결제상품 테이블은 1:1 관계였습니다. 하지만 레거시 데이터 구조상 Foreign Key 제약조건이 없었고, 조인 키 컬럼에 인덱스조차 없었습니다.

보통 PK(Primary Key)에는 인덱스가 자동 생성되지만, 이 레거시 테이블의 결제상품 ID는 논리적으로만 식별자일 뿐, 물리적인 PK나 인덱스가 선언되어 있지 않았습니다.

Nested Loop Join의 비극

인덱스가 없는 상태에서의 조인은 Nested Loop Join (NL Join) 방식으로 동작하더라도 최악의 효율을 보입니다.

  1. 승인테이블에서 조건에 맞는 행을 하나 꺼냅니다. (Outer Loop)
  2. 그 행의 결제상품 ID 와 일치하는 것을 찾기 위해 결제상품 테이블을 처음부터 끝까지 뒤집니다(Full Scan). (Inner Loop)
  3. 이것을 승인데이터 행의 개수만큼 반복합니다.

승인건과 결제상품이 1:1 관계였으니 제곱 만큼 데이터 접근이 발생한것이었죠.

그런데 왜 승인내역에 인덱스가 없었을까?

승인내역 테이블은 조회보다 CU(Create, Update) 작업이 압도적으로 많은 테이블입니다. 승인거래는 수시로 등록되고, 수정됩니다. MariaDB를 비롯한 대부분의 RDBMS는 INDEX의 내부 알고리즘으로 B-tree를 사용하고, 이 인덱스는 조회를 빠르게 해주지만, 데이터가 변경될 때마다 인덱스도 함께 재정렬해야 합니다.

따라서, 인덱스는 필요한 컬럼에만 선별적으로 적용해야 합니다.

튜닝 결과 및 결론

저는 두 가지 조치를 취했습니다.

  1. 매입내역: 거대한 VIEW를 해체하고 최적화된 직접 쿼리로 변경(N+1 해결).
  2. 승인내역: 승인 테이블에 인덱스 생성.
항목 Before After 개선율
매입내역 (4만건) 30초 2초 93% 감소
승인내역 (4만건) 43초 4초 91% 감소

맺는글

쿼리가 이렇게 개선이 되고 회사로부터 굉장히 긍정적인 평가를 많이 받았습니다. 운영팀 입장에서는 시스템이 낡은 것을 알아서 오래걸려도 오래걸리나보다 하고 있었던 일이었는데, 제가 발견하고 개선한 내용이다보니 성능 개선 전후의 차이가 더 크게 다가오셨던것 같습니다.

레거시 시스템은 오히려 개선의 여지가 있는 좋은 플레이그라운드라고 생각합니다. 처음 마주한 레거시 시스템은 뒷골이 땡겼지만 틈날때마다 코드 정리나 소소한 마이그레이션을 거듭하며 깔끔해지고 좋은 평가를 받는 자사 시스템을 보면 보람이 생겨서 더 문제점을 찾아내고 해결하려고 하게 되는 것 같습니다.