부분 인덱스

부분 인덱스 (Partial Indexes)

부분 인덱스는 테이블 행의 부분집합에 대한 인덱스예요. 적절히 사용하면 데이터베이스 파일을 더 작게 만들고 질의와 쓰기 성능을 모두 개선할 수 있어요.

출처: Partial Indexes 문서

본문

1. 소개

부분 인덱스는 테이블 행의 부분집합에 대한 인덱스예요.

일반 인덱스에서는 테이블의 모든 행에 대해 정확히 하나의 인덱스 항목이 있어요. 부분 인덱스에서는 테이블 행 중 일부만 대응하는 인덱스 항목을 가져요. 예를 들어 부분 인덱스는 인덱싱되는 열이 NULL인 항목을 생략할 수 있어요. 부분 인덱스를 현명하게 사용하면 데이터베이스 파일을 더 작게 만들고 질의와 쓰기 성능을 모두 개선할 수 있어요.

2. 부분 인덱스 만들기

일반 CREATE INDEX 문 끝에 WHERE 절을 추가하면 부분 인덱스를 만들 수 있어요.

문법: create-index-stmt · expr · filter-clause · indexed-column

끝에 WHERE 절을 포함하는 모든 인덱스는 부분 인덱스로 간주돼요. WHERE 절을 생략한 인덱스(또는 CREATE TABLE 문 안의 UNIQUE나 PRIMARY KEY 제약으로 생성된 인덱스)는 일반적인 전체 인덱스예요.

WHERE 절 뒤에 오는 표현식은 연산자, 리터럴 값, 그리고 인덱싱되는 테이블의 열 이름을 포함할 수 있어요. WHERE 절은 서브쿼리, 다른 테이블에 대한 참조, 비결정적 함수(non-deterministic functions), 또는 바운드 매개변수를 포함할 수 없어요.

WHERE 절이 true로 평가되는 테이블 행만 인덱스에 포함돼요. WHERE 절 표현식이 테이블의 일부 행에 대해 NULL이나 false로 평가되면 그 행들은 인덱스에서 생략돼요.

부분 인덱스의 WHERE 절에서 참조되는 열은 테이블의 어떤 열이든 될 수 있으며, 우연히 인덱싱되는 열일 필요는 없어요. 하지만 부분 인덱스의 WHERE 절 표현식이 인덱싱되는 열에 대한 단순한 표현식인 경우가 매우 흔해요. 다음은 대표적인 예시예요.

CREATE INDEX po_parent ON purchaseorder(parent_po) WHERE parent_po IS NOT NULL;

위 예시에서 대부분의 구매 주문(purchase order)에 "부모" 구매 주문이 없다면 대부분의 parent_po 값은 NULL이 돼요. 즉 purchaseorder 테이블의 아주 일부 행만 인덱싱된다는 뜻이에요. 따라서 인덱스는 훨씬 적은 공간을 차지해요. 그리고 po_parent 인덱스는 parent_po가 NULL이 아닌 예외적인 행에 대해서만 갱신하면 되므로 원래 purchaseorder 테이블에 대한 변경도 더 빨라져요. 하지만 인덱스는 여전히 질의에 유용해요. 특히 특정 구매 주문 "?1"의 모든 "자식"을 알고 싶다면 질의는 다음과 같아요.

SELECT po_num FROM purchaseorder WHERE parent_po=?1;

위 질의는 po_parent 인덱스가 관심 있는 모든 행에 대한 항목을 포함하고 있으므로 그 인덱스를 사용해 답을 찾아요. po_parent가 전체 인덱스보다 작기 때문에 질의도 더 빨라질 가능성이 높아요.

2.1. 고유 부분 인덱스 (Unique Partial Indexes)

부분 인덱스 정의는 UNIQUE 키워드를 포함할 수 있어요. 그렇게 하면 SQLite는 인덱스 안의 모든 항목이 고유해야 한다고 요구해요. 이는 테이블 행의 특정 부분집합에 걸쳐 고유성을 강제하는 메커니즘을 제공해요.

예를 들어, 각 사람이 특정 "팀"에 배정된 대규모 조직 구성원 데이터베이스가 있다고 가정해 봐요. 각 팀에는 그 팀의 구성원이기도 한 "리더"가 있어요. 테이블은 대략 다음과 같을 수 있어요.

CREATE TABLE person(
  person_id       INTEGER PRIMARY KEY,
  team_id         INTEGER REFERENCES team,
  is_team_leader  BOOLEAN,
  -- other fields elided
);

보통 같은 팀에 여러 사람이 있으므로 team_id 필드는 고유할 수 없어요. 각 팀에 보통 리더가 아닌 사람이 여러 명 있으므로 team_id와 is_team_leader의 조합도 고유하게 만들 수 없어요. 팀당 한 명의 리더를 강제하는 해결책은 is_team_leader가 true인 항목으로 제한된 team_id에 대한 고유 인덱스를 만드는 거예요.

