[SQL 레벨업] 1장: DBMS 아키텍처

📍 1장의 목표: RDB의 내부적인 동작 모델 이해

DBMS 아키텍처 개요

1. DBMS의 일반적인 아키텍처 개요

DBMS를 통해 SQL 구문이 실행되어 데이터를 '다양하게 처리' 가능

2. DBMS 내부 기능

쿼리 평가 엔진

사용자로부터 입력받은 SQL 구문 분석, 어떤 순서로 기억장치의 데이터에 접근할 지 실행 계획을 결정 -> DBMS의 핵심 기능 담당

접근 메서드(access method): 실행 계획에 기반을 두어 데이터에 접근하는 방법

쿼리(query): 좁은 의미로는 SELECT 구문을, 넓은 의미로는 SQL 구문 전체를 나타냄

버퍼 매니저

특별한 용도로 사용하는 메모리 영역인 버퍼를 관리

디스크 용량 매니저

데이터의 저장 위치와 방법을 관리, 읽고 쓰기를 제어

트랜잭션 매니저와 락 매니저

데이터베이스에 접근하는 각각의 처리는 DBMS 내부에서 트랜잭션이라는 단위로 관리됨

트랜잭션의 정합성을 유지하면서 실행시킴, 필요할 경우 데이터에 락을 걸어 다른 요청을 대기시킴

리커버리 매니저 

시스템에 장애가 발생하는 것을 대비하여 데이터를 정기적으로 백업, 문제 발생 시 복구하는 역할 수행

 

💡하나의 SQL 구문을 독립적을 실행할 때의 성능에 대해 중점을 둠

💡성능과 밀접한 관련을 가지는 기능
  ☑️ 쿼리 평가 엔진(+ 실행 계획)
  ☑️ 버퍼 매니저

---

DBMS와 버퍼

1. 트레이드 오프

기억 비용: 데이터 저장에 소모되는 비용

	종류	접근 속도	기억 비용
1차 기억 장치	레지스터, 메모리	빠름	높음
2차 기억 장치	HDD, CD, DVD, 플래시 메모리	보통	보통
3차 기억 장치	테이프	느림	낮음

많은 데이터를 영속적으로 저장하려면 속도를 잃고, 속도를 얻고자 하면 영속성을 잃음 -> trade off

2. DBMS 와 기억장치의 관계

하드디스크

DBMS가 데이터를 저장하는 매체의 대부분이 하드디스크임

어떤 상황에서도 평균적인 수치를 가지는 매체

메모리

규모있는 상용 시스템의 데이터베이스 내부 데이터를 모두 메모리에 올리는 것은 불가능

저장 공간이 아무리 많아도 100GB를 넘는 경우는 거의 없음

버퍼를 활용한 속도 향상

성능 향상을 위해 일부 데이터를 버퍼 or 캐시 (매체: 메모리)에 올림

자주 접근하는 데이터를 메모리에 올려두면 빠른 속도로 데이터 처리 가능

-> 버퍼 매니저는 고속 접근이 가능한 버퍼에 '데이터를 어떻게, 어느 정도의 기간동안 올릴지' 관리하여 성능에 직접적인 영향을 줌

3.  메모리의 버퍼

데이터 캐시

디스크에 있는 데이터의 일부를 메모리에 유지하기 위해 사용하는 메모리 영역

SQL문에서 찾는 데이터가 모두 데이터 캐시에 있다면 빠른 처리 가능

로그 버퍼

갱신과 관련된 SQL구문이 들어올 경우, 로그 버퍼 위에 변경 정보를 보낸 후 디스크에 변경을 수행

갱신 처리는 SQL 구문의 실행 시점과 저장소에 갱신하는 시점에 차이가 있는 비동기 처리

🙋🏻‍♀️ 비동기 처리를 하는 이유? 갱신 시 소모되는 시간동안 사용자가 대기하지 않도록 하기 위함

4.  메모리의 성질에 의한 트레이드 오프

휘발성

DBMS를 껐다 켜면 버퍼 위의 모든 데이터가 사라짐 -> 메모리에 영속성 기능이 없는 이상 디스크를 대체할 수 없음

장애 발생 시 로그 버퍼 위의 사용자의 갱신 정보 데이터가 사라지면 큰 문제 발생

-> 커밋(commit)을 사용

•  갱신 정보를 반드시 영속적인 저장소에 있는 로그 파일에 써 갱신 처리를 확정
• (+) 장애가 발생해도 정합성 유지 가능
• (-) 디스크에 동기 접근이 일어나 성능이 낮아짐

5.  시스템 특성에 의한 트레이드 오프

데이터 캐시와 로그 버퍼의 크기

DBMS의 버퍼는 보통 다음과 같이 할당됨: (데이터 캐시 초깃값)  > (로그 버퍼의 초깃값)

이유: 데이터베이스가 기본적으로 검색 기능을 메인으로 처리한다고 가정하기 때문

• 검색 처리 시 레코드가 수백만~수천만 건에 달하는 경우 많음
• 갱신 처리 시 갱신 대상 레코드가 많아봐야 트랜잭션마다 한 건~ 수만 건에 달함

