그림에서 위에 있는 것은 데이터베이스 사용자 (일반 사용자 또는 프로그래머)와의 인터페이스를 나타낸다 여기서 전달된 SQL구문은 중간에 있는 DBMS를 통해 다양한 처리를 수행한다. 그리고 저장 장치에 있는 데이터에 접근해서 데이터를 읽고 쓰게 된다.

여기서 관심을 가질 부분은 중간에 있는 DBMS에서 일어나는 ‘다양한 처리’이다.

1. 쿼리 평가 엔진

쿼리 평가 엔진은 사용자로부터 입력받은 SQL 구문을 분석하고, 어떤 순서로 기억장치의 데이터에 접근할지를 결정한다. 이때 결정되는 계획을 ‘실행 계획’ (또는 ‘실행 플랜’)이라 부른다. 이러한 실행 계획에 기반을 둬서 데이터에 접근하는 방법을 ‘접근 메서드 (access method)‘라고 부른다. 한마디로 쿼리 평가 엔진은 계획을 세우고 실행하는 DBMS의 핵심 기능을 담당하는 모듈이다.

추가로 ‘쿼리(query)‘는 ‘질의’를 의미하는 영어 단어이다. 좁은 의미로는 SELECT 구문을 나타내는 말이며 큰 의미로는 SQL 구문 전체를 나타낸다.

2. 버퍼 매니저

DBMS는 버퍼라는 특별한 용도로 사용하는 메모리 영역을 확보해둔다. 이 메모리 영역을 관리하는 것이 바로 버퍼 매니저이다.

버퍼 매니저는 디스크를 관리하는 디스크 용량 매니저와 함께 연동되어 작동한다. 이러한 매커니즘도 성능과 중요한 관련이 있다.

3. 디스크 용량 매니저

데이터베이스 프로그램 중에서도 가장 많은 데이터를 다루는 소프트웨어이다. 디스크 용량 매니저는 어디에 어떻게 데이터를 저장할지를 관리하며, 데이터의 읽고 쓰기를 제어한다.

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

데이터베이스는 수백에서 수천명의 사람이 동시에 데이터베이스에 접근해서 사용하게된다. 이때 각각의 처리는 DBMS 내부에서 트랜잭션이라는 단위로 관리된다. 이러한 트랜잭션의 정합성을 유지하면서 실행시키고, 필요한 경우 데이터에 락을 걸어 다른 사람의 요청을 대기시키는 것이 트랜잭션 매니저와 락 매니저의 역할이다.

5. 리커버리 매니저

시스템은 언제나 장애가 발생할 수 있다. 따라서 이러한 상황을 대비하려면 데이터를 정기적으로 백업하고, 문제가 일어났을 때 복구해줘야 하는데 이러한 기능을 수행하는 것이 리커버리 매니저이다.

DBMS와 버퍼

1. 공짜 밥은 존재할까?

기억장치는 기억 비용(또는 기억 코스트)에 따라 1차부터 3차까지의 계층으로 분류된다. 기억 비용이라고 하는 것은 간단하게 ‘데이터를 저장하는 데 소모되는 비용’을 나타낸다.

많은 데이터를 영속적으로 저장하려면 속도를 잃고, 속도를 얻고자 하면 많은 데이터를 영속적으로 저장하기 힘들다는 트레이드오프가 발생한다.

2. DBMS와 기억장치의 관계

하드디스크(HDD)

DBMS가 데이터를 저장하는 매체(저장소)는 현재 대부분 HDD이다. 하드디스크는 기억장치 계층에서 한가운데에 있는 2차 기억장치로 분류된다. 일반적인 DBMS는 항상 디스크 이외의 장소에도 데이터를 올려 놓는다. 바로 1차 계층의 기억장치 메모리에 말이다.

메모리

메모리는 디스크에 비해 기억 비용이 굉장히 비싸다. 일반적인 데이터베이스 서버의 경우 탑재되는 메모리 양은 한두 자리 정도이다. 따라서 규모 있는 상용 시스템의 데이터베이스 내부 데이터를 모두 메모리에 올리는 것은 불가능하다.

