[MySQL] 8. 인덱스 (1) - Primary Key 클러스터링 인덱스

(B Tree 자료구조에 대해서는 설명하지 않습니다)

 

MySQL의 InnoDB는 Primary Key 클러스터링 인덱싱이 기본이다.

Primary Key 클러스터링 인덱싱이 있을 때와 없을 때를 비교해보고, 클러스터링 인덱싱이 어떤 효과를 발휘하는지 이해해보자.

 

1. Primary Key 클러스터링 인덱스 없는 경우 (세컨터리 인덱스도 X)

아래 그림을 보면 Primary Key인 ID 필드가 정렬되어있지 않다.

ID=1인 레코드를 저장한 다음, ID=4인 레코드를 저장한 다음, ID=3인 레코드를 저장한 모습이다.

 

 

이렇게 하나씩 레코드가 늘어나다보니

1억개가 저장되었다. (~라고 해보자.)

 

그리고 다음과 같은 쿼리를 실행해보자.

SELECT * FROM user WHERE id = 890;

 

만약 맨 위에서부터 내려가면서 레코드를 찾는다면

해당 데이터는 1억번 째에 있다.

 

MySQL의 한 페이지에 user 레코드가 500개까지 저장될 수 있다고 하자.

그러면 최소 1억 / 500 = 20만개의 페이지를 찾아봐야 해당 레코드를 발견할 수 있다.

따라서 디스크 I/O는 약 20만 회 일어나게 된다.

 

(버퍼 풀로 I/O 횟수를 줄이는 경우에 대해서는 이 글에서 고려하지 않을 것이다.

어차피 인덱스도 레코드도 버퍼풀에 올라갔다 내려갔다 하는거고... 다같이 생각하면 복잡하니 생각 안하겠음)

 

2. Primary Key 클러스터링 인덱스 있는 경우

이번에도 앞에서와 같이

ID=1인 레코드를 저장한 다음, ID=4인 레코드를 저장한 다음, ID=3인 레코드를 저장한 모습이다.

4번 먼저 저장하고나서 3번을 저장했는데, 디스크(=파일)에 쓰여진 데이터가 ID 순서대로 유지되었다.

(이 과정에서 정렬을 유지하는 데에 비용이 들었으리라.)

 

 

앞에서와 마찬가지로, MySQL의 한 페이지 단위에 user 레코드가 최대 500개 들어갈 수 있다고 하자.

 

위 그림의 경우 데이터가 3개밖에 없으므로 한 페이지에 들어가있다.

따라서 이 페이지가 인덱스의 루트노드이자 리프노드인 상태.

 

이제 또 다시 레코드가 늘어나서 1억개가 저장되었다고 해보자.

 

데이터파일을 보면 1억개의 데이터가 ID 순서대로 디스크에 쓰여있음을 볼 수 있다.

그리고 MySQL의 한 페이지에 500개의 레코드가 들어갈 수 있다고 하자.

그러면 디스크의 페이지 갯수는 1억 / 500 = 20만 개 이다.

 

인덱스에서 브랜치노드, 루트노드는

한 페이지에 1000개의 레코드를 가질 수 있다고 가정하겠다.

(리프노드와 달리, 브랜치노드와 루트노드는 NAME, AGE 필드가 없고 자식노드 필드가 있다.

따라서 row 한개의 크기가 더 작고, 이로인해 한 페이지에 더 많은 row가 들어갈 것이다.)

 

 

트리 높이 = 2

이제 PK로 데이터 한 개를 검색해보자.

그러면

루트 노드, 브랜치노드 중 1개, 리프노드 중 1개

이렇게 세 개의 페이지밖에 읽지 않는다.

이러면 디스크 I/O는 겨우 3번밖에 일어나지 않으므로 디스크 IO가 획기적으로 줄어들었다!

 

PK 클러스터링 인덱스의 장점과 단점

아래 표는 Real MySQL 8.0 교재의 275 페이지에 있는 표(를 거의 배낀 것)이다.

(쓰다보니 세컨더리 인덱스 얘기가 많아서

이후에 세컨더리 인덱스까지 쓴 다음에 그 뒤로 옮겨야겠다..!)

장점	PK 검색 매우 빠름
	세컨더리 인덱스에서 PK를 가지고있기 때문에,  (테이블 읽기까지 안가고) 인덱스 읽기만으로 검색작업이 끝나는 경우가 많음  (커버링 인덱스)
단점	세컨더리 인덱스들이 PK를 갖기 때문에  PK 크기가 크면 전체적으로 인덱스의 크기가 커짐
	세컨더리 인덱스를 통해 검색할 때 PK로 다시한번 검색해야 하므로 느림
	INSERT 할 때 PK에 의해 레코드의 저장 위치가 결정되기 때문에 느림
	PK 를 UPDATE할 경우, 레코드를 DELETE 하고 INSERT 함 -> 느림

 

앞이 이해가 되었다면, 대강

read는 빨라지고 write가 느려지는 느낌이라는걸 알것이다.

 

일반적으로 웹 서비스와 같은 온라인 트랜잭션 환경에서는 쓰기:읽기 비율이 2:8 ~ 1:9 정도로, read 작업이 훨씬 많이 발생한다. 따라서 조금 느린 write를 감수하고 읽기를 빠르게 유지하는 것은 매우 중요하다.

 

---

참고

1

Real MySQL 8.0 (백은빈, 이성욱)

2

[Database/MySQL] Tablespace (테이블 스페이스와 데이터 파일에 대해서 이해할 수 있었던 자료)

https://pangtrue.tistory.com/190