SQL 레벨업 1장

devyu·2023년 10월 29일

💡Deep Dive to DB

목록 보기

7/8

sql 레벨업 1 2강 정리

1. DBMS 아키텍처

공짜 밥은 존재할까?

DBMS 아키텍처 개요

쿼리 평가 엔진
계획을 세우고 실행하는 DBMS의 핵심 기능 담당
버퍼 매니저
버퍼 (메모리)영역을 관리
디스크 용량 매니저
어디에 어떻게 데이터 저장할지, 데이터의 읽고 쓰기를 제어
트랜잭션 매니저와 락 매니저
트랜잭션의 정합성 유지 및 실행. 필요시 데이터에 락을 걸어 다른 요청 대기
리커버리 매니저
데이터 정기적 백업, 문제시 복구

성능의 관점에서 가장 중요한 것은 쿼리 평가 엔진 (혹은 이 엔진이 세우는 실행 계획)
버퍼 매니저는 성능과 밀접한 관련을 가짐.
책에서는 하나의 SQL구문을 도립적으로 실행할 때의 성능을 중심으로 설명. 동시 실행시 발생하는 충돌 X

DBMS와 버퍼

DBMS의 버퍼 매니저가 어떤 기능을 수행하는가: 버퍼는 성능에 매우 중요한 영향을 미침.
메모리는 한정 but 데이터베이스가 메모리에 저장하고자 하는 데이터는 매우 많음 → 트레이드 오프 발생 (데이터를 버퍼에 어떠한 식으로 확보할 것인가)

1. 공짜 밥은 존재할까?

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

기억 비용에 따른 피라미드

* 피라미드의 위 아래는 우수한 기억장치를 나누는 기준 아님.

속도 <-> 많은 데이터 저장 사이의 트레이드 오프 발생

2. DBMS와 기억장치의 관계

DBMS는 데이터 저장을 목적으로 하는 미들웨어.

하드디스크(HDD)
DBMS가 데이터를 저장하는 매체(저장소)는 현재 대부분 HDD다.(용량, 비용, 성능의 관점)

하드디스크는 기억장치 계층에서 가운데 있는 2차 기억장치로 분류: 2차 기억장치는 그렇게 좋은 장점도 없지만, 그렇게 나쁜 단점도 없는 매체.

DB는 대부분의 시스템에서 범용적으로 사용하는 미들웨어이므로, 어떤 상황에서도 평균적인 수치를 가지는 매체를 선택하는 것이 자연스럽다.

but DBMS가 데이터를 디스크 이외에 장소에 저장하지 않는다는 뜻 X. 일반적인 DBMS는 항상 디스크 이외의 장소에도 데이터를 올려놓음. -> (1차 계층의 기억장치 메모리)

메모리
메모리는 디스크에 비해 기억비용이 굉장히 비쌈. 따라서 하드웨어 1대에 탑재할 수 있는 양이 크지 않음. 일반적인 DB서버에 경우 탑재되는 메모리의 양은 한두 자리 정도 -> 상용 시스템의 DB 내부 데이터를 모두 메모리에 올리는 것은 불가능.

버퍼를 활용한 속도 향상
그렇지만 DBMS가 일부라도 데이터를 메모리에 올리는 것은 성능 향상 때문. 메모리는 가장 빠른 1차 기억장치. 따라서 자주 접근하는 데이터를 메모리 위에 올려둔다면, 같은 SQL 구문을 실행한다고 해도 디스크에서 데이터를 가져올 필요 없이 곧바로 메모리에서 읽어 빠르게 데이터 검색 가능.

디스크 접근을 줄일 수 있다면 굉장히 큰 폭의 성능 향상이 가능하다.

SQL구문의 실행 시간을 대부분 저장소 I/O에 사용하기 때문이다.

버퍼와 캐시
이렇게 성능 향상을 목적으로 데이터를 저장하는 메모리를 버퍼 또는 캐시라고 부름.

버퍼: 완충제라는 의미인데 사용자와 저장소 사이에서 SQL구문의 디스크 접근을 줄여주는 역할을 함.

캐시: 사용자와 저장소 사이에서 데이터 전송 지연을 완화.

