SQL 처리과정과 I/O

도서/친절한 SQL 튜닝

SQL 처리과정과 I/O

min.c00 2024. 5. 8. 00:05

이 글은 친절한 SQL 튜닝이라는 책을 읽고 공부한 내용을 정리한 글입니다. 이해한 내용을 제 나름대로 간략히 정리했음을 미리 알려드립니다.

SQL 파싱과 최적화
1. 최적화 과정
2. SQL 공유 및 재사용
바인드 변수의 중요성
1. 이름 없는 SQL 문제
데이터 저장구조 및 I/O 메커니즘
1. SQL이 느린 이유
2. 데이터 베이스 저장 구조
3. 블록 단위 I/O
4. 논리적 I/O vs 물리적 I/O
5. 인덱스를 이용해도 성능이 느릴 수 있을까?
6. 캐시 탐색 메커니즘

1. SQL 파싱과 최적화

SQL은 구조적(Structured), 집합적(Set-Based), 선언적(Declarative)인 질의 언어이다.

SQL은 사용자가 원하는 데이터를 구조적으로 선언할 수 있는 기능을 제공합니다. 이는 데이터베이스에서 필요한 정보를 어떤 형태로 가져올지를 정의하는 것을 의미합니다. 예를 들어, "고객 테이블에서 이름이 'John'인 고객의 정보를 가져와라"와 같은 명령은 SQL에서 쉽게 표현할 수 있습니다.

하지만 이런 구조적인 선언만으로는 데이터를 가져오는 실제 과정은 수행되지 않습니다. 이를 실행하기 위해서는 구조적 집합을 만드는 과정이 필요합니다. 이때 SQL 옵티마이저가 등장합니다. SQL 옵티마이저는 데이터베이스 관리 시스템(DBMS)의 내부 엔진으로, 사용자가 작성한 SQL 쿼리를 실행하기 위한 최적의 실행 계획을 수립합니다.

즉, SQL은 원하는 데이터의 구조를 정의하고, SQL 옵티마이저는 그 구조를 바탕으로 데이터를 가져오기 위한 최적의 절차를 결정하여 수행하는 역할을 합니다. 이러한 최적화된 절차를 구현하기 위해서는 프로시저(Stored Procedure)가 필요할 수 있습니다. 프로시저는 데이터베이스에서 정의된 절차적인 작업을 수행하는 데 사용되며, SQL 옵티마이저가 이를 만들어내는 과정 중 하나로 활용될 수 있습니다.

DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행가능한 상태로 만드는 과정을 SQL 최적화라고 합니다.

1-1. 최적화 과정

SQL 파싱
- 파싱 트리 생성(SQL 문장을 해석하고 구조화하기 위해 생성되는 트리 구조)
- 문법 체크(문법 오류 검사)
- Semantic 체크(의미상 오류 검사 = 테이블 칼럼 유무, 권한 등)
SQL 최적화
로우 소스 생성

SQL 최적화 과정을 도식화하면 다음과 같습니다. 먼저 사용자가 작성한 SQL의 문법적, 의미적 오류가 없는지 검사합니다. 여기서 파싱 트리란, SQL에서 데이터를 가져올 테이블을 지정하는 부분, 조건 지정 부분 등을 노드로 표현하고 이들 간의 관계를 트리 구조로 표현을 의미합니다. 예를 들어 "SELECT * FROM Employees WHERE Age > 30" SQL의 파싱트리는 다음과 같습니다.

SQL 파싱 이후는 옵티마이저가 SQL을 실행하기 위한 실행계획 중 가장 비용이 낮은 실행계획을 선택합니다. 여기서 말하는 비용은 쿼리를 수행하는 동안 발생할 것으로 예상하는 I/O 횟수 또는 예상 소요시간을 표현한 값을 실측치가 아닌 예상치를 의미합니다. 실행계획 선택 후에는 옵티마이저가 실제 실행가능한 코드 또는 프로시저 형태로 포맷팅 합니다.

1-2. SQL 공유 및 재사용

SQL 파싱, 최적화, 로우소스로 만들어지는 과정이 여러 번 발생하면 시간과 비용 측면에서 비효율적이기 때문에 DBMS는 생성된 프로시저를 캐싱하고 재사용함으로써 문제를 해결하고 있습니다.

소프트 파싱 vs 하드 파싱

이때 캐싱은 SGA(System Global Area)의 구성요소 중 DB Buffer Cache(라이브러리 캐시)에 저장되며 사용자가 입력한 쿼리가 라이브러리 캐시에 존재하면 바로 읽어서 사용하고 없다면 최적화 과정을 거쳐서 프로시저를 만들어서 읽어옵니다. 전자의 과정을 소프트 파싱, 후자의 과정을 하드 파싱이라고 합니다.

