실행 계획

실행 계획은 데이터베이스가 질의 하나를 어떤 절차로 처리할지 미리 정해 둔 것입니다. 같은 답을 내는 절차가 여럿이라 그중 하나를 골라 둡니다. 질의가 오래 걸릴 때 무엇을 고쳐야 할지는 이 계획을 열어 봐야 알 수 있습니다. 업무에서 말하는 계획서와는 다른 것입니다.

상세

목적지만 말하는 길 안내와 닮았습니다. 「강남역으로 가 줘」라고 말하면 어느 길로 갈지는 안내기가 고릅니다. 막히는 큰길과 신호가 잦은 골목 가운데 하나를 골라 길 한 벌을 그려 줍니다.

실행 계획은 데이터베이스가 질의 하나를 처리하려고 고른 절차 한 벌입니다. 어느 표를 어떤 방법으로 읽을지, 읽어 온 것을 어떤 순서로 조인하고 거르고 정렬할지가 여기 적혀 있습니다. 조인은 표 둘을 한 열의 값으로 맞붙이는 일입니다. 질의 계획이라고 부르기도 합니다.

질의 문장에 없는 절차

SQL(Structured Query Language, 구조화 질의어) 문장은 원하는 답만 적습니다. 무엇을 어떤 순서로 읽으라는 절차는 적지 않습니다. 이렇게 원하는 것만 적고 방법을 안 적는 방식을 선언형이라고 부릅니다.

절차를 안 적으면 편합니다. 데이터가 늘어도 질의 문장은 손대지 않습니다. 절차는 데이터베이스가 그때그때 다시 고릅니다.

대신 절차를 누군가는 정해야 합니다. 그 일을 맡은 부품이 질의 옵티마이저입니다. 옵티마이저가 내놓는 결과물이 실행 계획입니다.

계획을 세우는 절차

옵티마이저는 후보를 여럿 만들어 놓고 그중 하나를 고릅니다. 사용자 번호로 주문을 찾는 질의 하나에도 방법이 여럿입니다. 주문 표를 앞에서 뒤까지 읽고 조건에 맞는 행만 남길 수 있습니다. 사용자 번호 인덱스로 해당 행의 위치를 먼저 찾고 그 행만 읽을 수도 있습니다.

후보마다 얼마나 걸릴지를 숫자로 어림합니다. 이 숫자를 비용이라고 부릅니다. 읽어야 하는 행이 몇 개인지, 디스크를 몇 번 건드리는지가 비용을 좌우합니다. 제일 싼 후보가 계획이 됩니다.

후보를 견주는 모양은 이렇습니다.

flowchart TD
    Q["질의 하나"] --> A["후보 · 표를 앞에서 뒤까지"]
    Q --> B["후보 · 인덱스로 집기"]
    Q --> C["후보 · 조인 순서를 바꿔서"]
    A --> P["비용을 견줘 하나를 고른다"]
    B --> P
    C --> P
    P --> R["실행 계획"]

후보 수는 질의가 복잡해질수록 빠르게 늘어납니다. 표 셋을 조인하는 질의라면 조인 순서만으로도 여러 갈래가 생깁니다. 그래서 옵티마이저는 후보를 끝까지 다 세어 보지 않고 가망 없는 갈래를 중간에 버립니다.

갈래가 층마다 불어나고 그중 하나가 끊기는 모양은 이렇습니다.

flowchart TD
    Q3["표 셋을 조인하는 질의"]
    subgraph L1["1층 · 먼저 조인할 짝"]
        J1["주문 · 사용자"]
        J2["사용자 · 상품"]
        J3["주문 · 상품"]
    end
    subgraph L2["2층 · 남은 표를 붙이는 순서"]
        K1["+ 상품 · 인덱스로"]
        K2["+ 상품 · 앞에서 뒤까지"]
        K3["+ 주문 · 인덱스로"]
        X["여기서 버린다"]
    end
    Q3 --> J1
    Q3 --> J2
    Q3 --> J3
    J1 --> K1
    J1 --> K2
    J2 --> K3
    J3 --> X

층마다 갈래가 둘씩만 늘어도 표가 하나 더 붙을 때마다 후보가 배로 불어납니다. 그림은 대표만 그린 것이고 실제 갈래는 더 많습니다.

계획의 모양

계획은 연산자를 쌓아 만든 나무 모양입니다. 연산자는 「표를 읽어라」·「두 줄기를 조인해라」· 「정렬해라」처럼 한 가지 일만 하는 부품입니다.

