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

1. 인덱스란?

사전적 정의는 색인이다.

*색인 : 쉽게 찾아볼 수 있도록 일정한 순서에 따라 놓은 목록을 뜻합

 

인데스는 원하는 값을 빠르게 찾는다! 에 초점이 있다.

SELECT, INSERT, UPDATE, DELETE 중 '찾는다'에 해당하는 SELECT에 활용할 수 있을 것이다.

 

이메일로 정렬된 데이터 찾는 쿼리

    SELECT * FROM member

    WHERE email = 'xxx@xxxx'

        [1] 인덱스가 적용된 대상을(email로 정렬된 데이터)

        [2] WHERE 절을 통해 검색

 

    SELECT * FROM member

        [1] WHERE 절을 통해 검색x

        [2] 인덱스가 사용되지 않음

 

위 쿼리문은 WHERE 절로 검색하지 않아서 인덱스가 사용되지 않았다.

 

데이터베이스 인덱스란?

인덱스는 데이터베이스 테이블에 대한 검색 성능을 향상시키는 자료 구조이며 WHERE 절 등을 통해 활용된다.

 

- 인덱스 특징 -

1) 인덱스는 항상 최신의 정렬상태 유지

2) 인덱스도 하나의 데이터베이스 객체

3) 데이터베이스 크기의 약10% 정도의 저장공간 필요

 

 

2. 인덱스 알고리즘

페이지 : 데이터가 저장되는 단위(MySql의 경우 16 Kbyte)

 

1) Full Table Scan

 

특징

    [1] 순차적으로 접근

    [2] 접근 비용 감소

 

언제 사용할까?

    [1] 적용 가능한 인덱스가 없는 경우

    [2] 인덱스 처리 범위가 넓은 경우

    [3] 크기가 작은 테이블에 엑세스하는 경우

 

 

2) B-Tree (Balanced-Tree)

[참고]

Binary Search Tree(이진 탐색 트리)

이진탐색과 연결리스트의 장점이 합쳐저 만들어진 자료구조

검색 시간 복잡도가 최악의 경우 O(n)이 나오게 된다.

이 단점을 극복하기 위해 나온것 중 하나가 B-Tree이다. 

 

특징

    [1] 트리 높이가 같음

    [2] 자식 노드를 2개 이상 가질 수 있음

    [3] 기본 데이터베이스 인덱스 구조

 

 

인덱스에서 B-Tree를 예시로 적용

 

 

 

Full Table Scan에서 사용한 예시를 B-Tree 적용

 

이를 통해 'SELECT의 성능이 향상된 것을 알 수 있다.

 

 

INSERT

OOO를 삽입할 경우 PPP를 이동 시키고 NNN아래 삽입을 한다.

 

ZZZ을 삽일할 경우 페이지가 꽉 차있기 때문에 삽입할 수 없다.

데이터베이스는 비어있는 페이지를 확보하고 문제가 있는 페이지의 데이터를 공평하게 나눠서 저장한다.

이를 페이지 분할이라고 하는데 페이지에 새로운 데이터를 추가할 여유 공간이 없어 페이지에 변화가 발생하는 것을

말한다. 이는 데이터베이스에 부담이 되는 작업이다.

 

 

 

페이지 분할

    [1] 페이지에 새로운 데이터를 추가할 여유공간이 없어 페이지에 변화가 발생

    [2] DB가 느려지고 성능에 영향을 준다.

 

DELETE

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

 

UPDATE

[1] DELETE(기존 값 사용안함 표시)

[2] INSERT(변경된 값 삽입)

 

 

UPDATE, DELETE의 경우도 WHERE 절을 사용하면 빨라지지 않을까?WHERE 절로 처리할 대상을 찾기 위한 조회 성능은 향상된다.하지만 사용하지 않는 인덱스가 적용되었다면 불필요한 처리량이 증가하고사용안함 표시로 페이지 낭비 및 인덱스 조각화 심해질 수 있다.

 

 

 

3. 인덱스 종류

클러스터(Cluster)

    [1] 무리, 군집

    [2] 무리를 이루다

 

클러스터링	논-클러스터링
실제 데이터와 무리를 이룸	실제 데이터와 무리를 이루지 않음

클러스터링 인덱스	논-클러스터링 인덱스
실제 데이터와 같은 무리의 인덱스	실제 데이터와 다른 무리의 별도의 인덱스
실제 데이터가 정렬된 사전	실제 데이터 탐색에 도움을 주는 별도의 찾아보기 페이지

 

 

우리도 모르는 사이 데이터베이스 인덱스를 사용하고 있었다는 사실!

 

 

위와 같이 테이블을 생성만 해도 인덱스가 자동으로 생성된다.

 

