SQLite 최적화 FAQ

Jim Lyon (jplyon@attglobal.net)
2003. 9. 10
Compiled from sqlite docs, source, and yahoo postings
Translated by Park JongHwa (http://littlebrain.tistory.com)
원본 http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html

이 문서는 SQLite 2.8.6에 맞춰서 작성되었습니다.

po발번역wer!!

1. 소개

1.1. 본 문서에 대해

이 문서는 SQLite 라이브러리를 사용한 어플리케이션 최적화 과정에서 얻은 필자의 경험을 정리한 미완성 문서입니다. 이 문서는 이미 SQLite 사용에 친숙한 사람들에게 보다 유익할 것입니다. 이 문서에 관한 질문이나 코멘트는 언제든지 환영입니다.

이 문서는 SQLite license하에 배포할 수 있습니다. :)

1.2. 목차

  1. 소개
  2. PRAGMA 설정
  3. 트랜잭션
  4. 인덱스
  5. 최적화 쿼리
  6. 코드를 SQL로 옮기기
  7. 사용자 함수
  8. 콜백 함수
  9. DB 파일 크기 줄이기
  10. DB 로드 시간 줄이기
  11. 쿼리 파싱 시간 줄이기
  12. 소스 수정하기
  13. B-Tree 인터페이스 사용
  14. 다중 쓰레드
  15. 운영체제 이슈
    부록
  1. 시간 측정법

1.3. 개요

SQLite는 충분히 빨라질 수 있습니다. 만약 여러분이 MySQL, PostGres와 같은 다른 DBMS보다 느리다고 느낀다면, 그것은 SQLite를 최대한 최적화하지 못했기 때문입니다. 속도 최적화는 SQLite의 창시자인 D. Richard Hipp이 두번째로 강조하는 것입니다. 첫번째는 데이터 무결성과 검증가능성입니다.

여러분이 반드시 알아야 할 것 중 하나는, SQLite(및 다른 대부분의 DBMS)에서 가장 긴 시간이 걸리는 작업은 디스크 액세스라는 것입니다. 그러므로 SQLite 최적화의 키포인트는 디스크 액세스를 최소한으로 줄이는 것입니다. 이를 위해서는 SQLite가 언제, 그리고 왜 디스크에 엑세스 하는 지를 이해해야 합니다. 일반적으로 운영체제는 이미 열려진 파일을 메모리에 캐싱해 두기 때문에, 세심하게 SQLite를 다룬다면 항상 메모리에 적재된 데이터베이스를 이용해 빠른 속도라는 결과물을 얻을 수 있습니다.

여러분이 최적화에 처음 도전하신다면, 여러분은 속도 최적화의 유일한 방법이 어디에 시간이 소모되는 지 파악해야 한다는 것임을 알아야 합니다. 여러분이 SQLite를 다루는 데 익숙하더라도, 잘못 생각할 때가 있기 마련입니다. 가능한 한 많은 경우에 대해 테스트를 해 보십시오. 불행히도, 어플리케이션 내부에서 디스크 엑세스를 수행하는 작업을 측정하는 것은 그다지 쉽지 않습니다. 부록 시간 측정법를 참조하십시오.

1.4. 간추려 보기

코드에 한 두 줄 추가하는 것 만으로 할 수 있는 것들이 몇 가지 있습니다. 이것 만으로도 큰 변화를 이끌어 낼 수도 있습니다.

2. PRAGMA 설정

2.1 PRAGMA cache_size

SQLite 데이터베이스는 기본 1KB 크기의 "페이지"라는 이름의 B-Tree로 분할됩니다. SQLite는 작업 수행 시 이 페이지들을 그들을 추적하기 위한 몇 가지 장치와 함께 메모리에 올립니다. 만약 쿼리가 캐싱된 페이지에만 접근한다면, 디스크 액세스가 필요하지 않기 때문에 훨씬 빨라질 것입니다.

cache_size라는 값은 SQLite가 한 번에 메모리에 유지할 페이지의 최대 수입니다. 버전 2.8.6 기준으로 디폴트 값은 2000입니다.("sqliteInt.h" 파일의 MAX_PAGES로 정의). 이 값은 데이터베이스 파일에 저장되어 있으며, SQLite가 데이터베이스를 불러올 때 읽어오는 변수인 default_cache_size를 수정하여 변경할 수 있습니다.

데이터베이스 페이지의 기본 크기가 1KB이므로, 최근의 장치라면 아주 많은 개수의 페이지를 메모리에 올려둘 수 있습니다. 페이지 캐시는 미리 할당되는 것이 아니기 때문에 읽어야 할 페이지 수보다 많은 양을 세팅하더라도 오버헤드가 커지지는 않습니다. 더불어, 운영체제는 페이징에 있어 대체로 여러분보다 나은 선택을 합니다.

동적으로 값을 세팅할 수도 있기 때문에, 큰 데이터를 위해서 일시적으로 값을 늘렸다가 작업이 끝난 후 다시 원래의 값으로 돌려 놓을 수 있습니다.

2.2 PRAGMA synchronous

