SQLite 질의 최적화 프로그램 개요

SQLite 질의 최적화 프로그램 개요 (The SQLite Query Optimizer Overview)

이 문서는 SQLite의 질의 계획기(planner)와 최적화 프로그램(optimizer)이 어떻게 동작하는지 개요를 제공해요. 단일 SQL 문에도 문 자체와 기본 데이터베이스 스키마의 복잡성에 따라 수십, 수백, 심지어 수천 가지 구현 방법이 있을 수 있어요.

출처: The SQLite Query Optimizer Overview 문서

본문

이 문서는 SQLite의 질의 계획기와 최적화 프로그램이 어떻게 동작하는지에 대한 개요를 제공해요.

단일 SQL 문이 주어지면, 문 자체와 기본 데이터베이스 스키마의 복잡성에 따라 그 문을 구현하는 수십, 수백, 심지어 수천 가지 방법이 있을 수 있어요. 질의 계획기의 임무는 디스크 I/O와 CPU 오버헤드를 최소화하는 알고리즘을 선택하는 것이에요.

추가 배경 정보는 인덱싱 튜토리얼 문서에서 확인할 수 있어요. 차세대 질의 계획기 문서는 조인 순서가 선택되는 방식에 대한 더 자세한 정보를 제공해요.

2. WHERE 절 분석

분석 전에 모든 조인 제약을 WHERE 절로 옮기기 위해 다음 변환이 수행돼요.

모든 NATURAL 조인이 USING 절이 있는 조인으로 변환돼요.
모든 USING 절(이전 단계에서 만든 것 포함)이 동등한 ON 절로 변환돼요.
모든 ON 절(이전 단계에서 만든 것 포함)이 WHERE 절의 새 접속항(AND 연결 항)으로 추가돼요.

SQLite는 WHERE 절에 있는 조인 제약과 내부 조인의 ON 절에 있는 제약을 구분하지 않아요. 그 구분이 결과에 영향을 주지 않기 때문이에요. 하지만 외부 조인(outer join)에서는 ON 절 제약과 WHERE 절 제약 사이에 차이가 있어요. 따라서 SQLite가 외부 조인의 ON 절 제약을 WHERE 절로 옮길 때, 제약이 외부 조인에서 왔고 어느 외부 조인에서 왔는지 나타내는 특수 태그를 추상 구문 트리(AST)에 추가해요. 순수 SQL 텍스트로는 그 태그를 추가할 방법이 없어요. 따라서 SQL 입력은 외부 조인에 ON 절을 사용해야 해요. 하지만 내부 AST에서는 모든 제약이 WHERE 절의 일부예요. 모든 것을 한 곳에 두면 처리가 단순해지기 때문이에요.

모든 제약이 WHERE 절로 옮겨진 후, WHERE 절은 접속항(이하 "항(terms)")으로 분해돼요. 다시 말해 WHERE 절은 AND 연산자로 서로 구분되는 조각으로 나뉘어요. WHERE 절이 OR 연산자로 구분되는 제약(분리항, disjuncts)으로 구성되면 전체 절은 OR-절 최적화가 적용되는 단일 "항"으로 간주돼요.

WHERE 절의 모든 항은 인덱스로 충족될 수 있는지 분석돼요. 인덱스에 사용되려면 항은 보통 다음 형태 중 하나여야 해요.

  column = expression
  column IS expression
  column > expression
  column >= expression
  column < expression
  column <= expression
  expression = column
  expression IS column
  expression > column
  expression >= column
  expression < column
  expression <= column
  column IN (expression-list)
  column IN (subquery)
  column IS NULL
  column LIKE pattern
  column GLOB pattern

이런 문으로 인덱스를 만들면:

CREATE INDEX idx_ex1 ON ex1(a,b,c,d,e,...,y,z);

인덱스의 초기 열(열 a, b 등)이 WHERE 절 항에 나타나면 인덱스가 사용될 수 있어요. 인덱스의 초기 열은 = 또는 IN 또는 IS 연산자와 함께 사용되어야 해요. 사용되는 가장 오른쪽 열은 부등식(inequality)을 사용할 수 있어요. 사용되는 인덱스의 가장 오른쪽 열에 대해, 열의 허용 값을 두 극단 사이에 끼우는 부등식이 최대 두 개 있을 수 있어요.

