인덱스와 저장 구조 - 빨리 찾는 원리

이 장에서 배우는 것

앞 장에서 여러 사용자의 작업이 겹쳐도 데이터를 일관되게 유지하는 방법을 살펴보았다. 이제 같은 데이터를 얼마나 적은 작업으로 찾아낼 수 있는지 생각해 본다. 온라인 서점에 책이 수백 권 있을 때는 모든 책을 확인해도 부담이 작다. 책이 수십만 권으로 늘어나면 특정 가격대의 책 몇 권을 찾기 위해 전체를 읽는 방식이 부담스러워진다.

인덱스(index)는 데이터와 별도로 검색에 필요한 값을 정리해 두는 구조다. 책 뒤쪽의 찾아보기가 본문을 대신하지 않으면서 원하는 내용을 찾도록 돕는 것과 비슷하다. 다만 데이터베이스의 인덱스는 데이터가 바뀔 때 함께 관리해야 하며, 어떤 질문에나 도움이 되는 것도 아니다. 이 장에서는 저장 단위에서 출발해 인덱스의 구조와 비용을 연결하고, SQLite의 실행계획으로 실제 접근 방식을 확인한다.

• 페이지와 블록이 행을 읽는 비용과 어떻게 연결되는지 설명한다.
• 전체를 확인하는 순차 탐색과 B+ 트리의 탐색 경로를 비교한다.
• 클러스터드 인덱스와 보조 인덱스가 실제 행에 도달하는 방식을 구분한다.
• 인덱스가 유리한 조건과 불리한 조건을 판단한다.
• SQLite의 실행계획에서 SCAN, SEARCH, 임시 정렬의 의미를 읽는다.

문제 상황

SQL 연구소의 온라인 서점에서 운영자가 “가격이 20,000원 이상이고 30,000원 미만인 책을 가격순으로 보여 달라”는 기능을 요청했다고 하자. 필요한 열은 book의 id와 price다. 같은 가격의 책은 id가 작은 순서로 보여 주면 조회할 때마다 순서를 설명하기도 쉽다.

SELECT id, price
FROM book
WHERE price >= 20000
  AND price < 30000
ORDER BY price, id;

이 쿼리의 결과가 맞는 것과 결과를 효율적으로 찾는 것은 다른 문제다. DBMS는 모든 책의 가격을 확인한 뒤 해당하는 행을 정렬할 수도 있고, 가격순으로 정리된 인덱스에서 필요한 범위만 읽을 수도 있다. SQL에는 원하는 결과를 적지만, 그 결과에 이르는 경로는 저장 구조와 데이터 분포에 따라 달라진다.

운영자는 처음에 모든 열에 인덱스를 만들자고 제안할 수 있다. 그러나 책의 가격을 수정할 때마다 관련 인덱스도 고쳐야 한다. 주문 등록처럼 쓰기가 잦은 테이블에서는 인덱스를 늘리는 일이 다른 작업의 부담으로 돌아온다. 이 장의 질문은 “인덱스가 있는가”에서 그치지 않는다. “이 질문에 쓸모 있는 인덱스인가, 읽기에서 얻는 이익이 유지 비용에 맞는가”까지 살펴본다.

행을 담는 페이지와 읽기의 단위

행 하나를 찾더라도 주변 데이터를 함께 읽는다

표에서는 행이 독립된 줄처럼 보이지만, 저장장치에서는 여러 행과 관리 정보가 일정한 크기의 공간에 나뉘어 들어간다. DBMS가 이러한 공간을 관리하는 대표적인 단위가 페이지(page)다. SQLite 데이터베이스 파일도 페이지 단위로 구성된다. 페이지마다 역할이 있으며, 모든 페이지가 사용자 테이블의 행을 담는 것은 아니다.

블록(block)은 저장장치나 파일 시스템의 입출력 단위를 설명할 때 자주 쓰는 말이다. 제품이나 문맥에 따라 데이터베이스의 저장 단위를 블록이라고 부르기도 한다. 따라서 페이지와 블록의 크기가 언제나 같다고 이해하면 안 된다. 여기서는 페이지를 DBMS가 관리하는 단위로, 블록을 그 아래 저장 계층에서도 쓰이는 단위로 구분한다.

다음 명령으로 현재 SQLite 데이터베이스의 페이지 크기를 바이트 단위로 확인할 수 있다. PRAGMA는 SQLite 전용 명령이다.

