데이터베이스 인덱스와 페이지네이션 최적화

CREATE TABLE orders ( order_id BIGINT PRIMARY KEY, -- 클러스터 인덱스 user_id BIGINT, product_name VARCHAR(255), amount INT, created_at DATETIME );

[Root Node] ↓ [Branch Nodes: order_id 범위] ↓ [Leaf Nodes] order_id=1 → [전체 행: 1, 123, '상품A', 10000, '2025-01-01'] order_id=2 → [전체 행: 2, 456, '상품B', 20000, '2025-01-02'] order_id=3 → [전체 행: 3, 123, '상품C', 30000, '2025-01-03']

-- 세컨더리 인덱스 생성 CREATE INDEX idx_user ON orders(user_id); CREATE INDEX idx_created ON orders(created_at);

[Secondary Index: idx_user] ↓ [Leaf Nodes] user_id=123 → order_id=1 ─┐ user_id=123 → order_id=3 ─┤ user_id=456 → order_id=2 │ ↓ [Clustered Index로 이동] order_id=1 → 전체 데이터 조회 order_id=3 → 전체 데이터 조회

SELECT * FROM orders WHERE user_id = 123; -- 실행 단계: -- 1. idx_user 스캔 → order_id=1, order_id=3 획득 -- 2. 클러스터 인덱스에서 order_id=1 조회 → 전체 데이터 -- 3. 클러스터 인덱스에서 order_id=3 조회 → 전체 데이터

SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20 OFFSET 10000;

-- 일반 세컨더리 인덱스 CREATE INDEX idx_user ON orders(user_id); SELECT * FROM orders WHERE user_id = 123; -- 2단계 탐색 필요 (인덱스 → 테이블) -- 커버링 인덱스로 설계 CREATE INDEX idx_user_created_id ON orders(user_id, created_at, order_id); SELECT user_id, created_at, order_id FROM orders WHERE user_id = 123 ORDER BY created_at DESC; -- 1단계만 (인덱스 스캔으로 완료) - 커버링 인덱스!

[Covering Index: idx_user_created_id] ↓ [Leaf Nodes에 필요한 모든 데이터 포함] (user_id=123, created_at='2025-01-03', order_id=3) (user_id=123, created_at='2025-01-01', order_id=1) ↓ 테이블 접근 없이 바로 결과 반환!

-- 느린 쿼리 SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20 OFFSET 10000;

CREATE INDEX idx_user_created_id ON orders(user_id, created_at DESC, order_id);

SELECT o.* FROM orders o INNER JOIN ( -- 이 서브쿼리가 커버링 인덱스로 동작 SELECT order_id FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20 OFFSET 10000 ) sub ON o.order_id = sub.order_id ORDER BY o.created_at DESC;

EXPLAIN SELECT o.* FROM orders o INNER JOIN ( SELECT order_id FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20 OFFSET 10000 ) sub ON o.order_id = sub.order_id;

-- 기존 방식 SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20 OFFSET 100; -- 120개 읽고 100개 버림 -- No Offset 방식 SELECT * FROM orders WHERE user_id = 123 AND created_at < '2025-01-05 10:00:00' -- 이전 페이지의 마지막 값 ORDER BY created_at DESC LIMIT 20; -- 20개만 읽음!

SELECT * FROM orders WHERE user_id = 123 AND (created_at < '2025-01-05' OR (created_at = '2025-01-05' AND order_id < 1000)) ORDER BY created_at DESC, order_id DESC LIMIT 20;

CREATE INDEX idx_user_created_id ON orders(user_id, created_at DESC, order_id DESC);

@Service public class OrderService { // 첫 페이지 public PageResponse getFirstPage(Long userId, int size) { List<Order> orders = orderRepository .findByUserIdOrderByCreatedAtDesc(userId, PageRequest.of(0, size)); LocalDateTime lastCreatedAt = orders.isEmpty() ? null : orders.get(orders.size() - 1).getCreatedAt(); return new PageResponse(orders, lastCreatedAt, null); } // 다음 페이지 (No Offset) public PageResponse getNextPage(Long userId, LocalDateTime lastCreatedAt, int size) { List<Order> orders = orderRepository .findByUserIdAndCreatedAtLessThanOrderByCreatedAtDesc( userId, lastCreatedAt, PageRequest.of(0, size)); LocalDateTime newLastCreatedAt = orders.isEmpty() ? null : orders.get(orders.size() - 1).getCreatedAt(); return new PageResponse(orders, newLastCreatedAt, lastCreatedAt); } } // Repository public interface OrderRepository extends JpaRepository<Order, Long> { @Query(value = """ SELECT o.* FROM orders o INNER JOIN ( SELECT order_id FROM orders WHERE user_id = :userId AND created_at < :lastCreatedAt ORDER BY created_at DESC LIMIT :size ) sub ON o.order_id = sub.order_id ORDER BY o.created_at DESC """, nativeQuery = true) List<Order> findNextPageOptimized( @Param("userId") Long userId, @Param("lastCreatedAt") LocalDateTime lastCreatedAt, @Param("size") int size); }

CREATE TABLE orders ( order_id BIGINT AUTO_INCREMENT, user_id BIGINT, created_at DATETIME, amount INT, PRIMARY KEY (order_id, created_at), -- 파티션 키 포함 필수 INDEX idx_user_created_id (user_id, created_at DESC, order_id) ) PARTITION BY RANGE (YEAR(created_at)) ( PARTITION p2023 VALUES LESS THAN (2024), PARTITION p2024 VALUES LESS THAN (2025), PARTITION p2025 VALUES LESS THAN (2026), PARTITION p2026 VALUES LESS THAN (2027) );

SELECT o.* FROM orders o INNER JOIN ( SELECT order_id FROM orders WHERE user_id = 123 AND created_at >= '2025-01-01' -- 파티션 p2025만 스캔 AND created_at < '2026-01-01' ORDER BY created_at DESC LIMIT 20 OFFSET 5000 ) sub ON o.order_id = sub.order_id ORDER BY o.created_at DESC;

-- 처음 1000페이지까지만 허용 SELECT o.* FROM orders o INNER JOIN ( SELECT order_id FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20 OFFSET :offset ) sub ON o.order_id = sub.order_id WHERE :offset < 20000 -- 1000페이지 * 20개 ORDER BY o.created_at DESC;

EXPLAIN SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20 OFFSET 10000;

CREATE INDEX idx_user_created_id ON orders(user_id, created_at DESC, order_id);

SELECT o.* FROM orders o INNER JOIN ( SELECT order_id FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20 OFFSET 10000 ) sub ON o.order_id = sub.order_id;