[테코톡] DB Index 1

---

최신순

https://youtu.be/edpYzFgHbqs - 라라제로, 자막o

https://youtu.be/P5SZaTQnVCA - 찰리, 자막o

https://youtu.be/9ZXIoh9PtwY - 레베카, 자막o

https://youtu.be/NkZ6r6z2pBg - 안돌, 클러스티드 인덱스 & 넌 클러스티드 인덱스

 

---

https://youtu.be/edpYzFgHbqs - 라라제로

 

MySQL InnoDB(MySQL의 Storage Engine의 하나) 환경에서 발표

기본적인 데이터베이스 문법을 학습한 개발자, 인덱스를 데이터베이스에 적용하려는 개발자 에게 도움되는 발표

 

---

목차

 

---

인덱스? = 색인 = 쉽게 찾아볼 수 있도록 일정한 순서에 따라 놓은 목록

 

자바의 정석 책에서 다형성을 공부했는데.. 어디서 봤는지 기억이 안나

처음부터 찾아보려니까 책의 양이 많아서 찾기 어려워

그래서 ㄱㄴㄷ 순으로 정렬된 찾아보기 페이지에 간다.

ㄷ-ㄹ에 가서 다형성을 찾게 된다. 354p로 가게 된다.

이런식으로 원하는 값을 빠르게 찾는 데 초점이 있다

 

이를 데이터베이스에도 적용하면 어떻게 될까?

일단 찾는다는 것은 CRUD중에 SELECT에 활용할 수 있다

 

---

DB INDEX란?

이름, 성별, 이메일로 된 100만건 이상 데이터가 있다고 하자.

인덱스 기준이 하나도 잡혀 있지 않아.

내가 이메일이 ~~인 회원을 조회하려고 하면, 전체데이터에서 순차적으로 확인한다

 

왜냐?

현재 데이터는 기준 없이 저장된 상태다  ▽인덱스는 미리 정렬 해놓느냐 그런거네

데이터가 특정 기준으로 정렬돼 있다면 검색을 빠르게 할 수 있어

 

이메일을 인덱스로 정한다면?

그럼 현재 데이터가 이메일로 정렬된 백만건의 데이터로 바뀐다

이때 이메일을 통해 조회하게 되면 속도가 훨씬 빨라진다,

where절에 인덱스가 적용된 대상을 쓰는 것

 

인덱스가 적용이 됐다고 해도 SELECT * FROM TABLE; 이런건 WHERE절을 통해 검색을 하지 않았기 때문에 인덱스가 사용되지 않는다.

 

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

 

---

인덱스의 특징

2.인덱스도 하나의 객체 3.이기 때문에 저장공간이 필요

 

---

인덱스 알고리즘

 

*페이지 : 데이터가 저장되는 단위, MySQL에서는 16Kbyte. 데이터베이스 환경마다 다를 수 있다

 

(1)Full Table Scan

페이지 3개가 있고 값이 이렇게 있다

Full Table Scan을 이용하면 그냥 페이지1의 위에서 아래로 -> 페이지 2의 위에서 아래로 -> 페이지 3...

이렇게 순차적으로 찾는다

PPP를 찾으려면 저 번호 순서대로 찾게되고, 검증과정으로 총 3개의 페이지와 12번의 검색이 있었다.

 

Full Table Scan은 순차적으로 접근하기 때문에 접근 비용이 감소한다.

 

언제 쓸까?

인덱스가 적용됐더라도 처리 범위가 너무 넓은 경우

데이터베이스가 인덱스를 적용해도 성능상의 이슈가 별로 이점이 없다고 판단했을 때  ▽알아서 DBMS가 판단한다는거지 이게

 

---

(2)B-Tree

그 전에 Binary Search Tree를 설명

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

이진탐색트리가 균형이 있을 때 없을 때 검색시간복잡도가 이렇게 차이가 난다(최악의 경우)

이 BST의 단점을 극복 하기 위해 여러 자료 구조가 나왔고, 그 중 하나가 B-Tree

B-Tree가 InnoDB 기본인덱스의 구조다

 

---

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

루트 페이지, 브랜치 페이지는 자식 페이지의 정보를 갖고 있다

루트 페이지는 최상단에 위치, 브랜치 페이지는 루트 페이지와 리프 페이지와 사이, 여러개가 올 수 있다

▽데이터가 많으면 이 브랜치 페이지가 많아지겠지??

리프 페이지는 최하단. 실제 데이터 페이지나 실제 데이터의 주소 페이지가 올 수 있다(클, 논클 차이가 이것. 뒤에서 더)

 

---

아까의 FTS적용했던 예시에서 B-tree를 적용해보기

루트페이지와 리프 페이지가 연결된다

 

PPP를 찾는다면,

ABC 순서대로 정렬돼 있는 상황이라서

루트페이지부터 찾는다. P는 L다음에 있기 때문에, 루트의 L의 연결

이 순서로 찾는다.

3개 페이지 12번 검색 -> 2개 페이지 7번 검색 : SELECT의 성능이 향상됐다.

 

