[DB/SQL] 성능 튜닝 기초

songeunm·2025년 6월 6일

db sql

DB & SQL

목록 보기

19/27

🎱 성능 튜닝

⚽️ 성능 튜닝

시스템 리소스를 효율적으로 사용하여 응답 시간 향상, 처리량 증대, 비용 절감을 도모하는 작업

⚽️ 튜닝 대상 및 종류

쿼리 튜닝
- SQL 문 자체를 개선
- 불필요한 연산 제거
- JOIN 순서 최적화
- EXISTS vs. IN
인덱스 튜닝
- 인덱스를 전략적으로 설계
- B-Tree/해시 인덱스, 복합 인덱스, 커버링 인덱스
DB 구조 튜닝
- 테이블 설계, 정규화 수준 조정
- 정규화/반정규화
- 파티셔닝
통계 정보 활용
- 옵티마이저가 의사결정할 수 있도록 도움
- 테이블/칼럼 통계 갱신 (ANALYZE)
리소스 튜닝
- DBMS 내부 자원 설정
- Buffer Pool
- Sort Area
- 병렬 처리 설정
물리적 튜닝
- 하드웨어, 저장소 개선
- SSD, CPU 코어 수, IOPS 증가 등

⚽️ 튜닝 순서

문제 진단
실행 계획 확인 (병목 위치 확인)
쿼리 수정 or 인덱스 적용
DB 구조 조정
통계 갱신/ 리소스 설정 점검

🎱 쿼리 성능 저하 주요 원인

⚽️ 잘못된 SQL 작성

SELECT * 남용
- 불필요한 컬럼까지 조회하여 I/O 과다 발생
조건절 누락
- WHERE 절에 인덱스 컬럼 누락 → 풀스캔 유발
서브쿼리 과다
- 비효율적인 서브쿼리 사용으로 중첩 Loop 발생

⚽️ 인덱스 미사용/오용

인덱스 없는 컬럼 조회
- 인덱스 없이 풀스캔 유발
함수 사용
- 함수 사용으로 인한 인덱스 미사용
- 인덱스는 원본 컬럼 값 기준으로 정렬되어 있기 때문
불필요한 복합 인덱스
- 사용 패턴에 안 맞는 복합 인덱스

⚽️ 데이터 분포 불균형

특정 값이 많은 경우
- 옵티마이저는 보통 분포가 균일하다고 가정하는 경우 많음
- 옵티마이저가 잘못된 선택을 할 가능성 높음

⚽️ 통계 정보 미갱신

ANALYZE / UPDATE STATS 미사용
- 옵티마이저 판단 오류 → 잘못된 실행 계획 사용

⚽️ JOIN 과다 or 순서 비효율

불필요한 JOIN
- 필요없는 테이블까지 JOIN하여 과부하
JOIN 순서 비효율
- 작은 테이블 → 큰 테이블 순서 JOIN
- 큰 테이블 먼저 JOIN 시 불필요한 데이터 생성

⚽️ 정규화 수준 미흡

중복 데이터
- 테이블 사이즈 증가
- 조인 비용 증가

⚽️ 서버 설정 or 하드웨어 병목

메모리 부족, CPU 스파이크
- DB 레벨 튜닝만으로는 한계

➕ 성능이 갑자기 떨어지는 경우

갑자기 느려짐
- 통계 정보 변경
- 인덱스 삭제
- 쿼리 캐시 만료
주기적으로 느려짐
- 배치 작업, 백업 작업과 충돌
특정 조건에서만 느려짐
- 인덱스 스킵
- 데이터 분포 불균형

🎱 쿼리 튜닝 기본 전략

불필요한 컬럼 조회 지양

모든 컬럼을 조회하는 * → 디스크 I/O 증가 + 인덱스 사용 어려움

필요한 컬럼만 명시

-- 모든 컬럼 선택
SELECT * FROM Users WHERE id = 1001;

-- 개선: 필요한 컬럼만 선택
SELECT name, email FROM Users WHERE id = 1001;

필터 조건을 WHERE 절로 분리

HAVING은 집계 이후 필터링

HAVING 보다 WHERE가 빠름

-- HAVING을 사용한 필터링
SELECT user_id, COUNT(*) 
FROM Orders 
GROUP BY user_id 
HAVING user_id = 1001;

-- 개선: 가능하다면 WHERE에서 필터링
SELECT user_id, COUNT(*) 
FROM Orders 
WHERE user_id = 1001 
GROUP BY user_id;

인덱스 컬럼에 함수 사용 지양

함수 사용시 인덱스 활용이 불가하여 풀스캔 발생

인덱스 컬럼은 함수 없이 비교

-- 인덱스 미사용
SELECT name FROM Orders WHERE UPPER(name) = 'ALICE';

-- 개선: 함수 사용하지 않고 조건 작성
SELECT name FROM Orders WHERE name = 'Alice';