PRAGMA page_size;

예를 들어 페이지 크기가 4,096바이트라면 그 공간에는 행뿐 아니라 페이지를 관리하는 정보도 들어간다. 행의 길이는 서로 다를 수 있고, 긴 값은 추가 페이지를 사용할 수 있다. 따라서 “페이지 크기를 행 크기로 나누면 저장 행 수가 정확히 나온다”는 계산은 실제 저장 형식을 지나치게 단순화한 것이다.

DBMS는 읽은 페이지를 메모리에 보관해 다시 사용할 수 있다. 이 때문에 논리적으로 같은 페이지를 읽는 작업도 저장장치에서 새로 가져오는 경우와 메모리에 남아 있는 경우의 시간이 다르다. 쿼리를 두 번 실행했을 때 두 번째가 빨라졌다는 사실만으로 인덱스의 효과를 입증할 수는 없다.

순차 탐색은 언제 합리적인가

순차 탐색은 대상 테이블의 행을 차례로 확인하는 방법이다. 전체 테이블을 대상으로 하면 전체 테이블 탐색이라고 한다. 조건에 맞는 행이 드물어도 끝까지 확인해야 할 수 있다는 점이 부담이지만, 많은 행을 읽을 때는 단순하고 효율적인 방법이 될 수 있다. 여기서 차례로 읽는다는 말은 SQL 결과의 순서를 보장한다는 뜻이 아니다. 결과 순서는 ORDER BY로 지정한다.

예를 들어 book의 거의 모든 행을 반환하는 조회라면 인덱스에서 위치를 찾은 다음 테이블을 반복해서 읽는 것보다 테이블을 한 번 훑는 편이 나을 수 있다. 또한 논리적으로 순서 있게 읽는 페이지가 저장장치에서도 모두 붙어 있다는 보장은 없다. 저장 구조를 이해할 때는 행의 순서, 페이지의 배치, 결과의 출력 순서를 나누어 생각해야 한다.

B+ 트리와 인덱스의 저장 방식

범위를 나누고 필요한 경로로 내려간다

B+ 트리(B+ tree)는 정렬된 키를 이용해 검색 범위를 단계적으로 좁히는 구조다. 최상단의 루트에서 시작해 내부 노드의 경계값을 비교하고, 데이터 항목이 있는 리프에 도달한다. 노드는 트리를 구성하는 한 단위이며, 저장장치용 트리에서는 한 노드를 페이지에 대응시키는 방식으로 이해할 수 있다.

한 노드에는 키 하나만 들어가는 것이 아니라 여러 키와 다음 위치를 가리키는 정보가 들어간다. 따라서 한 단계에서 많은 갈래로 나뉘며, 데이터가 늘어나도 트리의 높이를 비교적 낮게 유지할 수 있다. 정확히 몇 번 읽어야 하는지는 페이지 크기, 키 길이, 데이터 양, 메모리 보관 상태 등에 따라 달라진다.

가격 범위를 찾는다면 먼저 시작 가격이 들어갈 리프를 찾는다. 그다음 리프에 정렬된 항목을 읽으며 상한에 도달할 때까지 진행한다. B+ 트리는 리프끼리 연결되어 있어 범위를 이어 읽기 좋다. 다만 시작점을 빨리 찾더라도 조건에 맞는 항목이 많으면 그 항목들을 읽는 작업은 여전히 필요하다.

그림의 숫자는 원리를 설명하기 위해 만든 가상 가격이며, 실제 온라인 서점의 저장 상태가 아니다. 또한 이 그림을 SQLite 파일의 정확한 내부 도면으로 읽어서는 안 된다. SQLite는 테이블과 인덱스를 B-트리 계열 구조로 저장하며, 일반적인 rowid 테이블의 행은 테이블 트리의 리프에 저장된다. 별도의 인덱스 트리는 내부 페이지에도 키를 저장할 수 있어, 모든 검색 항목을 리프에 두는 교과서적인 B+ 트리와 세부 구조가 다르다.

클러스터드 인덱스와 보조 인덱스

