[MySQL] Index

보라보라·2024년 2월 27일

Index(인덱스)

인덱스는 데이터베이스 쿼리의 성능을 언급하면서 빼놓을 수 없는 부분이다. 인덱스를 어떻게 설정하느냐에 따라 검색속도의 성능 향상을 볼 수 있기 때문이다.
이제 인덱스에 대해 살펴보고 인덱스가 어떻게 동작하는 원리도 알아볼 것이다.

Index 란?

동의어) 색인, 찾아보기
쉽게 찾아볼 수 있도록 일정한 순서에 따라 나열한 목록
-> 목적 : 원하는 값을 빠르게 찾는다!

예시) 책에서 '폭포수 모델'이라는 단어가 나온 부분을 찾아보세요.

책 자체 : Table (전체 데이터)

찾아보기, 색인 : Index (정렬)

페이지 : 실제 데이터의 주소 값

Database에서 Index란?

데이터베이스 테이블에 대한 검색 성능을 향상시키는 자료구조이며, WHERE 절 등을 통해 활용된다.
-> 목적 : 대용량 데이터에서 원하는 데이터를 빠르게 조회하기 위해
즉, WHERE절에 사용할 Column의 효율화이다.

Problem)
'학생' 테이블에 '학번', '이름', '나이', '전화번호' 컬럼이 있고, 인덱스는 '학번', '전화번호'에 걸려있습니다.
다음 중 인덱스의 영향을 받는 쿼리는?

1) SELECT '학번' FROM '학생';
2) SELECT '전화번호' FROM '학생' WHERE '이름' = '보라보라';
3) SELECT * FROM '학생' WHERE '학번' = 1;

인덱스가 왜 필요할까?

만약 주소가 '서울'인 음식점을 찾는 쿼리를 날린다고 하였을 때... DATABASE는 어떻게 데이터를 찾을까?

아래의 그림과 같이 Restaurant의 Table의 전체 데이터를 조회하면서 주소가 서울인 것을 찾게 된다.
이렇게 Table의 전체 데이터를 처음부터 끝까지 조회하여 데이터를 찾는방식을 Full Table Scan 이라고 합니다.

지금은 데이터의 개수가 12개 뿐인데....만약
1. 주소가 서울인 데이터가 id = 6이라면 그 데이터를 찾으면 조회(Scan)를 멈출까?
2. 데이터가 10만건 이상이라면?

이러한 경우 모든 데이터를 조회하기에는 속도 측면에서도 굉장히 느릴 것으로 보인다.

인덱스의 기준?

SELECT * FROM students WHERE phone = '01012345678';

인덱스가 적용된 대상을(phone으로 정렬된 데이터)
WHERE 절을 통해 검색

인덱스 기준이 하나도 잡혀 있지 않을 때

=> 전체 데이터에서 순차적으로 모두 확인해야하므로 느리다
이유 : 기준 없이 저장된 상태이므로

인덱스의 기준을 정한 경우

데이터가 특정 기준인 핸드폰번호로 정렬되어 있다면?
=> 빠르게 찾을 수 있다!

그렇다면 WHERE 절이 없는 경우?

SELECT * FROM students;

WHERE 절을 통해 검색 x
인덱스가 사용되지 않음

인덱스의 특징

인덱스는 항상 최신의 정렬상태를 유지
인덱스도 하나의 데이터베이스 객체
- 객체(MySQL) : 테이블, 뷰, 인덱스 등 데이터베이스 내에 정의해서 사용하는 실체를 가진 것
데이터베이스 크기의 약 10% 정도의 저장공간이 필요

장점

검색 속도가 무척 빨리질 수 있다.(단, 항상 그런 것은 아니다)
해당 쿼리의 부하가 줄어들어서 결국 시스템 전체의 성능이 향상된다.

단점

인텍스가 데이터베이스 공간을 차지해서 추가적인 공간이 필요해지는데, 대략 데이터베이스 크기의 10% 추가 공간이 필요하다.
처음 인덱스를 생성하는데 시간이 많이 소요될 수 있다.
데이터의 변경 잡업(Insert, Update, Delete)이 자주 일어날 경우에는 오히려 성능이 많이 나빠질 수도 있다.
필요없는 인덱스를 만들게 되면 데이터베이스가 차지하는 공간만 더 늘어나고 인덱스를 이용해서 데이터 베이스를 찾는 것이 전체 테이블을 찾아보는 것보다 훨씬 느려진다.

Index의 생성 / 삭제 / 확인

Index 생성

CREATE INDEX [인덱스명] ON [테이블명(컬럼명)];