인덱스를 사용하기 위해 인덱스의 모든 열이 WHERE 절 항에 나타날 필요는 없어요. 하지만 사용되는 인덱스 열에 빈틈(gap)이 있어서는 안 돼요. 따라서 위의 예제 인덱스에서 열 c를 제약하는 WHERE 절 항이 없으면, 열 a와 b를 제약하는 항은 인덱스와 함께 사용할 수 있지만 열 d부터 z를 제약하는 항은 사용할 수 없어요. 마찬가지로 부등식으로만 제약되는 열의 오른쪽에 있는 인덱스 열은 (인덱싱 목적으로) 보통 사용되지 않아요. (예외는 아래의 skip-scan 최적화를 참조하세요.)

표현식 인덱스의 경우, 앞선 텍스트에서 "column"이라는 단어가 사용될 때마다 "인덱스된 표현식"(CREATE INDEX 문에 나타나는 표현식의 복사본)으로 대체할 수 있고 모든 것이 동일하게 동작해요.

2.1. 인덱스 항 사용 예제

위 인덱스와 이런 WHERE 절에 대해:

... WHERE a=5 AND b IN (1,2,3) AND c IS NULL AND d='hello'

인덱스의 처음 네 열 a, b, c, d는 그 네 열이 인덱스의 접두사를 형성하고 모두 동등성 제약으로 묶이므로 사용 가능해요.

위 인덱스와 이런 WHERE 절에 대해:

... WHERE a=5 AND b IN (1,2,3) AND c>12 AND d='hello'

인덱스의 열 a, b, c만 사용 가능해요. d 열은 c의 오른쪽에 있고 c는 부등식으로만 제약되므로 사용할 수 없어요.

위 인덱스와 이런 WHERE 절에 대해:

... WHERE a=5 AND b IN (1,2,3) AND d='hello'

인덱스의 열 a와 b만 사용 가능해요. d 열은 열 c가 제약되지 않고 인덱스가 사용할 수 있는 열 집합에 빈틈이 있을 수 없으므로 사용할 수 없어요.

위 인덱스와 이런 WHERE 절에 대해:

... WHERE b IN (1,2,3) AND c NOT NULL AND d='hello'

인덱스의 가장 왼쪽 열(열 "a")이 제약되지 않으므로 인덱스는 전혀 사용할 수 없어요. 다른 인덱스가 없다고 가정하면 위 질의는 전체 테이블 스캔이 돼요.

위 인덱스와 이런 WHERE 절에 대해:

... WHERE a=5 OR b IN (1,2,3) OR c NOT NULL OR d='hello'

WHERE 절 항이 AND 대신 OR로 연결되므로 인덱스는 사용할 수 없어요. 이 질의는 전체 테이블 스캔이 돼요. 하지만 열 b, c, d를 가장 왼쪽 열로 포함하는 인덱스 세 개가 추가되면 OR-절 최적화가 적용될 수 있어요.

3. BETWEEN 최적화

WHERE 절 항이 다음 형태이면:

  expr1 BETWEEN expr2 AND expr3

다음과 같이 두 개의 "가상" 항이 추가돼요.

  expr1 >= expr2 AND expr1 <= expr3

가상 항은 분석에만 사용되며 어떤 바이트코드도 생성하지 않아요. 두 가상 항이 모두 인덱스에 대한 제약으로 사용되면 원래 BETWEEN 항은 생략되고 입력 행에 대해 해당 테스트가 수행되지 않아요. 따라서 BETWEEN 항이 인덱스 제약으로 사용되면 그 항에 대해 테스트가 절대 수행되지 않아요. 반면 가상 항 자체는 입력 행에 대해 테스트를 수행하게 하지 않아요. 따라서 BETWEEN 항이 인덱스 제약으로 사용되지 않고 대신 입력 행을 테스트하는 데 사용되어야 하면, expr1 표현식은 한 번만 평가돼요.

4. OR 최적화

AND 대신 OR로 연결되는 WHERE 절 제약은 두 가지 다른 방식으로 처리될 수 있어요.

4.1. OR 연결 제약을 IN 연산자로 변환

