[Database] Database Index - 기본 개념

Indexing?

데이블에서 데이터를 빠르게 조회할 수 있게 조회 성능을 향상시키는 구조를 의미한다.

Index 라는 용어에서도 알 수 있듯이 책의 목차처럼 내용을 찾기 쉽게 구조를 갖추는 것이라 이해할 수 있다.

인덱스의 작동 방식

데이터베이스 테이블의 특정 컬럼의 값과 해당 데이터의 포인터를 포함하는 별도의 테이블을 생성한다.

기존 테이블을 조회하기 쉽게 정렬하거나, 자료 구조를 활용한 테이블 구조를 매핑 하는 방식 등으로 생성된다.

자료 구조는 일반적으로 B-트리, B+트리, 해시 테이블 등을 적용한다.

Clustered, Non-Clustered Index

https://josipmisko.com/posts/clustered-vs-non-clustered-index

인덱스의 작동 방식에서 설명한 것 처럼 값과 포인터를 포함하는 테이블을 생성한다 했는데,

이때, 인덱스가 데이터와 함께 저장되느냐(Clustered), 별도로 포인터만 저장하느냐에(Non-Clustered) 따라 종류가 달라진다.

Clustered Index

데이터의 물리적 순서를 인덱스의 순서와 일치하도록 배열한 인덱스이다.

즉, 데이터가 인덱스에 따라 정렬된 상태로 저장된다는 것을 의미한다.

특징

• 하나의 테이블에 하나의 클러스터드 인덱스만 존재할 수 있다.
• 이미 정렬된 상태이기에 여러 개의 물리적 정렬은 불가능하다.
• 클러스터드 인덱스로 설정된 컬럼을 기준으로 조회하면 성능이 우수하다.
• Primary key는 기본적으로 클러스터드 인덱스로 설정되는 경우가 많다.

클러스터드 인덱스는 데이터가 자주 정렬되어 조회되거나, 자주 사용 되는 기본 키에 사용하기 적합하다.

Non-Clustered Index

데이터가 물리적으로 정렬되지 않고 별도의 인덱스 테이블에 인덱스 정보와 데이터의 포인터만 저장하는 방식이다.

즉, 데이터 자체는 원래 테이블에 저장되고, 인덱스만 별도로 관리된다.

특징

• 하나의 테이블에 여러 개의 넌클러스터드 인덱스를 생성할 수 있다.
• 인덱스는 데이터와 독립적으로 저장된다.
• 포인터를 사용하여 실제 데이터를 조회하기에 검색 속도가 클러스터드 인덱스보다는 다소 느릴 수 있다.

논클러스터드 인덱스는 특정 열에 대해 종종 조회가 이루어지지만, 데이터가 자주 변결되거나 정렬될 필요가 없는 경우 유리하다.

저장 공간

인덱싱이라는건 결국 별도의 인덱스 테이블을 생성하는 것으로 추가적인 메모리를 요구하게 된다.

하지만 밑에서 기술할 단점에 “추가적인 메모리 요구” 와 같은 단점을 기술할 것인데, 이는 클러스터드, 논클러스터드 인덱스의 전략에 따라 조금 차이가 있다는 것을 의미한다.

인덱스 자료 구조의 사용 방식과 물리적 데이터 정렬 여부에 따라 저장 공간의 활용 방식이 조금 다르게 작용한다.

클러스터드

데이터 자체가 인덱스 컬럼에 순서를 가지고 물리적으로 저장되므로, 데이터의 위치를 저장할 별도 포인터는 필요하지 않다.

논클러스터드

물리적 데이터와 별도로 저장 되며, 인덱스 테이블에 데이터를 가리키는 포인터가 포함된다.

즉, 인덱스 컬럼의 값과 포인터가 저장된 별도의 인덱스 테이블이 필요하다.

정리하자면 클러스터드 인덱스는 추가 저장 공간의 필요성이 적을 수는 있지만, 여전히 정렬과 유지에 따른 비용이 따른다.

인덱싱 컬럼의 종류

인덱스를 적용하는 기준은 컬럼의 필드 값을 기준으로 적용을 한다.

어떻게 컬럼을 인덱스로 활용하는지에 따라 종류가 나눠진다.

기본 인덱스, Primary Index

https://codingexplained.com/databases/concepts/primary-index

기본 키 즉, 데이블에서 각 행을 유일하게 식별하는데 사용하는 컬럼과 연결된 인덱스를 의미한다.

특징

유일하게 식별되는 기본 키 인덱스인 만큼

