[SQLite3] 06. 임베디드 SQLite3 최적화 #2

이 글은 "빠르게 활용하는 모바일 데이터베이스 SQLite3” 이란 글을 보며 필요한 내용만 정리한 글입니다.

자세한 내용은 책을 구매해서 보세요

5. 엄격한 자료형 검사

SQLite3 자체는 동적 자료형 형태지만 typeof() 함수와 CHECK 제약조건을 이용해서 정적 자료형처럼 사용할 수 있다.

ex)

sqlite> CREATE TABLE testTable(

name text CHECK(typeof(name)=‘text’),

age integer CHECK(typeof(age)=‘integer’));

CHECK 규칙을 위반할 경우 constraint failed error 가 발생한다.

6. DB 갱신과 트랜잭션

INSERT/UPDATE 와 같이 테이블을 수정하는 구문을 하나 이상 사용하는 경우 이를 묶어서 명시적으로 트랜잭션을 사용하는 것이 성능 향상의 지름길이다.

명시적으로 트랜잭션을 이용하지 않더라도 SQLite3 내부에서는 DB 를 변경하는 모든 SQL 문이 하나의 트랜잭션 내에서 수행된다.

이 때 트랜잭션이 시작되고 종료될 때마다 해당 DB 파일을 열고 변경사항을 갱신한 후 파일을 닫는 동작을 수행한다.

7. 저널 모드와 트랜잭션 성능 향상

SQLite3 은 journal 파일을 사용한다. 이 파일의 용도는 DB 파일을 변경하는 도중에 어떠한 이유에서든 다시 최초 상태로 복원해야 하는 경우에 사용되는 파일이다.

그런 이유로 종종 롤백 저널(Rollback journal) 또는 핫 저널(Hot journal) 이라고 부른다

내부적으로 SQLite3 에서는 DB 를 변경하는 SQL 문을 수행하기 이전에 변경될 원본 데이터를 저널 파일에 저장하고, 파일 이름은 ‘DB이름-journal’ 형태로 자동 지정한다.

트랜잭션이 정상적으로 종료(COMMIT/ROLLBACK) 된다면 저널 파일은 삭제된다.

저널 모드 변경

파일 I/O 연산이 잦을수록 DB 질의 성능은 저하된다.

SQLite3 에서는 기본적으로 하나의 트랜잭션이 시작되고 종료될 때마다 저널 파일이 생성되고 삭제되기 때문에 트랜잭션은 매우 무거운 연산에 해당한다.

이런 경우 저널 파일을 처리하는 방식을 변경해서 질의 성능을 향상시킬 수 있다.

SQLite3 에서 저널 파일을 처리하는 방식은 다음 6가지가 있다.

DELETE : 매 트랜잭션이 종료되면 저널 파일을 삭제한다. (기본 설정)

TRUNCATE : 트랜잭션이 종료되더라도 저널 파일을 삭제하는 대신 파일의 크기를 ‘0’ 으로 만든다.

PERSIST : 트랜잭션이 종료되더라도 저널 파일을 삭제하거나 파일 크기를 0으로 만드는 대신 저널 파일의 헤더 부분을 ‘0’ 으로 초기화한다.

MEMORY : 저널 파일을 메모리상에 기록한다.

OFF : 저널 파일을 사용하지 않는다.

WAL : 롤백 저널 방식 대신 Write-ahead log 방식으로 트랜잭션을 처리한다. (3.7.0 버전 이상)

Write-ahead log 방식은 트랜잭션이 종료되더라도 모든 변경사항을 DB 에 기록하는 대신 최소한의 로그(log)만 기록한다.

즉 DB 일관성을 유지하기 위한(손상되지 않게 하기 위한) 최소한의 로그만 저장하는 방식이다.

PRAGMA journal_mode 구문을 이용해 값을 확인할 수도, 값을 설정할 수도 있다.

8. 메모리 DB

영속성(persistence)란 데이터를 생성한 프로그램이 종료되더라도 데이터가 사라지지 않는 특성을 의미한다.

SQLite3 에서는 DB 파일을 물리적인 파일 시스템에 저장해서 영속성을 갖게 한다.