클러스터드 인덱스(clustered index)는 테이블의 실제 행이 특정 키를 기준으로 조직되는 저장 방식과 연결되는 개념이다. 보조 인덱스(secondary index)는 별도 구조에 검색 키와 실제 행을 찾는 데 필요한 정보를 둔다. 보조 인덱스에서 조건을 만족하는 항목을 찾더라도, 필요한 열이 그 안에 없으면 테이블 쪽으로 이동해 행을 읽어야 한다.

클러스터드라는 말이 파일의 모든 바이트가 키순으로 빈틈없이 붙어 있다는 뜻은 아니다. 핵심은 실제 행을 조직하는 기준이다. 제품별로 용어와 구현도 다르다. 테이블의 주된 행 저장 순서를 여러 기준으로 동시에 조직하기는 어렵지만, 별도 보조 인덱스는 여러 개 둘 수 있다.

실제 행과 인덱스 항목의 저장 관계

구분	주로 담는 내용	조회할 때의 특징
클러스터드 방식	키를 기준으로 조직된 실제 행	행에 도달하면 다른 열도 읽을 수 있다.
보조 인덱스	검색 키와 행을 찾는 정보	필요한 열에 따라 테이블을 추가로 읽는다.
SQLite 일반 rowid 테이블	rowid를 기준으로 조직된 행	별도 인덱스에서는 보통 rowid로 행을 찾는다.
SQLite WITHOUT ROWID 테이블	기본 키를 기준으로 조직된 행	보조 인덱스에서 기본 키 정보로 행을 찾는다.

SQLite에는 CREATE CLUSTERED INDEX라는 명령이 없다. 일반 테이블에서 id가 정확히 INTEGER PRIMARY KEY로 선언되면 보통 내부 행 식별자인 rowid의 별칭이 된다. 그러나 열 이름이 id라는 사실만으로 그렇게 판단할 수는 없다. INT PRIMARY KEY 같은 선언은 동일한 의미가 아니다. 다음 조회로 SQL 연구소에 실제로 정의된 book 테이블의 생성문을 확인할 수 있다.

SELECT sql
FROM sqlite_schema
WHERE type = 'table'
  AND name = 'book';

기본 키가 있으면 모든 DBMS에서 같은 방식으로 행이 저장된다고 생각해서는 안 된다. 키가 보장하는 논리적 규칙과 행을 배치하는 물리적 구조는 구분해야 한다.

복합 인덱스의 순서는 질문의 순서와 연결된다

여러 열을 함께 정리한 복합 인덱스는 열을 적은 순서대로 정렬된다. book(price, id) 인덱스는 먼저 price로 항목을 모으고, 같은 price 안에서는 id로 순서를 정한다. 따라서 가격 범위로 좁히면서 price, id 순서로 결과를 만드는 질문에 잘 맞는다.

CREATE INDEX idx_lab12_book_price_id
ON book(price, id);

이 인덱스를 사용하는 조회에서 id와 price만 필요하면 테이블의 나머지 열을 읽지 않아도 된다. 특정 조회에 필요한 정보를 인덱스만으로 얻는 경우를 커버링 인덱스(covering index)라고 부른다. 이는 별개의 인덱스 종류를 만드는 문법이 아니라 인덱스와 조회 사이의 관계다. 같은 인덱스라도 title까지 요구하는 조회에는 필요한 정보가 부족할 수 있다.

반면 id만 조건에 있는 질문은 이 인덱스의 첫 정렬 기준인 price를 건너뛴다. 따라서 price로 먼저 좁히는 질문과 같은 효율을 기대하기 어렵다. DBMS가 통계에 따라 다른 접근법을 선택하는 예외도 있으므로, “첫 열이 없으면 어떤 상황에서도 사용할 수 없다”는 규칙으로 외우기보다 실제 계획을 확인하는 편이 정확하다.

인덱스의 효과를 실행계획으로 확인하기

적은 행을 찾는 것과 적은 작업을 하는 것

인덱스가 유리한 대표적인 상황은 전체 중 적은 부분을 찾아 읽는 경우다. 그러나 결과 행 수만으로 판단할 수는 없다. 조건에 맞는 행을 찾기까지 읽은 항목 수, 테이블의 추가 읽기, 정렬 작업도 비용에 포함된다. 반대로 많은 행을 반환하더라도 필요한 열이 모두 들어 있는 작은 인덱스를 순서대로 읽으면 도움이 될 수 있다.

인덱스의 이익과 비용을 판단하는 질문

