우리가 작성한 SQL 이 데이터를 찾아가는 과정
우리가 작성하는 SQL 문은 선언적(Declarative) 이라고도 합니다. 명령형 코드에 따라 순차적으로 프로그램이 실행되는 방식과는 달리, 우리가 원하는 조건에 대해서만 SQL문으로 작성하면 쿼리 옵티마이저가 해석하여 데이터에 접근할 수 있는 최적의 경로(비용이 적게 드는, Cost)를 계산해 데이터를 찾아 주는데 이번 아티클 에서는 이 과정에 대해 상세히 알아보려 합니다.
테이블 스페이스
DB 내에서 테이블의 정보를 저장하는 공간으로, 1개 이상의 테이블 정보들을 저장하고 있는 논리적인 단위이며 하나의 테이블 스페이스는 아래와 같은 논리적인 공간을 포함하고 있습니다.
해당 아티클에서 세그먼트 (파티션)과 세그먼트 (LOB)에 대한 설명은 다루지 않습니다.
- 테이블 스페이스 — 세그먼트를 묶는 컨테이너, 1개 이상의 테이블 정보들을 저장하고 테이블은 여러 테이블 스페이스에 속할 수 없음
- 세그먼트 — 각 오브젝트 별(테이블 / 인덱스…) 데이터 저장공간
- 익스텐트 — 블록을 묶는 저장공간 확장 단위
- 블록 — 데이터 Row를 묶는 데이터를 읽고 쓰는 단위
위 개념은 Oracle의 개념이며, PostgreSQL 의 경우 세그먼트, 익스텐트의 개념은 없습니다. 하지만 데이터 파일을 확장하여 비슷하게 관리 합니다.
또한 블록은 PostgreSQL의 페이지와 대조된다 보면 됩니다. 앞으로는 통일된 명칭으로 블록이라 표현 하겠습니다.
아래 내용에서 좀 더 자세히 다루겠지만, 쿼리를 실행하여 데이터를 찾는다는건 결국 대상 데이터가 속해있는 세그먼트 (테이블)의 블록을 순차적으로 탐색하여 원하는 데이터 Row를 찾는 동작입니다.
우리가 아는 Table Full Scan과 Index Range Scan 은 아래와 같은 방식으로 블록 내 Row를 탐색합니다.
Table Full Scan — 세그먼트 (테이블)의 익스텐트 내 블록들을 수평 (순차적)으로 전체 탐색
Index Range Scan — 세그먼트 (인덱스)의 익스텐트 내 루트 인덱스 블록 -> 브랜치 인덱스 블록까지 수직 탐색 -> 브랜치 인덱스 블록 내 Row에 해당하는 리프 인덱스 블록전체를 수평 탐색 -> 해당 Row가 가리키는 세그먼트 (테이블)의 블록을 탐색
코드 캐싱
위에서 테이블 스페이스의 구조와 데이터를 찾는 동작에 대해 간단히 알아보았습니다.
이번 항목 에서는 SQL문을 실행하면 어떤 과정을 거쳐 DB 에 명령을 내리는 로우 소스코드로 변환 되는지, 그리고 이 과정들을 한번 수행하면 로우 소스코드를 효율적으로 재사용 하기 위해 어떻게 캐싱 하는지 알아보도록 하겠습니다.
Oracle 에서는 SGA > Library Cache, PostgreSQL 에서는 Prepared Statement Cache 라고 하는 공간에서 기존 실행된 로우 소스코드를 캐싱하여 재사용합니다.
각 DB 엔진 별로 내부 구조가 다르지만 일반적으로 아래와 같은 과정을 거쳐 로우 소스코드가 생성되고 실제 데이터를 찾는 동작을 수행합니다.
DB 엔진 별 1번만 실행 하더라도 SQL 문을 바로 캐싱 하는 경우도 있고 여러번 실행 했을 때 SQL 문을 캐싱 하는 경우도 있습니다. Oracle 은 1번 실행 시 캐싱 하는 경우고, PostgreSQL 은 여러번 실행할 경우 캐싱 합니다.
이렇게 SQL 문에 대한 최적화 결과를 캐싱 하는 이유는 최적화 하는 과정에서 많은 실행 비용(Cost)이 발생 하는데, 이 과정을 매번 수행 하는 것 보단 결과값을 저장 해두고 매번 최적화 과정을 Skip 하는게 당연히 효율적이기 때문입니다.
데이터 캐싱
위와 같이 SQL 문에 대한 로우 소스코드를 미리 캐싱 해두어 쿼리 최적화 과정을 Skip 하는 것처럼 데이터 또한 블록 단위로 캐싱 해두어 물리적 I/O를 최소화 시킵니다.
- 메모리 I/O — 데이터 캐시에 접근하여 블록을 찾는 경우
- 논리적 I/O — 데이터 캐시에 접근 하거나 Direct Path 로 DB File에 바로 접근하는 모든 경우
- 물리적 I/O — DB File 을 직접 접근하는 경우, 데이터 캐시 접근에 비해 보통 10,000 배 느림
I/O 가 발생하는 시간 동안 프로세스는 Wait Queue 에서 I/O 가 끝날때까지 휴식을 취하게 됩니다. 그렇기 때문에 우리는 프로세스가 쉬는 시간을 최대한 줄여야 합니다.
한마디로 물리적 I/O 는 캐시 미스가 발생 하거나, Direct Path 로 직접 DB File 에 접근하는 경우 ‘물리적 I/O 가 발생했다’ 라고 한다.
물리적 I/O 가 발생하여 DB File 을 직접 접근하는 경우 데이터 캐시에 블록 데이터를 캐싱 해둡니다.
DB File 에서 블록을 읽은 후 데이터 캐시에 저장 하는데, 한 블록만 읽고 저장하는 경우를 Single Block I/O 라고 하고 다수의 블록을 읽은 후 저장하는 경우를 Multi Block I/O 라고 합니다.
그럼 어떨때 Single Block I/O를 수행하고 어떨때는 Multi Block I/O 수행 할까요?
테이블 블록을 스캔하는 동작을 다시 떠올려 본다면 쉽게 유추 할 수 있습니다.
Table Full Scan의 경우 세그먼트 (테이블) 내의 블록들을 수평 탐색 하면서 결과값을 찾을때 까지 탐색 합니다. 이 경우 1개 이상의 블록을 탐색하기 때문에 여태 읽어온 블록들을 데이터 캐시에 저장합니다. 블록 하나의 평균 8kb 이며, 최대 1MB 까지 Multi Block I/O 를 수행하여 캐싱 할 수 있습니다.
그렇다면 나머지 경우는 자연스레 Index Scan(Index Range Scan…)의 경우를 떠올리면 됩니다. 수직적 탐색을 통해 하나의 블록 단위로만 접근하기 때문에 Single Block I/O를 수행하여 데이터 캐시에 적재 합니다. 그래서 아래와 같이 인덱스를 스캔하는 동작 마다 Single Block I/O를 수행 합니다.
아래 인덱스 루트 / 브랜치 리프, 테이블 블록이 기억이 나지 않는다면 위 테이블 스페이스 설명 그림을 한번 보면 좋습니다.
- 인덱스 루트 블록을 읽을때
- 인덱스 브랜치 블록을 읽을때
- 인덱스 리프 블록을 읽을때
- 테이블 블록을 읽을때
위와 같이 인덱스를 탄다는 것은 많은 Single Block I/O 를 발생 시키기 때문에 많은 데이터 블록을 조회해야 한다면 Table Full Scan이 여로모로 효율적일 수 있습니다.
Summary
부제 처럼 우리가 작성한 SQL 이 데이터를 찾아가는 과정을 잘 설명하고 싶었는데 얼마나 이해가 가실지, 혹시나 올바르지 않은 정보를 제공해 드리진 않았을지 걱정되는 마음도 있습니다.
또한 해당 아티클에서 사용하는 용어들이 DB 마다 다른 명칭으로 사용되기 때문에 조심스러운 부분도 있었습니다. ex) Oracle 에서 블록 이란 개념이 PostgreSQL 에서는 페이지 라는 개념과 비슷
하지만 데이터와 인덱스 정보들이 저장되는 구조와 각각 캐싱되는 과정, 데이터를 찾아가는 과정에 대한 흐름은 거의 유사하기 때문에 해당 아티클이 내가 작성한 SQL 문이 어떤 흐름으로 실행되고 관리 되는지, 더 빠르고 효율적인 조회를 위해 DB 엔진이 어떤 노력들을 하는지 조금이라도 알 수 있도록 정보를 전달 드릴 수 있으면 좋겠습니다.