결론적으로, 자주 검색하는 데이터를 캐시에 올려놓는 것이 효율적 +) 만약 시스템이 검색에 비해 갱신이 많다면 튜닝하여 사용 가능

검색과 갱신 중 중요한 것

검색, 갱신 중 어떤 것을 우선하여 메모리 관리를 할 것인지 판단해야 함

 

설계된 데이터베이스의

• 로그 버퍼 비중이 크다 -> 갱신 처리와 관련해 큰 부하가 걸릴 것을 고려한 설계
• 데이터 캐시 비중이 크다 -> 검색 처리와 관련된 처리가 중심

6.  추가적인 메모리 영역 '워킹 메모리'

워킹 메모리

로그 버퍼, 데이터 캐시 외에 정렬 또는 해시 관련 처리에 사용되는 작업용 영역

처리가 종료되면 해제되는 임시 영역

다루려는 데이터 양보다 워킹 메모리 영역이 작으면 저장소(일시 영역)를 사용, 일시 영역은 접근 속도가 느림

정렬: ORDER BY 구, 집합 연산, 윈도우 함수 등 기능을 사용할 때 실행

해시: 테이블 등의 결합에서 해시 결합이 사용될 때 실행

저장소가 부족해지면?

저장소에 접근하면 전체적인 속도가 느려지지만 메모리가 부족하다고 처리가 멈추거나 에러가 발생하지는 않음

 

여러 개의 SQL 구문들이 공유해 사용하는 영역이므로 부하 검사(하나의 SQL구문 실행 시 잘 동작하지만 여러개를 실행 시 메모리 넘치는 상황을 재현하는 검사)를 실시하지 않는 이상 메모리 부족 현상을 예측하기 어려움

4장의 GROUP BY, 6장의 해시 결합과 관련도 ⬆️

DBMS와 실행 계획

1.권한 이양의 좌악

SQL은 어디에 저장된 데이터를 어떻게 찾고 처리할 지에 대한 세부 사항을 전혀 기술하지 않고 비절차적으로 사용

사용자는 데이터에 접근하기 위한 절차(how)를 책임지고 기술하지 않고, 대상(what)을 기술하기만 하면 됨

비즈니스 전체의 생산성을 향상

2. 데이터에 접근하는 방법 결정하기

데이터 접근 절차를 결정하는 쿼리 평가 엔진이 사용자로부터 입력받은 SQL 구문을 처음 읽어들임

쿼리 평가 모듈은 추가로 파서 또는 옵티마이저 같은 여러 개의 서브 모듈로 구성됨

파서

사용자로부터 입력받은 SQL 구문을 분석, 검사

SQL 구문을 정형적인 형식으로 변환해주어 DBMS 내부에서 일어나는 후속 처리 효율화

옵티마이저

데이터 접근법(실행 계획)을 최적화: DBMS의 핵심

인덱스 유무, 데이터 분산 또는 편향 정도, DBMS 내부 매개변수 등의 조건을 고려해

1.  선택 가능한 많은 실행 계획을 작성
2.  각 실행 계획의 비용을 연산

가장 낮은 비용을 가진 실행 계획 선택

카탈로그 매니저

옵티마이저가 실행 계획을 세울 때 중요한 정보를 제공

카탈로그: DBMS의 내부 정보를 모아놓은 테이블들, 테이블 또는 인덱스의 통계 정보 저장

플랜 평가

SQL 구문에서 세운 실행 계획들 중 최적의 실행 결과를 선택하는 과정

성능이 좋지 않은 SQL 구문이 있을 때 실행 계획을 읽고 수정 방안 등 고려 가능

 

실행 계획이 선택되면 DBMS는 실행 계획을 절차적인 코드로 변환하고 데이터 접근을 수행함

3. 옵티마이저와 통계 정보

카탈로그 매니저가 관리하는 통계 정보는 데이터베이스 엔지니어가 항상 신경써야 함

통계 정보가 부족한 경우 옵티마이저에서 최적의 플랜을 선택하지 못할 수 있음

카탈로그에 포함된 통계 정보
- 각 테이블의 레코드 수
- 각 테이블의 필드 수와 필드 크기
- 필드의 카디널리티(값의 개수)
- 필드값의 히스토그램(값의 분포)
- 필드 내부의 NULL 수
- 인덱스 정보

카탈로그 정보가 실제와 일치하지 않을 때, 테이블의 데이터 삽입/갱신/제거 시 카탈로그 정보가 갱신되지 않을 때 -> 옵티마이저는 과거 정보로 계획을 세우므로 최적의 플랜을 세울 수 없음

4. 최적의 실행 계획 작성

테이블의 데이터가 많이 바뀌면 카탈로그의 통계 정보도 갱신해야 함

통계 정보 갱신은 대상 테이블, 인덱스의 크기와 수에 따라 몇십분~몇시간이 소요되는 실행비용이 높은 작업

최적의 플랜 선택에 필요한 조건이므로 갱신 시점의 확실한 검토 필요

 

대표적인 DBMS의 통계 정보 갱신 명령어