CREATE INDEX idx_phone ON students(phone);

이 때, 컬럼명에 쉼표(,)를 사용하여 여러 컬럼을 가지는 인덱스를 생성할 수 있음

CREATE INDEX [인덱스명] ON [테이블명(컬럼명1, 컬럼명2, ...)];

Index 삭제

DROP INDEX [인덱스명] ON [테이블명];

DROP INDEX idx_phone ON students;

Index 정보 보기

SHOW INDEX FROM [테이블명];

SHOW INDEX FROM students;

결과

Table : 테이블의 이름을 표시함.
Non_unique : 인덱스가 중복된 값을 저장할 수 있으면 1, 저장할 수 없으면 0을 표시함.
Key_name : 인덱스의 이름을 표시하며, 인덱스가 해당 테이블의 기본 키라면 PRIMARY로 표시함.
Seq_in_index : 인덱스에서의 해당 필드의 순서를 표시함.
Column_name : 해당 필드의 이름을 표시함.
Collation : 인덱스에서 해당 필드가 정렬되는 방법을 표시함. A(ascending - 오름차순)
Cardinality : 인덱스에 저장된 유일한 값들의 수를 표시함.
Sub_part : 인덱스 접두어를 표시함.
Packed : 키가 압축되는(packed) 방법을 표시함.
Null : 해당 필드가 NULL을 저장할 수 있으면 YES를 표시하고, 저장할 수 없으면 ''를 표시함.
Index_type : 인덱스에 사용되는 메소드(method)를 표시함.
Comment : 해당 필드를 설명하는 것이 아닌 인덱스에 관한 기타 정보를 표시함.
Index_comment : 인덱스에 관한 모든 기타 정보를 표시함.

인덱스 알고리즘

용어 설명

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

Full Table Scan

PPP라는 데이터를 찾을 때

검증 과정

총 3개의 페이지 12번 검색

Full Table Sacn 특징

순차적으로 접근
접근 비용 감소

Full Table Sacn 사용

적용 가능한 인덱스가 없는 경우 - index 걸지 않을 때
인덱스 처리 범위가 넓은 경우
크기가 작은 테이블에 엑세스하는 경우
즉, 인덱스를 적용하여도 성능상 큰 이점이 발생하지 않을 때에 사용.

B-Tree (Balanced-Tree)

를 알아보기 전에 먼저 알아야할 용어

Binary Search Tree(이진 탐색 트리)

이진 탐색

연결리스트
둘의 장점을 합쳐서 만들어진 자료구조

B-Tree

이진 탐색 트리의 단점을 극복하기 위한 자료구조

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

용어
- 루트 페이지 : 최상단에 위치한 페이지 - 자식 페이지의 정보를 가짐
- 리프 페이지 : 실제 데이터 페이지(클러스트링 인덱스), 실제 데이터의 주소 페이지(논-클러스트링 인덱스)
- 브랜치 페이지 : 루트 페이지와 리프 페이지 사이에 있는 페이지, 여러개 올 수 있음 - 자식 페이지의 정보를 가짐

B-Tree의 데이터 조회 그림

검증과정 : 총 2개의 페이지 7번의 검색
기존 Full Table Scan에 비해 Select의 성능이 향상 되었음을 볼 수 있다.

B-Tree Insert

=> 페이지 분할이 일어날 수 있다.

아래 구조에서 OOO데이터 삽입시
아래 구조에서 ZZZ데이터 삽입시

페이지 분할

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

DB가 느려지고 성능에 영향을 줌

B-Tree Delete

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

B-Tree Update

Delete(기존 값 사용안함 표시)
Insert(변경된 값 삽입)

B-Tree의 정리

Select : 성능이 향상된다.
Insert, Update, Delete
- 페이지 분할과 사용안함 표시로 인덱스의 조각화가 심해져 성능이 저하된다.

인덱스 종류

[용어]

Cluster(클러스터)

무리, 군집

무리를 이루다

클러스터링

실제 데이터와 무리를 이룸

클러스터링 인덱스

실제 데이터와 같은 무리의 인덱스

예) 실제 데이터가 정렬된 사전

논-클러스터링 인덱스

(보조 인덱스, 세컨더리 인덱스)

실제 데이터와 다른 무리의 별도의 인덱스

예) 실제 데이터 탐색에 도움을 주는 별도의 찾아보기 페이지

놀라운 사실

우리도 모르는 사이에 인덱스를 사용하고 있었다...

Table에 primary Key와 unique Key의 제약조건을 사용하여 만들어 보자.

