4강. 페이징 쿼리 작성

1. 페이징 쿼리란?

• 원하는 전체 데이터에 대해 부분적으로 나눠서 데이터를 조회 및 처리하는 방법

• DB 및 애플리케이션 서버의 리소스(CPU, 메모리, 네트워크) 사용 효율 증가

• 애플리케이션 단의 처리 효율 증가!

2. 페이징 쿼리 작성

• DB 에서 제공하는 LIMIT & OFFSET 구문을 사용하는 경우가 많다.

• 하지만 LIMIT & OFFSET 구문을 사용할 때, DBMS 에 더 많은 부하를 발생시킨다. → 왜냐면, DB 는 OFFSET 위치까지 데이터 레코드를 순차적으로 읽은 후에야 원하는 데이터를 리턴할 수 있다.

• DBMS 에서 순차적으로 데이터를 읽지 않고, 원하는 OFFSET 에서 바로 데이터를 읽을수는 없다!

• 결국, LIMIT & OFFSET 구문을 사용하는 경우 쿼리 실행 횟수가 증가할수록 점점 더 읽는 데이터가 많아지고, 응답 시간이 길어진다.

• 때문에, LIMIT & OFFSET 구문을 사용하지 않고서 데이터를 원하는 만큼만 조회할 수 있어야 한다.

• 아래 2개 방식을 사용할 수 있다.

  1. 범위 기반 방식

  2. 데이터 개수 기반 방식

3. 범위 기반 방식

→ 쿼리의 형태가 매우 단순하다.

• 날짜 기간이나, 숫자로 범위를 나누어 조회하는 방식

• 매 쿼리 실행 시, WHERE 절에 조회 범위를 직접 지정하는 형태로, 쿼리에서 LIMIT 절이 사용되지 않는다.

• 주로 배치 작업 등에서, 테이블 전체 데이터를 일정 숫자, 날짜 범위로 나누어 조회할 때 사용

• 쿼리에서 사용되는 조회 조건도 굉장히 단순하며, 여러 번 쿼리를 나누어 실행하더라도 사용하는 쿼리 형태는 동일하다.

• 범위 조건에 해당하는 컬럼의 인덱스를 미리 설정해 두는 것이 조회를 더욱 빠르게 할 수 있다.

• 예시

  • 숫자인 id 값을 바탕으로 범위를 나눠 데이터 조회(5000 개씩)

  SELECT * 
  FROM user
  WHERE id > 0 AND id <= 5000

  • 날짜 기준으로 나눠서 조회(일 단위로 조회)

  SELECT *
  FROM payment 
  WHERE finished_at >= '2024-01-01' AND finished_at < '2024-01-02'

4. 데이터 개수 기반 방식

→ 쿼리 형태가 간단하지는 않기에 상황을 잘 살펴보자

• 지정된 데이터 건수만큼 결과 데이터를 반환하는 형태로 구현된 방식

• 배치보다는 주로 서비스 단에서 많이 사용되는 방식이다.

• 쿼리에서 ORDER BY & LIMIT 절이 사용된다.

• 처음 쿼리를 실행할 때(1회차) 와 그 이후 쿼리를 실행할 때(N회차) 쿼리 형태가 달라진다.

• 예시

  • 데이터 개수 기반 방식(동등 조건 시) → ORDER BY 절에는 각각의 데이터를 식별할 수 있는 식별자 컬럼(PK)이 반드시 포함되어야 한다. → 인덱스가 걸려 있는 컬럼을 조건으로 사용해 인덱스 값으로 정렬 되어 있는 정보를 활용할 수 있다.

  CREATE TABLE payments {
  	id int NOT NULL AUTO_INCREMENT,
  	user_id int NOT NULL,
  	..., 
  	PRIMARY KEY (id),
  	KEY ix_uesrid_id(user_id, id)
  };
  
  // 1회차 
  SELECT *
  FROM payments
  WHERE user_id = ? 
  ORDER BY id
  LIMIT 30 
  
  // N회차
  SELECT *
  FROM payments
  WHERE user_id = ? 
  	**AND id > 이전 ID 마지막 값** 
  ORDER BY id
  LIMIT 30 

  • 데이터 개수 기반 방식(범위 조건 시)

    • 왜 ORDER BY 절에 finished_at 이 포함되어야 할까?

      • id 컬럼만 명시하는 경우 WHERE 을 만족하는 데이터들을 모두 읽어들인 후, id 로 정렬한 다음 지정된 건수만을 반환하게 됨 → 인덱스를 사용하지 않는다!

      • finished_at, id 컬럼을 명시하는 경우, WHERE 을 만족하는 데이터들을 모두 읽어들인 후, 정렬 작업 없이 데이터를 읽어들일 수 있기 때문에, 처리 효율 향상 → 인덱스를 사용한다!

    • 범위 조건 컬럼 값 순서와 식별자 컬럼 값 순서가 동일하지 않은 경우

    CREATE TABLE payments {
    	id int NOT NULL AUTO_INCREMENT,
    	user_id int NOT NULL,
    	finished_at datetime NOT NULL,
    	..., 
    	PRIMARY KEY (id),
    	KEY ix_finishedat_id(finished_id, id)
    };
    
    // 1회차 
    SELECT *
    FROM payments 
    WHERE finished_at >= '2024-01-01 00:00:00'
    	AND finished_at < '2024-01-02 00:00:00'
    ORDER BY finished_at, id 
    LIMIT 30 
    
    // N회차 
    SELECT *
    FROM payments 
    WHERE ((finished_at = '2024-01-01 00:00:02' AND id > 8) 
    	OR 
    	(finished_at > '2024-01-01 00:00:02' AND finished_at < '2024-01-02 00:00:00')
    ORDER BY finished_at, id 
    LIMIT 30 

    • 범위 조건 컬럼 값 순서와 식별자 컬럼 값 순서가 동일한 경우

    CREATE TABLE user_logs {
    	id int NOT NULL AUTO_INCREMENT,
    	created_at datetime NOT NULL,
    	..., 
    	PRIMARY KEY (id),
    	KEY ix_createdat_id(created_at, id)
    };
    
    // 1회차 
    SELECT *
    FROM user_logs 
    WHERE created_at >= '2024-01-01 00:00:00'
    	AND created_at < '2024-01-02 00:00:00'
    ORDER BY created_at, id 
    LIMIT 30 
    
    // N회차 
    SELECT *
    FROM user_logs 
    WHERE created_at >= '2024-01-01 00:00:02'
    	AND created_at < '2024-01-02 00:00:00'
    	AND id > 5
    ORDER BY created_at, id 
    LIMIT 30