버퍼를 활용한 속도 향상

DBMS가 일부라도 데이터를 메모리에 올리는 것은 성능 향상 때문이다. 한마디로 SQL구문의 실행 속도를 빠르게 만들기 위함이라는 것이다. 따라서 자주 접근하는 데이터를 메모리 위에 올려둔다면, 같은 SQL 구문을 실행한다고 해도 디스크에서 데이터를 가져올 필요 없이 곧바로 메모리에서 읽어 빠르게 데이터를 검색할 수 있다.

디스크 접근을 줄일 수 있다면 굉장히 큰 폭의 성능 향상이 가능하다. 일반적인 SQL구문의 실행 시간 대부분을 저장소 I/O에 사용하기 때문이다.

이렇게 성능 향상을 목적으로 데이터를 저장하는 메모리를 버퍼(buffer) 또는 캐시(cache)라고 한다. 모두 물리적인 매체로 메모리가 사용되는 경우가 많다. 따라서 하드디스크 위에 있는 데이터에 접근하는 것보다 훨씬 빠르다.

이러한 고속 접근이 가능한 버퍼에 ‘데이터를 어떻게 어느 정도의 기간 동안 올릴지’를 관리하는 것이 DBMS의 버퍼 매니저이다. 이러한 것을 생각하면 버퍼 매니저가 데이터베이스의 성능에 괸장히 중요한 영향을 끼친다는 것을 쉽게 이해할 수 있다.

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

버퍼는 사용자가 용도에 따라 크기를 변경하는 것이 가능하다.

데이터 캐시

데이터 캐시는 디스크에 있는 데이터의 일부를 메모리에 유지하기 위해 사용하는 메모리 영역이다. SELECT구문에서 선택하고 싶은 데이터가 운 좋게 이러한 데이터 캐시에 있다면, 디스크와 같은 저속 저장소에 접근하지 않고 처리가 수행되며 따라서 굉장히 빠르게 응답한다.

반대로 운 나쁘게 버퍼에서 데이터를 찾을 수 없다면, 저속 저장소까지 데이터를 가지러 가야한다. 따라서 SQL 구문의 응답 속도가 느려진다.

로그 버퍼

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

이처럼 데이터베이스의 갱신 처리는 SQL구문의 실행 시점과 저장소에 갱신하는 시점에 차이가 있는 비동기 처리이다. SQL구문을 실행할 때 단순히 저장소 상의 파일을 바로 변경해버리는 편이 간단한 방법이나 그럼에도 DBMS가 이러한 시점 차이를 두는 이유는 역시 성능을 높이기 위해서이다. 저장소는 검색뿐만 아니라 갱신을 할 때도 상당한 시간이 소모된다. 따라서 저장소 변경이 끝날 때까지 기다리면 사용자는 장기간 대기하게 된다. 따라서 한번 메모리에 갱신 정보를 받은 시점에서 사용자에게는 해당 SQL구문이 ‘끝났다’라고 통지하고, 내부적으로 관련된 처리를 계속 수행하는 것이다.

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

휘발성

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

DBMS에 어떤 장애가 발생해서 프로세스다운이 일어나면(즉, 서버가 죽으면). 메모리 위에 있는 모든 데이터가 날아간다. 결국 미래에 메모리 가격이 엄청나게 싸진다고 해도 영속성이 없는 이상 기능적으로 디스크를 완전히 대체하는 것은 불가능하다.

휘발성의 문제점

휘발성의 가장 큰 문제점은 장애가 발생했을 때 메모리에 있던 데이터가 모두 사라져버려 데이터 부정합을 발생시키는 것이다. 데이터 캐시라면 장애로 인해 메모리 위에 데이터가 사라져버려도, 원본 데이터는 디스크 위에 남아있으므로 아무문제없다.

