DB·SQL 실무 가이드 · 인덱스가 동작하는 원리

이 파트에서 다루는 내용

인덱스 구조랜덤 액세스 비용복합 인덱스컬럼 순서NULL과 인덱스인덱스의 대가

인덱스는 정렬된 색인입니다

인덱스는 책 뒤의 찾아보기와 같습니다. 키워드가 정렬돼 있고 각 항목이 본문 페이지 번호를 가리킵니다. 전체를 읽지 않고 원하는 곳으로 바로 갈 수 있게 해 줍니다.

Oracle의 기본 인덱스는 B-Tree입니다. 루트에서 시작해 브랜치를 거쳐 리프에 도달하는 트리 구조이고, 리프에는 인덱스 키 값과 실제 행의 물리적 위치인 ROWID가 들어 있습니다.

중요한 것은 리프가 정렬돼 있고 서로 연결돼 있다는 점입니다. 그래서 등호 조회뿐 아니라 범위 조회와 정렬에도 인덱스를 쓸 수 있습니다.

INDEX UNIQUE SCAN

1건

유일한 값을 찾아 한 건만 읽습니다. 기본키 조회가 여기 해당하고 가장 빠릅니다.

INDEX RANGE SCAN

범위

시작 지점을 찾은 뒤 리프를 옆으로 훑습니다. 범위 조건과 비유일 인덱스 조회의 기본 형태입니다.

INDEX FULL SCAN

인덱스 전체

인덱스 전체를 순서대로 읽습니다. 테이블 전체보다 크기가 작고 이미 정렬돼 있어 유리할 때가 있습니다.

TABLE ACCESS FULL

테이블 전체

인덱스를 안 쓰고 테이블을 통째로 읽습니다. 많은 행을 읽어야 할 때는 이게 더 빠릅니다.

인덱스를 탄다고 항상 빠른 것은 아닙니다

인덱스로 ROWID를 찾은 뒤에는 그 위치의 테이블 블록을 읽어야 합니다. 이걸 랜덤 액세스라고 하고, 여기에 비용이 붙습니다.

읽어야 할 행이 많으면 이 왕복이 계속 쌓입니다. 어느 지점을 넘으면 차라리 테이블을 순차적으로 통째로 읽는 편이 빠릅니다. 옵티마이저가 전체 스캔을 고르는 것은 대개 이 판단입니다.

그래서 '인덱스를 안 탄다'가 곧 문제인 것은 아닙니다. 전체의 절반을 읽는 쿼리에 인덱스를 강제로 태우면 오히려 느려집니다.

클러스터링 팩터

핵심 지표

인덱스 순서와 테이블에 저장된 물리적 순서가 얼마나 비슷한지를 나타냅니다. 비슷할수록 같은 블록을 연달아 읽어 효율적입니다.

좋은 경우

예시

주문일자처럼 시간 순으로 쌓이는 컬럼은 인덱스 순서와 저장 순서가 비슷해 범위 조회가 효율적입니다.

나쁜 경우

예시

상태코드처럼 값이 흩어져 저장되는 컬럼은 인덱스로 찾아도 매번 다른 블록으로 가야 해서 이득이 적습니다.

선택도

판단 기준

조건에 맞는 행의 비율입니다. 비율이 낮을수록(적게 걸러질수록) 인덱스가 유리합니다. 상태코드가 두세 종류뿐이면 인덱스 효과가 작습니다.

복합 인덱스는 선두 컬럼이 없으면 못 씁니다

여러 컬럼을 묶은 복합 인덱스는 사전과 같은 방식으로 정렬됩니다. 첫 번째 컬럼으로 먼저 정렬하고, 같으면 두 번째 컬럼으로 정렬합니다.

전화번호부에서 성을 모르고 이름만으로 찾을 수 없는 것과 같습니다. 복합 인덱스도 선두 컬럼 조건이 없으면 효율적으로 접근하지 못합니다.

예외가 하나 있습니다. Oracle에는 인덱스 스킵 스캔이 있어서 선두 컬럼의 값 종류가 아주 적을 때는 각 값마다 훑는 방식으로 인덱스를 쓸 수 있습니다. 다만 정상적인 접근보다 느리므로 설계 근거로 삼지는 않습니다.

선두 컬럼 원칙 확인sql

CREATE INDEX idx_orders_01 ON orders (customer_id, order_dt, status_cd);

-- 인덱스를 효율적으로 씁니다
SELECT * FROM orders WHERE customer_id = 100;
SELECT * FROM orders WHERE customer_id = 100 AND order_dt >= SYSDATE - 30;
SELECT * FROM orders WHERE customer_id = 100 AND order_dt >= SYSDATE - 30 AND status_cd = 'DONE';

-- 선두 컬럼이 없어 이 인덱스로는 효율이 떨어집니다
SELECT * FROM orders WHERE order_dt >= SYSDATE - 30;
SELECT * FROM orders WHERE status_cd = 'DONE';