하지만 SQLite3에 저장되는 데이터가 굳이 영속성을 갖지 않아도 되는 경우에는 메모리상에 DB 를 생성해서 재부팅되면 해당 DB 가 삭제되게 할 수 있다.

또한 파일 I/O 연산 자체가 없으므로 파일에 저장할 때보다 상대적으로 매우 빠른 성능을 발휘한다.

메모리 DB 를 생성하려면 sqlite3_open() 함수를 실행할 때 파일명 대신 “:memory:” 를 사용하면 된다.

sqlite3_open(“:memory:”, &db);

ATTACH DATABASE 구문을 사용해서 새롭게 ATTACH 로 연결하는 DB 를 메모리상에 위치시킬 수도 있다.

ATTACH DATABASE “:memory:” AS memdb;

SQLite3 의 성능 극대화를 목표로 앱에서 사용하는 데이터 중 일부는 영속성을 갖도록 파일 기반의 DB 에 저장하고, 다른 데이터는 성능 향상을 위해 메모리 기반의 DB 에 저장하기도 한다.

이러한 기법은 임베디드 시스템에서는 자주 이용되는 것으로 안드로이드 플랫폼 내부에서도 이와 같은 기법을 사용한다.

예를 들어 안드로이드의 Contact provider 내부 코드에서도 이와 유사한 기법이 활용된다.

9. 테이블 제약조건의 충돌 처리

INSERT/UPDATE 구문을 수행할 때 UNIQUE 인덱스와 같이 테이블에 설정된 제약조건을 위반하는 경우 다른 DB 와 동일하게 SQLite3에서도 수행 중인 연산이 취소(ABORT)되면서 SQLITE_CONSTRAINT 에러 코드를 반환한다.

SQLite3 에서는 SQL 표준에 포함되지 않은 SQL 확장 형태의 충돌(Conflict) 처리 구문을 지원하므로 이와 같이 제약조건이 위반됐을 때의 처리 방식을 설정할 수 있다.

ABORT : 해당 구문이 제약조건을 위반하는 경우 해당 연산에 대해서만 취소되고 SQLITE_CONSTRAINT 에러 코드를 반환하지만 진행 중인 트랜잭션은 유효하다. (기본값)

ROLLBACK : 해당 구문이 제약조건을 위반하는 경우 진행 중인 전체 트랜잭션이 취소되고 SQLITE_CONSTRAINT 에러 코드를 반환한다.

FAIL : 해당 구문이 제약조건을 위반하는 경우 SQLITE_CONSTRAINT 에러 코드를 반환하고 해당 연산은 종료되나, 제약조건을 위반하기 전까지 수행됐던 정상적인 연산은 유효하다.

IGNORE : 해당 구문이 제약조건을 위반하는 경우 개별 레코드 가운데 제약조건을 위반하지 않는 레코드에 대해서는 정상적으로 변경된다.

REPLACE : 해당 구문이 제약조건을 위반하는 경우 제약조건을 위반한 기존 레코드는 삭제되고 신규 레코드는 추가되는 연산을 수행한다.

충돌조건은 "INSERT [충돌조건]" 또는 "UPDATE [충돌조건]" 의 syntax 로 사용한다.

ABORT

해당 구문이 제약조건을 위반하는 경우 해당 연산에 대해서만 취소되고 SQLITE_CONSTRAINT 에러 코드를 반환하지만 진행 중인 트랜잭션은 유효하다. 기본값이라 ABORT 는 생략해도 된다.

해당 연산에 대해서만 취소된다는 것은 트랜잭션에서 미리 성공적으로 수행된 연산은 취소되지 않는다는 것.

ROLLBACK

해당 구문이 제약조건을 위반하는 경우 진행 중인 전체 트랜잭션이 취소되고 SQLITE_CONSTRAINT 에러 코드를 반환한다.

ABORT 와 ROLLBACK 은 제약조건 충돌이 발생하지 않더라도 SQLite3 내부적으로 복원을 위한 이전 데이터를 유지해야 하기 때문에 가장 비용이 드는 충돌 해결 방법이다.

FAIL

해당 구문이 제약조건을 위반하는 경우 SQLITE_CONSTRAINT 에러 코드를 반환하고 해당 연산은 종료되나, 제약조건을 위반하기 전까지 수행됐던 "정상적인 레코드 연산은 유효"하다.

