EXPLAIN QUERY PLAN 명령

EXPLAIN QUERY PLAN 명령 (쿼리 계획 설명)

EXPLAIN QUERY PLAN 은 SQLite 가 특정 SQL 쿼리를 구현하기 위해 사용하는 전략이나 계획을 높은 수준으로 설명해주는 명령이에요. 특히 쿼리가 데이터베이스 인덱스를 어떻게 사용하는지 보여줘요.

출처: EXPLAIN QUERY PLAN

본문

1. EXPLAIN QUERY PLAN 명령

경고: EXPLAIN QUERY PLAN 명령이 반환하는 데이터는 대화형 디버깅을 위한 것뿐이에요. 출력 형식은 SQLite 릴리스 사이에 바뀔 수 있어요. 애플리케이션은 EXPLAIN QUERY PLAN 명령의 출력 형식에 의존하면 안 돼요.

주의: 위에서 경고했듯이 EXPLAIN QUERY PLAN 출력 형식은 3.24.0 릴리스(2018-06-04)에서 상당히 바뀌었어요. 추가로 사소한 변경이 3.36.0(2021-06-18)에서 발생했어요. 이후 릴리스에서도 추가 변경이 가능해요.

EXPLAIN QUERY PLAN SQL 명령은 SQLite 가 특정 SQL 쿼리를 구현하는 데 사용하는 전략이나 계획을 높은 수준으로 설명하기 위해 사용돼요. 가장 중요하게는 쿼리가 데이터베이스 인덱스를 어떻게 사용하는지 보고해요. 이 문서는 EXPLAIN QUERY PLAN 출력을 이해하고 해석하는 안내서예요. 배경 정보는 따로 제공돼요:

쿼리 계획은 트리로 표현돼요. sqlite3_step() 이 반환하는 원시 형태에서 트리의 각 노드는 네 개의 필드로 구성돼요. 정수 노드 id, 정수 부모 id, 현재 사용되지 않는 보조 정수 필드, 그리고 노드의 설명이에요. 따라서 전체 트리는 네 개의 컬럼과 0 개 이상의 행을 가진 테이블이에요. 명령줄 셸은 보통 이 테이블을 가로채서 편리하게 보기 위해 ASCII-art 그래프로 렌더링해요. 셸의 자동 그래프 렌더링을 비활성화하고 EXPLAIN QUERY PLAN 출력을 표 형식으로 표시하려면 ".explain off" 명령을 실행해 "EXPLAIN formatting mode" 를 off 로 설정해요. 자동 그래프 렌더링을 복원하려면 ".explain auto" 를 실행해요. 현재 "EXPLAIN formatting mode" 설정은 ".show" 명령으로 볼 수 있어요.

".eqp on" 명령으로 CLI 를 자동 EXPLAIN QUERY PLAN 모드로 설정할 수도 있어요:

sqlite> .eqp on

자동 EXPLAIN QUERY PLAN 모드에서 셸은 입력한 각 문장에 대해 별도의 EXPLAIN QUERY PLAN 쿼리를 자동 실행하고, 실제 쿼리를 실행하기 전에 결과를 표시해요. 자동 EXPLAIN QUERY PLAN 모드를 끄려면 ".eqp off" 명령을 사용해요.

EXPLAIN QUERY PLAN 은 SELECT 문에서 가장 유용하지만, 데이터베이스 테이블에서 데이터를 읽는 다른 문장(UPDATE, DELETE, INSERT INTO ... SELECT 등)에도 나타날 수 있어요.

1.1. 테이블 및 인덱스 스캔

SELECT (또는 다른) 문을 처리할 때 SQLite 는 다양한 방식으로 데이터베이스 테이블에서 데이터를 가져올 수 있어요. 테이블의 모든 레코드를 스캔하거나(전체 테이블 스캔), rowid 인덱스를 기반으로 테이블의 연속된 레코드 부분집합을 스캔하거나, 데이터베이스 인덱스의 연속된 항목 부분집합을 스캔하거나, 단일 스캔에서 위 전략들의 조합을 사용할 수 있어요. SQLite 가 테이블이나 인덱스에서 데이터를 가져올 수 있는 다양한 방법은 여기에서 자세히 설명돼요.

쿼리가 읽는 각 테이블에 대해 EXPLAIN QUERY PLAN 출력에는 "detail" 컬럼 값이 "SCAN" 이나 "SEARCH" 로 시작하는 레코드가 포함돼요. "SCAN" 은 전체 테이블 스캔에 사용되는데, SQLite 가 인덱스가 정의한 순서로 테이블의 모든 레코드를 반복하는 경우를 포함해요. "SEARCH" 는 테이블 행의 일부만 방문한다는 것을 나타내요. 각 SCAN 또는 SEARCH 레코드는 다음 정보를 포함해요:

  • 데이터를 읽는 테이블, 뷰, 서브쿼리의 이름
  • 인덱스나 자동 인덱스가 사용되는지 여부
  • covering index 최적화가 적용되는지 여부
  • WHERE 절의 어떤 용어가 인덱싱에 사용되는지

