인덱스 설계 기초 - 결합 인덱스 컬럼 순서 정하기

이 장에서 배우는 것

앞 장에서는 Range, Unique, Full, Fast Full, Skip Scan 같은 인덱스 스캔 방식이 어떻게 동작하는지를 다뤘다. 그런데 실무에서 마주치는 인덱스 대부분은 단일 컬럼이 아니라 여러 컬럼을 묶은 결합 인덱스(composite index)다. 같은 컬럼들로 인덱스를 만들더라도 컬럼을 어떤 순서로 배치하느냐에 따라 같은 조건문이 액세스 조건이 되기도 하고 필터 조건이 되기도 한다. 이 장은 온라인 서점의 주문 테이블을 예로 들어, 등치 조건과 범위 조건이 섞인 결합 인덱스의 컬럼 순서를 정하는 기준과 그 기준이 실행계획에 어떻게 드러나는지를 살펴본다.

등치 조건과 범위 조건을 구분하고 인덱스에서 어느 쪽을 앞에 둘지 판단할 수 있다
DBMS_XPLAN의 Predicate Information에서 access 조건과 filter 조건을 구분해 읽을 수 있다
결합 인덱스 컬럼 순서가 잘못됐을 때 실행계획이 어떻게 나빠지는지 설명할 수 있다
인덱스 개수를 늘리는 것과 DML 부하 사이의 균형을 판단 기준으로 설명할 수 있다

문제 상황

온라인 서점의 주문 테이블은 1,000만 건 규모이며, 서비스 초기에는 다음과 같이 회원번호 단일 컬럼 인덱스만 두고 있었다.

CREATE TABLE 주문 (
   주문번호   NUMBER        PRIMARY KEY,
   회원번호   NUMBER        NOT NULL,
   주문일시   DATE          NOT NULL,
   주문상태   VARCHAR2(10)  NOT NULL,
   총액       NUMBER(10)    NOT NULL
);

CREATE INDEX IX_주문_회원번호 ON 주문 (회원번호);

고객센터 화면에 "주문상태별로, 최근 몇 개월 안에" 조건이 추가되면서 문제가 드러났다. 회원번호 인덱스는 여전히 특정 회원의 주문을 빠르게 찾아주지만, 그 회원이 주문을 수십 건 넘게 쌓아 둔 경우 주문상태와 기간 조건은 인덱스에서 걸러지지 않고 테이블까지 가져온 뒤에야 필터링됐다. 결과적으로 화면 하나를 여는 데 필요한 논리 읽기가 눈에 띄게 늘었다. 인덱스를 하나 더 만들면 되는 문제처럼 보이지만, 어떤 컬럼을 몇 번째에 두느냐에 따라 결과가 크게 달라진다.

등치 조건과 범위 조건, 인덱스 안에서의 위치

결합 인덱스는 나열한 컬럼 순서 그대로 정렬된 B*Tree다. 첫 번째 컬럼으로 먼저 정렬하고, 값이 같은 구간 안에서 두 번째 컬럼으로 다시 정렬하는 식이다. 이 정렬 규칙 때문에 등치 조건(=)과 범위 조건(BETWEEN, >=, <, LIKE 'AB%' 등)을 같은 인덱스에 함께 쓸 때는 등치 조건을 앞에, 범위 조건을 뒤에 두는 것이 기본 원칙이다.

이유는 단순하다. 등치 조건은 인덱스 안의 구간을 정확한 한 지점으로 좁힌다. 그 좁아진 구간 안에서 다음 컬럼을 다시 정렬된 상태로 훑을 수 있으므로, 뒤따르는 범위 조건도 여전히 access 조건으로 쓸 수 있다. 반대로 범위 조건이 먼저 나오면, 그 뒤로는 값이 연속된 여러 지점에 걸쳐 흩어져 있어 정렬 순서를 더 이상 좁히는 데 쓸 수 없다. 뒤에 오는 등치 조건은 인덱스를 읽는 동안 하나하나 값을 비교해서 걸러내는 filter 조건으로 내려간다.

액세스 조건과 필터 조건

access 조건은 인덱스 구조 자체를 이용해 시작 지점과 끝 지점을 정하는 조건이고, filter 조건은 그 구간 안에서 스캔한 행마다 값을 비교해 맞는지 아닌지 확인하는 조건이다. 둘 다 조건을 "만족시키는" 결과는 같지만, access 조건은 읽는 블록 수 자체를 줄이고 filter 조건은 이미 읽은 블록 안에서 버릴 행을 고른다. DBMS_XPLAN의 Predicate Information 절에서 access(...)와 filter(...)로 구분해 보여주므로, 인덱스 컬럼 순서가 의도대로 동작하는지는 이 절만 봐도 확인된다.

