라라와 제로의 데이터베이스 인덱스를 보고 주관적으로 정리한 내용입니다.
이런 사람이 들으면 좋아요
- 기본적인 데이터베이스 문법을 학습한 개발자
- 인덱스를 데이터베이스에 적용하려는 개발자
목차
- 인덱스란?
- 인덱스 알고리즘
- Full Table Scan
- B-Tree
- 인덱스 종류
- 클러스터링 인덱스
- 논-클러스터링 인덱스
- 인덱스 적용 기준
- 카디널리티
- 실습
- 인덱스 조회
- 성능 비교
- 인덱스 사용 시 주의사항
인덱스란
사전적 정의
- 색인: 쉽게 찾아볼 수 있도록 일정한 순서에 따라 놓은 목록
- 원하는 값을 빠르게 찾음
인덱스 정의
이름, 성별, 이메일 등 백만 건 이상의 데이터가 인덱스 기준이 하나도 잡혀 있지 않은 경우
- 이메일이 test@gmail.com인 회원을 조회하면 전체 제이터에서 순차적으로 확인하기 때문에 매우 느림
- 데이터가 특정 기준으로 정렬되어 있다면 검색을 빠르게 할 수 있음
인덱스를 이메일로 정한 경우 (이메일로 정렬된 백만 건 이상의 데이터)
- test@gmail.com인 회원을 조회하면 매우 빠르게 찾을 수 있음
WHERE email = 'test@gmail.com'- 인덱스가 적용된 대상(email로 정렬된 데이터)을 WHERE절을 통해 검색
SELECT * FROM member- 인덱스가 적용이 된 상태여도 WHERE절을 통해 검색을 하지 않았기 때문에 인덱스가 사용되지 않음
- 즉, 인덱스는 데이터베이스 테이블에 대한 검색 성능을 향상 시키는 자료 구조이며 WHERE 절 등을 통해 활용됨
인덱스 특징
- 인덱스는 항상 최신의 정렬상태를 유지
- 인덱스도 하나의 데이터베이스 객체
- 데이터베이스 크기의 약 10% 정도의 저장공간 필요
인덱스 알고리즘
용어 설명
- 페이지: 데이터가 저장되는 단위 (mysql기준 16kbyte)
Full Table Scan
- 페이지 3개가 있고 PPP를 찾는다면 이런 순서로 찾음
Full Table Scan 특징
- 순차적으로 접근
- 접근 비용 감소
Full Table Scan 사용
- 적용 가능한 인덱스가 없는 경우
- 인덱스 처리 범위가 넓은 경우
- 크기가 작은 데이블에 엑세스 하는 경우
- 데이터베이스가 인덱스를 적용해도 성능 상의 이점이 별로 없다고 판단되는 경우
B-Tree
Binary Search Tree (이진 탐색 트리)
- 이진탐색
- 연결리스트
- 균형 있는 이진탐색트리의 시간 복잡도는 O(log n)
- 균형 없는 이진탐색트리의 시간 복잡도는 O(n) - 이진탐색트리의 장점을 살렸다고 볼 수 없음
- 이런 이진탐색트리의 단점을 극복하기 위해 나온 것이 B_Tree임
B-Tree(Balancde-Tree)
- 트리 높이가 같음
- 자식 노드를 2개 이상 가질 수 있음
- 기본 데이터베이스 인덱스 구조임
- Full Table Scan에 적용한 B-Tree를 적용한 예시
- 현재 ABC 순서대로 정렬되어 있고 PPP를 찾기 위해서 루트 페이지 -> LLL 페이지로 이동
- 이런 순서로 찾음
- 이를 통해 SELECT의 성능 향상을 알 수 있음
- 그렇다면 INSERT, DELETE, UPDATE의 경우에는 어떻게 될까?
INSERT로 인한 페이지 분할
- OOO를 삽입하려고 함
- OOO는 NNN과 PPP사이로 들어감
- 이 과정에서 PPP의 이동이 있었지만 페이지 내부에서 작업되었기 때문에 큰 부담은 없음
- 여기서 ZZZ를 삽입하려고 할 때 페이지가 가득찼기 때문에 삽입할 수 없음
- 이때 데이터베이스는 비어있는 페이지를 확보하고, 문제가 있는 페이지의 데이터를 공평하게 나누어 저장함
- 이를 페이지 분할이라고 하는데, 데이터베이스에 부담이 되는 작업임
페이지 분할
- 페이지에 새로운 데이터를 추가할 여유공간이 없어 페이지에 변화가 발생하는 것
- 데이터베이스가 느려지고 성능에 영향을 줌
DELETE
- 인덱스의 데이터를 실제로 지우지 않고 사용안함 표시를 함
UPDATE
- DELETE (기존 값 사용안함 표시)
- INSERT (변경된 값 삽입)
UPDATE,DELETE의 경우도 WHERE절을 사용할 때 빨라지지 않을까?
- WHERE절로 처리할 대상을 찾기 위한 조회 성능 향상
- 사용하지 않는 인덱스가 적용되었다면 불필요한 처리량 증가
- 사용안함 표시로 페이지 낭비 및 인덱스 조각화 심해짐
정리
- SELECT: 성능 향상
- INSERT, DELETE, UPDATE: 페이지 분할과 사용안함 표시로 인덱스의 조각화가 심해져 성능 저하
인덱스 종류
- 클러스터링 인덱스
- 논-클러스터링 인덱스 (=보조 인덱스, 세컨더리 인덱스)
클러스터란? (Cluster)
- 무리, 군집
- 무리를 이루다
데이터베이스에서 클러스터링이란?
- 클러스터링: 실제 데이터와 무리를 이룸
- 논-클러스터링: 실제 데이터와 무리를 이루지 않음
- 클러스터링 인덱스: 실제 데이터와 같은 무리의 인덱스 (ex. 실제 데이터가 정렬된 사전)
- 논-클러스터링 인덱스: 실제 데이터와 다른 무리의 별도의 인덱스 (ex.국어 사전의 찾아보기 페이지)
클러스터링 인덱스 vs 논-클러스터링 인덱스
CREATE TABLE member (
id int primary key,
name varchar(255),
email varchar(255) unique
)- 여기서 primary key를 적용하면 클러스터링 인덱스가 자동으로 생성
- unique 제약 조건을 걸게 되면 논-클러스터링 인덱스가 자동으로 생성
클러스터링 인덱스
인덱스가 없는 테이블
- 어떠한 제약 조건도 걸지 않았기 때문에 인덱스도 없음
- 이 테이블에는 id, name, group라는 컬럼이 존재
- 여기에 순차적으로 데이터를 넣었을 때
- 데이터를 넣은 순서대로 순차적으로 데이터가 쌓임
- id 컬럼에 클러스터링 인덱스를 적용하기 위해서는 2가지 방법이 있음
-- primary key를 id에 적용하는 방법
ALTER TABLE member
ADD CONSTRAINT pk_id PRIMARY KEY (id);
-- 한 컬럼에 not null과 unique 제약 조건을 한번에 걸어주는 방법
ALTER TABLE member MODIFY COLUMN id int NOT NULL;
ALTER TABLE member ADD CONSTRAINT nuq_id UNIQUE (id);인덱스 구성 후
- 정렬된 데이터를 기준으로 루트 페이지가 생성
- 루트 페이지와 리프 페이지는 앞서 보았던 B-Tree구조로 이루어져 있음
- 클러스터링 인덱스를 적용한 id칼럼을 기준으로 데이터가 정렬되어 있고, 만약 어떤 데이터가 추가되거나 삭제되더라도 이 정렬을 최신 상태로 유지하면서 데이터가 저장되어있음
- 여기서 id가 7인 멤버를 조회할 때
1. 루트 페이지에서 id 7은 5와 9사이에 있기 때문에 1001 페이지를 찾음- 1001 페이지에서 순차적으로 id를 찾아서 '후이'의 데이터를 찾음
특징
- 실제 데이터 자체가 정렬
- 테이블 당 1개만 존재 가능
- 리프 페이지가 데이터
- 아래의 제약 조건 시 자동 생성
- primary key (우선 순위)- unique + not null
논-클러스터링 인덱스
인덱스가 없는 테이블
- name 컬럼에 논-클러스터링 인덱스를 적용하는 방법
-- 한 컬럼에 unique 제약 조건 걸기
ALTER TABLE member
ADD CONSTRAINT unq_name UNIQUE (name);
-- 인덱스 자체를 생성하기
-- unique index를 생성하면 중복을 허용하지 않으며 인덱스를 생성
CREATE UNIQUE INDEX unq_idx_name
ON member (name);
-- default index를 생성하면 중복을 허용하는 인덱스가 생성
CREATE INDEX idx_name
ON member (name);
- 실제 데이터가 저장된 데이터 페이지는 어떠한 정렬이나 변경도 일어나지 않음
- 데이터 페이지 = 리프 페이지 인 클러스터링 인덱스와는 다르게 별도의 name에 대한 인덱스 페이지가 추가 생성 - B-Tree 구조로 동일하게 이루어져 있음
- 리프 페이지는 name을 기준으로 정렬되어 있음
- 도리 옆의 1002는 실제 데이터 페이지의 주소를 의미
- 3은 1002페이지의 세번째에 '도리'에 대한 데이터가 존재한다는 주소를 의미
- 여기서 name이 '라라'인 멤버를 조회할 때
1. name 인덱스 페이지에서 '라라' 탐색- 데이터 페이지 주소를 통해 실제 데이터 탐색
특징
- 실제 데이터 페이지는 그대로
- 별도의 인덱스 페이지 생성 -> 추가 공간 필요
- 테이블 당 여러 개 존재 가능
- 리프 페이지에 실제 데이터 페이지 주소를 담고 있음
- unique 제약조건 적용 시 자동 생성
- 직접 index 생성 시 논-클러스터링 인덱스 생성
다수의 인덱스
클러스터링 + 논-클러스터링 인덱스
- id 컬럼에 클러스터링 인덱스 + name 컬럼에 논-클러스터링 인덱스
- 전의 논-클러스터링 구성처럼 '라라'옆에 데이터 페이지의 주소가 존재해서 이런식으로 데이터를 줘야하지 않을까?
- 데이터 페이지의 주소 값이 아닌 클러스터링 인덱스가 적용된 id 컬럼 값이 들어있음
- 여기서 name이 '라라'인 멤버를 조회할 때
1. name 인덱스 페이지에서 '라라' 탐색- 12라는 id값을 id 인덱스 페이지에서 다시 탐색
데이터 페이지의 주소가 들어있지 않는 이유
- 데이터가 추가되거나 삭제될 때마다 인덱스 페이지들의 주소들을 계속해서 변경해야 하기 때문에 비효율적
클러스터링 인덱스의 특징과 논-클러스터링 인덱스의 특징을 합친 것과 같지만 리프 페이지에 실제 데이터 페이지 주소가 아닌 클러스터링 인덱스가 적용된 컬럼의 실제 값이 들어있다는 점이 다르다!
인덱스 적용 기준
카디널리티 (Cardinality)
사전적 의미
- the number of elements in a set or group
- 그룹 내 요소의 개수
적용
- id, 이름, 그룹, 이메일, 성별, 주민번호, 나이 7개의 컬럼이 있을 때, 어떤 컬럼에 인덱스를 적용해야 할까?
- 카디널리티(그룹 내 요소의 개수)가 높은 것 = 중복 수치가 낮은 것
- 그룹을 살펴보면 BE와 FE 중복이 굉장히 많음 = Cardinality가 굉장히 낮음
- 성별도 마찬가지
- id나 이메일, 주민번호가 중복이 적음 = Cardinality가 높음
- id, 이메일, 주민번호에 인덱스를 적용하는 것이 효율적
사용하면 좋은 경우
- 카디널리티가 높은 (=중복도가 낮은) 컬럼
- WHERE, JOIN, ORDER BY 절제 자주 사용되는 컬럼
- 인덱스는 추가 공간이 필요로 된다- 조건 절이 없다면 인덱스가 사용되지 않음
- INSERT / DELETE / UPDATE 가 자주 발생하지 않는 컬럼
- 규모가 작지 않은 테이블
인덱스 실습
인덱스 조회
CREATE TABLE member (
id int primary key,
email varchar(255) unique
)- 먼저 member 테이블을 생성하고 id는 pk, email 컬럼에는 unique 제약 조건을 검
- 인덱스를 조회하면 다음과 같이 나옴
- non-unique: 중복 값이 허용되지 않으면 0, 중복 값이 허용되면 1을 나타냄 -> 현재 pk와 unique로 걸려있기 때문에 둘 다 0으로 나타남
- key_name: 인덱스의 이름, 기본 키라면 primary를 표시
- cardinality: 그룹 내 요소의 개수, 현재 아무런 데이터가 없어서 0
- index_type: 인덱스 구조, 현재 B-Tree로 되어있음
성능 비교
인덱스 미적용
- 10만 건의 데이터에서 0.265초, 100만 건의 데이터에서 0.672초가 걸림
email 컬럼 인덱스 적용
- 10만 건에서 0.000, 100만 건에서 0.001초로 매우 빨라짐
인덱스 사용 시 주의사항
- 잘 활용되지 않는 인덱스는 과감히 제거하자
- WHERE 절에 사용되더라도 자주 사용해야 가치가 있음- 불필요한 인덱스로 성능 저하가 발생할 수 있음
- 데이터 중복도가 높은 컬럼은 인덱스 효과가 적음
- 자주 사용되더라도 INSERT / DELETE / UPDATE 가 자주 일어나는지 고려해야 함
- 일반적인 웹 서비스와 같은 온라인 트랜잭션 환경에서 쓰기와 읽기 비율은 2:8 또는 1:9임- 조금 느린 쓰기를 감수하고 빠른 읽기를 선택하는 것도 하나의 방법
추가로 알아보면 좋을 지식
- B+Tree
- 다중 컬럼 인덱스
- ANALYZE TABLE
- 실행 계획
- SQL 최적화
- 옵티마이저
https://youtu.be/edpYzFgHbqs [10분 테코톡] 라라, 제로의 데이터베이스 인덱스