항이 공통 열 이름을 포함하고 OR로 구분되는 여러 하위항으로 구성되면, 예를 들면:

  column = expr1 OR column = expr2 OR column = expr3 OR ...

그 항은 다음과 같이 다시 쓰여요:

  column IN (expr1,expr2,expr3,...)

다시 쓰여진 항은 그런 다음 IN 연산자의 일반 규칙으로 인덱스를 제약할 수 있어요. column은 모든 OR 연결 하위항에서 같은 열이어야 하지만, 열은 = 연산자의 왼쪽이나 오른쪽 어느 쪽에도 올 수 있다는 점에 주목하세요.

4.2. OR 제약을 별도로 평가하고 결과의 UNION 취하기

앞서 설명한 OR을 IN 연산자로 변환하는 것이 동작하지 않는 경우에만 두 번째 OR-절 최적화가 시도돼요. OR 절이 다음과 같이 여러 하위항으로 구성되어 있다고 가정해 봐요.

  expr1 OR expr2 OR expr3

개별 하위항은 a=5나 x>y 같은 단일 비교 표현식이거나, LIKE나 BETWEEN 표현식이거나, AND 연결 하위-하위항의 괄호 목록일 수 있어요. 각 하위항은 하위항 자체가 인덱스로 사용 가능한지 보기 위해 마치 그것이 전체 WHERE 절인 것처럼 분석돼요. OR 절의 모든 하위항이 개별적으로 인덱스 가능하면, OR 절은 OR 절의 각 항을 평가하는 데 별도의 인덱스가 사용되도록 코딩될 수 있어요. SQLite가 각 OR 절 항에 별도 인덱스를 사용하는 방식을 생각하는 한 가지 방법은 WHERE 절이 다음과 같이 다시 쓰여진 것처럼 상상하는 거예요.

  rowid IN (SELECT rowid FROM table WHERE expr1
            UNION SELECT rowid FROM table WHERE expr2
            UNION SELECT rowid FROM table WHERE expr3)

위의 다시 쓰여진 표현식은 개념적인 것이에요. OR을 포함하는 WHERE 절이 실제로 이렇게 다시 쓰여지지는 않아요. OR 절의 실제 구현은 더 효율적이고 WITHOUT ROWID 테이블이나 "rowid"에 접근할 수 없는 테이블에서도 동작하는 메커니즘을 사용해요. 그럼에도 구현의 본질은 위 문으로 포착돼요: OR 절의 각 항에서 후보 결과 행을 찾는 데 별도의 인덱스가 사용되고 최종 결과는 그 행들의 합집합이에요.

대부분의 경우 SQLite는 질의의 FROM 절에 있는 각 테이블에 대해 단일 인덱스만 사용한다는 점에 주목하세요. 여기 설명된 두 번째 OR-절 최적화가 그 규칙의 예외예요. OR 절에서는 OR 절의 각 하위항에 대해 다른 인덱스가 사용될 수 있어요.

주어진 질의에 대해 여기 설명된 OR-절 최적화를 사용할 수 있다는 사실이 반드시 사용되리라는 것을 보장하지는 않아요. SQLite는 경쟁하는 다양한 질의 계획의 CPU와 디스크 I/O 비용을 추정하고 가장 빠를 것이라고 생각하는 계획을 선택하는 비용 기반 질의 계획기를 사용해요. WHERE 절에 OR 항이 많거나 개별 OR-절 하위항의 인덱스 중 일부가 선택성이 높지 않으면 SQLite는 다른 질의 알고리즘이나 심지어 전체 테이블 스캔을 사용하는 것이 더 빠르다고 결정할 수 있어요. 애플리케이션 개발자는 문에 EXPLAIN QUERY PLAN 접두사를 사용해 선택된 질의 전략에 대한 높은 수준의 개요를 얻을 수 있어요.

5. LIKE 최적화

LIKE 또는 GLOB 연산자를 사용하는 WHERE-절 항은 때때로 인덱스와 함께 사용되어 범위 검색을 할 수 있어요. 마치 LIKE나 GLOB가 BETWEEN 연산자의 대안인 것처럼요. 이 최적화에는 많은 조건이 있어요.

