[MySQL] 데이터 베이스 인덱스

데이터 베이스 인덱스란?

• 기준 없이 정렬된 경우 → 전체 데이터에서 순차적 확인 → 느리다
• 데이터가 특정 기준으로 정렬되어 있다면 검색을 빠르게 할 수 있다.
• 인덱스는 데이터베이스 테이블에 대한 검색 성능을 향상시키는 자료구조이며 WHERE 절 등을 통해 활용된다.

select * from member where email = 'ella@whatt.com'

 

인덱스 특징

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

 

페이지

• 데이터가 저장되는 단위

 

Full Table Scan

• 순차적으로 접근
• 적용 가능한 인덱스가 없는 경우
• 인덱스 처리 범위가 넓은 경우
• 크기가 작은 테이블에 엑세스 하는 경우 (데이터 베이스가 인덱스를 적용해도 성능 상 이점이 없다고 판단하였을 때) 사용

 

B-Tree (Balanced-Tree)

• 기본 데이터 베이스 인덱스 구조
• 루트 페이지를 찾고 → 리프 페이지를 찾는다.
• 루트페이지: 자식 페이지의 정보
• 브랜치 페이지: 자식 페이지의 정보
• 리프 페이지: 클러스터링 인덱스에서는 실제 데이터 페이지, 논-클러스터링 인덱스에서는 실제 데이터의 주소 페이지

 

페이지 분할

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

• DELETE
  • 인덱스의 데이터를 실제로 지우지 않고 사용안함 표시를 함
• UPDATE
  • delete → 기존 값 사용안함 표시
  • insert → 변경된 값 삽입
• Select 는 성능이 향상되고, Insert & Update & Delete는 페이지 분할과 사용 안함 표시로 인덱스의 조각화가 심해져 성능이 저하된다.

 

인덱스 종류

• 클러스터링 인덱스 → 실제 데이터와 같은 무리의 인덱스 (실제 데이터가 정렬된 사전)
• 논-클러스터링 인덱스 → 실제 데이터와 다른 무리의 별도의 인덱스 (실제 데이터 탐색에 도움을 주는 별도의 찾아보기 페이지)
• 놀라운 사실
  • 컬럼에 primary key 제약조건 → 클러스터링 인덱스 자동으로 생성
  • 컬럼에 unique 제약조건 → 논-클러스터링 인덱스 자동으로 생성

 

클러스터링 인덱스

• 추가 방법 1. 한 컬럼에 primary key 제약 조건을 거는 것

alter table member add constraint pk_id primary key (id);

 

• 추가 방법2. 한 컬럼에 not null, unique 제약 조건을 한 번에 거는 것

alter table member modify column id int not null;
alter table member add constraint nuq_id unique (id);

• 클러스터링 인덱스 특징
  • 실제 데이터 자체가 정렬
  • 테이블당 1개만 존재 가능
  • 리프 페이지가 데이터 페이지
  • 아래의 제약조건 시 자동 생성
    • primary key (우선 순위)
    • unique + not null

 

논-클러스터링 인덱스

• 추가 방법
  1. 한 컬럼에 unique 제약 조건을 거는 것
  2. 인덱스 테이블 자체를 생성
     1. unique → 중복을 허용하지 않으며 인덱스 생성
     2. default → 중복을 허용하는 인덱스를 생성

alter table member add constraint unq_name unique (name);
 
create uqique index unq_idx_name on member (name);
 
create index idx_name on member (name);

• 인덱스 구성
  • 별도의 인덱스 페이지 추가 : 루트 페이지, 리프 페이지 (인덱스, 주소 저장)
  • 실제 데이터가 저장된 곳 : 데이터 페이지 (변경되지 않음)

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

 

다수의 인덱스 (클러스터링 + 논-클러스터링 인덱스)

id 컬럼에 클러스터링 인덱스 + name 컬럼에 논-클러스터링 인덱스

• 클러스터링 인덱스
  • 실제 데이터 자체가 정렬
  • 리프 페이지가 데이터 페이지
  • 테이블당 1개만 존재
  • primary key (우선 순위), unique + not null 제약조건 적용시 자동 생성
• 논-클러스터링 인덱스
  • 실제 데이터 페이지는 그대로
  • 별도의 인덱스 페이지 존재 (추가 공간 필요)
  • 테이블당 여러 개 존재
  • 리프 페이지에 주소가 아닌 클러스터링 인덱스가 적용된 컬럼의 실제 값 존재
  • unique 제약 조건 적용시 자동 생성
  • 직접 index 생성시 논-클러스터링 인덱스 생성

 

인덱스 적용 기준

• 카디널리티
  • 그룹 내 요소의 개수
  • 카디널리티가 높은 것 (=중복 수치가 낮은 것) 컬럼에 인덱스 적용
  • id, 이메일, 주민 번호
• Where, join, order by 절에 자수 사용되는 컬럼
  • 조건 절이 없다면 인덱스가 사용되지 않음
• insert, update, delete가 자주 발생하지 않는 컬럼
• 규모가 작지 않은 테이블

 

인덱스 사용시 주의사항

• 잘 활용되지 않는 인덱스는 과감히 제거하자
  • where절에 사용되더라도 자주 사용해야 가치가 있다
  • 불필요한 인덱스로 성능저하가 발생할 수 있다
• 데이터 중복도가 높은 컬럼은 인덱스 효과가 적다
• 자주 사용되더라도 insert/update/delete 가 자주 일어나는지 고려해야 한다
  • 조금 느린 쓰기를 감수하고 빠른 읽기를 선택하는 것도 하나의 방법이다.

---

Source

https://www.youtube.com/watch?v=edpYzFgHbqs