예를 들어, 다음 EXPLAIN QUERY PLAN 명령은 테이블 t1 에서 전체 테이블 스캔을 수행해 구현되는 SELECT 문에 대해 동작해요:

sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
QUERY PLAN
`--SCAN t1

쿼리가 인덱스를 사용할 수 있다면 SCAN/SEARCH 레코드에 인덱스 이름이 포함되고, SEARCH 레코드의 경우 방문한 행 부분집합이 어떻게 식별되는지에 대한 표시가 포함돼요. 예를 들어:

sqlite> CREATE INDEX i1 ON t1(a);
sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
QUERY PLAN
`--SEARCH t1 USING INDEX i1 (a=?)

이전 예에서 SQLite 는 인덱스 "i1" 을 사용해 (a=?) 형태의 WHERE 절 용어를 최적화해요. 이 경우 "a=1" 이에요. 이전 예는 covering index 를 사용할 수 없었지만, 다음 예는 사용할 수 있고, 그 사실이 출력에 반영돼요:

sqlite> CREATE INDEX i2 ON t1(a, b);
sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
QUERY PLAN
`--SEARCH t1 USING COVERING INDEX i2 (a=?)

SQLite 의 모든 조인은 중첩 스캔을 사용해 구현돼요. 조인이 포함된 SELECT 쿼리를 EXPLAIN QUERY PLAN 으로 분석하면 중첩 루프마다 하나의 SCAN 또는 SEARCH 레코드가 출력돼요. 예를 들어:

sqlite> EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2;
QUERY PLAN
|--SEARCH t1 USING INDEX i2 (a=? AND b>?)
`--SCAN t2

항목의 순서는 중첩 순서를 나타내요. 이 경우 인덱스 i2 를 사용한 테이블 t1 의 스캔이 바깥 루프(먼저 나타나므로)이고, 테이블 t2 의 전체 테이블 스캔이 안쪽 루프(마지막에 나타나므로)예요. 다음 예에서는 SELECT 의 FROM 절에서 t1 과 t2 의 위치가 뒤집혀 있어요. 쿼리 전략은 동일하게 유지돼요. EXPLAIN QUERY PLAN 의 출력은 쿼리가 실제로 어떻게 평가되는지를 보여주지, SQL 문장에서 어떻게 지정됐는지를 보여주지 않아요.

sqlite> EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2;
QUERY PLAN
|--SEARCH t1 USING INDEX i2 (a=? AND b>?)
`--SCAN t2

쿼리의 WHERE 절에 OR 표현식이 포함되어 있으면 SQLite 는 "OR by union" 전략(OR 최적화라고도 함)을 사용할 수 있어요. 이 경우 각 인덱스에 대해 하나씩 두 개의 하위 레코드가 있는 단일 최상위 검색 레코드가 있어요:

sqlite> CREATE INDEX i3 ON t1(b);
sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2;
QUERY PLAN
`--MULTI-INDEX OR
   |--SEARCH t1 USING COVERING INDEX i2 (a=?)
   `--SEARCH t1 USING INDEX i3 (b=?)

1.2. 임시 정렬 B-Tree

SELECT 쿼리에 ORDER BY, GROUP BY 또는 DISTINCT 절이 포함되어 있으면 SQLite 는 출력 행을 정렬하기 위해 임시 b-tree 구조를 사용해야 할 수 있어요. 아니면 인덱스를 사용할 수도 있어요. 인덱스를 사용하는 것이 거의 항상 정렬보다 훨씬 효율적이에요. 임시 b-tree 가 필요하면 EXPLAIN QUERY PLAN 출력에 "detail" 필드가 "USE TEMP B-TREE FOR xxx" 형태의 문자열 값으로 설정된 레코드가 추가되는데, 여기서 xxx 는 "ORDER BY", "GROUP BY" 또는 "DISTINCT" 중 하나예요. 예를 들어:

sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c;
QUERY PLAN
|--SCAN t2
`--USE TEMP B-TREE FOR ORDER BY

이 경우 t2(c) 에 인덱스를 만들어서 임시 b-tree 사용을 피할 수 있어요:

sqlite> CREATE INDEX i4 ON t2(c);
sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c;
QUERY PLAN
`--SCAN t2 USING INDEX i4

1.3. 서브쿼리

위의 모든 예에는 SELECT 문이 하나만 있었어요. 쿼리에 하위 SELECT 가 포함되어 있으면 그것들이 바깥 SELECT 의 자식으로 표시돼요. 예를 들어:

sqlite> EXPLAIN QUERY PLAN SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2;
|--SCAN TABLE t2 USING COVERING INDEX i4
|--SCALAR SUBQUERY
|  `--SEARCH t1 USING COVERING INDEX i2 (a=?)
`--CORRELATED SCALAR SUBQUERY
   `--SEARCH t1 USING INDEX i3 (b=?)

