[DB] 인덱스(Index)

※ 해당 포스팅은 개인의 공부 정리용 글입니다. 틀린 내용이 있다면 추후 수정될 수 있습니다.

※ 해당 포스팅은 Mycrosoft SQL Server에서 사용하는 T-SQL 문법을 기반으로 하고 있으며, 예시는 SSMS(SQL Server Management Studio) 환경에서 작성하고 있습니다. 

 

 

 

 

이번 포스팅에서는 데이터베이스의 인덱스에 대해 알아보겠습니다.

1. 인덱스(Index)란

 사전적 의미의 인덱스(index)는 책에서 특정 내용을 쉽게 찾도록 해주는 색인을 뜻하며, 데이터베이스에서는 이와 비슷하게 특정 속성으로 인덱스를 생성하여 검색을 빠르게 해주는 기능을 지원합니다. 관계형 데이터베이스는 테이블에 존재하는 속성을 인덱스로 설정할 수 있으며, 외부에 해당 속성으로 별도의 트리를 생성하여 그 속성을 기준으로 검색 시 빠르게 찾아낼 수 있습니다. 다만 반드시 트리를 따로 생성하는 것은 아니며 별도의 트리를 생성하는 경우는 넌클러스터 인덱스, 데이터의 저장 순서 자체를 바꾸는 경우는 클러스터 인덱스라고 합니다. 또한 인덱스를 구성하는 속성 갯수에 따라 단일 인덱스와 복합 인덱스로 구분하며, 인덱스 종류에 따른 내용은 밑에서 다루도록 하겠습니다.

 

 인덱스의 장점은 검색 속도가 상승한다는 점입니다. 데이터베이스는 많은 양의 데이터를 저장하기 때문에, 조건에 맞는 데이터의 검색 비용이 생각보다 높아질 수 있습니다. 이 때 검색 조건(속성)의 인덱스가 있다면, DBMS에서 모든 행을 돌며 조건을 만족하는 데이터를 일일히 찾는것이 아니라(순차 검색) 인덱스를 통해  빠르게 해당 데이터를 찾을 수 있습니다. 따라서 검색 조건으로 사용될 일이 많은 속성의 경우 미리 인덱스로 설정하여 검색 비용을 줄이는 것이 좋습니다.

-- 1920년도 및 그 이후에 태어난 선수들의 수
SELECT COUNT(*)
FROM players
where birthYear >= 1920;

그림 1. (좌)인덱스 X, (우)인덱스 O

 위 예시는 players 테이블에서 1920년도 이후로 태어난 선수의 숫자를 카운트하는 내용으로, birthYear 속성으로 인덱스를 생성하기 전과 후를 비교하고 있습니다. 전에는 논리적 읽기 횟수가 454회 발생했지만, 인덱스 추가 후에는 단 18회만에 실행했음을 확인할 수 있습니다. SSMS에서는 Ctrl + L 을 눌러 실행 계획(Execution Plan)을 살펴보면 좀 더 자세하게 알아볼 수 있습니다.

그림 2. (상)인덱스 X, (하)인덱스 O

 위는 birthYear 속성 인덱스 생성 전, 아래는 생성 후의 실행 계획입니다. 우리가 주목할 부분은 우측의 Index Scan 과 Index Seek 입니다. Index Scan은 인덱스라는 단어가 들어갔지만, 사실상 순서대로 모든 데이터를 스캔하듯이 확인한다는 뜻입니다. 위의 경우 birthYear를 조건으로 검색해야 하나 해당 속성의 인덱스가 없으므로, 모든 데이터를 돌며 birthYear 값이 1920 이상인 데이터를 체크하는 것입니다.

 아래의 경우 birthYear 속성의 인덱스가 존재하므로, 해당 속성으로 정렬된 트리에서 birthYear 값이 1920인 곳을 빠르게 찾은 뒤 그 이후의 데이터만 체크하면 됩니다. 이러한 방식을 Index Seek이라 하며, 검색 조건에 해당하는 인덱스가 존재하여 이를 활용, 빠르게 데이터를 찾아낼 수 있는 경우에 해당합니다.

 

 다만 인덱스에도 단점은 존재합니다. 인덱스를 만들 경우 테이블 외부에 별도로 해당 속성으로 정렬된 트리(관계형 DB는 주로 B+트리)을 생성하게 되는데, 데이터의 삽입, 수정, 삭제 시마다 해당 트리도 갱신되어야 합니다. 즉, 인덱스가 많을수록 데이터 삽입, 수정, 삭제 연산의 비용이 증가하는 문제점이 있습니다. 따라서 검색 조건으로 자주 사용되는 속성들만 인덱스로 설정하고, 무분별한 인덱스 생성은 지양하는 것이 좋습니다. 

 

 

 