• Unique : 모든 행에서 중복되지 않는 주민등록번호, ID 등과 유일한 값을 지켜야한다.
• NOTNULL : 항상 행을 식별하기 위해서는 키 값이 NULL 이면 안된다.
• Immutability : 식별자로 사용되는 값이기에 값이 변가게 되면 참조 무결성(Referential Integrity)를 유지하기 어렵고 성능 저하를 야기할 수 있다.
• Composite Key : 하나의 열로 구분짓기 까다롭다면 식별 값을 여러 개 조합하여 기본 키로 활용할 수 있다.

등을 준수하며 선정해야한다.

기본 인덱스는 보통 클러스터드 인덱스로 동작한다.

동작 방식

데이터 정렬 및 저장

기본 인덱스가 클러스터드로 설정된 경우, 데이터베이스는 기본 키 값의 순서에 따라 데이터를 정렬하여 테이블에 저장한다.

클러스터드 인덱스 사용

기본 인덱스가 클러스터드로 설정되면, 실제 데이터가 인덱스에 따라 정렬되고 저장된다.

클러스터드 인덱스는 테이블의 데이터가 정렬된 상태로 유지되며, 이 정렬된 데이터 자체가 인덱스 역할을 하므로 별도의 인덱스 테이블 없이 데이터를 검색할 수 있다.

데이터의 물리적 순서

기본 인덱스를 설정하면, 기본 키 값을 기준으로 데이터의 물리적 순서가 결정되며, 이는 테이블에 데이터가 삽입되거나 삭제될 때도 유지된다.

검색 프로세스

1. 인덱스 탐색 기본 키를 사용한 검색 요청이 들어오면, 데이터베이스는 기본적으로 B-트리 또는 B+와 같은 자료 구조를 사용하여 기본 인덱스를 탐색한다.

2. 데이터 직접 접근 데이터가 저장된 순서와 일치하므로, 검색 과정에서 별도의 포인터를 사용하지 않고도 데이터를 직접 접근할 수 있다. 이는 보조 인덱스와 달리 데이터의 물리적 위치와 인덱스 값이 동일하게 정렬되어 있기 때문에 가능한 구조다.

3. 빠른 범위 검색 특정 범위의 데이터를 검색할 때도 성능이 매우 효율적이다. 예를 들어, 특정 날짜 범위 내에 있는 모든 주문 기록을 찾는 경우, 클러스터드 인덱스를 사용하면 해당 범위를 빠르게 순차적으로 탐색할 수 있다.

4. 효율적인 저장 공간 사용 추가적인 인덱스 테이블이 필요하지 않기에 저장 공간 측면에서도 효율적이며, 인덱스를 관리하는 데 드는 부하를 줄일 수 있다.

장점

• 각 행을 고유하게 식별하므로, 기본 키를 기준으로 하는 검색 성능이 향상된다.
• 물리적으로 정렬된 상태이기에 범위 검색 시에도 성능이 향상된다.
• 데이터의 물리적 순서와 일치하므로, 기본 키를 통한 데이터 접근 시 별도의 인덱스 탐색 후 포인터를 따라가는 과정이 필요 없다.

단점

• 기본 키 값이 변경될 경우나 데이터의 삽입 등 클러스터드 인덱스는 데이터의 물리적 위치를 재정렬하면서 업데이트 작업의 부담을 증가시켜 성능 저하의 원인이 될 수 있다.
• 하나의 테이블에 하나만 지정할 수 있다.

사용 예시

주민등록번호, 사원 ID, 제품 코드 등 각 행을 유일하게 식별하는 컬럼에 기본 인덱스를 설정한다.

보조 인덱스, Secondary Index

https://www2.cs.sfu.ca/CourseCentral/354/zaiane/material/notes/Chapter11/node8.html

기본 키가 아닌 다른 컬럼에 적용되는 인덱스를 의미한다.

기본 키 값이 아닌 특정 열을 기준으로 조회 및 정렬, 필터링을 자주하게 될 때 해당 컬럼을 인덱싱하여 성능을 향상 시킬 수 있다.

특징

• 다중 인덱스 : 하나의 테이블에 여러 개의 보조 인덱스를 설정할 수 있다.
• 기본 키와 독립적 : 기본 키에 의존하지 않는다.
• 데이터와 인덱스 분리 : 데이터는 기존 테이블에 저장되고, 보조 인덱스는 별도의 인덱스 테이블에 값과 해당 데이터 행에 대한 포인터만을 저장한다.

보조 인덱스는 주로 논클러스터드 인덱스로 동작한다.

