[DB] SQL 쿼리 성능 최적화 공부

현재 회사에서 내가 맡은 시스템에 조회 성능이 안 좋은 부분이 있다. 이 때문에 업무에 불편함을 느낀다는 불만이 있어서 틈틈이 최적화를 해볼까 한다.
쿼리 성능 최적화는 처음이라, 공부하면서 진행 방법을 기록하려고 글을 남긴다.

 

진행 순서

[1단계] 어디가 느린지 찾기

1. 성능 최적화가 필요한 쿼리문을 복사해서 가져온다.
2. 테이블 정보 조회.sql로 쿼리에 포함된 테이블 정보를 엑셀로 추출한다.
3. 조건절(WHERE, JOIN의 ON)에 쓰인 컬럼에 인덱스가 걸려 있는지 확인한다.
   • 단, 인덱스가 있다고 해서 실제로 쓰이는 건 아니다. 사용 여부는 실행계획에서 확인한다.
4. 가장 안쪽 쿼리부터 실행하면서 오래 걸리는 부분을 찾는다. (약 2000건 결과면 0.01초 정도가 적당)
   • 주의: 같은 쿼리를 두 번 실행하면 DB 버퍼 캐시 때문에 두 번째가 빨라진다. 시간보다 **읽은 양(Rows, Buffers)**이 더 믿을 만한 기준이다.

[2단계] 왜 느린지 보기

1. 느린 부분의 실행계획을 확인한다. (공부 중)
   • 가장 안쪽으로 들여쓰기된 줄부터 실행된다.
   • TABLE ACCESS FULL: 테이블 전체를 읽었다는 뜻. 큰 테이블이면 1순위로 의심한다.
   • 많이 읽었는데 결과 건수가 적으면 낭비가 있다는 신호다.

[3단계] 고치고 확인하기

1. 아래 "증상별 처방"을 보고 원인을 하나씩 고친다.
2. 고치기 전후의 실행계획, 읽은 양, 시간을 비교한다. 결과 건수와 내용이 같은지도 반드시 확인한다.

증상별 확인할 부분

실행계획에서 보이는 것

인덱스가 있는데 FULL SCAN	조건절 좌변(컬럼)에 함수나 연산을 쓰지 말고 우변 값을 바꾼다. OR 대신 UNION 사용을 검토한다.
많이 읽고 결과는 적음	WHERE 조건으로 필요한 데이터만 조회한다. 꼭 필요한 행과 컬럼만 가져온다.
서브쿼리가 행마다 반복 실행됨	서브쿼리를 줄이고 JOIN, EXISTS, UNION으로 대체한다.
큰 테이블부터 조인함	JOIN 순서를 확인한다. 작은 결과를 먼저 만드는 쪽이 유리하다.

 

좌변 연산 예시:

-- 인덱스 사용 불가
WHERE TO_CHAR(REG_DT, 'YYYYMMDD') = '20261010'
-- 인덱스 사용 가능
WHERE REG_DT >= TO_DATE('20261010', 'YYYYMMDD')
  AND REG_DT <  TO_DATE('20261010', 'YYYYMMDD') + 1

캐시 정리

• DB 버퍼 캐시: DB가 자동으로 관리한다. 개발자가 직접 만들 수 없다.
• 스칼라 서브쿼리 캐싱: SELECT절 서브쿼리에 같은 입력값이 반복되면 Oracle이 결과를 재사용한다. 코드값처럼 종류가 적은 값이면 효과가 크고, 사번처럼 매번 다르면 효과가 없다. 그럴 땐 JOIN이 낫다.
• 애플리케이션 캐시: Spring @Cacheable, Redis 등으로 직접 만든다. 데이터가 바뀌어도 옛날 값이 남을 수 있어서, 공통코드처럼 거의 안 바뀌는 데이터에만 쓴다. 쿼리를 먼저 고치고 캐시는 마지막 수단으로 생각한다.

 

 

테이블 정보 조회.sql

0.00MB

 

 

참고: 

https://community.heartcount.io/ko/query-optimization-tips/

SQL 쿼리 성능 최적화를 위한 튜닝 팁 6가지 (Query Optimization)

https://chung-develop.tistory.com/145

SQL 쿼리 튜닝하는 여러가지 방법