[Database] 6. 인덱스

1. 인덱스란?

1.1. 인덱스의 정의

- 데이터베이스에서 검색 속도를 향상시키기 위해 사용되는 자료구조

- 지정한 컬럼들을 기준으로 메모리 영역에 일종의 목차를 생성하는 것

- 주로 두 가지 구성 요소로 구성됨

(1) key

• 키는 실제 검색을 위한 기준이 되는 데이터의 일부 또는 전체를 포함함
• 인덱스를 만들 때 이 키에 해당하는 데이터의 값이 정렬되어 저장됨
• 키의 형태는 해당 인덱스의 종류와 데이터베이스 시스템에 따라 달라질 수 있음 예를 들어, B-tree 인덱스의 경우 키는 트리의 노드에 저장됨

(2) value

• 값은 인덱스의 키에 대응하는 실제 데이터의 위치나 데이터의 일부를 가리킴
• 주로 clustered 인덱스가 아닌 non-clustered 인덱스에서 사용됨
• 키가 데이터베이스 레코드의 위치를 가리킨다면, 값은 해당 레코드의 위치 정보 또는 식별자를 포함할 수 있음

 

- 일반적으로 인덱스는 두 번 탐색을 진행함

(1) 인덱스 검색: 인덱스를 사용해 검색 조건을 만족시키는 레코드를 찾음

(2) 인덱스를 통한 실제 데이터 조회: 찾은 레코드에 대한 실제 데이터를 조회하기 위해 인덱스를 통해 레코드의 위치를 찾음

 

1.2. 인덱스의 장단점

1.2.1. 장점

- 검색 조건에 해당하는 데이터를 빠르게 찾을 수 있어 응답 시간을 단축시킴

- 인덱스에 의해 데이터들이 정렬된 형태를 가지기에 WHERE문이나 ORDER BY문, MIN/MAX 같은 경우도 이미 정렬되어 있어 조건에 맞는 데이터를 빠르게 찾을 수 있음

 

1.2.2. 단점

- 추가 저장 공간 필요: 인덱스를 생성하면 추가적인 저장 공간이 필요하며 더 많은 디스크 공간이 사용될 수 있음

- 인덱스 업데이트 시간: 데이터가 삽입, 수정, 삭제될 때 인덱스도 업데이트 되어야 하므로 데이터 변경 작업의 시간이 더 많이 소요될 수 있음

-인덱스 관리 비용 증가: 인덱스를 관리하고 최적화하는 데 시간과 노력이 필요함

 

1.3. 인덱스를 사용하면 좋은 경우

- 데이터 검색이 빈번한 경우

- 삽입, 수정, 삭제 작업이 자주 발생하지 않는 컬럼

- 정렬 및 그룹화가 필요한 경우 (ORDER BY, GROUP BY)

- 조인 작업이 자주 수행되는 경우 (JOIN)

- 범위 검색이 필요한 경우 (WHERE)

- 유니크한 값이 필요한 경우: 아이디 같이 유니크한 값을 가지는 컬럼에 유니크 인덱스를 생성해 중복된 값의 삽입을 방지할 수 있음

 

2. 인덱스의 종류

인덱스 종류는 데이터 저장 방식에 따라 나눌 수도 있고, 어떤 자료구조를 사용하느냐에 따라 나눌 수도 있음

 

2.1. 저장 방식에 따른 분류

2.1.1. Clustered Index

- 데이터의 물리적인 순서로 인덱스를 구성하는 방식

- 테이블의 데이터와 인덱스가 일치하게 정렬되어 저장되므로, 클러스터 인덱스를 기반으로 검색 시 I/O 비용이 낮아짐

- 하나의 테이블에 하나의 클러스터 인덱스만 생성 가능함

- 주로 PK에 사용되어 유니크한 값을 가지게 됨

 

2.1.2. Non-Clustered Index

- 인덱스 키와 해당 레코드의 포인터로 구성되며, 데이터와 인덱스가 별도로 저장됨

- 인덱스는 테이블의 데이터와 무관하게 별도로 정렬되어 저장됨

- 하나의 테이블에 여러 넌클러스터 인덱스를 생성할 수 있음

- 주로 검색, 조인, 정렬에 사용됨

 

2.2. 자료구조에 따른 분류

2.2.1. B-Tree 인덱스

- B-트리 구조를 기반으로 인덱스를 생성함

- B-트리는 균형 트리 구조로, 검색, 삽입, 삭제 연산의 평균 시간 복잡도가 O(logn)임

- 범위 검색이나 정렬된 순서로 데이터에 접근하는데 효율적임

- 대부분의 RDBMS에서 기본적으로 사용하는 인덱스 구조

 

2.2.2. Hash 인덱스

- 해시 함수를 기반으로 인덱스를 생성함

- 해시 함수에 따라 고유한 위치에 데이터를 저장하고 검색하므로 시간 복잡도가 O(1)임