여기서 총 몇 개의 인덱스가 생성되었을까?

 

 

한 컬럼에 pk를 적용하면 클러스터링 인덱스가 자동으로 생성되고

UNIQUE 제약 조건을 걸게 되면 논-클러스터링 인덱스가 자동으로 생성되어

총 2개의 인덱스가 생성된다.

 

 

1) 클러스터링 인덱스

클러스터링 인덱스 적용 방법

방법 1. PK 적용

    ALTER TABLE member

    ADD CONSTRAINT pk_id PRIMARY KEY (id);

 

방법2. NOT NUL과 UNIQUE 제약 조건을 한번에 적용

    ALTER TABLE member MODIFY COLUMN id int NOT NULL;

    ALTER TABLE member ADD CONSTRAINT nuq_id UNIQUE (id);

 

 

id 컬럼 기준으로 데이터가 정렬되고

정렬된 데이터를 기준으로 루트 페이지가 생성된다.

루트 페이지와 리프 페이지는 B-Tree 구조로 이루어져 있다.

1001 이런 숫자는 데이터 페이지의 주소를 의미한다.

*데이터 페이지 : 실제 데이터가 저장되는 페이지를 의미(모든 컬럼에 대한 실제 데이터를 다 담고 있는 페이지)

 

이 정렬을 최신 상태로 유지하면서 데이터가 저장되어 있다.

 

아이디가 7인 멤버를 조회한다면 루트 페이지에서 Id 7은 5와 9 사이에 있기 때문에 5 이후에 있다 생각하고 1001 페이지를 찾는다. 그리고 1001 페이지에서 순차적으로 Id 값 7을 찾아서 '후이'의 데이터를 찾는다.

 

클러스터링 인덱스 특징

[1] 실제 데이터 자체가 정렬

[2] 테이블당 1개만 존재 가능 (Id 칼럼을 기준으로 정렬되어 있었던 것 처럼 1개만 존재 가능)

[3] 리프 페이지가 데이터 페이지 (B-Tree의 리프 페이지가 데이터 페이지와 동일)

[4] 아래의 제약조선 시 자동 생성

        - primary key (둘다 있다면 PK가 우선)

        - unique + not null

 

 

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

논-클러스터링 인덱스 적용 방법

방법 1. 한 컬럼에 UNIQUE 제약 조건

    ALTER TABLE member

    ADD CONSTRAINT unq_name UNIQUE (name);

 

방법2. 인덱스 자체를 생성 (중복을 허용하지 않으며 인덱스 생성)

    CREATE UNIQUE INDEX unq_idx_name

    ON member (name);

 

방법3. 인덱스 자체를 생성 (중복을 허용하는 인덱스 생성)

    CREATE INDEX idx_name

    ON member (name);

 

이처럼 인덱스를 직접 생성하게 되면 논-클러스터링 인덱스가 생성된다.

위 방벙을 사용하여 논-클러스터링 인덱스를 구성하면 아래와 같다.

 

 

실제 데이터가 저장된 데이터 페이지는 어떠한 정렬이나 변경도 일어나지 않는다.

 

 

데이터 페이지가 리프 페이지였던 클라스터링 인덱스 와는 다르게
별도의 name에 대한 인덱스 페이지가 추가로 생성되었고 구조는 B-Tree 구조로 동일하게 이루어져 있다.
리프 페이지를 살펴보면 name을 기준으로 정렬되어 있는 것을 볼 수 있다.

 

 

1002는 실제 데이터 페이지의 주소를 의미하고

3은 1002 페이지의 세 번째에 '도리'에 대한 데이터가 존재한다는 주소를 의미한다.

 

 

name이 '라라'인 멤버를 조회한다면 name 인덱스 페이지에서 '라라'를 검색하고

여기서 얻은 데이터 페이지 주소를 통해 실제 데이터를 검색해서 '라라'에 대한 데이터를 탐색하게 된다.

 

논-클러스터링 인덱스 특징

    [1] 실제 데이터 페이지는 그대로

    [2] 별도의 인덱스 페이지 생성 -> 추가 공간 필요

    [3] (추가만 하면 되기 때문에) 테이블당 여러 개 존재

    [4] 리프 페이지에 실제 데이터 페이지 주소를 담고 있음

    [5] unique 제약조건 적용시 자동 생성

    [6] 직접 index 생성시 논-클러스터링 인덱스 생성

 

 

그렇다면 클러스터링 인덱스랑 논-클러스터링 인덱스를 함께 적용하면 어떻게 될까?
Id 컬럼에는 클러스터링 인덱스를 적용하고
name 컬럼에는 논-클러스터링 인덱스를 적용한다면 아래와 같을것으로 예상된다.

 

 