하지만 로그 버퍼 위에 존재하는 데이터가 디스크 위에 로그파일에 반영되기 전에 장애가 발생해서 사라져버린다면 해당 데이터가 완전히 사라져서 복구조차 불가능해질 것이다. 이는 사용자가 수행했던 갱신 정보가 사라진다는 의미이다. 이러한 문제는 비즈니스적인 관점에서 굉장히 심각한 문제이다. 은행 입출금 또는 카드 인출이 데이터베이스에 반영되지 않을 수도 있다.

그런데 로그 파일에 전달된 갱신 정보가 DBMS가 다운될 때 사라지는 현상은 DBMS가 갱신을 비동기로 하는 이상, 언제든 발생할 수 있는 문제이다. 따라서 이를 회피하고자 DBMS는 커밋 시점에 반드시 갱신 정보를 로그 파일(이는 영속적인 저장소 위에 존재)에 씀으로써, 장애가 발생해도 정합성을 유지할 수 있게 한다. 커밋(Commit)이란 갱신 처리를 ‘확정’하는 것이다. DBMS는 커밋된 데이터를 영속화 한다.

반대로 말하면 커밋 때는 반드시 디스크에 동기접근이 일어난다. 결국 여기서 지연이 발생할 가능성이 있다. 디스크에 동기 처리를 한다면 데이터 정합성은 높아지지만 성능은 낮아진다. 반대로 성능을 높이려면 데이터 정합성이 낮아진다. 이 두가지 선택 사항은 현재에도 많은 데이터베이스 엔지니어가 골머리를 썩히는 문제이다.

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

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

데이터 캐시와 로그 버퍼를 비교해보면 3개의 DBMS에서 공통으로 데이터 캐시에 비해 로그 버퍼의 초깃값이 괸장히 작다는 것을 알 수 있다.

데이터베이스가 2개의 버퍼에 대해 이렇게 극단적으로 비대칭적인 크기를 할당한 데는 명확한 이유가 있다. 이는 데이터베이스가 기본적으로 검색을 메인으로 처리한다고 가정하기 때문이다.

검색 처리를 할 떄는 검색 대상 레코드가 수백만에서 수천만 건에 달하는 경우도 많다. 하지만 갱신 처리를 할 때는 갱신 대상이 많아 봤자 트랜잭션마다 한 건에서 수만 건 정도 밖에 안된다. (물론 트랜잭션 규모에 따라서 다를 수 는 있다) 따라서 갱신 처리에 값비싼 메모리를 많이 사용하는 것보다는, 자주 검색하는 데이터를 캐시에 올려놓는 것이 좋다고 생각하는 것이다.

실제로 많은 DBMS가 물리 메모리에 여유가 있다면, 데이터 캐시를 되도록 많이 할당할 것을 추천하고 있다. 만약 우리가 만드는 시스템이 검색에 비해 갱신이 많다면, 초기 설정을 그대로 사용해서는 성능이 제대로 나오지 않을 수 있다. 그럴 때는 로그 버퍼의 크기를 늘려주는 튜닝(최적화)이 당연히 필요하다.

검색과 갱신 중에서 중요한 것

최근의 DBMS는 꽤 발전해서, 리소스를 자동으로 조정하는 기능도 가지고 있다. 그리고 이를 사용해 메모리 할당을 스스로 조정하는 DBMS도 있다. 하지만 여기에도 한계는 있다. 굉장히 중요한 리소스 배분이 필요한 상황에서 아무 생각 없이 모든 것을 자동 설정에 의지하는 일은 굉장히 위험하다.

일단 로그 버퍼가 크게 잡혀있다면, 갱신 처리와 관련되어 큰 부하가 걸린다는 것을 알 수 있다. 반대로 데이터 캐시가 크게 잡혀있다면 검색 처리와 관련된 처리가 중심이라는 것을 알 수 있다.

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

언제 사용할까?