불리언 변수인 synchronous는 라이브러리가 디스크 쓰기 작업이 끝날 때까지 자기 작업은 기다릴 지를 결정하는 값입니다. 이 값은 데이터베이스에서 불려오는 default_synchronous를 수정하여 변경할 수 있습니다. 일반적인 경우 라이브러리는 단지 파일 시스템을 기다리는 데 많은 시간을 소모하곤 합니다. "PRAGMA synchronous=OFF"라고 세팅하는 것 만으로도 큰 변화를 볼 수 있을 것입니다.

2.3 PRAGMA count_changes

만약 count_changes 값이 ON이라면, 콜백 함수는 매 DELETE, INSERT, UPDATE마다 한 번씩 호출될 것입니다. 인수는 값이 변한 행의 개수입니다. 만약 여러분이 이와 관련된 작업을 하지 않는다면, 이 값을 OFF로 바꾸는 것으로 약간의 성능 향상을 얻을 수 있습니다.

이 pragma는 SQLite의 차기 버전에서는 제거될 예정입니다. sqlite_changes() API 함수를 대신 사용하는 것을 고려해 보십시오.

2.4 PRAGMA temp_store

(이 PRAGMA는 2.8.6에서 완전히 구현되지 않았습니다.)

temp_store라는 값은 데이터베이스가 임시 파일을 어떤 식으로 운용할 지를 정하는 변수입니다. 정할 수 있는 값은 DEFAULT (0), FILE (1), MEMORY (2)입니다. 임시 테이블이 메모리에 올라가도록 수정한다면 눈에 띄는 효과를 볼 수 있을 것입니다. DEFAULT는 컴파일 타임에 정해진 값을 따르는 것으로, 특별히 수정하지 않았다면 FILE과 같습니다.

이 pragma는 SQLITE_OMIT_INMEMORYDB를 사용해 SQLite가 데이터베이스 메모리 적재 지원을 못하게 하면 아무런 효과도 발휘하지 못합니다.

데이터베이스의 기본 세팅값으로 이 pragma와 같은 역할을 하는 변수인 PRAGMA default_temp_store도 있습니다.

3. 트랜잭션

3.1. BEGIN TRANSACTIONEND TRANSACTION 사용하기

이미 트랜잭션 안에 있더라도, 각 SQL 구문은 자신만의 트랜잭션을 수행합니다. 이것은 매우 비효율적인데, 각 구문마다 저널 파일을 다시 열고, 쓰고, 닫는 작업을 해야 하기 때문입니다. 이런 일련의 작업을 전체 SQL 구문들의 앞뒤에 BEGIN TRANSACTION;END TRANSACTION;을 붙여 줌으로서 피할 수 있습니다. 이는 데이터베이스를 변경하지 않는 구문에 대해서도 효과가 있습니다.

키워드 COMMITEND TRANSACTION과 같은 의미입니다.

다음과 같이 sqlite_exec() 함수를 호출하여 트랜잭션을 시작하고 끝낼 수 있습니다.

 
sqlite_exec(sqlitedb, "BEGIN;",...);
sqlite_exec(sqlitedb, query1,...);
...
sqlite_exec(sqlitedb, queryN,...);
sqlite_exec(sqlitedb, "END;",...);

참고로 SQLite는 트랜잭션이 시작되면 데이터베이스 파일에 쓰기 잠금을 겁니다. 그러므로 여러 쓰레드에서 하나의 데이터베이스 파일을 다루려고 할 때 쓰레드간 데드락이 일어나지 않도록 조심해야 합니다.

.

3.2. 실패한 트랜잭션 처리

SQLite가 이름 붙은 트랜잭션을 파싱하기는 하지만, 이름은 무시됩니다. SQLite는 겹쳐진 트랜잭션을 지원하지 않습니다. 만약 여러분이 트랜잭션 안에서 여러 번의 sqlite_exec() 함수를 사용해 SQL 구문을 나눠 실행하려 한다면, 여러분은 각 구문의 실행이 실패했을 때를 대비해야 합니다. SQLite 구문의 실행이 실패한다면 0이 아닌 값이 리턴됩니다. 그 이후, SQLite는 새로운 트랜잭션이 시작되기 전에 각 SQL 구문에 대해 기본 동작으로 되돌립니다.

3.3. 저널링 비활성화

SQLite는 트랜잭션 실패 시 롤백을 위해 "저널" 파일을 사용합니다. 이 파일은 트랜잭션 시에 생성됩니다. 이 파일 생성이 디스크 액세스를 발생시켜 추가적인 오버헤드가 생기고, SQLite의 실행 속도에 영향을 끼칩니다.

저널 파일은 데이터베이스를 수정하는 작업에서만 사용됩니다. 또한 저널 파일은 읽기 전용 데이터베이스 파일에 대해서는 생성되지 않습니다. 그리고 임시 테이블에서 작업을 수행할 때에는 테이블의 스키마를 수정할 때 조차도 사용되지 않습니다.

쓰기 가능한 데이터베이스에 대해, C 코드 레벨에서 저널링을 비활성화 하는 것이 가능합니다. 이것은 최후의 수단으로 사용되는 것이 좋습니다만, 게임처럼 속도가 데이터 무결성보다 중요한 어플리케이션같은 경우도 있을 수 있습니다. 대부분의 경우에는 임시 테이블을 사용함으로서 비슷한 효과를 얻을 수 있습니다.

