효과적인 DB index 설정하기

망7H·2021년 5월 2일

인덱스(INDEX)란 검색 속도를 높이기 위한 색인 기술이다.
보통 인덱스는 일반적으로 SELECT 쿼리의 WHERE에 사용할 컬럼에 대해 효율적인 검색을 위해 사용하거나, 다른 테이블과의 JOIN에 사용된다.
(주로 효율적인 검색을 위해 사용된다.)

일반적으로 SQL 서버에 데이터를 저장할 때는 내부적으로 아무런 순서없이 저장한다.
이때, 데이터 저장영역은 Heap 이라고 한다.
Heap에서는 인덱스가 없는 테이블의 데이터를 찾을 때
전체 데이터 페이지의 처음 레코드부터 끝 페이지 마지막 레코드까지 모두 조회하게 된다.
이러한 검색 방식을 풀 스캔(Full Scan) 또는 테이블 스캔(Table Scan)이라고 한다.

이러한 검색의 속도 향상을 이유로 인덱스를 사용하게 된다.

0. Sample Data

1) Create Table

아래와 같은 학생(t_student) 테이블이 있다고 가정하겠습니다.

-- t_student 테이블 SCHEMA
CREATE TABLE t_student (
  seq_no INTEGER PRIMARY KEY, -- sequence
  id CHAR(14) NOT NULL, -- 주민번호
  name VARCHAR(255) NOT NULL, -- 학생 이름
  age INTEGER NOT NULL, -- 나이
  grade INTEGER NOT NULL, -- 학년
  ins_timestamp     TIMESTAMP      NOT NULL -- 가입 일시
)

2) Add Index

인덱스는 아래와 같이 하나 혹은 두개 시앙의 컬럼에 대해서 설정할 수 있습니다.

-- single column index for id
CREATE INDEX si_id ON t_student (id);
-- single column index for name
CREATE INDEX si_name ON t_student (name);
-- multi column index for id, name
CREATE INDEX mi_id_name ON t_student (id, name);
-- multi column index for id, name, age
CREATE INDEX mi_id_name_age ON t_student (id, name, age);
...

인덱스가 효율적인 검색에 도움이 된다고 하니,
위와 같이 모든 컬럼에 대해 인덱스를 설정하면 좋을 것 같지만
무조건 많이 인덱스를 설정한다고 해서 검색 속도를 향상시켜주지는 않습니다.

1. 어떤 컬럼에 Index를 설정해야 할까?

1) 핵심적인 기준 4가지

인덱스는 한 테이블당 보통 3~5개가 적당합니다.
(정규화 정도나 테이블의 목적에 따라서는 개수가 달라질 수도 있습니다.)
아래 4가지 기준을 사용하여 기준에 부합하는 컬럼을 인덱스로 설정하는 것이 좋습니다.

카디널리티 (Cardinality)
카디널리티가 높으면(↑) 인덱스 설정에 좋은 컬럼이다. (인덱스를 통해 불필요한 데이터의 대부분을 걸러낼 수 있음.)
카디널리티가 높다 = 한 컬럼이 갖고 있는 값의 중복도가 낮음. (= 값들이 대부분 다른 값을 가짐.)
카디널리티가 낮다 = 한 컬럼이 갖고 있는 값의 중복도가 높음. (= 값들이 거의 같은 값을 가짐 )
선택도 (Selectivity)
선택도가 낮으면(↓) 인덱스 설정에 좋은 컬럼이다. (일반적으로 5~10%가 적당함.)
선택도가 높다 = 한 컬럼이 갖고 있는 값 하나로 여러 row가 찾아진다.
선택도가 낮다 = 한 컬럼이 갖고 있는 값 하나로 적은 row가 찾아진다.

선택도 계산법 (= 컬럼의 특정 값의 row 수 / 테이블의 총 row 수 * 100)
ex) 10개의 데이터에서 고유한 학번(grade) 컬럼, 2명씩 같은 이름(name) 컬럼, 5명씩 같은 나이(age) 컬럼인 경우
① 학번(grade) 컬럼 선택도: 1 / 10 = 10%
② 이름(name) 컬럼 선택도: 2 / 10 = 20%
③ 나이(age) 컬럼 선택도: 5 / 10 = 50%
조회 활용도
조회 활용도가 높으면(↑) 인덱스 설정에 좋은 컬럼이다.
해당 컬럼이 실제 작업에서 얼마나 활용되는지에 대한 값.
(WHERE의 대상 컬럼으로 많이 활용되는지로 판단하면 된다.)
수정 빈도
수정 빈도가 낮으면(↓) 인덱스 설정에 좋은 컬럼이다.
인덱스도 테이블이기 때문에, 인덱스로 지정된 컬럼의 값이 바뀌게 되면 인덱스 테이블도 새롭게 갱신되어야 하기 때문.

2) 그 밖의 Index 명시 사항

WHERE에 자주 사용되는 컬럼에 사용하기.
LIKE와 사용할 경우에는 %가 뒤에 사용되도록 하기. (앞에 사용되면 Full Scan)
ORDER BY에 자주 사용되는 컬럼에 사용하기.
JOIN에 자주 사용되는 컬럼에 사용하기.
데이터의 변경이 잦은 컬럼에는 인덱스를 사용하지 않기.