정렬 또는 해시 관련 처리에 사용되는 작업용 영역으로 워킹 메모리(working memory)라고 부른다. 정렬은 ORDER BY구, 집합 연산, 윈도우 함수 등의 기능을 사용할 때 실행된다. 반면 해시는 주로 테이블 등의 결합에서 해시 결합이 사용되는 때 실행되며 최근에는 GROUP BY에서도 해시 알고리즘이 사용되는 경우가 있다.

이 작업용 메모리 영역은 SQL에서 정렬 또는 해시가 필요한 때 사용되고, 종료되면 해제되는 임시 영역으로, 일반적으로는 데이터 캐시와 로그 버퍼와는 다른 영역으로 관리되는 경우가 많다. 이 영역이 성능적으로 중요한 이유는, 만약 이 영역이 다루려는 데이터양보다 작아 부족해지는 경우가 생기면 대부분의 DBMS가 저장소를 사용하기 때문이다. 이는 OS 동작에서 말하는 스왑과 같은것이다.

많은 DBMS는 워킹 메모리가 부족할 때 사용하는 임시적인 영역을 가지고 있다.

이러한 일시 영역들은 저장소 위에 있으므로 당연히 접근속도가 느리다.

부족하면 무슨 일이 일어날까?

메모리가 부족하다고 처리가 멈추거나 에러가 발생하는 것처럼 심각한 문제가 생기는 것은 아니다. 하지만 메모리에서 작동하고 있을 때는 빠르게 움직이다가, 메모리가 부족해지는 순간 갑자기 느려지는 순간적인 변화가 일어나는 것이 문제이다.

또한 이 영역은 여러 개의 SQL 구문들이 공유해서 사용하므로, 하나의 SQL구문을 실행하고 있을 때는 메모리에 잘 들어가지만 여러 개의 SQL 구문을 동시에 실행하면 메모리가 넘치는 경우가 있다. 따라서 그러한 상황을 재현하는 검사(부하 검사)를 실시하지 않으면 메모리 부족 현상이 일어날지 판별하기 힘들다. 하나가 있을 때의 성질뿐만 아니라 여러 개가 있을 때의 성질도 주의해야 한다는 것은 컨트롤 하기 힘든 성능 문제이다.

하지만 이를 반대로 생각하면 DBMS는 ‘메모리가 부족하더라도 무언가를 처리하려고 계속 노력하는 미들웨어’라고 생각할수 있다. 메모리가 부족하다는 이유로 SQL구문에 오류를 절대 발생시키지 않는다. 비록 느려지는 사오항이 발생하더라도 상관없으니 어떻게든 끝까지 처리하려 노력한다. 이는 DBMS가 중요한 데이터를 보관하고 처리할 때, 운영체제 정도의 급으로 처리 계속성을 담보하려 하기 때문이다.

DBMS와 실행 계획

1. 권한 이양의 죄악

C, 자바, 루비와 같은 절차가 기초가 되는 언어는 사용자가 데이터에 접근하기 위한 절차(How)를 책임지고 기술하는 것이 전제이다. 반면 비 절차적인 RDB는 그러한 모든 일을 사용자가 아니라 시스템에게 맡겼다. 따라서 사용자가 하는 일은 대상(What)을 기술하는 것으로 축소되었다.

RDB가 이렇게 대담하게 권한 이양을 감행한 데는 당연히 정당한 이유가 있다. 바로 ‘그렇게 하는 것이 비즈니스 전체의 생산성을 향상시키기 때문’이다. 이러한 말은 반은 맞고 반은 틀렸다. 맞다는 것은 RDB가 시스템 세계 곳곳에 침투해 있는 것을 보면 알 수 있다. 반면, 틀렸다는 것은 여전히 우리가 RDB를 다루기 어려워한다는 점을 보면 알 수 있다. SQL은 초기에 생각했던 것처럼 그렇게 간단한 언어가 아니다. 또한 How를 의식하지 않고 사용하는 것 때문에 성능 문제로 고생하는 경우도 꽤 있다. RDB가 숨기고 있는 내부 절차를 들여다봐야 하는 것은 이러한 이유 때문이다.

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