동작 방식

데이터와 인덱스 분리

보조 인덱스는 테이블에 저장된 데이터와 별도로, 보조 인덱스 테이블에 인덱싱 대상 컬럼 값과 해당 데이터 행의 위치 정보를 저장한다. 이 구조로 인해 기본 테이블과 독립적으로 인덱스를 관리할 수 있다.

논클러스터드 인덱스 방식

보조 인덱스는 주로 논클러스터드 인덱스(non-clustered index)로 설정된. 이는 데이터가 정렬된 상태로 저장되지 않으며, 인덱스 테이블에 저장된 포인터를 사용하여 실제 데이터를 찾는 방식으로 작동한다.

검색 프로세스

1. 인덱스 탐색 특정 보조 인덱스를 사용한 검색 요청이 들어오면, 데이터베이스는 해당 인덱스를 먼저 탐색한다. 보조 인덱스는 인덱스 대상 컬럼의 값을 기준으로 정렬되어 있으며, B-트리 또는 B+ 트리와 같은 자료 구조를 통해 탐색이 이루어진다.

2. 포인터로 데이터 찾기 인덱스 탐색을 통해 해당 데이터가 있는 위치를 찾은 후, 실제 테이블에서 해당 위치로 이동하여 데이터를 가져온다. 보조 인덱스는 실제 데이터의 물리적 저장 순서와는 독립적이기 때문에 인덱스 테이블에 저장된 포인터를 통해 원하는 데이터를 찾는다.

3. 추가적인 I/O 발생 보조 인덱스를 통해 데이터를 검색할 때는 인덱스 테이블을 탐색한 후 포인터를 사용하여 실제 데이터를 접근해야 하므로, 기본 인덱스에 비해 추가적인 디스크 I/O가 발생할 수 있다. 이는 검색 성능에 약간의 영향을 줄 수 있지만, 조회 조건에 따라서는 성능 향상을 기대할 수 있다.

4. 인덱스 유지 비용 데이터가 삽입, 수정, 삭제될 때 보조 인덱스도 함께 갱신되어야 한다. 이는 인덱스를 유지하는 데 드는 부하를 증가시키며, 데이터 변경 작업의 성능에 영향을 줄 수 있다.

장점

• 기본 키 외에대 자주 조회되는 열에 대해 성능을 향상시킬 수 있다.
• 여러 개의 보조 인덱스를 생성할 수 있으므로, 참조되는 필드가 다양해도 성능을 향상 시킬 수 있다.

단점

• 데이터가 추가되거나 삭제될 때마다 보조 인덱스도 함께 갱신되어야 하므로, 쓰기 성능이 떨어질 수 있다.
• 보조 인덱스를 저장하는 추가 저장 공간을 차지한다.
• 포인터로 데이터를 참조하므로, 기본 키를 사용하는 검색보다 다소 느릴 수 있다.

사용 예시

사용자 정보 테이블에서 email, last_name 열에 대해 보조 인덱스를 생성하면, 이메일이나 이름을 기준으로 한 검색 속도가 향상된다.

고유 인덱스, Unique Index

https://goodgid.github.io/Tech-Database-Indexing-Strategies-2/

중복이 허용되지 않는 컬럼에 설정되는 인덱스다.

기본 키와 다른 점은 NULL 값이 허용되며 데이터의 유일성을 유지하는 데 사용된다.

특징

• 중복 불가 : 모든 컬럼은 고유한 값을 가져야한다.
• NULL 허용 : 기본 키 인덱스와 다르게 NULL 값은 허용된다.
• 다중 설정 가능: 하나의 테이블에 여러 개의 고유 인덱스를 설정할 수 있다.
• 유일성 제약 : 고유한 값이 여러 컬럼에 걸쳐 있을 때 무결성을 유지하는데 주로 사용한다.

동작 방식

데이터 유일성 보장

고유 인덱스는 컬럼 값이 중복되지 않도록 보장하며, 중복된 데이터가 삽입되면 데이터베이스는 오류를 반환하여 중복 삽입을 방지한다.

인덱스 생성 시 유일성 유지

고유 인덱스를 생성하면, 인덱스 대상 컬럼에 대해 데이터의 유일성이 강제된다. 삽입, 수정 작업 시 데이터베이스는 해당 컬럼의 기존 값들과 비교하여 중복 여부를 확인한다.

NULL 허용