ABORT 와의 차이점은 ABORT 는 한 연산에 대해 제약조건을 위반하는 경우 바로 취소가 되는데, FAIL 은 한 연산 내에서도 유효하게 변경된 내용이 반영된다.

예를 들어 id 는 unique constraint 가 있고, 값으로 1, 5, 6 을 가진 rows 가 있다.

모든 id 를 1 increase 하는 연산을 수행하면. 1 -> 2 는 성공이고 5 -> 6 는 실패한다.

이 경우 1 -> 2 는 변경된 상태로 유지된다. ABORT 는 1 -> 2 가 반영되지 않는다.

IGNORE

해당 구문이 제약조건을 위반하는 경우 "개별 레코드 가운데" 제약조건을 위반하지 않는 레코드에 대해서는 정상적으로 변경된다. 어떤 에러 코드도 반환하지 않고 해당 연산은 종료된다. 트랜잭션은 종료되지 않는다.

REPLACE

해당 구문이 제약조건을 위반하는 경우 제약조건을 위반한 기존 레코드는 삭제되고 신규 레코드는 추가되는 연산을 수행한다.

만약 UNIQUE 가 아닌 NOT NULL 제약조건인 경우라면 제약조건을 위반한 레코드에는 설정된 기본값이 입력되며, 기본값이 설정되지 않은 경우에는 ABORT 옵션과 동일하게 작동한다. 또한 CHECK 제약조건에 대해서는 IGNORE 옵션과 동일한 방식으로 동작한다.

테이블을 생성할 때도 제약조건 위반에 대한 충돌 처리 방식을 지정할 수 있다.

CREATE TABLE 구문에서 제약조건을 설정하는 CONSTRAINT 절의 마지막에 "ON CONFLICT [옵션]" 형태로 설정하면 된다.

ex)

sqlite> CREATE TABLE testTable(

id INTEGER,

name TEXT,

CONSTRAINT ctIdUk UNIQUE(id) ON CONFLICT REPLACE

);

테이블 생성할 때 충돌 처리 방식을 지정한 경우에도 INSERT/UPDATE 구문에서 명시적으로 충돌 처리 방식을 설정해면 해당 방식으로 처리된다.

10. 외래키 제약조건의 확장

SQLite3 에서는 참조 무결성을 유지하기 위해 상위 테이블의 레코드가 삭제되거나 해당 기본키가 수정됐을 경우 종속 테이블에 해당 키를 참조하는 레코드가 존재하면 에러가 발생한다.

하지만 제약조건 충돌 시 처리 방식을 지정하는 것과 동일하게 외래키 생성 시 특정 옵션을 지정해서 해당 동작을 수행할 수 있다.

에를 들어 상위 테이블의 레코드가 삭제될 때 단순히 에러를 반환하는 대신 이를 참조하는 종속 테이블의 모든 레코드를 삭제하거나, 혹은 참조하는 외래키 값을 NULL 로 설정하는 등의 동작 방식을 지정할 수 있다.

해당 옵션을 지정하는 방법은 CREATE TABLE 구문의 외래키를 생성하는 부분에서 레코드가 삭제되는 경우는 "ON DELETE [옵션]" 으로, 레코드가 수정되는 경우는 "ON UPDATE [옵션]" 같은 형식으로 지정할 수 있다.

옵션 값은 아래와 같다

NO ACTION : 상위 테이블의 기본키가 삭제되거나 변경돼도 종속 테이블에는 변경을 하지 않는다.

RESTRICT : 종속 테이블에 상위 테이블의 기본키를 참조하는 레코드가 하나 이상 존재하는 경우 상위 테이블의 해당 레코드를 삭제할 때 에러가 발생한다. 이러한 동작 방식은 기본 외래키 제약조건과 비슷하지만 트랜잭션이 Deferred 모드로 동작 중일 때도 COMMIT 시점이 아닌 해당 레코드가 INSERT/UPDATE 될 때 즉시 에러가 발생한다.

SET NULL : 상위 테이블의 기본키가 삭제되거나 변경됐을 때 이를 참조하는 종속 테이블의 외래키를 NULL 로 설정한다.

