SQL 튜닝으로 데이터베이스 쿼리 성능 개선하기

쿼리 성능은 접근 경로에서 갈린다

SQL 튜닝은 DBMS, 응용 프로그램, 운영체제를 분석해 최소 자원으로 응답속도와 처리시간을 확보하는 개선 작업이다. 목표는 사용자의 업무 처리가 막히지 않도록 데이터베이스 트랜잭션 성능을 높이는 데 있다.

핵심은 디스크 블록 I/O와 시스템 리소스 경합을 줄이는 것이다. 따라서 SQL 문장만 들여다보기보다 데이터 모델, 인덱스, 메모리, 디스크 구성, 업무 처리 방식까지 함께 봐야 한다.

성능 문제를 보는 범위

데이터베이스 설계에서는 반정규화, 테이블 파티셔닝, 클러스터링 구조가 튜닝 대상이 된다. 데이터베이스 환경에서는 메모리 할당, 블록 크기, 버퍼 캐시, 공유 풀 크기를 검토한다. SQL 문장 차원에서는 조인 기법, 테이블 접근 순서, 서브쿼리와 조인 전략을 조정한다.

하드웨어 측면도 분리할 수 없다. CPU에서는 멀티코어 활용과 CPU 바인딩 전략을, 메모리에서는 버퍼 캐시 확장과 결과셋 처리를, 디스크 I/O에서는 RAID 구성·테이블스페이스 분리·I/O 분산 전략을 살핀다. 업무 프로세스 재설계, 데이터 처리 시간 조정, 배치 작업 최적화도 비즈니스 관점의 튜닝 범위에 들어간다.

옵티마이저가 비효율적인 인덱스를 고르거나, 대량 데이터를 가진 테이블부터 읽는 경우가 대표적인 점검 신호다. 필요 이상의 디스크 I/O, 데이터 특성에 맞지 않는 조인 알고리즘, 인덱스 사용을 막는 함수 적용도 같은 범주에 속한다.

실행계획을 검토하기 전의 흐름

⤢✕

먼저 테이블 간 관계, 카디널리티(선택도), 데이터 분포를 확인한다. 그다음 기존 인덱스와 WHERE 절 조건 컬럼을 조사하고, ORDER BY·GROUP BY 컬럼도 함께 검토한다.

실행계획을 분석해 인덱스 활용도를 평가한 뒤 필요하면 인덱스 재구성을 검토한다. 드라이빙 테이블은 필터링 효과가 높고 작은 결과셋을 반환하는 쪽을 우선으로 보며, 조인 키 인덱스 구성도 확인한다.

조인 방식은 Nested Loop Join, Hash Join, Sort Merge Join 가운데 데이터 양과 분포에 맞춰 정한다. 스칼라 서브쿼리와 인라인뷰 처리 효율성, 함수가 인덱스에 미치는 영향도 이 단계에서 확인한다. 이어 커버링 인덱스와 인덱스 온리 스캔 가능성을 검토한다.

힌트는 옵티마이저 결정을 오버라이드할 수 있다. 유지보수 부담을 고려해 신중하게 적용하고, 사용 근거를 문서로 남겨야 한다.

인덱스가 활용되는 조건을 만든다

인덱스 컬럼에 함수를 적용하면 인덱스 스캔을 방해할 수 있다. 암시적 형변환을 피하고, 부정형 조건보다 긍정형 조건을 쓰는 것도 같은 맥락이다.

-- 비효율적인 쿼리
SELECT * FROM employees
WHERE SUBSTR(employee_name, 1, 3) = 'Kim';

-- 최적화된 쿼리
SELECT * FROM employees
WHERE employee_name LIKE 'Kim%';

복합 인덱스는 WHERE 절 조건 순서와 맞추고, 선택도가 높은 컬럼을 앞에 배치하며, 자주 함께 쓰이는 조건 조합을 분석해 설계한다.

필요한 범위만 읽고 조인 순서를 고른다

전체 결과가 필요하지 않다면 부분범위 처리를 적용해 필요한 데이터만 검색한다. ROWNUM, LIMIT 등을 활용한 페이징과 커서 기반 처리 방식이 검토 대상이다. 대용량 데이터는 UNION ALL로 쿼리를 나누어 병렬 처리 가능성을 확보할 수 있다.

