인덱스 (Index)

인덱스?

인덱스는 데이터베이스에서 특정 테이블의 검색 속도를 향상시키기 위한 자료 구조이다. 인덱스는 테이블 내의 열(Column)에 대한 검색을 더 빠르게 수행할 수 있도록 데이터의 논리적인 순서를 유지하고, 검색 연산을 효율적으로 수행할 수 있도록 지원한다.

 

1. 빠른 검색 : 인덱스를 사용하면 특정 열에 대한 검색 연산(주로 WHERE 조건)을 빠르게 수행할 수 있다. 인덱스는 특정 값이나 범위에 해당하는 레코드를 빠르게 찾아준다.

2. 정렬 및 유지 : 인덱스는 해당 열의 데이터를 정렬하여 저장하고 유지한다. 따라서 정렬된 데이터를 유지함으로써 이진 검색과 같은 효율적인 알고리즘을 사용할 수 있다.

3. 고유성 보장 : 유니크 인덱스는 특정 열의 값이 고유하도록 보장하며, 기본 키 제약 조건을 구현하는 데 사용될 수 있다.

4. 조인 성능 향상 : 인덱스는 여러 테이블 간의 조인 연산에서도 성능을 향상시킬 수 있다.

5. WHERE, ORDER BY, GROUP BY 성능 향상 : 인덱스를 사용하면 WHERE 절에 사용되는 조건, ORDER BY 및 GROUP BY 절에서도 성능이 향상될 수 있다.

6. 쓰기 성능 저하 : 인덱스는 데이터의 변경(추가, 수정, 삭제)이 발생할 때 인덱스를 갱신해야 하므로 쓰기 연산의 성능은 일반적으로 저하될 수 있다.

 

가장 일반적인 인덱스 유형으로는 B-트리(B-tree) 인덱스가 있으며, 다양한 데이터베이스 시스템에서 지원되는 다양한 인덱스 유형이 있다. 예를 들어, 클러스터형 인덱스, 비클러스터형 인덱스, 공간 인덱스 등이 있다. 개발자는 쿼리의 성능 향상을 위해 적절한 인덱스를 선택 및 관리 해야한다.

 

클러스터형 인덱스(Clustered Index), 비클러스터형 인덱스(Non-Clustered Index)

클러스터형 인덱스(Clustered Index)

- 클러스터형 인덱스는 테이블당 한 개만 생성이 가능하다.(프라이머리 키에 대해서만 적용되는 개념)

- 인덱스가 정렬되어 있고, 인덱스에 해당하는 튜플(레코드)도 정렬되어 있는 방식이다. 또한 인덱스와 튜플이 저장되는 공간이다.

- 인덱스들과 각 인덱스에 해당하는 튜플이 정렬되어 있기 때문에 빠르게 튜플을 찾을 수 있다. 특히 범위 조회 시 유리하다.

- 영어 사전 처럼 책의 내용 자체가 순서대로 정렬이 되어 있어, 인덱스 자체가 책의 내용과 같다.

- 데이터의 입력,수정,삭제는 느리다.

 

비클러스터형 인덱스(Non-Clustered Index)

- 테이블당 여러개를 생성할 수 있다.

- 비클러스터형 인덱스는 인덱스 자체는 정렬되어 있지만, 인덱스에 해당하는 튜플(레코드)은 정렬되어 있지 않다. 또 한 인덱스 인덱스와 튜플이 저장되는 공간이 다르다.

- 컬럼 인덱스를 통해 원하는 데이터에 해당하는 튜플의 프라이머리 키의 주소를 알아내고, 프라이머리 키를 기준으로 튜플에 접근하는 방식이다.

- 비클러스터형 인덱스는 그냥 찾아보기(목)가 있는 일반 책과 같다.

- 데이터의 입력,수정,삭제가 빠르다.

인덱스의 종류

B-Tree 인덱스

- 데이터베이스의 인덱싱 알고리즘 가운데 가장 먼저 도입되고 가장 일반적으로 사용되는 알고리즘

- 균형유지 : 좌우 균형을 유지하도록 설계가 되어있다

- 브랜치 노드와 리프 노드로 구성이 된다. 리프 노드에는 데이터가 저장되어 있다.

- 검색, 삽입, 삭제 연산에 대해 균형을 유지하면서 효율적인 성능을 제공한다.

- 중복된 키를 허용한다.

 

B+TREE 인덱스

- 데이터는 리프 노드에만 저장되고, 브랜치 노드는 오직 키 값과 해당 키에 대한 리프 노드로 가는 포인터만을 갖는다.

