소개
이 랩에서는 SQLite 데이터베이스의 최적 성능 유지를 위한 방법을 배우게 됩니다. 공간을 확보하기 위한 VACUUM 명령 사용, 쿼리 속도를 높이기 위한 인덱스 재구축, 그리고 SQLite 가 더 나은 결정을 내릴 수 있도록 테이블 통계를 분석하는 등 필수적인 기술들을 다룰 것입니다. 시작해 봅시다!
데이터베이스 및 샘플 테이블 생성
유지 관리에 들어가기 전에, SQLite 데이터베이스와 일부 데이터가 있는 샘플 테이블을 만들어 보겠습니다. 이를 통해 작업할 대상을 갖게 됩니다.
먼저, LabEx VM 에서 터미널을 엽니다. 기본 경로는 /home/labex/project입니다.
mydb.db라는 SQLite 데이터베이스를 생성하려면 다음 명령을 실행하십시오.
sqlite3 mydb.db이 명령은 데이터베이스 파일을 생성하고 (존재하지 않는 경우) SQLite 명령줄 도구를 열어 데이터베이스에 연결합니다. sqlite> 프롬프트를 보게 됩니다.
이제 사용자 정보를 저장할 users라는 테이블을 만들어 보겠습니다. 다음 SQL 명령을 실행합니다.
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);이 명령은 세 개의 열이 있는 테이블을 생성합니다: id (각 사용자를 고유하게 식별하는 정수), name (사용자 이름), 그리고 email (사용자 이메일 주소). PRIMARY KEY 제약 조건은 각 id가 고유하도록 보장합니다.
다음으로, users 테이블에 몇 가지 샘플 데이터를 삽입해 보겠습니다.
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');
INSERT INTO users (name, email) VALUES ('Charlie', 'charlie@example.com');이 명령은 users 테이블에 세 개의 행을 추가합니다.
테이블과 데이터가 성공적으로 생성되었는지 확인하려면 다음 명령을 실행하십시오.
SELECT * FROM users;예상 출력:
1|Alice|alice@example.com
2|Bob|bob@example.com
3|Charlie|charlie@example.com이 출력은 users 테이블의 내용을 보여줍니다.
데이터 삭제 시뮬레이션
VACUUM의 효과를 보여주기 위해 데이터 삭제를 시뮬레이션해야 합니다. 이는 데이터베이스 파일의 단편화를 초래할 수 있습니다.
users 테이블에서 행을 삭제해 보겠습니다.
DELETE FROM users WHERE id = 2;이 명령은 id가 2 인 행 (Bob 의 레코드) 을 제거합니다.
삭제를 확인하려면 다음 명령을 실행하십시오.
SELECT * FROM users;예상 출력:
1|Alice|alice@example.com
3|Charlie|charlie@example.comBob 의 레코드가 더 이상 테이블에 없음을 알 수 있습니다. 이 삭제는 데이터베이스 파일에 갭을 남기며, VACUUM이 이를 해결하는 데 도움이 될 수 있습니다.
VACUUM 실행하여 공간 확보
이제 VACUUM 명령을 사용하여 데이터베이스 파일을 조각 모음하고 삭제된 레코드로 인해 남겨진 공간을 회수해 보겠습니다.
SQLite 셸 내에서 다음 명령을 실행합니다.
VACUUM;이 명령은 전체 데이터베이스 파일을 다시 작성하여 데이터를 통합하고 빈 공간을 제거합니다.
VACUUM을 실행한 후에는 특별한 출력을 볼 수 없지만, 데이터베이스를 최적화하기 위해 백그라운드에서 작동하고 있습니다.
SQLite 셸을 종료하려면 다음을 실행합니다.
.exit이제 터미널로 돌아왔습니다.
인덱스 생성
인덱스는 쿼리 속도를 높이는 데 매우 중요합니다. users 테이블의 email 열에 인덱스를 생성해 보겠습니다.
먼저, SQLite 데이터베이스에 다시 연결합니다.
sqlite3 mydb.db이제 다음 명령을 사용하여 인덱스를 생성합니다.
CREATE INDEX idx_users_email ON users (email);이 명령은 email 열에 idx_users_email이라는 인덱스를 생성합니다. SQLite 는 이 인덱스를 사용하여 이메일 주소를 기반으로 사용자를 빠르게 찾습니다.
인덱스 재구축
시간이 지남에 따라 인덱스는 조각화될 수 있으며, 특히 많은 데이터 수정 후에 더욱 그렇습니다. 인덱스를 재구축하면 효율성을 향상시킬 수 있습니다.
방금 생성한 인덱스를 재구축하려면 REINDEX 명령을 사용합니다.
REINDEX idx_users_email;이 명령은 idx_users_email 인덱스를 재구축하여 현재 데이터에 최적화되도록 합니다.
SQLite 셸을 종료하려면 다음을 실행합니다.
.exit이제 터미널로 돌아왔습니다.
테이블 통계 분석
SQLite 는 쿼리를 최적화하기 위해 데이터에 대한 통계를 사용합니다. 이러한 통계를 주기적으로 업데이트하는 것이 좋습니다.
먼저, SQLite 데이터베이스에 다시 연결합니다.
sqlite3 mydb.dbusers 테이블을 분석하려면 다음 명령을 실행합니다.
ANALYZE users;이 명령은 users 테이블에 대한 통계를 수집하며, 쿼리 옵티마이저 (query optimizer) 는 이를 사용하여 쿼리 성능을 향상시킬 수 있습니다.
마지막으로, SQLite 셸을 종료합니다.
.exit이제 터미널로 돌아왔습니다.
요약
이 랩에서는 SQLite 데이터베이스에서 필수 유지 관리 작업을 수행하는 방법을 배웠습니다. VACUUM 명령을 사용하여 공간을 확보하고, 쿼리 성능을 향상시키기 위해 인덱스를 생성하고 재구축했으며, SQLite 가 쿼리를 최적화하도록 돕기 위해 테이블 통계를 분석했습니다. 이러한 기술은 데이터베이스를 원활하고 효율적으로 실행하는 데 매우 중요합니다.