JOIN 순서와 조건 확인
- JOIN 순서는 옵티마이저가 결정하지만 잘못된 조건 순서 or 누락 시 Nested Loop 발생 등 성능 저하 가능
- 각 테이블의 건수, 인덱스 유무 고려 필요
```
-- 조인 조건 누락 -> 카티션 곱(Cartesian Product) 위험
SELECT * FROM A, B WHERE A.id > 10;

-- 개선: JOIN 명시
SELECT * FROM A JOIN B ON A.key = B.key WHERE A.id > 10;
```

서브쿼리 vs JOIN 비교

JOIN이 서브쿼리보다 빠른 경우가 많음
특히 상관 서브쿼리는 루프당 재실행 → 느림

상관 서브쿼리
- 외부 쿼리의 값을 서브쿼리 안에서 사용하는 서브쿼리

-- 느린 상관 서브쿼리
SELECT name FROM Users u 
WHERE EXISTS (
  SELECT 1 FROM Orders o 
  WHERE o.user_id = u.id AND o.amount > 100
  -- u.id는 Users 테이블의 값. 이를 서브쿼리 안에서 사용중
  -- -> Users 테이블 각 행마다 Orders 서브쿼리 실행 -> 비효율
);

-- 개선: 서브쿼리 대신 JOIN 사용
SELECT DISTINCT u.name 
FROM Users u
JOIN Orders o ON u.id = o.user_id 
WHERE o.amount > 100;

LIMIT / OFFSET 사용시 주의
- OFFSET은 앞 데이터를 스캔 후 버리는 방식 (스캔이 일어나긴 함)
- 큰 OFFSET은 성능 저하
- OFFSET
  - 결과 셋에서 앞부분 데이터 건너뛰는 역할
- 커서 기반 페이지네이션
  - 이전 페이지의 마지막 값을 기억하고, 그 다음부터 조회하는 방식
  - 필요한 지점부터 조회 → 성능 좋음
  - 대규모 데이터의 경우 커서 방식이 좋음
```
-- 큰 OFFSET
SELECT * FROM Orders ORDER BY created_at LIMIT 10 OFFSET 100000;

-- 개선: 커서 방식 또는 마지막 ID 기억해서 조건 필터링
SELECT * FROM Orders 
WHERE created_at < '2024-06-01 00:00:00' 
ORDER BY created_at DESC 
LIMIT 10;
```
DISTINCT / GROUP BY 최소화
- 중복 제거는 내부적으로 정렬 or 해시 테이블을 사용
  - 만약 DISTINCT / GROUP BY 대상 컬럼이 인덱스가 있다면
    정렬 단계 생략 → 효율적
- 중복 제거는 많은 정렬, 비교 연산 필요
- 꼭 필요한 경우에만 사용

🎱 인덱스 튜닝 전략

⚽️ 인덱스 작동 원리

(B-Tree의 경우) 루트부터 리프노드까지 내려가며 비교하여 탐색
리프노드에는 해당 행의 주소(RID) 또는 데이터 자체가 있음
Table Lookup
- 인덱스 탐색 + 테이블 액세스 (인덱스에서 RID 찾아 테이블에서 데이터 조회)
  → 비용 발생
- 커버링 인덱스로 개선
인덱스가 효과적인 경우
- WHERE절에 자주 사용되는 컬럼
- JOIN, GROUP BY, ORDER BY 대상 컬럼
- 데이터가 균등하게 분포된 컬럼

⚽️ 인덱스 튜닝 전략

자주 조회하는 컬럼에 인덱스 생성
- WHERE, JOIN, ORDER BY, GROUP BY 에 빈번히 등장하는 컬럼에 인덱스 고려
- 특히 데이터 건수가 수십만 건 이상이면 효과 큼!
선행 컬럼 원칙 준수
- 복합 인덱스는 왼쪽부터 차례로 조건에 써야 동작함
- 중간부터 조건에 쓸 수 없음! 주의
- 일부만 조건에 써야하는 경우 인덱스 사용 못함 → 튜닝 효과 낮음
선택도가 높은 컬럼에 인덱스 적용
- 선택도: 전체 행 중에서 조건에 의해 선택되는 행의 비율
- 선택도 낮은 컬럼에 인덱스를 걸면 성능 개선 효과 낮음
커버링 인덱스 고려
- 쿼리에 사용하는 모든 컬럼이 인덱스에 포함되면 다시 테이블에 접근할 필요 없이 처리 가능
- 디스크 I/O 감소 → 성능 향상
- 단점
  - 인덱스 크기 증가 → 공간 효율성 저하
  - DML 성능 저하 → 인덱스 갱신 비용이 커짐 → 쓰기 성능 저하
  - 유지관리 복잡성 → 인덱스 관리 복잡도 증가
  - 설계 난이도 증가 → 쿼리 패턴 변경 시 인덱스 재설계 필요