4. 인덱스

인덱스는 컬럼의 정렬 상태나 저장된 값을 보존합니다. 인덱스를 사용함으로서 여러분은 테이블 전체를 탐색하지 않고도 원하는 값을 SELECT할 수 있습니다. 인덱스는 쿼리가 테이블 전체를 탐색할 필요가 없을 때 크나큰 효과를 보여줍니다.

인덱스는 색인된 테이블의 행과 열에서 생성된 키를 매핑한 인덱스 테이블로부터 만듭니다. 때문에 추가적인 공간이 필요하지만, 이는 속도 이득으로써 충분히 상쇄하고도 남습니다.

4.1. 인덱스 생성

SQLite는 CREATE TABLE을 수행할 때 PRIMARY KEY 컬럼을 포함한 모든 UNIQUE 컬럼에 대해 자동으로 인덱스를 생성합니다.

 
CREATE TABLE mytable (
  a TEXT PRIMARY KEY,  -- 인덱스 생성
  b INTEGER,
  c TEXT UNIQUE        -- 인덱스 생성
);

여러분은 또한 CREATE INDEX 구문을 이용해 이미 존재하는 테이블에 인덱스를 생성할 수 있습니다. 물론 나중에 인덱스를 제거하는 것도 가능합니다.

4.2. 다중 컬럼 하의 인덱스

4.3. 인덱스가 올바르게 사용되고 있는지 점검

4.4. 인덱스를 사용하는 쿼리 적절히 정렬하기

4.5. 접두사 탐색

4.6. 접미사 탐색

4.7. 컬럼의 Rowid는 정수 인덱스

4.8. 올바른 정렬을 위한 날짜 저장

날짜를 정확히 저장하기 위해서는 정수값으로 저장하면 됩니다. 만약 문자열로 저장하려고 한다면, 다음과 유사한 포맷으로 맞춰서 저장해야 합니다. "YYYY-MM-DD HH:MM:SS.XXX Day"

4.9. 인덱스 대체 요소 -- 빠른 탐색을 위한 해시값 사용

4.9. 인덱스를 쓰면 안되는 경우

여러분이 인덱스가 생성된 테이블에 값을 넣으려고 한다면, 모든 데이터를 새로 넣고 인덱스를 다시 만드는 편이 오히려 더 빠를 수 있습니다.

만약 여러분의 쿼리가 LIKE 문을 사용하는 찾기 쿼리처럼 테이블 전체를 사용해야 한다면 인덱스 사용은 별로 좋지 않습니다.

5. 쿼리 최적화

5.1. EXPLAIN 커맨드

EXPLAIN 커맨드는 파서에서 출력하는 VDBE opcodes를 보여주며, 이를 통해 쿼리가 실제로 어떻게 실행되는 지를 알 수 있습니다. 이 opcodes에 대한 도움말은 온라인에서 찾아볼 수 있으며, "vdbe.c" 코드를 참조해도 좋습니다.

sqlite 도구를 명령줄에서 실행하여, .explain명령을 이용해 EXPLAIN의 출력을 더욱 보기 쉽도록 바꿀 수 있습니다.

5.2. 임시 테이블

생성된 코드를 조작하기 위해 사용합니다.

(digest 337의 Message 5 참조 (sam_saffron))

서브쿼리는 임시 테이블을 통해 내부에서 동작합니다. 서브쿼리를 주 쿼리에서 분리해 냄으로서, 복잡한 쿼리가 분해되어 파서가 내놓은 코드가 바뀝니다.

결과를 즉시 임시 테이블에 저장합니다.

5.3. 작은 결과부터 출력되도록 서브쿼리 정리

만약 쿼리가 다양한 기준으로 필터링 되도록 짜여져 있다면, 가장 결과가 적은 것부터 출력되도록 바꿔 보십시오.
인덱스를 사용하는 테스트는 일반적으로 뒤쪽으로 옮길 수 없습니다.

5.4. LIMITOFFSET 사용

여러분은 종종 어떤 조건에 들어맞는 결과가 최소 한 개 존재하는지를 알고 싶을 때가 있을 것입니다. SQLite는 EXISTS를 지원하지 않지만, LIMIT 1을 이용해 원하던 결과를 얻을 수 있습니다.

5.5. 구문

5.5.1 GLOBLIKE 사용 자제

SQLite에서 GLOBLIKE 명령은 부하가 큰 편인데, 이는 이들 명령어가 인덱스를 사용하지 못하기 때문입니다. 그러한 이유 중 하나는 이것들이 오버라이드 가능한 사용자 함수이기 때문으로, 그렇기에 파서는 이들이 어떠한 동작을 해야 할 지 알지 못합니다. 이는 컬럼이 인덱스를 가지고 있더라도 매칭을 위해 테이블 전체를 탐색하도록 강제합니다.

특정 문자로 시작해 와일드카드 * (혹은 LIKE에서의 %)로 끝나는 문자열에서의 GLOBLIKE 구문은 부등호 연산자 쌍으로 바꿀 수 있습니다. 이것은 와일드카드 %가 문자열의 맨 끝에 나타나, 결과 문자열이 항상 접두사 다음, 접두사와 같은 길이의 동일한 문자열 이전에 정렬되기 때문에 가능합니다. 이 부등호 연산자는 아래 예제의 x가 컬럼이라면 인덱스를 이용할 수 있습니다.

