LabHub
배우기 러닝패스 코스

SI Database Operations

Reading Plans and Improving Slow Queries

LabHub 에서 이어서 보기

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

목표

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

왜 중요한가

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

단계

  1. /root/db/tune.db 를 만들고 /opt/lab/fixtures/dbo/gen.sql 을 실행해 SALES 테이블을 생성합니다. 200,000행 이어야 합니다. 컬럼: SALE_ID, CUST_ID, SALE_DT(문자열 YYYYMMDD), PROD_CD, AMT
  2. 아래 쿼리(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 이 나타나야 합니다. (인덱스를 만들기 전에 떠야 합니다. 만든 뒤에 뜨면 계획이 달라집니다.)
  3. Q1 을 위한 인덱스 IX_SALES_01 을 만들고, 같은 쿼리의 계획을 /root/db/plan2.txt 에 저장합니다. 계획에 IX_SALES_01 이 나타나야 합니다.
  4. 인덱스 IX_SALES_BAD 를 컬럼 순서를 반대로 만들고, /root/db/composite.txt 를 만듭니다. 세 줄입니다.
    good=<Q1 에 적합한 인덱스의 컬럼 순서, 쉼표 구분>
bad=<부적합한 순서>
reason=<한 줄 근거>
  5. 아래 쿼리(Q2)를 인덱스를 탈 수 있는 형태로 다시 써 /root/db/rewrite.sql 에 저장합니다.
    SELECT COUNT(*) FROM SALES WHERE substr(SALE_DT,1,6) = '202603';
    재작성한 쿼리의 결과가 원래와 같아야 하고, 실행계획을 /root/db/plan3.txt 에 저장했을 때 인덱스를 사용해야 합니다. (필요하면 인덱스를 추가로 만드세요.)
  6. 아래 쿼리(Q3)가 커버링 인덱스만으로 처리되게 만듭니다.
    SELECT SALE_DT, AMT FROM SALES WHERE CUST_ID = 'C000123';
    실행계획을 /root/db/covering.txt 에 저장하고, 계획에 COVERING INDEX 가 나타나야 합니다.
  7. /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행입니다. 쿼리는 조인 한 번으로 작성해야 합니다(서브쿼리 반복 금지).
  8. /root/db/tuning.md 를 작성합니다. ## 대상, ## 증상, ## 원인, ## 조치, ## 결과 다섯 개의 h2 제목이 있어야 하고, 본문에 아래 세 가지가 들어가야 합니다.
    • 2단계와 3단계 계획의 차이 (SCAN → 인덱스)
    • ANALYZE 가 하는 일과 이관 직후 왜 필요한지
    • 인덱스가 INSERT 를 느리게 하는 이유

참고

대용량 테이블 생성

/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 제목이 있어야 하고, 본문에 아래 세 가지가 들어가야 합니다.

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