[1] SQL 처리 과정과 I/O

이 글은 [친절한 SQL 튜닝] 을 학습하고 정리한 글입니다.

 

1.1 SQL 파싱과 최적화

SQL은 집합적이고 선언적인 질의 언어이기 때문에, 질의를 통해 결과 집합을 얻게 됩니다.

그런 결과 집합을 얻기 위해서는 절차적인 과정이 필요한데, 이 과정을 SQL 옵티마이저가 대신해줍니다.

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

 

실제 최적화 과정을 세분화 하면 다음과 같습니다

1. SQL 파싱 - SQL Parser가 파싱을 수행합니다

2. SQL 최적화 - Optimizer가 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 다양한 실행경로를 생성한 후 비교하여 가장 효율적인 방식을 선택합니다. (데이터베이스 성능의 핵심)

3. Row-Source 생성 - Optimizer가 선택한 실행 경로를 실행 가능한 코드 혹은 프로시저 형태로 포멧팅합니다. 

 

SQL 옵티마이저는 최적의 데이터 액세스 경로를 선택해주며, 다음 과정을 거쳐 작업을 수행합니다.

1. 사용자로부터 받은 쿼리를 수행하는 데 후보군이 될 만한 실행계획들을 찾습니다.

2. 데이터 딕셔너리에 미리 수집해 둔 통계 정보를 이용해 각 실행계획의 예상 비용을 산정합니다.

3. 최저 비용을 나타내는 실행계획을 선택합니다. 

 

 그럼 실행계획을 구성할 때 그 근거는 무엇일까요? 바로 테이블에 대한 통계 정보입니다. 

 

MySQL에서 테이블 통계정보를 보는 방법

테이블에 대한 통계정보를 통해 where 절 혹은 조건문에서 어떤 작업을 먼저 수행해야 할 지에 대해 결정하게 됩니다. 

이 정보들을 기반한 연산을 통해 개별 경로에 대한 cost를 계산하고, 이를 비교하여 최적 경로를 찾아냅니다. 

 

쿼리를 수행할 때 Optimizer로 하여금 힌트를 제공하여 데이터 액세스 경로를 바꿀 수 있습니다. 

사용자가 쿼리를 할 때, 어떤것을 포커싱하여 최적화 하면 더 좋은 결과를 낳는지 아는 경우 Optimizer에게 힌트를 주면

더욱 빠르게 최적화 경로를 찾아 Row-Source 코드를 생성 할 것입니다. 

힌트는 개별 Vender마다 다르지만, 핵심은 동일하기 때문에 개별 힌트가 목표하는 바에 대해서 추후에 자세히 알아보겠습니다. 

 

1.2 SQL 공유 및 재사용

SQL 파싱 -> 최적화 -> 로우 소스 생성 과정을 거쳐생성한 내부 프로시저를

재사용 하게끔 캐싱해두는 메모리 공간을 "라이브러리 캐시" 라고 합니다. 

MySQL 에서는 옵티마이저 캐싱을 PreparedStatement 와 같은 형태로 제공합니다. 

 

사용자가 SQL문을 전달하면 DBMS는 SQL을 파싱한 후 해당 SQL이 캐시에 존재하는지 확인합니다.

만약 있으면 해당 로우 소스를 그대로 실행하고 (소프트 파싱), 그게 아니면 최적화 과정을 거치고 로우 소스를 생성해야 합니다. (하드 파싱)

 

SQL 최적화 과정은 매우 길고 복잡한 과정입니다.

예를 들어 테이블 3개를 조인하는 경우 조인 순서만 놓고 보면 6가지 경우를 탐색해야 합니다. (3!)

Join 방식도 NL조인, Sort Merge 조인, 해시 조인 등이 있으며

테이블 전체를 스캔할지, 인덱스를 이용할지도 결정해야 하며

인덱스 스캔에도 Range Scan, Unique Scan, Full Scan, Fast Full Scan, Skip Scan 등 다양한 방식이 필요합니다. 

게다가 인덱스가 여러개 일 수도 있겠죠. 

 

이 과정에서 옵티마이저는 테이블에 대한 정보, 테이블 인덱스 및 컬럼 통계값, 시스템 통계 및 옵티마이저 패러미터 정보들을 사용합니다. 

이와 같이 무수히 많은 작업을 수행하는 SQL 파싱 작업을 한번 쓰고 버리기엔 아까우니