캐싱을 하는 이유는 앞서 말한 것처럼 SQL 최적화 과정에서 발생되는 비용을 줄이기 위함입니다.

조인순서, 조인 방식, 테이블 또는 인덱스 사용 여부, 인덱스 방식 및 인덱스 개수 등 여러 요인
옵티마이저가 사용하는 정보
- 테이블, 칼럼, 인덱스 구조
- 오브젝트 통계(테이블 통계, 인덱스 통계, 칼럼 통계)
- 시스템 통계(CPU 속도, Single Block I/O, Multi Block I/O 속도 등)
- 옵티마이저 관련 파라미터

SQL 최적화 과정은 위에서 정리한 요인들에 의해서 결정되기 때문에 프로시저를 한번 생성하는데 발생하는 비용이 크다는 것을 예측할 수 있습니다. 때문에 한번 생성된 프로시저를 재사용하는 것이 바람직합니다.

2. 바인드 변수의 중요성

2-1. 이름 없는 SQL 문제

SQL은 영구 저장 되지 않습니다. 의미적으로 같은 SQL이지만 실행 시 각각 최적화 및 라이브러리 캐시에서 별도 공간으로 저장된다는 것을 의미합니다.

예를 들어 "SELECT * FROM USER WHERE USERNAME = [:username]"이라는 SQL이 있을 때

SELECT * FROM USER WHERE USERNAME = 'A'
SELECT * FROM USER WHERE USERNAME = 'B'
SELECT * FROM USER WHERE USERNAME = 'C'

3개의 SQL은 모두 의미적으로 같은 SQL이지만 실행 시 최적화 및 라이브러리 캐시에서 별도 공간으로 저장됩니다. 만약 여러 명의 사용자를 조회하는 호출이 1,000,000번 호출하게 된다면 라이브러리 캐시에 비효율적인 메모리 사용이 발생할 것입니다.

이 문제를 해결하기 위해서 프로시저를 생성할 때 파라미터 Driven 방식으로 SQL을 작성할 수 있습니다. 가장 대표적인 방법이 Java진영에서 JDBC + prepared statement 객체를 활용해 SQL 실행 시 파라미터를 전달하는 방법입니다.

3. 데이터 저장구조 및 I/O 메커니즘

3-1. SQL 이 느린 이유

I/O = 잠(Sleep)

책에서는 입출력 작업을 '잠'이라고 표현하는데 그 이유는 I/O가 필요할 때는 프로세스가 Sleep 해야 하기 때문입니다. 데이터 베이스 서버에서 스토리지에서 I/O작업을 할 때 시간이 오래 걸리기 때문에 앞서 우리는 캐싱 방법을 사용한다고 정리했습니다. 이처럼 디스크 I/O가 SQL 성능을 크게 좌우한다고 할 수 있습니다.

3-2. 데이터 베이스 저장구조

데이터 저장을 위해서는 먼저 테이블 스페이스를 생성해야 합니다. 테이블 스페이스 구조는 다음과 같습니다.

테이블 스페이스 : 세그먼트를 담는 컨테이너
세그먼트 : 테이블, 인덱스처럼 테이블 저장 공간이 필요한 오브젝트
익스텐트 : 공간을 확장하는 단위(테이블 or 인덱스에 테이블을 입력하다가 공간이 부족해지면, 해당 오브젝트가 속한 테이블 스페이스로부터 익스텐트를 추가 할당)
(데이터) 블록 : 사용자가 입력한 레코드를 실제로 저장하는 공간(DB2, SQL Server 같은 DBMS는 블록이 아닌 페이지라는 용어를 사용합니다.)
데이터 파일 : 디스크 상의 물리적인 os 파일

하나의 테이블 스페이스를 여러 데이터 파일로 구성하면, 파일 경합을 줄이기 위해 DBMS가 데이터를 가능한 한 여러 데이터 파일로 분산 저장하기 때문에 세그먼트에 할당된 모든 익스텐트는 서로 다른 데이터 파일에 위치할 가능성이 높습니다.

3-3. 블록 단위 I/O

DB에서 읽고 쓰는 단위는 블록입니다. 즉, 특정 레코드 하나를 읽고 싶어도 해당 블록을 통째로 읽어야 한다는 말고 동일합니다.

왼편에 테이블에 저장된 데이터가 N개의 블록으로 저장되어 있다고 할 때, 해당 블록은 레코드(row 수)로 구성되어 있다. 이때 하나의 레코드의 크기가 1Byte, 블록의 크기를 8KB라고 가정하면 하나의 레코드를 읽기 위해서는 8KB를 읽어야 한다.

3-4. 논리적 I/O vs 물리적 I/O

DB 버퍼 캐시는 블록에 대한 반복적인 I/O 호출을 줄이는 역할을 한다. 논리적 I/O는 SQL을 처리하는 과정에서 발생한 총 블록 I/O, 즉 메모리 I/O를 의미하고, 물리적 I/O는 디스크에서 발생한 총 블록 I/O를 의미한다.

