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

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

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

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

-

SQLite3 이 주로 임베디드 시스템에서 사용되기 때문에 다른 DBMS 와 다르게 단순히 질의 성능만을 우선시하기보다는 파일 크기, 메모리 사용량, 전원의 비정상적인 종료(Power failure) 와 같은 상황을 모두 고려해서 최적화해야 한다.

1. 실행 계획을 통한 질의 최적화

-

개발 과정에서 SQL 문을 작성하고 수행해서 원하는 결과를 얻으면 즉시 앱에 적용하는 개발자들이 종종 있다.

하지만 이렇게 작성된 SQL 문이 비록 동작은 정상적으로 하지만 나중에 성능상의 문제를 일으키는 경우가 많다.

-

DB 의 실행계획(Execution plan)이란 DB 가 내부에서 질의를 수행하는 일련의 방법을 의미하는 것으로서 어떤 방법을 수행하느냐에 따라 성능 차이는 매우 크다.

-

테이블에서 원하는 결과를 선택하는 방법은 크게 두 가지로 나눌 수 있다.

테이블을 처음부터 끝까지 순차적으로 이동하면서 조건에 맞는 레코드를 선택하는 방법(테이블 스캔, Table Scan)과 인덱스를 이용해 해당 레코드를 직접 가지고 오는 방법(인덱스 스캔, Index Scan)이다.

전체 테이블을 검사하는 경우에는 레코드의 개수가 많을수록 조회하는 데 시간이 오래 걸린다.

반면 인덱스를 이용해서 조회하면 검색 성능은 상대적으로 전체 테이블을 검사하는 것에 비해 매우 빠르지만 새로운 레코드가 입력되거나 기존 레코드를 수정하는 경우에는 인덱스도 갱신되어야 하기 때문에 인덱스가 없는 경우와 비교해서 조금 느리게 동작한다.

실행 계획 확인

-

EXPLAIN QUERY PLAN 키워드를 사용해서 SQLite3 에서 수행되는 질의문과 관련된 테이블 정보,테이블 검색 방법과 사용된 인덱스 정보, 테이블에서 방문할 레코드의 예상 개수와 같은 실행 계획과 관련된 정보를 확인할 수 있다.

수행할 SQL 구문 앞에 해당 키워드를 입력하면 된다.

-

ex)

> EXPLAN QUERY PLAN

…>    SELECT * FROM info WHERE id = 3;

selectid    order    from     detail

--------     ------    -----      ----------------------------------------------------------------------

0               0           0          SEARCH TABLE info USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)

결과로 나오는 것 중 SEARCH 는 인덱스 스캔을 수행했다는 의미이다.

(rowid=?) 는 WHERE 절에서의 최적 성능을 발휘하는 케이스를 말한다.

(~1 rows) 는 테이블에서 방문할 것으로 예상되는 추정 레코드의 개수를 의미한다.

-

…. detail

…. ------------------------------------------------------------

…. SCAN TABLE info (~100000 rows)

SCAN 은 전체 테이블을 조회하는 테이블 스캔을 이야기한다.

-

…. detail

…. ------------------------------------------------------------

…. SCAN TABLE info (~100000 rows)

…. USE TEMP B-TREE FOR ORDER BY

ORDER BY name 과 같이 name 을 기준으로 정렬하는 경우에는 SQLite3 내부적으로 최적의 성능을 발휘하기 위해 임시(TEMP, Temporary) 인덱스를 만들어 사용하는 것을 확인 할 수 있다.

-

질의문에서 자주 조건절로 사용되는 컬럼인데 인덱스가 없는 경우에는 질의 수행 성능 향상을 위해 해당 컬럼에 인덱스를 생성해주는 것이 좋다.

범위 검색

-

비교 연산자, BETWEEN 등을 사용한 범위(Range) 검색에도 인덱스가 사용될 수 있다.

…. detail

…. ------------------------------------------------------------

…. SEARCH TABLE info USING INTEGER PRIMARY KEY(rowid>?)  (~330000 rows)

-

WHERE 절에서 동일한 컬럼을 OR 로 묶어서 조회하는 경우나 IN 연산을 사용하는 경우에도 인덱스를 이용해서 검색한다.