CREATE UNIQUE INDEX team_leader ON person(team_id) WHERE is_team_leader;

우연히도 그 같은 인덱스는 특정 팀의 리더를 찾는 데도 유용해요.

SELECT person_id FROM person WHERE is_team_leader AND team_id=?1;

3. 부분 인덱스를 사용하는 질의

부분 인덱스 WHERE 절의 표현식을 X, 인덱싱되는 테이블을 사용하는 질의의 WHERE 절을 W라고 하자. 그러면 W⇒X일 때(W⇒는 "implies"로 읽는 논리 연산자로, "X or not W"와 동등) 질의는 부분 인덱스를 사용하는 것이 허용돼요. 따라서 주어진 질의에서 부분 인덱스를 사용할 수 있는지 여부를 결정하는 것은 일차 술어 논리에서 정리를 증명하는 문제로 귀결돼요.

SQLite에는 W⇒X를 결정할 정교한 정리 증명기가 없어요. 대신 SQLite는 W⇒X가 참인 흔한 경우를 찾기 위해 두 가지 간단한 규칙을 사용하고, 다른 모든 경우는 거짓이라고 가정해요. SQLite가 사용하는 규칙은 다음과 같아요.

  1. W가 AND로 연결된 항들이고 X가 OR로 연결된 항들이며, W의 어떤 항이 X의 항으로 나타난다면 부분 인덱스를 사용할 수 있어요.

    예를 들어 인덱스가 다음과 같다고 하자.

    CREATE INDEX ex1 ON tab1(a,b) WHERE a=5 OR b=6;

    그리고 질의가 다음과 같다고 하자.

    SELECT * FROM tab1 WHERE b=6 AND a=7; -- uses partial index

    "b=6" 항이 인덱스 정의와 질의 양쪽에 나타나므로 인덱스는 질의에 사용될 수 있어요. 기억해야 할 점: 인덱스의 항은 OR로 연결되고 질의의 항은 AND로 연결되어야 해요.

    W와 X의 항은 정확히 일치해야 해요. SQLite는 그것들이 같아 보이게 하려고 대수 연산을 하지 않아요. "b=6" 항은 "b=3+3"이나 "b-6=0"이나 "b BETWEEN 6 AND 6"과 일치하지 않아요. "b=6"이 인덱스에 있고 "6=b"가 질의에 있으면, "b=6"은 "6=b"와 일치해요. "6=b" 형태의 항이 인덱스에 나타나면 아무것도 일치하지 않아요.

  2. X의 항이 "z IS NOT NULL" 형태이고 W의 항이 "IS"가 아닌 "z"에 대한 비교 연산자이면 그 항들은 일치해요.

    예시: 인덱스가 다음과 같다고 하자.

    CREATE INDEX ex2 ON tab2(b,c) WHERE c IS NOT NULL;

    그러면 열 "c"에 대해 =, <, >, <=, >=, <>, IN, LIKE, GLOB 연산자를 사용하는 모든 질의는 부분 인덱스와 함께 사용할 수 있어요. 왜냐하면 그 비교 연산자들은 "c"가 NULL이 아닐 때만 참이기 때문이에요. 그래서 다음 질의는 부분 인덱스를 사용할 수 있어요.

    SELECT * FROM tab2 WHERE b=456 AND c<>0;  -- uses partial index

    하지만 다음 질의는 부분 인덱스를 사용할 수 없어요.

    SELECT * FROM tab2 WHERE b=456;  -- cannot use partial index

    후자의 질의는 b=456이고 c가 NULL인 행이 테이블에 있을 수 있기 때문에 부분 인덱스를 사용할 수 없어요. 하지만 그런 행은 부분 인덱스에 없을 거예요.

이 두 규칙은 이 글을 쓰는 시점(2013-08-01)의 SQLite 질의 플래너가 동작하는 방식을 설명해요. 그리고 위 규칙은 항상 존중될 거예요. 하지만 미래의 SQLite 버전은 W⇒X가 참인 다른 경우를 찾을 수 있는 더 나은 정리 증명기를 포함해 부분 인덱스가 유용한 경우를 더 많이 찾아낼 수도 있어요.

4. 지원 버전

부분 인덱스는 버전 3.8.0(2013-08-26)부터 SQLite에서 지원돼요.

부분 인덱스를 포함하는 데이터베이스 파일은 3.8.0 이전 SQLite 버전으로는 읽거나 쓸 수 없어요. 하지만 SQLite 3.8.0이 만든 데이터베이스 파일은 스키마에 부분 인덱스가 없는 한 이전 버전에서도 여전히 읽고 쓸 수 있어요. 구형 SQLite 버전이 읽을 수 없는 데이터베이스는 부분 인덱스에 대해 DROP INDEX를 실행하기만 하면 읽을 수 있게 만들 수 있어요.

더 알아보기 (Learn more)