LIKE 또는 GLOB의 오른쪽은 문자열 리터럴이거나 와일드카드 문자로 시작하지 않는 문자열 리터럴에 바인딩된 매개변수여야 해요.
왼쪽에 숫자 값(문자열이나 blob 대신)을 가짐으로써 LIKE나 GLOB 연산자를 참으로 만들 수 없어야 해요. 이는 다음 중 하나를 의미해요.
1. LIKE 또는 GLOB 연산자의 왼쪽이 TEXT 선호도(affinity)를 가진 인덱스된 열의 이름이거나,
2. 오른쪽 패턴 인수가 마이너스 부호("-")나 숫자로 시작하지 않아야 해요.
이 제약은 숫자가 사전순으로 정렬되지 않는다는 사실에서 발생해요. 예: 9<10이지만 '9'>'10'이에요.
LIKE와 GLOB을 구현하는 데 사용되는 내장 함수가 sqlite3_create_function() API로 오버로드되지 않았어야 해요.
GLOB 연산자의 경우 열이 내장 BINARY 정렬 순서로 인덱스되어야 해요.
LIKE 연산자의 경우 case_sensitive_like 모드가 활성화되면 열이 내장 BINARY 정렬 순서로 인덱스되어야 하고, case_sensitive_like 모드가 비활성화되면 열이 내장 NOCASE 정렬 순서로 인덱스되어야 해요.
ESCAPE 옵션을 사용하면 ESCAPE 문자는 ASCII 또는 UTF-8에서 단일 바이트 문자여야 해요.

LIKE 연산자는 pragma로 설정할 수 있는 두 가지 모드가 있어요. 기본 모드는 LIKE 비교가 latin1 문자의 대소문자 차이에 둔감하도록 하는 거예요. 따라서 기본적으로 다음 표현식은 참이에요.

'a' LIKE 'A'

case_sensitive_like pragma를 다음과 같이 활성화하면:

PRAGMA case_sensitive_like=ON;

LIKE 연산자는 대소문자를 신경 쓰고 위 예제는 false로 평가돼요. 대소문자 둔감성은 latin1 문자, 즉 기본적으로 ASCII의 낮은 127바이트 코드에 있는 영어의 대문자와 소문자에만 적용된다는 점에 주의하세요. 국제 문자 집합은 애플리케이션 정의 정렬 순서와 비-ASCII 문자를 고려하는 like() SQL 함수가 제공되지 않는 한 SQLite에서 대소문자를 구분해요. 애플리케이션 정의 정렬 순서와/또는 like() SQL 함수가 제공되면 여기 설명된 LIKE 최적화는 절대 수행되지 않아요.

LIKE 연산자는 SQL 표준이 요구하기 때문에 기본적으로 대소문자를 구분하지 않아요. 컴파일러에 SQLITE_CASE_SENSITIVE_LIKE 명령줄 옵션을 사용해 컴파일 시 기본 동작을 바꿀 수 있어요.

LIKE 최적화는 연산자 왼쪽에 이름이 지정된 열이 내장 BINARY 정렬 순서로 인덱스되고 case_sensitive_like가 켜져 있으면 발생할 수 있어요. 또는 열이 내장 NOCASE 정렬 순서로 인덱스되고 case_sensitive_like 모드가 꺼져 있으면 최적화가 발생할 수 있어요. 이 두 조합만이 LIKE 연산자가 최적화되는 유일한 조합이에요.

GLOB 연산자는 항상 대소문자를 구분해요. GLOB 연산자 왼쪽의 열은 항상 내장 BINARY 정렬 순서를 사용해야 하며, 그렇지 않으면 그 연산자를 인덱스로 최적화하려는 시도가 이루어지지 않아요.

LIKE 최적화는 GLOB 또는 LIKE 연산자의 오른쪽이 리터럴 문자열이거나 문자열 리터럴에 바인딩된 매개변수인 경우에만 시도돼요. 문자열 리터럴이 와일드카드로 시작해서는 안 돼요. 오른쪽이 와일드카드 문자로 시작하면 이 최적화는 시도되지 않아요. 오른쪽이 문자열에 바인딩된 매개변수이면, 이 최적화는 표현식을 포함하는 prepared statement가 sqlite3_prepare_v2() 또는 sqlite3_prepare16_v2()로 컴파일된 경우에만 시도돼요. 오른쪽이 매개변수이고 문이 sqlite3_prepare() 또는 sqlite3_prepare16()로 준비되면 LIKE 최적화는 시도되지 않아요.

