ANALYZE와 PRAGMA optimize

Planner가 더 잘 고르게 도와주기

SQLite의 query planner는 어떤 인덱스를 쓸지 정할 때 sqlite_stat1에 쌓인 통계를 봐. 통계가 없으면 그럭저럭 합리적인 기본값으로 움직이는데, 통계가 있으면 데이터가 한쪽으로 쏠려 있을 때 훨씬 나은 plan을 골라.

알아둘 명령은 둘이야.

ANALYZE — DB 전체나 테이블 하나의 통계를 모아. 데이터가 크게 바뀐 뒤에 돌리거나 배포 단계에 끼워 넣어.
PRAGMA optimize — 요즘 방식이고 가벼워. SQLite가 어느 테이블을 다시 분석해야 하는지 추적해뒀다가 필요한 것만 돌려. connection을 닫을 때마다 불러도 부담이 없어.

Tip: 요즘 권장하는 패턴은 간단해. connection을 닫을 때마다 PRAGMA optimize를 불러. 뭘 해야 하는지는 SQLite가 알아서 판단하고, 할 게 없으면 거의 공짜야. 오래 떠 있는 앱에서 점검 시간을 따로 잡지 않고도 통계를 최신으로 유지할 수 있어.

Code

통계를 최신으로 유지하는 3 패턴·sql

-- 큰 import 후 one-shot
ANALYZE;

-- 단일 테이블
ANALYZE messages;

-- 추천: connection close 시 PRAGMA optimize
-- (마지막 analyze 이후 충분히 안 바뀐 테이블엔 아무 것도 안 함)
PRAGMA optimize;

Python — connection 닫을 때 자동으로 걸어두기·python

import sqlite3, atexit

conn = sqlite3.connect('demo.db')
conn.execute('PRAGMA journal_mode = WAL')

def on_close():
    try:
        conn.execute('PRAGMA optimize')
    finally:
        conn.close()

atexit.register(on_close)

Exercise

row가 10만 개 넘는 DB에서 인덱스를 여러 개 쓸 수 있는 query에 EXPLAIN을 걸어봐. ANALYZE를 돌린 다음 다시 EXPLAIN을 걸어서 planner가 다른 인덱스를 골랐는지 적어. 그다음 오래 떠 있는 앱 패턴을 붙여봐. connection을 닫을 때마다 PRAGMA optimize를 부르고, 시간이 지나면서 통계가 어떻게 유지되는지 관찰해.

Planner가 더 잘 고르게 도와주기

Code

External links

Exercise

Progress

댓글 0