…. detail

…. ------------------------------------------------------------

…. SEARCH TABLE info USING INDEX idx_name (name=?)  (~30 rows)

…. EXECUTE LIST SUBQUERY 1

-

서로 다른 컬럼을 OR 로 연결하면 AND 나 OR 로 연결된 조건 중에서 인덱스가 가장 효율적일 수 있는 한 컬럼을 골라 해당 컬럼에 대해 인덱스 스캔을 하고 그 결과집합을 모두 순회하며 나머지 조건을 검색한다.

LIKE 와 GLOB 연산자

-

LIKE 와 GLOB 연산자는 문자열을 검색하는 연산자로서 대소문자 구별 여부와 같은 일부 특징을 제외하고는 동작 방식이 유사하다.

하지만 SQLite3 내부적으로 두 연산자 사이에 차이가 있다.

-

LIKE 를 사용하는 경우에는 인덱스를 활용하지 못하고 전체 테이블을 조회한다.

> EXPLAIN QUERY PLAN

…>    SELECT * FROM info WHERE name LIKE ‘D%’;

SCAN TABLE info (~50000 rows)

이런 경우 다음과 같이 변경하면 INDEX 를 사용하면서 성능 향상이 생긴다.

> EXPLAIN QUERY PLAN

…>    SELECT * FROM info WHERE name > ‘D’ AND name <‘E’;

SEARCH TABLE info USING INDEX idx_name (name>? AND name<?) (~110000 rows)

-

다음과 같이 LIKE 연산자에서 ‘_’ 를 사용해 검색하는 문자열의 글자 수를 지정하는 경우에도 범위 검색과 length() 함수로 동일한 결과를 인덱스를 활용해 얻을 수 있다.

sqlite> EXPLAIN QUERY PLAN

…>    SELECT * FROM info WHERE name LIKE ‘D____’;

SCAN TABLE info (~500000 rows)

sqlite> EXPLAIN QUERY PLAN

…>     SELECT * FROM info WHERE name > ‘D’ AND name <‘E’ AND length(name) = 5;

SEARCH TABLE info USING INDEX idx_name (name>? AND name<?) (~55000 rows)

-

GLOB 연산자를 쓰는 경우에는 LIKE 연산자와 달리 인덱스를 활용할 수 있는 경우에는 인덱스를 사용한다.

sqlite> EXPLAIN QUERY PLAN

…>    SELECT * FROM info WHERE name GLOB ’T*’;

SEARCH TABLE info USING INDEX idx_name (name>? AND name<?) (~55000 rows)

그러나 다음과 같이 검색 문자열의 앞에 * 가 붙은 경우에는 컬럼에 인덱스가 설정돼 있더라도 검색에는 사용할 수 없으므로 테이블 스캔을 수행한다.

sqlite> EXPLAIN QUERY PLAN

…>    SELECT * FROM info WHERE name GLOB ‘*om’;

SCAN TABLE info (~500000 rows)

-

인덱스를 사용하는 것이 “무조건” 질의 성능을 향상시키는 것은 아니다.

예를 들어 name 컬럼에 ‘D’ 로 시작되는 문자열이 많은 비중을 차지하는 경우에 ‘D' 로 시작하는 값을 가진 row 를 찾을 때는, B-Tree 와 같은 인덱스를 사용하면 성능 저하가 발생한다.

대부분의 대용량 DBMS 의 경우에는 히스토그램 등의 통계 정보로 대략적인 선택(Selectivity)을 추측하지만 SQLite3 에서는 아직까지 이러한 기능을 지원하지 않는다.

따라서 DB 를 설계할 때는 실제 DB 에서 데이터 값이 어떻게 입력되는지, 어떠한 질의문이 주로 수행되는지 등을 고려해야 한다.

다중 컬럼 인덱스의 활용

-

sqlite> EXPLAIN QUERY PLAN

…>    SELECT * FROM info WHERE age = 31 AND name = ‘Derick’;

SEARCH TABLE info USING INDEX idx_name (name=?) (~2 rows)