등치 조건을 선두에 둔 인덱스는 3건만 접근해 끝나지만 범위 조건을 앞세운 인덱스는 8건을 접근한 뒤 5건을 필터로 버린다

그림에서 보듯 컬럼 순서만 바꿔도 인덱스가 실제로 접근하는 항목 수가 달라진다. 좋은 순서에서는 등치 조건 두 개(회원번호, 주문상태)가 먼저 구간을 좁히고, 그 안에서 주문일시 범위가 다시 access 조건으로 동작해 딱 필요한 만큼만 읽는다. 나쁜 순서에서는 주문일시 범위가 먼저 오는 바람에 주문상태 조건이 filter로 밀려나, 필요 없는 항목까지 읽었다가 버리는 일이 생긴다.

같은 조건, 다른 컬럼 순서에서 access/filter가 어떻게 갈리는지
인덱스 컬럼 순서	access 조건	filter 조건	결과
(회원번호, 주문상태, 주문일시)	회원번호=, 주문상태=, 주문일시 범위	없음	필요한 행만 읽음
(회원번호, 주문일시, 주문상태)	회원번호=, 주문일시 범위	주문상태=	범위 안 행을 모두 읽고 걸러냄

인덱스 개수와 DML 부하의 균형

조건마다 인덱스를 따로 만들면 어떤 조합의 쿼리가 와도 인덱스 하나쯤은 걸릴 것 같지만, 인덱스는 조회 성능만 좌우하지 않는다. 테이블에 행 하나가 INSERT, UPDATE, DELETE될 때마다 그 테이블에 걸린 인덱스도 함께 갱신돼야 한다. 주문 테이블처럼 초당 수백 건씩 INSERT가 몰리는 테이블에서 불필요한 인덱스를 늘리면, 조회는 조금 빨라질지 몰라도 쓰기 지연과 리프 블록 분할(leaf block split)이 함께 늘어난다.

등치 조건 여러 개와 범위 조건 하나를 결합 인덱스 하나로 묶으면, 단일 컬럼 인덱스 여러 개를 두는 것보다 조회 조건을 더 정확히 좁히면서도 유지해야 할 인덱스 개수는 줄어든다. 다만 결합 인덱스는 선두 컬럼부터 순서대로만 활용되므로, 선두 컬럼만 조건으로 오는 쿼리에서는 여전히 그 컬럼 하나짜리 인덱스와 비슷하게 동작한다. 즉 결합 인덱스를 설계할 때는 "이 인덱스가 커버할 쿼리 패턴이 무엇인지"를 먼저 정하고, 그 패턴에서 등치 조건을 앞으로, 범위 조건을 뒤로 배치하는 순서로 접근해야 한다.

단일 컬럼 인덱스 4개를 유지하면 INSERT 한 건마다 쓰기가 4회 발생하지만 PK와 결합 인덱스 1개로 정리하면 쓰기가 2회로 줄어든다

완성 코드

index_order_demo.sql

-- Oracle 19c
SET SERVEROUTPUT ON;

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE 주문 PURGE';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;
/

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE 회원 PURGE';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;
/

CREATE TABLE 회원 (
   회원번호   NUMBER        PRIMARY KEY,
   이메일     VARCHAR2(100) NOT NULL,
   등급       VARCHAR2(10)  NOT NULL
);

CREATE TABLE 주문 (
   주문번호   NUMBER        PRIMARY KEY,
   회원번호   NUMBER        NOT NULL REFERENCES 회원(회원번호),
   주문일시   DATE          NOT NULL,
   주문상태   VARCHAR2(10)  NOT NULL,
   총액       NUMBER(10)    NOT NULL
);

BEGIN
   FOR i IN 1..2000 LOOP
      INSERT INTO 회원 (회원번호, 이메일, 등급)
      VALUES (i, 'user' || i || '@mail.com',
              CASE MOD(i, 5) WHEN 0 THEN 'VIP' ELSE '일반' END);
   END LOOP;
   COMMIT;
END;
/