예) (x GLOB 'abc*')은 다음 명령으로 바꿀 수 있습니다 : (x >= 'abc' AND x < 'abd').

참고) LIKE연산자는 대소문자를 구분합니다. 그러므로 LIKE 하의 문자열이 모두 대문자로 이뤄져 있거나 모두 소문자로 이뤄져 있는 것이 아니라면 위의 방법은 사용할 수 없습니다.

만약 임의의 문자열로 이루어져 있고 길이는 같은 문자열을 매칭시켜야 한다면, SQL 함수인 length()를 사용해도 됩니다.

예) (x GLOB '???')은 다음 명령으로 바꿀 수 있습니다 : (length(x) == 3).

만약 *% 와일드카드가 문자열의 중간에 사용되거나, 문자열 끝에 복잡한 패턴이 나타난다면, 여러분은 위에 소개된 테크닉을 사용해 보다 효율적인 필터를 만들 수 있습니다.

예) (x GLOB 'abc*jkl*')은 다음 명령으로 바꿀 수 있습니다 : (x > 'abc' AND x < 'abd' AND x GLOB 'abc*jkl*').
예) (x LIKE 'abc_')은 다음 명령으로 바꿀 수 있습니다 : (x > 'abc' AND x < 'abd' AND length(x) == 4).

5.5.2 매우 긴 문자열에 length() 함수를 쓰지 말 것

length() 함수는 전체 문자열을 탐색하며, 해당 문자열이 포함된 컬럼 전체의 로딩에 영향을 끼칩니다. 정해진 페이지 사이즈(기본 1K)를 초과하는 문자열이 있다면, 한 번에 불려졌어야 할 남은 데이터들은 순차적으로 다른 페이지들로 나뉘어집니다. 만약 여러분이 짧은 부분에 대해서만 다루려 한다면, 첫번째 페이지만을 불러올 수 있는 방법이 존재합니다.

비어있는 문자열에 직접 접근

예) (WHERE length(str)=0)(WHERE str='')으로 교체.

LIKEGLOB를 짧은 문자열의 길이를 파악하는데도 사용할 수 있습니다.

예) (WHERE length(str)==3)(WHERE str GLOB '???')이나 (WHERE str LIKE '___')로 교체 가능.

긴 문자열을 여러 번 읽어야 하는 경우에는, 문자열의 길이를 다른 컬럼에 따로 저장해 두는 것도 좋습니다. 이 길이 값은 필요하다면 TRIGGER를 이용해 변경할 수 있습니다. 만약 이를 데이터베이스에 저장하는 것이 비합리적으로 보인다면, 임시 테이블에 값을 저장한 후 원본 테이블과 JOIN하여 사용해도 됩니다. 기억해야 할 것은, length() 함수는 인덱스를 사용할 수 없기 때문에, (length(x) < 45)와 같은 표현은 테이블 전체를 탐색한다는 것입니다. 만약 여러분이 테이블 전체를 탐색하면서 각 행에 대해 최소한 한 번은 계산해야 할 일이 있다면, 문자열을 테이블에 저장하기 전 길이를 따로 저장해두는 편이 좋습니다.

5.5.3 비효율적 인덱스 제거

인덱스는 쿼리가 테이블에서 불필요한 행을 건드리는 것을 막아줍니다. 인덱스를 사용하기 위해서 추가 비용이 드는데, 그것의 테이블이 데이터베이스에서 불려와야 하며, VDBE가 추가적인 코드를 실행해야 합니다. 이러한 추가 비용은 보통 테이블 전체 탐색을 하지 않아도 되는 이득에 비해 매우 작으므로 무시되는 편입니다. 하지만, 인덱스가 쿼리 수행 속도를 더 느리게 하는 몇 가지 경우도 존재합니다.:

이미 존재하는 인덱스는 쿼리를 인덱스를 사용하지 않도록 수정함으로서 우회할 수 있습니다. 이 방법은 위의 이유로 일시적으로 인덱스가 비효율적일 때 유용합니다. "where.c" 파일에 있는 쿼리 최적화 도구는 "column-name binop expr"나 "expr binop column-name"와 같은 유형의 쿼리를 취급합니다. 이 때 binop<, <=, ==, =, >=, > 중 하나입니다. 쿼리를 이런 유형에 맞지 않도록 수정하면 해당 컬럼의 인덱스를 사용하지 않게 할 수 있습니다.

예) "WHERE x=5"는 인덱스를 우회하기 위해 "WHERE x+0=5"이나 "WHERE +x=5"로 바꿀 수 있습니다.

5.5.4 OR 대신 IN을 사용하라

"x='A' OR x='B'"와 같은 명령은 컬럼 x에 대한 인덱스를 사용하지 않습니다. 그 대신 "x IN ('A', 'B')"를 사용하십시오.

6. 코드를 SQL로 옮기기