위의 경우는 name 에만 index 가 있는 경우로, Derick 을 검색할 때 index 를 사용하고 그 후 age 값이 31 인 row 를 찾는다.

-

아래도 name 에만 index 가 있는 경우인데, OR 연산으로 두 조건 중 하나라도 만족하는 레코드를 모두 조회하거나 index 가 없는 column 을 범위조건으로 검색하는 경우에도 인덱스를 이용하지 못하고 테이블을 순차 검색한다.

sqlite> EXPLAIN QUERY PLAN

…>    SELECT * FROM info WHERE age > 20 OR name = ‘Derick’;

SCAN TABLE info (~500000 rows)

-

위와 같은 경우 age 컬럼과 name 컬럼을 묶어서 하나의 다중 컬럼 인덱스로 구성해서 질의 성능을 향상시킬 수 있다.

다중 컬럼 인덱스(Multi column index)는 같은 테이블에 존재하는 두 개 이상의 컬럼을 묶어서 구성된 인덱스로, 검색 조건이 둘 이상으로 복잡한 경우 다중 컬럼 인덱스를 이용하면 매우 빠르게 조회할 수 있다.

sqlite> CREATE INDEX idx_age_name ON info (age ASC, name DESC);

sqlite> EXPLAIN QUERY PLAN

…>    SELECT * FROM info WHERE age = 31 AND name = ‘Derick’;

SEARCH TABLE info USING COVERING INDEX idx_age_name (age=? AND name=?) (~9 rows)

인덱스가 age, name 컬럼 순으로 정렬됐기 때문에 age 만으로 검색을 해도 해당 index 를 사용할 수 있다.

-

다중 컬럼 인덱스를 생성할 때 CREATE INDEX 구문의 각 컬럼 순서는 사용할 SELECT 문에 따라 신중하게 작성해야 한다.

다중 컬럼 인덱스가 생성될 때는 컬럼의 순서대로 정렬되기 때문이다.

인덱스와 집합 연산

두 결과집합 간의 합집합(UNION), 교집합(INTERSECTION), 차집합(EXCEPT) 연산을 수행하는 경우에도 인덱스를 이용해 검색 성능을 향상시킬 수 있다.

sqlite> EXPLAIN QUERY PLAN

…>    SELECT * FROM info1

…>    UNION

…>    SELECT * FROM info2

…>    ORDER BY name;

1|0|0|SCAN TABLE info USING INDEX idx_name (~100000 rows)

2|0|0|SCAN TABLE info2 USING INDEX idx_name2 (~100000 rows)

0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION)

ORDER BY 를 사용하지 않는 경우에는 index 를 사용하지 않는다.

1|0|0|SCAN TABLE info (~100000 rows)

2|0|0|SCAN TABLE info2 (~100000 rows)

0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)

-

EXPLAIN 만 사용하면 SQLite3 내부의 VDBE ( Virtual Database Engine) 에서 사용하는 Virtual Machine 명령어가 실행된 순서대로 반환된다.

2. 데이터베이스 파일 크기 최적화

-

SQLite3 을 비롯해 대부분의 DB 에서는 많은 레코드를 입력한 후, 입력한 레코드 전부를 삭제하더라도 해당 DB 의 물리적인 파일 크기는 줄어들지 않는다.

이는 SQLite3 라이브러리의 내부 동작 방식 때문이다.

SQLite3 lib 은 디스크 공간을 페이지(page)라는 논리적인 공간으로 나눠서 관리한다.

이 때 레코드가 삭제되어 해당 페이지 공간이 더는 사용되지 않더라도 이를 DB 파일에서 제거하지 않고 내부적으로 빈 페이지(free page)라고 표시해두기만 한다.

그러다 나중에 새로운 레코드가 입력되어 새로운 페이지 공간이 필요해지면 DB 파일의 크기를 늘리는 대신 앞서 빈 공간으로 표시해둔 페이지를 사용한다.

이러한 내부 동작방식으로 인해 DB 레코드가 입력되고 삭제될수록 저장된 레코드의 총 크기보다 DB 파일의 크기가 큰 현상이 발생한다.

-