LIKE 또는 GLOB 연산자 오른쪽의 초기 비-와일드카드 문자 시퀀스가 x라고 가정하자. 이 비-와일드카드 접두사를 나타내는 데 단일 문자를 사용하지만, 독자는 접두사가 1개보다 많은 문자로 구성될 수 있음을 이해해야 해요. y를 /x/와 같은 길이이지만 x보다 크게 비교되는 가장 작은 문자열이라고 하자. 예를 들어 x가 'hello'이면 y는 'hellp'가 돼요. LIKE 및 GLOB 최적화는 다음과 같이 두 개의 가상 항을 추가하는 것으로 구성돼요.

  column >= x AND column < y

대부분의 상황에서 원래 LIKE 또는 GLOB 연산자는 가상 항이 인덱스를 제약하는 데 사용되더라도 각 입력 행에 대해 여전히 테스트돼요. 그 이유는 x 접두사 오른쪽의 문자들이 부과할 수 있는 추가 제약이 무엇인지 우리가 모르기 때문이에요. 하지만 x 오른쪽에 단일 전역 와일드카드만 있으면 원래 LIKE 또는 GLOB 테스트는 비활성화돼요. 즉 패턴이 다음과 같으면:

  column LIKE x%
  column GLOB x*

가상 항이 인덱스를 제약할 때 원래 LIKE 또는 GLOB 테스트는 비활성화돼요. 그 경우 인덱스가 선택한 모든 행이 LIKE 또는 GLOB 테스트를 통과할 것임을 알기 때문이에요.

LIKE 또는 GLOB 연산자의 오른쪽이 매개변수이고 문이 sqlite3_prepare_v2() 또는 sqlite3_prepare16_v2()로 준비되면, 오른쪽 매개변수에 대한 바인딩이 이전 실행 이후 바뀌었다면 문은 각 실행의 첫 번째 sqlite3_step() 호출에서 자동으로 다시 파싱되고 다시 컴파일된다는 점에 주목하세요. 이 재파싱과 재컴파일은 기본적으로 스키마 변경 다음에 발생하는 것과 같은 동작이에요. 재컴파일은 질의 계획기가 LIKE 또는 GLOB 연산자 오른쪽에 새로 바인딩된 값을 검사하고 위에서 설명한 최적화를 적용할지 여부를 결정할 수 있도록 필요해요.

6. Skip-Scan 최적화

일반 규칙은 인덱스의 가장 왼쪽 열이 WHERE 절에 나타나야 SQLite가 그 인덱스를 사용한다는 것이에요. 하지만 SQLite 버전 3.8.0(2013-08-26)부터 인덱스의 가장 왼쪽 열이 제약되지 않고 WHERE 절이 인덱스의 두 번째 열이나 이후 열에 제약이 있는 경우에도 SQLite가 인덱스를 사용할 수 있어요. skip-scan은 SQLite가 WHERE 절 조건을 충족하지 않는 인덱스의 첫 번째 열 값들을 "건너뛰는(skip)" 것을 의미해요.

skip-scan은 특히 보조 인덱스(secondary indexes)에서 유용하며, 인덱스의 첫 번째 열의 서로 다른 값이 거의 없고 두 번째 열이나 이후 열이 매우 다양한 값을 가질 때 효과적이에요. 이 경우 skip-scan이 전체 인덱스 스캔보다 훨씬 효율적일 수 있어요.

SQLite는 skip-scan에서 인덱스의 첫 번째 열 값의 개수를 추정하고, 그 개수가 작으면 skip-scan을 선택할 가능성이 높아요. 예를 들어 인덱스가 (a,b)이고 WHERE 절이 b=5를 제약하면, 열 a의 고유 값이 많으면 SQLite는 전체 인덱스 스캔을 선택하고, 열 a의 고유 값이 적으면 skip-scan이 더 효율적일 수 있어요.

skip-scan은 전체 테이블 스캔보다 느릴 수 있지만, 질의에서 여러 테이블을 조인하는 경우 종종 여전히 유용해요.

7. 조인 (Joins)

