[TIL] Index

인덱스(index)란?

추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조

• 책의 목차처럼 원하는 내용을 훨씬 빠르게 찾을 수 있듯이 테이블에서 원하는 데이터를 찾기 위해 인덱스를 이용

 인덱스를 활용하면, 데이터를 조회하는 SELECT 외에도 UPDATE나 DELETE의 성능이 함께 향상된다. 그 이유는 해당 연산을 수행하려면 해당 대상을 조회해야만 작업을 할 수 있기 때문이다.

 

 만약 index를 사용하지 않은 컬럼을 조회해야 하는 상황이라면 전체를 탐색하는 Full Scan을 수행해야 한다. Full Scan은 전체를 비교하여 탐색하기 때문에 처리 속도가 떨어진다.

 

인덱스의 장점과 단점

장점

• 데이터가 정렬되어 있기 때문에 테이블에서 검색과 정렬 속도를 향상
  • 조건 검색 Where의 효율성
  • 정렬 Order by 정의 효율성
  • MIN, MAX의 효율적인 처리 가능
• 테이블 행의 고유성을 강화
• 전반적인 시스템의 부하를 줄임단점
• 인덱스를 잘못 사용할 경우 오히려 성능이 저하되는 역효과가 발생
• 인덱스를 관리하기 위해 DB의 약 10%에 해당하는 저장공간이 필요
• 인덱스 스캔의 비효율적인 사용

 

인덱스를 관리하기 위해 DB의 약 10%에 해당하는 저장공간이 필요한 이유

 

1. 인덱스 구조

• 인덱스는 데이터베이스 내에서 빠른 검색을 위해 사용되는 데이터 구조
• 일반적으로 B-tree, B+tree, 해시 등의 알고리즘을 사용하여 인덱스를 구성하며 효율적인 검색을 위해 추가적인 저장 공간을 필요

 

2. 중복 데이터

• 인덱스는 기본 테이블의 키(주로 열)와 해당 키가 위치한 물리적인 주소를 매핑하는 역할
• 따라서 인덱스에는 중복된 값을 가진 여러 개의 항목이 존재할 수 있음
  • 예를 들어, 동일한 값에 대한 다른 레코드들은 모두 같은 위치 정보를 가지게 됨.

 

3. 추가 오버헤드

• 인덱싱은 데이터베이스 작업에 대한 성능 향상을 제공하지만, 이를 유지하기 위해서는 추가적인 오버헤드가 발생
• 새로운 레코드가 삽입되거나 삭제되면, 해당 변경 사항을 반영하기 위해 인덱스도 갱신하고 이러한 갱신 작업은 시간과 저장 공간을 소비

 

4. 멀티레벨 인덱싱

• 대용량의 데이터베이스에서는 멀티레벨 인덱싱 기법이 사용
• 멀티레벨 인덱싱은 대규모 데이터셋에서 검색 속도를 향상시키기 위해 사용
• 여러 수준의 인덱스가 생성되므로 전체 저장 공간 요구량이 증가

 

5. 세그먼트 및 페이지 할당

• 일부 DBMS에서는 세그먼트(segment) 및 페이지(page) 할당 방식으로 데이터와 관련된 메모리 영역을 관리
• 이 때문에 일부 공간은 실제 데이터보다 크게 할당될 수 있으며, 따라서 전체 디스크 공간 중 일부가 비어있게 됨

 

인덱스 스캔의 비효율적인 사용 예시

 

 1개의 데이터가 있는 테이블과 100만개의 데이터가 들어 있는 테이블이 있다고 하자. 100만 개의 데이터가 들어있는 테이블이라면 풀 스캔보다는 인덱스 스캔이 유리하겠지만, 1개의 데이터가 들어있는 테이블은 인덱스 스캔보다 풀 스캔이 더 빠르다.

• 데이터 양이 적을 경우 인덱스를 사용하는 것보다 인덱스 구조 자체에 엑세스 하는 오버헤드가 비용이 크기 때문
• 인덱스는 디스크 상에 저장되며, 디스크에서 데이터를 읽는 I/O 작업은 상대적으로 느릴 수 있음
• 조건 처리: 데이터가 적을 경우 조건 처리하는 시간에 풀스캔 하는 것이 빠름

 

성능이 저하되는 이유

 DBMS는 index를 항상 최신의 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있다. 그렇기 때문에 인덱스가 적용된 컬럼에 INSERT, UPDATE, DELETE가 수행된다면 각각 다음과 같은 연산을 추가적으로 해주어야 하며 그에 따른 오버헤드가 발생한다.

• INSERT: 새로운 데이터에 대한 인덱스를 추가
• DELETE: 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행
• UPDATE: 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스를 추가

 

인덱스를 사용하면 좋은 경우

• 규모가 작지 않은 테이블
• INSERT, UPDATE, DELETE가 자주 발생하지 않는 칼럼
• JOIN이나 WHERE 또는 ORDER BY에 자주 사용되는 칼럼
• 데이터의 중복도가 낮은 칼럼

 