2. Index를 무조건 많이 설정하면 안되는 이유가 무엇일까?

'위의 (1) 어떤 컬럼에 인덱스를 설정해야 할까?'를 보았다면 인덱스를 많이 설정하면 안되는 이유를 어렴풋하게 이해할 수도 있습니다.

① 인덱스 설정 시, 데이터베이스에 할당된 메모리를 사용하여 테이블 형태로 저장되게 됩니다.
(즉, 인덱스가 많아지면 데이터베이스의 메모리를 많이 잡아먹게 됩니다.)
② 인덱스로 지정된 컬럼의 값이 바뀌게 되면 인덱스 테이블이 갱신되어야 하므로 느려질 수 있습니다.

위와 같은 이유로 인덱스를 계속해서 만드는 것이 하나의 쿼리문을 빠르게 만들 수는 있지만, 전체적인 데이터베이스의 성능 부하를 초래합니다.

3. 설정된 Index가 DML(Data Manipulation Language)에 미치는 영향

Index는 주로 SELECT 쿼리에서는 성능이 잘 나온다.
하지만 INSERT/UPDATE/DELETE에서는 경우에 따라 다르다.

UPDATE, DELETE
인덱스로 설정된 컬럼에 대해 조건(WHERE)을 사용할 수도 있는 UPDATE, DELETE사용 시 조회에서는 성능이 크게 저하되지 않는다.
※ 여기서 수정/삭제할 데이터를 찾는 때의 속도가 빠르다는 것이지 수정이나 삭제 그 자체를 빠르게 처리한다는 뜻은 아니다.
INSERT
반면, INSERT의 경우에는 효율이 좋지 않다.
새로운 데이터를 추가하면서 인덱스가 설정되어 있던 컬럼의 테이블이 수정되어야 하기 때문이다.

4. Single Column Index와 Multi Column Index의 비교

Multi Column Index의 장점
① 질의(SQL) 컬럼이 모두 조합 인덱스에 있는 경우, 물리적인 데이터 블록을 읽을 필요가 없다.
(인덱스 테이블만 읽으면 된다.)
Multi Column Index를 고려해야 하는 경우
① WHERE에 사용될 때 AND 연산자에 의해 자주 같이 질의되는 컬럼들인 경우.

5. Index 생성으로 발생되는 특징 요약

검색 (SELECT) 속도 향상
인덱스 테이블을 위한 추가 공간과 시간 필요.
INSERT, UPDATE, DELETE가 경우에 따라 성능 하락 발생.

6. Clustered Index와 Non Clustered Index

1) Clustered Index

테이블 당 하나의 Clustered Index만 생성이 가능하다. (일반적으로 PK 컬럼으로 자동 생성됨.)
맨 위의 '0. Sample Data'에서 생성한 테이블을 기준으로 'seq_no' 컬럼은 PRIMARY KEY로 지정하였기 때문에, 자동으로 Clustered Index가 된다.
만약, Clustered Index가 아닌 Non Clustered Index로 설정하고 싶다면 아래와 같이 스키마를 수정할 수 있다.

CREATE TABLE t_student (
  seq_no INTEGER PRIMARY KEY NONCLUSTERED, -- sequence (non clustered index)
  ...
)

물리적으로 데이터를 정리하므로 Clustered Index의 Index 테이블은 하나만 존재한다.

데이터 입력 시, 물리적 정렬로 DB에 Clustered Index를 기준으로 입력이 되므로
Heap에 있는 데이터를 꺼내었을 때, 모든 페이지의 데이터가 Clustered Index를 기준으로 정렬이 되어 있는 것을 확인할 수 있다.
물리적으로 정렬되어 있기 때문에 순차적 데이터를 접근할 때 가장 빠른 처리를 한다.

2) Non Clustered Index

테이블당 여러 개를 생성할 수 있다.
데이터 페이지가 물리적으로 정렬되어 있지 않기 때문에 인덱스에 의해 찾아가야 한다.

※ Non Clustered Index에 대해 더 궁금하다면? 이 포스팅을 참고하시면 됩니다.

7. Index와 SlowQuery

이전에 작성했던 슬로우 쿼리에 대처했던 경험에 대한 포스팅이 있습니다.
실행계획을 분석하며 여러 Scan 과정에서 Index가 어떻게 사용되었는지 확인할 수 있어서 추가하여 보았습니다. Postgresql 슬로우 쿼리에 대처하기를 참고해주세요.

서버 개발을 하다보면, 직접 테이블을 생성하고 관련 설정(인덱스)들을 해야 할 때가 있습니다.
데이터베이스에 대해 전문적 지식을 가지고 효율을 생각해서 만들 수 있으면 좋겠지만,
각 분야의 전문적 지식을 가지진 못하더라도
작업 시점에 가지고 있는 지식을 사용한 최선의 선택으로 데이터베이스에 대한 작업을 하는 편입니다.