게시판 조회가 느릴 때: SQLite 실행 계획으로 인덱스 효과 확인하기

👤 인스피

조회 수 11

2. 실행 계획과 실행 시간은 다릅니다

EXPLAIN QUERY PLAN은 SQLite가 조회를 처리할 경로를 설명합니다. SCAN은 전체 스캔을, SEARCH는 조건을 이용한 탐색을 나타내는 표현으로 볼 수 있습니다. 다만 표현 문자열과 출력 형식은 버전에 따라 달라질 수 있습니다. 문자열을 고정해 프로그램 로직을 만드는 것보다 진단 자료로 사용하는 편이 적합합니다.

실행 계획이 바뀌었다고 무조건 체감 속도가 몇 배 좋아졌다고 말할 수는 없습니다. 데이터 규모, 캐시 상태, 저장 장치, 동시 요청이 다릅니다. 이번 실습에서는 실제로 확인한 계획 변화와 결과 일치만 기록합니다. 시간 수치를 주장하려면 같은 조건에서 반복 측정한 방법과 편차도 함께 제시해야 합니다.

3. 조회 조건에 맞는 복합 인덱스를 생각합니다

예제는 category가 it인 행을 찾고 id를 내림차순으로 정렬한 뒤 10개를 가져옵니다. category와 id를 포함한 복합 인덱스가 이 조회에 어떤 영향을 주는지 확인합니다. 인덱스 설계는 열 이름만 보고 하는 것이 아니라 자주 사용하는 WHERE와 ORDER BY를 함께 살펴보는 작업입니다.

실제 게시판이 created_at으로 정렬하거나 예약 발행, 공지, 공개 범위를 필터링한다면 쿼리가 달라집니다. 예제의 인덱스를 그 사이트에 바로 추가하지 마세요. 기존 인덱스와 조회 조건을 확인하고 후보 하나를 검증용 환경에서 비교합니다. 비슷한 이름의 중복 인덱스를 여러 개 만드는 것은 관리 비용을 늘릴 수 있습니다.

4. 결과를 먼저 고정한 뒤 계획을 비교합니다

아래 코드는 정해진 규칙으로 1만 개의 행을 삽입합니다. 매 열 번째 행만 it로 지정해 결과를 재현하기 쉽게 만들었습니다. 인덱스를 추가하기 전 결과를 보관하고, 추가 후 같은 결과인지 assert로 확인합니다. 이 검사는 빠른 조회가 올바른 조회이기도 한지 확인하는 최소 절차입니다.

조회 값을 SQL 문자열에 직접 연결하지 않고 매개변수로 전달합니다. 예제에서 입력은 고정돼 있지만 실제 검색 조건에서도 같은 습관을 유지하는 편이 좋습니다. in-memory 데이터베이스는 프로그램이 끝나면 사라집니다. 로컬의 실제 DB 파일을 실습 대상과 혼동하지 않도록 실행 전에 연결 문자열을 확인하세요.

5. 인덱스를 늘리면 생기는 비용도 있습니다

인덱스는 읽기 작업을 돕지만 삽입과 수정 때도 관리해야 합니다. 글을 자주 수집하는 사이트에서는 쓰기량과 인덱스 저장 공간을 함께 고려해야 합니다. 조회가 느리다는 이유만으로 모든 열에 인덱스를 추가하면 쓰기와 유지 관리에 부담이 생길 수 있습니다.

특히 본문 검색, 일부 문자열 검색, 다른 정렬 기준은 같은 인덱스의 효과를 기대하기 어렵습니다. 함수 적용이나 조건 형태도 중요합니다. 이번 예제의 category 동등 조건과 일반 제목 검색은 다른 문제입니다. 목록 조회를 개선한 결과를 전체 검색 성능 개선으로 확대해 설명하지 마세요.

6. 운영에 적용하기 전에 확인할 항목

실제 조회 SQL과 실행 계획을 확보하고, 자주 사용하는 조건과 느린 조건을 따로 비교합니다. 데이터가 적은 개발 환경에서만 시험하면 운영 규모의 비용을 놓칠 수 있습니다. 개인정보를 제거한 대표 데이터나 별도 시험 환경을 사용하고 결과 행의 순서와 공개 범위도 확인하세요.