하지만 실제로는 아래와 같이 존재한다.

 

 

데이터 페이지의 주소 값이 아닌 바로 클러스터링 인덱스가 적용된 Id 컬럼 값이 들어있다.

그래서 '도리'의 id는 6이기 때문에 6을 담고 있고 '라라'는 12를 담고 있게 된다.

 

 

여기서 네임이 '라라'인 멤버를 조회한다면 name 인덱스 페이지에서 '라라'를 검색해서

12라는 id 값을 Id 인덱스 페이지에서 다시 탐색해서 데이터를 조회하게 된다.

예상했던 것처럼 왜 데이터 페이지의 주소가 들어있지 않았까?

이유는 데이터가 추가로 삽입됐을 경우 문제점이 있어서 이다.

 

 

id가 3인 '파랑'이 추가로 삽입됐다고 한다면 id를 기준으로 정렬되어야 하기 때문에
'파랑'의 데이터는 '제로' 밑에 들어와야한다.
그러면 '호호와 '스컬'은 4, 5번이기 때문에 페이지 분할이 발생하게 된다.
그렇게 되면 name 인덱스 페이지에서 '스컬'과 '호호'의 주소는 변경되어야 한다.

그렇기 때문에 데이터가 추가되거나 삭제될 때마다 이 인덱스 페이지들의 주소들을 계속해서 변경해야 하는 영향을 주게 된다.

이러한 이유때문에 id가 직접 변경되지 않는 한 인덱스 페이지에 영향을 주지 않는 방식으로 구성하고 있다.

 

클러스터링과 논-클러스터링 인덱스를 함께 사용하더라도 앞서 살펴보았던 특징 중
논-클러스터링 인덱스의 리프 페이지에 실제 데이터 페이지 주소가 아닌 클러스터링 인덱스가 적용된 컬럼의 실제 값이 존재한다는 것이 다르다.

클러스터링 인덱스 특징	논-클러스터링 인덱스 특징
[1] 실제 데이터 자체가 정렬 [2] 테이블당 1개만 존재 가능 [3] 리프 페이지가 데이터 페이지 [4] 아래의 제약조선 시 자동 생성         - primary key (둘다 있다면 PK가 우선)         - unique + not null	[1] 실제 데이터 페이지는 그대로 [2] 별도의 인덱스 페이지 생성 -> 추가 공간 필요 [3] 테이블당 여러 개 존재 [4] 리프 페이지에 실제 데이터 페이지 주소를 담고 있음                    클러스터링 인덱스가 적용된 컴럼의 실제 값 [5] unique 제약조건 적용시 자동 생성 [6] 직접 index 생성시 논-클러스터링 인덱스 생성

 

 

그렇다면 어떤 컬럼에 인덱스를 적용해야 할까?

그래서 나온 개념이 카디널리티이다.

 

 

4, 인덱스 적용 기준

1) 카디널리티

the number of elements in a set or group

그룹 내 요소의 개수

 

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

카디널리티(그룹 내 요소의 개수)가 높은 것

                = 중복 수치(중복도)가 낮은 것

 

위 예시에서는 id, 이메일, 주민번호 가 해당된다.

 

인덱스 적용 기준

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

 

 

5. 실습

1) 인덱스 조회

 

 

Cardinality는 현재 아무런 데이터가 없기 때문에 0으로 나와있다.

 

 

2) 성능 비교

 

단건으로만 봤을 때는 짧다고 생각할 수 있지만
만약 다른 로직을 위해서 선행되어야 하는 로직이라면 전체 로직은 더 커질 수 있다.

 

 

인덱스 적용 시 시간이 많이 줄은 것을 확인할 수 있다.

 

그렇다면 인덱스를 항상 적용하는 것이 좋을까?

 

 

6. 인덱스 사용시 주의사항

    [1] 잘 활용되지 않는 인덱스는 과감히 제거하자
         - WHERE 절에 사용되더라도 자주 사용해야 가치가 있다
        - 불필요한 인덱스로 성능저하가 발생할 수 있다
    [2] 데이터 중복도가 높은 컬럼은 인덱스 효과가 적다
    [3] 자주 사용되더라도 INSERT / UPDATE / DELETE 가 자주 일어나는지 고려해야 한다
         - 일반적인 웹 서비스와 같은 온라인 트랜잭션 환경에서 쓰기와 읽기 비율은 2:8 또는 1:9이다
         - 조금 느린 쓰기를 감수하고 빠른 읽기를 선택하는 것도 하나의 방법이다

---

참고

https://www.youtube.com/watch?v=edpYzFgHbqs&ab_channel=%EC%9A%B0%EC%95%84%ED%95%9C%ED%85%8C%ED%81%AC