상황	기대할 수 있는 효과	확인할 점
좁은 가격 범위 조회	시작점을 찾고 필요한 범위만 읽는다.	실제로 몇 행이 조건을 만족하는가.
인덱스와 같은 순서의 정렬	별도 정렬을 줄일 수 있다.	조건과 정렬 열의 순서가 맞는가.
조회 열이 인덱스 안에 있음	테이블의 추가 읽기를 줄인다.	불필요하게 많은 열을 요청하지 않는가.
테이블 대부분을 반환	전체 탐색이 더 나을 수 있다.	테이블로 반복 이동하는 비용이 큰가.
삽입과 수정이 잦음	읽기는 빨라져도 쓰기 부담이 늘 수 있다.	추가 공간과 유지 비용을 감당할 수 있는가.

인덱스에는 새 행의 항목도 추가해야 한다. 인덱스에 포함된 값이 바뀌면 기존 항목의 위치를 조정해야 할 수 있고, 페이지에 공간이 부족하면 페이지를 나누는 작업도 발생할 수 있다. 인덱스는 저장 공간과 메모리도 사용한다. 조회 하나의 개선만 보고 테이블 전체의 작업량을 잊지 않아야 한다.

SCAN과 SEARCH는 속도 판정표가 아니다

실행계획(execution plan)은 DBMS가 쿼리를 처리하기 위해 선택한 접근 방법을 보여 준다. SQLite에서는 조회 앞에 EXPLAIN QUERY PLAN을 붙인다. 이 명령은 계획을 설명하며, 원래 조회의 결과 행을 반환하지 않는다.

EXPLAIN QUERY PLAN
SELECT id, price
FROM book
WHERE price >= 20000
  AND price < 30000
ORDER BY price, id;

출력에는 계획 항목의 번호, 상위 항목과의 관계, 설명 등이 포함된다. 셸에서는 이를 나무 모양으로 표시할 수 있다. 구체적인 번호와 문구, 선택되는 인덱스는 SQLite 버전과 데이터베이스 상태에 따라 달라질 수 있다. 다음은 설명 열의 행 모양만 보인 예시 결과다. 서로 다른 계획에서 나올 수 있는 표현을 모은 것이며, 한 번의 실행에서 모두 나온다는 뜻은 아니다.

예시 결과: SQLite 실행계획의 설명 열을 읽는 방법

설명 열의 예	읽는 방법
SCAN book	book을 전체 탐색하는 접근이다.
SEARCH book USING COVERING INDEX idx_lab12_book_price_id (price>? AND price<?)	인덱스에서 가격 범위를 좁히며 필요한 열도 얻는다.
SCAN book USING COVERING INDEX idx_lab12_book_price_id	인덱스를 전체 탐색하면서 필요한 열을 얻는다.
USE TEMP B-TREE FOR ORDER BY	정렬을 위해 임시 구조를 사용한다.

SCAN은 인덱스를 전혀 사용하지 않는다는 뜻이 아니다. 인덱스 전체를 순서대로 읽는 계획도 SCAN으로 표시된다. SEARCH는 일부 범위로 접근을 좁힌다는 뜻이지만, 그 범위에 많은 행이 들어 있으면 실제 작업량도 많을 수 있다. 실행계획만으로 경과 시간을 확정해서는 안 된다.

DBMS의 계획 선택에는 데이터 분포에 관한 통계도 영향을 준다. SQLite의 ANALYZE는 이러한 통계를 수집하는 명령이다. 통계를 갱신하면 같은 SQL의 계획이 달라질 수 있다. 다만 이 실습에서는 접근 방식의 차이를 분명히 관찰하기 위해 통계를 변경하지 않고 인덱스 사용 여부를 직접 지정한다.

SQLite 문법과 내부 형식을 더 확인하려면 실행계획 설명 문서, 데이터베이스 파일 형식 문서, 쿼리 계획 설명 문서를 참고할 수 있다. 이 장의 그림과 예제는 학습을 위해 별도로 구성했다.

완성 코드

다음은 Python 3의 표준 sqlite3 모듈로 실행하는 완전한 프로그램이다. 추가 패키지는 필요 없다. SQL 연구소에서는 아래 코드의 SELECT와 EXPLAIN QUERY PLAN을 SQL 입력창에서 실행할 수 있다. 전체 프로그램을 macOS나 Linux에서 실행하려면 온라인 서점 테이블이 들어 있는 로컬 SQLite 데이터베이스 파일이 필요하다. 빈 파일을 새로 만드는 프로그램은 아니다.