RDB에서 데이터 접근 절차를 결정하는 모듈은 쿼리 평가 엔진이라고 부른다. 쿼리 평가 엔진은 사용자로부터 입력받은 SQL구문(쿼리)을 처음 읽어들이는 모듈이기도 한다. 쿼리 평가 모듈은 추가로 파서 또는 옵티마이저오 ㅏ같은 여러 개의 서브 모듈로 구성된다.

파서(parser)

파서의 역할은 이름 그대로 파스(구문 분석)하는 것이다. 사용자로부터 입력받은 SQL 구문이 항상 구문적으로 올바르다는 보증이 없으므로 검사를 해주는 것이다. 파서는 SQL 구문을 정형적인 형식으로 변환해준다. 그렇게 해야 DBMS내부에서 일어나는 후속 처리가 효율화 된다.

옵티마이저(optimizer)

서류 심사를 통과한 쿼리는 옵티마이저로 전송된다. 옵티마이저는 ‘최적화’이다. 이때 최적화의 대상은 데이터 접근법(실행 계획)이다. 옵티마이저가 바로 DBMS 두뇌의 핵심이다.

옵티마이저는 인덱스 유무, 데이터 분산 또는 편향 정도, DBMS내부 매개변수 등의 조건을 고려해서, 선택 가능한 많은 실행 계획을 작성하고, 이들의 비용을 연산하고, 가장 낮은 비용을 가진 실행 계획을 선택한다.

접근법의 수가 많이 나온다면 그 비용을 계산하고 비교해야 한다. RDB가 데이터 접근의 절차 지향 결정을 자동화하는 이유는 이런 귀찮은 일을 기계적으로 처리해주기 위해서이다.

카탈로그 매니저(catalog manager)

옵티마이저가 실행 계획을 세울 때 옵티마이저에 중요한 정보를 제공하는 것이 카탈로그 매니저이다. 카탈로그란 DBMS의 내부정보를 모아놓은 테이블들로, 테이블 또는 인덱스의 통계 정보가 저장되어 있다. 따라서 이러한 카탈로그 정보를 간단하게 ‘통계 정보’라고 부르기도 한다.

플랜 평가(plan evaluation)

옵티마이저가 SQL구문에서 여러개의 실행 계획을 세운 뒤 그것을 받아 최적의 실행 결과를 선택하는 것이 플랜 평가이다. 실행 계획이라는 것은 곧바로 DBMS가 실행할 수 있는 형태의 코드가 아니다. 오히려 인간이 읽기 쉽게 만들어진 문자 그대로의 ‘계획서’이다. 따라서 성능이 좋지 않은 SQL구문이 있을 때 실행 계획을 읽고, 수정 방안등을 고려할 수 있다.

이렇게 해서 하나의 실행 계획을 선택하면, 이후에 DBMS는 실행 계획을 절차적인 코드로 변환하고 데이터 접근을 수행한다.

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

데이터베이스 사용자로서는 옵티마이저를 잘 사용하는 것이 더 중요하다. 이유는 옵티마이저가 명령하는 대로 다 잘 처리해주는 만능은 아니기 때문이다. 특히 카탈로그 매니저가 관리하는 통계정보에 대해서는 데이터베이스 엔지니어가 항상 신경 써줘야 한다.

플랜 선택을 옵티마이저에게 맡기는 경우, 실제로 최적의 플랜이 선택되지 않는 경우가 꽤 많다. 옵티마이저가 실패하는 패턴이 몇가지잇는데 통계 정보가 부족한 경우가 대표적인 원인으로 꼽힌다.

카탈로그에 포함되어 있는 통계 정보는 다음과 같은 것들이다.

이러한 정보를 활용하여 옵티마이저는 실행 계획을 만든다. 문제가 생기는 경우는 이러한 카탈로그 정보가 테이블 또는 인덱스의 실제와 일치하지 않을 때이다. 테이블에 데이터 삽입/갱신/제거가 수행될 때 카탈로그 정보가 갱신되지 않는다면, 옵티마이저는 오래된 정보를 바탕으로 실행 계획을 세우게 된다. 옵티마이저는 과거 정보 밖에 가지고 있지 않으므로 어쩔 수 없이 잘못된 계획을 세울 수 밖에 없다.