아래쪽 연산자가 내놓은 행을 위쪽 연산자가 받아 씁니다. 맨 위 연산자가 그 행을 내보냅니다. 내보낸 행이 질의의 답입니다.

주문과 사용자를 함께 가져오는 질의를 하나 놓고 보겠습니다.

SELECT u.name, o.total
FROM orders o JOIN users u ON u.id = o.user_id
WHERE o.user_id = 7;

사용자 번호가 7 로 못 박혀 있으니 사용자 표에서는 한 행만 읽으면 됩니다. 계획은 이런 나무가 됩니다.

아래 그림의 화살표는 「이 연산자가 무엇에서 행을 받는지」를 가리킵니다. 행이 흐르는 방향은 반대입니다. 그래서 읽는 순서는 아래에서 위입니다.

flowchart TD
    T["내보내기"] --> J["조인 · 사용자 번호로"]
    J --> S1["주문 · 인덱스로 집기"]
    J --> S2["사용자 · 한 행 읽기"]

잎에 해당하는 연산자가 표나 인덱스에서 행을 꺼냅니다. 그 위 연산자가 받아 조인합니다. 다시 그 위가 정렬하거나 개수를 셉니다.

데이터를 읽는 두 방법

나무의 잎에는 표에서 행을 꺼내는 연산자가 놓입니다. 꺼내는 방법은 크게 둘입니다.

두 방법이 주문 표를 건드리는 자국은 이렇게 다릅니다.

flowchart TD
    SEQ["순차 스캔"]
    subgraph IDX["인덱스 스캔"]
        I1["인덱스 항목 · 3번 행"]
        I2["인덱스 항목 · 6번 행"]
        I3["인덱스 항목 · 2번 행"]
    end
    subgraph TBL["주문 표"]
        R1["1번 행"] --> R2["2번 행"] --> R3["3번 행"] --> R4["4번 행"] --> R5["5번 행"] --> R6["6번 행"]
    end
    SEQ --> R1
    I1 --> R3
    I2 --> R6
    I3 --> R2

순차 스캔은 첫 행에서 마지막 행까지 한 줄기로 내려갑니다. 인덱스 스캔은 흩어진 행을 한 행씩 따로 읽습니다.

표를 모두 읽는 편이 나을 때가 있다는 것은 뜻밖입니다. 인덱스로 찾아낸 행은 표 안에 흩어져 있어서 한 행씩 따로 읽어야 합니다. 찾을 행이 많으면 이 따로 읽기가 쌓여, 앞에서 뒤로 쭉 읽는 것보다 오래 걸립니다.

비용을 어림하는 근거

비용은 어림입니다. 어림의 바탕은 통계입니다. 통계는 표마다 따로 모아 두는 요약값입니다. 행이 몇 개인지, 어떤 값이 얼마나 자주 나오는지가 들어 있습니다.

옵티마이저는 이 요약값으로 「조건에 맞는 행이 몇 개일까」를 먼저 어림합니다. 어림한 행 수가 실제와 크게 다르면 계획도 어긋납니다. 열 행쯤이라고 보고 인덱스를 골랐다고 해 보겠습니다. 실제로 십만 행이면 한 행씩 따로 읽는 일이 십만 번 일어납니다.

어림이 맞았을 때와 어긋났을 때가 어디서 갈리는지는 이렇습니다.

flowchart TD
    S["통계"] --> E["조건에 맞는 행 수 어림 · 10행"]
    E --> P["제일 싼 후보 · 인덱스 스캔"]
    P --> OK["실제 12행 · 따로 읽기 12번"]
    P --> NG["실제 100,000행 · 따로 읽기 100,000번"]

고른 계획은 양쪽이 같습니다. 갈리는 것은 실제 행 수뿐이고, 그 차이가 그대로 따로 읽기 횟수가 됩니다.

통계는 저절로 최신이 되지 않습니다. 데이터가 크게 바뀐 뒤에는 다시 모아야 합니다. 여러 데이터베이스가 이 일을 하는 명령에 ANALYZE 라는 이름을 씁니다.

계획을 꺼내 보는 명령

질의가 오래 걸릴 때 제일 먼저 하는 일은 계획을 열어 보는 것입니다. 대부분의 데이터베이스가 이 목적으로 EXPLAIN 이라는 명령을 둡니다. 질의 앞에 붙이면 질의를 실행하지 않고 고른 계획만 보여 줍니다.

돌려주는 것은 앞에서 본 나무입니다.

EXPLAIN SELECT * FROM orders
 WHERE user_id = 7;   -- 고른 계획이 나무로 나온다