- 읽기 성능이 매우 중요하고, 자주 사용하는 쿼리에 사용
인덱스 남용 주의
- 인덱스가 많아지면 INSERT, UPDATE, DELETE (쓰기)성능은 감소
- 불필요하거나 중복된 인덱스는 제거
조건절 함수 사용 주의
- 가능하면 함수 없이 범위 조건으로 작성
- 인덱스는 컬럼의 원본 데이터를 기준으로 정렬되기 때문

인덱스 재구성 고려

대량 DML 이후 인덱스가 조각날 수 있음

ALTER INDEX REBUILD 또는 OPTIMIZE TABLE 로 성능 회복 가능

-- 인덱스 리빌드 (MSSQL)
ALTER INDEX [인덱스명] ON [테이블명] REBUILD;
-- 테이블의 모든 인덱스 리빌드
ALTER INDEX ALL ON [테이블명] REBUILD;

-- 조각화율(Fragmentation Percent) 확인
-- 조각화율이 30% 이하라면 ORGANIZE(페이지 재정렬)
-- 30% 이상이라면 REBUILD(페이지 재생성)
SELECT 
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips
    JOIN sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE 
    ips.database_id = DB_ID();

🎱 실행 계획 해석하기

⚽️ 실행 계획

SQL이 실행될 때 어떤 순서로 어떤 방식으로 데이터를 읽고 처리할지에 대한 DBMS의 계획
옵티마이저가 통계 정보를 바탕으로 최적의 실행 계획을 선택
실행 계획을 보면 인덱스 사용 여부, JOIN 순서, 스캔 방식 등 확인 가능
실행 계획 확인 방법
DBMS 실행 계획 확인 명령어
MySQL EXPLAIN, EXPLAIN ANALYZE
MSSQL 실행계획 보기
(SSMS에서 ‘실행 계획 보기’ 체크)
Oracle EXPLAIN PLAN, AUTOTRACE
PostgreSQL EXPLAIN, EXPLAIN ANALYZE

DBMS	실행 계획 확인 명령어
MySQL	EXPLAIN, EXPLAIN ANALYZE
MSSQL	실행계획 보기
(SSMS에서 ‘실행 계획 보기’ 체크)
Oracle	EXPLAIN PLAN, AUTOTRACE
PostgreSQL	EXPLAIN, EXPLAIN ANALYZE

⚽️ 실행 계획 해석 방법

실행 계획 주요 항목 (MySQL 기준)
- id
  - 실행 단계 (서브쿼리 순서 등)
- select_type
  - SELECT 타입
- table
  - 접근 대상 테이블
- type
  - 접근 방식
  - ALL, index, range, ref, const, eq_ref
- key
  - 사용된 인덱스 이름
- key_len
  - 인덱스 키 길이
- rows
  - 예측되는 탐색 행 수
- Extra
  - 추가정보 (Using index, Using shere, Using temporary 등)
예시
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE Orders ref idx_user idx_user 4 const 10 Using where
- 위의 실행 계획 예시를 분석
  - id
    - 1 → 단일 쿼리
  - select_type
    - SIMPLE → 서브쿼리 or UNION이 없는 단순 쿼리
  - table
    - Orders → 조회 테이블명
  - type
    - ref → 인덱스를 사용한 동등 조건 조회
  - possible_keys
    - idx_user → 사용 가능한 인덱스
  - key
    - idx_user → 실제 사용된 인덱스
  - key_len
    - 4 → 인덱스 사용 시 참조한 바이트 수
  - ref
    - const → 조건으로 사용된 값이 상수
  - rows
    - 10 → 예측 탐색 행 수 10건
  - Extra
    - Using where → WHERE절 필터링도 수행함
- type이 ref, eq_ref, const → 빠른 인덱스 탐색
- type이 ALL 이면 풀스캔
- Extra에 Using filesort, Using temporary → 성능 저하 가능성 있음
- Extra에 Using index → 인덱스만으로 SELECT 컬럼을 모두 해결한다는 뜻
  (커버링 인덱스라면 Extra에 Using index가 뜸)

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Orders	ref	idx_user	idx_user	4	const	10	Using where

⚽️ 실행 계획 해석 기반 튜닝

실행 계획 해석
- type이 ALL → 풀스캔
- type이 index → 인덱스 전체 스캔
- key가 NULL → 사용 가능한 인덱스 없음 / 옵티마이저가 인덱스 무시
- rows가 많음 → 인덱스 마비 / 비효율적 인덱스
- Extra
해결 전략
- 인덱스 생성 고려
- 복합 인덱스의 경우 컬럼 순서 설계 주의

➡️ 실행 계획을 보고 직관적으로 알 수 있는건 인덱스 튜닝 전략

➡️ 쿼리 튜닝 / 통계 정보 & 구조적 튜닝은 실행 계획 + 쿼리 내용 + 상황 파악 필요

songeunm

데굴데굴 구르는 개발자 지망생

이전 포스트

[DB/SQL] 데이터베이스 설계 기초 (ERD, 정규화, 인덱스, 파티셔닝)

다음 포스트