프로그램은 실습용 인덱스를 트랜잭션 안에서 만들고 두 조회의 결과를 비교한 뒤 되돌린다. 인덱스 이름이 이미 있으면 기존 객체를 재사용하거나 삭제하지 않고 오류로 종료한다. 앞의 생성 예제를 실행했다면 직접 만든 실습용 인덱스인지 확인하고 정리한 뒤 실행한다. 인덱스 생성에는 데이터베이스 쓰기 권한이 필요하다.

inspect_book_index.py

import sqlite3
import sys
from pathlib import Path


def explain(conn, sql):
    rows = conn.execute("EXPLAIN QUERY PLAN " + sql).fetchall()
    return [row[3] for row in rows]


def main():
    if len(sys.argv) != 2:
        raise SystemExit(
            "사용법: python3 inspect_book_index.py 데이터베이스파일"
        )

    uri = Path(sys.argv[1]).resolve().as_uri() + "?mode=rw"
    conn = sqlite3.connect(uri, uri=True)

    scan_sql = """
        SELECT id, price
        FROM book NOT INDEXED
        WHERE price >= 20000 AND price < 30000
        ORDER BY price, id
    """
    index_sql = """
        SELECT id, price
        FROM book INDEXED BY idx_lab12_book_price_id
        WHERE price >= 20000 AND price < 30000
        ORDER BY price, id
    """

    try:
        conn.execute("BEGIN")
        conn.execute(
            "CREATE INDEX idx_lab12_book_price_id ON book(price, id)"
        )

        scan_plan = explain(conn, scan_sql)
        index_plan = explain(conn, index_sql)

        if not any(item.startswith("SCAN book") for item in scan_plan):
            raise RuntimeError("전체 탐색 계획을 확인하지 못했다.")
        if not any(
            item.startswith("SEARCH book")
            and "idx_lab12_book_price_id" in item
            for item in index_plan
        ):
            raise RuntimeError("가격 범위 탐색 계획을 확인하지 못했다.")

        scan_rows = conn.execute(scan_sql).fetchall()
        index_rows = conn.execute(index_sql).fetchall()
        if scan_rows != index_rows:
            raise RuntimeError("두 조회의 결과가 다르다.")

        print("인덱스 없는 계획: 전체 탐색")
        print("인덱스 지정 계획: 범위 탐색")
        print("조회 결과 비교: 일치")
    finally:
        conn.rollback()
        conn.close()


if __name__ == "__main__":
    main()

NOT INDEXED와 INDEXED BY는 SQLite 전용 구문이다. 전자는 별도 인덱스의 사용을 막으며, rowid를 통한 접근까지 모두 금지하는 것은 아니다. 여기서는 price에 조건을 주므로 전체 탐색을 관찰한다. 후자는 지정한 인덱스를 사용하도록 요구하며, 사용할 수 없으면 오류가 발생한다. 운영 쿼리에 습관적으로 붙이는 구문이 아니라 이번 비교의 조건을 통제하는 장치다.

줄별 해설