고유 인덱스는 NULL 값을 허용할 수 있다. 이는 기본 키와의 주요 차이점 중 하나로, 고유 인덱스를 가진 컬럼에 NULL 값이 삽입되는 경우 이를 허용하며, 단일 NULL 값만 존재하도록 관리한다.

검색 프로세스

1. 인덱스 탐색 고유 인덱스를 사용하여 데이터를 검색할 때, 데이터베이스는 B-트리 또는 B+ 트리와 같은 자료 구조를 사용하여 인덱스 테이블을 탐색한다.

2. 포인터로 데이터 접근 고유 인덱스는 보조 인덱스와 마찬가지로, 별도의 인덱스 테이블에 컬럼 값과 해당 데이터 행의 위치 정보를 포인터 형태로 저장한다. 탐색을 통해 찾은 인덱스 값을 바탕으로 포인터를 사용하여 실제 데이터를 접근하게 된다.

3. 데이터 무결성 확인 데이터 삽입이나 수정 시, 고유 인덱스를 통해 데이터베이스는 중복된 데이터가 있는지 검사한다. 중복된 값이 이미 존재한다면 오류를 반환하여 데이터 무결성을 유지하게 된다.

장점

• 고유 인덱스를 사용함으로써 데이터의 유일성을 보장하고, 특정 컬럼에 중복 데이터가 삽입되는 것을 방지할 수 있다.
• 이메일, 사용자 이름 등 중복되면 안 되는 컬럼에 대해 고유 인덱스를 설정하여 데이터의 무결성을 보장할 수 있다.

단점

• 데이터 삽입 시마다 중복값이 있는지 체크하기 때문에 쓰기 성능이 다소 떨어질 수 있다.
• 보조 인덱스와 같이 추가적이 저장 공간을 사용한다.
• 고유 인덱스는 NULL 값을 허용하지만, NULL 값이 중복될 수 없는 특성 때문에 이 부분을 관리하는 데 추가적인 주의가 필요하다.

사용 예시

사용자 이메일 주소나 휴대폰 번호 등 중복이 없어야 하는 컬럼에 설정하면, 데이터 무결성을 보장할 수 있다.

다중 열 인덱스, Composite Index

https://hyperskill.org/learn/step/18011

여러 개의 컬럼을 조합하여 만드는 인덱스다.

하나의 컬럼으로는 충분한 식별이 어렵거나, 여러 컬럼의 조합을 검색한거나 정렬, 필터링할 때 사용된다.

특징

• 조합 : 두 개 이상의 컬럼을 조합하여 하나의 인덱스로 만든다.
• 순서 : 인덱스가 생성된 순서대로 데이터를 정렬하고 검색하므로, 자주 사용하는 검색 조건에 맞춰 순서를 정하는 것이 유리하다.

동작 방식

컬럼 조합

여러 컬럼을 조합하여 단일 인덱스를 생성한다. 이 인덱스는 결합된 컬럼 값들을 기준으로 정렬되며, 특정한 순서로 인덱스가 구성된다.

데이터 정렬 및 인덱스 생성

여러 컬럼을 기준으로 데이터를 정렬하며, 결합된 인덱스 키를 기반으로 인덱스를 생성한다. 이 과정에서 데이터베이스는 먼저 첫 번째 컬럼을 기준으로 정렬하고, 동일한 값이 있는 경우 두 번째 컬럼을 기준으로 추가 정렬을 진행하는 방식으로 작동한다.

부분적 인덱스

첫 번째 컬럼만을 사용해 검색할 경우에는 성능 향상을 제공할 수 있지만, 두 번째 컬럼만을 사용하는 경우에는 인덱스를 효과적으로 사용할 수 없다. 다중 열 인덱스를 설계할 때는 검색에서 어떤 컬럼이 더 자주 사용되는지 분석하고 인덱스 순서를 결정해야 한다.

검색 프로세스

1. 인덱스 테이블 탐색 다중 열 인덱스를 사용하는 검색 요청이 들어오면, 데이터베이스는 결합된 컬럼의 순서에 따라 인덱스 테이블을 탐색한다. 이 과정은 B-트리 또는 B+ 트리와 같은 자료 구조를 통해 이루어진다.

2. 정렬된 순서 탐색 컬럼들이 결합된 형태로 정렬되어 있기 때문에, 특정 컬럼 조합에 대한 검색이 효율적으로 이루어진다.

3. 포인터를 통한 데이터 접근 포인터를 사용하여 해당 데이터를 접근한다. 데이터 행의 물리적 위치를 가리키며, 검색된 결과를 기반으로 테이블에서 데이터를 읽어오는 방식이다.

장점