BEGIN
   FOR i IN 1..20000 LOOP
      INSERT INTO 주문 (주문번호, 회원번호, 주문일시, 주문상태, 총액)
      VALUES (
         i,
         MOD(i, 2000) + 1,
         DATE '2024-01-01' + MOD(i, 730),
         CASE MOD(i, 4)
            WHEN 0 THEN '결제완료'
            WHEN 1 THEN '배송중'
            WHEN 2 THEN '배송완료'
            ELSE '취소'
         END,
         5000 + MOD(i, 40) * 1000
      );
   END LOOP;
   COMMIT;
END;
/

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, '주문', CASCADE => TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, '회원', CASCADE => TRUE);

-- 좋은 순서: 등치(회원번호, 주문상태) 먼저, 범위(주문일시) 나중
CREATE INDEX IX_주문_MEM_STAT_DT ON 주문 (회원번호, 주문상태, 주문일시);

EXPLAIN PLAN FOR
SELECT 주문번호, 주문일시, 총액
FROM   주문
WHERE  회원번호 = 777
AND    주문상태 = '배송완료'
AND    주문일시 >= DATE '2024-06-01'
AND    주문일시 <  DATE '2024-09-01'
ORDER BY 주문일시 DESC;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

DROP INDEX IX_주문_MEM_STAT_DT;

-- 나쁜 순서: 범위(주문일시)가 등치(주문상태)보다 앞에 옴
CREATE INDEX IX_주문_MEM_DT_STAT ON 주문 (회원번호, 주문일시, 주문상태);

EXPLAIN PLAN FOR
SELECT 주문번호, 주문일시, 총액
FROM   주문
WHERE  회원번호 = 777
AND    주문상태 = '배송완료'
AND    주문일시 >= DATE '2024-06-01'
AND    주문일시 <  DATE '2024-09-01'
ORDER BY 주문일시 DESC;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

줄별 해설

맨 앞의 두 익명 블록은 이전 실행에서 남은 테이블을 정리한다. 테이블이 없으면 ORA-00942가 나는데, 이를 무시하고 그 외 오류만 다시 던지도록 했다. 반복 실행해도 매번 같은 결과가 나오게 하려는 장치이며, 인덱스 설계 자체와는 무관하다.

회원, 주문 테이블은 이 장의 문제 상황에서 본 최소 스키마를 그대로 쓴다. 주문 테이블에 회원번호 단일 인덱스를 다시 만들지 않은 이유는, 이 장에서 다룰 결합 인덱스가 회원번호를 선두 컬럼으로 포함하므로 단일 인덱스를 따로 둘 필요가 없기 때문이다.

회원 2,000건, 주문 20,000건을 생성하는 두 PL/SQL 블록은 회원 한 명당 평균 10건의 주문을 갖는 분포를 만든다. 주문상태는 MOD(i, 4)로 네 값이 고르게 섞이고, 주문일시는 2024-01-01부터 730일 구간에 흩어진다. 데이터 규모가 작아 보이지만, 이 장의 목적은 실제 성능 수치가 아니라 access/filter 조건이 어떻게 갈리는지 확인하는 것이므로 이 정도 분포로 충분하다.

DBMS_STATS.GATHER_TABLE_STATS는 옵티마이저가 인덱스를 얼마나 좁게 쓸 수 있는지 판단할 통계를 만든다. 통계 없이 실행계획을 보면 딕셔너리 기본값에 의존하게 되어 컬럼 순서 차이가 실행계획에 잘 드러나지 않는다.

IX_주문_MEM_STAT_DT는 등치 조건인 회원번호, 주문상태를 앞에 두고 범위 조건인 주문일시를 마지막에 둔 인덱스다. 뒤이은 EXPLAIN PLAN과 DBMS_XPLAN.DISPLAY로 이 인덱스가 어떻게 쓰이는지 확인한다.

인덱스를 지우고 IX_주문_MEM_DT_STAT를 다시 만드는 부분은 똑같은 조건, 똑같은 컬럼 세 개를 쓰면서 순서만 (회원번호, 주문일시, 주문상태)로 바꾼 경우다. 같은 쿼리를 다시 EXPLAIN PLAN으로 확인해 두 실행계획의 Predicate Information을 비교한다.

실행 결과

SQL> @index_order_demo.sql

PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.

Table created.
Table created.

PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.

Index created.

Explained.

Plan hash value: 1894672503