- 해시 충도링 발생할 수 있으며, 이를 해결하기 위해 chaining 등의 방법이 있음

 

O(1)인 Hash 인덱스보다 B-Tree 인덱스가 더 일반적인 이유는?

- B-트리는 노드 하나에 여러 개의 데이터를 저장할 수 있다는 특징

- 트리 포인터를 참조해서 계속 depth를 타고 들어가는 것보다 효율적임

- 또한 해시 테이블은 한 가지 키에 대한 탐색일 때는 효율적이나, 데이터가 정렬되어 있지 않아 부등호를 사용하지 못한다는 단점이 있음

 

3. 인덱스 만드는 법

3.1. MySQL

-- 단일 컬럼에 인덱스 생성
CREATE INDEX idx_username ON users (username);

-- 복합 인덱스 생성 (여러 컬럼을 결합한 인덱스)
CREATE INDEX idx_firstname_lastname ON users (first_name, last_name);

-- 인덱스를 사용하여 데이터 검색
SELECT * FROM users WHERE username = 'example_user';

 

3.2. MongoDB

-- 단일 컬럼에 인덱스 생성
db.users.createIndex({ name: 1 });

-- 복합 인덱스 생성 (여러 컬럼을 결합한 인덱스)
db.users.createIndex({ name: 1, age: -1 });

-- 인덱스를 사용하여 데이터 검색
db.users.find({ name: "Alice" }).explain();

{ name : 1 }은 "name" 필드에 오름차순으로 인덱스를 생성하라는 것

 

 

4. 인덱스 생성 시 고려사항

- 인덱스의 개수가 너무 많으면 추가 작업(삽입, 수정, 삭제 시 인덱스도 함께 변경)이 늘어나 성능 및 공간상 이슈가 생김

- 카디널리티(Cardinality)가 높은 컬럼에 지정하자, 즉 데이터 중복이 적은 컬럼

- 한번에 읽어야 하는 레코드 수가 적을 때 사용하자 (인덱스를 통해 읽어야 할 레코드가 전체 테이블의 20~25% 이상이면 직접 테이블을 읽는 것이 효율적임)

- 복합 인덱스를 구성할 때, 카디널리티가 높은 순에서 낮은 순으로 지정하자 (여러 개의 컬럼을 함께 키 값으로 지정한 경우, 첫번째 컬럼을 기준으로 정렬되고 두번째 컬럼이 정렬되기 때문에 처음에 중복이 적은 컬럼을 기준으로 한다면, 데이터의 대부분을 걸러낼 수 있음)

 

5. 커버링 인덱스

5.1. 커버링 인덱스란?

- 쿼리를 충족시키는데 필요한 모든 데이터를 갖고 있는 인덱스

 

1.1. 인덱스 정의에서 정리한 바와 같이, 일반적인 인덱스를 사용하면 MySQL 엔진이 실제 데이터를 조회하기 위해 (1) 인덱스 검색으로 검색 조건을 만족하는 레코드의 PK 찾기, (2) 찾은 PK에 대한 실제 레코드 조회하기 와 같이 두 번의 작업이 발생함

- 일반적으로 Non-Clustered Index에서 조건을 만족하는 레코드의 PK를 찾고, Clustered Index에서 다시 데이터를 구함

- Clustered Index만이 실제 테이블의 row 위치를 알고 있음

 

그러나 커버링 인덱스를 사용하는 경우, 인덱스에 이미 필요한 데이터가 전부 존재해 테이블에 접근할 필요 없이 한 번의 읽기로 결과를 얻을 수 있음

- Clustered Index까지 통하지 않고 Non-Clustered Index만으로도 데이터를 구할 수 있음

 

5.2. 커버링 인덱스 구현 방법

- 해당 쿼리에서 사용하는 필드를 모두 인덱스로 만들면 됨

 

"username"과 "email"에 대한 커버링 인덱스를 생성하고, "username"과 "email"을 조회하는 쿼리를 작성하는 경우

CREATE INDEX idx_covering_index ON users (username, email);

EXPLAIN SELECT username, email FROM users WHERE username = 'john_doe';

 

5.3. 커버링 인덱스의 장단점

5.3.1. 장점

- 성능 향상: 필요한 데이터를 인덱스만으로 얻을 수 있으 빠른 읽기 및 처리 속도를 제공함

- 자원 절약: 실제 데이터를 읽지 않아 자원을 효율적으로 사용할 수 있음

 

5.3.2. 단점

- 인덱스 크기 증가: 인덱스에 필요한 모든 컬럼을 포함하기에 인덱스 크기가 증가함

- 인덱스 업데이트 오버헤드: 인덱스에 대한 업데이트 시, 관련된 모든 인덱스를 업데이트 해야 함

 

참고자료

Difference between Clustered and Non-clustered index - GeeksforGeeks

MySQL Index 특징 및 유의사항 정리

[mysql] 인덱스 정리 및 팁

1. 커버링 인덱스 (기본 지식 / WHERE / GROUP BY)