• 여러 컬럼을 조합한 검색 성능을 크게 향상시킬 수 있다.
• 복합적인 조건을 가진 WHERE 절이나 JOIN에서 조회 성능이 향상된다.
• 특정 컬럼들의 조합으로 이루어진 조건으로 검색될 때 유용하다.

단점

• 컬럼의 순서를 잘못 정의하면 성능 향상을 기대하기 힘들다.
• 컬럼의 조합이 변경될 경우, 인덱스를 재정의해야 하는 경우가 생길 수 있다.

사용 예시

city와 zipcode를 결합하여 다중 열 인덱스를 생성하면, 특정 도시와 우편번호 조합으로 빠르게 검색할 수 있다.

텍스트 인덱스, Full-Text Index

https://velog.io/@leehanju408/%EA%B2%80%EC%83%89-%EC%84%B1%EB%8A%A5-%EA%B0%9C%EC%84%A0-3-Full-Text-Index

문자열과 같은 텍스트를 인덱스로 지정하여 긴 텍스트 데이터도 빠르게 검색할 수 있는 인덱스다.

문서, 설명, 댓글 등과 같은 긴 텍스트 필드에서 특정 단어나 구문을 검색하는 데 최적화 되어있다.

특징

• 텍스트 검색 최적화 : 긴 텍스트 데이터에서 특정 단어나 구문을 찾는데 최적화 되어있다.
• 단어 기반 검색 : 특정 단어가 포함된 문장이나 문서를 빠르게 찾을 수 있다.
• 자연어 처리 지원 : 일반적으로 텍스트 인덱스는 불용어(stop words)와 같은 일반적인 단어를 필터링하고, 동의어 처리나 어근 추출(stemming) 등 자연어 처리 기능을 제공하여 검색 정확도를 높인다.

동작 방식

인덱싱 과정

텍스트 인덱스를 생성할 때, 데이터베이스는 긴 텍스트 데이터를 단어 단위로 분할하고, 각 단어에 대해 인덱스를 생성한다. 이 과정에서 불필요한 단어들은 필터링되며, 주요 의미를 가지는 단어들만 인덱스로 기록한다.

단어 분할 및 저장

텍스트를 분할하여 각 단어의 위치를 기록한다. 특정 단어나 구문이 포함된 행을 빠르게 검색할 수 있으며, 검색뿐만 아니라 단어 빈도, 단어 위치 정보도 인덱싱된다.

자연어 처리

텍스트 인덱스는 동의어 처리나 어근 추출 등과 같은 자연어 처리 기법을 활용해 동일한 의미의 단어를 묶거나, 복수형과 단수형 같은 다양한 형태의 단어를 하나로 묶어 검색 효율을 높인다.

검색 프로세스

1. 단어 단위 인덱스 탐색 데이터베이스는 인덱스 테이블을 단어 단위로 탐색한다. 이를 통해 특정 단어가 포함된 모든 위치를 빠르게 찾을 수 있다.

2. 단어의 위치와 빈도 확인 단어 인덱스를 통해 검색된 단어의 위치와 빈도를 확인하고, 검색된 단어가 포함된 모든 문서나 행을 빠르게 반환한다. 이는 텍스트 데이터의 특성상 특정 구문이나 단어의 출현 위치가 중요한 경우에 유용하다.

3. 불용어 필터링 및 동의어 처리 검색 프로세스에서는 불필요한 일반 단어들을 필터링하고, 동의어나 어근이 동일한 단어들도 검색 결과에 포함시켜 보다 정확하고 포괄적인 검색 결과를 제공한다.

장점

• 긴 텍스트 데이터에서 특정 단어나 구문을 빠르게 검색할 수 있다.
• 부분 검색이나 단어 위치 기반 검색이 가능하다.
• 검색 엔진과 유사한 기능을 제공하여 사용자에게 더 나은 검색 경험을 제공할 수 있다.
• 단어 빈도, 특정 구문의 출현 위치 등 텍스트의 특성에 기반한 복합적인 검색을 수행할 수 있어 문서나 설명 필드와 같은 텍스트 데이터를 다루는 데 매우 유용하다.

단점

• 추가적인 저장 공간 필요.
• 일반 인덱스에 비해 더 복잡한 구조를 가짐.
• 쓰기 작업이 잦을 경우 성능 저하가 있을 수 있음.

사용 예시

게시판, 블로그 등에서 게시글 본문 내 특정 키워드를 검색할 때 전체 텍스트 인덱스를 사용하여 빠르게 조회할 수 있다.