[29일차]SQL 시간 범위 검색, parent_id, 페이지네이션

1. SQL 시간 범위 검색: DATE_ADD, DATE_SUB

데이터베이스에서 특정 기간 내의 데이터를 조회할 때 DATE_ADD와 DATE_SUB 함수는 매우 유용하게 사용된다. 이 함수들은 날짜 및 시간 값에 특정 간격을 더하거나 빼서 동적인 시간 범위를 설정할 수 있게 해준다.

1.1. DATE_ADD(date, INTERVAL expr unit)

DATE_ADD 함수는 지정된 날짜(date)에 특정 기간(INTERVAL expr unit)을 더한 결과를 반환하는 함수이다.

• date: 기준이 되는 날짜 또는 시간 값 (예: '2025-10-20', NOW())
• INTERVAL expr unit: 더하고자 하는 시간 간격. expr은 숫자, unit은 YEAR, MONTH, DAY, HOUR 등의 시간 단위이다.

활용 예시: 현재 시점으로부터 한 달 후까지의 이벤트 목록 조회

-- events 테이블에서 현재부터 한 달 이내에 시작하는 이벤트를 조회한다.
SELECT *
FROM events
WHERE start_date BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 1 MONTH);

1.2. DATE_SUB(date, INTERVAL expr unit)

DATE_SUB 함수는 지정된 날짜(date)에서 특정 기간(INTERVAL expr unit)을 뺀 결과를 반환하는 함수이다.

활용 예시: 최근 7일 동안 작성된 게시물 조회

-- posts 테이블에서 현재 시점을 기준으로 7일 전부터 지금까지 작성된 게시물을 조회한다.
SELECT *
FROM posts
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY);

이처럼 두 함수를 사용하면 '최근 일주일', '지난 한 달' 등과 같이 변하는 시간 범위를 하드코딩 없이 유연하게 쿼리할 수 있어 유지보수성이 향상된다.

---

2. 계층형 데이터 구조 설계: parent_id

2.1. parent_id 개념

parent_id는 자기 참조 외래 키(Self-Referencing Foreign Key)를 활용하여 테이블 내에서 데이터 간의 계층 구조(Hierarchy)를 구현하는 설계 방식이다. 제공된 categories 테이블의 parent_id는 categories 테이블 자신의 id를 참조하도록 설정되어 있다.