조건의 작성 순서는 상관없습니다. WHERE 절에 status_cd 를 먼저 쓰든 나중에 쓰든 옵티마이저가 알아서 판단합니다. 중요한 것은 인덱스의 컬럼 순서입니다.

복합 인덱스의 컬럼 순서를 정하는 기준

등호 조건으로 쓰이는 컬럼을 앞에, 범위 조건 컬럼을 뒤에 둡니다. 범위 조건이 앞에 오면 그 뒤 컬럼은 걸러내는 역할을 제대로 못 합니다.
여러 쿼리에서 공통으로 쓰이는 컬럼을 앞에 둡니다. 인덱스 하나로 더 많은 쿼리를 커버할 수 있습니다.
값의 종류가 많은 컬럼이 앞에 오면 한 번에 더 많이 걸러집니다. 다만 등호와 범위 구분이 우선입니다.
정렬에 쓰이는 컬럼을 인덱스 순서와 맞추면 정렬 작업 자체를 건너뛸 수 있습니다.
SELECT하는 컬럼까지 인덱스에 다 있으면 테이블에 갈 필요가 없어집니다. 이걸 커버링 인덱스라고 하고 실행계획에 테이블 접근 단계가 사라집니다.

등호와 범위의 순서 차이sql

-- 자주 쓰는 쿼리
SELECT * FROM orders
WHERE  status_cd = 'DONE'
AND    order_dt >= SYSDATE - 30;

-- 좋음: 등호 조건이 앞
CREATE INDEX idx_orders_good ON orders (status_cd, order_dt);

-- 나쁨: 범위 조건이 앞이면 status_cd 로 걸러내지 못하고
-- 30일치 전체를 훑으면서 하나씩 확인합니다
CREATE INDEX idx_orders_bad ON orders (order_dt, status_cd);

Oracle 인덱스는 NULL을 저장하지 않습니다

단일 컬럼 인덱스에서 값이 NULL인 행은 인덱스에 들어가지 않습니다. 이건 Oracle의 고유한 동작이고 두 가지 결과를 낳습니다.

첫째, `WHERE col IS NULL` 조회는 그 컬럼의 단일 인덱스를 쓸 수 없습니다. 인덱스에 없는 것을 찾을 수 없기 때문입니다.

둘째, NULL이 많은 컬럼의 인덱스는 크기가 작습니다. 상태가 있는 소수의 행만 인덱스에 들어가므로 오히려 효율적일 수 있습니다.

IS NULL 조회를 인덱스로 처리하기sql

-- 이 조회는 단일 컬럼 인덱스를 못 씁니다
SELECT * FROM orders WHERE process_dt IS NULL;

-- 방법 1: 상수를 함께 넣은 복합 인덱스 (NULL도 저장됨)
CREATE INDEX idx_orders_proc ON orders (process_dt, 0);

-- 방법 2: 함수 기반 인덱스로 미처리 건만 인덱싱
CREATE INDEX idx_orders_pending
  ON orders (CASE WHEN process_dt IS NULL THEN 'Y' END);

SELECT * FROM orders
WHERE  CASE WHEN process_dt IS NULL THEN 'Y' END = 'Y';

복합 인덱스는 모든 컬럼이 NULL일 때만 저장을 생략합니다. 그래서 상수를 하나 붙이면 NULL 행도 인덱스에 들어갑니다. 방법 2는 미처리 건이 전체의 극히 일부일 때 인덱스가 아주 작아져 유리합니다.

인덱스에는 대가가 있습니다

조회가 빨라지는 만큼 입력, 수정, 삭제는 느려집니다. 테이블 한 건을 바꾸면 그 테이블의 모든 관련 인덱스를 함께 갱신해야 하기 때문입니다.

인덱스가 10개인 테이블에 INSERT 하면 실제로는 11번의 쓰기가 일어납니다. 대량 배치가 유난히 느리다면 인덱스 개수를 확인해 볼 만합니다.

중복 인덱스

흔한 낭비

(A), (A, B) 두 인덱스가 있으면 앞의 것은 대개 불필요합니다. (A, B)로 A 조건도 처리되기 때문입니다.

안 쓰는 인덱스

정리 대상

과거에 만들었지만 지금은 아무 쿼리도 안 쓰는 인덱스가 쌓입니다. 사용 여부를 모니터링해 정리합니다.

배치 전 비활성화

대량 처리

대량 적재 시 인덱스를 UNUSABLE로 두고 적재 후 재생성하는 방법이 있습니다. 다만 재생성 시간과 그동안의 조회 영향을 함께 계산해야 합니다.

운영 중 생성 주의

락

인덱스 생성은 테이블에 락을 겁니다. 운영 중이라면 ONLINE 옵션을 검토하고, 그래도 부하가 있으므로 작업 시간을 잡습니다.