모두 물리적인 매체로 메모리가 사용되는 경우가 많고, 하드디스크 위에 있는 데이터에 접근하는 것보다 훨씬 빠름. 이러한 고속 접근이 가능한 버퍼에 데이터를 어떻게, 어느 정도의 기간동안 올릴지를 관리하는 것이 DBMS의 버퍼 매니저.

3. 메모리 위에 있는 두 개의 버퍼

DBMS가 데이터를 유지하기 위해 사용하는 두 종류의 메모리:

데이터 캐시
로그 버퍼

대부분의 DBMS는 이러한 두 개의 역할을 하는 메모리 영역을 가짐.

데이터 캐시
데이터 캐시는 디스크에 있는 데이터의 일부를 메모리에 유지하기 위해 사용하는 메모리영역. 내가 실행한 select 구문에서 선택하고 싶은 데이터가 데이터 캐시에 있다면 디스크와 같은 저속 저장소에 접근하지 않고 처리가 수행되기 때문에 빠르게 응답함

반대로 버퍼에서 데이터를 찾을 수 없다면 저속 저장소까지 데이터를 가지러 가야하기 때문에 SQL구문의 응답속도가 느려짐.

로그버퍼
로그 버퍼는 갱신처리(select, delete, update, merge)와 관련이 있음.

DBMS는 갱신과 관련된 SQL 구문을 사용자로부터 받으면, 곧바로 저장소에 있는 데이터를 변경하지 않음. 로그 버퍼 위에 변경 정보를 보내고 이후 디스크에 변경을 수행.(갱신 처리는 비동기로)

이렇게 데이터베이스의 갱신 처리는 비동기 처리임. -> DBMS가 이러한 시점 차이를 두는 이유는 성능을 높이기 위해서임.
즉 DBMS는 "저장소의 느림을 어떻게 보완한 것인가" 를 계속해서 고민해온 미들웨어.

4. 메모리의 성질이 초래하는 트레이드오프

메모리가 가진 단점은 가격이 비싸서 보유할 수 있는 데이터양이 적은 것 뿐만 아니라 몇가지 단점이 더 존재함.

휘발성
메모리에는 데이터의 영속성이 없다. 하드웨어의 전원을 꺼버리면 메모리 위에 올라가 있는 데이터가 모두 사라진다. 이러한 성질을 휘발성이라고 한다.

DBMS을 껐다 켜면 버퍼위의 모든 데이터가 사라짐 -> DBMS에 어떤 장애가 발생해서 프로세스다운이 일어나면(= 서버가 죽으면) 메모리 위에 있는 모든 데이터가 날아감.

휘발성의 위험성
휘발성의 가장 큰 문제점은 장애가 발생했을 때 메모리에 있는 데이터가 모두 사라져버려 데이터 부정합을 발생시킴.

데이터 캐시라면 장애로 인한 메모리 위의 데이터가 사라져버려도, 원본 데이터는 디스크 위에 남아있으므로 아무 문제가 없음.

하지만 로그 버퍼 위에 존재하는 데이터가 디스크 위의 로그 파일에 반영되기 전 장애가 발생해 사라져 버리면 해당 데이터가 완전히 사라져서 복구조차 불가능. 이는 사용자가 수행했던 갱신 정보가 사라진다는 의미

이러한 문제를 회피하고자 DBMS는 커밋 시점에 반드시 갱신 정보를 로그파일에 씀으로써, 장애가 발생해도 정합성을 유지할 수 있게 한다.

커밋(Commit)이란 갱신 처리를 확정하는 것으로 DBMS는 커밋된 데이터를 영속화 함. 커밋 때는 반드시 디스크에 동기 접근이 일어남. 여기에서 또다시 트레이드오프가 모습을 드러냄.

디스크에 동기 처리를 한다면 데이터 정합성은 높아지고 성능이 낮아짐. 반대로 성능을 높이러면 데이터의 정합성이 낮아짐.

이름	데이터 정합성	성능
동기 처리	O	X
비동기 처리	X	O

5. 시스템 특성에 따른 트레이드 오프

데이터 캐시와 로그 버퍼의 크기
DBMS를 보면 데이터 캐시에 비해 로그 버퍼의 초깃값이 굉장히 작음 -> 이렇게 할당한 이유는 DB가 기본적으로 검색을 메인으로 처리한다고 가정하기 때문.