1. import 부분은 데이터베이스 연결, 실행 인수 읽기, 파일 경로 처리를 위한 표준 모듈을 가져온다. 외부 라이브러리를 설치하지 않는다.
2. explain 함수는 전달받은 조회 앞에 EXPLAIN QUERY PLAN을 붙인다. 반환 행의 네 번째 값인 row[3]에서 계획 설명을 얻는다. 여기에 넣는 SQL은 프로그램 안에 작성한 고정 문자열이다.
3. main의 인수 검사는 데이터베이스 파일 경로 하나를 받도록 한다. 경로를 빠뜨리면 사용법을 보여 주고 종료한다.
4. as_uri는 파일 경로를 연결용 주소로 바꾼다. mode=rw는 기존 파일을 읽고 쓰도록 하며, 경로를 잘못 입력했을 때 빈 데이터베이스가 만들어지는 일을 막는다.
5. scan_sql과 index_sql은 접근 방식을 지정한 부분만 다르다. 조건과 출력 열, 정렬 순서를 같게 유지해야 접근 방법에 따른 결과 비교가 의미 있다.
6. BEGIN과 CREATE INDEX는 되돌릴 수 있는 실습 범위를 만든다. SQLite에서는 이 트랜잭션 안에서 만든 인덱스도 롤백 대상이 된다.
7. 두 explain 호출은 각각의 접근 방법을 확인한다. 이어지는 조건문은 전체 탐색과 지정한 인덱스의 범위 탐색이 실제로 계획되었는지 검사한다.
8. fetchall은 두 조회의 결과를 모두 가져온다. ORDER BY price, id가 있으므로 같은 가격의 행까지 순서를 맞추어 비교할 수 있다. 이 방식은 작은 실습 데이터용이며 대량 결과를 메모리에 모으는 벤치마크로 쓰지 않는다.
9. 세 print 호출은 데이터의 구체적인 가격이나 행 수와 관계없는 확인 결과를 출력한다. 일치하지 않거나 계획을 확인하지 못하면 이 출력에 도달하기 전에 오류가 발생한다.
10. finally의 rollback과 close는 성공과 실패 어느 경우에도 실행된다. 실습용 인덱스를 되돌린 다음 연결을 닫는다.

계획 설명은 SQLite 버전에 따라 달라질 수 있으므로 이 프로그램의 문자열 검사는 제한된 학습용 확인이다. 실행계획 문구를 장기적으로 고정된 응용 프로그램 인터페이스처럼 사용해서는 안 된다. 또한 이 프로그램은 두 접근의 구조와 결과 일치를 확인하며, 어느 쪽이 몇 배 빠른지는 측정하지 않는다.

실행 결과

코드를 inspect_book_index.py로 저장하고, 온라인 서점 데이터가 들어 있는 파일 이름이 bookstore.db라고 가정하면 다음 명령으로 구문을 검사하고 실행한다. 파일 경로에 공백이 있으면 따옴표로 감싼다. 첫 번째 명령은 정상일 때 아무것도 출력하지 않는다.

python3 -m py_compile inspect_book_index.py
python3 inspect_book_index.py bookstore.db

실습 조건을 만족하는 데이터베이스에서 검사가 통과하면 프로그램의 출력은 다음과 같다. 이는 데이터 조회 결과표가 아니라 코드가 출력하는 확인 메시지다.

인덱스 없는 계획: 전체 탐색
인덱스 지정 계획: 범위 탐색
조회 결과 비교: 일치

조건에 해당하는 책이 없어도 두 조회는 모두 빈 결과를 반환하므로 비교는 일치한다. 실제 행 모양을 확인하려면 문제 상황의 SELECT를 SQL 연구소에서 따로 실행한다. 다음은 값과 행 수를 고정하지 않은 예시 결과다.

예시 결과: 가격 범위 조회가 반환하는 행의 모양

id	price
조건을 만족하는 책의 식별자	20,000 이상 30,000 미만인 가격
다음 책의 식별자	앞 행 이상인 가격, 같으면 id순

프로그램 종료 후에는 실습용 인덱스가 남지 않는다. 프로그램 밖에서 원래 SELECT의 계획을 다시 확인하면 실습 중에 강제한 계획과 달라질 수 있다. 이는 인덱스를 되돌리도록 작성한 코드의 결과다.

실무에서 자주 틀리는 것

조건 열을 계산한 뒤에도 같은 범위 탐색을 기대한다

다음 쿼리는 가격에 1,000을 더한 결과를 비교한다. price에 만든 일반 인덱스로 원래 가격 범위를 바로 찾는 형태가 아니다. 아래 예제는 같은 의미를 원래 열의 비교로 표현할 수 있으므로 조건을 정리한다.

틀린 코드: price 인덱스의 범위 탐색을 기대하면서 열을 계산한다.

SELECT id, price
FROM book
WHERE price + 1000 >= 21000
  AND price + 1000 < 31000;

고친 코드: 같은 가격 범위를 직접 비교한다.

SELECT id, price
FROM book
WHERE price >= 20000
  AND price < 30000;

함수가 있는 조건에서 인덱스를 전혀 쓸 수 없다는 뜻은 아니다. 별도의 표현식 인덱스 같은 방법도 있다. 여기서는 일반적인 열 인덱스와 조건식의 모양을 맞추는 원칙을 익힌다.

필요 없는 열까지 읽고 인덱스만으로 끝나기를 바란다

