인덱스

인덱스(INDEX)는 열 값과 그 값이 든 행의 위치를 정렬해 따로 저장한 구조입니다. 책 뒤의 찾아보기처럼, 테이블을 처음부터 다 읽지 않고 원하는 행으로 바로 가게 합니다.

학생이 7명이면 이름이 이서준인 행을 찾으려고 7행을 다 읽어도 금방입니다. 학생이 수백만 명이면 이야기가 다릅니다. 인덱스가 없으면 DBMS는 테이블의 모든 행을 처음부터 끝까지 읽어야 합니다. 이것을 전체 테이블 스캔(full table scan)이라고 합니다. 인덱스가 있으면 정렬된 인덱스를 따라 몇 칸만 내려가 행의 위치를 찾습니다. DBMS는 인덱스를 흔히 B-트리(B-tree)라는 구조로 만듭니다. 트리는 맨 위 칸 하나에서 아래로 가지를 치며 갈라지는 구조입니다. 여기서는 '위 칸에서 값의 범위를 보고 한 갈래씩 골라 내려간다'만 알면 됩니다.

INSERT INTO 학생VALUES (108, '나은비', 1, 'EE');

SELECT * FROM 학생WHERE 이름 = '이서준';

1인덱스가 없을 때와 있을 때

그림은 같은 행을 찾는 두 길을 차례로 보입니다. 학생 표에서 이름이 이서준인 행을 찾습니다. 행은 넣은 순서대로 있어 이름이 정렬돼 있지 않습니다. 인덱스가 없으면 첫 행부터 차례로 읽습니다. 102에서 이서준을 찾아도 같은 이름이 더 있을 수 있어 끝까지 7행을 모두 읽습니다. 이름 열에 인덱스를 만들면, 이름과 그 행의 위치가 가나다순으로 정렬되어 표와 따로 저장됩니다. 맨 아래 칸(잎)에 이름과 행 위치가 있고, 위 칸에는 어느 잎으로 내려갈지 가르는 값이 있습니다. 이서준은 위 칸에서 '박서연 이상, 정하윤 앞' 범위라 가운데 잎으로 내려갑니다. 잎에서 이서준을 찾고, 적힌 행 위치를 따라 102 행으로 바로 갑니다. 세 칸만 읽었습니다.

2인덱스가 부담이 될 때

그림의 마지막 장면처럼, 행을 넣으면 표뿐 아니라 인덱스에도 가나다 순서에 맞는 자리를 찾아 끼워 넣어야 합니다.

• 넣기·고치기·지우기가 잦은 테이블: 행이 바뀔 때마다 인덱스도 함께 고쳐야 해서 느려집니다. 인덱스가 많을수록 더합니다.
• 저장 공간: 인덱스는 표와 따로 저장되므로 그만큼 공간을 더 씁니다.
• 값의 종류가 적은 열이나 작은 테이블: 학년(1~4)처럼 한 값에 행이 많이 걸리면 결국 많은 행을 읽어야 해서, 인덱스를 거치는 것보다 차례로 다 읽는 편이 나을 수 있습니다.

3클러스터드 인덱스와 넌클러스터드 인덱스

다음 두 사례를 보고, 어느 쪽이 테이블 하나에 하나만 만들 수 있는 인덱스인지 먼저 정해 보세요.

(가) 학생 행 자체를 학번 순서대로 늘어놓아 저장하고, 학번으로 행을 찾습니다.

(나) 행은 넣은 순서대로 두고, 이름과 행 위치만 가나다순으로 따로 적어 두었다가 이름으로 행을 찾습니다.

클러스터드 인덱스(clustered index)·넌클러스터드 인덱스(nonclustered index)는 SQL Server 같은 DBMS가 쓰는 이름입니다. 이 레슨에서 Oracle로 만드는 인덱스는 표와 따로 저장되고 행 위치를 가리키므로 넌클러스터드 쪽입니다.

예제

결과 표는 Oracle에서 돌린 결과입니다. 인덱스를 만들고 지우는 문은 SQL 표준에 정해져 있지 않아 DBMS마다 조금씩 다릅니다. 아래는 Oracle의 꼴입니다.

인덱스 만들기

CREATE INDEX 인덱스이름 ON 테이블 (열) 꼴로 만듭니다. 만든 인덱스는 USER_INDEXES로 확인합니다. USER_INDEXES는 내 인덱스의 목록을 보여 주는 Oracle의 시스템 카탈로그입니다(다음 레슨).

CREATE INDEX 학생_이름_IX
  ON 학생 (이름);
SELECT INDEX_NAME,
  UNIQUENESS
FROM USER_INDEXES
WHERE TABLE_NAME = '학생'
ORDER BY INDEX_NAME;

