thumbnail

서론.

<img src="https://static.podo-dev.com/blogs/images/2020/06/24/origin/ed370a7a-96b8-48d9-9ecf-ec5097e4fc2e" alt="800x0" style="width:280px;">

평소에 서버개발을 하며,
데이터베이스에 대한 이해가 부족해서 리딩한 책입니다.
정말 책의 제목대로, 친절한 워딩에 많은 도움이 되었습니다.

복습을 위해 일부 내용을 기록합니다.

<br>

1장.

1.1 SQL 처리과정과 I/O

SQL은 구조적인 질의어의 약자입니다.
하지만 실제로 결과 집합을 만드는 과정은 절차적일 수 밖에 없습니다.
프로시저가 필요한데, 이 프로시저를 만들어내는 DBMS 내부 엔진이 옵티마이저입니다.

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

SQL최적화

SQL최적화는 3단계로 이루어집니다.

    1. SQL 파싱
    • 파싱 트리(실행계획) 생성, #실행계획 : 어떤 방식으로 SQL 문장을 실행할 것인지 정해 놓은 계획
    • Syntax 체크 : 문법 오류 검증 (SQL 쿼리의 문법이 옳은가?)
    • Semmantic 체크 : 의미상 오류 검증 (테이블 존재여부, 컬럼 확인, 권한체크와 같은)
    1. SQL 최적화
    • 옵티마이저가 최적화를 진행,
    • 미리수집한 시스템 통계, 오브젝트 통계정보를 다양한 실행경로에 최적의 하나를 선택.
    • 데이터 베이스 성능의 가장 핵심적인 엔진
    1. 로우소스 생성
    • 프로시저 생성 단계

SQL 옵티마이저

SQL 옵티마이저는 사용자가 원하는 작업을 가장 효율적으로 수행 할 수있는, 최적의 데이터 엑세스 경로를 선택해줍니다.

  1. 사용자로부터 전달받은 쿼리를 수행하는데 후보군이 될만한 실행계획들을 찾아낸다.
  2. 데이터 딕셔너리에 미리 수집해둔 오브젝트 통계 및 시스템 통계 정보를 이용해 실행계획의 예상비용 산출
  3. 최저비용 실행계획 선택

옵티마이저 힌트를 통해 최적화를 수동으로 조절 할 수 있습니다.

<br>

1.2 SQL 공유 및 재사용

소프트 파싱 vs 하드 파싱

SQL 파싱, 최적화 로우 소스 생성 과정을 거쳐 생성한 내부 프로시져는 반복 재사용 할 수있도록
캐싱해두는 공간을 라이브러리 캐시라고 합니다.

라이브러리캐시는 SGA에 구성요소입니다.
SGA는 서버프로세서와, 백그라운드 프로세스가 공통으로 액세스하는 데이터와 제어구조를 캐싱하는 공유 메모리 공간입니다.

캐싱에서 바로 찾아 실행하며 소프트 캐싱,
찾는데 실패하여 최적화 및 로우생성까지 가는것을 하드 캐싱이라 합니다. (하드 = hard = 어려운)

바인드 변수가 중요한 이유

SQL 쿼리에는 ID가 있을까?
결론은 없습니다, SQL문 자체가 하나의 ID입니다.

따라서 다음 10000개의 쿼리는 서로 다릅니다.

public void selectSQL(){
    for(int i = 1; i <= 10000; i++){
        st.excuteQuery("SELECT * FROM member m WHERE m.id = " + i );
    }
}
SELECT * FROM member m WHERE m.id = 1
SELECT * FROM member m WHERE m.id = 2
//~
SELECT * FROM member m WHERE m.id = 10000

다른 각각의 프로시져가 생성되고,
소프트 파싱이 아닌, 10000번의 하드파싱이 이루어집니다.

하지만 바인딩 변수는 이를 해결합니다.

public void selectSQL(){
    for(int i = 1; i <= 10000; i++){
        st.excuteQuery("SELECT * FROM member m WHERE m.id = ?" );
    }
}
SELECT * FROM member m WHERE m.id = ?
SELECT * FROM member m WHERE m.id = ?
//~
SELECT * FROM member m WHERE m.id = ?