SQLite는 FROM 절의 테이블을 조인하기 위해 왼쪽 깊이 우선(left-deep) 조인 순서를 사용해요. 두 개 이상의 테이블을 조인할 때 질의 계획기는 조인 순서를 신중하게 선택해요. 최적의 조인 순서는 데이터의 특성과 WHERE 절 제약에 따라 달라져요.

기본적으로 SQLite는 비용 기반 추정을 사용해 조인 순서를 선택해요. 개발자는 조인 순서를 수동으로 제어할 수도 있어요.

7.1. 조인 순서의 수동 제어

SQLite는 조인 순서를 수동으로 제어하는 두 가지 방법을 제공해요.

7.1.1. SQLITE_STAT 테이블을 사용한 질의 계획의 수동 제어

sqlite_stat1 테이블에 가짜 통계를 수동으로 삽입하면 질의 계획기가 조인 순서를 다르게 선택하도록 만들 수 있어요. 예를 들어 SQLite에게 테이블 X가 테이블 Y보다 2배 더 큰 것처럼 보이게 하려면 sqlite_stat1 테이블에 해당하는 항목을 수정할 수 있어요. 이는 애플리케이션 개발자가 인덱스 선택에 큰 영향을 줄 수 있는 강력하지만 위험한 기법이에요.

7.1.2. CROSS JOIN을 사용한 질의 계획의 수동 제어

SQLite는 FROM 절에서 CROSS JOIN으로 연결된 테이블들이 다른 명시적 조인 순서 힌트가 없으면 지정된 순서대로 조인되도록 해요. 즉 SQL에서 FROM a CROSS JOIN b라고 하면 SQLite는 a 다음 b 순서로 조인을 시도해 조인 순서를 효과적으로 고정시켜요. 이것은 조인 순서를 제어하는 깨끗하고 명시적인 방법이에요.

8. 여러 인덱스 사이에서 선택하기

질의의 FROM 절에 있는 각 테이블에 대해 SQLite는 WHERE 절 제약과 일치하는 여러 인덱스가 있을 수 있어요. SQLite는 비용 기반 추정을 사용해 어느 인덱스가 가장 효율적인지 결정해요. 각 후보 인덱스의 선택성(selectivity)과 관련 열 통계를 고려해 가장 적은 디스크 I/O와 CPU 비용을 예상하는 인덱스를 선택해요.

8.1. 단항 "+"로 WHERE 절 항 제외시키기

개발자는 단항 "+" 연산자를 사용해 특정 WHERE 절 항이 인덱스 최적화에 사용되지 못하게 할 수 있어요. 예를 들어 a+1=5는 인덱스를 사용하지 못하게 하고, a=4는 인덱스를 사용할 수 있어요. 단항 "+"는 항을 불리언 참으로 강제해 옵티마이저가 그 항을 인덱스 제약으로 사용하는 것을 막아요.

이 기법은 특히 의미론적으로는 동일하지만 계획기가 다르게 해석하는 표현을 만들기 위해 사용돼요. 예를 들어 a=5가 인덱스를 사용하게 하지만 a||''=5는 인덱스를 사용하지 못하게 해요. 개발자는 이런 방식으로 특정 인덱스를 강제하거나 피할 수 있어요.

8.2. 범위 질의

a>45 AND a<55 같은 범위 질의의 경우 SQLite는 인덱스의 시작점과 끝점을 결정하기 위해 두 부등식을 모두 사용해요. 인덱스가 단일 열보다 많은 열을 가질 때, 범위의 한쪽 끝에만 부등식이 적용되는 경우 SQLite는 어느 열이 인덱스에서 다음 열인지에 따라 범위 검색을 더 크게 만들 수 있어요.

9. 커버링 인덱스 (Covering Indexes)

쿼리의 모든 필요한 데이터가 인덱스 자체에 포함되어 있고 각 테이블 행을 조회할 필요가 없을 때 SQLite는 "커버링 인덱스"를 사용해요. 이 경우 SQLite는 인덱스만 스캔하고 테이블 행에 접근하지 않아 매우 효율적이에요. 인덱스의 모든 열과 인덱스에 포함된 표현식이 질의에 필요한 데이터를 제공하는 한, 커버링 인덱스 스캔은 전체 테이블 스캔보다 훨씬 빠를 수 있어요.