CREATE TABLE members (
    id int PRIMARY KEY,
    name varchar(255),
    email varchar(255) UNIQUE
);

다음 인덱스 정보를 확인해 보면 인덱스가 자동으로 2개나 생성되었다.

SHOW INDEX FROM members;

id : PrimaryKey 제약 조건을 걸으면 ---> 클러스터링 인덱스 자동으로 생성
email : UniqueKey 제약 조건을 걸으면 ---> 논-클러스터링 인덱스 자동으로 생성

예제를 통해 알아보는 클러스터링 인덱스

예제

인덱스가 없는 테이블에서

CREATE TABLE members2 (
     id int,
     name varchar(255),
     group_name varchar(255)
);

테이블에 id가 중구난방 데이터 집어 넣기
그럼 데이터가 집어넣은 순서대로 정렬되는 것을 볼 수 있다.

INSERT INTO members2 VALUES (6, '후니', 'BE'),
                            (9, '비녀', 'FE')
                            ...
                            (1, '승팡', 'BE'),
                            (5, '도리', 'FE'),
                            (10, '알렉스', 'BE');

제약 조건을 걸어보고 인덱스 확인하기.
a. PrimaryKey 제약조건 걸어보기

ALTER TABLE members2 ADD CONSTRAINT pk_id PRIMARY KEY (id);

b. NotNull Unique 제약조건 걸어보기

ALTER TABLE members2 MODIFY COLUMN id int NOT NULL;
ALTER TABLE members2 ADD CONSTRAINT unq_id UNIQUE (id);

적용 후 Table의 데이터를 확인하면 데이터가 정렬 된 것을 볼 수 있다.

클러스터링 인덱스를 적용한 구조 그림

클러스터링 인덱스를 적용 하기 전
id를 클러스터링 인덱스 적용하게 되면 id 컬럼을 기준으로 데이터가 정렬되면서 데이터 페이지가 구성됩니다.
정렬된 데이터를 기준으로 루트페이지가 생성된다. 그리고 B-Tree의 구조를 갖게된다.

루트페이지의 1000, 1001, 1002이라는 숫자는 자식 페이지(리프페이지)의 주소값을 가리킨다.
id 컬럼을 기준으로 데이터페이지의 값들이 실제로 정렬되어 있음.
중간에 데이터를 삽입이나 삭제를 하더라도 이 정렬을 최신상태로 유지하면서 데이터가 저장되어 있다.

클러스터링 인덱스가 적용된 조회

SELECT * FROM members2 WHERE id = 7;

클러스터링 인덱스의 특징

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

예제를 통해 알아보는 논-클러스터링 인덱스

예제

인덱스가 없는 테이블에서

CREATE TABLE members3 (
     id int,
     name varchar(255),
     group_name varchar(255)
);

테이블에 id가 중구난방 데이터 집어 넣기
그럼 데이터가 집어넣은 순서대로 정렬되는 것을 볼 수 있다.

INSERT INTO members3 VALUES (6, '후니', 'BE'),
                            ...
                            (1, '승팡', 'BE'),
                            (5, '도리', 'FE'),
                            (10, '알렉스', 'BE');

제약 조건을 걸어보고 인덱스 확인하기.
a. 해당 컬럼 Unique Key 제약조건
```
ALTER TABLE members3 ADD CONSTRAINT unq_name UNIQUE (name);
```
b. 해당 컬럼 UNIQUE INDEX 만들기
-> 중복을 허용하지 않는 인덱스를 생성하게 됨.
```
CREATE UNIQUE INDEX unq_inx_name ON members3 (name);
```
c. 해당 컬럼 INDEX 만들기
```
CREATE INDEX idx_name ON members3 (name);
```
적용 후 Table의 데이터를 확인하면 데이터는 정렬 되어있지 않다.

논-클러스터링 인덱스를 적용한 구조 그림

