[DB-005][기초] 인덱스로 필요한 행을 빠르게 찾고 실행 계획 읽기 > IT 기술 공유

• 목록

[이번 수업]
인덱스 전후 실행 계획을 비교해 전체 표를 읽는 SCAN이 필요한 일부 행만 찾는 SEARCH로 바뀌는 과정을 확인합니다.

[선수지식]
DB-001~004의 표·행·열·기본 키, SELECT, WHERE를 알고 SQLite 명령줄 도구를 사용할 수 있으면 됩니다.

[학습목표]
1. 인덱스와 실행 계획을 쉬운 말로 설명한다.
2. EXPLAIN QUERY PLAN에서 SCAN과 SEARCH를 구분한다.
3. 자주 쓰는 조건에 맞는 복합 인덱스를 만들고 비용도 판단한다.

[핵심개념]
인덱스는 책의 찾아보기처럼 열 값을 정렬해 원본 행을 빨리 찾게 돕는 구조입니다. 없으면 많은 행을 차례로 읽지만, 있으면 정렬된 키에서 범위를 찾은 뒤 원본 행으로 이동합니다. 조회 결과는 같고 찾는 경로만 달라집니다.

실행 계획은 데이터베이스가 SQL을 처리하기 위해 고른 경로입니다. SQLite의 EXPLAIN QUERY PLAN에서 SCAN은 전체 표나 인덱스 항목을 훑는 경우, SEARCH는 조건으로 일부 행만 방문하는 경우를 뜻합니다. 출력 형식은 버전에 따라 달라질 수 있어 프로그램이 이 문자열에 의존하면 안 됩니다.

복합 인덱스는 여러 열을 순서대로 묶습니다. `(customer_id, status)`는 customer_id로 먼저 좁히고 같은 값 안에서 status를 찾는 조건에 잘 맞습니다. 열 순서는 실제 조건과 값 분포를 보고 정합니다. 인덱스는 공간을 쓰고 쓰기 때 갱신되므로 많을수록 좋은 것은 아닙니다. ANALYZE는 표와 인덱스 통계를 모아 쿼리 계획 선택에 활용하게 합니다.

[따라하기]
index-demo.sql 파일을 만들고 아래 내용을 저장합니다.

```sql
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  created_at TEXT NOT NULL
);

WITH RECURSIVE seq(n) AS (
  SELECT 1 UNION ALL
  SELECT n + 1 FROM seq WHERE n < 1000
)
INSERT INTO orders(customer_id, status, created_at)
SELECT n % 100,
      CASE WHEN n % 2 = 0 THEN 'paid' ELSE 'waiting' END,
      '2026-01-01'
FROM seq;

.print BEFORE
EXPLAIN QUERY PLAN
SELECT id, created_at FROM orders
WHERE customer_id = 42 AND status = 'paid';

CREATE INDEX idx_orders_customer_status
ON orders(customer_id, status);
ANALYZE;

.print AFTER
EXPLAIN QUERY PLAN
SELECT id, created_at FROM orders
WHERE customer_id = 42 AND status = 'paid';

.print RESULT_COUNT
SELECT count(*) FROM orders
WHERE customer_id = 42 AND status = 'paid';
```

macOS·Linux 터미널과 Windows 명령 프롬프트에서는 `sqlite3 :memory: < index-demo.sql`을 실행합니다. Windows PowerShell에서는 `Get-Content .\index-demo.sql | sqlite3 :memory:`을 사용합니다. 예상 결과의 BEFORE에는 `SCAN orders`, AFTER에는 `SEARCH orders USING INDEX idx_orders_customer_status`, 마지막에는 10이 표시됩니다. `:memory:` 데이터는 종료하면 사라집니다.

[흔한 실수]
모든 열에 인덱스를 만들거나 작은 표의 SCAN을 문제라고 단정하지 마세요. 함수나 자료형 때문에 인덱스를 못 쓸 수도 있습니다. 복합 인덱스의 열 순서와 실제 조건을 함께 확인하고, 계획뿐 아니라 대표 데이터에서 응답 시간과 쓰기 비용도 측정해야 합니다.

[보안 주의]
실제 운영 데이터 대신 본인 소유의 로컬·격리된 복제본에서 실습하세요. 실행 계획과 로그에 이름·조건값이 드러날 수 있으므로 공개하지 않습니다. SQL 값은 문자열 연결 대신 매개변수로 전달하고 최소 권한 계정을 사용합니다. 다른 데이터베이스의 EXPLAIN ANALYZE 계열 명령은 쿼리를 실제 실행할 수 있으니 공식 문서와 영향 범위를 먼저 확인하세요.

[직접 해볼 과제]
1. 인덱스를 `(status, customer_id)` 순서로 다시 만들고 같은 계획을 비교하세요.
2. status만 조건으로 조회했을 때 두 인덱스 순서 중 어느 쪽이 선택되는지 기록하고 이유를 설명하세요.

[확인문제]
1. SCAN과 SEARCH는 각각 어떤 접근을 뜻하나요?
2. 인덱스를 많이 만들수록 쓰기 작업이 느려질 수 있는 이유는 무엇인가요?
3. 복합 인덱스에서 열 순서를 실제 쿼리와 함께 봐야 하는 이유는 무엇인가요?

[다음 학습]
DB-006에서 여러 작업을 하나로 묶는 트랜잭션과 격리 수준·잠금을 배웁니다.

[공식 참고 자료]
SQLite Query Planning: https://www.sqlite.org/queryplanner.html
SQLite EXPLAIN QUERY PLAN: https://www.sqlite.org/eqp.html
SQLite CREATE INDEX: https://www.sqlite.org/lang_createindex.html
SQLite ANALYZE: https://www.sqlite.org/lang_analyze.html