인덱스에 없는 열을 질의가 참조하면 SQLite는 테이블 행을 조회해야 하며 그러면 인덱스는 더 이상 커버링이 아니에요. 그런 경우 SQLite는 보통 전체 테이블 스캔이 인덱스 조회보다 효율적인지 판단하기 어려워질 수 있어요.

10. ORDER BY 최적화

SQLite는 ORDER BY 절을 처리할 때 인덱스를 사용해 정렬을 피할 수 있어요. 인덱스의 열 순서가 ORDER BY 절의 열 순서와 일치하고 필요한 정렬 방향과 맞으면, SQLite는 인덱스 스캔 결과가 이미 정렬되어 있으므로 별도의 정렬 단계를 생략해요. 이것은 성능에 큰 영향을 줄 수 있어요.

예를 들어 (a,b) 인덱스가 있고 ORDER BY a, b가 있으면 SQLite는 별도 정렬 없이 인덱스를 사용해 결과를 순서대로 반환할 수 있어요. 하지만 ORDER BY b, a처럼 열 순서가 뒤바뀌면 인덱스로는 충분하지 않아 별도 정렬이 필요해요.

10.1. 인덱스를 통한 부분 ORDER BY

ORDER BY 절이 인덱스로 완전히 충족되지 못하더라도, 인덱스가 ORDER BY의 접두사 부분을 정렬할 수 있으면 SQLite는 부분 정렬을 수행할 수 있어요. 이는 "부분 ORDER BY" 최적화로 알려져 있어요. 인덱스가 ORDER BY의 처음 몇 열을 충족하면 SQLite는 그 부분에 대해 인덱스 순서를 사용하고 나머지에 대해서만 정렬을 수행할 수 있어 정렬에 필요한 작업을 줄여요.

11. 서브쿼리 평탄화 (Subquery Flattening)

SQLite는 서브쿼리를 [평탄화(flatten)]하여 조인으로 변환할 수 있어요. 예를 들어:

SELECT * FROM (SELECT a, b FROM t1) WHERE b > 5;

SQLite는 이 내부 서브쿼리를 평탄화하여 상위 질의의 FROM 절에 t1을 직접 배치하고 제약을 적용해요. 이것은 별도의 서브쿼리 실행을 피하고 질의 계획기가 더 효율적인 실행 계획을 세울 수 있게 해요. 평탄화는 서브쿼리가 집계 함수, ORDER BY, LIMIT, 또는 상관(correlated) 참조를 포함하지 않는 경우와 같은 여러 조건이 충족될 때 발생해요.

12. 서브쿼리 코루틴 (Subquery Co-routines)

평탄화할 수 없는 서브쿼리는 "코루틴"으로 실행될 수 있어요. 코루틴은 서브쿼리 결과가 상위 질의에서 필요할 때마다 재개할 수 있는 특수 실행 방식이에요. 이는 특히 상위 질의과 서브쿼리 사이에 데이터 흐름이 있을 때 임시 테이블에 서브쿼리 전체를 넣어 실행한 다음 상위 질의에서 삭제하는 것보다 더 효율적일 수 있어요.

12.1. 정렬 후 작업을 지연하기 위해 코루틴 사용하기

어떤 경우 SQLite는 서브쿼리가 상위 질의에서 사용되기 전에 전체 결과를 먼저 정렬해야 할 수도 있어요. 코루틴을 사용하면 SQLite는 상위 질의의 정렬이 완료된 후에 서브쿼리 작업을 지연시켜 메모리 사용을 줄일 수 있어요.

13. MIN/MAX 최적화

SQLite는 MIN() 또는 MAX() 집계 함수의 인수가 인덱스의 왼쪽에 있는 열이나 인덱스된 표현식일 때 특수 최적화를 적용해요. 이 경우 SQLite는 전체 테이블을 스캔할 필요 없이 인덱스의 첫 번째 또는 마지막 항목을 조회해 최소값이나 최대값을 구할 수 있어요. 예를 들어 열 a에 인덱스가 있으면 SELECT MIN(a) FROM t1은 전체 스캔 없이 인덱스의 첫 번째 항목을 조회할 수 있어 매우 빠르답니다.

14. 자동 질의-시간 인덱스 (Automatic Query-Time Indexes)