2. 클러스터(Clustered) 인덱스 vs 넌클러스터(Non-Clustered) 인덱스

 데이터베이스의 인덱스는 크게 클러스터 인덱스와 넌클러스터 인덱스로 구분합니다.

 

 클러스터(Clustered) 인덱스는 특별한 의미를 가지는 인덱스로, 해당 테이블의 데이터가 저장되는 기준이 됩니다. 예를 들어 players라는 테이블에서 playerID를 클러스터 인덱스로 설정할 경우, players의 데이터는 playerID를 기준으로 정렬되며 이후에 추가하는 데이터도 playerID 순서에 맞게 삽입됩니다. 이러한 특징때문에 클러스터 인덱스는 테이블당 한개만 설정할 수 있지만, 검색 속도는 가장 빠릅니다. 보통 테이블의 기본키를 클러스터 인덱스로 설정하는 경우가 많습니다.

 

 클러스터 인덱스 외에 추가로 생성하는 인덱스는 넌클러스터(Non-Clustered) 인덱스입니다. 특정 속성으로 넌클러스터 인덱스로 생성할 경우, 해당 속성을 기준으로 정렬된 트리를 외부에 따로 생성하게 됩니다. 데이터의 실제 저장 순서에는 영향을 미치지 않기 때문에, 클러스터 인덱스와 달리 별도의 갯수 제한은 없습니다. 다만 데이터 검색 시 해당 트리에서 먼저 찾은 뒤, 트리에 저장된 실제 데이터 주소로 찾아가는 과정이 들어가므로(Look Up 연산) 클러스터 인덱스보다는 검색  비용이 조금 더 소모됩니다. 물론 아예 인덱스가 존재하지 않는 경우보다는 검색 비용이 훨씬 절약되기 때문에, 클러스터 인덱스 외에도 검색 조건으로 자주 쓰이는 속성이 있다면 넌클러스터 인덱스로 설정하는 것이 좋습니다. 

 

그림 3. 클러스터(Clustered) vs 넌클러스터(Non-Clustered)

 위 그림은 클러스터 인덱스와 넌클러스터 인덱스를 추가하는 모습을 도식화한 것입니다(실제 관계형 데이터베이스의 구조는 B+트리를 많이 사용한다고 하며, 그림은 일단 트리형 구조라는 뜻으로 이해해주시기 바랍니다). 클러스터 인덱스를 추가할 경우 기존의 트리를 클러스터 인덱스의 속성을 기준으로 재정렬하며, 넌클러스터 인덱스 추가 시에는 외부에 별도의 트리를 따로 생성하게 됩니다.

 따라서 클러스터 인덱스를 조건으로 검색 시 애초에 데이터가 해당 속성을 기준으로 정렬되어 있으므로 매우 빠르게 실행 가능합니다. 넌클러스터 인덱스를 조건으로 검색 시 외부 트리를 참조하여 원본 데이터 위치를 찾으므로(Look Up) 클러스터 인덱스보다는 다소 느리지만, 인덱스가 없는 경우에 비하면 빠르게 실행 가능합니다. 

---

클러스터와 넌클러스터 인덱스에 관한 내용은 중요하므로, 다시 한번 정리하자면 다음과 같습니다.

 

 

클러스터 인덱스(Clustered Index)

• 해당 테이블의 데이터 저장 기준
• 생성 시 테이블의 데이터를 해당 속성 기준으로 정렬
• 테이블당 1개만 생성 가능
• 검색 시 가장 빠르게 동작함 

 

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

• 실제 데이터 구조에는 영향 X
• 생성 시 외부에 해당 속성의 트리를 별도로 생성
• 갯수 제한 X
  (단, 무분별한 생성은 데이터 삽입, 수정, 삭제 연산 비용을 증가시킴)
• 검색 시 클러스터 인덱스보다는 다소 느림
  (외부 트리에서 데이터를 찾은 후 실제 데이터 위치로 찾아가는 Look Up 연산 발생)

 

 

 