INSERT, DELETE, UPDATE의 경우는 어떨까?

 

---

INSERT

▽어떤 구조를 유지하고 있어야 하기 때문에, 집어넣을 때도 그냥 집어넣는게 아니므로 비용이 발생하겠지?

OOO를 삽입하고 싶어

O는 N과 P사이에 있기 때문에 그 사이에 들어가고, 이동이 있어도 페이비 내부에서 작업돼서 큰 부담이 없다.

 

근데 여기서 ZZZ를 또 삽입하면?

페이지가 꽉 차있기 때문에 ZZZ를 삽입할 수 없다

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

이는 데이터베이스에 담이 되는 작업이다. 이를 페이지 분할이라고 한다.

 

---

DELETE

 

---

UPDATE

먼저DELETE를 통해 기존 값(인덱스)를 '사용 안함'표시하고 INSERT를 통해 변경된 값을 삽입하여 UPDATE를 진행

 

그럼 UPDATE, DELETE의 경우도 WHERE절을 사용할 때 빨라지지 않아?

조회 성능 향상

BUT 처리량 증가, 페이지 낭비, 인덱스 조각화..

 

---

 

---

인덱스 종류

클러스터?

그럼 데이터베이스에서 클러스터링이란?

인덱스는?

같은 무리의 인덱스??사전

다른 무리의 인덱스??찾아보기

▽어쨌든 데이터를 또 만드는거네?

 

---

근데, 우리도 모르는 새에 인덱스를 사용하고 있다.

이렇게만 해도 2개의 INDEX가 생성돼

PK

UNIQUE

이 두개의 제약조건이 INDEX를 만든다

 

---

클러스터링 인덱스

테이블을 제약조건없이 만들면 인덱스가 만들어지지 않아

여기에 순차적으로 데이터를 넣으면

데이터를 넣은 순서대로 데이터가 쌓여

이렇게

이 상태에서, id컬럼에 클러스터링 인덱스를 적용해보면

클러스터링 인덱스를 적용하는 법

방법 1. PK를 컬럼에 적용하거나,

방법 2. 한 컬럼에 NOT NULL + UNIQUE 제약 조건을 한번에 걸어주는 것.

 

---

인덱스를 구성하면

이게 정렬이 돼

그리고!!!!!!

정렬된 데이터를 기준으로 루트 페이지가 생성이 된다  ▽정렬도 되고 루트페이지도 생성이 되는거였구나

 

루트 페이지와 리프 페이지 구조는 앞에 봤던 B-Tree구조로 이뤄져 있다

1000, 1001 이런건 데이터 페이지의 주소를 의미하는데,

데이터 페이지란 실제 데이터가 저장되는 페이지. 인덱스에 관련된 그런게 아니라 모든 컬럼에 대한 실제 데이터를 다 담고 있는 페이지

클러스터링 인덱스를 적용한 id 컬럼

을 기준으로 데이터가 정렬이 돼 있고

데이터가 추가되거나 삭제돼도 이 정렬을 최신 상태로 유지하면서 데이터가 저장 돼 있다.

사전과 같은 역할을 한다!!

 

---

id가 7인 멤버를 조회한다면 

루트페이지에서 1, 5, 9니까 5과 9사이에 있으므로 5이후에 있겠구나 하고 1001페이지를 찾는다

그리고 1001 페이지에서 순차적으로 Id값 7을 찾아. 이게 클러스터링 인덱스의 데이터 조회법

 

---

클러스터링 인덱스의 특징

실제 데이터 자체가 정렬돼 있어서, 테이블당 한개만 존재할 수 있다.  ▽오!그렇겠네. 정렬을 걔 기준으로 하니까?

리프페이지 = 데이터페이지

PK를 걸든지 or UNIQUE+NOT NULL 하든지 - 두개 다 있으면 PK가 우선순위를 가진다

 

---

논-클러스터링 인덱스

똑같이 인덱스가 하나도 존재 하지 않는 테이블에서 시작

name컬럼에 논-클러스터링 인덱스를 적용해본다.

 

---

논-클러스터링 인덱스를 추가하는 방법은

1.한 컬럼에 UNIQUE 걸기

2.인덱스 자체를 생성할 수도 있다. 직접 생성하면 논-클러스터링 인덱스가 생성 돼

-UNIQUE INDEX : 중복허용x면서 인덱스 생성

-INDEX : 중복을 허용하는 인덱스 생성

 

---

인덱스를 구성하면 이러한 구조로 생성이 돼

 

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

그리고, 별도의 name에 대한 인덱스 페이지가 추가로 생성된다. 이 구조는 B-Tree구조로 (▽클러스터링 인덱스와)동일하게 이뤄져 있다. 여기서 리프 페이지를 살펴보면 name을 기준으로 정렬돼 있는 것을 볼 수 있다.(▽ㄱㄴㄷ순)

그리고

여기 1002 + #3은

1002은 실제 데이터 페이지의 주소이고

#3은 1002페이지의 세번째에 '도리'에 대한 데이터가 존재한다는 주소를 의미  ▽그러면 실제 데이터 페이지니까 레코드 전부를 갖고 있겠구나

 