디스크 공간을 페이지라는 논리적인 블록으로 나눠서 관리하기 때문에 각 블록들은 디스크 공간의 여기저기에 흩어져서 저장될 수 있다. 이러한 현상을 DB 단편화(fragmentation)이라고 하며, 단편화가 심할수록 질의 성능에 나쁜 영향을 끼친다.

한 레코드가 여러 페이지에 임의로 흩어져 있으면 각 페이지의 위치를 찾아서 SQLite3 내부 버퍼에 로딩해야 한다.

디스크 I/O 연산은 메모리 내 연산에 비해 상대적으로 시간이 많이 걸리므로 단편화가 심할수록 시간이 오래 걸린다.

단편화 문제를 해결하는 방법은 입력된 레코드를 바탕으로 새롭게 DB 를 생성해서 페이지가 디스크 공간에 순차적으로 저장되게 하는 것이다.

이와 같은 일을 하는 명령어는 VACUUM 이며, 이 명령어를 실행하면 전체 DB 를 재구축(rebuild)한다.

일반 SQL 구문과 동일하게 VACUUM 명령어도 SQLite3 커맨드 도구나 sqlite3_exec() API 등을 이용해서 수행할 수 있다.

-

VACUMM 명령어를 수행하면 물리적 DB 파일 크기가 줄어든다. 그러나 레코드가 하나도 없어도 테이블 스키마 정보나, SQLite3 lib 의 내부적으로 사용하는 자료가 있어 파일 사이즈는 0 이 아니다.

-

VACUUM 구문의 내부 동작 방식은 임시 DB 파일을 생성해서 원본 DB 파일에서 레코드를 복사하고, 이 임시 DB 파일을 원본 이름으로 덮어쓰는 것이다.

따라서 VACUUM 구문을 수행하려면 원본 DB 파일의 2배 크기에 달하는 여유 디스크 공간이 있어야 한다.

또한 ATTACH 로 연결한 DB 에 대해서는 수행할 수 없고, 메인으로 연결된 DB 파일이더라도 트랜잭션이 진행 중이거나 다른 SQL 구문이 수행 중일 때는 VACUUM 구문이 실패한다.

테이블의 기본키가 INTEGER PRIMARY KEY 로 설정되지 않은 경우 VACUUM 구문을 수행하면 SQLite3 에서 내부적으로 사용되는 기본키인 ROWID 가 변경될 수 있다.

-

PRAGMA 구문을 이용하면 SQLite3 lib 의 기본 설정을 변경할 수 있다.

그 중에서 auto_vacuum 모드를 FULL 로 설정하면 DB 에 데이터가 삭제된 이후 빈 페이지(free page)는 자동으로 디스크 공간으로 반환된다.

그 결과 VACUUM 구문을 명시적으로 수행하지 않고도 물리적인 DB 파일의 크기가 줄어드는 효과를 얻을 수 있다.

-

VACUUM 구문을 사용하면 내부의 빈 공간을 정리하는 작업을 수행하기 때문에 DB 에 입력되고 삭제된 레코드에 따라 다르지만 대체로 많은 시간이 걸린다.

또한 다른 연결에서는 해당 DB 파일의 잠금을 획득할 수 없기 때문에 수정이 불가능하다.

앱의 응답성과 같은 요소를 고려해서 사용해야 한다.

3. 페이지 크기 최적화

-

대부분의 경우 SQLite3 의 기본 설정대로 사용해도 DB 가 동작하는 데는 아무런 문제가 없다.

하지만 프로젝트의 마무리 단계에서 성능 저하와 같은 해결하기 힘든 문제가 발생할 때 반드시 확인해야 하는 부분이 페이지 크기 설정이다.

운영체제의 블록 크기

대부분의 운영체제에서는 운영체제 내부의 버퍼(buffer) 공간에서 디스크로 데이터를 읽고 쓸 때 블록(block)이라는 논리적인 단위로 데이터를 읽고 쓴다.

SQLite3 의 페이지 크기가 OS 의 블록 크기보다 작은 경우 SQLite3 의 쓰기 연산이 OS 에 요청되면 상대적으로 작은 페이지 크기의 데이터를 기록하는 데도 OS 의 블록 크기만큼 OS 의 내부 메모리 공간을 읽고 쓰게 된다.

