Relational Database Index

인덱스

관계형 데이터베이스의 인덱스에 대해 알아보자. 인덱스는 다음과 같은 특징이 있다.

장점 : 빠른 검색 (해당 컬럼으로 미리 정렬되어 있음)
단점 : 검색 외의 작업(삽입/갱신/삭제)에 부하 발생, 디스크 공간 요구
종류 :
- 기본키 인덱스 : 기본키에 자동으로 설정됨
- 단일 컬럼 인덱스 : 컬럼 하나로 설정
- 다중 컬럼 인덱스 : 다수의 컬럼으로 설정
- 유니크 인덱스 : 중복되는 값이 없어야 함
- 커버링 인덱스 : 인덱스만 조회하여 원하는 값을 얻을 수 있는 경우

인덱스의 장점과 단점을 실험을 통해 직접 느껴보자. 우선 SQLite3에서 테이블과 레코드를 준비한다.

/* 테이블과 레코드 준비 */
CREATE TABLE test ( id INTEGER PRIMARY KEY AUTOINCREMENT, c1 TEXT, c2 TEXT ); -- 테이블

WITH RECURSIVE
  cnt(x, y) AS (
	 SELECT 1, 10000000
	 UNION ALL
     SELECT x + 1, 10000000 - (x + 1) FROM cnt
     LIMIT 10000000
  )
INSERT INTO test (c1, c2) SELECT x, y FROM cnt; -- 데이터 천만건 삽입

인덱스 유무에 따른 검색 성능을 비교해본다. 쿼리 플랜을 조회해보면 어떤 이유로 성능이 달라지는지 눈으로 확인할 수 있다.

/* 인덱스 유무에 따른 검색 성능 비교 */
SELECT * FROM test WHERE id = 7770000; -- 기본키(기본키 인덱스)로 검색 : 2ms 👈
EXPLAIN QUERY PLAN SELECT * FROM test WHERE id = 7770000; 
-- 쿼리 플랜 : SEARCH TABLE test USING INTEGER PRIMARY KEY (rowid=?)

SELECT * FROM test WHERE c1 = '7770000'; -- 인덱스가 없는 필드로 검색 : 6000ms 👈
EXPLAIN QUERY PLAN SELECT * FROM test WHERE c1 = '7770000'; 
-- 쿼리 플랜 : SCAN TABLE test 👈 (테이블 스캔)

CREATE INDEX myindex1 ON test (c1); -- c1 필드에 인덱스 생성
-- CREATE UNIQUE INDEX myindex1 ON test (c1); -- c1 필드에 유니크 인덱스 생성

SELECT * FROM test WHERE c1 = 7770000; -- 인덱스가 있는 필드로 검색 : 2ms 👈
EXPLAIN QUERY PLAN SELECT * FROM test WHERE c1 = 7770000; 
-- 쿼리 플랜 : SEARCH TABLE test USING COVERING INDEX myindex1 (c1=?)

인덱스의 종류와 수에 따른 검색 성능을 비교해본다.

/* 단일, 다중 컬럼 인덱스에 따른 검색 성능 비교 */
SELECT * FROM test WHERE c1 >= 7770000 and c2 = 2230000; -- 단일 컬럼 인덱스 : 1600ms 👈

CREATE INDEX myindex2 ON test (c1 ASC, c2 DESC);
SELECT * FROM test WHERE c1 >= 7770000 and c2 = 2230000; -- 다중 컬럼 인덱스 : 700ms 👈

CREATE INDEX myindex3 ON test (c2 DESC);
SELECT * FROM test WHERE c1 >= 7770000 and c2 = 2230000; -- 단일 컬럼 인덱스 2개 : 2ms 👈

인덱스 유무에 따른 삽입 성능을 비교해본다.

/* 인덱스 삽입 성능 비교 */
WITH RECURSIVE ... LIMIT 10000 ...
INSERT INTO test (c1) SELECT x FROM cnt; -- 인덱스가 있을 때 1만건 삽입 : 120ms 👈

DROP INDEX myindex1; -- 인덱스 삭제
...

WITH RECURSIVE ... LIMIT 10000 ...
INSERT INTO test (c1) SELECT x FROM cnt; -- 인덱스가 없을 때 1만건 삽입 : 12ms 👈

인덱스가 성능에 미치는 영향을 직접 확인해볼 수 있었다. 이번에는 임시테이블과 뷰에서 인덱스가 어떤 역할을 하는지도 확인해보도록 하자.

임시테이블

장점 : 인덱스를 생성할 수 있다(빠른 검색)
단점 : 원본 테이블의 수정 사항을 실시간 반영하지 않음

CREATE TEMPORARY TABLE t_test AS SELECT name FROM test WHERE id > 9000000; -- 일부 데이터
CREATE INDEX myindex ON t_test (name); -- 임시테이블에 인덱스 생성
EXPLAIN QUERY PLAN SELECT * FROM t_test WHERE name = '9000001';
-- 쿼리 플랜 확인 : SEARCH TABLE t_test USING COVERING INDEX myindex (name=?)
SELECT * FROM t_test WHERE name = '9000001'; -- 2ms 👈

뷰

장점 : 복잡한 쿼리를 뷰로 만들어 단순화할 수 있다
단점 : 인덱스를 생성할 수 없다(느린 검색)

CREATE VIEW v_test AS SELECT name FROM test WHERE id > 9000000;
EXPLAIN QUERY PLAN SELECT * FROM v_test WHERE name = '9000001';
-- 쿼리 플랜 : SEARCH TABLE test USING INTEGER PRIMARY KEY (rowid>?)
SELECT * FROM v_test WHERE name = '9000001'; -- 300ms

요약

인덱스는 검색 성능을 비약적으로 향상시켜줄 수 있으며, 적절한 인덱스 종류와 수를 사용해야 최상의 성능을 얻을 수 있다. 반면에, 인덱스를 사용할 경우 삽입과 같은 검색 외의 작업에는 부하가 발생하므로 언제 인덱스를 사용할 것인지 잘 판단하는 것이 좋겠다. 임시테이블은 인덱스를 생성할 수 있어 빠른 검색이 가능하지만 뷰는 인덱스를 생성할 수 없다.

인덱스

임시테이블

뷰

요약

참고