이름	명령어
Oracle	exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => [스키마 이름], TABNAME => [테이블 이름]);
Microsoft SQL Server	UPDATE STATISTICS [테이블 이름]
DB2	RUNSTATS ON TABLE [스키마 이름], [테이블 이름];
PostgreSQL	ANALYZE [스키마 이름], [테이블 이름];
MySQL	ANALYZE TABLE [스키마 이름], [테이블 이름];

실행 계획이 SQL 구문의 성능을 결정

1.실행 계획 확인 방법

데이터가 많은 테이블에 접근하거나 복잡한 SQL 구문 실행 시 최적의 경로이더라도 지연 발생

-> 가장 먼저 실행 계획을 살펴봐야 함

 

실행 계획을 확인하는 명령어

이름	명령어
Oracle	set autotrace traceonly
Microsoft SQL Server	SET SHOWPLAN_TEXT ON
DB2	EXPLAIN ALL WITH SNAPSHOT FOR SQL 구문
PostgreSQL	EXPLAIN SQL 구문
MySQL	EXPLAIN EXTENDED SQL 구문

2.테이블 풀 스캔의 실행 계획

레코드 전체를 검색하는 SQL 구문의 실행 계획

EXPLAIN
SELECT * FROM Shops;

조작 대상 객체

사용하는 테이블을 출력

여러 테이블을 사용할 때 혼동하지 않도록 주의

테이블 외에 인덱스, 파티션, 시퀀스처럼 SQL 구문으로 조작 가능한 객체도 대상이 될 수 있음

객체에 대한 조작 종류

실행 계획에서 가장 중요한 부분

Oracle: 'Operation'필드에 나타남, 'TABLE ACCESS FULL' 테이블의 데이터 전부 읽어들임

PostgreSQL: 'Seq Scan' 순차적인  접근의 줄임말, 파일을 순차적으로 접근해 테이블의 데이터 전체를 읽어냄

 

실질적으로 Oracle도 시퀀셜 스캔을 하므로 두 가지가 같다고 생각해도 됨, 이러한 형태의 접근법을 '테이블 풀 스캔'이라고 부름

실행 계획에 출력되는 비용, 실행 시간, 처리 레코드 수는 추정값이므로 지표로 사용하면 안됨
+) 실제 값을 검색하는 방법이 있는 DBMS도 있음

조작 대상이 되는 레코드 개수

Rows라는 항목에 출력됨

결합 또는 집약이 포함되면 1개의 구문을 실행해도 여러 개의 조작이 수행됨

SQL 구문 전체의 실행 비용 파악 시 각 조작에서 얼마만큼의 레코드가 처리되는지가 중요한 지표임

실제 SQL 구문을 실행한 시점의 테이블 레코드 수와 카탈로그 정보가 다를 수 있음 -> 옵티마이저에 실시간 테이블 정보 반영 X

3. 인덱스 스캔의 실행 계획

EXPLAIN
SELECT * FROM Shops WHERE shop_id='00050';

조작 대상이 되는 레코드 개수

기본키인 id를 지정했으므로 접근 대상은 레코드 한 개임

접근 대상 객체와 조작

PostgreSQL: 'Index Scan'

Oracle: 'INDEX UNIQUE SCAN' - 모집합 레코드 수에서 선택되는 레코드 수가 적다면 테이블 풀 스캔보다 빠르게 접근 수행

인덱스를 사용해 스캔을 수행한다는 의미

레코드 수가 특정 데이터양(N)보다 많은 양일 경우 인덱스 스캔이 효율적인 접근을 하게 됨

4. 간단한 테이블 결합의 실행 계획

SQL에서 지연이 일어나는 경우는 대부분 결합과 관련됨

EXPLAIN
SELECT * FROM Shops S INNER JOIN Reservations R ON S.shop_id=R.shop_id;

DBMS에서 결합 시 사용되는 일반적인 세가지 알고리즘

Nested Loops: 한쪽 테이블을 읽으며 레코드 하나마다 결합 조건에 맞는 레코드를 다른 쪽 테이블에서 찾음 (이중 반복)

Sort Merge: 결합 키로 레코드를 정렬하고 순차적으로 두 테이블을 결합, 정렬 시 워킹 메모리를 사용함

Hash: 결합 키 값을 해시값으로 매핑, 해시 테이블을 만들어야 하므로 작업용 메모리 영역 필요

객체에 대한 조작 종류

Oracle, PostgreSQL 모두 'nested loops' 알고리즘 사용

Reservation 테이블과 Shop 테이블 접근이 같은 중첩 단계에 있지만 Reservation 테이블에 먼저 접근함

실행 계획의 중요성

옵티마이저의 실행 계획이 최적의 방법이 아니라면 개발자가 힌트 구와 같은 기능으로 강제 명령할 수 있도록 함

 

SQL 구문들이 어떤 접근 경로로 데이터를 검색하는지,

어떤 테이블 설정이 효율적인지,

어떤 SQL 구문이 주어졌을 때 어떤 실행 계획이 나올지 예측할 수 있어야 실행 계획을 변경할 수 있다