DBMS 및 SQL 활용(2) - 쿼리 최적화하기
DBMS 수업 중 쿼리를 최적화할 수 있는 기술들에 관해 학습했습니다.
처음 알게 된 내용이고, 미니 프로젝트도 진행해서 복습을 하기로 했습니다..!
실습 복습하기
Oracle 실습에서는 다양한 조인 상황에서 발생하는 Full Table Scan(FTS) 을 줄이기 위한 인덱스 설계와 활용에 중점을 두었습니다.
조인 조건에 인덱스 생성: 고객-주문, 주문-직원, 리뷰-상품 등 다양한 조인에서 인덱스를 생성하여 옵티마이저가 Full Scan 대신 인덱스를 사용하도록 유도했습니다.
몇몇 상황에서의 예외: 옵티마이저가 Full Scan이 더 낫다고 판단하는 경우, 인덱스가 오히려 무시되기도 한다는 점을 알게 되었습니다.
- 인덱스를 사용하지 않아도 될 정도로 데이터의 양이 적은 경우
- 인덱스를 사용하지 않아야 하는 경우(Full Outer Join을 하는 경우, Sum() 등 연산을 하는 경우 등)
인덱스의 한계
- 인덱스는 조회 성능을 높이지만, 쓰기 성능과 디스크 사용량을 증가시킬 수 있습니다.
- 인덱스가 너무 많으면 오히려 옵티마이저의 판단 비용이 증가할 수 있습니다.
즉, 조회 빈도와 테이블 특성을 고려한 전략적 인덱스 설계가 중요하다는 것을 알게 되었습니다.
미니 프로젝트: 패밀리 레스토랑 신규 매출 분석
실습에서 진행했던 내용을 기반으로 미니 프로젝트도 진행했습니다.
다양한 SQL 쿼리를 작성하며 성능을 최적화해본 프로젝트입니다.
특히 WITH 절, 가상 컬럼, 서브쿼리 정리, 인덱스 생성 등을 통해 실행 시간을 줄이고, 쿼리 가독성도 개선했습니다.
전용상품 여부 판별 최적화
- INSTR() 함수 중복 제거 → 가상 컬럼 또는 WITH 절로 분리
- 평균 실행 시간 0.045ms → 0.035ms로 감소
월별 매출 PIVOT 처리
- SUBSTR + TO_DATE + TO_CHAR로 요일 및 월별 집계
- PIVOT 문법 활용으로 결과 가독성 개선
지점별 전용상품 매출 순위 분석
- RANK() OVER (PARTITION BY) 사용
- 상위 1~3위 지점을 월별로 출력
종합 리포트 생성
- 다양한 쿼리 결과를 UNION과 JOIN으로 통합
- 매출 기여율, 예약 취소율 등 비율 계산도 ROUND()와 NULLIF() 활용
프로젝트를 통해 얻은 인사이트
데이터가 작아도 미리 최적화 습관을 들이는 것이 중요하다는 것을 알게 되었습니다.
데이터가 커진 이후에는 쿼리 하나가 수십 초씩 걸릴 수 있기 때문입니다..🥹
WITH 절을 적극 활용 하고 실행 계획을 계속 확인 하자 !
WITH 를 활용해 쿼리를 구조화하면 실행 계획도 좋아지고, 유지 보수도 쉬워집니다.
쿼리를 조금만 수정하더라도 실행 계획이 많이 달라져 성능에 영향을 많이 미칠 수도 있기 때문에 실행 계획을 계속 확인 해야 합니다!
마치며
이번 실습과 프로젝트는 SQL을 단순히 ‘동작만 하는 쿼리’가 아닌, ‘효율적이고 확장 가능한 쿼리’로 발전시키는 좋은 계기였습니다.
쿼리를 최적화 하는 방법이 있다는 것은 알았지만, 이렇게까지 성능에 큰 차이를 미치는 줄 몰랐고
직접 성능을 최적화를 하면서 실행 계획의 카디널리티나 cost, bytes(성능에 영향이 없다고는 하지만..)가 줄어드는 것을 보고 뿌듯했습니다,,
앞으로도 SQL 성능에 주의를 기울여 개발을 해야할 것 같다고 생각했습니다.
무작정 JPA만 쓴다고 좋은 것이 아니라는 것을 깨달은 한 주였습니다. ☺️
참고 자료
스칼라 교육자료 및 내 과제📚