CREATE TABLE `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL, -- 이 컬럼이 부모 카테고리의 id를 가리킨다.
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  -- parent_id가 categories 테이블의 id를 참조하는 외래 키 제약 조건
  CONSTRAINT `fk_categories_parent` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE
);

• 최상위 카테고리: 부모가 없는 카테고리는 parent_id를 NULL로 설정한다.
• 하위 카테고리: 특정 카테고리의 하위에 속하는 경우, parent_id에 부모 카테고리의 id 값을 저장한다.

2.2. parent_id 활용 방안

교보문고의 예시처럼 '국내도서', '서양도서'와 같은 대분류와 그 아래 '소설', '경제' 등의 하위 분류를 하나의 categories 테이블로 관리할 수 있다.

데이터 예시:

id	parent_id	name
1	NULL	국내도서
2	NULL	서양도서
3	1	소설
4	1	경제
5	2	Novel
6	3	현대소설

• id=1 '국내도서'는 최상위 카테고리이므로 parent_id가 NULL이다.
• id=3 '소설'은 '국내도서'의 하위 카테고리이므로 parent_id가 1이다.
• id=6 '현대소설'은 '소설'의 하위 카테고리이므로 parent_id가 3이다.

이 구조는 별도의 테이블 없이 무한한 깊이의 카테고리 구조를 표현할 수 있어 확장성이 매우 뛰어나다.

2.3. 인덱스(Index)의 역할과 성능 최적화

KEY `fk_categories_parent` (`parent_id`)

parent_id 컬럼에 인덱스를 추가하면 특정 부모에 속한 모든 자식 카테고리를 검색하는 쿼리의 성능이 극적으로 향상된다.

인덱스가 없을 경우:
WHERE parent_id = 1과 같은 쿼리를 실행하면, 데이터베이스는 categories 테이블의 모든 행을 처음부터 끝까지 스캔(Full Table Scan)하여 parent_id가 1인 데이터를 찾아야 한다. 데이터가 많아질수록 검색 시간은 선형적으로 증가한다.

인덱스가 있을 경우:
인덱스는 책의 '찾아보기'처럼 데이터의 주소를 미리 정렬된 자료구조(주로 B-Tree)로 저장해 둔다. WHERE parent_id = 1 쿼리가 실행되면, 데이터베이스는 전체 테이블을 스캔하는 대신 인덱스를 통해 parent_id가 1인 데이터의 위치를 즉시 찾아낸다. 이로 인해 검색 속도가 대폭 빨라지며, 특히 JOIN이나 WHERE 절에서 해당 컬럼을 자주 사용할 때 큰 효과를 볼 수 있다.

---

3. 대규모 데이터 처리: 페이지네이션(Pagination)

3.1. 페이지네이션의 필요성

페이지네이션(Pagination, 쪽 나누기)은 대량의 데이터를 여러 페이지로 분할하여 보여주는 기법이다. 만약 수십만 건의 도서 데이터를 한 번에 클라이언트에 전송한다면 다음과 같은 문제가 발생한다.

1. 서버 및 데이터베이스 부하: 대용량 데이터를 조회하고 직렬화하는 과정에서 서버의 CPU와 메모리 사용량이 급증한다.
2. 네트워크 지연: 대량의 데이터는 전송 시간이 길어져 사용자가 응답을 받기까지 오래 기다려야 한다.
3. 클라이언트 성능 저하: 브라우저가 대량의 데이터를 렌더링하면서 메모리 부족, UI 버벅임(lag) 현상이 발생하여 사용자 경험(UX)을 심각하게 저해한다.

페이지네이션은 LIMIT과 OFFSET을 사용하여 필요한 만큼의 데이터만 요청하고 응답함으로써 이러한 문제를 해결하는 필수적인 최적화 기법이다.

3.2. 코드 기반 페이지네이션 분석

제공된 searchBooks 코드는 페이지네이션을 효과적으로 구현한 예시이다.

// [수정] 도서 검색 쿼리
exports.searchBooks = async ({
  // page와 limit의 기본값을 설정
  page = DEFAULT_PAGE, // 예: 1
  limit = DEFAULT_LIMIT, // 예: 10
}) => {
  // ... (sql, params 초기화)

  // ... (필터링 로직)

  // 1. OFFSET 계산
  const offset = (page - 1) * limit;

  // 2. LIMIT과 OFFSET을 쿼리에 추가
  sql += ` ORDER BY b.created_at DESC LIMIT ? OFFSET ?`;
  params.push(limit, offset);

  const [books] = await dbPool.query(sql, params);
  return { books };
};

동작 원리:

1. limit: 한 페이지에 보여줄 데이터의 개수를 의미한다. LIMIT 10은 "결과 중에서 10개만 가져오라"는 뜻이다.
2. offset: 조회를 시작할 데이터의 위치를 지정한다. OFFSET 20은 "앞의 20개를 건너뛰고 그 다음부터 가져오라"는 뜻이다.
3. offset 계산: offset = (page - 1) * limit 공식은 페이지 번호에 따라 건너뛸 데이터의 수를 계산한다.
   • 1페이지 요청 (page=1): offset = (1 - 1) * 10 = 0. 처음부터 10개의 데이터를 가져온다. (1~10번째)
   • 2페이지 요청 (page=2): offset = (2 - 1) * 10 = 10. 10개를 건너뛰고 10개의 데이터를 가져온다. (11~20번째)
   • 3페이지 요청 (page=3): offset = (3 - 1) * 10 = 20. 20개를 건너뛰고 10개의 데이터를 가져온다. (21~30번째)

이처럼 LIMIT과 OFFSET을 SQL 쿼리에 동적으로 적용함으로써, 클라이언트는 현재 필요한 페이지의 데이터만 효율적으로 서버에 요청하고 응답받을 수 있다. 이는 시스템 전반의 성능과 안정성을 보장하는 핵심적인 역할을 한다.