実行計画を読んで遅いクエリを改善する

한국어 원문으로 표시합니다.

목표

실행계획을 읽고, 인덱스 유무·컬럼 순서·함수 조건·커버링 여부가 계획을 어떻게 바꾸는지 직접 확인하며, N+1 을 조인으로 개선하고 튜닝 리포트를 남길 수 있게 됩니다.

왜 중요한가

실행계획을 읽는다는 것은 노드 이름을 훑는 일이 아니라 옵티마이저의 예측과 실제 결과의 괴리를 찾는 일입니다. 그리고 cost 는 시간이 아니라 상대 점수라서 "cost 가 10000 이 넘으면 위험하다" 같은 기준은 근거가 없습니다. 또 하나 중요한 태도 — 전체 스캔이 항상 나쁜 것이 아닙니다. 대부분의 행을 읽어야 하는 쿼리에서는 스캔이 최선이고, "스캔이 보이니 인덱스를 만들자"는 반사적 반응은 인덱스만 늘리고 쓰기를 느리게 만듭니다. 이 실습은 그 판단을 계획을 보면서 직접 해 보는 것입니다.

단계

/root/db/tune.db 를 만들고 /opt/lab/fixtures/dbo/gen.sql 을 실행해 SALES 테이블을 생성합니다. 200,000행 이어야 합니다. 컬럼: SALE_ID, CUST_ID, SALE_DT(문자열 YYYYMMDD), PROD_CD, AMT
아래 쿼리(Q1)의 실행계획을 /root/db/plan1.txt 에 저장합니다.
```
SELECT SALE_ID, SALE_DT, AMT FROM SALES
 WHERE CUST_ID = 'C000123' AND SALE_DT BETWEEN '20260101' AND '20260630';
```
계획에 SCAN 이 나타나야 합니다. (인덱스를 만들기 전에 떠야 합니다. 만든 뒤에 뜨면 계획이 달라집니다.)
Q1 을 위한 인덱스 IX_SALES_01 을 만들고, 같은 쿼리의 계획을 /root/db/plan2.txt 에 저장합니다. 계획에 IX_SALES_01 이 나타나야 합니다.

인덱스 IX_SALES_BAD 를 컬럼 순서를 반대로 만들고, /root/db/composite.txt 를 만듭니다. 세 줄입니다.

good=<Q1 에 적합한 인덱스의 컬럼 순서, 쉼표 구분>
bad=<부적합한 순서>
reason=<한 줄 근거>

아래 쿼리(Q2)를 인덱스를 탈 수 있는 형태로 다시 써 /root/db/rewrite.sql 에 저장합니다.
```
SELECT COUNT(*) FROM SALES WHERE substr(SALE_DT,1,6) = '202603';
```
재작성한 쿼리의 결과가 원래와 같아야 하고, 실행계획을 /root/db/plan3.txt 에 저장했을 때 인덱스를 사용해야 합니다. (필요하면 인덱스를 추가로 만드세요.)
아래 쿼리(Q3)가 커버링 인덱스만으로 처리되게 만듭니다.
```
SELECT SALE_DT, AMT FROM SALES WHERE CUST_ID = 'C000123';
```
실행계획을 /root/db/covering.txt 에 저장하고, 계획에 COVERING INDEX 가 나타나야 합니다.
/opt/lab/fixtures/dbo/gen.sql 이 함께 만든 CUSTOMER_M 테이블을 이용해, "고객별 총 매출 상위 10명(고객명 포함)"을 구하는 쿼리를 /root/db/join.sql 에 작성하고 결과를 /root/db/join-result.txt 에 저장합니다. 결과는 CUST_ID,CUST_NM,TOTAL 세 컬럼, TOTAL 내림차순 10행입니다. 쿼리는 조인 한 번으로 작성해야 합니다(서브쿼리 반복 금지).
/root/db/tuning.md 를 작성합니다. ## 대상, ## 증상, ## 원인, ## 조치, ## 결과 다섯 개의 h2 제목이 있어야 하고, 본문에 아래 세 가지가 들어가야 합니다.
- 2단계와 3단계 계획의 차이 (SCAN → 인덱스)
- ANALYZE 가 하는 일과 이관 직후 왜 필요한지
- 인덱스가 INSERT 를 느리게 하는 이유

참고

실행계획: EXPLAIN QUERY PLAN <쿼리>;
통계 갱신: ANALYZE;
시간 측정: .timer on
흔한 실수 1: 인덱스를 만들고 ANALYZE 를 안 해서 계획이 그대로인 것.
흔한 실수 2: 재작성한 쿼리의 결과가 원래와 달라지는 것. substr(SALE_DT,1,6)='202603' 은 3월 1일부터 3월 31일까지입니다.
흔한 실수 3: 커버링을 만든다며 인덱스에 컬럼을 다 넣는 것. 인덱스가 테이블만큼 커지면 이득이 사라집니다.