SET DEFAULT : 상위 테이블의 기본키가 삭제되거나 변경됐을 때 이를 참조하는 종속 테이블의 외래키를 테이블 스키마에 저장된 기본값으로 설정한다.

CASCADE : 상위 테이블의 기본키가 삭제되거나 변경됐을 때 이를 참조하는 종속 테이블의 외래키에도 동일한 동작이 수행되게 한다.

CASCADE

상위 테이블의 기본키가 삭제되거나 변경됐을 때 이를 참조하는 종속 테이블의 외래키에도 동일한 동작이 수행되게 한다.

ex)

sqlite> CREATE TABLE bookInfo(

isbn INTEGER PRIMARY KEY,

title TEXT,

publisher_id INTEGER,

CONSTRAINT ctPublihserID_fk FOREIGN KEY(publisher_id)

REFERENCES publisherInfo(publisher_id)

ON DELETE CASCADE

ON UPDATE CASCADE

);

SET DEFAULT

상위 테이블의 기본키가 삭제되거나 변경됐을 때 이를 참조하는 종속 테이블의 외래키를 테이블 스키마에 저장된 기본값으로 설정한다. 이 경우 종속 테이블의 외래키에는 반드시 기본값이 설정되어 있어야 한다.

11. SQLite3 설정 변경

PRAGMA 구문은 SQLite3 lib 내부 설정이나 기본 동작 방식을 변경할 때 사용한다.

PRAGMA 는 표준 SQL 구문이 아니라서 SQLite3 에서만 사용할 수 있으며, 존재하지 않거나 인자가 잘못된 PRAGMA 구문에 대해서도 에러 메시지가 발생하지 않는다.

Syntax

PRAGMA [DBName].[optionName]; // 조회

PRAGMA [DBName].optionName] = Value; // 설정

현재 main 으로 연결된 DB 에 대한 것은 DBName 을 생략해도 된다.

그러나 ATTACH 된 DB 설정을 변경하려면 DBName 을 반드시 사용해야 한다.

PRAGMA 구문을 이용해 변경할 수 있는 SQLite3 옵션은 50여 가지 정도이며, 기능이 추가되고 버전이 올라갈수록 계속 추가될 예정이다.

최대 캐시 크기 수정

DB 에서 매번 디스크로부터 페이지를 읽거나 쓰면 디스크 I/O 연산이 자주 발생해서 성능 저하가 일어난다.

이러한 경우 일정 메모리 공간을 캐시(Cache)로 사용해서 디스크 입출력 연산을 최대한 줄여 성능 향상을 꾀한다.

또한 이러한 공간은 질의를 수행하는 도중에 필요에 따라 질의의 중간 결과를 저장할 공간으로 사용된다.

따라서 이 영역을 크게 잡아야 디스크 입출력 연산이 줄어들어 성능이 좋아진다.

SQLite3 에서는 이러한 공간을 페이지 캐시(page cache)라고 한다.

페이지 캐시는 개별 DB 연결마다 생성되며, 각 연결마다 크기를 다르게 설정할 수 있다.

현재 연결된 DB 에서 할당될 수 있는 최대 페이지 캐시 크기는 "PRAGMA cache_size" 구문으로 확인하고 설정한다.

기본값은 2000 이다.

만약 질의문의 결과집합이 매우 크거나 중간 연산 과정에서 페이지 캐시가 많이 사용되어 메모리 공간이 부족하다면 SQLite3 에서는 임시 파일을 만들어 관련 데이터를 저장한다.

하지만 디스크 I/O 가 발생하기 때문에 DB 연산속도가 느려지는 문제가 발생할 수 있다.

이 경우 캐시 공간의 크기를 늘려서 성능 향상을 꾀할 수 있다.

페이지 캐시로는 10 이상의 값을 설정해야 하며, 10보다 작은 값을 설정하면 10으로 설정된다.

변경된 페이지 캐시 값은 현재 연결에 대해서만 유효하고, 다음 연결 시에는 기본값으로 복원된다.

만약 영구적으로 페이지 캐시의 값을 설정하고 싶다면 "PRAGMA default_cache_size" 구문을 사용해야 한다.

임시 파일 디렉토리 설정