인덱스를 설정하면 좋은 칼럼

• 인덱스는 한 테이블당 보통 3~5개 정도가 적당 (테이블의 목적 등에 따라 개수는 달라질 수  O)
• 인덱스는 컬럼을 정해서 설정하는 것이므로 후보 컬럼의 특징을 잘 파악해야 함

 

인덱스 설정 기준

아래 4가지 기준을 사용하면 효율적으로 인덱스를 설정할 수 있음

• 카디널리티 (Cardinality)
• 선택도 (Selectivity)
• 활용도
• 중복도

 

기준정도

카디널리티 (Cardinality)	높을수록 적합
선택도 (Selectivity)	낮을 수록 적합 (5~10% 적정)
활용도	높을 수록 적합
중복도	없을 수록 적합

 

 

카디널리티 (Cardinality)

카디널리티가 높을수록 인덱스 설정에 좋은 칼럼

 

 카디널리티는 칼럼에 사용되는 값의 다양성 정도를 나타내는 지표로서, 카디널리티가 높을수록 인덱스 설정에 좋다. 

 

예를 들어, 10개의 rows를 가지는 '학생' 테이블에 '학번'과 '이름' 컬럼이 있다고 가정

• '학번'은 학생마다 부여 받으므로 10개 값 모두 고유
  • 중복 정도가 낮으므로 카디널리티가 높다
• '이름'은 동명이인이 있을 수 있으므로 1~10개 사이의 값을 가짐
  • 중복 정도가 '학번'에 비해 높으므로 카디널리티가 높다고 표현할 수 있음

 

선택도 (Selectivity)

선택도가 낮을수록 인덱스 설정에 좋은 컬럼

• 5~10% 정도가 적당
• 데이터에서 특정 값을 얼마나 잘 선택할 수 있는지에 대한 지표

선택도는 아래와 같이 계산한다. 

= 컬럼의 특정 값의 row 수 / 테이블의 총 row 수 * 100
= 컬럼의 값들의 평균 row 수 / 테이블의 총 row 수 * 100

 

즉, 선택도는 특정 필드값을 지정했을 때 선택되는 레코드 수를 테이블 전체 레코드 수로 나눈 것이다.

 

예를 들어, 10개의 rows를 가지는 '학생'테이블에 '학번', '이름', '성별' 컬럼이 있다고 가정해보자.

 

 

학번은 고유하고, 이름은 2명씩 같고, 성별은 남녀 5:5 비율

• '학번'의 선택도 = 1/10*100 = 10%

SELECT COUNT(1) FROM '학생' WHERE '학번' = 1; (모두 고유하므로 특정 값: 1)

 

• '이름'의 선택도 = 2/10*100 = 20%

SELECT COUNT(1) FROM '학생' WHERE '이름' = "김철수; (2명씩 같으므로 특정 값: 2)

 

• '성별'의 선택도 = 5/10*100 = 50% 

SELECT COUNT(1) FROM '학생' WHERE '성별' = 'F'; (5명씩 같으므로 특정 값: 5)

 

활용도

활용도가 높을수록 인덱스 설정에 좋은 컬럼

• 해당 컬럼이 실제 작업에서 얼마나 활용되는지에 대한 값
• 수동 쿼리 조회, 로직과 서비스에서 쿼리를 날릴 때 WHERE절에 자주 활용되는지를 판단

 

중복도

중복도가 없을수록 인덱스 설정에 좋은 컬럼

 

 인덱스 성능에 대한 고려없이 마구잡이로 설정하거나, 다른 부서 다른 작업자의 분리된 요청으로 같은 컬럼에 대한 인덱스가 중복으로 생성된 경우를 볼 수 있다. 인덱스도 속성을 가지며 테이블 형태로 생성되므로, 속성을 컬럼으로 관리한다.

 

주요 인덱스 컬럼

• Table: The name of the table.
• Non_unique: 0 if the index cannot contain duplicates, 1 if it can.
• Key_name: The name of the index. If the index is the primary key, the name is always PRIMARY.
• Seq_in_index: The column sequence number in the index, starting with 1.
• Column_name: The column name.
• Collation: How the column is sorted in the index. This can have values A (ascending) or NULL (not sorted).
• Cardinality: An estimate of the number of unique values in the index.
• Index_type: The index method used (BTREE, FULLTEXT, HASH, RTREE)

출처: https://www.fun-coding.org/mysql_advanced5.html

 

 이 속성이 다를 때 같은 컬럼에 대해 중복으로 인덱스 설정이 가능해진다. 같은 컬럼에 대해 중복 인덱스가 설정되어 있다고 하더라도 SQL 자체 연산이 빠른 쪽으로 데이터를 조회한다.

 

인덱스의 자료구조

1. 해시테이블
2. B+Tree