어플리케이션에서 SQLite를 사용하기 위해서는 일종의 "장벽"을 넘기 위한 추가 비용이 들게 됩니다. 메모리가 할당되어야 하고 결과가 복사되어야 하며, 이는 한 번이 아닐 수도 있습니다. 만약 여러분이 결과를 다루기 위한 C 코드를 콜백 함수, 사용자 함수, 트리거 등의 방법을 통해 SQLite "내부로" 옮길 수 있게 된다면, 상당한 수준의 속도 향상을 얻게 될 것입니다.

이러한 방법을 사용할 수 있는 한 예로, 어떠한 컬럼에 있는 모든 데이타의 이름을 바꿔야 할 때가 있습니다. 필자는 처음에 이를 위해 데이터를 가져와서, 이름을 바꾼 후, 다시 데이터베이스에 집어넣는 식으로 코딩했었습니다. 이 방법에는 트랜잭션 오버헤드를 비롯한 몇가지 문제가 있었습니다. 이 복잡한 이름 변경 함수는 UPDATE문을 사용한 하나의 SQL구문으로 바꿀 수 있습니다. SQL 구문이 인터프리트 되었다는 사실은, 하나의 쿼리만을 실행함으로서 얻을 수 있는 오버헤드 감소와 리턴값을 받을 필요가 없는 이점에 비해 확연히 적습니다.. 여기에 더해 상기의 작업을 사용자 함수로 만들어 SQL 구문을 "컴파일"하게 할 수도 있지만, 이 이상으로 빠른 속도는 거의 필요하지 않습니다.

6.1. 코드를 SQL 구문으로 바꾸기

6.2. 코드 데이터를 SQLite 데이터베이스로 옮기기

6.3. 코드를 사용자 함수로 감싸기

6.4. 여러 개의 쿼리를 사용자 함수를 사용하는 하나의 쿼리로 합치기

이를 통해 명시적으로 TRANSACTION 구문을 기재할 필요가 없게 됩니다.

이를 통해 쿼리를 한 번만 파싱하면 되도록 합니다.

6.5. 코드 치환을 위한 TRIGGER 사용

7. 사용자 함수

7.1. 사용자 함수의 예

7.2. 통계 함수

8. 콜백 함수

8.1. 콜백 함수의 예

8.2. sqlite_get_table()과 콜백의 교체

8.2.1. sqlite_get_table() 함수는 테이블 데이터의 결과를 위한 추가 메모리 공간을 할당합니다. 데이터 양이 많을 경우 이러한 메모리 할당이 시스템에 부담이 될 수 있으며, 심지어는 수백만 번의 malloc()을 필요로 할 수도 있습니다. 이런 수많은 malloc() 호출은 메모리 풀을 조각내는 원인이 됩니다.

8.2.2. 각 행을 순차적으로 다루기 위한 콜백 사용

8.2.3. 특정 지점의 쿼리에서 오류가 발생했을 때의 처리

8.2.4. 가능한 최적화

8.2.4.1. 참고로 할당된 공간이 결과 집합의 행과 열의 수에 따라 고정된 최소값으로 정해지는 경우도 있을 수 있습니다. 만약 우리가 이 최소 크기를 적절히 정해줄 수 있다면, 불필요한 재할당을 많이 방지할 수 있을 것입니다. 이를 위해서는 단지 sqlite_get_table() 함수에서 다음 코드만을 바꿔 주면 됩니다. "res.nAlloc = 20;"

8.2.4.2. 우리가 어떤 행의 데이터 전체에 대한 메모리를 한꺼번에 할당하면, 각 행의 값에 대한 개별 malloc() 호출 수를 많이 줄일 수 있습니다. 다만 이 방법은 개별 값에 대한 조작에 제한을 줄 수 있으므로 사용에 주의를 요합니다.

9. DB 파일 크기 줄이기

9.1. 데이터베이스 압축

어떠한 정보가 데이터베이스에서 제거될 경우, B-Tree 페이지는 빈 페이지가 되지만, 데이터베이스에서 완전히 제거되지는 않으며 나중에 다시 사용될 때를 대비해 '비어 있다'는 표시가 된 채로 남게 됩니다. 후에 새로운 페이지가 필요하게 되면, SQLite는 데이터베이스의 크기를 늘리는 대신 이런 빈 공간을 먼저 사용합니다. 이것이 반복되면 데이터베이스 조각이 발생하여, 파일 크기가 담고 있는 데이터의 크기보다 커지게 되며, 데이터 자체도 파일 내에서 잘 정리돼 있지 않게 됩니다.

동적 데이터베이스에서 발생하는 또 하나의 부작용은 테이블 조각입니다. 단일 테이블에 대한 정보를 담고 있는 페이지들이 데이터베이스 파일 내에 흩어져 있어, 로딩에 많은 시간이 걸리게 되는 것입니다. 이는 필연적으로 데이터베이스의 속도를 깎아먹으며, 데이터베이스 압축을 통해 이러한 문제들을 해결할 수 있습니다.

빈 페이지들을 제거하는 가장 쉬운 방법은 SQLite 명령어인 VACUUM을 사용하는 것입니다. 이는 SQLite 라이브러리 호출을 통해 코드 내에서도 할 수 있고, sqlite 도구를 커맨드 라인에서 실행함으로써 할 수도 있습니다.

(C 코드)
sqlite_exec(db, "VACUUM;", 0, 0);

(sqlite 도구)
sqlite> VACUUM;

