[Relational Databases] DB Index 설정

인덱스를 사용하는 이유

일반적으로 SQL 서버에 데이터를 저장할 때 내부적으로는 아무런 순서 없이 저장합니다.
이때, 데이터 저장 영역은 Heap이라고 합니다.

Heap 에서는 인덱스가 없는 테이블의 데이터를 찾을 때
전체 데이터 페이지의 처음 레코드부터 끝 페이지 마지막 레코드 까지 모두 조회하게 됩니다.

이러한 검색 방식을 풀 스캔(Full Scan) 또는 테이블 스캔(Table Scan) 이라고 합니다.
다음과 같은 검색의 속도를 향상 하기 위해 인덱스를 사용하게 됩니다.

 

사용 예시

다음과 같은 테이블이 있다고 가정하겠습니다.

Create Table User (
	uuid Binary(16) Primary Key,
	name Varchar(20) Not Null,
	age Integer Not Null,
)

인덱스를 생성하기 위해서는 아래와 같이 설정할 수 있습니다.

-- single column index
Create Index single_uuid On User (uuid);

-- multi column index
Create Index multi_uuid_name On User (uuid, name);

인덱스는 무분별하게 설정한다고 해서 좋은 것이 아니라, 한 테이블에 3-5개 정도가 적당하고 타당한 기준을 통해 설정 해야 검색 속도를 향상 시킬 수 있습니다.

 

Index 설정 핵심 기준

1. 카디널리티 (Cardinality)
   카디널리티가 높으면 인덱스 설정에 좋은 column입니다. 왜냐하면 인덱스를 통해 불필요한 데이터의 대부분을 걸러낼 수 있기 때문입니다.
   카디널리티가 높으면 한 column이 갖고 있는 값의 중복도가 낮기 때문에 값들이 대부분 다른 값을 가집니다.
2. 선택도 (Selectivity)
   선택도가 낮으면 인덱스 설정에 좋은 column입니다. 왜냐하면 한 컬럼이 갖고 있는 값 하나로 여러 row가 찾아지기 때문입니다.
   선택도를 계산하는 방법은 (컬럼의 column 값의 row 수 / 테이블의 총 row 수 * 100) 입니다. 그리고 일반적으로 5~10%가 적당합니다.
   예를 들어 2명씩 같은 name의 column이 총 10개가 있다고 한다면 2 / 10 = 20% 입니다.
3. 조회 활용도
   해당 column이 실제 작업에서 얼마나 활용되는지 확인하여 많이 활용되면 인덱스를 설정하기 좋은 column입니다.
4. 수정 빈도
   인덱스도 테이블이기 때문에, 인덱스로 지정된 column의 값이 바뀌게 되면 인덱스 테이블도 새롭게 갱신되어야 하기 때문에 수정 빈도가 낮아야 인덱스 설정에 좋은 column입니다.

Index 설정 기준

• WHERE에 자주 사용되는 컬럼에 사용하기.
• LIKE와 사용할 경우에는 %가 뒤에 사용되도록 하기. (앞에 사용되면 Full Scan)
• ORDER BY에 자주 사용되는 컬럼에 사용하기.
• JOIN에 자주 사용되는 컬럼에 사용하기.
• 데이터의 변경이 잦은 컬럼에는 인덱스를 사용하지 않기.

 

Index 설정 시 주의 사항

인덱스 설정 시, 데이터베이스에 할당된 메모리를 사용하여 테이블 형태로 저장하게 됩니다. 즉, 인덱스가 많아지면 데이터베이스의 메모리를 많이 잡아먹게 됩니다. 그렇기 때문에 무분별한 인덱스 생성은 좋지 않습니다.

인덱스로 지정된 컬럼의 값이 바뀌게 되면 인덱스 테이블이 갱신되어야 하므로 쿼리 시간이 오래걸리고, 비효율적일 수 있습니다.

위와 같은 이유로 인덱스를 계속해서 만드는 것이 하나의 쿼리문을 빠르게 만들 수는 있지만, 전체적인 데이터베이스의 성능 부하를 가져올 수 있습니다.

 

Index 설정이 DML에 미치는 영향

Index는 Select 쿼리에서는 성능이 잘 나오지만, Insert, Update Delete 에서는 다릅니다.

Update, Delete는 인덱스로 설정된 column에 대해 조건(Where)을 사용할 수도 있는 Update, Delete 사용 시 조회에서는 성능이 크게 저하되지 않습니다. 여기서 수정/ 삭제할 데이터를 찾을 때 속도가 빠르다는 것이고, 수정, 삭제 자체를 빠르게 처리한다는 의미는 아닙니다.

반면에, Insert는 효율이 좋지 않습니다. 새로운 데이터를 추가하면서 인덱스가 설정되어 있던 컬럼의 테이블이 수정되어야 하기 때문입니다.

 

Index 설정 시 특이 사항

1. 검색 (Select) 속도 향상
2. 인덱스 테이블을 위한 추가 공간과 시간 필요
3. Insert, Update, Delete 가 경우에 따라 성능 하락 발생

 

Clustered Index, Non Clustered Index

Clustered Index

Clustered Index는 테이블의 데이터를 지정된 column에 대해 물리적으로 데이터를 재배열합니다. 데이터가 테이블에 삽입되는 순서에 상관없이 Index로 생성되어 있는 column을 기준으로 정렬되어 삽입됩니다.

Index Page를 키값과 데이터 페이지 번호로 구성하고, 검색하고자하는 데이터의 키 값으로 페이지 번호를 검색하여 데이터를 찾습니다.

이미지 출처: https://velog.io/@gillog/SQL-Clustered-Index-Non-Clustered-Index

• 테이블 당 하나의 Clustered Index 만 생성이 가능합니다. 일반적으로 PK Column으로 자동 생성됩니다.
• 물리적으로 데이터를 정리하므로 Clustered Index의 Index 테이블은 하나만 존재합니다.
  # 데이터 입력 시, 물리적 정렬로 DB에 Clusted Index를 기준으로 입력이 되므로 Heap에 있는 데이터를 꺼내었을 때, 모든 페이지의 데이터가 Clustered Index를 기준으로 정렬이 되어 있는 것을 확인할 수 있습니다.

 

Non Clustered Index

Non clustered Index는 물리적으로 데이터를 배열하지 않은 상태로 데이터 페이지가 구성됩니다. 테이블의 데이터는 그대로두고 지정된 column에 대해 정렬시킨 인덱스를 만들 분입니다.

Non Clustered Index는 Clustered Index보다 검색 속도는 느리지만, 데이터의 입력, 수정, 삭제는 더 빠릅니다.

이미지 출처: https://velog.io/@gillog/SQL-Clustered-Index-Non-Clustered-Index

• 테이블 당 여러 개를 생성할 수 있습니다.
• Unique 제약 조건이 있는 테이블을 만들면 자동으로 Non-Clustered Index를 만듭니다.
• 데이터 페이지가 물리적으로 정렬되어 있지 않기 때문에 인덱스에 의해 찾아가야 합니다.

 

마무리

프로젝트 DB 검색 효율을 높이기 위해 Index를 적용해보려고 합니다.
그래서 이번 포스팅에서는 인덱스를  설정하는 방법과, 인덱스의 특징, 장단점을 살펴보았습니다.
프로젝트에 적용해보면서 더 자세하게 알아보겠습니다.

포스팅 읽어주셔서 감사합니다.

 

 

[참고]

1. 효과적인 DB Index 설정하기

2. [SQL] Clustered Index & Non-Clustered Index