DBMS의 인덱스 (INDEX)

1. 인덱스란?

인덱스(색인)는 데이터베이스에서 검색 성능을 향상시키기 위해 사용하는 데이터 구조이다. 인덱스는 특정 열(column)에 대한 검색 작업을 빠르게 수행할 수 있도록 돕는다.

인덱스의 구조

• B-Tree 인덱스: 가장 일반적으로 사용되며, 균형 잡힌 트리 구조로 되어 있어 검색, 삽입, 삭제가 효율적이다. B-Tree자료구조는 나중에 시간되면 작성하겠다.
• Hash 인덱스: 해시 테이블을 사용하여 정확한 매칭 검색에 빠르지만 범위 검색에는 적합하지 않다.

인덱스의 종류

• 클러스터드 인덱스 (Clustered Index): 테이블의 데이터가 인덱스 순서에 따라 물리적으로 정렬된다. 한 테이블에 하나만 존재할 수 있다.
• 논클러스터드 인덱스 (Non-Clustered Index): 인덱스가 별도의 공간에 저장되며, 인덱스 키와 실제 데이터의 포인터를 포함한다. 한 테이블에 여러 개 존재할 수 있다.

클러스터링 인덱스와 기본 키 (Primary Key)

• 클러스터링 인덱스는 테이블의 데이터가 인덱스 순서에 따라 물리적으로 정렬되도록 하는 인덱스이다.

클러스터링 인덱스와 기본 키의 관계

• 기본 키(Primary Key): 기본 키는 테이블의 각 행을 고유하게 식별하는 열 또는 열의 조합이다. 기본 키는 자동으로 고유 제약 조건(Unique Constraint)을 가진다.
• 기본 키와 클러스터링 인덱스: 대부분의 데이터베이스 시스템에서 기본 키를 설정하면, 기본 키 열에 자동으로 클러스터링 인덱스가 생성된다. 이는 기본 키 열이 물리적으로 정렬되고, 기본 키를 기반으로 데이터 검색이 빠르게 이루어지도록 한다.

-- 기본 키를 설정하면 기본 키 열에 클러스터링 인덱스가 자동으로 생성된다.
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    LastName NVARCHAR(50),
    FirstName NVARCHAR(50)
);

인덱스를 생성시

• 디스크 용량 증가: 인덱스는 추가적인 저장 공간을 필요로 한다. 이는 특히 큰 테이블에 인덱스를 추가할 때 더 많은 디스크 용량을 차지한다. 인덱스가 많아질수록, 디스크 용량 소모도 증가한다.
• 인덱스 데이터 구조 생성: 인덱스가 생성될 때, 데이터베이스는 인덱스 키와 해당 키가 가리키는 실제 데이터의 포인터를 포함하는 데이터 구조(B-Tree 또는 Hash Table 등)를 디스크에 작성한다. 이를 통해 원본데이터를 찾아간다.

2. 인덱스 사용 방법

인덱스는 테이블 생성 시 또는 테이블 생성 후에 추가할 수 있다. 다음은 MSSQL에서 인덱스를 생성하는 예시이다.

인덱스 생성 예시

-- 클러스터드 인덱스 생성
CREATE CLUSTERED INDEX idx_employee_id
ON Employees(EmployeeID);

-- 논클러스터드 인덱스 생성
CREATE NONCLUSTERED INDEX idx_employee_lastname
ON Employees(LastName);

인덱스 사용 시 주의점

• 인덱스는 테이블의 특정 열에 대해 WHERE조건이 잦을 때 유용하다.
• 인덱스는 삽입, 수정, 삭제 작업 시 성능 저하를 초래할 수 있다. (락이 걸리기 때문)
• 하지만 너무 많은 인덱스는 오히려 성능을 저하시킬 수 있다.

3. 인덱스의 용이성

• 인덱스가 유용한 경우
  빈번한 검색: WHERE 절에서 자주 사용되는 열에 인덱스를 추가하면 검색 속도가 향상된다.
  조인 작업: 조인 조건에 사용되는 열에 인덱스를 추가하면 조인 속도가 빨라진다.
  정렬 및 그룹화: ORDER BY, GROUP BY 절에 사용되는 열에 인덱스를 추가하면 성능이 향상된다.