따라서 결과적으로 디스크 I/O 연산이 비효율적으로 동작한다.

반대로 SQLite3 의 페이지 크기가 OS 의 블록 크기보다 큰 경우에도 하나의 페이지를 읽기 위해 OS 는 페이지 크기만큼의 더 많은 I/O 연산을 수행해야만 한다.

레코드 크기

DB 에 입력된 한 레코드의 크기가 너무 커서 하나의 페이지 공간에 단독으로 저장할 수 없는 경우 SQLite3 에서는 내부적으로 해당 페이지를 확장해서 오버플로우 페이지(overflow page)에 저장한다.

이러한 동작은 SQLite3 lib 에서 자체적으로 수행되는 것으로 개발자는 이러한 동작 방식에 특별히 신경을 쓰지 않아도 된다.

그러나 이 레코드를 조회하기 위해서는 SQLite3의 일반적인 페이지 크기보다 큰 오버플로우 페이지를 메모리 공간으로 읽어들여야만 한다.

이런 이유로 SQLite3 에서 오버플로우 페이지가 사용되는 경우에 성능 저하가 일어난다.

따라서 DB 를 설계할 때 테이블에 입력되는 레코드 크기를 미리 예측해서 SQLite3 의 페이지 크기를 예측한 것보다 크게 설정하는 편이 좋다.

최적의 페이지 크기 산출 방법

-

SQLite3 의 페이지 크기는 기본적으로 OS 파일 시스템 블록 크기와 동일하게 설정한다.

입력되는 레코드가 너무 커서 오버플로우 페이지가 생성될 것으로 예상되면 입력되는 레코드에 맞춰 페이지 크기를 크게 설정한다. 이 때 페이지 크기는 블록 크기의 배수가 되도록 설정해야 한다.

-

SQLite3 의 페이지 크기는 트랜잭션, 동기화와도 연관이 있으므로 이러한 점도 페이지 크기를 결정할 때 고려해야 한다.

일반적인 임베디드 시스템에서는 OS 의 블록크기, 레코드 크기만 고려해도 충분하지만, 훨씬 더 깊은 튜닝이 필요한 경우 관련 정보를 더 찾아서 튜닝할 소지는 많다.

SQLite3 페이지 크기 조회 및 설정

-

PRAGMA page_size 구문을 실행하면 페이지 크기를 조회할 수 있다.

PRAGME page_size=<pageSize> 를 하면 페이지 크기를 설정할 수 있다.

-

페이지 크기 설정은 DB 파일이 생성되기 전에만 할 수 있고 이미 생성된 DB 파일에 대해서는 수정이 불가능하므로 CREATE TABLE 구문을 실행하기 전에 해야 한다.

또한 설정 가능 값은 512 에서 65536 사이의 2의 제곱값만 가능하다.

-

SQLite3 에서 확장 기능을 위해 사용하는 PRAGMA 구문은 그 특성상 정상적이지 않거나 인자 값이 올바르지 않은 경우에도 에러가 발생하지 않고 해당 값이 그대로 유지된다.

그러므로 설정을 변경한 경우에는 변경된 내용이 정상적으로 적용됐는지 반드시 확인해야 한다.

4. DB 파일 검사

-

극히 드물지만 DB 파일 자체가 깨지는 경우(DB File Corruption)가 있다.

DB 파일의 일부가 삭제되거나 SQLite3 lib 이 해당 파일을 분석할 수 없는 상태를 의미한다.

이런 경우에는 SQLITE_CORRUPT 와 같은 에러가 반환되거나 최악의 경우 앱 자체가 비정상적으로 종료되기도 한다.

-

DB 파일이 깨지는 원인은 매우 다양하다.

다른 프로세스가 DB 파일의 일부를 삭제하거나 덮어쓰는 경우에는 당연히 파일 손상이 발생한다.

또한 특정 OS 의 경우 DB 의 쓰기 연산에 문제가 있는 경우에도 발생할 수 있다.