(쉘 프롬프트)
$ echo 'VACUUM;' | sqlite filename
또는
$ sqlite filename 'VACUUM;'

빈 페이지를 제거하는 또다른 방법은 데이터베이스를 비우고 새로 만드는 것입니다. 이는 오직 데이터베이스 밖에서만 할 수 있습니다.

(쉘 프롬프트)
$ echo '.dump' | sqlite file1.db > file1.sql
$ cat dump-file | sqlite file2.db
또는
$ sqlite file1.db '.dump' > dump-file
$ sqlite file2.db < file1.sql
또는
$ sqlite file1.db '.dump' | sqlite file2.db

(DOS / Win 프롬프트)
> echo .dump | sqlite file1.db > file1.sql
> sqlite file2.db < file1.sql
또는
> sqlite file1.db .dump > file1.sql
> sqlite file2.db < file1.sql

그리고 나서, 새로운 파일을 덮어씁니다. (혹은 원래 파일을 지운 후 새로운 파일을 같은 이름으로 생성합니다.)

(쉘 프롬프트)
$ cp file2.db file1.db

(DOS / Win 프롬프트)
> move file2.db file1.db

데이터베이스에 얼마나 많은 빈 페이지가 있는지는 런타임에 sqlite_count_free_pages() 함수를 실행시킴으로서 알 수 있습니다. 현재 버전은 freepages.c에 있습니다.

DRH가 빈 페이지를 알려주는 TCL 스크립트를 소개했습니다. space_used.tcl을 참조하십시오.

9.2. 파일 크기 계산

9.3. 다양한 페이지 크기 지원을 위한 SQLite 재컴파일

9.4. 데이터 크기 파악

데이터베이스에 저장돼 있는 데이터의 크기는 다음 쿼리를 이용해 파악할 수 있습니다.:

SELECT length(colname) FROM tablename
SELECT sum( length(colname) ) FROM tablename;
SELECT avg(length(colname)), min(length(colname)), max(length(colname)) FROM tablename;
SELECT sum( min(100, length(colname) * 0.125) ) FROM tablename;
SELECT name FROM SQLITE_MASTER WHERE type = 'table'
UNION SELECT name FROM SQLITE_TEMP_MASTER WHERE type = 'table';

9.5. 큰 데이터 압축

9.5.1. 압축도구

9.5.2. 이진 데이터 인코딩

SQLite의 이진 인코딩 API

SQLite 라이브러리는 sqlite_encode_binary()sqlite_decode_binary()와 같은 함수를 "encode.c" 파일 내에 포함하고 있습니다. 이 함수들을 이용하면 이진 데이터를 문자열 형태로 안전하게 인코딩하여 SQLite에 적절하게 보관하고, SQL 쿼리들이 안전하게 사용하게 할 수 있습니다. 이러한 함수들은 SQLite 라이브러리에 있어 "추가적인" 요소입니다. 즉, 이들은 컴파일 시 라이브러리에 디폴트로 포함되는 것이 아니며, 다운로드 페이지에서 제공되는 이미 컴파일 된 라이브러리에도 포함되지 않는다는 말입니다.

인코딩 사용은 매우 효과적일뿐더러, 추가적인 오버헤드도 대략 2%밖에 증가하지 않습니다. This is achieved by first scanning the data to find an infrequently used character which is suitable to use to rotate the data. 그리고 결과 데이터는 특수한 문자들로 치환됩니다. 아래에 소스 내에서 인코딩이 어떻게 작동하는 지 기술되어 있습니다.

(예제 추가바람)

Binhex 인코딩

Binary data can be encoded by converting each character to a sequence of 2 hexadecimal digits in ASCII form. This will result in strings twice as long as the orginal data, but this may not matter if the strings are small, since there is overhead in the database. The resulting strings will contain no apostrophes or 0s, so will be safe to use in SQL statements as well.

 
/* Convert binary data to binhex encoded data.
** The out[] buffer must be twice the number of bytes to encode.
** "len" is the size of the data in the in[] buffer to encode.
** Return the number of bytes encoded, or -1 on error.
*/
int bin2hex(char *out, const char *in, int len)
{
  int ct = len;
  if (!in || !out || len < 0) return -1;
 
  /* hexadecimal lookup table */
  static char hex[] = "0123456789ABCDEF";
 
  while (ct-- > 0)
  {
    *out   = hex[*in >> 4];
    *out++ = hex[*in++ & 0x0F];
  }
 
  return len;
}
 
/* Convert binhex encoded data to binary data.
** "len" is the size of the data in the in[] buffer to decode, and must 
** be even. The out[] buffer must be at least half of "len" in size.
** The buffers in[] and out[] can be the same to allow in-place decoding.
** Return the number of bytes encoded, or -1 on error.
*/
int hex2bin(char *out, const char *in, int len)
{
  int ct = len;
  if (!in || !out || len < 0 || len&1) return -1;
 
  while ((ct-=2) > 0)
  {
    char ch = ((*in >= 'A')? (*in++ - 'A' + 10): (*in++ - '0')) << 4;
    *out++ += ch + ((*in >= 'A')? (*in++ - 'A' + 10): (*in++ - '0'));
  }
 
  return len;
}
XOR 인코딩