• 인덱스가 유용하지 않은 경우
  작은 테이블: 테이블이 작으면 인덱스의 장점이 미미하다.
  빈번한 업데이트: 데이터의 삽입, 수정, 삭제가 빈번한 경우 인덱스 유지 비용이 높아질 수 있다.
  범위 검색: 해시 인덱스의 경우 범위 검색에 적합하지 않다.

4. 성능 분석 (실행 계획)

• 인덱스의 성능을 분석하려면 실행 계획을 확인해야 한다. MSSQL에서는 EXPLAIN 또는 SHOWPLAN 명령을 사용하여 실행 계획을 볼 수 있다. 아니면 SSMS에서 쿼리 실행계획 포함을 통해 확인할 수 있다.

• 실행 계획 예시

-- 실행 계획 보기
SET SHOWPLAN_ALL ON;
GO
SELECT * FROM Employees WHERE LastName = 'Smith';
GO
SET SHOWPLAN_ALL OFF;
GO

• 실행 계획 해석
  Index Seek: 인덱스를 통해 필요한 데이터를 효율적으로 찾는 작업이다. 매우 빠르다.
  Index Scan: 인덱스 전체를 스캔하는 작업이다. 데이터가 많으면 느려질 수 있다.
  Table Scan: 인덱스를 사용하지 않고 테이블 전체를 스캔하는 작업이다. 매우 비효율적이다.

5. 포괄 열, 비포괄 열?

• 포괄 열은 인덱스의 키 값에 포함되지 않지만, 인덱스에 저장되어 쿼리 성능을 향상시키는 열을 말한다. 인덱스를 설정할 때 특정 열을 포괄 열로 지정하면, 쿼리가 인덱스만으로도 필요한 데이터를 모두 가져올 수 있어 성능이 향상된다. 이는 커버링 인덱스(covering index)라고도 한다.

포괄 열 예시

-- LastName을 키로 하고, FirstName을 포괄 열로 포함하는 인덱스 생성
CREATE NONCLUSTERED INDEX idx_employee_lastname
ON Employees(LastName)
INCLUDE (FirstName);

포괄 열 사용 시점

• 포괄 열 설정: 주로 검색 조건에는 포함되지 않지만, SELECT 절에 자주 사용되는 열을 포함할 때.
• 비포괄 열 설정: 인덱스 키 열로 지정된 열만을 사용하여 충분히 성능 향상이 가능한 경우.

6. 여러 컬럼에 대한 인덱스

• 여러 컬럼에 대해 인덱스를 걸었을 때, 인덱스가 효과적으로 사용되려면 쿼리 조건에 모든 인덱스 컬럼이 포함되어야 한다. 예를 들어 LastName과 FirstName 컬럼에 인덱스를 걸었을 때, 다음과 같이 두 컬럼을 모두 WHERE 조건에 포함해야 인덱스를 효율적으로 탄다.

-- LastName과 FirstName 컬럼에 인덱스가 있는 경우
SELECT * FROM Employees WHERE LastName = 'Smith' AND FirstName = 'John';

7. 예외적인 케이스

• 특정 조건을 항상 사용하는 경우
  예를 들어 STATUS라는 값이 1에서 10까지 존재한다고 가정하자. 이때 항상 1에서 7까지만 보고 모든 쿼리에서 해당 조건을 사용한다면, 인덱스 조각화가 덜 되어도 인덱스는 여전히 효율적일 수 있다. 이러한 경우 인덱스를 사용하면 특정 범위의 데이터를 빠르게 검색할 수 있다.

-- STATUS가 1에서 7까지인 데이터만 조회
SELECT * FROM Orders WHERE STATUS BETWEEN 1 AND 7;

• 소량의 데이터
  테이블의 행(row) 수가 적으면 옵티마이저는 인덱스 스캔 대신 테이블 스캔을 선택할 수 있다. 이는 소량의 데이터에 대해서는 인덱스 스캔보다 테이블 스캔이 더 효율적일것으로 판단하기 때문.

결론

• 인덱스는 데이터베이스 성능 최적화에 중요한 역할을 한다. 적절한 열에 인덱스를 추가하면 검색 성능이 향상되지만, 너무 많은 인덱스는 오히려 성능 저하를 초래할 수 있다. 실행 계획을 통해 인덱스의 사용 여부를 확인하고, 필요에 따라 인덱스를 추가하거나 제거하도록 하자.