책 제목
SQL 레벨업
출판연월
16년 1월
지은이
미크

읽기 전, 도입

  • 내가 작성한 쿼리는 좋은걸까?라는 의문을 항상 가지고 있었다. 좋다는 것은 아래 두가지 관점에서 생각해볼 수 있다
    1. 시간이 가능한 적게 걸리는지
    2. 남들이 알아보기 쉬운지 (어떤 테이블로 어떤 질의를 하는지 파악하기
  • 이 책에서 소개하는 쿼리를 기반으로 내가 작성하던 쿼리가 어떤 문제가 있는지 정리해서 개선하는 것이 실무에 도움이 될 것
  • SQL 쿼리 실행은 사내 데이터브릭스 플랫폼의 SQL Editor를 사용함
    • 사내에선 ANSI SQL 기반 Spark SQL 사용하는데, 널리 알려진 DBMS와 다르지만, 분산 컴퓨팅을 사용하는 회사가 대부분일거라 도움이 되지 않을까 싶음

먼저 [SQL 레벨업] 책에 대해 간략히 정리한 뒤, 내가 평소 궁금해했던 Spark SQL 관련 내용 적어볼 것

DBMS 아키텍쳐

  • RDB와 SQL
    • 사용자가 대용량 데이터를 직접 다루기엔 제약이 많음. 이 간극을 좁히는 미들웨어
    • 그래서 내부 동작 원리를 몰라도 사용할 수 있는 것을 지향하고 있음
  • 사용자로부터 SQL 구문이 나옴 -> DBMS를 통해 다양한 작업이 들어감 -> 저장 장치에 있는 데이터에 접근해서 데이터를 읽고 씀
  • DBMS 주요 구성요소
구성 요소설명
쿼리 평가 엔진- SQL 구문을 분석, 어떤 순서로 기억 장치 데이터에 접근할지 결정함
- 여기서 실행 계획을 결정.
- 결정된 실행 계획에 기반해서 데이터에 접근하는 방법을 접근 메서드라고 함
버퍼 매니저- DBMS에선 특별한 용도로 사용되는 메모리 영역이 있음
- 버퍼 매니저는 이 메모리 영역을 관리함
디스크 용량 매니저- 데이터를 어디에 어떻게 저장할지 관리
- 데이터 읽기와 쓰기를 제어함.
트랜잭션 매니저 및 락 매니저- 데이터베이스의 동시 접속 처리 역할.
- 각각의 처리를 트랜잭션이라고 함
- 트랜잭션의 정합성을 유지하며 실행.
- 필요 시 데이터에 락을 걸어 트랜잭션 대기시킴.
리커버리 매니저- 데이터의 정기적 백업 및 복구 작업을 담당함.

DBMS와 버퍼

  • 저장소(하드디스크, 메모리 등)에는 기억 비용과 접근 속도 간 트레이드 오프가 있음
  • DBMS가 데이터 일부를 메모리에 올리는 것은 성능 향상 목적
    • 같은 SQL 구문이라더라도, 메모리에 올라가 있는 것에 접근한다면 훨씬 빠르게 데이터를 읽을 수 있음
  • 이 메모리를 버퍼(buffer) 또는 캐시(cache)라고 함
  • 이 버퍼에 데이터를 어떻게, 어느 정도의 기간 동안 올려놓을지 관리하는 것이 버퍼 매니저
  • DBMS에서 사용하는 메모리는 크게 데이터 캐시, 로그 버퍼 두 개로 나뉨
    • 데이터 캐시: 디스크에 있는 데이터의 일부를 메모리에 유지하기 위해 사용하는 메모리 영역
    • 로그 버퍼: 갱신 처리(INSERT, DELETE, UPDATE, MERGE)를 전달 받으면 바로 데이터를 변경하지 않고, 이 위에 변경 정보를 보내고 이후에 디스크에 변경 수행
      • 왜? 결국 성능 향상. 검색, 갱신에 상당한 시간이 소요되는데, 변경이 끝날 때까지 대기하는 것보다, 메모리에 갱신 처리 받은 시점에서 사용자에게는 처리되었다고 통지하고, 내부적으로 관련된 처리 계속 수행하는 것
  • 저장소의 속도가 빨랐다면 이런 버퍼 메커니즘이 필요없을 것.
  • 메모리는 휘발성이라는 특징이 있음. 데이터 캐시에선 큰 문제 없지만, 로그 버퍼에선 문제가 큼
    • 사용자가 수행한 갱신 정보가 사라질 수 있다는 것. 내가 입금한 금액이 없어지는 것과 같음
  • 워킹 메모리: 정렬, 해시 관련 처리에 사용되는 작업용 메모리 영역. 해시는 특히 테이블 조인에서 사용됨
    • DBMS마다 워킹 메모리 명칭이 다름. MySQL에선 정렬 버퍼라고 함
    • 워킹 메모리가 부족하면, 저장소 사용함. 당연히 속도는 느려짐
    • 이를 다른 관점에서 보면, DBMS는 메모리가 부족하더라도 무언가를 처리하려고 계속 노력하는 미들웨어..라고 할 수 있다네
    • 이와 대비되는 예시로 자바는 heap 크기가 부족하면 oom 에러 발생시켜 모든 처리를 중단시키는데, 이걸로 고생했던 적이 적지 않았다

DBMS와 실행 계획

  • 쿼리 평가 엔진이 실행 계획을 결정함
  • 쿼리 평가 모듈이라고도 하는데, 이는 파서, 옵티마이저와 같은 여러 개의 서브 모듈로 구성됨
구성 요소설명
파서- 이름 그대로 파스(구문 분석)하는 모듈
- 쉼표 누락, 존재하지 않는 테이블 이름 식별 등의 오류를 찾아냄.
- SQL 구문을 정형적인 형식으로 변환.
옵티마이저- 실행 계획을 수립하기 위해 인덱스 유무, 데이터 분산 또는 편향 정도, DBMS 내부 매개변수 등을 고려.
- 다양한 실행 계획을 작성하고 비용을 계산하여 가장 낮은 비용의 실행 계획을 선정.
카탈로그 매니저- DBMS 내부 정보를 담은 테이블, 즉 카탈로그 관리.
- 테이블이나 인덱스의 통계 정보 등을 포함.
플랜 평가- 옵티마이저에서 제시된 실행 계획을 절차적인 코드로 변환.
- 실행 계획의 효율성 평가 및 구현.
  • 옵티마이저가 최적의 실행 계획을 뱉기 위해선 완전한 통계 정보가 뒷받침 되어야 함
    • 통계 정보를 갱신시키는 것도 큰 비용이 들긴 함

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

  • 실행 계획대로 처리되는게 항상 만능은 아님. 이걸 내가 제어하는 것도 어느정도 필요함. 그럼 실행 계획을 어떻게 확인?
  • 테이블 풀 스캔 & 인덱스 스캔
    • 모집합의 데이터가 많을수록 인덱스 스캔의 처리 비용이 낮음. B-tree에 기인한다고 함
  • 결합 알고리즘
    • Nested Loops: 한쪽 테이블을 스캔하면서 레코드 하나마다 결합 조건에 맞는 레코드를 다른 쪽 테이블에서 찾는 방식.
    • Sort Merge: 결합 키로 레코드를 정렬하고 순차적으로 두 테이블을 결합 (정렬 -> 워킹 메모리 필요)
    • Hash: 결합 키값을 해시로 맵핑 (해시 -> 워킹 메모리 필요)
  • 내가 사용하는 MySQL에선 실행 계획을 어떻게 확인할 수 있나?

실행 계획의 중요성

  • 실행 계획이 중요한데, 옵티마이저가 내는 실행 계획이 항상 최선은 아님
  • 그래서 수동으로 조정할 수 있어야함 (힌트 구)
    • 조정에는 어떤 선택지가 있는지?
    • 어떤 접근 경로로 데이터를 검색하는지
    • 어떤 SQL 구문이면 어떤 실행 계획이 나올지 예측할 수도 있어야 함
  • 이런 수동 조작은 물리 계층을 은폐한다는 RDB의 목표를 거스르는 일
  • 이상에 도달하지 못한 현실에 살고 있는 우리에게는 이상적이지 않은 방법도 필요함
  • 여기까지가 일반적인 DBMS의 아키텍쳐에 대한 내용.
  • 스파크는 컴퓨터 클러스터의 데이터 처리 작업을 관리하고 조율하는 역할
    • 클러스터는 집합과 비슷한 개념. 여러 컴퓨터를 모으기만 해선 작업을 할 수 없으니, 이걸 전두지휘할 매니저가 필요한 것
    • 이 매니저의 본질? 속성?을 SparkSession 이라고 함. 이걸 띄운다면 클러스터를 제어할 수 있음
  • 스파크와 대화하려면 어떻게 해야 할까? 라는 물음을 가져본다면, 스파크가 다양한 언어를 API 형태로 지원한다는 것이 이해됨
    • 스파크는 스칼라로 개발되어 있어 스칼라가 기본 언어
    • 자바, 파이썬, SQL, R 등을 지원함
  • SQL, DataFrame API로 비즈니스 로직(=코드)을 표현함
  • 스파크에서 이 코드를 실행하기 전 기본 실행 계획으로 컴파일함
    • explain 메서드로 확인 가능

SELECT 구문

  • 대부분 아는 내용이라 정리할 것 위주로만 남겨보면
  • SELECT 구문은 절차 지향형 언어의 함수라고 함. 입출력의 자료형이 테이블(관계)로 정해져 있다는 점에서라는데. 또 인풋 아웃풋이 모두 같다는 것에서 폐쇄성이 있다고도 함. 뷰와 서브쿼리 이해할 때 이 개념이 중요하다네
  • WHERE 구는 레코드에 조건을 건다면, HAVING 구는 집합에 조건을 건다는 차이. HAVING은 필드에만 적용되는게 아니라 집합 연산을 조건으로 할 수도 있음
  • VIEW는 데이터베이스 안에 테이블을 저장하는데 아니라, SELECT 구문을 저장한 것 뿐. 실제로는 내부적으로 추가적인 SELECT 구문을 실행하는 nested 구조임
  • 만약 FROM 구에 SELECT 구문을 지정했다면 결과는 VIEW와 같지만, 이 때의 SELECT 구문을 subquery라고 함
  • 조건 지정 시 서브쿼리를 사용해 매칭(IN)을 할 수도 있음. JOIN 과 같은 역할이지만 작동 방식이 다를 것

조건 분기, 집합 연산, 윈도우 함수, 갱신

  • CASE는 하나의 식 (구가 아님!) 그래서 SELECT, WHERE, GROUP BY, HAVING, ORDER BY 구 등에 적용 가능.
  • UNION은 중복 제거 후 합치고, UNION ALL은 중복 포함해서 합침
  • WINDOW 함수는 성능과 관계 큼. GROUP BY는 자르기 + 집약 두 기능이 있는 반면, WINDOW는 자르기만 있음
  • SQL은 쿼리, 즉 검색을 중심으로 수행하기 위한 언어. 데이터를 갱신하는 것은 부가적인 기능임. 갱신은 크게 세 종류로 분류됨
    • INSERT
    • DELETE
    • UPDATE

UNION을 사용한 쓸데없이 긴 표현

  • 조건분기 할 땐 SELECT 구의 CASE 식을 사용해라. WHERE 구로 조건 걸어서 UNION으로 합치지 말고
  • 이 책을 읽기 전까진 WHERE + UNION으로 하는 사람이 있다는 것도 몰랐다

집계와 조건 분기

  • 이건 좀 특이한 경우긴 한데..
  • GROUP BY 로 A, B 두 키를 잡았는데, B 열의 값 b_1, b_2,.. 를 열로 pivot 시켜서 뭔갈 집계하려고 할 때
  • 몇 번 이런 경우를 처리해야 했던 적이 있는데, SQL에선 pivot이 없어서 pyspark로 빼내와 pivot 시켰음
  • 이걸 SQL에서 하려면, CASE 식을 집계식에 사용하라고 함
SELECT SUM(CASE WHEN col_b = 'b_1' THEN col_c ELSE 0 END) AS b_1_sum,
       SUM(CASE WHEN col_b = 'b_2' THEN col_c ELSE 0 END) AS b_2_sum
FROM table
GROUP BY col_a
  • 이러면 col_b가 옆으로 cast 된 것을 확인 가능함
  • 데이터 풀 스캔도 한 번만 있어서 UNION 을 사용했을 때 보다 비용 절감도 된다고 함
  • CASE 식 안에 집약 결과를 사용할 수도 있음
SELECT col_a,
       CASE WHEN COUNT(*) = 1 THEN SUM(col_c)
            ELSE 'more_than_1'
            END AS col_d
FROM table
GROUP BY col_a
  • 이렇게 쿼리할 일이 거의 없었던 것 같은데, 만약 해야했다면 SQL로 집약하고 pyspark로 빼와서 when 함수로 처리했을 것 같다
  • 책에서는 각 집약 결과마다 HAVING으로 조건 걸어서 UNION 시키는 안 좋은 사례를 보여줬는데, 이렇게는 안 할 것 같고..
  • 내가 한 방식과 책에서 제시한 방식의 연산 비용이 크게 다를 것 같진 않다. 어차피 COUNT(*)를 상수로 취급해서 치환하는 것은 같으니까
  • 그래도 쿼리 안에서 해결하는게 좀 더 보기는 산뜻하니, 이렇게도 시도해볼 것

그래도 UNION이 필요한 경우

  • 인덱스 잘 심어져 있으면 UNION으로 풀스캔 회피할 수 있다는 것 같은데 빠르게 넘어감

절자 지향형과 선언형

  • SQL의 기본 체계는 선언형이라고 함. 그래서 절차 지향적으로 생각하면 초보를 벗어날 수 없다는데..
  • 일단 절차 지향형과 선언형이 무슨 차이일까
  • ChatGPT 답변
절차 지향형 프로그래밍과 선언형 프로그래밍은 프로그래밍 패러다임의 두 가지 주요 분류입니다. 이들 간의 주요 차이점은 다음과 같습니다:

절차 지향형 프로그래밍 (Procedural Programming):

접근 방식: 코드가 순차적으로 실행되는 방식을 중시합니다. 이는 "어떻게" 문제를 해결할 것인지에 초점을 맞춥니다.
예시: C언어는 전형적인 절차 지향형 프로그래밍 언어입니다.
구조: 함수와 절차의 집합으로 프로그램을 구성합니다.
제어 흐름: 프로그램은 일련의 절차적 단계를 따라 작업을 수행합니다.
선언형 프로그래밍 (Declarative Programming):

접근 방식: "무엇"을 해야 할지에 초점을 맞춥니다. 즉, 목표의 결과에 집중하며, 그 결과를 얻기 위한 구체적인 단계는 강조하지 않습니다.
예시: SQL이나 HTML과 같은 언어는 선언형 프로그래밍의 예입니다.
구조: 프로그램은 상태와 표현식의 선언으로 구성됩니다.
제어 흐름: 프로그램은 실행 순서보다는 표현식의 평가에 의존합니다.
결론적으로, 절차 지향형 프로그래밍은 "어떻게" 작업을 완수할지에 중점을 두는 반면, 선언형 프로그래밍은 "무엇"을 달성하고자 하는지에 초점을 맞춥니다.
  • pyspark도 선언형이라고 함

집약

  • GROUP BY 기반 집약에서 정렬보다 해시를 사용하는 경우가 많음. 해시 함수를 사용해서 특정 필드를 해시 키로 변환 후, 같은 키를 가진 그룹을 모아 집약하는 방법
  • 해시, 정렬은 메모리를 많이 사용해 만약 메모리 부족하면 일시 영역으로 저장소 사용해서 성능 문제 일으킴
  • GROUP BY 와 WINDOW 함수는 내부적으로 해시, 정렬 처리 실행

자르기

  • GROUP BY 구는 자르기, 집약을 한꺼번에 수행하는 연산
    • 모집합 테이블을 작은 부분 집합으로 분리 후
    • 분리된 집합을 집약
    • 맞나..?
  • 부분 집합을 파티션이라고 함. 파티션끼리는 독립임
  • GROUP BY 구에 CASE 식을 키로 줄 수 있다는데, 나같으면 그냥 WITH 구문으로 처리했을 것 같다..
    • CASE 식을 키로 준다면, SELECT 구에도 같은 식을 입력해야 하는데, 중복이기도 하고 CASE 식이 얼마나 커질지도 모르는거고
  • PARTITION BY 구를 사용한 방법. 이건 집약 기능이 없는, 자르기 + 연산만 있는 구
    • 마찬가지로 키에 CASE 식을 넣을 수 있음

반복문 의존증

  • SQL에는 반복문이 왜 없을까?
  • 그전에 용어 먼저. 반복계, 포장계
    • 전자는 반복문 우겨넣은 SQL으로 SQL 잘 몰라도 짤 수 있고
    • 후자는 여러 행을 한번에 처리하려는 쿼리인데 성능은 반복계보단 좋지만 구문이 복잡해져서 유지 보수성이 떨어진다고 함
  • 반복계의 단점
    • 한 SQL 구문 실행할 때는 데이터 검색, 연산말고도 아래 처리가 있음
      • 전처리
        • SQL 구문을 네트워크로 전송
        • 데이터베이스 연결
        • SQL 구문 파스
        • SQL 구문의 실행 계획 생성 또는 평가
      • 후처리
        • 결과 집합을 네트워크로 전송
    • 작은 SQL을 여러 번 반복하는 반복계에서는 오버 헤드가 높아짐
    • 병렬 분산이 힘듦
  • 반복계의 장점
    • 실행 계획이 간단해 안정적
    • 예상 처리 시간이 거의 정확함
    • 트랙잭션 제어가 편리: 갱신 처리 중간에 오류 발생 시 대응이 용이하다는 것

SQL에서는 반복을 어떻게 표현할까?

  • SQL에서 반복계를 포장계로 바꾸러면, CASE 식 + WINDOW 함수 필요함
  • 책에서는 특정 테이블, 특정 예제에 대해 ROWS BETWEEN으로 직전 레코드 가져다가 처리하는 예제를 보여줬는데, 너무 한정적인 예제가 아닐지. 이런 포장계 치환(?)도 있다는 정도만 알아도 될듯
  • 상관 서브쿼리? 이것도 모르곘다.. 쿼리 겁나 복잡함
  • 우편번호 탐색 문제 -> n회 반복을 n회 CASE 식 분기로 변환. 여기에 WINDOW 함수까지 적용하면 풀스캔을 1회로 줄일 수 있음
    • 다만 WINDOW를 쓰기 때문에 정렬이 추가로 사용됨. 풀스캔이냐, 정렬이냐는 테이블 크기에 따라 판단
  • 인덱스 온리 스캔도 잠깐 언급
  • 인접 리스트 모델, 재귀 쿼리
    • 포인터 체인은 계층 구조를 표현하는 고전적인 방법
    • 포인터 체인을 사용한 테이블 형식을 인접 리스트 모델이라고 함
    • 모델이라니까 뭔가 어려워 보이는데, 음.. 정의가 어렵긴한데 그냥 chain rule과 비슷한 결이라 생각하면 쉽다
  • 체인의 가장 처음을 탐색하려고 할 때, 보통 반복문으로 많이 접근하는데, 이 경우 몇 번 반복해야 하는지 알 수 없음
    • 반복문으론 처리 쉬움
    • SQL에서 계층 구조 찾는 방법 중 하나: 재귀 공통 테이블 식(recursion common table expression) 사용하는 방법
  • Recursive Union
    • 알 것 같긴한데, 실행 계획까지 해석을 어렵
  • 재귀 공통 테이블이 없거나 실행 계획이 최적화되지 않은 DBMS의 경우, 대체 수단
    • SQL에서 계층 구조를 나타내는 방법: 인접 리스트 모델, 중첩 집합 모델, 경로 열거 모델
    • 중첩 집합 모델: 각 레코드의 데이터를 집합(원)으로 보고, 계층 구조를 집합의 중첩 관계로 나타냄
    • 좌표값을 부여해서, 이 값이 포함되는지 여부로 계층 구조를 파악할 수 있음
  • SQL을 감싸고 있는 애플리케이션과 실행 계획 모두 절차 지향형 언어로 구성됨. SQL만 집합 지향형.
    • 저자는 이런 상황에서 고성능의 RDB를 실현하려면, 절차 지향적인 사고방식에서 벗어날 필요가 있다고 함
    • 왠지 SQL이 잘못한 것 같은 느낌이 들기도 하고.. Codd 란 사람이 엄청난 통찰에 기반해서 집합 지향형을 고수한거라면
    • 도대체 그 통찰이 뭘까

NATURAL JOIN, USING

  • 자연 결합은 같은 이름의 필드를 등호로 묶어 자동으로 결합시켜주는 연산
  • 유연해보이지만, 그만큼 허술한 것 같기도 하고..
 SELECT *
   FROM table1
NATURAL JOIN table2;
  • 비슷하게 아래와 같이 USING을 사용해서 어떤 필드를 사용할건지만이라도 보여주는 방법이 있음
SELECT *
FROM table1
INNER JOIN table2
USING (field1);
  • 두 방식 모두 성능 면에서는 차이가 없으니, 특별한 사정 없으면 내부 결합 사용하는 것이 나을듯

CROSS JOIN

  • table1의 모든 행과 table2의 모든 행을 대응시켜 조인하는 연산
  • 데카르트 곱이라 불리는 연산
  • 결합 연산 중에서도 비용이 가장 많이 드는 연산
  • 아래와 같이 실행해도 크로스 결합이 된다고 함
SELECT *
FROM table1, table2
  • 책에서 얘기한 것과 달리 나는 실무에서 사용할 일이 종종 있었는데, 좀 복잡한 경우라 설명하기 번거롭다. 어쨌건 있었음..!
  • 크로스 결합은 다른 모든 결합 연산의 모체.
    • 내부 결합의 결과는 모두 크로스 결합 결과의 일부임

내부 결합

  • 그러면 내부 결합 = 크로스 결합 후 필터링인가? DBMS가 이런 무식한 알고리즘을 사용하지는 않는다고 함
    • 처음부터 결합 대상을 최대한 축소하는 방식으로 작동
  • 내부 결합은 기능적으로 상관 서브쿼리로 대체 가능
SELECT a.a_id1, (
  SELECT b.b_id1
  FROM table2 b
  WHERE a.a_id1 = b.b_id1
) AS a_id2
FROM table1 a;
  • 상관 서브쿼리를 스칼라 서브쿼리로 사용한 경우 대체 가능
  • 다만 이 쿼리를 결과 레코드 수만큼 상관 서브쿼리를 실행하기 때문에 비용이 꽤 높음

외부 결합

  • 오른쪽, 왼쪽, 풀 외부 결합 있음
  • 외부 결합 결과는 크로스 결합의 부분 집합이 아님
    • 전자는 NULL이 있을 수 있는데 후자는 없음. 간단하게 이해 가능

자기 결합

  • 그냥 같은 테이블에 다른 별칭 붙여서 일반적인 결합하는 셈. 자세히 안 다룸

결합 알고리즘과 성능

  • 내가 제일 궁금했던 부분
  • nested loops, hash, sort merge 세 알고리즘. 옵티마이저는 데이터 크기, 결합 키 분산 요인을 가지고 이 세가지 중 선택함

Nested Loops

  • 중첩 반복 알고리즘
  • 세부 처리 방식
    • driving table, outer table에서 레코드를 하나씩 반복해가며 스캔
    • driving table의 레코드 하나마다 inner table의 레코드를 하나씩 스캔해서 결합 조건에 맞으면 리턴
    • 이걸 driving table의 모든 레코드에 반복
  • driving table에 따라 성능이 달라지지 않을 것 같지만, driving table이 작을수록 성능이 좋아짐. 왜? 왜 계산량이 비대칭일까?
    • 조건이 있었음: inner table의 결합 키 필드에 인덱스가 존재해야 함. 당연한 것
  • 결합 키가 내부 테이블에 대해 유일하지 않을 경우, 인덱스로 내부 테이블에 접근해도 여러 개의 레코드가 히트될 가능성 있음
    • 히트되는 레코드가 작은 결합 키를 사용하는게 더 좋겠네 그럼?

  • driving table이 작은 nested loops + inner table의 결합 키 인덱싱 이 조합은 SQL 튜닝의 기본 중의 기본

  • 만약 driving table 레코드에 히트하는 inner table 레코드 수가 많다면?
    • 각각 n,m개 레코드라면 최대 n*m 레코드가 반환 (n < m>)
    • 강사는 오히려 driving table로 큰 테이블 선택하는게 나을 수도 있다고 함
    • 이 경우 최대 m*1 연산만 필요.
  • 또 다른 방법은 hash

Hash

  • 해시란 일종의 함수. 입력에 대해 어느 정도 유일서오가 균일성을 가진 값을 출력함. 시스템에서 굉장히 자주 사용됨
  • 해시 결합은
    • 일단 작은 테이블을 스캔
    • 결합 키에 해시 함수 적용해서 해시값으로 변환
    • 다른 테이블(큰 테이블) 스캔
    • 결합 키가 해시값에 존재하면 리턴
  • 그냥 결합 키로 바로 확인하면 안되나? 해시 테이블이라는 것을 새로 만드는 비용이 추가로 들 뿐 아닌가?
    • 책에서도 nested loops에 비해 메모리 크게 소모한다고 함
    • 만약 워킹 메모리가 부족하면 저장소 사용하니 더 지연됨
    • 해시값엔 순서가 없으니, 등치 결합에만 사용 가능
    • 양쪽 테이블 풀 스캔 필요.
  • 반대로 해시 결합이 유용한 경우가 있다고 함
    • 적절한 driving table이 없는 경우(상대적으로 충분히 작은 테이블이 없는 경우)
    • 적절한 driving table이 있어도 inner table에서 히트되는 레코드 수가 너무 많은 경우
    • inner table에 인덱스가 존재하지 않는 경우
  • 워킹 메모리 확보된 상태에서 해시 결합 사용하는 것이 좋음
  • 근데 해시가 어떻게 연산을 줄여주는지…?

Sort Merge

  • 이름 그대로, 결합 대상 테이블을 결합 키로 먼저 정렬시킨 다음, 일치하는 결합 키 찾으면 결합
  • 두 테이블을 모두 정렬해야 함
    • 해시는 한 쪽 테이블에 대해서만 해시 테이블 만듦. sort merge는 두 테이블 정렬이니 더 많은 메모리 사용하는 경우 있음
  • 부등호 결합에서도 사용 가능
  • 이게 성능 면에서 유용한 경우는, 테이블 정렬을 생략할 수 있는 경우라는데
    • DBMS의 sort merge 연산 함수가 에게 이 테이블은 이미 정렬되어 있다는걸 어떻게 알 수 있게 한단 말인가?

의도치 않은 크로스 결합

  • 세 테이블 조인 시 (삼각 결합)
    • 이거 사용하고 있던데…?
SELECT a.a1
FROM table1 a
INNER JOIN table2 b
ON a.a1 = b.b1
INNER JOIN table3 c
ON a.a1 = c.c1;
  • 이 경우 실행 계획 4가지 가능
    • table1을 driving table로 table2와 결합 후 그 결과를 table3과 결합
    • table1을 driving table로 table3와 결합 후 그 결과를 table2와 결합
    • table2을 driving table로 table1와 결합 후 그 결과를 table3와 결합
    • table3을 driving table로 table1와 결합 후 그 결과를 table2와 결합
  • 만약 table2, table3의 크기가 충분히 작다면, 이걸 table1에 각각 결합하는 것 보다는, 작은 테이블끼리 결합하고 결과를 큰 테이블에 결합하는 것이 더 합리적
    • 이런 경우 table2, table3은 결합 조건이 없이 결합하는 것으로 인식하기 때문에 크로스 결합해버림
  • 이걸 쿼리 단에서 조정할 수 있음. 신기하네
    • 방법은 간단한데, table2와 table3 사이에 불필여한 결합 조건을 부여하는 것. 결과에 영향 안 미치는 선에서
SELECT a.a1
FROM table1 a
INNER JOIN table2 b
ON a.a1 = b.b1
INNER JOIN table3 c
ON a.a1 = c.c1
AND b.b1= c.c1;
  • 결과에 영향 안 미치는 선에서 저런 조건 줄 수 있는 경우가 얼마나 될까…
    • 불가피하게 크로스 결합하느니 그냥 쿼리를 나누는게 낫지 않을까?

결합이 느리다면

  • 최적의 결합 알고리즘을 결합 대상 레코드 수의 관점에서 정리하면,
    • 소규모-소규모: 어떤 알고리즘이어도 성능 차이 작음
    • 소규모 - 대규모: 소규모를 driving table로 하는 nested loops 사용. 대규모 테이블 결합 키는 인덱싱 되어 있어야 함. 또 inner table의 결합 대상 레코드 너무 많으면 driving으로 사용하거나 해시 사용 검토 추천
    • 대규모 - 대규모: 일단 hash 사용. 결합 키로 처음 정렬되어 있다면 sort merge 사용
  • 사람의 판단으로 실행 계획을 변경하는 것은 DMBS의 의도에 역행하는 발상이라고 함
    • 옵티마이저가 답답한걸 어쩌라고..! 그래서 DBMS 개발자가 열심히 일하고 있다고 함

  • DBMS마다 사용자가 직접 제어 가능한 것도 있고, 불가능한 것도 있음. Spark SQL은?

  • 내가 기대한 것은, 그동안 내 쿼리가 잘못된 것임을 알길 원했는데, 여기선 DBMS 탓에 초점을 두네
  • 결합을 대체하는 다른 쿼리 사용하는 것도 방법이라고 함
  • 중간에 비정규화하면 SQL 성능의 변동 위험을 줄일 수도 있다는데, 이건 이해 못함

서브쿼리