변경은 기존 인덱스 목록과 되돌릴 방법을 기록한 뒤 진행합니다. 사용 중인 DB 제품에 따라 인덱스 생성 중의 잠금이나 부하가 다를 수 있습니다. SQLite 실습만으로 그 운영 영향을 판단할 수는 없습니다. 앱 내부 캐시와 DB 인덱스의 효과도 구분해 측정해야 실제 병목이 남아 있는지 알 수 있습니다.

7. 조회 조건을 바꾸어 한계를 확인하기

예제에서 반환 열을 id 대신 title로 바꾸고 계획이 어떻게 달라지는지 살펴볼 수 있습니다. 인덱스가 탐색에 사용돼도 필요한 데이터를 얻는 경로는 달라질 수 있습니다. 이 변화는 결과가 틀렸다는 뜻이 아니라 비용을 읽을 항목이 달라졌다는 뜻입니다. 원본 예제의 covering 결과를 모든 반환 열에 확대하지 마세요.

카테고리가 free인 경우도 비교합니다. 데이터 분포가 다르면 선택한 조건이 전체 행의 어느 정도를 포함하는지 달라집니다. 같은 SQL이라고 모든 값에서 비용이 같다고 보기 어렵습니다. 운영 환경에서는 방문자가 자주 선택하는 게시판과 드문 게시판을 구분해 점검하면 개선의 효과를 더 현실적으로 이해할 수 있습니다.

페이지 번호가 커질 때의 목록 조회도 별도 주제입니다. 이번 SQL은 최신 10개만 가져오며 아주 깊은 OFFSET 조회를 시험하지 않습니다. 최신 글 조회가 개선됐다는 결과를 오래된 모든 목록의 속도 개선으로 설명하지 마세요. 실제 서비스가 사용하는 페이징 방식과 허용 범위를 함께 확인해야 합니다. 검색과 정렬 옵션도 같은 이유로 구분합니다.

측정 기록에는 SQLite 버전, 행 수, 데이터 생성 규칙, 조회 조건과 반환 열을 적습니다. 적용 전후의 계획과 결과 개수도 저장하세요. 실행 시간 측정을 추가한다면 준비와 삽입 시간을 제외하고 조회 구간을 반복해서 측정해야 합니다. 결과를 한 번 읽은 뒤의 캐시 효과도 고려하고 측정 환경을 공개하면 다른 사람이 비교하기 쉬워집니다.

인덱스 후보를 운영에 제안할 때는 읽기 개선뿐 아니라 글 삽입과 수정 빈도를 적습니다. 데이터베이스에 가해지는 실제 부하를 알지 못하면 단정적인 비용 절감을 주장하지 않습니다. 이번처럼 작게 재현한 사례는 가설을 확인하는 출발점이며 운영 변경의 최종 검증은 아닙니다. 결과의 범위를 정확하게 전달하는 편이 유지 관리에 도움이 됩니다.

8. 따라 하는 예제

명령과 코드는 학습용입니다. 설명에 표시된 실행 환경을 구분하고 새 시험 폴더 또는 모의 입력으로 시작하세요.

예제 코드

import sqlite3
con = sqlite3.connect(':memory:')
con.execute('CREATE TABLE posts(id INTEGER PRIMARY KEY, category TEXT, title TEXT)')
con.executemany('INSERT INTO posts(category,title) VALUES (?,?)',
    [('it' if i % 10 == 0 else 'free', f'post {i}') for i in range(10000)])
sql = 'SELECT id FROM posts WHERE category=? ORDER BY id DESC LIMIT 10'
args = ('it',)
print('before:', con.execute('EXPLAIN QUERY PLAN ' + sql, args).fetchall())
expected = con.execute(sql, args).fetchall()
con.execute('CREATE INDEX idx_posts_category_id ON posts(category,id)')
print('after:', con.execute('EXPLAIN QUERY PLAN ' + sql, args).fetchall())
assert con.execute(sql, args).fetchall() == expected
con.close()

9. 검증 결과와 해석

확인 항목	결과 또는 검증 범위
행 수	10,000
적용 전	SCAN posts
적용 후	SEARCH posts USING COVERING INDEX idx_posts_category_id (category=?)
조회 결과	적용 전후 동일
속도 배수	측정·주장하지 않음

Python 3.12 내장 SQLite로 실행 계획과 결과 일치를 확인했습니다. 운영 DB와 동시 접속 상황의 응답 시간을 측정한 결과는 아닙니다.