- 범위 검색에 효율적이다. 모든 데이터가 리프 노드에만 존재하므로, 범위 검색 시에는 해당 범위의 리프 노드만을 스캔하면 되기 때문이다.

- 순차 액세스에 더 효율적이다. 모든 데이터는 정렬된 순서로 리프 노드에 저장되어 있기 때문이다.

- 중복된 키를 허용한다.

 

인덱스 레인지 스캔

- 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식

- 인덱스 레인지 스캔은 2단계로 진행이 된다.

- 첫 번째 단계는 루트에서부터 트리를 순회하여 리프 노드에서 하위 키를 찾는다.

- 두 번째 단계는 첫 번째 단계에서 찾은 키에서부터 상위 키까지 순차적으로 레코드를 읽어 처리한다.

- 보다 적은양의 데이터를 스캔하여 효율적으로 결과를 가져오기 때문에 대량의 데이터베이스에서 성능을 향상시킬 수 있다.

 

해시(Hash)인덱스

- 해시 인덱스는 주로 등호(=)를 이용한 동등 검색에 사용되며, 해시 함수를 통해 계산된 해시값을 통해 데이터에 빠르게 액세스할 수 있다.

- 동등 검색에 대한 검색 속도가 빠르고 인덱스 크기가 일정하다는 장점이 있다.

- 범위 검색이나 정렬된 결과를 필요로 하는 쿼리에는 적합하지 않고 두 개 이상의 다른 키가 동일한 해시값을 생성하는 해시 충돌이 발생할 수 있는 단점이 있다.

- 검색하고자 하는 값을 주면 해시 함수를 거쳐서 찾고자 하는 키값이 포함된 버킷을 알아내고 버킷안에서 실제 레코드가 저장된 위치를 찾을 수 있다.

 

인덱스의 동작 방식

인덱스의 동작 방식은 주로 B-트리(B+트리)와 같은 트리 구조를 사용합니다.

1. 인덱스 생성 :

- 인덱스는 특정 테이블의 열(또는 열의 조합)에 대한 정렬된 키와 해당 키가 위치한 레코드 주소(포인터)를 저장하는 데이터 구조입니다.

- 데이터베이스 시스템은 테이블에 인덱스를 생성할 때 해당 열의 값을 정렬하여 트리 구조로 만듭니다.

2. B-트리 탐색 :

- B-트리는 균형 잡힌 트리로, 각 노드는 정해진 수의 키와 자식 노드에 대한 포인터를 가집니다.

- 검색 시, 인데스를 사용하는 쿼리에서 제공된 조건에 따라 B-트리를 탐색합니다.

- 탐색은 트리의 루트부터 시작하여 조건에 부합하는 키를 찾을 때까지 이어집니다.

- 각 단계에서 트리를 분기하면서 찾아야 할 키값의 범위를 좁혀나갑니다.

3. 레코드 위치 찾기 :

- 검색이 완료되면 해당 키 값에 대한 레코드의 위치(데이터 파일 내의 주소 또는 페이지 번호 등)를 얻습니다.

- 이 위치 정보를 사용하여 데이터베이스는 실제 레코드에 대한 액세스를 수행합니다.

4. 쿼리 최적화 :

- 인덱스를 사용하면 전체 테이블을 스캔하지 않고도 원하는 행을 빠르게 찾을 수 있습니다.

- 이로써 데이터베이스는 검색 연산의 성능을 향상시킬 수 있습니다.

B-트리 인덱스

인덱스는 어떤 기준으로?

1. 검색 빈도가 높은 열 :

- 인덱스는 검색 연산을 효과적으로 수행하기 위한 것이므로 검색 빈도가 높은 열에 인덱스를 생성 하는 것이 일반적으로 좋습니다.

- 예를 들어,  자주 검색되는 열이나 WHERE절에서 자주 사용되는 조건으로 사용되는 열이 해당됩니다.

2. 조인에 사용되는 열 :

- 조인 연산에서 조인 조건으로 사용되는 열은 인덱스를 고려할 가치가 있습니다.

- 조인할 때 인덱스가 있는 열을 기준으로 조인하면 성능 향상이 기대됩니다.

3. 정렬 및 그룹화에 사용되는 열 :

- ORDER BY 나 GROUP BY 구문에서 사용되는 열에 인덱스를 생성하면 정렬 및 그룹화 작업의 성능을 향상시킬 수 있습니다.

4. 적절한 인덱스 선택 :