갱신 처리에 값비싼 메모리를 많이 사용하는 것보다는 자주 검색하는 데이터를 캐시에 올려놓는 것이 좋다고 생각하기 때문.

데이터베이스는 검색을 중시한 메모리 배분이 기본.

검색과 갱신 중에서 중요한 것
검색과 갱신 중에서 어떤 것이 더 우선되어야 하는가? 트레이드오프 발생.

데이터베이스가 어떠한 생각에 기반을 두어 리소스를 배분하고 있는지 이해하는 것이 중요.

로그 버퍼가 큼 - 갱신 처리에 큰 부하
데이터 캐시가 큼 - 검색 처리에 큰 부하

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

언제 사용될까?
DBMS는 메모리 영역을 하나 더 가지고 있는데 정렬 또는 해시 관련 처리에 사용되는 작업용 영역으로 워킹 메모리(working memory)라고 함.

정렬은 ORDER BY구, 집합 연산, 윈도우 함수 등의 기능을 사용할 때 실행.
해시는 주로 테이블의 결합에서 해시 결합이 사용되는 때 실행.

* 이 메모리 영역의 이름과 관리 방법은 DBMS에 따라 다름

이 작업용 메모리 영역은 SQL에서 정렬 또는 해시가 필요한 때 사용되고, 종료되면 해제되는 임시영역으로, 일반적으로는 데이터 캐시와 로그 버퍼와는 다른 영역으로 관리되는 경우가 많음.

이 영역이 성능적으로 중요한 이유는 만약 이 영역이 다루려는 데이터양보다 작아 부족해지는 경우 대부분의 DBMS가 저장소를 사용하기 때문. -> OS 동작에서 말하는 스왑과 같은 것.

이러한 시 영역들은 저장소 위에 있으므로 접근 속도가 느림.

저장소가 부족하면 무슨 일이 일어날까?
저장소는 메모리에 비해서 굉장히 느리다. 따라서 그런 곳에 접근하게 되면 전체적인 속도가 느려진다.

이 영역은 여러 개의 SQL 구문들이 공유해서 사용하므로, 하나의 SQL 구문을 실행하고 있을 때는 메모리가 잘 들어가지만 여러 개의 SQL 구문을 동시에 실행하면 메모리가 넘치는 경우가 있다. 하나가 있을 때의 성질뿐만 아니라, 여러 개가 있을 때의 성질도 주의해야 한다는 것이 컨트롤하기 힘든 성능 문제.

DBMS는 메모리가 부족하더라도 무언가를 처리하려고 계속 노력하는 미들웨어라고 생각할 수 있음.

DB는 메모리가 부족하다는 이유로 SQL 구문에 오류를 절대 발생시키지 않음 - DBMS가 중요한 데이터를 보관/처리할 때 운영체제 정도의 급으로 처리 계속성을 담보하려 하기 때문.

DBMS와 실행 계획

사용사 혹은 개발자가 데이터베이스에서 의식적으로 사용하는 것은 SQL레벨까지. 이후는 SQL구문을 읽어들인 DBMS가 알아서 처리 후 결과 제공. -> 사용자는 데이터가 있는 곳을 알 필요 X. 데이터에 접근하는 방법도 생각하지 X.

이는 프로그래밍과는 거리가 멈. SQL은 어디에 있는 데이터를 어떻게 찾고 처리할지 등의 절차적인 세부사항 작성이 필요 X.

1. 권한 이양의 죄악

이는 언어의 설계 사상 차이일 뿐, 좋고 나쁨을 구분하는 것이 아님

절차 기초 언어(C, Java등): HOW를 책임지고 기술하는 것이 전제.
비 절차적인 RDB: WHAT을 기술하는 것. (나머지는 시스템이 처리)

-> 비즈니스 전체의 생산성 향상을 위한 선택.
그러나 반은 맞고 반은 틀림. SQL은 간단한 언어가 아니며, HOW를 의식하지 않기 때문에 성능 문제가 발생함 -> RDB의 내부 절차를 들여다봐야 하는 이유

2. 데이터에 접근하는 방법은 어떻게 결정할까?

