표현식 인덱스

표현식 인덱스 (Indexes On Expressions)

표현식 인덱스는 컬럼 이름 대신 표현식을 인덱싱하는 기능이에요. 쿼리 플래너가 표현식을 정확히 매칭할 때만 인덱스를 사용한다는 점을 이해하는 게 핵심이에요.

출처: Indexes On Expressions

본문

1. 표현식에 인덱스 사용하는 방법

컬럼에 인덱스를 만들 때처럼 CREATE INDEX 문을 사용해 하나 이상의 표현식에 새로운 인덱스를 만들 수 있어요. 유일한 차이는 컬럼 이름 대신 인덱싱할 요소로 표현식을 나열한다는 점이에요.

SQLite 쿼리 플래너는, 인덱싱된 표현식이 CREATE INDEX 문에 적힌 정확히 그대로 쿼리의 WHERE 절이나 ORDER BY 절에 나타날 때 그 인덱스를 사용하는 것을 고려해요. 쿼리 플래너는 대수(algebra) 계산을 하지 않아요. WHERE 절 제약 조건과 ORDER BY 항목을 인덱스에 매칭하기 위해 SQLite 는 표현식이 (공백 같은 사소한 구문 차이를 제외하고) 같아야 한다고 요구해요. 다음과 같은 경우를 볼게요:

CREATE TABLE t2(x,y,z);
CREATE INDEX t2xy ON t2(x+y);

그런 다음 이 쿼리를 실행하면:

SELECT * FROM t2 WHERE y+x=22;

인덱스는 사용되지 않아요. CREATE INDEX 문의 표현식(x+y)이 쿼리에 나타난 표현식(y+x)과 같지 않기 때문이에요. 두 표현식은 수학적으로 동치일 수 있지만, SQLite 쿼리 플래너는 단지 동치가 아니라 같아야 한다고 요구해요. 쿼리를 이렇게 다시 쓰는 것을 고려해보세요:

SELECT * FROM t2 WHERE x+y=22;

이 두 번째 쿼리는 WHERE 절의 표현식(x+y)이 인덱스의 표현식과 정확히 일치하므로 인덱스를 사용할 가능성이 높아요.

2. 제한 사항 (Restrictions)

CREATE INDEX 문에 나타나는 표현식에는 합리적인 제한 사항이 몇 가지 있어요:

  • CREATE INDEX 문의 표현식은 인덱싱되는 테이블의 컬럼만 참조할 수 있어요. 다른 테이블의 컬럼은 참조할 수 없어요.

  • CREATE INDEX 문의 표현식에는 함수 호출을 포함할 수 있지만, 그 출력이 항상 입력 매개변수에 의해 완전히 결정되는 함수(즉, 결정적 함수)만 사용할 수 있어요. 당연히 random() 같은 함수는 인덱스에서 잘 동작하지 않아요. sqlite_version() 같은 함수도, 어떤 데이터베이스 연결에서도 상수이지만, 기본 데이터베이스 파일의 수명 전반에 걸쳐 상수는 아니므로 CREATE INDEX 문에서 사용할 수 없어요. 애플리케이션 정의 SQL 함수는 기본적으로 비결정적 함수로 간주되며, 함수를 등록할 때 SQLITE_DETERMINISTIC 플래그를 사용하지 않는 한 CREATE INDEX 문에서 사용할 수 없어요.

    참고: 오래된 표현식 인덱스

  • CREATE INDEX 문의 표현식은 서브쿼리를 사용할 수 없어요.

  • 표현식은 CREATE INDEX 문에만 사용할 수 있어요. CREATE TABLE 문 안의 UNIQUE 나 PRIMARY KEY 제약 조건 안에서는 사용할 수 없어요.

3. 호환성 (Compatibility)

표현식 인덱싱 기능은 SQLite 버전 3.9.0 (2015-10-14) 에 추가됐어요. 표현식 인덱스를 사용하는 데이터베이스는 더 이른 버전의 SQLite 에서는 사용할 수 없어요.

더 알아보기 (Learn more)