서론
실행 계획이 만들어지면 DBMS는 그것을 바탕으로 데이터 접근을 수행한다.
하지만 데이터양이 많은 테이블에 접근하거나 복잡한 SQL 구문을 실행하면 반응 지연이 발생하는 경우가 꽤 있다.
그 이유로는 앞에서 설명했던 것처럼 통계 정보가 부족한 경우도 있지지만, 이미 최적의 경로(방법)가 설정되어 있는데도 느린 경우가 있다.
또한 통계 정보가 최신이라도 SQL 구문이 너무 복잡하면 옵티마이저가 최적의 접근 경로(방법)를 선택하지 못할 수도 있다.
1. 실행 계획 확인 방법
이러한 SQL 구문의 지연이 발생했을 때 제일 먼저 실행 계획을 살펴봐야 한다.
모든 DBMS는 실행 계획을 조사하는 수단을 제공한다.
구현에 따라서 차이는 있겠지만 모두 명령형 인터페이스에서 확인할 방법을 제공해 준다.
Oracle: set autotrace traceonly
Microsoft: SET SHOWPLAN_TEXT ON
DB2: EXPLAIN WITH SNAPSHOT FOR SQL 구문
PostgreSQL: EXPLAIN SQL 구문
MySQL: EXPLAIN EXTENDED SQL 구문그럼 지금부터 다음과 같은 3개의 기본적인 SQL 구문의 실행 계획을 살펴보자.
1. 테이블 풀 스캔(Full Scan)의 실행 계획
2. 인덱스 스캔의 실행 계획
3. 간단한 테이블 결합의 실행 계획2. 테이블 풀 스캔의 실행 계획
SELECT * FROM Shops;실행 계획의 출력 포맷이 완전히 같지는 않지만, 두 가지 DBMS에 모두 공통적으로 나타나는 부분이 있다.
바로 다음과 같은 3가지이다.
1. 조작 대상 객체
2. 객체에 대한 조작의 종류
3. 조작 대상이 되는 레코드 수이러한 3가지 내용은 거의 모든 DBMS의 실행 계획에 포함되어 있다.
그만큼 중요한 부분이라는 것이다.
- 조작 대상 객체
조작 대상 겍체는 SQL 구문의 FROM 구 뒤에 오는 테이블이다.
여러 개의 테이블을 사용하는 SQL 구문에서는 어떤 객체를 조작하는지 혼동하지 않게 주의가 필요하다.
- 객체에 대한 조작의 종류
객체에 대한 조작의 종류는 실행 계획에서 가장 중요한 부분입니다.
PostgreSQL의 'Seq Scan'은 '순차적인 접근(Sequential Scan)'의 줄임말로 '파일을 순차적(Sequential)으로 접근해서 해당 테이블의 데이터 전체를 읽어낸다'는 의미이다.
Oracle의 'TABLE ACCESS FULL'은 '테이블이 데이터를 전부 읽어들인다'는 의미이다.
사실 엄밀하게 말해서 두 가지가 같은 것은 아니다.
테이블에서 데이터를 모두 읽는 방법으로, 반드시 시퀀셜 스캔을 선택할 이유는 없기 때문이다.
따라서 PostgreSQL의 출력이 조금 더 물리적 차원에 가까운 출력이라고 볼 수 있다.
하지만 실질적으로 Oracle도 테이블 풀 스캔을 할 때는 내부적으로 시퀀셜 스캔을 수행하므로, 같다고 생각해도 상관은 없다.
따라서 이러한 형태의 접근법을 모두 '테이블 불 스캔'이라고 부른다.
- 조작 대상이 되는 레코드 수
세 번째로 중요한 항목은 조작 대상이 되는 레코드 수이다.
이는 Rows라는 항목에 출력된다.
결합 또는 집약이 포함되면 1개의 SQL 구문을 실행해도 여러 개의 조작이 수행된다.
그러면 각 조작에서 얼마만큼의 레코드가 처리되는지가 SQL 구문 전체의 실행 비용을 파악하는 데 중요한 지표가 된다.
Rows에 출력되는 숫자는 옵티마이저가 실행 계획을 만들 때 설명했던, 카탈로그 매니저로부터 얻은 값이다.
따라서 통계 정보에서 파악한 숫자이므로, 실제 SQL 구문을 실행한 시점의 테이블 레코드 수와 차이가 있을 수 있다.
3. 인덱스 스캔의 실행 계획
이번에는 이전에 실행했던 간단한 SQL 구문에 WHERE 조건을 추가해보자.
SELECT * FROM Shops WHERE shop_id = '00050';이번 실행 계획에는 재미난 변화가 보인다.
이전과 마찬가지로 3개의 부분으로 나누어 살펴보자.
- 조작 대상이 되는 레코드 수
일단 두 가지 DBMS 모두 Rows가 1로 바뀌었다.
WHERE 구에서 조건을 지정했으므로, 접근 대상은 반드시 레코드 하나이기 때문이다.
- 접근 대상 객체와 조작
객체와 조작은 어떻게 되었을까?
이 부분에서 굉장히 흥미로운 변화가 보인다.
PostgreSQL에서는 'Index Scan', Oracle에서는 'INDEX UNIQUE SCAN'이라는 조작이 나타난다.
이는 인덱스를 사용해 스캔을 수행한다는 것이다.
인덱스와 관련된 내용은 추후에 자세하게 설명하겠지만, 일반적으로 스캔하는 모집합 레코드 수에서 선택되는 레코드 수가 적다면 테이블 풀 스캔보다 빠르게 접근을 수행한다.
이는 풀 스캔이 모집합의 데이터양에 비례해서 처리 비용이 늘어나는 것에 반해, 인덱스를 사용할 때 활용되는 B-tree가 모집합의 데이터양에 따라 로그합수 적으로 처리 비용이 늘어나기 때문이다.
간단하게 말해서 인덱스의 처리 비용이 완만하게 증가한다는 뜻으로, 특정 데이터양(N)을 손익분기점으로 인덱스 스캔이 풀 스캔보다도 효율적인 접근을 하게 된다는 것이다.
4. 간단한 테이블 결합의 실행 계획
마지막으로 결합을 수행하는 쿼리의 실행 계획을 살펴보자.
SQL에서 지연이 일어나는 경우는 대부분 결합과 관련된 것이다.
결합을 사용하면 실행계획이 상당히 복잡해지므로, 옵티마이저도 최적의 실행 계획을 세우기 어렵다.
따라서 결합 시점의 실행 계획 특성을 공부하는 것은 굉장히 중요한 의미가 있다.
실행 계획을 검색할 SQL은 다음과 같다.
SELECT shop_name FROM Shops S INNER JOIN Reservations R ON s.shop_id = R.shop_id;상세한 내용은 추후에 설명하겠지만, 일반적으로 DBMS는 결합을 할 때 세 가지 종류의 알고리즘을 사용한다.
가장 간단한 결합 알고리즘은 Nested Loops이다.
한쪽 테이블을 읽으면서 레코드 하나마다 결합 조건에 맞는 레코드를 다른 쪽 테이블에서 찾는 방식이다.
절차 지향영 언어로 구현한다면, 이중 반복으로 구현되므로 중첩 반복(Nested Loops)이라는 이름이 붙은 것이다.
두 번째는 Sort Merge이다.
결합 키(현재 예제에서는 점포ID)로 레코드를 정렬하고, 순차적으로 두 개의 테이블을 결합하는 방법이다.
결합 전에 전처리로 (원칙적으로) 정렬을 수행해야 하는데, 이때 작업용 메모리로 '워킹 메모리'를 사용한다.
세 번째는 Hash이다.
이름 그대로 결합 키값을 해시값으로 맵핑하는 방법이다.
해시 테이블을 만들어야 하므로, 마찬가지로 작업용 메모리 영역을 필요로 한다.
여기서 잠시 실행 계획을 읽는 방법을 짚고 넘어가자.
실행 계획은 일반적으로 트리 구조이다.
이때 중첩 단계가 깊을수록 먼저 실행된다.
PostgreSql의 결과를 예로 살펴보면 'Nested Loop'보다도 'Seq Scan'과 'Index Scan'의 단계가 깊으므로, 결합 전에 테이블 접근이 먼저 수행된다는 것을 알 수 있다.
이때 결합의 경우 어떤 테이블에 먼저 접근하는지가 굉장히 중요한 의미를 갖는다.
같은 중첩 단계에서는 위에서 아래로 실행한다는 뜻이다.
이런 결합 알고리즘과 테이블 접근 순서의 중요성에 대해서는 뒤에서 자세하게 설명한다.
지금은 실행 계획이라는 것을 읽는 방법에 익숙해지는 것만으로 충분하다.