3. 복합 인덱스

 인덱스는 단일 속성뿐 아니라 여러개의 속성을 이용하여 생성할 수 있으며, 2개 이상의 속성을 포함한 인덱스를 복합 인덱스라고 합니다. 검색 조건으로 여러개의 속성을 사용할 수도 있으므로, 이런 경우에도 인덱스를 통해 성능을 향상시키고자 한다면 해당 속성들로 구성된 복합 인덱스가 필요합니다.

 

 복합 인덱스의 경우 인덱스를 구성하는 속성들의 순서가 중요하다는 특징이 있습니다. 속성 A와 B를 이용해 복합 인덱스(A, B)를 설정한다면 A가 1순위, B가 2순위 정렬 조건이 됩니다. 이 경우, 단일 속성 A를 조건으로 하는 검색 수행 시에는 해당 인덱스를 통해 빠르게 실행할 수 있으나, 2순위 속성인 B를 조건으로 검색 시 인덱스를 활용하지 못합니다. 따라서 단일 속성 B를 이용한 검색이 많이 필요해진다면, 복합 인덱스(A, B)와는 별개로 B 속성의 인덱스를 따로 생성해야 합니다. 

-- 복합 인덱스(birthYear, birthMonth, birthDay)
-- birthYear, birthMonth 검색
SELECT COUNT(*)
FROM players
where birthYear >= 1920 AND birthMonth>= 5;

-- 복합 인덱스(birthYear, birthMonth, birthDay)
-- birthMonth 검색
SELECT COUNT(*)
FROM players
where birthMonth >= 5;

그림 4. (상)birthYear, birthMonth 검색,(하)birthMonth 검색

  players 테이블에 복합 인덱스(birthYear, birthMonth, birthDay)를 설정한 상태에서 검색 효율을 비교하는 예시입니다. 위쪽은 birthYear, birthMonth 두가지 속성을 조건으로 검색해도, 실행 계획을 확인해보면 Index Seek로 고효율로 동작하고 있음을 알 수 있습니다. 허나 아래쪽은 birthMonth 속성 하나만 조건으로 걸고 검색해도 실행 계획을 보면 Index Scan으로 동작함을 확인할 수 있습니다. 따라서 birthMonth나 birthDay를 이용한 검색이 자주 필요해진다면, 해당 속성의 단일 인덱스를 따로 생성해야 합니다. 

 

 

 

4. SQL - 인덱스 생성 및 삭제

 SQL로 테이블에 인덱스를 설정하려면 다음과 같은 문법을 사용합니다.

CREATE (UNIQUE) (CLUSTERED/NONCLUSTERED) INDEX 인덱스명 ON 테이블명(속성1, 속성2, ...)

  UNIQUE 키워드를 붙이면 해당 인덱스가 중복값을 허용하지 않도록 설정할 수 있습니다. 물론 해당 속성에 이미 중복값이 있는 경우라면 사용할 수 없습니다. 

 CLUSTERED나 NONCLUSTERED 키워드를 사용해 해당 인덱스의 클러스터/넌클러스터 여부를 명시할 수 있으며, 생략 시 기본적으로 넌클러스터 인덱스가 생성됩니다. 이미 클러스터 인덱스가 존재하는 상태에서 CLUSTERED 키워드 사용 시 에러가 발생합니다.

 속성을 여러개 입력함으로써 복합 인덱스로 생성할 수 있으며, 복합 인덱스는 위에서 언급했듯이 순서가 중요하므로 주의해서 입력해야 합니다.

 

생성한 인덱스는 다음과 같이 삭제할 수 있습니다.

DROP INDEX 테이블명.인덱스명

 

 다음은 SQL을 이용해 players 테이블에 인덱스를 생성, 삭제하는 예시입니다.

-- 인덱스 생성(birthYear)
CREATE NONCLUSTERED INDEX NCl_birthYear ON players(birthYear);

-- 인덱스 삭제
DROP INDEX players.NCl_birthYear

그림 5. 인덱스 생성 및 삭제

 players 테이블에 birthYear 속성으로 NCl_birthYear라는 이름의 넌클러스터 인덱스를 추가했습니다. birthYear 속성은 중복값을 허용하기 때문에 UNIQUE 키워드는 사용 불가하며, NONCLUSTERED 키워드는 생략해도 결과는 같습니다. players 테이블을 새로고침 해보면 Indexes 폴더에 NCl_birthYear 인덱스가 추가된 모습을 확인할 수 있으며, 해당 인덱스의 유니크 여부와 클러스터 여부도 나타납니다(Non-Unique, Non-Clustered). DROP INDEX 후에는 해당 인덱스가 삭제됩니다.