SQLite는 질의에서 WHERE 절 제약에 사용된 임시 인덱스가 도움이 되면 자동으로 그런 인덱스를 만들어 실행합니다. 이 "자동 인덱스"는 질의 실행 중에만 존재하고 질의가 끝나면 폐기되며 스키마에 추가되지 않아요. 자동 인덱스는 주요 조인의 오른쪽 테이블에 특히 유용하며, 개발자가 명시적 인덱스를 만들 필요 없이 질의 계획을 개선할 수 있어요. 하지만 자동 인덱스 생성은 질의에 오버헤드를 추가하므로, 자주 실행되는 질의에는 명시적 인덱스를 만드는 것이 더 좋을 수 있어요.

14.1. 해시 조인 (Hash Joins)

SQLite 3.39.0부터는 특정 상황에서 해시 조인을 사용할 수 있어요. 해시 조인은 조인할 테이블 중 하나를 해시 테이블로 만들어 조인 키로 빠르게 찾는 방법이에요. 특히 조인의 WHERE 조건이 동등성(equality) 비교일 때 유용해요. 해시 조인은 큰 테이블을 조인할 때, 특히 기존 인덱스가 없거나 적합하지 않은 경우 성능을 크게 향상시킬 수 있어요. 조인할 두 테이블 중 작은 쪽을 해시 테이블로 만들고 큰 쪽을 스캔하면서 해시 테이블과 매칭시키는 방식이 일반적이에요.

15. 술어 푸시다운 최적화 (Predicate Push-Down Optimization)

SQLite는 [서브쿼리]나 [뷰]의 WHERE 절 제약을 상위 질의로 끌어올려(push up) 조기에 제약을 적용할 수 있어요. 이는 주로 [집계 함수]에서 특히 유용하며, 서브쿼리/뷰에서 행을 필터링하는 조건을 상위 질의에 추가해 서브쿼리/뷰가 처리해야 하는 행 수를 줄일 수 있어요. 이 최적화는 질의 계획기가 서브쿼리/뷰가 결과를 만들어내기 전에 필터링을 수행하도록 하여 I/O와 CPU 작업을 줄여요.

16. OUTER JOIN 강도 축소 최적화 (Outer Join Strength Reduction)

외부 조인(OUTER JOIN)은 조인의 WHERE 절에서 외부 조인의 널 확장 행(NULL-extended rows)에 대해 거짓이거나 NULL인 제약 조건이 있을 때 내부 조인으로 변환할 수 있어요. 예를 들어 LEFT JOIN에서 오른쪽 테이블의 열이 WHERE ... AND right_col IS NOT NULL로 제한되면, 이 제약 때문에 오른쪽 테이블의 널 확장 행은 걸러져서 결과에 포함될 수 없어요. 그러면 SQLite는 LEFT JOIN을 내부 조인으로 안전하게 축소할 수 있고, 이는 옵티마이저가 조인 순서를 더 자유롭게 선택할 수 있게 하며 성능을 개선해요.

17. OUTER JOIN 생략 최적화 (Omit OUTER JOIN Optimization)

어떤 경우 OUTER JOIN을 완전히 생략할 수 있어요. 조인이 영향을 주지 않는 경우 예를 들어 LEFT JOIN의 오른쪽 테이블에서 어떤 열도 사용되지 않고, LEFT JOIN이 결과 행 수를 늘리지 않는 경우, SQLite는 조인 자체를 생략할 수 있어요. 이렇게 하면 조인 작업 자체가 사라져 실행이 더 빨라집니다. 예를 들어 SELECT t1.a FROM t1 LEFT JOIN t2 ON ...에서 t2의 어떤 열도 사용되지 않으면 조인을 생략할 수 있어요.

18. 상수 전파 최적화 (Constant Propagation Optimization)

SQLite는 질의 계획 중에 상수 값을 WHERE 절의 다른 제약으로 전파할 수 있어요. 예를 들어 WHERE 절에 a=5가 있으면, 질의 계획기는 a가 5라는 사실을 사용해 a+1=6 같은 표현식도 평가할 수 있고, 추가적인 제약을 유도해 선택성을 높일 수 있어요. 이는 질의에서 더 나은 인덱스 선택과 필터링으로 이어질 수 있어요.

SQLite 질의 최적화 프로그램 개요