[혼자 공부하는 SQL] 인덱스의 내부 작동 원리와 구조, 인덱스에서 데이터 검색하기

안녕하세요, 혼자 공부하는 SQL를 보고 학습한 자료를 남깁니다.

 

트리구조

인덱스의 내부 작동 원리

노드는 데이터가 저장되는 공간들이다.

루트와 리프노드(잎사귀)가 달려있다 이렇게 보면 된다.

이 노드를 MySQL에서는 페이지라고 부른다.

한 페이지에 그림에서는 4개의 데이터를 저장할 수 있는 것이다.

리프 페이지 - Full Table Scan

3페이지짜리 책인데, 찾아보기가 없다는 것이다.

그래서 첫 페이지를 다 찾아봐요, 그리고 두 번째 페이지를 다 찾아봐요. 이렇게 8번만에 MMM이라는 데이터를 찾아낸 것이다.

중요한 것은 몇 페이지를 읽었냐는 것이다.

인덱스가 없이 생성된 테이블에서 MMM을 조회 시 3페이지를 읽게 된다.

이를 전체 테이블 검색(Full Table Scan)을 했다고 한다.

이것이 굉장히 안 좋은 것이다.

만약 1,000만 페이지짜리 책이다. 그러면 한 건의 데이터를 찾기 위해서 1,000만건의 데이터를 뒤졌다.

엄청나게 오래 걸리는 작업이다.

예를 들어 카카오톡은 이용자수가 5,000만명이 넘는다.

그런데 대부분의 사람들이 동시에 접속을 할텐데 

그러면 각 사용자가 5,000만건의 데이터를 찾는 일이 어마어마하게 시간이 걸리게 된다.

루트 페이지

그래서 루트 페이지를 추가를 하면

새로운 데이터를 추가하는 것은 아니다.

루트 페이지에 각각의 페이지의 맨 위의 것들만 가져오는 것이다.

이 상태에서 다시 MMM을 찾으면

무조건 먼저 루트 페이지를 찾게 된다. 

루트 페이지에 있는 데이터는 진짜 데이터가 아니라 찾아보기 데이터이다.

그래서 LLL을 찾고, 어디에 MMM이 있을지 예측을 하게 된다.

그래서 3페이지에서 찾아서 MMM을 찾은 것이다.

결국 1페이지만에 MMM을 찾게 된 것이다.

인덱스가 없었을 때는 3페이지가 걸렸다.

적은 건수만 찾아서도 데이터를 찾아냈다.

데이터를 찾는 결과는 똑같다. 결과가 바뀌는 것은 아니지만 빠르게 찾느냐 느리게 찾느냐 그 차이만 기억하면 된다.

균형 트리의 페이지 분할

결론은 SELECT는 빨라져요. 그러나 INSERT, UPDATE, DELETE 작업 시 느려질 수 있다.

그 이유가 '페이지 분할' 때문이다.

현재 그림은 인덱스가 만들어진 상태다.

INSERT로 III 데이터를 입력 시 

JJJ 데이터를 아래로 한칸 내리고

그 위에 III 데이터를 INSERT를 시킨 것이다.

하지만 크게 문제가 되는 것은 아니다.

왜냐 하면 리프 페이지에 한칸의 빈 공간이 있었기 때문이다.

하지만,

이번에는 GGG 라는 데이터를 입력해보겠다.

먼저 루트 페이지에 GGG라는 데이터가 입력되었다. 한칸의 여유가 있었기 때문에 순서에 맞게 데이터를 이동 시키고 입력 시키는 작업을 통해 입력되었다.

리프 페이지에서는 페이지 분할이 이루어져서 

새로운 리프 페이지가 만들어지고, GGG라는 데이터가 입력되었다.

원래는 3페이지였는데 변경되어서 총 4페이지가 만들어졌다.

이게 느려지는 작동이에요.

이번에는 더 느려지는 현상을 알아보겠다.

PPP, QQQ 데이터를 입력해보겠다.

루트 페이지에 공간이 가득 찼으므로 분할이 이루어져야 한다.

그래서 루트 페이지의 분할이 이루어져서

또 새로운 루트 페이지가 만들어지고

중간 페이지로 분할이 일어나는 것이다.

언제는 똑같이 한 건을 입력을 했는데 금방 되고, 언제는 엄청나게 오래 걸리는 경우가 발생을 한다.

페이지 분할이 여러 건 발생하는 현상이 있다.

INSERT 시 느려지는 경우를 개념적으로 알아보았다. 이유는 페이지 분할 때문이다.

인덱스의 구조

클러스터형 인덱스와 보조 인덱스 둘 다 SELECT 시 빠르다.

이번에는 두 인덱스의 종류 중 어떤 인덱스가 빠른지 알아보겠다.

클러스터형 인덱스의 속도

먼저 인덱스 없이 10건의 데이터를 INSERT 해보겠다.

 

그러면 다음과 같이 페이지로 구분 되어서 순서대로 데이터가 입력된 것을 볼 수 있다.

이 상태에서 클러스터형 인덱스를 지정해보겠다.

이번에는 클러스터형 인덱스를 생성하고 다시 SELECT를 해보겠다.

mem_id로 순서가 정렬되어서 데이터가 조회되는 것을 볼 수 있다.

내부 구조는 다음과 같다.

루트 페이지가 만들어진다.

그리고 리프 페이지(=데이터 페이지) 별로 가장 첫 번째 데이터가 루트 페이지에 저장이 된다.

보조 인덱스의 속도

보조 인덱스를 같이 알아보겠다.

table second를 생성해서 데이터를 마구잡이로 입력을 하고 

mem_id에 UNIQUE를 제약조건으로 추가하고

조회를 한 결과이다.

순서에는 그리고 내용에는 전혀 변함이 없다.

내부 구조를 살펴보겠다.

책 뒤에 찾아보기가 따로 만들어진 것을 볼 수가 있다.

인덱스 페이지가 만들어지고 

리프페이지에 실제 데이터 페이지의 데이터의 위치를 #을 통해서 담고 있는 것을 볼 수 있다.

이렇게 데이터는 그대로 있고, 찾아보기가 만들어진다.

두 인덱스의 비교

1) 클러스터형 인덱스로 SELECT 시 데이터를 찾는 방법

SPC 데이터를 찾아보겠다.

클러스터형 인덱스는 내용 자체도 인덱스라고 생각하면 된다. 전체가 인덱스다.

클러스터형 인덱스로 SPC를 찾는데 몇 페이지를 읽었는가

루프 페이지 그리고 1001페이지, 이렇게 총 2개의 페이지를 읽어서 데이터를 찾은 것을 볼 수 있다.

2) 보조 인덱스로 SELECT 시 데이터를 찾는 방법

총 3개의 페이지를 읽고 SPC 데이터를 찾은 것을 볼 수 있다.

 

결론적으로, SPC 데이터를 찾았다는 결과는 똑같다. 하지만 읽은 페이지 수를 보면 

클러스터형 인덱스가 조금 더 효율적이다 라고 볼 수 있다.

 

실무에서는 300만 페이지일 수도 있다.

그런데 인덱스를 통해서 20만 페이지로 데이터를 찾아냈다.

이렇게 데이터를 효율적으로 찾아야 한다.

 

감사합니다.

https://www.youtube.com/watch?v=vWTDuoSG-YQ&list=PLVsNizTWUw7GCfy5RH27cQL5MeKYnl8Pm&index=17