소개
이 랩에서는 데이터베이스 성능과 안정성을 최적화하기 위해 SQLite PRAGMA 튜닝을 탐구합니다. PRAGMA 문을 사용하여 SQLite 의 주요 동작 방식을 구성하는 방법을 배우게 됩니다. 저널 모드 구성, 외래 키 검사 활성화, 무결성 검사 수행, 캐시 크기 조정 등을 다룰 것입니다.
데이터베이스 생성 및 저널 모드 구성
이 단계에서는 SQLite 데이터베이스를 생성하고 저널 모드를 구성합니다. 저널 모드는 SQLite 가 트랜잭션을 처리하고 데이터 무결성을 보장하는 방식을 제어합니다.
먼저, LabEx VM 에서 터미널을 엽니다. 기본 경로는 /home/labex/project입니다.
test.db라는 SQLite 데이터베이스를 생성하고 다음 명령을 사용하여 SQLite 셸에 들어갑니다.
sqlite3 test.db
이 명령은 데이터베이스 파일 test.db를 생성하고 (존재하지 않는 경우) SQLite 명령줄 도구를 엽니다. 다음과 같은 프롬프트가 표시됩니다.
SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>
이제 저널 모드를 구성해 보겠습니다. SQLite 는 DELETE, TRUNCATE, PERSIST, MEMORY, WAL, OFF를 포함한 여러 저널 모드를 제공합니다. WAL (Write-Ahead Logging) 은 성능과 안정성의 균형을 잘 맞춥니다.
다음 SQL 명령을 실행하여 저널 모드를 WAL로 설정합니다.
PRAGMA journal_mode=WAL;
이 명령은 데이터베이스가 Write-Ahead Logging 을 사용하도록 구성합니다. WAL 은 변경 사항을 데이터베이스에 적용하기 전에 별도의 WAL 파일에 기록하여 동시성과 성능을 향상시킵니다.
저널 모드를 확인하려면 다음을 실행합니다.
PRAGMA journal_mode;
예상 출력:
wal
이는 저널 모드가 WAL로 설정되었음을 확인합니다.
외래 키 지원 활성화
이 단계에서는 SQLite 데이터베이스에서 외래 키 지원을 활성화합니다. 외래 키는 테이블 간의 관계를 적용하고 데이터 무결성을 유지하는 데 도움이 됩니다.
SQLite 셸 내에서 (이전 단계에서 종료했다면 sqlite3 test.db로 다시 연결합니다) 다음 명령을 실행합니다.
PRAGMA foreign_keys = ON;
이 명령은 현재 데이터베이스 연결에 대한 외래 키 적용을 활성화합니다. 데이터베이스에 대한 새로운 연결마다 외래 키를 활성화해야 합니다.
외래 키 지원이 활성화되었는지 확인하려면 다음을 실행합니다.
PRAGMA foreign_keys;
예상 출력:
1
이 출력은 외래 키 지원이 활성화되었음을 확인합니다.
외래 키 관계를 가진 테이블 생성
이제 외래 키 지원이 활성화되었으므로, 기능을 시연하기 위해 외래 키 관계를 가진 두 개의 테이블을 생성해 보겠습니다.
다음 SQL 명령을 실행하여 users 테이블과 orders 테이블을 생성합니다.
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
user_id INTEGER,
amount REAL,
FOREIGN KEY (user_id) REFERENCES users(id)
);
이 명령은 두 개의 테이블을 생성합니다.
users:id(기본 키) 및name열을 사용하여 사용자 정보를 저장합니다.orders:id(기본 키),user_id(users.id를 참조하는 외래 키) 및amount열을 사용하여 주문 정보를 저장합니다.
FOREIGN KEY (user_id) REFERENCES users(id) 절은 orders 테이블과 users 테이블 간의 관계를 설정합니다. 이는 orders 테이블의 user_id가 users 테이블의 id 열에 반드시 존재해야 함을 보장합니다.
외래 키 제약 조건 테스트
이 단계에서는 외래 키 제약 조건을 테스트하여 유효하지 않은 데이터가 데이터베이스에 삽입되는 것을 어떻게 방지하는지 확인합니다.
먼저, users 테이블에 사용자를 삽입합니다.
INSERT INTO users (id, name) VALUES (1, 'Alice');
이 명령은 id 1 과 name 'Alice'를 가진 새로운 사용자를 users 테이블에 삽입합니다.
이제 users 테이블에 존재하지 않는 user_id를 가진 주문을 orders 테이블에 삽입해 봅니다.
INSERT INTO orders (user_id, amount) VALUES (999, 100.0);
외래 키 검사가 활성화되어 있으므로 이 명령은 오류 메시지와 함께 실패합니다.
Error: FOREIGN KEY constraint failed
이는 외래 키 제약 조건이 올바르게 작동하여 존재하지 않는 사용자에 대한 주문을 생성하는 것을 방지함을 보여줍니다.
다음으로, 기존 user_id를 사용하여 유효한 주문을 삽입합니다.
INSERT INTO orders (user_id, amount) VALUES (1, 100.0);
user_id 1 이 users 테이블에 존재하므로 이 명령은 성공합니다.
무결성 검사 수행
이 단계에서는 SQLite 데이터베이스에 대한 무결성 검사 (integrity check) 를 수행하여 불일치 또는 오류가 없는지 확인합니다.
SQLite 셸 내에서 다음 명령을 실행합니다.
PRAGMA integrity_check;
이 명령은 데이터베이스 전체에서 불일치 또는 오류를 검사합니다. 데이터베이스가 정상 상태이면 출력은 ok가 됩니다.
예상 출력:
ok
데이터베이스가 손상된 경우 출력에는 손상의 성격과 위치를 나타내는 오류 메시지가 포함됩니다.
캐시 크기 조정 및 종료
이 마지막 단계에서는 SQLite 에서 사용하는 캐시 크기를 조정하고 SQLite 셸을 종료합니다.
캐시 크기는 SQLite 가 최근에 액세스한 데이터를 저장하는 데 사용하는 메모리 양을 결정합니다. 캐시 크기를 늘리면, 특히 읽기 위주의 작업 부하 (workload) 에 대해 성능을 향상시킬 수 있습니다.
캐시 크기를 4MB (4096 KB) 로 설정하려면 다음 명령을 실행합니다.
PRAGMA cache_size = 4096;
이 명령은 현재 데이터베이스 연결에 대한 캐시 크기를 4MB 로 설정합니다.
캐시 크기를 확인하려면 다음을 실행합니다.
PRAGMA cache_size;
예상 출력:
4096
마지막으로, SQLite 셸을 종료합니다.
.exit
이 명령은 데이터베이스 연결을 닫고 명령줄로 돌아갑니다.
요약
이 랩에서는 SQLite PRAGMA 튜닝을 탐구했습니다. 성능 및 안정성 향상을 위해 저널 모드 (journal mode) 를 구성하고, 데이터 무결성을 적용하기 위해 외래 키 지원 (foreign key support) 을 활성화하고, 외래 키 관계 (foreign key relationships) 로 테이블을 생성하고, 외래 키 제약 조건 (foreign key constraints) 을 테스트하고, 데이터베이스 손상을 식별하기 위해 무결성 검사 (integrity check) 를 수행하고, 메모리 사용을 최적화하기 위해 캐시 크기를 조정하는 방법을 배웠습니다. 이러한 기술은 SQLite 데이터베이스를 효과적으로 사용하는 데 탄탄한 기반을 제공합니다.
