[DB] 데이터베이스 인덱스(Index)

인덱스(Index)

인덱스는 관계형 데이터베이스에서 추가적인 쓰기작업과 저장공간을 활용해 데이터베이스 테이블에 저장된 데이터의 검색 성능을 높이기 위한 자료구조를 말한다. 특정 컬럼에 인덱스를 설정하면 해당 컬럼의 데이터들을 정렬하여 별도의 메모리 공간에 물리적 주소와 함께 저장된다. 인덱스가 생성되고 해당 컬럼을 쿼리에서 조건으로 사용하게 되면, 옵티마이저에서 인지하여 생성된 인덱스를 사용하여 데이터베이스를 스캔한다. 즉, 데이터베이스 전체 데이터를 Full Sacn하지 않아 데이터를 효율적으로 조회할 수 있게 된다.

 

 

인덱스의 사용

인덱스 사용시 장점

• 검색 대상의 범위를 줄여 테이블을 조회하는 속도를 향상

 

인덱스 사용시 단점

• 인덱스 생성에 따른 인덱스 정보를 저장할 추가적인 저장공간이 필요, 대략 테이블 크기의 10%정도 (인덱스를 생성할 경우 해당 정보를 담은 MYI 파일이 생성)
• SELECT 이외의 작업이 자주 발생할 경우 성능 저하가 발생가능 (INSERT, DELETE, UPDATE 작업 시 인덱스에 대한 추가 작업이 필요)

 

인덱스를 사용하기 적합한 경우

• 삽입(INSERT), 삭제(DELETE), 수정(UPDATE) 작업이 자주 발생하지 않는 테이블일 경우
  • 인덱스의 추가, 삭제, 수정 작업으로 인해 성능저하 발생가능
• WHERE, ORDER BY, JOIN 절에 자주 사용되는 컬럼일 경우
  • 조건절이 없다면 인덱스가 사용되지 않음
• 카디날리티가 높은 컬럼의 경우
  • 데이터의 중복도가 낮은 컬럼
• 데이터의 규모가 작지 않은 테이블
  • 테이블의 규모가 적다면 전체 데이터를 스캔하는 Full Scan과 성능차이가 크지 않을 수 있다.

 

인덱스 사용 시 주의사항

• 잘 사용되지 않는 인덱스는 과감히 제거한다.
  • where 절에 사용되더라도 자주 사용되어야 가치가 있다.
  • 불필요한 인덱스로 성능저하가 발생할 수 있다.
• 데이터 중복도가 높은 칼럼은 인덱스 효과가 적다.
• 자주 사용되더라도 삽입(INSERT), 삭제(DELETE), 수정(UPDATE)이 자주 일어나는지 고려해야 한다.
  • 일반적인 웹서비스 같은 온라인 트랜잭션 환경에서 쓰기와 읽기 비율은 2:8 또는 1:9이다.
  • 조금 느린 쓰기를 감수하고 빠른 읽기를 선택하는 것도 방법이다.

 

 

인덱스의 자료구조

B-Tree

B-Tree는 자식을 2개만 갖는 이진트리를 확장하여 N개의 자식을 가질 수 있는 자료구조로 좌우 자식노드들의 균형을 맞춰 편향 이진트리처럼 비효율적인 모습을 갖는 이진트리의 단점을 보완한 트리이다. 항상 균형을 맞추기 때문에 균형트리라고 부르기도 한다. B-Tree의 최상단 노드는 루트노드 그리고 중간노드는 브랜치노드 최하위 노드를 리프노드라고 한다.

 

B+Tree

B+Tree는 B-Tree의 단점을 개선시킨 자료구조이다. B-Tree는 어느 한 데이터의 검색은 효율적이지만 모든 데이터를 조회해야하는 경우에는 트리의 모든 노드를 방문해야 하기 때문에 비효율적이라는 단점이 있다. B+Tree는 바로 이 단점을 보완한 자료구조이다. (MySQL 엔진인 InnoDB의 인덱스 자료구조는 B+Tree로 구현되어있다고 한다.)

 

 

Hash Table

해시 테이블은 데이터 요소의 주소/인덱스 값이 해시 함수에서 생성되는 데이터 구조이다. 인덱스 값이 데이터 값에 대한 키로 동작하기 때문에 매우 빠른 데이터 액세스가 가능하다. 즉, 해시 테이블은 키-값 쌍을 저장하지만 키는 해싱함수를 통해 생성된다. 따라서 키 값 자체가 데이터를 저장하는 배열의 인덱스가 되기 때문에 데이터 요소의 검색 및 삽입기능이 훨씬 빨라진다. 해시 테이블의 시간 복잡도는 O(1)이며 매우 빠른 검색을 지원하는데 반해 DB 인덱스에서 해시 테이블이 사용되는 경우는 제한적이다. 이유는 해시가 등호(=) 연산에만 특화되었기 때문이다. 해시 함수는 값이 조금이라도 달라지면 완전히 다른 해시 값을 생성하기 때문에 부등호 연산(<, >)이 자주 사용되는 데이터베이스 검색을 위해서는 적합하지 않다.

 

 

인덱스의 관리

DBMS는 인덱스를 최신의 정렬상태로 유지해야 원하는 값을 빠르게 탐색할 수 있다. 따라서 인덱스가 적용된 칼럼에 INSERT, UPDATE, DELETE가 수행된다면 추가 연산이 필요해 그에 따른 성능저하가 발생한다.

• 데이터 삽입(INSERT) : 새로운 데이터에 대한 인덱스 추가
• 데이터 삭제(DELETE) : 인덱스의 데이터를 삭제하지 않고 상태를 사용 안 함으로 변경한다.
• 데이터 수정(UPDATE) : DELETE (기존 값 사용 안 함으로 상태 변경) → INSERT (변경된 값 삽입)

 

 