임시 테이블, 인덱스, 뷰를 생성하는 경우나 질의문이 생성하는 결과집합이 페이지 캐시에서 할당할 수 있는 최대 메모리 공간보다 큰 경우에는 임시 공간에 파일을 만들어 임시 데이터를 저장한다.

이 임시 파일은 DB 연결이 종료되거나 내부적으로 더는 사용되지 않을 경우 삭제된다.

하지만 이렇게 파일을 생성하고 삭제하는 과정에서 디스크 I/O 가 발생하고, 이것이 페이지 캐시 내의 메모리 연산보다 상대적으로 느려서 성능 저하가 일어난다.

이 경우 "PRAGMA temp_store" 구문으로 디스크에서 임시 파일을 만들지 않고 메모리 공간 내의 임시 파일 저장소를 사용해 성능을 향상시킬 수 있다.

이 값은 0 은 DEFAULT 로 SQLite lib 이 컴파일 될 때 코드 내의 SQLITE_TEMP_STORE 매크로에 설정된 값으로 생성된다.

1은 FILE 로 임시 파일이 디스크에 생성되며,

2 는 MEMORY 로 임시파일이 메모리 내의 임시 파일 저장소에 생성된다.

설정값을 설정할 때는 번호로 해도 되고, 문자열을 그냥 사용해도 된다.

"PRAGMA temp_store_directory" 는 임시 파일의 생성 위치를 지정할 수 있다.

이 때 해당 디렉터리는 미리 생성돼 있어야 하고 해당 디렉터리에 대한 쓰기 권한이 있어야 한다.

또 임시 디렉터리가 변경되면 해당 디렉터리에 들어 있던 임시 테이블을 비롯해 인덱스, 트리거 정보가 담긴 파일이 삭제된다.

또한 이 구문은 스레드에 안전하지 않으므로 변경할 때 주의를 기울여야 한다.

SQLite3 3.7.4 버전부터는 이 구문은 deprecated 가 되었다.

디스크 동기화 수준 설정

SQLite3 에서는 DB 에 레코드를 입력하거나 DB 에 담긴 레코드를 수정하면 먼저 내부 메모리에서 변경 작업을 하고 난 뒤 디스크에 쓰는 작업을 한다.

이 때 메모리의 변경사항이 디스크 표면에 부분적이 아닌 온전하게 기록되어야만 DB 파일이 손상되지 않는다.

이 같은 디스크 동기화(Sync) 연산은 메모리 연산에 비해 비교적 오래 걸리기 때문에 이 연산을 수행하는 동안에는 해당 프로세스가 잠시 멈춰 있는 것처럼 보인다.

이 경우 "PRAGMA synchronous" 구문을 이용해 디스크 동기화 수준을 조절해 속도 향상을 꾀할 수 있다.

FULL(2) : 메모리상의 변경사항이 디스크 표면에 완전히 기록된 것을 확인할 때까지 진행을 멈추고 대기한다(기본 설정)

NORMAL(1) : 디스크 동기화 작업을 수행하지만, FULL 보다 동기화 수준이 낮다.

OFF(0) : 별도의 디스크 동기화 작업 없이 메모리에서 변경된 사항이 디스크에 기록되는 것을 확인하지 않고 계속 진행한다.

잠금 모드 설정

"PRAGMA locking_mode" 구문을 이용하면 해당 DB 파일의 잠금 수준을 확인하거나 설정할 수 있다.

NORMAL : 각 트랜잭션의 읽고 쓰는 동작에 맞춰 DB 파일의 잠금 수준이 자동적으로 조절된다.(기본 설정)

EXCLUSIVE : 해당 연결에서 DB 파일의 잠금 수준이 올라가면 트랜잭션의 종료와 상관없이 해당 수준을 그대로 유지한다.

NORMAL 은 기본 설정으로 트랜잭션의 동작에 맞춰 잠금 수준이 변경된다.

최종적으로 해당 트랜잭션이 COMMIT 되는 경우 DB 파일에는 잠금 모드가 모두 풀린다.

하지만 EXCLUSIVE 로 설정한 경우 한번 잠금 수준이 올라가면 DB 연결이 유지되는 동안 해당 잠금 수준이 그대로 유지된다.