캐시에 저장된 프로시저를 읽기 위해 반드시 버퍼 캐시에 접근하기 때문에 논리적 I/O는 SQL을 처리하는 과정에서 발생한 총 블록 I/O로 보는 것이다.

논리적 I/O는 전기적 신호, 물리적 I/O는 액세스 암(Arm)을 통해 물리적 작용이 일어나기 때문에 논리적 I/O가 보통 10,000배쯤 빠르다.

버퍼 캐시에서 찾고자 하는 프로시저를 찾는 비율을 버퍼캐시 히트율(BCHR = Buffer Cache Hit Rate)이라고 하며 수식으로 표현하면 다음과 같다.

BCHR = (캐시에서 곧바로 찾는 블록 수 / 총 읽은 블록 수) x 100

여기서 주의깊게 볼 점은 SQL의 성능은 물리적 I/O가 아닌 논리적 I/O가 결정한다는 것이다. 물리적 I/O가 논리적 I/O보다 느리다면 성능을 결정하는 요인은 물리적 I/O가 아닌가?라고 생각할 수 있지만, 물리적 I/O는 시스템 상황에 의해 결정되는 통제 불가능한 외생 변수라는 점이다.

물리적 I/O = 논리적 I/O x (100% - BCHR)

위의 수식을 다르게 보면 다음과 같다. 핵심은 물리적 I/O의 값은 논리적 I/O에 의해 결정된다는 점이고, 우리는 논리적 I/O를 줄여야 한다는 결론을 도출할 수 있다.

그럼 어떻게 논리적 I/O를 줄일 수 있을까? 이 질문의 답은 SQL 튜닝을 통해 읽는 총블록의 개수를 줄이는 것이다. 즉, 버퍼 캐시 접근의 횟수를 줄이는 것이다.

한 가지 짚고 넘어갈 점은 BCHR이 높다고 효율적인 SQL일까?라는 질문이다. 결론부터 말하자면 아니다 이다. 비효율적으로 반복해서 같은 블록을 읽을 때도 BCHR이 높게 나올 수 있기 때문이다.

3-5. 인덱스를 이용해도 성능이 느릴 수 있을까?

시퀀셜 액세스 vs 랜덤 엑세스

시퀀셜 액세스는 논리적 물리적으로 연결된 순서에 따라 차례대로 읽는 방식을 의미하고 랜덤 액세스는 레코드 하나를 읽기 위해 한 블록 씩 접근하는 방법을 의미한다.

Single Block I/O vs Mutil Block I/O

Single Block I/O는 한 번에 한 블록씩 요청해서 메모리에 적재하는 방법이고 Multi Block I/O는 한 번에 여러 블록을 요청에 메모리에 적재하는 방법이다. 보통 인덱스같이 소량의 데이터를 읽을 때 Single Block I/O가 적합하고 테이블 전체를 스캔해야 할 때는 Multi Block I/O가 적합하다.

Table Full Scan vs Index Range Scan

테이블에 저장된 데이터를 읽는 방식은 Table Full Scan, Index Range Scan 두 가지 방식으로 분류할 수 있다. 전자는 테이블 전체를 스캔함으로써 테이블에 속한 블록 전체를 읽는 방법이고 후자는 인덱스에서 일정량을 스캔하면서 얻는 RowID(레코드가 디스크 어디에 저장되어 있는지 가리키는 위치 정보)로 테이블 레코드를 찾는 방법이다.

Q. 인덱스를 이용해도 성능이 느릴 수 있나?

Table Full Scan 은 시퀀셜 액세스 + Multi Block I/O 방법을 사용해서 디스크 블록을 읽는다. 즉, 한 번의 I/O 호출로 인접한 수십 개 블록을 한꺼번에 읽는 방법이다.

반대로 Index Range Scan 은 랜덤 액세스 + Single Block I/O 방법을 사용해서 디스크 블록을 읽는다. 즉, 레코드를 하나 읽기 위해 매번 I/O 호출을 해야 한다.

A. 따라서 많은 데이터를 읽을 때는 Index Range Scan는 비효율 적일 수 있다. 또한 한 블록에 5000개 레코드가 있다면 같은 블록을 읽을 수 있기 때문에 (데이터를 읽는 단위는 블록이기 때문이다.) 불필요한 인덱스 사용은 경계해야 한다.

3-6. 캐시 탐색 메커니즘

버퍼 캐시 구조는 해시 구조로 관리합니다.

해시 체인을 결정하는 해시함수를 x를 5로 나눈 나머지 연산이라고 가정할 때, 버퍼 캐시에서 20번 블록을 찾는 다면 해시 체인 0에서 탐색하고 없다면 디스크로부터 읽고 해당하는 체인에 연결하고 읽습니다.