If you know that your data doesn't contain a specific character, you can exclusive-or each character of the data with the missing character to remove all 0 characters. This encoding can be done in-place, because the encoded data will have the same length. Also, the encoding is symmetric, so the same function is used for decoding.

 
/* Example of encoding and decoding binary data. */
 
/* XOR encode a buffer which is missing char "ch".
** If "ch" isn't in the data, no 0s will occur in the encoded data.
** This function is symmetric, and can be used for both encoding and decoding.
** Return the buffer pointer.
*/
char *xorbuf(char *data, int len, char ch)
{
  int ct = len;
  char *d = data;
 
  if (!data || len < 0) return NULL;
 
  while (ct-- > 0)
    *d++ ^= ch;
 
  return data;
}
7-bit 데이터 인코딩

7-bit data can be encoded by toggling the high bit. This can be done with xorbuf() above using encoding character '0x80'. This technique results in data safe to use in SQL statements, since encoding can't produce an apostrophe character.

To obtain encoded data which is still readable, you can selectively remap just the 0 and apostrophe characters. The following function does this encoding and decoding.

 
/* Encode 7-bit ASCII data into the buffer out[], which must be at least
** "len" bytes in length.  The encoded data will have the same size as 
** the decoded data.  You must append a 0 byte to terminate the string.
** The buffers in[] and out[] may be the same. Call this function again 
** to decode. Return the length encoded, or -1 on error.
*/
int ascii7enc(char *out, const char *in, int len)
{
  int ct = len;
  if (!in || !out || len < 0) return -1;
 
  while (ct-- > 0)
  {
    char ch = (*in == 0 || *in == '\'' || *in == 0x80 || *in == '\''^0x80)?
     (*in++ ^ 0x80): *in++;
    *out++ = ch;
  }
 
  return len;
}
Escape-char 인코딩
MIME base64 인코딩

MIME base64 encoding is used to encode binary data for transmission in email messages. It encodes the data into printable ASCII characters, requiring a fixed overhead of roughly 33%. This method isn't recommended unless you have functions to do this easily available. There are techniques above that are either faster or produce smaller encodings. There is a public domain implementation online at fourmilab or freshmeat, which includes full C source, docs, and the RFC 1341 standard, and builds on both Unix and Windows.

9.5.5. Operating in-place on data returned by sqlite_get_table()

9.6. 대규모 데이터는 외부 저장장치로

뭐 CD-ROM이라든지...

10. DB 로드 시간 줄이기

데이터베이스를 처음 시작할 때 드는 로드와, 실행중 데이터를 불러올 때 드는 로드를 모두 다룹니다.

로딩 시간을 줄이는 방법은 사실 몇가지밖에 없습니다:
1) 필요최소한의 데이터만 불러오기
2) 불러올 데이터들이 디스크 공간에서 최대한 가까이 있을 것
3) 이미 불러온 데이터를 또 불러오는 일이 없도록 할 것

이미 언급한 최적화 방법 중에서도 로드 시간을 줄이는 방법이 있습니다:

10.1 정보를 외부에 저장

만약 어떤 컬럼에 저장돼야 할 데이터의 양이 굉장히 많으면, 데이터는 외부에 파일을 생성해 그 쪽에 저장하고, 컬럼에는 해당하는 파일의 경로만 기록하는 편이 더 나을 수 있습니다. 이러한 방법은 바이너리 데이터를 다룰 때 유용하지만, 텍스트 데이터의 크기가 매우 클 때도 유용합니다.

10.2 대규모 컬럼을 다른 테이블로 옮기기

여러분이 생성한 테이블의 어떤 컬럼에 저장된 데이터의 크기가 굉장히 클 경우, 그 데이터를 다른 테이블에 나누어 저장하고 원래 테이블을 FOREIGN KEY를 이용해 참조하도록 설계할 수 있습니다. 이를 통해 테이블을 불러올 때 해당 컬럼의 값이 당장 필요하지도 않은데 불러오는 로드를 줄일 수 있습니다. 또한 이 방법은 데이터베이스 파일에 들어있는 테이블의 나머지 부분을 관리하는 데도 도움을 줍니다.

10.3 압축된 데이터 저장

아무리 짧은 텍스트 데이터라도 압축을 통해 약간은 양을 줄일 수 있습니다. XML과 같은 마크업 언어는 압축하기 좋습니다. 그것들은 보통 압축된 데이터를 불러오는 시간도 짧고, 압축을 푼 후 원본 데이터를 로드하는 것도 빠릅니다. 만약 데이터가 잘 사용되지 않는 컬럼에 저장되어 있다면, 압축은 특히 유용합니다.

[Fn]ZipString() 함수와 [Fn]UnzipString() 함수를 이용해 압축을 쉽게 구현할 수 있으며, 이 함수들을 사용하기 위해서는 zlib 라이브러리를 링크하면 됩니다.

10.4 인덱스 추가, 제거

인덱스는 쿼리가 테이블에서 필요 없는 행을 불러오는 일을 방지해 줍니다. 다만, 테이블이 작거나 필요 없는 행이 거의 없을 경우에는 인덱스가 오히려 속도 저하를 초래하기도 합니다. 왜냐하면 이러한 경우는 테이블 데이터 전체를 불러오는 것과 마찬가지이기 때문에, 인덱스 데이터가 점유한 페이지를 캐시에서 내리는 데 시간이 추가로 들기 때문입니다.