--------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                 | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                       |     3 |   126 |     4   (0)|
|   1 |  SORT ORDER BY                        |                       |     3 |   126 |     4   (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED | 주문                  |     3 |   126 |     3   (0)|
|*  3 |    INDEX RANGE SCAN                   | IX_주문_MEM_STAT_DT   |     3 |       |     2   (0)|
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("회원번호"=777 AND "주문상태"='배송완료' AND "주문일시">=DATE'2024-06-01'
              AND "주문일시"<DATE'2024-09-01')

Index dropped.

Index created.

Explained.

Plan hash value: 2938104671

--------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                 | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                       |     3 |   126 |    10   (0)|
|   1 |  SORT ORDER BY                        |                       |     3 |   126 |    10   (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED | 주문                  |     3 |   126 |     9   (0)|
|*  3 |    INDEX RANGE SCAN                   | IX_주문_MEM_DT_STAT   |    10 |       |     6   (0)|
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("회원번호"=777 AND "주문일시">=DATE'2024-06-01' AND "주문일시"<DATE'2024-09-01')
       filter("주문상태"='배송완료')

두 실행계획 모두 SORT ORDER BY와 INDEX RANGE SCAN이라는 같은 모양이지만, id 3의 Predicate Information이 다르다. 좋은 순서에서는 네 조건이 모두 access로 묶였고, 나쁜 순서에서는 주문상태가 filter로 분리됐다. INDEX RANGE SCAN의 예상 Rows도 3에서 10으로 늘었는데, 이는 access 조건만으로 좁혀지는 인덱스 구간이 더 넓어졌기 때문이다. Cost 수치는 통계와 환경에 따라 달라질 수 있으므로 절대값보다 access/filter 구분과 Rows 차이에 주목해서 읽는다.

Oracle 19c와 MySQL 8의 결합 인덱스 컬럼 순서 관련 차이
구분	Oracle 19c	MySQL 8
컬럼 순서 원칙	등치 먼저, 범위 나중	동일(leftmost prefix 규칙)
실행계획 확인 도구	DBMS_XPLAN, access/filter 절	EXPLAIN, key_len과 Extra의 Using where
후행 등치조건 처리	인덱스 스캔 안에서 filter로 표시	Index Condition Pushdown으로 인덱스 단계에서 일부 처리, Extra에 Using index condition
Skip Scan 지원	정식 지원(INDEX SKIP SCAN)	8.0.13부터 옵티마이저 힌트로 부분 지원

실무에서 자주 틀리는 것

범위 조건을 선두 컬럼에 둔다

정렬 대상이라는 이유만으로 범위 조건 컬럼을 앞에 놓는 경우가 있다.

-- 틀린 예: 범위 조건인 주문일시가 선두
CREATE INDEX IX_주문_DT_MEM ON 주문 (주문일시, 회원번호);
-- 회원번호=까지는 access, 그 이상 조건은 대부분 filter로 밀림

-- 고친 예: 등치 조건인 회원번호를 선두로
CREATE INDEX IX_주문_MEM_DT ON 주문 (회원번호, 주문일시);
-- 회원번호=, 주문일시 범위 모두 access

결합 인덱스 순서와 ORDER BY 컬럼이 어긋난다

조회 조건만 보고 컬럼 순서를 정하면, 인덱스가 이미 정렬해 둔 순서를 ORDER BY가 다시 뒤집어야 하는 경우가 생긴다.

-- 틀린 예: 주문상태가 정렬 기준보다 뒤에 있어 별도 정렬 필요
CREATE INDEX IX_주문_MEM_DT_STAT2 ON 주문 (회원번호, 주문상태, 총액);
SELECT * FROM 주문 WHERE 회원번호=777 AND 주문상태='배송완료' ORDER BY 주문일시 DESC;
-- SORT ORDER BY가 실행계획에 그대로 남음

-- 고친 예: ORDER BY 대상 컬럼을 인덱스 마지막에 배치
CREATE INDEX IX_주문_MEM_STAT_DT ON 주문 (회원번호, 주문상태, 주문일시);
-- 인덱스 순서 자체가 정렬을 제공해 SORT ORDER BY 생략 가능

조건마다 단일 컬럼 인덱스를 따로 만든다

조건이 세 개면 인덱스도 세 개 있어야 안전하다고 여기는 경우가 있다.

-- 틀린 예: 조건마다 인덱스 하나씩
CREATE INDEX IX1 ON 주문 (회원번호);
CREATE INDEX IX2 ON 주문 (주문상태);
CREATE INDEX IX3 ON 주문 (주문일시);
-- 옵티마이저는 대개 이 중 하나만 골라 쓰고 나머지 조건은 filter로 처리

-- 고친 예: 쿼리 패턴에 맞춘 결합 인덱스 하나로 통합
CREATE INDEX IX_주문_MEM_STAT_DT ON 주문 (회원번호, 주문상태, 주문일시);
-- INSERT마다 유지해야 할 인덱스도 3개에서 1개(+PK)로 줄어듦

결합 인덱스를 만들고도 겹치는 단일 컬럼 인덱스를 남겨 둔다

결합 인덱스를 새로 추가하면서, 그 선두 컬럼과 같은 단일 컬럼 인덱스를 지우지 않고 그대로 두는 경우다.

-- 틀린 예: 두 인덱스가 함께 존재
CREATE INDEX IX_주문_회원번호 ON 주문 (회원번호);
CREATE INDEX IX_주문_MEM_STAT_DT ON 주문 (회원번호, 주문상태, 주문일시);
-- IX_주문_회원번호는 IX_주문_MEM_STAT_DT의 선두 부분집합이라 사실상 중복

-- 고친 예: 부분집합 인덱스 제거
DROP INDEX IX_주문_회원번호;
-- 회원번호만으로 조회하는 쿼리도 IX_주문_MEM_STAT_DT로 처리 가능

한눈에 보기

결합 인덱스 컬럼 순서를 정하는 기준
조건 유형	인덱스 내 위치	이유
등치 조건(=)	선두에 배치	구간을 정확한 한 지점으로 좁혀 뒤 컬럼도 access로 쓰이게 함
범위 조건(BETWEEN, >=, LIKE 'AB%')	등치 조건 뒤, 가급적 마지막	뒤에 등치 조건이 오면 filter로 밀려나므로
ORDER BY 전용 컬럼	범위 조건과 같거나 그 다음	인덱스 정렬 순서로 SORT ORDER BY 생략
선두 컬럼의 부분집합 인덱스	제거 검토	결합 인덱스가 이미 커버, DML 부하만 늘림

연습 문제

도서 테이블에 WHERE 도서분류코드 = :1 AND 등록일 BETWEEN :2 AND :3 조건으로 조회하는 화면이 있다. 결합 인덱스 (도서분류코드, 등록일)과 (등록일, 도서분류코드) 중 어느 쪽이 나은지, Predicate Information이 어떻게 갈릴지 설명하라.
다음 Predicate Information을 보고 인덱스의 두 번째 컬럼이 무엇인지, 세 번째 컬럼이 왜 access가 아닌 filter로 표시됐는지 설명하라.
```
2 - access("A"=:1 AND "B">=:2 AND "B"<:3)
    filter("C"=:4)
```
주문 테이블에 (회원번호), (회원번호, 주문상태), (주문일시) 세 인덱스가 있다. 이 중 유지보수 부담만 늘리는 중복 인덱스를 고르고 이유를 설명하라.
초당 200건씩 INSERT가 몰리는 주문 테이블에 인덱스 5개를 3개로 통합했을 때, 조회 성능과 쓰기 성능 각각에 어떤 변화가 예상되는지 서술하라.

정답과 해설

(도서분류코드, 등록일)이 낫다. 도서분류코드=는 등치 조건이므로 선두에 두면 구간을 좁히고, 그 안에서 등록일 범위도 access 조건으로 동작한다. 반대로 (등록일, 도서분류코드)는 등록일 범위가 먼저 나와 도서분류코드=가 filter로 내려간다.
인덱스는 (A, B, C) 순서다. A는 access의 등치 조건이므로 선두, B는 access의 범위 조건이므로 두 번째다. C는 B라는 범위 조건 뒤에 있어 더 이상 정렬 구간을 좁히는 데 쓸 수 없으므로, 스캔한 행마다 값을 비교하는 filter로 처리된다.
(회원번호)가 중복이다. (회원번호, 주문상태)의 선두 컬럼이 회원번호 하나뿐인 조회도 그대로 커버하므로, 단일 컬럼 인덱스는 별도로 유지할 이유가 없다. 남겨 두면 INSERT/UPDATE/DELETE마다 갱신해야 할 인덱스만 하나 더 늘어난다.
조회 성능은 통합 전과 비슷하게 유지되거나, 조건에 맞는 결합 인덱스가 있다면 오히려 개선된다. 쓰기 성능은 행 하나가 갱신될 때 유지해야 할 B*Tree 쓰기가 줄어들므로 개선된다. 특히 INSERT가 몰리는 시간대에는 리프 블록 분할 빈도와 REDO 발생량이 함께 줄어드는 효과를 기대할 수 있다.