예를 들어 EXCLUSIVE 모드에서 DB 파일을 읽으면 SHARED 잠금 수준으로 변경되며 해당 파일을 쓰면 EXCLUSIVE 잠금 수준으로 설정되어 트랜잭션이 종료되더라도 잠금 수준은 그대로 유지된다.

이 때 해당 DB 연결이 종료되거나 잠금 모드를 NORMAL 로 변경해서 DB 를 쓰거나 읽으면 잠금 수준이 변경된다.

단순히 NORMAL 로 변경하는 경우에는 잠금 수준이 변경되지 않으므로 주의를 기울여야 한다.

일반적으로 한 앱에서 다른 프로세스의 간섭 없이 해당 DB 파일을 전용으로 사용하고 싶거나 임베디드 파일 시스템에서 최적화 등의 이유로 EXCLUSIVE 모드를 사용한다.

또한 임시 DB 파일이나 메모리 DB 인 경우에는 항상 EXCLUSIVE 모드로 동작한다.

자동 파일 크기 최적화

SQLite3 에서는 DB 파일에 저장된 레코드가 명시적으로 VACUUM 구문을 사용하지 않는 한 물리적인 파일의 크기는 줄어들지 않는다.

물리적인 파일의 크기를 줄이는 대신 SQLite3 내부의 빈 페이지(free page) 리스트에 등록해서 추후 추가적인 공간이 필요할 때 재사용한다.

하지만 "PRAGMA auto_vacuum" 구문을 사용하면 기본 설정을 변경할 수 있다.

NORMAL(0) : DB 에서 레코드가 삭제되더라도 파일의 크기는 줄어들지 않는다. (기본 설정)

FULL(1) : DB 레코드가 삭제되는 구문이 커밋되면 물리적인 파일 크기를 줄인다.

INCREMENTAL(2) : FULL 일 때 발생하는 DB 파일 단편화 문제를 해결하기 위한 실험적인 모드, incremental vacuum 구문을 수행해야 파일의 크기가 줄어든다.

SQLite3 lib 을 컴파일할 때 별도의 옵션 SQLITE_DEFAULT_AUTOVACUUM 을 설정하지 않으면 레코드가 삭제돼도 물리적인 DB 파일의 크기는 줄지 않는다.

이런 경우 해당 파일의 크기를 줄이려면 명시적으로 VACUUM 구문을 수행해야 한다.

그러나 FULL 로 설정된 경우에는 커밋될 때마다 빈 페이지들을 DB 파일의 끝부분으로 이동하고 파일 크기를 줄인다.

하지만 앞서 설명한 것처럼 단편화 문제가 발생한다.

파일 크기 이외에도 유의해야 할 점이 하나 있는데 rowid 가 변경된다는 것이다.

질의문 내에서 rowid 를 참조하는 경우에는 이 점을 염두해야 한다.

VACUUM 구문은 ATTACH 된 DB 에는 영향을 미치지 않는다.

LIKE 연산 시 대소문자 구별

SELECT 문의 LIKE 연산은 GLOB과 다르게 기본적으로 알파벳 대소문자를 구별하지 않는다.

즉 LIKE 연산에서 ‘A’ 와 ‘a’ 는 동일한 문자열로 판단한다.

하지만 "PRAGMA case_sensitive_like" 구문을 이용해 LIKE 연산도 대소문자를 구별하도록 변경할 수 있다.

문자열 인코딩 변경

"PRAGMA encoding" 구문을 이용하면 DB 파일에 저장되는 내부 문자열의 인코딩을 확인하거나 변경할 수 있다.

변경 가능한 값은 UTF-8, UTF-16, UTF-16le(little endian), UTF-16be(big endian)이다.

인코딩은 DB 파일이 생성되기 이전에만 변경할 수 있으며, 이미 DB 파일이 생성된 경우에는 해당 구문은 무시된다.

12. 전문 검색

SQLite3 에서는 FTS(Full Text Search)라는 전문 검색용 모듈을 제공한다.

이 모듈을 이용하면 여러 문자 데이터에서 원하는 키워드를 빠르게 검색할 수 있으며, 동작 방식은 구글에서 웹 문서를 검색하는 것과 유사하다. ( 초기 FTS3 모듈은 구글의 스콧헤스가 작성했다. )