다음 쿼리도 결과는 올바르다. 그러나 화면에 id와 price만 필요한데 모든 열을 요청하면 title 등 다른 값을 가져오기 위해 테이블을 추가로 읽을 수 있다.

틀린 코드: 두 열만 쓰는 화면에서 모든 열을 요청한다.

SELECT *
FROM book
WHERE price >= 20000
  AND price < 30000
ORDER BY price, id;

고친 코드: 화면에서 실제로 필요한 열만 요청한다.

SELECT id, price
FROM book
WHERE price >= 20000
  AND price < 30000
ORDER BY price, id;

title이 필요한 화면이라면 title을 빼는 것이 해결책은 아니다. 필요한 결과를 먼저 정하고 그 결과를 얻는 비용을 판단해야 한다.

인덱스 사용을 강제하면 항상 유리하다고 생각한다

다음 쿼리는 모든 책의 제목까지 반환하면서 가격 인덱스를 지정한다. 대량의 테이블 추가 읽기가 발생할 수 있으며, 정렬도 요구하지 않아 인덱스를 강제할 근거가 부족하다.

틀린 코드: 비용을 확인하지 않고 특정 인덱스를 고정한다.

SELECT id, title, price
FROM book INDEXED BY idx_lab12_book_price_id;

고친 코드: 접근 방식은 DBMS가 선택하게 한다.

SELECT id, title, price
FROM book;

두 쿼리의 출력 순서는 보장되지 않는다. 순서가 필요한 요구사항이면 ORDER BY를 추가해야 한다. 지정한 인덱스가 없으면 첫 쿼리는 오류도 발생한다.

인덱스 순서를 결과 순서로 오해한다

실행계획에서 가격 인덱스를 사용하는 모습을 보고 정렬 조건을 생략하기 쉽다. 그러나 인덱스가 있거나 지금 그 인덱스를 쓴다는 사실은 앞으로도 같은 출력 순서를 보장하지 않는다.

틀린 코드: 가격순 출력이 필요한데 ORDER BY를 생략한다.

SELECT id, price
FROM book
WHERE price >= 20000
  AND price < 30000;

고친 코드: 필요한 순서를 SQL에 명시한다.

SELECT id, price
FROM book
WHERE price >= 20000
  AND price < 30000
ORDER BY price, id;

정렬 요구를 명시해도 인덱스 순서가 맞으면 DBMS는 별도 정렬을 줄일 수 있다. 요구사항을 생략하는 것과 요구사항을 효율적으로 처리하는 것은 다르다.

한눈에 보기

저장 구조에서 실행계획까지 연결하는 핵심 개념

개념	핵심	확인할 질문
페이지	DBMS가 데이터를 나누어 관리하는 단위다.	필요한 행을 얻기 위해 어느 페이지를 읽는가.
순차 탐색	대상 전체를 차례로 확인한다.	전체 중 많은 부분을 읽는 질문인가.
B+ 트리	경계값으로 내려가 정렬된 리프 범위를 읽는다.	시작점 이후 얼마나 많은 항목을 읽는가.
보조 인덱스	검색 키와 행을 찾는 정보를 별도로 둔다.	테이블을 추가로 읽어야 하는가.
커버링	해당 조회의 정보를 인덱스만으로 얻는다.	선택한 열과 조건에 필요한 열이 모두 있는가.
실행계획	DBMS가 선택한 접근 방법을 보여 준다.	범위 탐색과 정렬이 어떻게 처리되는가.

다음 책에서 이어 갈 공부

이 책에서는 데이터베이스가 정보를 표현하고, 규칙을 지키고, 여러 작업을 처리하며, 필요한 값을 찾는 원리를 연결했다. 다음 책인 《SQL 기초》에서는 원하는 결과를 정확한 SQL로 옮기는 연습을 이어 간다. 같은 요구를 여러 쿼리로 표현하고 결과의 의미를 비교하면서 조회를 작성하는 경험을 쌓는다.

《SQLD》에서는 데이터 모델과 SQL의 개념을 체계적으로 정리하고, 조건에 따라 결과가 어떻게 달라지는지 판단하는 연습을 이어 간다. 이 장에서 익힌 습관도 유효하다. 먼저 질문과 결과를 확인하고, 그다음 DBMS가 실제로 선택한 처리 방법을 살펴본다.