쿼리 평가 엔진: RDB에서 데이터 접근 절차를 결정하는 모듈.

DBMS의 쿼리 처리 흐름

파서(parser)
이름 그대로 파스(구문 분석)하는 것. 사용자의 SQL구문이 항상 구문적으로 올바르다는 보증이 없으므로 검사진행.

SQL 구문을 정형적인 형식으로 변환. -> DBMS 내부에서 일어나는 후속 처리의 효율화를 위함.

옵티마이저(optimizer)
옵티마이저는 최적화라는 의미를 지님. 최적화의 대상은 데이터 접근법(실행 계획). DBMS 두뇌의 핵심.

선택 가능한 많은 실행 계획을 작성 - 이 비용을 연산 - 가장 낮은 비용의 실행 계획을 선택

카탈로그 매니저
카탈로그: DBMS의 내부 정보를 모아놓은 테이블들. 테이블 또는 인덱스의 통계 정보 저장.(카탈로그가 통계 정보로 불리기도 함)

플랜 평가(plan evaluation)
옵티마이저의 실행 계획을 받아 최적의 실행 결과를 선택하는 것.
이 실행 계획은 DBMS가 실행할 수 있는 코드가 아닌 문자 그대로의 계획서. 성능이 좋지 않은 SQL구문이 있을 때 실행 계획을 읽고, 수정 방안 등을 고려할 수 있음.

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

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

카탈로그 매니저가 관리하는 통계 정보에 대해서는 데이터베이스 엔지니어가 신경을 써줘야. (만능이 아니기 때문)

카탈로그에 포함된 콩계 정보

각 테이블의 레코드 수
각 테이블의 필드 수 / 크기
필드의 카디널리티(값의 개수)
필드값의 히스토그램(어떤 값이 얼마나 분포되어 있는가)
필드 내부에 있는 NULL수
인덱스 정보

이러한 카탈로그 정보가 테이블 또는 인덱스의 실제와 일치하지 않을 때 문제가 발생. 테이블에서 수행한 내용에 따라 카탈로그 정보가 갱신되지 않을때, 옵티마이저는 오래된 정보를 바탕으로 실행 계획 세움 -> 당연히 잘못된 계획 (Garbage In, Garbage Out)

4. 최적의 실행 계획이 작성되게 하려면

SQL성능에 있어 올바른 통계 정보가 모이는 것은 매우 중요. 테이블의 데이터가 많이 바뀌면 카탈로그의 통계 정보도 함께 갱신해야.

데이터를 크게 생신하는 배치 처리가 있을때는 Job Net을 조합하거나, Oracle 처럼 기본 설정에서 정기적으로 통계 정보 생긴 작업이 수행되는 경우도 존재. 혹은 Microsoft SQL Server처럼 생신 처리가 수행되는 시점에 자동으로 통계 정보를 갱신하는 DBMS도 존재.

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

DBMS가 최적의 플랜은 선택하려면 필요한 조건이므로 갱신 시점을 확실하게 검토해야.

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

1. 실행 계획 확인 방법

SQL 구문의 지연 발생시 제일 먼저 실행 계획을 살펴봐야.
기본적인 SQL구문의 실행 계획
1. 테이블 풀 스캔(Full Scan)의 실행 계획
2. 인덱스 슼매의 실행 계획
3. 간단한 테이블 결합의 실행 게획

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

레코드 전체 검색을 위한 SQL구문의 실행 계획에서 공통적으로 나타나는 부분
1. 조작 대상 객체
2. 객체에 대한 조작의 종류
3. 조작 대상이 되는 레코드 수

조작 대상 객체
테이블 이외에 인덱스, 파티션, 스퀀스와 같은 SQL구문으로 조작할 수 있는 객체는 무엇이든 올 수 있음

객체에 대한 조작의 종류
실행 계획에서 가장 중요한 부분. Oracle에서 Operation 피드이며, PostgreSQL의 Seq Scan은 순차적인 접근을 의미. 파일을 순차적으로 접근해서 해당 테이블의 데이터 전체를 읽어낸다는 의미. Oracle의 TABLE ACCESS FULL은 테이블의 데이터를 전부 읽어들인다는 의미.