성능 비교 자료에 따르면 이메일 517,430 개에서 linux 라는 문자열을 검색하는 FTS3 모듈을 사용하면 0.03초 걸렸으나 조건절에 LIKE 로 검색을 하는 경우에는 22.5초가 걸렸다.

또한 LIKE 로 검색하는 경우 ‘linuxphone’, ‘EnterpriseLinux’ 같은 문자열 중 일부가 일치하는 경우도 검색되어 원하는 결과를 얻을 수 없었다고 한다.

그러나 빠른 문자열 검색을 위해 FTS3 모듈은 역인덱스(inverted index)라는 특수한 자료 구조를 활용하며, 이로 인해 추가적인 저장 공간이 필요하기 때문에 DB 파일의 크기가 커진다.

FTS 테이블 생성

전문 검색을 위한 FTS 테이블을 생성하려면 ‘CREATE VIRTUAL TABLE’ 구문을 이용하면 된다.

이와 함께 USING 이라는 키워드와 사용할 모듈명인 fts3 모듈을 명시하고 테이블의 컬럼을 작성한다.

이 때 FTS 테이블은 오직 전문 검색을 목적으로 하기 때문에 자료형을 명시적으로 선언하더라도(혹은 선언하지 않더라도) 모두 TEXT 타입으로 변환되어 입력된다.

ex)

sqlite> CREATE VIRTUAL TABLE email

USING fts(subject, contents);

FTS 테이블을 생성할 때 명시적으로 컬럼명을 입력하지 않는 경우 자동으로 전문 검색이 가능한 ‘content’ 라는 하나의 컬럼을 갖는다.

FTS 테이블은 일반 테이블과 다르게 인덱스나 트리거를 만드는 것은 지원하지 않는다.

또한 ALTER TABLE 로 table 이름을 변경하는 것은 가능하지만, 컬럼을 추가하는 것은 불가능하며, 그 밖에 CHECK 같은 컬럼 제약조건도 모두 무시된다.

FTS 테이블의 레코드 입력, 수정, 삭제

FTS 테이블에도 SQLite3 의 일반적인 구문 형식으로 레코드를 입력, 수정, 삭제할 수 있다.

FTS 테이블 또한 숨겨진 내장 컬럼인 rowid 를 가지고 있으며, 명시적으로 rowid(docid)를 지정해서 레코드를 입력하거나 조건절에 사용할 수 있다.

하지만 VACUUM 을 수행하면 일반 SQLite3 테이블의 rowid 는 재배치되어 변경되는 반면 FTS3 테이블은 한번 할당된 rowid 를 변경하지 않는다.

FTS3 모듈은 내부적으로 레코드가 입력, 삭제, 갱신됨에 따라 갖가지 다른 B-Tree Index 가 생성된다.

이렇게 인덱스를 분리하면 쓰기(write) 연산의 성능은 향상되는 반면, 검색 시에는 분리된 인덱스를 모두 검사해야 하기에 성능이 저하된다.

이런 경우 다음과 같은 구문으로 분리된 인덱스를 하나로 통합해서 최적화할 수 있다.

SELECT optimize(FTSTableName) FROM FTSTableName;

수행 결과는 문자열로 반환되어 최적화된 경우 ‘index optimized’ 가 반환된다.

인덱스가 이미 최적화되어 있는 경우에는 ‘index already optimal’ 이 반환된다.

FTS 테이블 조회

FTS 테이블을 조회할 때 생성된 FTS 인덱스를 활용하려면 MATCH 키워드를 이용해 질의해야 한다.

질의문은 조건절에서 검색할 컬럼명 다음에 "MATCH searchKeyword" 형태로 작성하면 된다.

ex)

sqlite> SELECT rowid, subject, contents from email

WHERE contents MATCH ‘work’;

유사 결과를 얻기 위해 조건절에 LIKE 를 이용할 수도 있지만 테이블의 모든 레코드에서 찾으려는 문자열을 일일이 비교하기 때문에 검색 속도가 매우 느리다.

또한 정확한 단어가 아닌 포함한(include, contain) 단어도 찾기 때문에 올바른 결과를 얻을 수 없다.

만약 FTS 인덱스를 이용해 특정 단어로 시작하는 모든 단어를 찾고 싶을 경우에는 키워드 다음에 * 를 붙이면 된다.

ex)