11. SQL 쿼리 파싱 시간 줄이기

11.1. VDBE 프로그램을 재사용하는 새로운 API 함수 사용

11.2. LIKE 쿼리들의 시퀀스를 콜백 함수로 합치기

12. 소스 수정하기

12.1. 메모리 할당 라이브러리 교체

The memory allocation is notoriously bad on some systems (e.g. Windows). Replacing the functions malloc(), realloc(), and free() on these systems can have a dramatic effect on performance.

One widely used open source replacement is Hans Boehm's Memory Pool System at freshmeat and Ravenbrook Limited.
A light-weight, public domain implementation is Doug Lea's dlmalloc.
Both have bindings for malloc()/realloc()/free(), so they can be used as drop-in replacements for the standard memory allocation library.

12.2. sqlite_get_table() 수정하기

sqlite_get_table() 함수는 "table.c" 파일에 들어 있습니다. 이 함수를 더 효율적으로 수정하는 법을 알아봅시다.

sqlite_get_table_cb() originally allocates its array (that it returns string ptrs in) to size 20. When it needs more it reallocates by powers of 2 to avoid O(N^2) behavior. Afterwards it reallocates down to the size required by the number of elements. For large numbers of calls with small result sets, it is more efficient to use an application-tuned value for the initial size. This is set in member res.nAlloc in function sqlite_get_table() itself. This one-line change doesn't break anything else.

You can remove the if( res.nAlloc>res.nData ){...} case used to realloc down in size afterwards if you know you will always be freeing the table soon, which is the usual case. This is usually unnecessary because the table will be freed afterwards anyway. This array only holds the pointers to the result data, not the data itself, so if it twice as large as required, this usually doesn't present enough of an overhead to cause problems.

sqlite_get_table_cb() allocates the column strings for each row separately, so if there are 10 columns returned in a result, you get 10 malloc() calls for what are essentially inline strdup() calls (when the strings are not NULL). For large result sets (eg 100,000 rows), this would lead to 10^6 malloc() calls for the data.
A simple change is to allocate memory for the entire result row at once, with all the strings for a row copied contiguously into the memory block. This reduces the number of malloc() and free() calls proportionally to the number of rows, and has no overhead when there is only one column per row. The only thing this breaks is the ability to directly remove strings from the results table, which is not guaranteed anyhow. This only requires changes to sqlite_get_table_cb() and sqlite_free_table().

참고) if you are getting result sets of this size, using sqlite_get_table() only makes sense if you (1) need all the data available at once and (2) only want them if the entire query succeeds. If you don't require this, just using a callback is much more efficient because it bypasses all the copying entirely.

12.3. 컴파일-타임 옵션

12.4. 파일 라이브러리 교체

(Linking in custom file library under Windows for working with memory-mapped files.)

13. B-Tree 인터페이스 사용

13.1. 간단한 예제

digest #387의 message #8 참조 (by Mike Ekim).

14. 다중 쓰레드

14.1. 트랜잭션 -- 데이터베이스 잠금

14.2. 다중 쓰레드로부터 읽기

14.3. 변경사항 저장용 임시 테이블 사용

14.4. 쓰레드 잠금 함수 작성하기

15. 운영체제 이슈

15.1. 쓰레드 안전성

15.2. 페이징

15.3. 파일 잠금

15.4. 메모리 관리

15.5. 뮤텍스 다루기

15.6. 롤백

A. 시간 측정법

Accurately timing disk-intensive programs such as sqlite is rather hard.
There is variability in results, and both library and operating system paging can hide worst-case times.

A.1. 측정 도구

A.1.1 speedcompare 스크립트

DRH has uploaded a tcl script to the Hwaci website that compares the speed of two versions of SQLite named "sqlite-base" and "sqlite-test". The script is at speedcompare.tcl. This is the script he uses to judge the speed impact of changes made to the library.

A.1.2 쉘 명령어

A.1.3 C 함수

A.1.4 C 프로파일러

A.2. 타이밍 맞추기

A.2.1. Controlling file system variability

A.2.2. 반복문 테스트

반복문 내에서 같은 쿼리만 계속 사용하지 말 것.
(digest #302의 message #6 참조)

A.2.3. B-Tree 캐시 크기

PRAGMA cache_size로 설정되는 B-Tree 캐시의 크기는 속도에 큰 영향을 끼칩니다. 여러분은 이 값을 여러분의 프로그램에서 필요로 하는 값으로 변경해야 합니다.

If you want to test the speed of memory-intensive functions, you can set the cache size to a large value to minimize the effect of paging on the timings. After setting the cache size, force the entire table being used to load into memory with a SELECT statement.

A.2.4 데이터베이스에 저장된 설정들

몇몇 PRAGMA에 대한 기본 설정은 데이터베이스에 내장되어 있습니다. 이 값들은 재현가능한 실행결과를 위해 잘 제어돼야 합니다. 이들은 런타임에 각각에 대응하는 PRAGMA로 값을 변경할 수 있습니다. 이를테면 default_cache_sizedefault_synchronous 등이 있습니다.