책의 내용은 그대로 있고, 책 뒤에 책의 내용을 찾기 위해 도와주는 별도의 찾아보기 페이지와 같은 역할을 한다는게 이것  ▽찾아보기 페이지 자체는 정렬이 돼 있으니까

 

---

name이 '라라'인 멤버를 조회하면

1.name인덱스 페이지에서 '라라'를 검색해  ▽루트페이지부터 검색한다구~~

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

 

'제로'인 멤버는

이렇게

 

---

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

실제 데이터 페이지는 어떠한 변경도 없다

별도의 인덱스 페이지가 생성, 추가적인 공간을 필요로 하게 된다 - 추가만 하면 되기 때문에 몇 개든 존재할 수 있어

B-tree의 리프페이지에는 실제 데이터 페이지의 주소를 담고 있다

한 컬럼에 unique제약 조건을 적용하면 생성할 수 있어

직접 인덱스를 생성하는 것도 논클러스터링으로 생성된다

 

---

함께하면?

id엔 클러스터링 인덱스

name엔 논-클러스터링 인덱스

 

▽먼저 내가 생각을 해봐봐봐봐봐봐봐 그리고 들어가

클러스터링 인덱스 = 데이터 페이지가 정렬 + 이것에 대해 루트페이지가 생성

논 클러스터링 인덱스 = 루트페이지, 리프페이지가 생성(클러스터링 인덱스에서 정렬된 실제 페이지 부분에 해당)이 되고, 그 리프페이지에는 실제 데이터에 대한 주소가 들어가 있음.

 

이 둘이 같이 되면 ?? id로 검색하면 클러스터링 타면 되고 name으로 검색하면 논클러스터링 타면 되는거 아닌가

 

---

이렇게 해보자

id컬럼의 클러스터링 인덱스는 이렇게 적용돼 있겠고,

라라 옆에 데이터 페이지의 주소가 존재하는, 이런식으로 데이터를 줘야 하는거 아닌가?생각이 돼

 

근데 실제로는

이렇게 존재해  ▽클러스터링 인덱스가 존재하면, 논클러스터링 인덱스 페이지에는 데이터 페이지의 주소대신 PK컬럼의 값이 들어가는구나

 

클러스터링 인덱스가 적용된 id 컬럼 값이 들어가 있어

 

이때 name = '라라' 를 조회해보면

name인덱스 페이지에서 라라를 검색하면 12라는 값이 들어있어

그 12라는 id값을 id인덱스 페이지에서 탐색해서 조회한다  ▽이게 더 낫나보지? -> 밑에 이유 나옴

 

---

왜 그럴까? PK의 값이 아닌 우리의 예상처럼 데이터 주소로 돼 있으면 문제가 있어

여기서, id가 3인 파랑의 데이터가 추가로 삽입됐다고 해보자.

id를 기준으로 정렬돼야 하기 때문에, 파랑의 데이터는 제로 밑에 들어간다

 

그럼 그 밑에 4, 5는 페이지 분할이 발생하겠지??

그럼 그때 name인덱스 페이지에서 주소가 변경돼

데이터가 추가되거나, 삭제될 때마다 이 인덱스 페이지들의 주소들을 계속해서 변경해야 하는 영향을 줘서, id로 하면 인덱스 페이지에 영향을 주지 않는다. id가 직접 변경되지 않는 한!

 

---

함께 쓰더라도 앞에서 살펴봤던 특징들은 거의 동일

단하나 바뀌는게 인덱스의 리프페이지에 클러스터링 인덱스가 적용된 컬럼의 실제 값이 존재한다는 것

 

---

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

그래서 나온 개념이 Cardinality

 

---

정답은 중복 수치가 낮은 것

 

---

성별과 그룹은 데이터가 두개밖에 없고 중복이 많아 Cardinality가 굉장히 낮다

그럼 이런 애들이겠지

 

---

또? 조건절이 없다면 index가 사용되지 않으므로 where, join, order by절이 자주 사용되는 컬럼에 적용하는것이 효과적

CUD가 자주 발생하지 않는 컬럼

규모가 작은 테이블에는 인덱스를 생성해도 효과가 미미해서 규모가 큰 것에 적용하는 것이 효과가 좋다

 

---

실습

(1)인덱스 조회해보기

이렇게 생성하고, 인덱스를 조회해보면

- Non_unique

PRIMARY와 UNIQUE로 걸려 있기 때문에 둘다 0

 

- Key_name

 

- Cardinality 수치도 확인할 수 있다

아무런 데이터가 없어서 0으로 나옴

 

- Index_type

현재 구조가 B-Tree구조인 것도 확인할 수 있다

 

---

성능 비교

단건으로만 봤을 땐 짧았다고 생각할 수도 있지만, 다른 로직을 위해 선행돼야 하는 로직이라면  전체 로직은 더 커질 수 있다.

엄청나게 줄어들었다.

 

---

그럼 index를 항상 사용하는 게 좋을까?

인덱스 사용시 주의사항

 

---

추가로 알면 좋은 지식

 

---

참고한 자료