SELECT * FROM member m WHERE m.id = ? SQL문의 쿼리가 프로시져되어 호출 후 캐싱되고,
똑같은 SQL에 ID값을 바인딩을 달리하여 소프트 캐싱이 이루어집니다.
결국, 1번의 하드 캐싱과 9999번의 소프트 캐싱이 이루어집니다.

<br>

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

SQL이 느린 이유

SQL이 느린이유는 십중팔구 I/O가 느린 탓입니다.

I/Osleep입니다.
프로세스는 I/O 발생 시, 인터럽트가 발생하여 대기(sleep)상태로 전환됩니다.
I/O완료 후에 다시 프로세스는 실행됩니다.
열심히 일해야하는 프로세스가 I/O가 완료되기를 기다리니, 성능이 느려질 수 밖에 없습니다.

데이터 베이스 구조 이해하기

<img src="https://static.podo-dev.com/blogs/images/2020/06/27/origin/3f0c2528-5631-4cd1-ba4f-c5039eacf692.png" alt="base64.png" style="width:438px;">

데이터베이스를 생성하려면 먼저 테이블스페이스를 가집니다.
테이블스페이스는 여러개의 물리적인 데이터파일로 구성됩니다. (우리가 아는 폴더의 그 파일 !)

테이블스페이스를 생성했으면, 세그먼트를 생성합니다.
세그먼트는 테이블, 인덱스처럼 저장 공간이 필요한 오브젝트입니다.
파티션구조가 아니라면 테이블은 하나의 세그먼트입니다, 인덱스는 또한 하나의 세그먼트입니다.

세그먼트는 여러 익스텐트로 구성됩니다.
익스텐트는 공간 확장의 단위입니다.

테이블이나, 인덱스에 데이터를 입력하다 공간이 부족해지면,
테이블스페이스로 부터 세그먼트는 익스텐트를 추가로 할당 받습니다.

익스텐트는 여러개의 블록의 집합입니.
실제로 사용자가 입력한 레코드를 저장하는 공간은 블록입니다.

한 블록은 하나의 테이블이 독점합니다.
한 익스텐트 또한 하나의 테이블이 독점합니다.

블록단위 I/O

그럼 데이터베이스에 읽고 쓰는 단위는 무엇일까?
파일? 세그먼트? 익스텐트? 블록?

정답은 블록입니다.

즉, 1byte데이터를 하나 가져오고 싶어도, 1개의 블록을 통째로 읽습니다.

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

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

시퀀셜 엑세스는 논리적 또는 물리적으로 연결된 순서에 따라
차례대로 블록을 읽는 방법입니다.

테이블 블록간에는 논리적으로 연결고리르 가지고 있지 않습니다.

세그멘트 할당된 익스텐트 목록을 세그먼트 헤더에 맵으로 관리합니다.
익스텐트 맵은 각 인스텐트의 첫번째 블록 주소를 갖습니다.
따라서 첫번째부터 차근 차근 엑세스합니다. 그것이 곧 Full Table Scan 입니다.

둘째, 렌덤액세스는 말그대로 랜던하게 블록에 접근하여 엑세스 하는 방식입니다.
인덱스가 이 방식을 사용합니다.

논리적 I/O, 물리적 I/O

다시한번말하지만, 디스크 I/O가 SQL 성능을 결정합니다.
따라서, 계속해서 데이터블록을 읽는 과정은 비효율적입니다.

모든 DBMS에 데이터 캐싱 메커니즘이 필수인 이유입니다.
앞서 말한 공유메모리인 SGA의 또 하나의 구성요소는 DB 버퍼캐시입니다.

라이브러리 캐시가 최적화/프로시저를 캐싱과 같은 코드 캐싱이라면,
DB 버퍼 캐시는 데이터 캐시라 할 수 있습니다.
디스크에 어렵게 읽은 데이터를 캐싱함으로써 같은 블록에 대한 I/O Call을 줄이는 것이 목적입니다.

논리적 I/O는 SQL문을 처리하는 과정에 메모리 버퍼캐시에서 발생한 총 블록 I/O를 말한다.
(일반적으로 메모리 I/O와 같다 생각하면 됩니다)

물리적 I/O 디스크에서 발생한 총 블록 I/O를 말한다.