극단적인 예로, 테이블을 만들면 일단 레코드 0개의 상태로 카탈로그 정보가 저장된다. 그런데 이후에 1억 건의 데이터를 올리고 카탈로그 정보를 갱신하지 않는다면 옵티마이저는 데이터 0개를 기준으로 플랜을 생성하게 된다. 이것으로는 최적의 플랜을 절대 기대할 수 없다.

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

테이블의 데이터가 많이 바뀌면 카탈로그의 통계 정보도 함께 갱신해야 한다는 것은 데이터베이스 엔지니어의 상식이다. 통계 정보 갱신은 대상 테이블 또는 인덱스의 크기와 수에 따라서 몇십 분에서 몇시간이 소요되기도 하는, 실행 비용이 굉장히 높은 작업이다 하지만 DBMS가 최적의 플랜을 선택하려면 필요한 조건이므로 갱신 시점을 확실하게 검토해야 한다.

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

데이터양이 많은 테이블에 접근하거나 복잡한 SQL구문을 실행하면 반응 지연이 발생하는 경우가 꽤 있다. 이미 최적의 경로가 설정되어 있는데도 느린 경우도 있다. SQL구문이 너무 복잡하면 옵티마이저가 최적의 접근 경로를 선택하지 못할 수도 있다.

1. 실행 계획 확인 방법

이러한 SQL 구문의 지연이 발생했을 때 제일 먼저 실행 계획을 살펴봐야 한다.

그럼 지금부터 다음과 같은 3개의 기본적은 SQL구문의 실행 계획을 살펴보겠다.

실행 계획의 출력 포맷이 완전히 같지는 않지만 공통적으로 나타나는 부분이 있다.

조작 대상 객체

PostgreSQL은 on 뒤에, Oracle은 Name필드에, MySQL은 table필드에 조작 대상 객체 (shops, products)가 출력된다. 여러개의 테이블을 사용하는 SQL 구문에서는 어떤 객체를 조작하는지 혼동하지 않게 주의가 필요하다. 또한 이 부분은 테이블 이외에도 인덱스, 파티션, 시퀸스처럼 SQL 구문으로 조작 할 수 있는 객체라면 무엇이라도 올 수 있다.

객체에 대한 조작의 종류

객체에 대한 조작의 종류는 실행 계획에서 가장 중요한 부분이다. PostgreSQL은 문장의 앞부분에 나오며, Oracle은 Operation필드로 나타나며 MySQL은 type필드에 나타난다. PostgreSQL의 Seq Scan은 순차적인 접근 (Sequential Scan)의 줄임말로 ‘파일을 순차적으로 접근해서 해당 테이블의 데이터 전체를 읽어낸다’는 의미 이다. Oracle의 TABLE ACCESS FULL은 테이블의 데이터를 전부 읽어 들이다는 의미이다.

💡MySQL의 type필드

접근 방식	설명
const	기본 키 또는 고유키에 의한 loockup(등가비교), 조인이 아닌 가장 외부의 테이블에 접근 하는 방식, 결과는 항상 1행이다. 단 기본 키, 고유 키를 사용하고 있으므로 범위 검색으로 지정하는 경우 const가 되지 않는다
system	테이블에 1행밖에 없는 경우의 특수한 접근 방식
ALL	전체 행 스캔, 테이블의 데이터 전체에 잡근한다.
index	인덱스 스캔, 테이블의 특정 인덱스의 전체 엔트리에 접근한다.
eq_ref	조인이 내부 테이블로 접근할 때 기본키 또는 공유 키에 의한 lookup이 일어난다. const와 비슷하지만 조인의 내부 테이블에 접근한다는 점이 다르다
ref	고유 키가아닌 인덱스에 대한 등가비교, 여러 개 행에 접근할 가능성이 있다.
ref_or_null	ref와 마찬가지로 인덱스 접근 시 맨 앞에 저장되어 있는 NULL의 엔트리를 검색한다.
range	인덱스 특정 범위의 행에 접근한다
fulltext	fulltext 인덱스를 사용한 검색
index_merge	여러 개인스턴스를 사용해 행을 가져오고 그 결과를 통합한다.
unique_subquery	IN 서브쿼리 접근에서 기본 키 또는 고유 키를 사용한다. 이 방식은 쓸데 없는 오버헤드를 줄여 상당히 빠르다.
index_subquery	unique_sunquery와 거의 비슷하지만 고유한 인덱스를 사용하지 않는 점이 다르다. 이 접근 방식도 상당히 빠르다

