정렬과 실행계획
75분 안팎
학습 목표
안정적인 페이지 정렬과 인덱스의 조회·쓰기 비용을 설명합니다.
개념
왜 목록에 순서 계약이 필요한가요
동아리 도서가 늘어나 한 화면에 두 권씩 보여 줍니다. SELECT가 행을 반환했다고 기본키 순서로 나왔다고 가정하면 페이지가 바뀔 때 결과가 달라질 수 있습니다. API와 SQL 테스트 모두 원하는 순서를 명시해야 합니다. 이번 목록 계약은 title 오름차순 뒤 id 오름차순이며 페이지 크기는 2, OFFSET은 1입니다. 제목이 같은 실물도 빠짐없이 나타나야 합니다.
앞 레슨에서 제목은 유일키가 아니라고 정했습니다. ORDER BY title만으로는 같은 제목 사이의 순서를 완전히 정하지 못합니다. 마지막 정렬 키에 유일한 id를 넣어 같은 데이터 상태에서 순서가 하나로 정해지도록 만듭니다. 이것이 안정적인 정렬이며 여러 페이지 요청 사이에 데이터가 바뀌어도 같은 스냅샷을 보장한다는 뜻은 아닙니다.
LIMIT과 OFFSET을 결과 위치로 읽습니다
LIMIT 2 OFFSET 1은 정렬 결과의 첫 한 행을 건너뛰고 최대 두 행을 반환합니다. OFFSET은 도서 ID가 아니라 결과 행의 개수입니다. ID 7·10·11이 있을 때 OFFSET 1을 ID 1부터 조회한다는 의미로 해석하지 않습니다. 회원이 없는 경우와 마찬가지로 결과가 부족하면 있는 행만 나오고 가짜 빈 행을 추가하지 않습니다.
사용자 페이지 번호를 1부터 받는 API라면 OFFSET=(page-1)*size로 계산할 수 있지만 아직 그 API는 구현하지 않습니다. 음수 page나 지나친 size 입력 검증은 후속 HTTP 계층에서 다룰 일입니다. 브라우저 실습에서는 크기와 OFFSET을 고정해 SQL 정렬에 집중합니다. 독립 테스트에는 빈 목록과 한 권만 있는 목록도 포함합니다.
순서가 고정되어도 변경 중 페이지는 흔들립니다
첫 페이지를 읽은 뒤 앞쪽 제목의 새 책이 추가되면 다음 OFFSET 페이지가 이전에 본 행을 다시 포함할 수 있습니다. 삭제되면 한 행을 건너뛸 수도 있습니다. title,id 정렬은 동률의 임의 순서를 없애지만 페이지 사이의 삽입·삭제를 막지 않습니다. 같은 스냅샷이나 커서 조건 등의 정책을 별도로 정해야 한다는 점을 설명합니다.
나중에 큰 목록에서는 마지막 title,id 이후를 조회하는 키셋 방식도 검토할 수 있습니다. 그 경우에도 두 정렬 키를 모두 비교하고 동일 제목 경계를 고려합니다. OFFSET이 커지면 앞 결과를 처리하고 버리는 비용이 생길 수 있으므로 목록 크기와 요구를 보고 선택합니다. 이번 단계는 고정 데이터에서 정확한 두 행을 얻는 것을 먼저 검증합니다.
인덱스는 별도 접근 경로입니다
books(title,id) 인덱스는 제목과 같은 제목 내부 ID 순서의 접근 경로 후보입니다. 제목 등호 필터와 이 정렬을 쓰는 조회에 도움이 될 수 있습니다. 인덱스를 만들었다고 SQL의 ORDER BY를 생략하지 않습니다. 인덱스는 실행 방법이고 정렬은 결과 계약입니다. DB가 다른 경로를 고르더라도 SQL 의미는 유지되어야 합니다.
인덱스를 추가하면 저장 공간을 쓰고 INSERT·UPDATE·DELETE 시 유지 작업이 필요합니다. 모든 컬럼에 인덱스를 만들면 항상 성능이 좋아진다고 설명하지 않습니다. 실제 조회 조건·정렬·선택 컬럼과 쓰기 빈도를 기준으로 후보를 정합니다. PK·UNIQUE를 위한 인덱스와 조회용 추가 인덱스의 역할도 구별합니다. 외래키가 있다고 원하는 모든 조회 인덱스가 자동으로 준비되는 것은 아닙니다.
실행계획을 엔진별로 관찰합니다
SQLite 따라하기에서는 EXPLAIN QUERY PLAN을 실행하고 detail의 SCAN·SEARCH·인덱스 이름·정렬용 임시 B-tree 여부를 읽습니다. 출력은 엔진 버전과 쿼리에 따라 달라질 수 있습니다. 세 행에서도 인덱스 접근이 선택될 수 있지만 그것만으로 시간 단축을 증명하지 않습니다. 페이지 SELECT의 실제 결과가 인덱스 전후 같은지도 확인합니다.
미션 H2에서는 EXPLAIN SELECT를 사용하고 target/plan-evidence.txt에 전후 계획을 저장합니다. H2 2.1.214와 books 3행이라는 규모, 시간 미측정이라는 제한을 파일에 기록합니다. 테스트는 IDX_BOOKS_TITLE_ID가 계획에 나타나는지와 결과 보존을 확인합니다. 이를 운영 DB나 다른 엔진의 성능 결과로 일반화하지 않습니다.
측정 근거를 결과 정확성과 분리합니다
계획은 DB가 선택한 접근 방법의 설명이며 이 실습에서 실제 응답시간을 측정한 값은 아닙니다. 성능을 주장하려면 데이터량·분포·쿼리·반복 횟수·캐시·쓰기 조건을 함께 기록해야 합니다. 세 행 조회의 짧은 시간을 재서 큰 시스템도 같은 배수로 빨라진다고 쓰지 않습니다. 현재 제출물은 인덱스 전후 접근 경로 비교와 결과 동일성입니다.
작은 테이블에서는 전체 스캔을 선택해도 그것만으로 오류가 아닙니다. 인덱스가 있는지, 실제 쿼리가 무엇인지, 필터가 얼마나 많은 행을 고르는지 순서대로 확인합니다. 결과 행 수와 읽을 행의 추정량도 같은 숫자는 아닙니다. EXPLAIN 문구를 외우는 대신 계획에서 선택한 인덱스가 내가 만든 후보인지 확인하는 연습을 합니다.
브라우저 실습의 경계를 검증합니다
제공되는 books(id,title)는 같은 제목을 허용합니다. SELECT id,title FROM books ORDER BY title,id LIMIT 2 OFFSET 1을 완성합니다. 브라우저 SQLite의 기본 문자열 정렬을 사용하며 자연스러운 한국어 사전식 정렬 정책을 구현하는 과제는 아닙니다. 테스트는 영문 제목·동률·띄엄띄엄 ID·빈 목록·한 권·두 권을 써서 위치와 키를 구분하게 합니다.
starter는 ID로만 정렬하므로 제목 순서가 ID 순서와 다를 때 틀립니다. 출력이 우연히 맞는 첫 입력 하나로 완료하지 않습니다. 동률 제목에 ID를 빠뜨려도 해당 입력의 삽입 순서 때문에 맞아 보일 수 있으므로 SQL에 유일한 마지막 정렬 키가 있는지 함께 리뷰합니다. 행을 두 번 출력하거나 ID를 1부터 새로 매기면 원래 도서 식별을 깨뜨립니다.
오류 메시지와 제출 증거를 읽습니다
no such index는 실제 인덱스 이름이나 실행한 DB가 다른지 확인할 단서입니다. CREATE INDEX에서 already exists가 나면 새 실습 DB에서 다시 실행하거나 반복 실행 정책을 정합니다. LIMIT 주변 문법 오류가 나면 이 레슨은 SQLite 문법인지 H2 문법인지 먼저 확인합니다. 다른 DB의 페이징 구문을 그대로 옮기지 않습니다.
완료 제출에는 page.sql과 index.sql, 두 페이지의 기대 행, 인덱스 전후 계획과 규모·제한을 남깁니다. 미션 테스트에서 실패가 남으면 원래 API 테스트가 아니라 새로운 SqlModelTest의 메서드 이름을 먼저 봅니다. stablePage는 정렬 계약, explainEvidencePreservesResults는 인덱스 생성과 결과 보존을 검증합니다. 더 읽기에서는 다양한 실행계획을 다루고 여기서는 정확한 목록과 과장 없는 관찰 기록을 완성합니다.
미션 엔진 구문 확인: H2 명령 공식 문서의 EXPLAIN을 참고합니다.
따라하기
유일한 마지막 정렬 키를 둡니다
새 SQLite 콘솔 또는 브라우저 SQL 실습을 초기화하고 아래 초기화 SQL부터 실행합니다.
PRAGMA foreign_keys=ON;
CREATE TABLE books(id INTEGER PRIMARY KEY CHECK(id>0),title TEXT NOT NULL CHECK(length(trim(title))>0));
CREATE TABLE members(id INTEGER PRIMARY KEY,name TEXT NOT NULL,email TEXT NOT NULL UNIQUE);
CREATE TABLE loans(id INTEGER PRIMARY KEY,book_id INTEGER NOT NULL REFERENCES books(id),member_id INTEGER NOT NULL REFERENCES members(id),loaned_at TEXT NOT NULL,returned_at TEXT CHECK(returned_at IS NULL OR returned_at>=loaned_at));
INSERT INTO books VALUES(7,'SQL'),(10,'Java'),(11,'Java');
INSERT INTO members VALUES(1,'민지','m@example.test'),(2,'준','j@example.test'),(3,'민지','n@example.test');
INSERT INTO loans VALUES(1,7,1,'2026-10-01T10:00:00',NULL),(2,10,2,'2026-09-01T10:00:00','2026-09-02T10:00:00');
Java 두 권의 ID 순서를 확인합니다.
SELECT id,title FROM books ORDER BY title,id;실행 결과
10 Java 11 Java 7 SQL
위치 기반 두 행 페이지를 읽습니다
새 SQLite 콘솔 또는 브라우저 SQL 실습을 초기화하고 아래 초기화 SQL부터 실행합니다.
PRAGMA foreign_keys=ON;
CREATE TABLE books(id INTEGER PRIMARY KEY CHECK(id>0),title TEXT NOT NULL CHECK(length(trim(title))>0));
CREATE TABLE members(id INTEGER PRIMARY KEY,name TEXT NOT NULL,email TEXT NOT NULL UNIQUE);
CREATE TABLE loans(id INTEGER PRIMARY KEY,book_id INTEGER NOT NULL REFERENCES books(id),member_id INTEGER NOT NULL REFERENCES members(id),loaned_at TEXT NOT NULL,returned_at TEXT CHECK(returned_at IS NULL OR returned_at>=loaned_at));
INSERT INTO books VALUES(7,'SQL'),(10,'Java'),(11,'Java');
INSERT INTO members VALUES(1,'민지','m@example.test'),(2,'준','j@example.test'),(3,'민지','n@example.test');
INSERT INTO loans VALUES(1,7,1,'2026-10-01T10:00:00',NULL),(2,10,2,'2026-09-01T10:00:00','2026-09-02T10:00:00');
ID 1부터가 아니라 정렬 결과 첫 행을 건너뜁니다.
SELECT id,title FROM books ORDER BY title,id LIMIT 2 OFFSET 1;실행 결과
11 Java 7 SQL
인덱스 전후 계획과 결과를 봅니다
새 SQLite 콘솔 또는 브라우저 SQL 실습을 초기화하고 아래 초기화 SQL부터 실행합니다.
PRAGMA foreign_keys=ON;
CREATE TABLE books(id INTEGER PRIMARY KEY CHECK(id>0),title TEXT NOT NULL CHECK(length(trim(title))>0));
CREATE TABLE members(id INTEGER PRIMARY KEY,name TEXT NOT NULL,email TEXT NOT NULL UNIQUE);
CREATE TABLE loans(id INTEGER PRIMARY KEY,book_id INTEGER NOT NULL REFERENCES books(id),member_id INTEGER NOT NULL REFERENCES members(id),loaned_at TEXT NOT NULL,returned_at TEXT CHECK(returned_at IS NULL OR returned_at>=loaned_at));
INSERT INTO books VALUES(7,'SQL'),(10,'Java'),(11,'Java');
INSERT INTO members VALUES(1,'민지','m@example.test'),(2,'준','j@example.test'),(3,'민지','n@example.test');
INSERT INTO loans VALUES(1,7,1,'2026-10-01T10:00:00',NULL),(2,10,2,'2026-09-01T10:00:00','2026-09-02T10:00:00');
이 출력은 작성 시 SQLite 실행 결과입니다. 계획 문구는 버전에 따라 다를 수 있으며 속도 향상을 측정하지 않았습니다.
EXPLAIN QUERY PLAN SELECT id,title FROM books WHERE title='Java' ORDER BY title,id;
CREATE INDEX idx_books_title_id ON books(title,id);
EXPLAIN QUERY PLAN SELECT id,title FROM books WHERE title='Java' ORDER BY title,id;
SELECT id,title FROM books WHERE title='Java' ORDER BY title,id;실행 결과
3 0 0 SCAN books 3 0 0 SEARCH books USING COVERING INDEX idx_books_title_id (title=?) 10 Java 11 Java
뒤 페이지의 빈 결과를 확인합니다
새 SQLite 콘솔 또는 브라우저 SQL 실습을 초기화하고 아래 초기화 SQL부터 실행합니다.
PRAGMA foreign_keys=ON;
CREATE TABLE books(id INTEGER PRIMARY KEY CHECK(id>0),title TEXT NOT NULL CHECK(length(trim(title))>0));
CREATE TABLE members(id INTEGER PRIMARY KEY,name TEXT NOT NULL,email TEXT NOT NULL UNIQUE);
CREATE TABLE loans(id INTEGER PRIMARY KEY,book_id INTEGER NOT NULL REFERENCES books(id),member_id INTEGER NOT NULL REFERENCES members(id),loaned_at TEXT NOT NULL,returned_at TEXT CHECK(returned_at IS NULL OR returned_at>=loaned_at));
INSERT INTO books VALUES(7,'SQL'),(10,'Java'),(11,'Java');
INSERT INTO members VALUES(1,'민지','m@example.test'),(2,'준','j@example.test'),(3,'민지','n@example.test');
INSERT INTO loans VALUES(1,7,1,'2026-10-01T10:00:00',NULL),(2,10,2,'2026-09-01T10:00:00','2026-09-02T10:00:00');
위치를 넘으면 빈 결과이며 오류나 NULL 한 행이 아닙니다.
SELECT id,title FROM books ORDER BY title,id LIMIT 2 OFFSET 99;확인 문제
실습
books(id,title)를 title 오름차순, 동률은 id 오름차순으로 정렬하고 첫 한 행을 건너뛴 최대 두 행을 출력합니다. 출력은 id,title입니다. ID가 연속이라는 가정을 하지 않으며 빈 목록·한 권이면 결과가 비어야 합니다. 인덱스는 별도 미션에서 계획을 확인합니다.
모범 답안
SELECT id,title FROM books ORDER BY title,id LIMIT 2 OFFSET 1;
더 읽기
면접 질문
- 도서와 대여 기록의 테이블 관계를 설명합니다.