- 데이터베이스 시스템마다 지원하는 다양한 인덱스 유형이 있습니다. B-트리, 해시 인덱스, 전문 텍스트 인덱스 등이 있습니다.

- 어떤 유형의 인덱스가 쿼리에 가장 적합한지를 고려하여 선택해야 합니다.

5. 테이블 크기 :

- 작은 테이블에서는 인덱스의 이점이 크게 나타나지 않을 수 있습니다. 따라서 테이블의 크기와 함께 고려해야 합니다.

6. 쓰기 작업의 빈도 :

- 인덱스를 생성하면 읽기 성능은 향상되지만 쓰기 성능에는 영향을 미칩니다.

- 데이터의 쓰기 작업이 많은 경우, 인덱스를 과도하게 사용하면 오히려 성능 저하가 발생할 수 있습니다.

7. 시스템 자원 제한 :

- 시스템의 자원(메모리, 디스크 공간 등)이 제한적인 경우, 인덱스를 많이 사용하면 자원 부족 문제가 발생할 수 있습니다.

 

인덱스 스캔 방식

인덱스 스캔은 데이터베이스에서 인덱스를 사용하여 특정 조건에 맞는 레코드를 검색하는 방법을 나타낸다. 인덱스 스캔은 다양한 방식으로 이루어질 수 있다

 

1. 전체 인덱스 스캔(Full Index Scan) : 인덱스의 전체 범위를 스캔하여 조건에 맞는 모든 레코드를 찾는 방식이다. 이 방법은 데이터베이스 테이블의 크기가 작은 경우나 조건에 맞는 레코드가 많은 경우에는 비효율적일 수 있다.

2. 유니크 인덱스 스캔(Unique Index Scan) : 유니크한 값을 가진 인덱스의 경우, 해당 값에 대한 검색이 목적이다. 유니크한 값을 가진 인덱스에서 빠르게 검색할 수 있다.

3. 범위 인덱스 스캔(Range Index Scan) : 특정 범위의 값에 대해 인덱스를 스캔하여 해당 범위에 속하는 레코드를 찾는 방식이다. 주로 BETWEEN, >, < 등의 조건이 사용된다.

4. 포인터 인덱스 스캔(Pointers or Bookmark Scan) : 인덱스 자체에서 레코드로 바로 이동하여 해당 레코드를 읽는 방식이다. 주로 클러스터형 인덱스에서 사용된다.

5. 클러스터형 인덱스 스캔(Clustered Index Scan) : 클러스터형 인덱스를 사용하여 데이터를 읽는 방식으로, 인덱스의 순서대로 테이블을 스캔한다.

6. 비클러스터형 인덱스 스캔(Non-Clustered Index Scan) : 비클러스터형 인덱스를 사용하여 데이터를 읽는 방식으로, 인덱스를 통해 테이블의 행위치를 찾아서 읽는다.

7. 해시 인덱스 스캔(Hash Index Scan) : 해시 인덱스를 사용하여 검색하는 방식이다. 주로 동등 검색에서 사용되며, 해시 함수를 이용하여 레코드를 찾는다.

 

쿼리 실행 계획

데이터베이스 엔진이 쿼리를 실행하기 위해 선택한 최적의 경로를 나타내는 계획이다. 쿼리 실행 계획은 데이터베이스 엔진이 어떻게 데이터를 검색하고 조인하며 정렬하는지 등을 표현한다. 데이터베이스의 성능 튜닝과 최적화에 중요한 정보를 제공한다.

 

- 어떤 테이블이 먼저 스캔되는지, 그리고 어떤 인덱스가 사용되는지에 대한 정보가 보여진다.

- 여러 테이블이 조인될 때 어떤 순서로 조인이 수행되는지에 대한 정보가 있다.

- 어떤 인덱스가 사용되었는지, 그리고 그 인덱스가 어떻게 활용되었는지에 대한 정보가 있다.

- 어떤 조건이 where절에서 사용되어 데이터를 필터링하는지에 대한 정보가 있다.

- 정렬이나 그룹핑이 필요할 경우 어떻게 수행되는지 정보가 있다.

 

확인하는 방법

- EXPLAIN 명령어를 쿼리 앞에 붙여 실행하면 해당 쿼리의 실행 계획을 확인할 수 있다. 데이터베이스에 따라 명령어가 다르다.

- DBMS도구툴을 보면 쿼리 실행 계획을 볼 수 있는 도구를 제공한다.

- 성능 분석 도구나 쿼리 최적화를 지원하는 서드파티 도구들은 더 상세하고 시각적인 정보를 제공한다.