-- 비효율적인 쿼리
SELECT * FROM large_table;

-- 최적화된 쿼리
SELECT * FROM large_table
WHERE rownum <= 100;

조인에서는 필터링 효과가 큰 테이블, 인덱스가 잘 구성된 테이블, 작은 테이블 순으로 접근하는 방식을 우선 검토한다. Nested Loop Join은 소량 데이터에 효과적이고, Hash Join은 대용량 데이터 조인에 유리하며, Sort Merge Join은 정렬된 데이터를 활용할 때 효과적이다.

동적 SQL 대신 재사용 가능한 문장을 유지한다

동적 쿼리는 하드 파싱과 실행계획 재사용성 측면에서 불리할 수 있다. 정적 쿼리와 바인드 변수를 활용하면 하드 파싱을 줄이고 SQL 인젝션 방지 효과도 얻을 수 있다.

-- 다이나믹 쿼리 (지양)
EXECUTE IMMEDIATE 'SELECT * FROM ' || table_name || ' WHERE id = ' || id;

-- 정적 쿼리와 바인드 변수 활용 (권장)
SELECT * FROM employees WHERE id = :id;

동일 패턴의 쿼리는 가능한 한 통일하고, 불필요한 조건 분기는 줄이는 편이 낫다.

인덱스와 조인 전략을 바꾼 사례

급여 조건을 사용하는 조회라면 salary 인덱스 또는 dept_id와 salary를 묶은 복합 인덱스를 검토할 수 있다.

튜닝 전:

SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.dept_id
AND e.salary BETWEEN 3000 AND 5000;

튜닝 후:

-- salary에 대한 인덱스 추가
CREATE INDEX idx_emp_salary ON employees(salary);

-- 또는 복합 인덱스 활용
CREATE INDEX idx_emp_dept_salary ON employees(dept_id, salary);

인덱스 범위 스캔으로 I/O를 줄이고 조인 성능을 높여 전체 응답시간을 60% 단축하는 효과가 제시된다.

여러 테이블을 조인하는 조회에서는 드라이빙 테이블과 조인 알고리즘을 조정할 수 있다.

튜닝 전:

SELECT c.customer_name, o.order_date, p.product_name
FROM customers c, orders o, order_details od, products p
WHERE c.customer_id = o.customer_id
AND o.order_id = od.order_id
AND od.product_id = p.product_id
AND o.order_date >= '2023-01-01';

튜닝 후:

SELECT /*+ ORDERED USE_NL(o) USE_NL(od) USE_NL(p) */
  c.customer_name, o.order_date, p.product_name
FROM customers c, orders o, order_details od, products p
WHERE c.customer_id = o.customer_id
AND o.order_id = od.order_id
AND od.product_id = p.product_id
AND o.order_date >= '2023-01-01';

이 방식은 드라이빙 테이블 선정과 조인 알고리즘을 조정하고, 중간 결과셋을 줄여 메모리 사용량을 낮춘다.

서브쿼리로 작성된 조건은 조인으로 바꾸어 실행계획과 데이터 접근을 단순화할 수 있다.

튜닝 전:

SELECT e.emp_id, e.emp_name, e.salary
FROM employees e
WHERE e.dept_id IN (
  SELECT dept_id FROM departments
  WHERE location_id = 1700
);

튜닝 후:

SELECT e.emp_id, e.emp_name, e.salary
FROM employees e, departments d
WHERE e.dept_id = d.dept_id
AND d.location_id = 1700;

이 사례에서는 중복 데이터 액세스를 제거하고 전체 처리 시간을 40% 감소시키는 효과가 제시된다.

성능 수치만으로 판단하지 않기

기술적 최적화보다 업무 요구사항 충족이 우선이다. 실제 데이터 볼륨과 분포를 반영해 테스트하고, 튜닝에 투입한 노력과 성능 개선 효과를 함께 측정해야 한다.

과도한 힌트 사용은 이후 유지보수를 어렵게 만들 수 있다. 튜닝 이력과 근거를 문서화해 지식을 공유하고 재활용할 수 있도록 남기는 일도 성능 개선의 일부다.