실제 데이터 페이지는 변경되지 않는다.
리프페이지외 데이터페이지는 같지 않고, 별도의 B-Tree 구조의 인덱스 페이지를 추가한다.
리프페이지는 현재 name 을 기준으로 정렬 되어 있음 (ㄱ,ㄴ,ㄷ...순)
리프페이지의 오른쪽 데이터는 (데이터페이지 주소 값 + #그 페이지 순서)를 저장한다.

논-클러스터링 인덱스가 적용된 조회

SELECT * FROM members3 WHERE name = '라라';

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

실제 데이터 페이지는 그대로
별도의 인덱스 페이지 생성 -> 추가 공간 필요
테이블당 여러 개 존재할 수 있음.
리프 페이지에 실제 데이터 페이지 주소를 담고 있음.
unique 제약조건 적용시 자동 생성
직접 index 생성시 논-클러시터링 인덱스 생성
Clustering index와 비교해서 조회 속도가 약간 느림
Clustering index보다 INSERT / UPDATE / DELETE 시 부하가 적음

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

그렇다면 클러스터링 인덱스와 논-클러스터링 인덱스가 혼합된 인덱스 구성은 어떻게 될까?

구조 그림

논-클러스터링인덱스의 리프페이지를 보면 데이터페이지의 주소값을 연결하고 있는 것이 아닌 id값이 할당 되어 있는 것을 볼 수 있다.

조회

SELECT * FROM members4 WHERE name = '라라';

근데 왜?

혼합되게 되면 논-클러스터링 인덱스의 리프페이지는 주소값을 저장하지 않고 id를 갖게 되었을까? 주소값일 때 문제점 때문이다.

아래는 주소값일 때의 그림이다.
근데 파랑이라는 데이터가 추가되는 상황일 때 변경되는 것들이 많아진다.
- 1000의 페이지 분할이 발생한다.
- 우선 파랑데이터 뒤에 올 호호와 스컬데이터의 순서가 밀리게 된다.
- 그럼 name 인덱스 페이지 에서도 주소값이 변경될 것이다.
- 또 페이지 분할에 따른 주소 값 변경이 계속 이루어 지게 될 것이다.
- 즉 주소값으로 하기엔 많은 변경이 필요하게 되어 인덱스 페이지에 영향을 줌.

그럼 어떤 컬럼에 인덱스를 적용하지?

카디널리티(Cardinality)

The number of elements in a set a group
그룹 내 요소의 개수

카디널리티 적용

카디널리티가 높은 컬럼에 적용해야 한다.
즉, 컬럼 값의 중복 수치가 낮은 것으로 정해야 된다.

인덱스 적용 기준

카디널리티가 높은 (중복도가 낮은) 컬럼
WHERE, JOIN, ORDER BY 절에 자주 사용되는 컬럼
- 인덱스는 추가 공간이 필요하다.
- 조건절이 없다면 인덱스가 사용되지 않는다.
INSERT / UPDATE / DELETE 가 자주 발생하지 않는 컬럼
데이터가 많은 큰 테이블

인덱스 사용시 주의사항

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

인덱스의 성능

인덱스가 걸리기 전과 후의 조회

인덱스가 걸리지 않았을 때 조회해도면 (0.35 s) 걸리던 쿼리가 인덱스 생성 후에 조회 속도가 확연하게 빨라졌다.
인덱스 생성 시에 시간이 소요되는 점도 체크하자!
데이터가 많으면 많을수록 처음에 인덱스 생성시 성능부분을 고려해야할 것이다.

인덱스가 걸린 컬럼과 아닌 컬럼의 조회

students 테이블에는 100만건의 데이터가 들어있다.
1. 위의 예제의 students 테이블에 phone에만 인덱스가 설정 되어 있다.
다른 컬럼인 id, name, age에는 이러한 속성이 걸려져 있지 않다.

같은 row를 조회하는데
인덱스가 걸려져 있지 않은 name으로 조회할 때와
인덱스가 걸려져 있는 phone으로 조회할 때의 시간을 비교해보면 상당히 차이가 난다.

물론, 이정도도 짧은 시간에 조회가 되었다고 생각할 수 있지만, 어떤 비즈니스 로직을 수행 할 때, 쿼리가 여러개 수행 된다고 해보자. 이것이 바로 사용자는 장애라고 느낄수도 있는 것이다.

결론

인덱스를 설정함으로써 내부적으로 어떻게 구조를 잡는 것을 알아 보았다.
또한, 어떤 컬럼을 인덱스로 설정하는지 우리가 테이블을 생성할 때 제약조건에 따라 인덱스의 종류가 나뉨을 알고 있다.
중요한 건 인덱스 사용시 이 인덱스가 얼마나 효과를 보는지에 대해 항상 성능 테스트를 해보고 유리한 쪽을 찾아 가야할 것 같다.

[출처]

[10분 테코톡] 찰리의 인덱싱 (https://www.youtube.com/watch?v=P5SZaTQnVCA)
[10분 테코톡] 라라, 제로의 데이터베이스 인덱스 : (https://www.youtube.com/watch?v=edpYzFgHbqs)

보라보라

쉽게쓰려고 노력하는 블로그

이전 포스트

[자바] Java Garbage Collection 알고리즘 종류

다음 포스트