공공부하자개발 · 영어 학습 노트
홈 › 대용량·배치 › 03 / 8

인덱스 튜닝

커버링 인덱스, 컬럼 순서, 선택도, 인덱스가 오히려 느린 경우
섹션 6진행 0 / 8

3. 코드 예제

소스: sql-src/batch_03_index_tuning/01_covering.sql, 02_column_order.sql, 03_cost_of_index.sql.

세 파일 모두 orders 를 5,000행 만듭니다. emp_id 는 150, cust 는 C0C299 의 300종, ord_date 는 2023-01-01 부터 2년에 퍼집니다. status 는 DONE 65%, SHIP 20%, WAIT 10%, CANCEL 5% 입니다. 인덱스는 예제마다 만들고 지웁니다. 아래 결과는 모두 H2 형식이며 실제 DB 계획이 아닙니다.

예제 1: 재귀 CTE 로 5,000행 만들기

대용량 01 실행 계획과 같은 방식으로 재귀 CTE 로 번호를 만들어 넣습니다. status 는 나머지 연산으로 네 값에 비율을 나눠 줍니다.

sql
CREATE TABLE orders (id INT PRIMARY KEY, emp_id INT, cust VARCHAR(20), amt INT, ord_date DATE, status VARCHAR(10));
INSERT INTO orders
WITH RECURSIVE seq(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 5000
)
SELECT
       n
     , MOD(n, 50) + 1
     , 'C' || MOD(n * 7, 300)
     , (MOD(n, 97) + 1) * 100
     , DATEADD('DAY', MOD(n, 730), DATE '2023-01-01')
     , CASE
         WHEN MOD(n, 20) = 0 THEN 'CANCEL'
         WHEN MOD(n, 20) <= 2 THEN 'WAIT'
         WHEN MOD(n, 20) <= 6 THEN 'SHIP'
         ELSE 'DONE'
       END
  FROM seq;
SELECT COUNT(*) AS cnt FROM orders;
text
CNT
----
5000
(1행)

예제 2: emp_id 인덱스와 (emp_id, amt) 복합 인덱스

SELECT amt ... WHERE emp_id = 7 을 먼저 emp_id 인덱스만으로, 다음에 (emp_id, amt) 복합 인덱스로 계획을 봅니다.

sql
CREATE INDEX idx_emp ON orders (emp_id);
EXPLAIN SELECT amt FROM orders WHERE emp_id = 7;
text
PLAN
-----------------------------------------------------------------------------------------------
SELECT
    "AMT"
FROM "PUBLIC"."ORDERS"
    /* PUBLIC.IDX_EMP: EMP_ID = 7 */
WHERE "EMP_ID" = 7
(1행)
sql
DROP INDEX idx_emp;
CREATE INDEX idx_emp_amt ON orders (emp_id, amt);
EXPLAIN SELECT amt FROM orders WHERE emp_id = 7;
text
PLAN
---------------------------------------------------------------------------------------------------
SELECT
    "AMT"
FROM "PUBLIC"."ORDERS"
    /* PUBLIC.IDX_EMP_AMT: EMP_ID = 7 */
WHERE "EMP_ID" = 7
(1행)

H2 는 두 경우 모두 인덱스 조건이 EMP_ID = 7 이고 테이블 접근 단계가 따로 보이지 않아 커버링이 달라졌는지 알 수 없습니다.

첫 계획에서는 amt 를 읽으러 테이블로 가고, 둘째에서는 인덱스 안의 amt 를 바로 읽는다는 것은 실제 DB 의 계획에서 확인합니다.

조회 컬럼에 cust 를 더하면 인덱스 밖 컬럼이라 커버링이 깨지고, COUNT(*) 나 SUM(amt) 는 인덱스만으로 계산할 수 있습니다. 실제 DB 형태는 예제 6 에 도식으로 그렸습니다.

예제 3: 선택도 계산

status, cust, emp_id, ord_date 의 값 종류 수를 세고 status 와 cust 의 선택도를 비교합니다.

sql
SELECT
       COUNT(DISTINCT status) AS status_kinds
     , COUNT(DISTINCT cust) AS cust_kinds
     , COUNT(DISTINCT emp_id) AS emp_kinds
     , COUNT(DISTINCT ord_date) AS date_kinds
     , COUNT(*) AS total
  FROM orders;

SELECT
       COUNT(DISTINCT status) * 1.0 / COUNT(*) AS sel_status
     , COUNT(DISTINCT cust) * 1.0 / COUNT(*) AS sel_cust
  FROM orders;
text
STATUS_KINDS | CUST_KINDS | EMP_KINDS | DATE_KINDS | TOTAL
-------------+------------+-----------+------------+------
4            | 300        | 50        | 730        | 5000
(1행)
text
SEL_STATUS                                | SEL_CUST
------------------------------------------+------------------------------------------
0.000800000000000000000000000000000000000 | 0.060000000000000000000000000000000000000
(1행)

status 는 DONE 3,250건, SHIP 1,000건, WAIT 500건, CANCEL 250건으로 치우쳐 있습니다. H2 는 소수를 길게 출력하지만 값은 status 0.0008, cust 0.06 입니다. cust 가 status 보다 75배 높습니다. * 1.0 을 곱한 이유는 MSSQL 에서 정수끼리 나누면 소수가 잘려 0 이 되기 때문입니다.