[파이썬] SQLite 데이터베이스 성능 향상

01 Sep 2023

python

SQLite는 경량 데이터베이스 엔진으로 널리 사용되는 오픈 소스 데이터베이스입니다. 파이썬에서도 SQLite를 사용하여 간단하게 데이터를 저장하고 관리할 수 있습니다. 그러나 대량 데이터 처리 시 성능 저하가 발생할 수 있으며, 이를 최적화하기 위한 몇 가지 방법을 알아보겠습니다.

1. 적절한 인덱싱

인덱스는 데이터베이스에서 검색 성능을 향상시키는 중요한 요소입니다. SQLite에서도 인덱스를 활용하여 데이터 검색을 최적화할 수 있습니다. 특히 자주 검색되는 열에 인덱스를 차례로 추가하면 성능 향상에 크게 기여할 수 있습니다.

예시 코드:

import sqlite3

# 테이블 생성 쿼리
create_table_query = '''
CREATE TABLE IF NOT EXISTS my_table (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER
);
'''
# 인덱스 생성 쿼리
create_index_query = '''
CREATE INDEX IF NOT EXISTS idx_name ON my_table (name);
'''

# 데이터베이스 연결
conn = sqlite3.connect('my_database.db')

# 쿼리 실행
conn.execute(create_table_query)
conn.execute(create_index_query)

# 데이터베이스 연결 종료
conn.close()

2. 트랜잭션 사용

SQLite의 기본 동작은 ‘자동 커밋(auto-commit)’입니다. 이는 데이터 작업마다 즉시 디스크에 반영되어 I/O 부하를 증가시킬 수 있습니다. 대량 데이터 작업 시 트랜잭션을 명시적으로 제어하여 한 번에 커밋하는 것이 성능을 향상시키는데 도움이 됩니다.

예시 코드:

import sqlite3

# 데이터베이스 연결
conn = sqlite3.connect('my_database.db')

# 트랜잭션 시작
conn.execute('BEGIN TRANSACTION')

# 대량 데이터 작업
conn.execute('INSERT INTO my_table (name, age) VALUES ("John Doe", 25)')
conn.execute('INSERT INTO my_table (name, age) VALUES ("Jane Smith", 30)')
# ...

# 트랜잭션 커밋
conn.execute('COMMIT')

# 데이터베이스 연결 종료
conn.close()

3. 파라미터화된 쿼리 사용

SQLite에서는 파라미터화된 쿼리를 사용하는 것이 좋습니다. 이는 쿼리에 동적으로 값을 전달할 수 있으며 SQL 인젝션 공격을 방지하는 데 도움이 됩니다.

예시 코드:

import sqlite3

# 데이터베이스 연결
conn = sqlite3.connect('my_database.db')

# 파라미터화된 쿼리 실행
conn.execute('INSERT INTO my_table (name, age) VALUES (?, ?)', ("John Doe", 25))

# 데이터베이스 연결 종료
conn.close()

4. 캐시 크기 조정

SQLite는 기본 캐시 크기가 작게 설정되어 있습니다. 대량 데이터 처리 시 메모리 사용량이 증가하여 성능 저하가 발생할 수 있습니다. 따라서 적절한 캐시 크기를 설정하여 성능을 향상시킬 수 있습니다.

예시 코드:

import sqlite3

# 데이터베이스 연결
conn = sqlite3.connect('my_database.db')

# 캐시 크기 설정
conn.execute('PRAGMA cache_size = 10000')

# 데이터베이스 연결 종료
conn.close()

위의 방법들을 사용하여 SQLite 데이터베이스의 성능을 향상시킬 수 있습니다. 항상 데이터베이스 사용 시 적절한 인덱싱, 트랜잭션 사용, 파라미터화된 쿼리 및 캐시 크기 조정을 고려해야 합니다.