학생_PK는 만든 적이 없는데 있습니다. 학번에 기본키를 걸 때 DBMS가 저절로 만든 인덱스입니다. 기본키 값은 겹치면 안 되므로 고유(UNIQUE) 인덱스입니다. 학생_이름_IX는 같은 값이 여럿이어도 되는 인덱스(NONUNIQUE)입니다.

인덱스 지우기

DROP INDEX 인덱스이름으로 지웁니다. 지운 뒤에는 기본키 인덱스만 남습니다.

CREATE INDEX 학생_이름_IX
  ON 학생 (이름);
DROP INDEX 학생_이름_IX;
SELECT INDEX_NAME
FROM USER_INDEXES
WHERE TABLE_NAME = '학생';

MySQL처럼 DROP INDEX 인덱스이름 ON 테이블⁠로 테이블 이름까지 쓰는 DBMS도 있습니다. Oracle에서 이 꼴은 오류입니다.

고유 인덱스

고유 인덱스(UNIQUE INDEX)는 같은 값이 두 번 들어가지 못하게 막습니다. 지금 학생 이름은 모두 달라서 이름 열에 고유 인덱스를 만들 수 있습니다. 그 뒤 이미 있는 김민수를 한 번 더 넣으면 Oracle은 ORA-00001 오류를 냅니다.

CREATE UNIQUE INDEX 이름_UX
  ON 학생 (이름);
INSERT INTO 학생
  (학번, 이름, 학년)
VALUES (108, '김민수', 1);

시험 포인트

• 필기: 인덱스가 없으면 모든 행을 읽는 전체 테이블 스캔을 하고, 있으면 정렬된 B-트리를 따라 몇 칸만 읽어 행을 찾습니다. 기본키를 걸면 고유 인덱스가 저절로 생깁니다. 넣기·고치기·지우기가 잦은 테이블에서는 인덱스도 함께 고쳐야 해서 부담이 되고, 저장 공간도 더 씁니다. 값의 종류가 적은 열은 인덱스로 얻는 것이 적습니다. 클러스터드 인덱스는 행 자체를 키 순서로 저장해 테이블에 하나뿐이고, 넌클러스터드 인덱스는 따로 정렬해 행 위치를 가리키며 여럿 만들 수 있습니다.
• 실기: 인덱스를 만들고 지우는 문장을 예제 꼴대로 씁니다. 이름은 인덱스(Index), 고유 인덱스(Unique Index), 클러스터드 인덱스(Clustered Index), 넌클러스터드 인덱스(Nonclustered Index)로 씁니다.

확인 문제

1. 학생이 100만 명인 학생 표를 학번 순서대로 늘어놓아 저장하고 있습니다. 여기에 이름으로 찾는 인덱스와 학년으로 찾는 인덱스를 더 만듭니다. 이 표의 클러스터드 인덱스(clustered index) 개수와, 더 만드는 두 인덱스의 종류로 알맞은 것을 고르세요.

• ① 클러스터드 1개 · 더 만드는 둘도 클러스터드
• ② 클러스터드 1개 · 더 만드는 둘은 넌클러스터드
• ③ 클러스터드 3개 · 더 만드는 둘도 클러스터드
• ④ 클러스터드 3개 · 더 만드는 둘은 넌클러스터드

2. 다음 (가)~(라)에 맞는 이름을 한글 이름과 영문으로 함께 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.

• (가) 수강 표에서 점수로 행을 찾는 일이 잦아, 점수와 그 행의 위치를 점수 순으로 적은 것을 표와 따로 만들어 두었습니다.
• (나) 과목 표의 과목명으로 (가)를 만들면서, 같은 과목명이 두 번 들어오려 하면 오류로 막히게 함께 걸었습니다.
• (다) 100만 행인 주문 표의 행 자체를 주문번호 순서로 늘어놓아 저장했습니다. 한 표에 이렇게 할 수 있는 것은 하나뿐입니다.
• (라) 같은 주문 표에서 고객명으로도 찾으려고 하나를 더 만들었습니다. 행은 그대로 두고 고객명과 행 위치만 따로 정렬해 적어 둡니다.

3. 학과 표에서 학과명으로 학과를 자주 찾습니다. 학과명은 겹쳐도 됩니다. (1) 학과명에 인덱스 학과명_IX를 만드는 문장을 쓰고 (2) 그 뒤 USER_INDEXES에서 학과 표의 인덱스를 인덱스 이름 순서로 조회하면 몇 줄이 나오고 줄마다 UNIQUENESS가 무엇인지 쓰세요. (3) 만든 인덱스를 지우는 문장도 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.

CREATE INDEX 학과명_IX
  ON 학과 (학과명);
SELECT INDEX_NAME,
  UNIQUENESS
FROM USER_INDEXES
WHERE TABLE_NAME = '학과'
ORDER BY INDEX_NAME;

더 보기: 트리 구조는 3과목 20단원의 '트리' 레슨에서 자세히 배웁니다.