라이브러리 컴포넌트 테이블 재설계
실행계획에서 collation 불일치로 인한 전체 스캔을 찾아 조인·식별자·인덱스를 수정했습니다.
작업 요약
실행계획 비교 · 뷰 조인 약 130만 건 / 직접 조인 약 6,000건
슬로우 쿼리에서 collation 불일치를 찾았습니다
CPU 부하가 높아진 원인을 슬로우 쿼리와 실행계획에서 확인했습니다. 테이블 구조와 식별자 정책이 조인 성능과 데이터 정합성에 영향을 주고 있었습니다.
라이브러리 컴포넌트 테이블은 제품의 여러 기능이 함께 사용하는 테이블이라, 쿼리 튜닝에서 멈추지 않고 collation·스키마·식별자 정책까지 다시 정리했습니다.
배경
고객사 환경에서 중앙 운영 DB의 CPU 이상(부하)이 관측됐습니다. 슬로우 쿼리에서 시작해 실행계획으로 좁혀 보니, 다수 패키지 생태계의 컴포넌트 정보를 담는 라이브러리 컴포넌트 테이블의 구조가 부하·정합성 문제의 근원이었습니다.
- collation 불일치 조인: 같은 값을 저장하는 두 테이블의 collation이 달라(한쪽만 대소문자 구별) 뷰 조인의 실행계획에서는 인덱스를 타지 못해 약 130만 건을 전체 스캔하는 경로가 나타났고, 직접 조인에서는 인덱스를 타고 약 6,000건을 스캔하는 경로가 나타났습니다.
- 문자열(
varchar) 타입 컬럼에JSON을 그대로 저장하던 케이스(예: 라이선스 컬럼)가 있어 조회·가공 시 비효율과 데이터 깨짐을 유발 - 식별자 문제: 서로 다른 패키지가 같은 키로 충돌하거나, 대소문자 차이로 동일 컴포넌트가 누락되는 케이스가 발생
접근
- 추적
느린 쿼리를 찾아 실행 계획 분석
- 진단
collation 차이로 발생한 전체 스캔 확인
- 전수조사
JSON 저장 방식과 식별자 충돌 전수 조사
- 재설계
스키마·인덱스·식별자 정책 재설계
슬로우 쿼리에서 원인 쿼리를 찾아 실행계획을 비교했습니다. 뷰 조인은 collation 차이 때문에 인덱스를 타지 못하는 경로였고, 직접 조인은 인덱스를 사용하는 경로였습니다. 두 실행계획의 스캔 행 수는 각각 약 130만 건과 약 6,000건이었습니다. 이어 varchar에 JSON을 저장하던 케이스를 전체 테이블 대상 전수조사해 영향 범위와 깨진 데이터 비율을 파악하고, 식별자 충돌·대소문자 누락 같은 구조적 문제를 정리한 뒤, 어긋나 있던 collation을 통일하고 스키마·인덱스·식별자 정책을 재설계했습니다. 이 비교만으로 수정 후 CPU 사용률이나 응답시간, 서버 멈춤의 재발 여부를 판단할 수는 없습니다.
결과
역할
데이터 엔지니어로서 CPU 부하를 만든 쿼리의 실행 계획부터 확인했습니다. 관련 테이블과 인덱스를 전수 조사한 뒤 스키마, 식별자, 인덱스 수정안을 만들고 구현했습니다. 여러 기능이 함께 쓰는 중심 테이블이어서 최종안은 팀장·CTO 리뷰를 거쳐 정했습니다.
collation 통일, 식별자 정책 정비와 스키마·인덱스 재설계가 핵심 변경점. varchar에 JSON을 저장한 컬럼의 문제도 조사했으며, 타입 정규화가 완료됐다는 뜻은 아닙니다.