동일한 SQL의 경우 구성된 프로시져를 그대로 사용하면 최적화 횟수를 줄일 수 있어 자원을 아낄 수 있게 됩니다. 

 

이 때 값만 다르고 구조가 동일한 SQL은 Parameterize 하여 하드파싱을 1회만 수행하게끔 하면 성능에 큰 기여를 할 수 있게 됩니다.

 

1.3 데이터 저장 구조

결국 SQL 튜닝은 I/O 튜닝이라고도 할 수 있다. SQL이 느린 이유는 디스크 I/O가 느리기 때문입니다. 

 

Physical Storage Structure (출처 : https://docs.oracle.com/cd/E11882_01/server.112/e40540/physical.htm)

Oracle DB는 데이터를 저장하는 Tablespace 영역에 각 세그먼트를 생성합니다.  

세그먼트는 테이블, 인덱스와 같이 데이터 저장공간이 필요한 오브젝트입니다. 

파티션 구조가 아니면 테이블도 하나의 세그먼트이고, 인덱스도 하나의 세그먼트입니다. 

테이블 혹은 인덱스가 파티션 구조라면, 각 파티션이 하나의 세그먼트가 됩니다. 

 

Local Storage Structure (출처 : https://docs.oracle.com/database/121/CNCPT/logical.htm)

하나의 세그먼트는 여러개의 Extent로 구성되며, 이는 공간을 확장하는 단위입니다. 

익스텐트에서 작업을 수행하다가 용량이 부족하면 Segment로부터 익스텐트를 추가로 할당받습니다.

익스텐트는 연속된 블록들의 집합이기도 합니다. 

 

공간을 확장하는 것은 익스텐트 단위이지만, 레코드를 실제로 저장하는 공간은 데이터 블록입니다. 

OracleDB에선 이를 블록이라 부르지만, MySQL에서는 이를 페이지라고 부릅니다. 

한 블록은 하나의 테이블이 독점하여, 한 블록에 저장된 레코드는 모두 같은 테이블의 레코드입니다. 

 

한 익스텐트의 블록 또한 하나의 테이블이 독점하여, 한 익스텐트에 담긴 블록은 모두 같은 테이블의 블록입니다. 

(이 또한 Vendor마다 다른데, MS-SQL Server는 한 익스텐트를 여러 오브젝트가 같이 사용할 수도 있습니다)

 

세그먼트 공간이 부족해지면 테이블스페이스로부터 익스텐트를 추가로 할당받는데,

세그먼트에 할당된 모든 익스텐트가 서로 다른 데이터파일에 위치할 가능성이 더 높다. 

이는 Race Condition을 최소화 하기 위한 전략입니다. 

 

데이터베이스에서 파일을 읽고 쓰는 단위는 블록이기 때문에,

특정 레코드 혹은 인덱스 하나를 읽고 싶어도 해당 블록을 통채로 읽게 됩니다. 

 

인덱스의 구조 (출처 : https://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm)

다음 과 같이 인덱스의 구조가 leaf node가 블록으로 구성되어 있고, 하나를 읽기 위해 블록을 통채로 읽습니다. 

 

1.4 I/O 매커니즘

테이블 또는 인덱스를 액세스하는 방식으로는 "시퀀셜 액세스"와 "랜덤 액세스" 방식 두 가지가 있습니다.

Sequential Access는 말 그대로 논리적 혹은 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식입니다. 

 

인덱스의 leaf node는 주소값으로 서로 연결되어 있는데요 (B+ tree) 

이 주소값에 따라 앞뒤로 스캔하는 방식이 시퀀셜 액세스입니다. 

 

블록 간에는 논리적인 연결고리가 없고, 세그먼트에 할당된 익스텐트 목록을 세그먼트 헤더에 맵(map) 으로 관리합니다. 

익스텐트 맵은 각 익스텐트의 첫번째 블록 주소 값을 가집니다. 

읽어야 할 익스텐트 목록을 익스텐트 맵으로부터 얻어서, 첫번째 블록부터 순서대로 읽으면 그게 "Full Table Scan" 입니다. 

Index를 이용하여 개별 레코드의 주소값을 얻은 후 해당 블록으로 바로 이동하는 방식은 "랜덤 액세스" 방식이라고 합니다.

 

Sequential and Random (출처 : https://www.computerhope.com/jargon/s/sequacce.htm)

 

모든 DBMS엔 데이터 캐싱 매커니즘이 존재하는데, SQL에 대한 코드캐시가 아닌 "데이터 캐시"가 존재하는데

디스크에서 가져온 데이터 블록을 캐싱해 둠으로써 I/O 작업을 줄이는데 그 목적이 있습니다. 

 

Memory Architecture (출처 : https://sarc.io/index.php/oracledatabase/526-oracle-architecture-memory-concepts#!/ccomment-comment=344)

 

서버 프로세스와 데이터파일 사이에 캐시를 둠으로써 항상 먼저 버퍼캐시부터 찾습니다.

버퍼캐시는 공유메모리 영역에 있으므로 같은 영역을 읽는 다른 프로세스 또한 그 득을 보게 됩니다. 

버퍼 캐시를 통한 I/O 를 논리적 I/O, 디스크에서 발생한 I/O 를 물리적 I/O 라고 합니다. 

물리적 I/O 는 실제 디스크를 액세스해야 하므로 속도가 상당히 느립니다. (보통 10000배 차이)

그렇기 때문에 자연스럽게 버퍼캐시를 이용한 논리적 I/O가 성능상의 이점을 주게 됩니다. 

 

버퍼 캐시 히트를 통해 리턴된 비율을 Buffer Cache Hit Ratio (BCHR) 라고 합니다. 

버퍼 캐싱의 주요 성능 척도가 되며, 온라인 트랜잭션을 주로 처리하는 어플리케이션은 평균 99% 히트율을 달성해야 합니다.  

BCHR = (1 - (물리적 I/O) / (논리적 I/O) ) * 100 입니다. 

이를 다시 정리하면 (물리적 I/O) = (논리적 I/O) * (100 - BHCR) 입니다. 

BCHR은 시스템 상황에 따라 달라지므로, 물리적 I/O 는 시스템 상황에 의해 결정되는 통제 불가능한 외생 변수입니다.

따라서 SQL 성능을 높이기 위해 SQL 만으로 할 수 있는 것은 논리적 I/O를 줄이는 것 뿐입니다. 

 

논리적 I/O를 줄이려면 SQL을 튜닝하여 읽는 총 블록의 갯수를 줄이면 됩니다. 

논리적 I/O를 줄임으로써 물리적 I/O를 줄이는 것이 곧 SQL 튜닝입니다. 

 

Single Block I/O vs Multiblock I/O

 

캐시에서 찾지 못한 데이터 블록을 I/O Call을 통해 디스크에서 DB 버퍼캐시로 옮겨 읽는데

이 때 한번에 하나의 블록을 요청하는게 Single Block I/O

여러 블록을 요청하는 게 Multiblock I/O 입니다. 

 

인덱스를 이용하게 되면 인덱스와 테이블 블록 모두 Single Block I/O 방식으로 정보를 가져오게 되며, 

싱글 블록을 가져와 버퍼하므로, 작은 양의 데이터를 읽을때 효율적이다.

반면 많은 데이터 블록을 읽을 때는 Multiblock I/O 가 효율적이다. 인덱스를 쓰지 않고 테이블 전체를 스캔할 때 사용됩니다. 

어짜피 많은 데이터를 가져와서 버퍼에 두어야 하는데,

이왕 가져오는거 한번에 가져와야 프로세스가 I/O에 Block되는 시간을 줄일 수 있기 때문입니다.

 

관련한 내용은 다음 링크에 더 자세히 설명되어 있습니다. 

 

Table Full Scan vs Index Range Scan

 

테이블에 저장된 정보를 읽는 방식은 1. 테이블 전체를 스캔해서 읽거나 2. 인덱스를 이용하는 방식입니다. 

전자를 Table Full Scan, 후자를 Index Range Scan 이라고 합니다. 

 

전체를 읽으니까 느리다고 생각할 수 있지만, 오히려 인덱스가 성능을 떨어뜨리는 경우도 상당히 많습니다. 

한번에 많은 데이터를 처리하는 집계용 SQL이나 배치 프로그램의 상당수가 Table Full Scan으로 유도하면 성능이 빨라집니다. 

 

인덱스를 이용할때 성능이 더 느린 이유는, 위의 Single Block I/O와 Multi Block I/O의 관점에서 생각해보면 이해가 쉽습니다. 

Index Range Scan을 통한 테이블 액세스는 Random Access와 Single Block I/O 방식으로 디스크 블록을 읽는데, 

캐시에 없으면 매번 해당 블록을 불러오느라 Block 되기 때문에 

많은 데이터를 다루는 경우엔 Table Full Scan 보다 불리합니다.