왜 논리적 I/O?
논리적으로 동일합니다
삭제/추가 연산없이 같은 조건절을 항상 같은 메모리 I/O 발생

왜 물리적 I/O?
물리적으로 다릅니다.
삭제/추가 연산없이 같은 조건절도 다릅니다.
처음에 캐시에 없으니까 물리적 I/O발생, 두번째에는 캐시에 없으니 물리적 I/O 발생 X

실제로 물리적 I/O가 성능을 결정하지만,
실제 SQL 성능을 향상하려면 논리적 I/O를 줄여야 합니다.

논리적 I/O가 많다는 곧 물리적 I/O가 많다이기 때문입니다. ( + 물리적 I/O는 통제 불가능합니다)

논리적 I/O를 줄임으로써, 물리적 I/O를줄이는 것이 곧, SQL 튜닝이라 할 수 있습니다.

Single Block I/O vs Multi Block I/O

한번에 한블록씩 적재해서 메모리에 적재하는것을 Single Block I/O라 합니다.
인덱스를 이용하면 이 방식을 사용합니다.

한번에 많은 블록을 적재해서 메모리에 적재하는것을 Multi Block I/O라 합니다.
한번에 많은 데이터를 읽을 때는 이 방식이 효율적입니다.
따라서 인덱스를 사용하지 않고 테이블 전체를 스캔하면, 이 방식을 사용합니다.

I/O가 발생하면, 프로세스는 잠을 잡니다.
데이터를 많이 가져올때, 한번에 가져와서, 한번에 잠을 많이 자는게 좋을까?
한개씩 가져와서 매번 자는게 좋을까?
전자가 더 효율적이기 떄문에, 많은 블록이 필요할때는 Multi Block I/O가 효율적입니다.

Table Full Scan vs Index Range Scan

Table Full Scan은 말그대로 테이블을 전체 스캔하여, 데이터를 가져오는 방식입니다.
Index Range Scan은 인덱스의 일정량을 스캔하여 가져오는 방식입니다.

일반적으로 Table Full Scan보다 Index Range Scan이 성능이 좋다고 생각합니다.

하지만 한번에 많은 데이터를 처리하는 집계용 SQL 배치 프로그램을 생각해봅니다.
인덱스를 사용하느데 왜 성능이 느릴까?
Table Full Scan은 시퀀셜 엑세스, Multiblock I/O를 사용합니다.
한블록에 속한 모든 레코드를 한번에 읽어들이고, 한번에 sleep하여 수십~수백개의 블록을 가져옵니다.
반대로, 대량의 데이터에서 일부의 데이터를 찾는다면 비효율적입니다.

하지만 Index Range Scan은 랜덤 엑세스와 Single Block I/O 방식으로 디스크를 읽습니다.
랜덤하게 엑세스하며 매번 sleep하게 됩니다.
한블록에 평균적으로 500개 레코드가있으면, 같은 블록을 최대 500번 읽는 셈입니다.

인덱스는 중요합니다. 하지만 인덱스가 항상 답은 아닙니다.

버퍼 캐쉬 탐색 매커니즘

<img src="https://static.podo-dev.com/blogs/images/2020/06/27/origin/336c1257-b783-4c95-820e-083efaea7784.png" alt="base64.png" style="width:459px;">

체인 해쉬 알고리즘을 사용합니다.

  • 같은 입력값은 항상 동일한 체인
  • 다른 입력값이 동일한 해시에 연결될수 있음
  • 해시 체인내에서는 정렬 보장하지 않음

문제는 하나의 버퍼블록을 동시에 접근 할때 발생합니다.
따라서 캐시 체인 에 접근하는 특정 순간에 한 프로세스만 사용 할 수 있도록, 줄서서 기다려야합니다.
이것을 지원하는 매커니즘을 래치라 합니다.

캐시버퍼 체인뿐만 아니라, 버퍼블록자체에도 직렬화 알고리즘이 들어합니다.
직렬화 매커니즘에 의해 캐시 경합을 줄이려면, SQL 튜닝을 통해 쿼리 일량(논리적 I/O)를 줄여야 합니다.

CommentCount 0
이전 댓글 보기
등록
이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.
TOP