1️⃣   데이터베이스 인덱스란?

1.1 정의

• 데이터 베이스 테이블에 대한 검색 성능을 향상시키는 자료 구조이며 WHERE 절 등을 통해 활용된다.
• 데이터가 특정 기준으로 정렬되어 있다면 검색을 빠르게 할 수 있다.

1.2 특징

1. 인덱스는 항상 최신의 정렬 상태를 유지
2. 인덱스도 하나의 데이터베이스 객체
3. 데이터베이스 크기의 약 10% 정도의 저장 공간 필요

2️⃣   인덱스 알고리즘

• 페이지 : 데이터가 저장되는 단위(16 Kbyte)

2.1 Full Table Scan

• 특징
  • 순차적으로 접근
  • 접근 비용 감소
• 사용
  • 적용 가능한 인덱스가 없는 경우
  • 인덱스 처리 범위가 넓은 경우
  • 크기가 작은 테이블에 엑세스하는 경우

2.2 B-Tree (Balanced-Tree)

• 트리 높이가 같음
• 자식 노드를 2개 이상 가질 수 있음
• 기본 데이터베이스 인덱스 구조

2.3 INSERT로 인한 페이지 분할

• 하나 더 추가하는 경우

2.3.1 페이지 분할

1. 페이지에 새로운 데이터를 추가할 여유공간이 없어 페이지에 변화가 발생
2. DB가 느려지고 성능에 영향을 준다.

2.4 DELETE

인덱스의 데이터를 실제로 지우지 않고 사용안함 표시를 한다.

2.5 UPDATE

1. DELETE (기존 값 사용안함 표시)
2. INSERT (변경된 값 삽입)

2.6 UPDATE, DELETE의 경우도 WHERE 절을 사용할 때 빨라지지 않을까?

2.7 정리

3️⃣   인덱스 종류

1. 클러스터링 인덱스
2. 논-클로스터링 인덱스(보조 인덱스, 세컨더리 인덱스)

3.1 클러스터란?

: 무리, 군집

3.2 클러스터링 인덱스

• 리프 페이지 == 데이터 페이지(실제 데이터가 저장된 곳)

특징

1. 실제 데이터 자체가 정렬
2. 테이블당 1개만 존재 가능
3. 리프 페이지가 데이터 페이지
4. 아래의 제약조건 시 자동 생성
   • primary key (우선순위)
   • unique + not null

3.3 논-클러스터링 인덱스

특징

1. 실제 데이터 페이지는 그대로
2. 별도의 인덱스 페이지 생성 → 추가 공간 필요
3. 테이블당 여러 개 존재
4. 리프 페이지에 실제 데이터 페이지 주소를 담고 잇음
5. unique 제약 조건 적용시 자동 생성
6. 직접 index 생성 시 논-클러스터링 인덱스 생성

3.4 다수의 인덱스

: 클러스터링 + 논-클러스터링 인덱스

예상

• 데이터 추가 or 삭제 시 페이지 분할 발생 → 논-클러스터링 주소값 변경이 일어난다.
• 그렇기 때문에 위와 같은 방식으로 동작하지 않는다.

3.5 실제

특징

4️⃣   인덱스 적용 기준

4.1 카디널리티(Cardinality)

: 그룹 내 요소의 개수

어떤 컬럼에 인덱스를 적용해야 할까

: 카디널리티(그룹 내 요소의 개수)가 높은 것 == 중복 수치가 낮은 것

• 아래 예시 경우

: id, 이메일

4.2 사용하면 좋은 경우

1. 카디널리티가 높은 (중복도가 낮은) 컬럼
2. WHERE, JOIN, ORDER BY 절에 자주 사용되는 컬럼
   • 인덱스는 추가 공간이 필요로 된다.
   • 조건 절이 없다면 인덱스가 사용되지 않는다.
3. INSERT / UPDATE / DELETE 가 자주 발생하지 않는 컬럼
4. 규모가 작지 않은 테이블

5️⃣   인덱스 실습

5.1 인덱스 조회

• Non_unique
  • 중복 값이 허용되지 않으면 0
  • 중복 값이 허용되면 1
• Key_name
  • 인덱스의 이름, 기본 키라면 PRIMARY 표시

5.2 성능

6️⃣   인덱스 사용 시 주의사항

1. 잘 활용되지 않는 인덱스는 과감히 제거하자

- WHERE 절에 사용되더라도 자주 사용해야 가치가 있다.
- 불필요한 인덱스로 성능저하가 발생할 수 있다.

2. 데이터 중복도가 높은 컬럼은 인덱스 효과가 적다

3. 자주 사용되더라도 INSERT / UPDATE / DELETE 가 자주 일어나는지 고려해야 한다.

- 일반적인 웹 서비스와 같은 온라인 트랜잭션 화경에서 쓰기와 읽기의 비율은 2:8 또는 1:9이다.
- 조금 느린 쓰기를 감수하고 빠른 읽기를 선택하는 것도 하나의 방법이다.

7️⃣   추가로 알아보면 좋을 지식

• B+Tree
• 다중 컬럼 인덱스
• ANALYZE TABLE
• 실행계획
• SQL 최적화
• 옵티마이저

📌 출처

• [10분 테코톡] 라라, 제로의 데이터베이스 인덱스