조작 대상이 되는 레코드 수
두가지 DBMS 모두 Rows라는 항목에 출력됨. 결합 또는 집략이 포함되면 1개의 SQL구문을 실행해도 여러 개의 조작이 수행. 각 조작에서 얼마만틈의 레코드가 처리되는지가 SQL구문 전체의 실행 비용을 파악하는데 중요한 지표가 됨.

이때 숫자는 옵티마이저가 실행 계획을 만들떄 설명했던 카탈로그 매니저로부터 얻은 값임. 통계 정보에서 파악한 숫자이므로 실제 SQL구문을 실행한 시점의 테이블 레코드 수와 차이가 있을 수 있음.

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

SELECT *
	FROM Shops
    WHERE shop_id = '00050';

이때 나타나는 변화를 3개로 나누면

조작 대상이 되는 레코드 수
두 DBMS모두 Rows가 1로 변경. WHERE 구에서 기본키가 '00050'인 점포를지정했으므로 접근 대상은 반드시 레코드 하나임.

접근 대상 객체와 조작
postgreSQL에서는 Index Scan, Oracle에서는 INDEX UNIQUE SCAN이라는 조작이 발생. 인덱스를 사용해 스캔을 수행한다는 것.

일반적으로 인덱스는 스캔하는 모집합 레코드 수에서 선택되는 레코드 수가 적다면 테이블 풀 스캔보다 빠르게 접근을 수행. -> 풀 스캔이 모집합의 데이터양에 비례해서 처리 비용이 늘어나는 것에 반해 인덱스를 사용할 때 활용되는 B-tree가 모집합의 데이터양에 따라 대수 함수적으로 처리 비용이 늘어나기 떄문.

모집합의 데이터가 많을수록 인덱스 스캔이 좋음

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

SQL에서 지연이 일어나는 경우는 대부분 결합과 관련된 것. 따라서 결합 시점의 시행 계획 특성을 공부하는 것은 굉장히 중요한 의미가 있다.

실행 계획을 검색할 대상 SQL은 아래와 같다. 예약이 존재하는 점포를 선택하는 SELECT 구문

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

DBMS가 결합시 사용하는 세가지 종류의 알고리즘

Nested Loops
한쪽 테이블을 읽으면서 레코드 하나마다 결합 조건에 맞는 레코드를 다른 쪽 테이블에서 찾는 방식. 절차 지향형 언어에서 이중 반복으로 구현되기 때문에 해당 이름이 붙음
Sort Merge
결합 키로 레코드를 정렬하고 순차적으로 두 개의 테이블을 결합하는 방법. 결합 전 전처릴로 정렬을 수행해야 함. (이때 작업용 메모리로 워킹 메모리를 사용)
Hash
결합 키값을 해시값으로 맵핑하는 방법. 해시 테이블을 만들어야 하므로 작업용 메모리 영역을 필요로 함.

객체에 대한 조작의 종류
Oracle에서는 NESTED LOOP 라고 나오고, postgreSQL에서도 Nested Loop 라고 나오기 때문에 같은 알고리즘을 선택함을 알 수 있음.

+ 실행 계획을 읽는 방법
실행 계획은 일반적으로 트리 구조이고, 중첩 단계가 싶을수록 먼저 실행.

결합 전에 테이블 접근이 먼저 수행되는데, 이때 결합이 어떤 테이블에 먼저 접근하는지가 중요한 의미를 가짐: 같은 중첩 단계에서 위에서 아래로 실행.

실행 계획의 중요성

옵티마이저는 우수하지만 완벽하지 않다. 그 이전에 정보를 제대로 주지 못할수도 있다. (ex. 인덱스를 사용해야 빨라지는 데 사용하지 않거나, 테이블 결합 순서를 이상하게 적는 등)

이러한 경우 최후의 튜닝수단을 사용 -> 실행 계획을 수동으로 변경하는 것.

Oracle이나 MySQL등에서 가지는 힌트 구. 이를 사용하면 SQL 구문에서 옵티마이저에게 강제적으로 명령 가능.

실행 계획을 변경하려면 어떤 선택지가 있는지를 알아야 함

devyu

티스토리와 벨로그 사이 줄타기....

다음 포스트