연산자마다 어림한 행 수와 비용이 붙어 나옵니다. 출력 모양은 데이터베이스마다 다릅니다.

여기 붙는 행 수는 어림일 뿐 실제로 읽은 수가 아닙니다. 그래서 질의를 진짜로 돌린 뒤 읽은 행 수와 걸린 시간까지 보여 주는 모드도 함께 둡니다. 흔히 EXPLAIN ANALYZE 라고 씁니다.

앞 소절에서 통계를 다시 모으던 ANALYZE 와는 이름만 같습니다. 이쪽은 통계를 건드리지 않고 질의 한 번을 재는 일입니다. 어림과 실측이 크게 어긋난 연산자가 곧 손볼 대목입니다.

계획이 바뀌는 때

질의 문장을 손대지 않아도 계획은 바뀝니다. 데이터가 늘거나 줄면 어림한 행 수가 달라집니다. 인덱스를 새로 만들거나 지우면 후보 자체가 달라집니다. 통계를 다시 모으면 어제와 다른 후보가 싸 보일 수 있습니다.

이 성질은 양날입니다. 데이터가 바뀌어도 사람이 질의를 고칠 일이 없습니다. 대신 어제까지 잘 돌던 질의가 오늘 갑자기 오래 걸릴 수 있습니다. 배포한 것이 없이 응답만 늦어졌다면 계획이 바뀐 쪽을 의심합니다.

한 번 세운 계획을 다시 쓰기

모양이 같은 질의가 초마다 여러 번 들어오면, 매번 계획을 새로 세우는 일이 짐이 됩니다. 그래서 한 번 세운 계획을 담아 두고 다시 쓰는 방식이 있습니다. 문장 틀을 미리 넘겨 두고 값만 바꿔 끼우는 프리페어드 스테이트먼트가 이 방식을 씁니다.

첫 질의와 두 번째 질의가 어떻게 달라지는지를 순서대로 놓으면 이렇습니다.

sequenceDiagram
    participant 앱
    participant 데이터베이스
    앱->>데이터베이스: 문장 틀을 넘긴다
    데이터베이스->>데이터베이스: 계획을 세워 담아 둔다
    앱->>데이터베이스: 값 7
    데이터베이스-->>앱: 담아 둔 계획으로 실행
    앱->>데이터베이스: 값 9
    데이터베이스-->>앱: 같은 계획을 다시 쓴다
    Note over 앱,데이터베이스: 9 번 사용자는 주문이 수십만 건이어도 계획은 그대로다

다시 쓰면 계획을 세우는 비용이 사라집니다. 값에 따라 알맞은 절차가 달라지는 질의에서는 엉뚱한 계획을 물려받습니다. 어떤 사용자는 주문이 몇 건이고 어떤 사용자는 수십만 건이라면, 한 계획이 둘 다에 맞기는 어렵습니다.

데이터베이스 밖에서 쓰는 같은 말

데이터베이스 밖에서도 같은 말을 씁니다. 여러 대에 나눠 계산하는 분산 처리 엔진과 검색 엔진도 요청을 받으면 절차를 고릅니다. 고른 절차를 여기서도 실행 계획이라고 부릅니다.

이름이 같은 이유도 같습니다. 무엇을 원하는지만 받는 쪽은 절차를 스스로 정해야 합니다. 정한 절차는 어딘가에 적어 둬야 합니다. 그래야 실행할 수도, 사람이 열어 볼 수도 있습니다.

관련 항목

실행 계획을 만드는 데이터베이스 부품

질의 옵티마이저 · 질의 계획기 · 파서 · 질의 재작성 · 통계 · 비용 모델 · 카디널리티 추정

계획 안에 놓이는 연산자

순차 스캔 · 인덱스 스캔 · 비트맵 스캔 · 조인 · 중첩 루프 조인 · 해시 조인 · 병합 조인 · 정렬 · 집계 함수 · 조인 순서

계획이 읽어 들이는 저장 구조

인덱스 · B-tree · 테이블 · 파티션 · 구체화 뷰

계획을 꺼내 보고 손보는 수단

EXPLAIN · ANALYZE · EXPLAIN ANALYZE · 질의 힌트 · 프리페어드 스테이트먼트 · 계획 캐시 · 슬로우 쿼리 로그

실행 계획이 나오는 질의 처리 단계

질의 · 파싱 · 실행 · SQL · 선언형 프로그래밍 · 관계 대수

계획이 어긋났을 때 나는 문제

전체 테이블 스캔 · 파라미터 스니핑 · N+1 · 인덱스는 많을수록 좋다