[MySQL] Index [1] — 인덱스 사용 배경, 인덱스와 디스크 I/O
[1] [MySQL] Index [1] — 인덱스 사용 배경, 인덱스와 디스크 I/O
[2] [MySQL] Index [2] — 인덱스 자료 구조 (Hash Table)
| 인덱스 사용 배경
우리는 책의 목차를 통해 찾고 싶은 내용이 몇 번째 페이지에 있는지 (비교적)빠르게 확인할 수 있다. 이와 마찬가지로 데이터베이스 인덱스는 책의 목차와 같이 찾고 싶은 내용에 효율적으로 접근할 수 있도록 돕는 역할을 한다. 그런데 한 가지 고민해볼 부분이 있다. 다시 한 번 책의 목차 비유로 돌아가보자.
만약 책에서 담고 있는 내용이 방대해서 책 두께가 매우 두꺼운 경우 책의 목차가 갖는 효용은 상대적으로 크겠지만, 책이 담고 있는 내용이 매우 적어 책이 매우 얇다면 목차가 갖는 효용은 그렇게 크지 않을 것이다. 목차를 볼 시간에 책을 빠르게 훑으면서 원하는 내용을 찾는 것이 더 빠르거나 비슷할 수도 있기 때문이다.
따라서 인덱스는 일반적으로 많은 데이터(= 두꺼운 책)를 갖고 있는 상황에서 우리가 찾고 싶은 데이터를 빠르고 효율적으로 찾을 수 있도록 돕는 역할을 한다고 생각해볼 수 있다.
| 인덱스와 디스크 I/O
인데스 사용 배경을 설명하면서, 일반적으로 많은 데이터(= 두꺼운 책) 의 상황을 전제로 갖는다는 것을 설명했다. 여기까지만 보면, 많은 데이터가 있을 때 인덱스를 사용하면 무조건 효율적으로 데이터를 찾을 수 있을 것이라고 생각할 수 있지만, 사실 한 가지 더 고려해야 한다. 바로 인덱스를 통해 읽어들이는 데이터의 양이다.
인덱스 사용 시 읽어들이는 데이터의 양을 고려해야 하는 이유를 디스크 I/O 관점에서 생각해보자.
[ 그림 2 ] 는 프로세스 실행 흐름에 따른 프로세스의 상태 변화를 보여주는데, [ 그림 2 ] 에서 볼 수 있듯이 입출력 요청 시 해당 프로세스는 실행 상태에서 대기 상태가 된다. 이후 입출력 관리자가 해당 요청을 모두 처리하고 인터럽트 신호를 보낸 뒤 준비 상태가 되는 흐름을 갖는다.
그렇다면 읽어들이는 양이 많은 경우에는 어떤 방법을 통해 데이터를 조회해야 할까?
| 테이블 풀 스캔 vs 인덱스
데이터베이스 테이블에서 데이터를 찾는 방법도 아래 두 가지다. 수십 년에 걸쳐 DBMS가 발전해 왔는데도 이 두 방법 에서 크게 벗어나지 못하고 있다.
- 친절한 SQL 튜닝 -
위 인용문에서 말한 두 가지 방법이 이번 소제목에 등장하는 테이블 풀 스캔(table full scan)과 인덱스다. 테이블 풀 스캔은 이름에서 쉽게 유추할 수 있듯이 테이블 전체를 스캔하는 방식으로 데이터를 조회한다.
테이블 풀 스캔과 디스크 I/O 를 다루기 전에 먼저 테이블이 어떻게 저장되어 있는지를 가볍게 알아보자. 데이터를 저장하기 위해서는 테이블 스페이스를 생성해야 하고, 테이블 스페이스는 [ 그림 3 ] 과 같이 세그먼트 → 익스텐트 →블록 → 로우(row) 계층으로 구성된다.
테이블 풀 스캔 방식은 데이터를 조회할 때 각 블록을 순차적으로 접근하면서 읽어들인다. 이를 순차 접근(sequential access) 이라 한다. 그리고 각 블록을 읽어들일 때 여러 개의 블록을 한 번에 메모리에 로드하는 방식인 멀티 블록 I/O 를 기반으로 동작한다.
반면, 인덱스 방식은 책의 목차와 같이 찾고자 하는 데이터의 위치를 기반으로 대상 블록에 접근하는 방식으로 데이터를 조회하는데 이를 랜덤 접근(random access)이라 한다. 랜덤 접근 시에는 [ 그림 4 ] 에서 볼 수 있듯이 단일 블록에 대한 I/O 요청 을 기반으로 동작한다.
이제 왜 인덱스를 통해 많은 데이터를 읽어 들일 때는 디스크 I/O 가 많이 일어나는 지에 대해 생각해보자.
[ 그림 5 ] 의 인덱스가 저장된 형태를 보면, 트리 구조를 갖는 것을 볼 수 있다. 이 때 해당 트리의 리프 노드에는 (책의 목차에서 각 내용들이 몇 번째 페이지에 위치하고 있는지에 대해 정보를 알려준 것 처럼) 어떤 블록으로 가야하는지에 대한 정보를 담고 있다. 이를 활용해 대상 블록에 대해 I/O 요청을 보내는데, 이를 흔히 Random I/O 라고 한다.
[ 그림 5 ] 에서는 하나의 Random I/O 만 일어났지만, 만약 인덱스를 통해 읽어 들이는 데이터의 양이 많으면 어떤 양상을 보일까? [ 그림 6 ] 과 같이 각 인덱스의 리프 노드에 저장된 블록 위치 정보를 기반으로 많은 양의 Random I/O 요청이 일어날 것이다. 따라서 인덱스를 통해 많은 양의 데이터를 읽으면 디스크 I/O 가 많이 일어나 성능 측면에서 좋지 않을 수 있다.
전체 100만 건의 레코드 가운데 50만 건을 읽어야 하는 작업은 인덱스의 손익 분기점인 20~25%보다 훨씬 크기 때문에 MySQL 옵티마이저는 인덱스를 이용하지 않고 직접 테이블을 처음부터 끝가지 읽어서 처리할 것이다.
- Real MySQL 8.0 | 8장 인덱스 -
그렇다면, 많은 양의 데이터를 읽어 들일 때는 어떻게 해야할까? 우리에겐 다른 데이터 조회 방식이 남아 있다. 바로 테이블 풀 스캔 방식이다. 테이블 풀 스캔 방식은 위에서 설명한 것과 같이 단일 블록이 아닌 멀티 블록 단위로 디스크 I/O 요청을 하기 때문에 상대적으로 적은 디스크 I/O 요청으로 많은 데이터를 읽어 들일 수 있다. 위 인용 내용에서 테이블을 처음부터 끝가지 읽어서 처리하는 방식이 바로 테이블 풀 스캔 방식이다.
| Reference
- 친절한 SQL 튜닝 | 1.3장 데이터 저장 구조 및 I/O 메커니즘
- Real MySQL 8.0 | 8장 인덱스
