21강 - 서브쿼리가 일으키는 폐해
테이블, 뷰, 서브쿼리의 차이
서브쿼리의 문제점
•
연산 비용 추가
서브쿼리는 실체적인 데이터를 저장하고 있지 않다. 즉, 서브쿼리에 접근할 때마다 SELECT 구문을 실행해서 데이터를 만들고 이에 따라 구문 실행 비용이 추가된다.
•
데이터 I/O 비용 발생
데이터양이 큰 경우 TEMP 탈락 현상이 발생해 DBMS가 저장소에 있는 파일에 결과를 쓸 때도 있다. 이렇게 되면 저장소 성능에 따라 접근 속도가 급격하게 떨어진다.
•
최적화를 받을 수 없음
서브쿼리에는 테이블의 인덱스와 같은 메타 정보가 존재하지 않는다. 따라서 옵티마이저가 쿼리를 해석하기 위해 필요한 정보를 서브쿼리로부터 얻을 수 없다.
서브쿼리 의존증
구입 명세 테이블 정의
고객별 최소 순번(seq) 레코드를 구해보자
•
서브쿼리를 사용한 방법
서브쿼리 SQL문과 실행 계획
◦
코드가 복잡해서 읽기 어렵다.
◦
성능이 떨어진다.
▪
서브쿼리는 대부분 일시적인 영역(메모리 또는 디스크)에 확보되므로 오버헤드가 생긴다.
▪
서브쿼리는 인덱스 또는 제약 정보를 가지지 않기 때문에 최적화되지 못한다.
▪
이 쿼리는 결합을 필요로 하기 때문에 비용이 높고 실행 계획 변동 리스크가 발생한다.
▪
receipts 테이블에 스캔이 2번 필요하다.
•
상관 서브쿼리를 사용한 방법
상관 서브쿼리 SQL문과 실행 계획
상관 서브쿼리를 사용하더라도 receipts 테이블에 접근이 2번 발생한다. R2 접근에 기본 키의 index only scan을 사용하기도 하지만, 그렇다고 해도 receipts 테이블에 접근 1회와 기본 키의 인덱스에 접근 1회가 필요하다. 결과적으로 서브쿼리와 비교했을 때 큰 성능적 장점이 없다.
Index Only Scan
•
윈도우 함수로 결합을 제거 
SQL 튜닝에서 가장 중요한 부분이 저장소 I/O를 줄이는 것이다.
윈도우 함수 SQL문과 실행 계획
쿼리가 간단해지면서 가독성이 올라갔다. 무엇보다 receipts 테이블에 대한 접근이 1회로 감소한다.
장기적 관점에서의 리스크 관리
서브쿼리, 상관 서브쿼리, 윈도우 함수를 사용한 쿼리 중 어느 것이 얼마나 성능이 좋은지는 사용하는 DBMS 또는 DB 서버의 성능, 매개변수나 인덱스와 같은 환경 요인에 의해 크게 바뀔 수 있다. 저장소의 I/O 양을 감소시키는 것이 SQL 튜닝의 기본 원칙이라는 것을 기반으로 판단하자.
서브쿼리나 상관 서브쿼리를 사용한 쿼리와 비교했을 때 윈도우를 사용한 쿼리는 결합을 사용한 부분을 제거했다. → 성능 향상, 성능의 안정성 확보
결합을 사용한 쿼리가 갖는 불안정 요소 2가지
정리하자면, 결합을 사용한다는 것은 곧 장기적 관점에서 고려해야 할 리스크를 늘리게 된다는 것이다.
서브쿼리 의존증 → 윈도우 함수와 CASE 식을 이용해 성능 개선
서브쿼리는 정말 나쁠까?
서브쿼리 자체가 나쁜 것은 아니고, 서브쿼리를 사용해야만 하는 상황도 많다.
결과적으로 서브쿼리를 빼는 편이 나은 경우라도, 코딩하면서 처음 쿼리를 고민할 때는 생각의 보조 도구로서 먼저 서브쿼리를 사용해보는 것도 이해에 도움이 된다.
22강 - 서브쿼리 사용이 더 나은 경우
결합과 관련된 쿼리의 경우 서브쿼리를 사용하는 편이 성능 측면에서 더 낫다. 결합할 때는 최대한 결합 대상 레코드 수를 줄이는 것이 중요하다. 옵티마이저가 이러한 것을 잘 판별하지 못할 때는 사람이 직접 연산 순서를 명시해줌으로써 성능을 높일 수 있다.
회사 테이블과 사업소 테이블 생성문
회사마다 주요 사업소(main_flg 필드가 Y인 사업소)의 직원 수를 구해보자. 결과에는 지역(district)도 포함한다.
•
결합부터 하고 집약을 하는 방법
SQL문과 실행 계획
◦
결합 대상 레코드 수
▪
회사 테이블: 레코드 4개
▪
사업소 테이블: 레코드 10개
•
집약을 먼저 하고 결합하는 방법
SQL문과 실행 계획
◦
결합 대상 레코드 수
▪
회사 테이블: 레코드 4개
▪
사업소 테이블: 레코드 4개
csum 뷰가 회사 코드로 집약되어 4개로 압축되었다.
회사 테이블의 규모에 비해 사업소 테이블의 규모가 매우 크다면, 일단 결합 대상 레코드 수를 집약해 I/O 비용을 더 줄이는 것이 좋다.
사전에 결합 레코드 수를 압축하는 것으로 성능을 개선할 수 있다.