연습 문제

1. 페이지 하나에 여러 행이 저장될 때, 결과가 한 행이라는 사실만으로 저장장치에서 한 행 분량만 읽는다고 말할 수 없는 이유를 설명하라.
2. book(price, id) 인덱스가 있다고 하자. 가격 범위에서 id와 price를 조회하는 경우와 id, price, title을 조회하는 경우의 추가 읽기 가능성을 비교하라.
3. 실행계획에 SCAN book USING COVERING INDEX가 나왔다. “인덱스를 사용하지 않았다”와 “조건으로 좁힌 일부 범위만 탐색했다”라는 설명이 각각 왜 부정확한지 설명하라.

정답과 해설

1. DBMS는 페이지 단위로 데이터를 관리하므로 한 행을 얻는 과정에도 다른 행과 관리 정보가 담긴 페이지를 읽을 수 있다. 해당 페이지가 메모리에 남아 있다면 저장장치 읽기는 생략될 수도 있다. 결과 행 수와 실제 입출력 양은 동일하지 않다.
2. id와 price는 인덱스에 포함되어 있어 조건 확인과 결과 생성을 인덱스 안에서 처리할 수 있다. title은 이 인덱스에 없으므로 해당 인덱스를 이용하는 계획에서는 보통 테이블에서 값을 추가로 읽어야 한다. 추가 읽기의 비용이 커지면 DBMS가 다른 계획을 고를 수도 있다.
3. USING COVERING INDEX는 필요한 정보를 인덱스에서 얻는다는 뜻이므로 인덱스를 사용한다. 그러나 SCAN은 그 인덱스를 전체 탐색하는 접근이며, 조건으로 범위를 좁히는 SEARCH와 다르다. 전체 인덱스 탐색도 정렬이나 테이블 읽기를 줄인다면 합리적일 수 있다.

SQL 연구소 과제의 정답과 해설

과제 A의 예시 답안은 다음과 같다. book을 전체 탐색하는 설명과 ORDER BY를 위한 임시 구조가 나타나는지 확인한다. 실행계획의 번호나 행 수를 외우지 않는다.

EXPLAIN QUERY PLAN
SELECT id, price
FROM book NOT INDEXED
WHERE price >= 20000
  AND price < 30000
ORDER BY price, id;

과제 B는 다음 스크립트로 비교할 수 있다. 별도로 진행 중인 트랜잭션이 없는 상태에서 실행한다. 인덱스 이름이 이미 존재한다면 그 객체의 출처를 먼저 확인한다. 실습 중 생성에 실패했을 때도 열린 트랜잭션은 ROLLBACK으로 끝낸다.

BEGIN;

CREATE INDEX idx_lab12_book_price_id
ON book(price, id);

EXPLAIN QUERY PLAN
SELECT id, price
FROM book INDEXED BY idx_lab12_book_price_id
WHERE price >= 20000
  AND price < 30000
ORDER BY price, id;

EXPLAIN QUERY PLAN
SELECT id, price, title
FROM book INDEXED BY idx_lab12_book_price_id
WHERE price >= 20000
  AND price < 30000
ORDER BY price, id;

ROLLBACK;

첫 조회는 가격 범위를 탐색하면서 커버링이라는 설명을 기대할 수 있다. 두 번째는 title을 얻기 위한 테이블 읽기가 필요하므로 일반적으로 커버링 표시가 사라진다. 두 조회 모두 인덱스의 price, id 순서를 이용할 수 있어 별도 정렬이 필요하지 않다. ROLLBACK은 이 스크립트에서 만든 인덱스를 되돌린다.

SQL 연구소에서 실습하기

SQL 연구소의 온라인 서점 데이터를 선택하고 다음 과제를 수행한다. 정답 SQL과 해설은 바로 앞의 정답과 해설 절에 있다.

• 과제 A. 20,000원 이상 30,000원 미만인 책의 id와 price를 price, id순으로 조회하되 NOT INDEXED를 사용하고, EXPLAIN QUERY PLAN으로 탐색과 정렬 방식을 확인하라.
• 과제 B. 트랜잭션 안에서 book(price, id) 인덱스를 만들고, 해당 인덱스를 지정해 id와 price만 조회할 때와 title까지 조회할 때의 계획을 비교한 뒤 ROLLBACK으로 실습을 마쳐라.