조작 대상이 되는 레코드 수

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

다만, 이 숫자와 관련해서 자주 오해를 사는 것이 있는데 이 숫자는 옵티마이저가 실행 계획을 만들 때 설명했던, 카탈로그 매니저로부터 얻은 값이다. 따라서 통계 정보에서 파악한 숫자이므로, 실제 SQL구문을 실행한 시점의 테이블 레코드 수와 차이가 있을 수 있다.

예를 들어 Shops 테이블의 모든 레코드를 삭제하고, 실행 계획을 다시 검색하면 모두 그대로 60이라는 값이 출력된다. 이는 옵티마이저가 어디까지나 통계라는 메타 정보를 믿기 때문에, 실제 테이블을 제대로 보지 않는다는 증거이다.

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

조작 대상이 되는 레코드 수

일단 모든 DBMS 모두 Rows가 1로 바뀌었다. PostgreSQL과 Oracle에선 WHERE구에서 기본 키가 ‘00050’인 점포를 지정했으며 MySQL에서는 유일한 상품인 ‘바디’를 지정했으므로 접근 대상은 반드시 레코드 하나이기 때문이다.

접근 대상 객체와 조작

PostgreSQL에서는 Index Scan, Oracle에서는 INDEX UNIQUE SCAN, MySQL에서는 ref라는 조작이 나타난다. 이는 인덱스를 사용해 스캔을 수행했다는 것이다.

Oracle에서는 TABLE ACCESS FULL이 TABLE ACCESS BY INDEX ROWID로 바뀌었고 추가적으로 내역을 보면 id=2의 레코드에 INDEX UNIQUE SCAN, name(대상 객체)에 PK_SHOPS가 출력된다. 이 PK_SHOPS는 기본 키의 인덱스 이름이다.

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

간단하게 말해서 인덱스의 처리 비용이 완만하게 증가한다는 뜻으로 특정 데이터양(N)을 손익 분기점으로 인덱스 스캔이 풀 스캔보다도 효율적인 접근을 하게 된다는 것이다.

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

SQL에서 지연이 일어나는 경우는 대부분 결합과 관련된 것이다. 결합을 사용하면 실행 계획이 상당히 복잡해지므로, 옵티마이저도 최적의 실행 계획을 세우기 어렵다.

일반적으로 DBMS가 결합을 할 때는 세가지 종류의 알고리즘을 사용한다. 가장 간단한 결합 알고리즘은 Nested Loops이다. 한쪽 테이블을 읽으면서 레코드 하나마다 결합 조건에 맞는 레코드를 다른 쪽 테이블에서 찾는 방식이다. 절차 지향형 언어로 구현한다면, 이중 반복으로 구현되므로 중첩 반복(Nested Loops)이라는 이름이 붙은 것이다.

두번째는 Sort Merge이다. 결합 키로 레코드를 정렬하고 순차적으로 두 개의 테이블을 결합하는 방법이다. 결합 전에 전처리로 정렬을 수행해야하는데 이때 작업용 메모리로 워킹 메모리를 사용한다.

세번째는 Hash이다. 결합 키를 해시값으로 맵핑하는 방법이다. 해시 테이블을 만들어야 하므로, 마찬가지로 작업용 메모리 영역을 필요로 한다.