위 예에는 두 개의 "SCALAR" 서브쿼리가 포함돼 있어요. 서브쿼리는 단일 값을 반환한다는 점에서 SCALAR 이에요. 즉, 한 행 한 컬럼 테이블이에요. 실제 쿼리가 그보다 더 많이 반환하면 첫 번째 행의 첫 번째 컬럼만 사용돼요.

위의 첫 번째 서브쿼리는 바깥 쿼리에 대해 상수예요. 첫 번째 서브쿼리의 값은 한 번 계산된 다음 바깥 SELECT 의 각 행에 대해 재사용될 수 있어요. 하지만 두 번째 서브쿼리는 "CORRELATED" 이에요. 두 번째 서브쿼리의 값은 바깥 쿼리의 현재 행 값에 따라 달라져요. 따라서 두 번째 서브쿼리는 바깥 SELECT 의 각 출력 행마다 한 번씩 실행되어야 해요.

flattening 최적화가 적용되지 않으면, SELECT 문의 FROM 절에 서브쿼리가 나타날 때 SQLite 는 서브쿼리를 실행해 결과를 임시 테이블에 저장하거나, 서브쿼리를 코루틴(co-routine)으로 실행할 수 있어요. 다음 쿼리는 후자의 예시예요. 서브쿼리는 코루틴으로 실행돼요. 바깥 쿼리는 서브쿼리에서 또 다른 입력 행이 필요할 때마다 블록돼요. 제어가 원하는 출력 행을 생성하는 코루틴으로 전환된 다음, 처리를 계속하는 주 루틴으로 제어가 다시 전환돼요.

sqlite> EXPLAIN QUERY PLAN SELECT count(*)
      > FROM (SELECT max(b) AS x FROM t1 GROUP BY a) AS qqq
      > GROUP BY x;
QUERY PLAN
|--CO-ROUTINE qqq
|  `--SCAN t1 USING COVERING INDEX i2
|--SCAN qqqq
`--USE TEMP B-TREE FOR GROUP BY

SELECT 문의 FROM 절에 있는 서브쿼리에 flattening 최적화를 사용하면, 그 서브쿼리가 효과적으로 바깥 쿼리에 병합돼요. EXPLAIN QUERY PLAN 의 출력은 다음 예처럼 이를 반영해요:

sqlite> EXPLAIN QUERY PLAN SELECT * FROM (SELECT * FROM t2 WHERE c=1) AS t3, t1;
QUERY PLAN
|--SEARCH t2 USING INDEX i4 (c=?)
`--SCAN t1

서브쿼리의 내용을 두 번 이상 방문해야 할 수도 있다면 코루틴 사용은 바람직하지 않아요. 코루틴이 데이터를 두 번 이상 계산해야 하기 때문이에요. 그리고 서브쿼리를 flatten 할 수 없다면 서브쿼리를 임시 테이블로 구체화(manifest)해야 해요.

sqlite> SELECT * FROM
      >   (SELECT * FROM t1 WHERE a=1 ORDER BY b LIMIT 2) AS x,
      >   (SELECT * FROM t2 WHERE c=1 ORDER BY d LIMIT 2) AS y;
QUERY PLAN
|--MATERIALIZE x
|  `--SEARCH t1 USING COVERING INDEX i2 (a=?)
|--MATERIALIZE y
|  |--SEARCH t2 USING INDEX i4 (c=?)
|  `--USE TEMP B-TREE FOR ORDER BY
|--SCAN x
`--SCAN y

1.4. 복합 쿼리

복합 쿼리(UNION, UNION ALL, EXCEPT 또는 INTERSECT)의 각 구성 쿼리는 별도로 계산되며 EXPLAIN QUERY PLAN 출력에서 자신만의 줄을 가져요.

sqlite> EXPLAIN QUERY PLAN SELECT a FROM t1 UNION SELECT c FROM t2;
QUERY PLAN
`--COMPOUND QUERY
   |--LEFT-MOST SUBQUERY
   |  `--SCAN t1 USING COVERING INDEX i1
   `--UNION USING TEMP B-TREE
      `--SCAN t2 USING COVERING INDEX i4

위 출력의 "USING TEMP B-TREE" 절은 두 하위 SELECT 결과의 UNION 을 구현하는 데 임시 b-tree 구조가 사용된다는 것을 나타내요. 복합 쿼리를 계산하는 다른 방법은 각 서브쿼리를 코루틴으로 실행하고, 그 출력이 정렬된 순서로 나타나도록 정렬해 결과를 병합하는 것이에요. 쿼리 플래너가 후자의 접근 방식을 선택하면 EXPLAIN QUERY PLAN 출력은 다음과 같아요:

sqlite> EXPLAIN QUERY PLAN SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1;
QUERY PLAN
`--MERGE (EXCEPT)
   |--LEFT
   |  `--SCAN t1 USING COVERING INDEX i1
   `--RIGHT
      |--SCAN t2
      `--USE TEMP B-TREE FOR ORDER BY

더 알아보기 (Learn more)