대용량 테이블 생성

/root/db/tune.db 를 만들고 /opt/lab/fixtures/dbo/gen.sql 을 실행해 SALES 테이블을 생성합니다. 200,000행 이어야 합니다. 컬럼: SALE_ID, CUST_ID, SALE_DT(문자열 YYYYMMDD), PROD_CD, AMT

재귀 CTE 로 대량 행을 만들 수 있습니다. 생성 후 건수를 확인하세요.

인덱스 없는 실행계획

아래 쿼리(Q1)의 실행계획을 /root/db/plan1.txt 에 저장합니다.

SELECT SALE_ID, SALE_DT, AMT FROM SALES
 WHERE CUST_ID = 'C000123' AND SALE_DT BETWEEN '20260101' AND '20260630';

계획에 SCAN 이 나타나야 합니다. (인덱스를 만들기 전에 떠야 합니다. 만든 뒤에 뜨면 계획이 달라집니다.)

튜닝의 출발점은 개선 전 상태를 기록해 두는 것입니다. 계획에 어떤 단어가 나오는지 눈여겨보세요.

인덱스 생성 후 비교

Q1 을 위한 인덱스 IX_SALES_01 을 만들고, 같은 쿼리의 계획을 /root/db/plan2.txt 에 저장합니다. 계획에 IX_SALES_01 이 나타나야 합니다.

같은 쿼리의 계획이 어떻게 달라지는지 봅니다. 인덱스 이름이 계획에 나타나는지 확인하세요.

복합 인덱스 컬럼 순서

인덱스 IX_SALES_BAD 를 컬럼 순서를 반대로 만들고, /root/db/composite.txt 를 만듭니다. 세 줄입니다.

good=<Q1 에 적합한 인덱스의 컬럼 순서, 쉼표 구분>
bad=<부적합한 순서>
reason=<한 줄 근거>

등치 조건 컬럼이 앞, 범위 조건 컬럼이 뒤입니다. 두 순서를 모두 만들어 계획을 비교해 보면 차이가 분명해집니다.

함수 조건 제거

아래 쿼리(Q2)를 인덱스를 탈 수 있는 형태로 다시 써 /root/db/rewrite.sql 에 저장합니다.

SELECT COUNT(*) FROM SALES WHERE substr(SALE_DT,1,6) = '202603';

재작성한 쿼리의 결과가 원래와 같아야 하고, 실행계획을 /root/db/plan3.txt 에 저장했을 때 인덱스를 사용해야 합니다. (필요하면 인덱스를 추가로 만드세요.)

컬럼에 함수를 씌우면 인덱스의 정렬 순서를 쓸 수 없습니다. 같은 결과를 내는 범위 조건으로 바꿔 보세요.

커버링 인덱스

아래 쿼리(Q3)가 커버링 인덱스만으로 처리되게 만듭니다.

SELECT SALE_DT, AMT FROM SALES WHERE CUST_ID = 'C000123';

실행계획을 /root/db/covering.txt 에 저장하고, 계획에 COVERING INDEX 가 나타나야 합니다.

쿼리가 필요한 컬럼이 전부 인덱스에 있으면 테이블을 읽지 않습니다. 계획에 특정 단어가 추가로 나타납니다.

N+1 을 조인으로

/opt/lab/fixtures/dbo/gen.sql 이 함께 만든 CUSTOMER_M 테이블을 이용해, "고객별 총 매출 상위 10명(고객명 포함)"을 구하는 쿼리를 /root/db/join.sql 에 작성하고 결과를 /root/db/join-result.txt 에 저장합니다. 결과는 CUST_ID,CUST_NM,TOTAL 세 컬럼, TOTAL 내림차순 10행입니다. 쿼리는 조인 한 번으로 작성해야 합니다(서브쿼리 반복 금지).

반복 조회를 한 번의 조인으로 바꿉니다. 결과 집합이 원래와 같아야 개선이라 할 수 있습니다.

튜닝 리포트

/root/db/tuning.md 를 작성합니다. ## 대상, ## 증상, ## 원인, ## 조치, ## 결과 다섯 개의 h2 제목이 있어야 하고, 본문에 아래 세 가지가 들어가야 합니다.

2단계와 3단계 계획의 차이 (SCAN → 인덱스)
ANALYZE 가 하는 일과 이관 직후 왜 필요한지
인덱스가 INSERT 를 느리게 하는 이유

6개월 뒤 누군가 이 인덱스를 지우려 할 때 필요한 문서입니다. 증상·원인·조치·결과를 숫자와 함께 남기세요.