Oracle의 Operation필드를 보면 NESTED LOOPS라고 나오므로 어떤 알고리즘을 사용하고 있는 쉽게 알 수 있다. 마찬가지로 PostgreSQL에서도 Nested Loop이라고 나오며 MySQL에서는 Extra필드에 Using join buffer (hash join)이라고 나오는 걸로 보아 Hash 알고리즘을 선택하고 있다는 것을 알 수 있다.

실행 계획은 일반적으로 트리 구조이다 이때 중찹 단계가 깊을 수록 먼저 실행된다. PostgreSQL의 결과를 예로 살펴보면 Nested Loop보다도 Seq Scan과 Index Scan의 단계가 깊으므로 결합 전에 테이블 접근이 먼저 수행된다는 것을 알 수 있다. 이때 결합의 경우 어떤 테이블에 먼저 접근하는지가 굉장히 중요한 의미를 갖는데 같은 중첩 단계에서는 위에서 아래로 실행한다는 뜻이다.

예를 들어서 PostgreSQL과 Oracle 모두 Reservation테이블과 Shop테이블 접근이 같은 중첩 단계에 있지만 Reservation 테이블에 대한 접근이 위에 있으므로 Reservation테이블에 대한 접근이 먼저 일어난다는 것을 알 수 있다. (MySQL은 재고수 테이블에 먼저 접근한다.)

실행 계획의 중요성

옵티마이저가 좋을 것으로 여겨 선택한 실행 계획이 참담한 성능을 만들어내는 경우도 많다 또한 그러한 복잡한 문제 이전에 옵티마이저에게 정보를 제대로 주지 못하는 문제도 나올 수 있다. 예를 들어 인덱스를 사용해야 빨라지는 부분인데 사용하지 않거나. 테이블 결합 순서를 이상하게 적는 실수를 할 수도 있다.

이런 경우에는 최후의 튜닝 수단을 사용해야 한다. 바로 실행 계획을 수동으로 변경해 주는 것이다. 예를 들어서 Oracle, MySQL등이 가지고 있는 힌트 구를 사용하면 SQL 구문에서 옵티마이저에게 강제적으로 명령할 수 있다.

그런데 실행 계획을 변경하려면 어떤 선택지가 있는지를 알아야 한다. 따라서 어떤 기능에 대해 어떠한 선택지들이 있는 방법을 알아볼 것이다. 하지만 그 전에 SQL 구문과 그러한 SQL구문들이 어떠한 접근 경로(Access path)로 데이터를 검색하는지 아는지가 먼저이다. 그리고 제대로 된 SQL 구문을 작성하려면 어떤 테이블 설정이 효율적인지 알아야 하며, 어떤 SQL 구문이 주어졌을 때 어떠한 실행 계획이 나올지 예측할 수도 있어야 한다.

DBMS 아키텍처

DBMS 아키텍처 개요

1. 쿼리 평가 엔진

2. 버퍼 매니저

3. 디스크 용량 매니저

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

5. 리커버리 매니저

DBMS와 버퍼

1. 공짜 밥은 존재할까?

2. DBMS와 기억장치의 관계

하드디스크(HDD)

메모리

버퍼를 활용한 속도 향상

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

데이터 캐시

로그 버퍼

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

휘발성

휘발성의 문제점

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

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

검색과 갱신 중에서 중요한 것

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

언제 사용할까?

부족하면 무슨 일이 일어날까?

DBMS와 실행 계획

1. 권한 이양의 죄악

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

파서(parser)

옵티마이저(optimizer)

카탈로그 매니저(catalog manager)

플랜 평가(plan evaluation)

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

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

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

1. 실행 계획 확인 방법

조작 대상 객체

객체에 대한 조작의 종류

조작 대상이 되는 레코드 수

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

조작 대상이 되는 레코드 수

접근 대상 객체와 조작

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

실행 계획의 중요성

정리