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