DB 인덱스란

• 조건을 만족하는 튜플을 빠르게 조회, 정렬, 그룹화 하기 위해 사용하는 기술

SQL 인덱스 생성

존재하는 테이블의 특정 속성에 대하여 인덱스 생성

CREATE INDEX idx_name ON table_name (column_name1, column_name2, ...); -- 속성이 여러개면 multi-column index
CREATE UNIQUE INDEX idx_name ON table_name (column_name1, column_name2, ...); -- 선택한 속성들 집합이 유니크한 경우

테이블 생성시 인덱스 생성

CREATE TABLE table_name (
    id INT PRIMARY KEY, -- 대부분의 RDBMS에서 기본키는 인덱스로 자동 생성 됨
    column_name1,
    column_name2,
    ...
    INDEX idx_name (column_name1, column_name2, ...),
    UNIQUE INDEX idx_name (column_name1, column_name2, ...)
);

SQL 생성한 인덱스 조회

SHOW INDEX FROM table_name;

• team_id_backnumber_idx라는 인덱스는 멀티컬럼 인덱스
• id는 자동적으로 PRIMARY라는 이름으로 인덱스 생성

B-Tree 기반 인덱스

• 선택한 컬럼에 관하여 인덱스 생성시 인덱스 테이블 생성
• 인덱스 테이블은 해당 컬럼을 기준으로 정렬된 형태
• 또한, 해당 값의 실제 테이블에서 어디 위치하는지 포인터를 가지고 있음
• 이진 탐색을 통해 빠르게 찾음

a컬럼 기준의 인덱스와 a에대한 조건을 통한 조회

a컬럼 기준의 인덱스와 a AND b에대한 조건을 통한 조회

• a가 7인 모든 튜플에 관해서 결국 full scan을 해야함

{a, b}컬럼 기준의 인덱스와 a AND b에대한 조건을 통한 조회

• a에 대해 우선 정렬 후 b에 대해 정렬

• a에 관하여 이진 탐색 이후 b에 대해 이진 탐색을 진행

{a, b}컬럼 기준의 인덱스와 b에대한 조건을 통한 조회

• 제대로 인덱스를 활용하지 못함

SQL에 따라 제대로 활용할 수 있는 인덱스인지 확인

• 인덱스 활용 여부는 쿼리 조건에 따라 달라짐
• 다음과 같은 인덱스를 생성 시

활용 가능 쿼리

SELECT * FROM player WHERE team_id = 1; -- {team_id, backnumber} 컬럼에 대한 인덱스 활용

SELECT * FROM player WHERE team_id = 1 AND backnumber = 10; -- {team_id, backnumber} 컬럼에 대한 인덱스 활용

활용 불가 쿼리

SELECT * FROM player WHERE backnumber = 10; -- backnumber 컬럼에 대한 인덱스 없음

SELECT * FROM player WHERE team_id = 1 OR backnumber = 10; -- team_id 조건은 활용가능하나, OR 이므로 backnumber 조건은 활용 불가

쿼리에 활용되는 인덱스 테이블이 어떤건지 확인

EXPLAIN -- 사용할 인덱스 테이블이 어떤건지 알려줌
SELECT * FROM player WHERE team_id = 1 AND backnumber = 10;

사용할 인덱스 테이블의 결정은 RDBMS의 optimizer가 적절하게 결정

명시적으로 사용할 인덱스 테이블 결정

• optimizer가 적절하게 결정하지 못할 경우 명시적으로 사용할 인덱스 테이블을 개발자가 결정
• USE INDEX : 사용할 인덱스 테이블 권장

SELECT * FROM player USE INDEX (index_name) WHERE team_id = 1; -- index_name 사용 권장

• FORCE INDEX : 사용할 인덱스 테이블 강제

SELECT * FROM player FORCE INDEX (index_name) WHERE team_id = 1; -- index_name 사용 강제

• IGNORE INDEX : 제외할 인덱스 테이블 명시

SELECT * FROM player IGNORE INDEX (index_name) WHERE team_id = 1; -- index_name 사용 제외

인덱스 단점

• table에 튜플이 추가되면 인덱스 테이블에도 추가되고 정렬되므로 오버헤드 발생(인덱스 테이블 크기가 클수록 정렬에 오버헤드 발생)
• 저장 공간 추가 필요

covering index

• 쿼리에 필요한 모든 컬럼이 인덱스에 포함된 경우 포인터 사용 없이 인덱스 테이블을 통해 쿼리 처리
• 인덱스 테이블을 통해 쿼리 처리하므로 더 빠름

Hash 기반 인덱스

• hash table을 사용하여 인덱스 테이블 생성
• equality 비교만 가능, range 비교 불가(이상, 이하 등 불가)
• multi-column 인덱스는 사용한 모든 attribute에 대한 조회에만 활용 가능

인덱스 참고 사항

• 인덱스 활용보다 full scan이 더 빠른 경우가 있음
  • 튜플이 적은 경우
  • 조회하려는 튜플이 테이블의 대부분을 차지하는 경우
  • full scan vs index scan 또한 RDBMS의 optimizer가 적절하게 결정
• foreign key는 MYSQL에서는 인덱스 자동 생성, RDBMS마다 다름
• order by, group by에도 활용 가능
• 이미 데이터가 많은 테이블의 인덱스 생성시 시간이 오래 걸리고 DB 성능에 안좋은 영향을 끼칠 수 있음