예를 들어 파일 시스템에서 내부적인 버퍼 캐시(buffer cache)에 있는 내용을 물리적인 디스크에 기록하는 시스템 콜(리눅스의 fsync(), 윈도우의 FlushFileBuffer()) 이 정상적으로 동작하지 않는 경우가 여기에 해당한다.

그 밖에도 OS 가 비정상적으로 종료된 이후에 OS 가 복원되면서 DB 파일의 쓰기 연산 중 생기는 저널(journal)파일을 필요없는 파일로 여기고 삭제하거나 다른 내용으로 덮어쓰는 경우에도 이 같은 현상이 발생할 수 있다.

극히 드문 경우지만 SQLite3 lib 자체 버그에 기인하기도 한다.

예를 들어 저널 파일을 기록하고 sync 시스템 콜을 호출하지 않는 문제가 SQLite3 lib 3.6.23.1 버전에서 긴급히 수정된 경우도 있다.

-

DB 파일이 깨진 상태에서 DB 연산을 수행하면 프로그램 자체가 비정상 종료될 수 있다.

이런 경우를 사전에 예방하고자 한다면 PRAGMA integrity_check 구문을 이용해 전체 DB 파일이 오류가 없이 온전한지 확인할 수 있다.

PRAGMA integrity_check;

PRAGMA integrity_check(integer);

검사하는 항목으로는 파일 내의 존재하지 않는 페이지나 인덱스 위치, 잘못된 형식의 레코드 등이 있으며, 발견된 오류는 문자열로 반환된다.

-

일반적으로 DB 파일을 연(open) 직후 SQL 구문을 수행하기에 앞서 위와 같이 DB 파일이 정상적인지 확인하는 것이 좋다.

하지만 DB 파일이 너무 커서 전체 파일을 검사하는데 시간이 오래 걸릴 경우 최대로 반환할 오류의 개수를 지정해서 검사 속도를 빠르게 할 수 있다.

안드로이드에서도 내부적으로 아래와 같이 DB 파일을 검사한다.

PRAGMA integrity_check(1);

-

PRAGMA quick_check 도 있다.

이것은 인덱스의 내용과 테이블에 저장된 내용을 비교하는 것을 제외하기 때문에 PRAGMA integrity_check 보다는 비교적 빠르게 수행된다.

-

원인이 무엇이든 중요한 정보를 저장한 DB 파일이 깨졌다는 것은 매우 심각한 문제이다.

.dump 명령어를 이용해 SQL 구문의 형태로 DB 내용을 추출할 수 있다.

# sqlite3 corrumpt.db .dump > backup.sql

# sqlite3 recover.db < backup.sql

auto_vacuum, auto_vacuum full, Between, Block, db file corruption, db rebuild, db 단편화, db 백업 복구, db 재구축, db 파일 검사, db 파일 깨지는 경우, db 파일 크기 최적화, dump, except, execute plan, Execution Plan, EXPLAIN QUERY PLAN, fragmentation, free page, glob, glob index search, index scan, integrity_check, intersection, journal 파일 제거, Like, like search 최적화, like 최적화, multi column index, Order by, os block, os 문제, os 비정상 종료, overflow page, Page, power failure, pragma, PRAGMA page_size, PRAGMA quick_check, range search, Scan, scan table, search, search table, sqlite lib 버그, sqlite 버그, sqlite 최적화, sqlite3 최적화, sqlite3 페이지 크기 조회 및 설정, sqlite3_exec vacuum, sqlite_corrupt, table scan, temp index, temporary index, union, vacuum, vacuum primary key, vacuum rowid, vdbe, virtual database engine, 교집합, 다중 컬럼 인덱스 활용, 대소문자 구분, 레코드 크기, 메모리 사용량, 범위 검색, 범위 검색 index, 블록 크기, 비정상 종료, 실행 계획 확인, 실행 계획을 통한 질의 최적화, 실행계획, 오브플로우 페이지, 운영체제의 블록 크기, 인덱스 스캔, 질의 성능, 차집합, 최적의 페이지 크기 산출 방법, 테이블 스캔, 파일 크기, 페이지 크기 제한, 페이지 크기 최적화, 합집합