인덱스의 종류

인덱스의 종류에는 테이블의 기본 키에 대해 적용되는 클러스터형 인덱스와 기본 키 외에 다른 칼럼에 적용하는 논-클러스터형 인덱스(세컨더리 인덱스)가 있다.

CREATE TABLE member (
	id int primary key               // -> 클러스터형 인덱스
	name varchar(255),
	email varchar(255) unique        // -> 논-클러스터형 인덱스
);

 

클러스터형 인덱스

테이블의 레코드를 지정된 칼럼에 대해 물리적으로 재배열한다. 클러스터형 인덱스는 테이블 당 한 개만 존재할 수 있고 primary key 제약조건을 지정하는 컬럼에 대해 자동으로 클러스터형 인덱스를 생성한다. 따라서, 우리가 일반적으로 테이블을 생성할 때 특정 컬럼에 primary key 제약조건을 지정했다면, 데이터가 자동으로 primary key를 기준으로 정렬되는 것이다. 클러스터형 인덱스를 생성한 컬럼을 기준으로 테이블의 데이터가 정렬되기 때문에 속도면에서 우수한 성능을 보이지만 데이터의 추가, 수정, 삭제 시 매번 데이터를 정렬해야 하기 때문에 성능이 저하된다.

 

 

클러스터형 인덱스 특징

• 데이터를 클러스터형 인덱스 기준으로 정렬한다.
• 테이블 당 1개만 설정 가능하다.
• 리프 페이지가 실제 데이터가 저장된 페이지이다. (별도의 인덱스 페이지를 생성하지 않는다.)
• 논-클러스터형 인덱스보다 속도면에서 우수한 성능을 보이지만 데이터 추가, 수정, 삭제 시 매번 정렬을 수행해야 하기 때문에 성능이 떨어진다.
• 한 테이블에 PRIMARY KEY와 UNIQUE NOT NULL이 한 테이블에 있을경우 PRIMARY KEY를 클러스터형 인덱스로 사용한다.
• 아래의 제약조건시 자동으로 생성된다.
  • PRIMARY KEY 제약조건 설정 (우선순위)
  • NOT NULL 제약조건과 UNIQUE 제약조건을 한 컬럼에 설정

 

클러스터형 인덱스 적용방법 2가지

// 방법1 (PRIMARY KEY 활용)
ALTER TABLE [테이블명] ADD CONSTRAINT [PK 제약조건명] PRIMARY KEY[PK 컬럼명];

// 방법2 (UNIQUE + NOT NULL)
ALTER TABLE [테이블명] MODIFY COLUMN [PK 컬럼명] int NOT NULL;
ALTER TABLE [테이블명] ADD CONSTRAINT [UNIQUE 제약조건명] UNIQUE [PK 컬럼명];

 

 

논-클러스터형 인덱스

물리적으로 레코드를 정렬하지 않은 상태로 데이터 페이지가 구성된다. 즉, 테이블의 레코드는 그대로 두고 지정된 칼럼에 대해 정렬된 인덱스를 만든다. 물리적으로 레코드를 정렬하지 않기 때문에 클러스터형 인덱스보다 속도면에서는 성능이 떨어지지만, 추가/수정/삭제의 성능은 더 뛰어나다. 논-클러스터형 인덱스는 unique 제약 조건을 설정한 컬럼에 대해 자동으로 생성된다. 따라서 테이블 당 여러 개 설정이 가능하다. 하지만 남용할 경우 성능이 저하되는 문제가 발생할 수 있다.

 

 

논-클러스터형 인덱스 특징

• 인덱스 생성 시 데이터 페이지는 그냥 둔 상태에서 별도의 인덱스 페이지를 따로 만들기 때문에 추가 용량이 필요하다.
• 정렬시 데이터는 정렬되지 않고 인덱스 페이지만 정렬된다.
• 물리적으로 테이블이 정렬되어 있지 않기 때문에 클러스터형 인덱스보다 속도면에서 성능은 떨어지지만 데이터의 입력, 수정, 삭제 성능은 더 뛰어나다.
• 클러스터형 인덱스와는 달리 리프 페이지에 데이터가 아닌 데이터의 위치를 나타내는 정보가 저장된다.
• 한 테이블에 여러 개(테이블 당 약 240개)의 논-클러스터형 인덱스를 생성할 수 있다.
• UNIQUE 제약조건 설정 시 자동으로 논-클러스터형 인덱스가 생성된다.

 

 

논-클러스터형 인덱스 적용방법 3가지

// 방법1 - UNIQUE 제약조건을 컬럼에 부여하여 인덱스를 생성하는 방법
ALTER TABLE [테이블명] ADD CONSTRAINT [UNIQUE 제약조건명] UNIQUE [컬럼명];

// 방법2 - UNIEQUE INDEX를 생성하여 인덱스를 부여하는 방법으로 중복을 허용하지 않으며 인덱스를 생성
CREATE UNIQUE INDEX [UNIQUE INDEX명] on [테이블명] [컬럼명];

// 방법3 - 방법 3은 DEFAULT INDEX를 생성하여 인덱스를 부여하는 방법으로 중복을 허용하는 인덱스를 생성
CREATE INDEX [DEFAULT INDEX명] on [테이블명] [컬럼명];

 

 

Reference

[Database] Clustered Index와 Non-clustered Index

[MYSQL] 📚 인덱스(index) 핵심 설계 & 사용 문법 💯 총정리

[DB] 인덱스(index)란? 인덱스 자료구조

[데이터베이스] B-Tree 인덱스를 보편적으로 사용하는 이유