close
프로필 배경
프로필 로고

heechann

MySQL · 2025. 5. 7. fullscreen 넓게보기

페이징 쿼리 성능 비교 (offset 기반 vs 인덱스 기반)

페이징 처리에는 일반적으로 OFFSET .. LIMIT 구문이 사용된다. 하지만 이 방식은 OFFSET에 해당하는 수만큼의 행을 순차적으로 스캔한 후 버리는 구조이기 때문에, 데이터 양이 많아질수록 성능이 급격히 저하된다고 학습하였습니다.
이러한 성능 저하의 원인이 단순한 이론적 설명이 아닌 실제 I/O 동작에서 비롯된 것인지 확인하고자, I/O 접근 횟수와 실제 스캔된 행 수를 측정해가며 실습을 진행하였습니다.

 

1. 실습 환경 세팅

테이블을 생성하고 파이썬을 이용하여 1000만개의 데이터를 생성후 LOAD DATA LOCAL INFILE을 이용하여 테스트용 데이터를 생성했다.

 

`DDL` 

CREATE TABLE `item` (
  `created_at` datetime(6) DEFAULT NULL,
  `finished_at` datetime(6) DEFAULT NULL,
  `id` bigint NOT NULL AUTO_INCREMENT,
  `description` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

`python code`

import csv

N = 10_000_000
csv_file = "data.csv"

with open(csv_file, mode="w", newline="", encoding="utf-8") as f:
    writer = csv.writer(f)

    for i in range(1, N + 1):
        row = [
            "name",
            "title",
            "2025-03-12 23:05:13",
            "2025-03-12 23:05:13",
        ]
        writer.writerow(row)

print("CSV 생성 완료!")

`데이터 저장`

mysql -u root -p --local-infile=1
USE mydatabase;

SET GLOBAL local_infile = 1;
SHOW GLOBAL VARIABLES LIKE 'local_infile';

LOAD DATA LOCAL INFILE 'C:/Temp/data.csv'
INTO TABLE item
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(
  name,
  description,
  created_at,
  finished_at
);

 

대략 데이터를 적재하는데 1분 20초 정도 걸리므로 콘솔창에서 아무 동작을 하지 않더라도 기다리면 된다.

 

이렇게 진행하면 기본적으로 테스트할 환경은 세팅된 것이다. 

 

2. EXPLAIN ANALYZE 사용하기

가장 간단하게 실제로 읽은 행 수와 해당 쿼리를 실행하는데 얼마나 걸렸는지를 확인해보며 offset 페이징 쿼리와 index를 이용한 페이징 쿼리를 비교해보겠습니다.

 

먼저 offset 쿼리입니다.

EXPLAIN ANALYZE SELECT * FROM item LIMIT 10 OFFSET 1000000;

실제 읽은 행 수는 (actual ...) 부분의 rows를 통해 알 수 있다. 총 100만개의 행을 테이블 스캔으로 접근한 것을 알 수 있다.

Limit / Offset 쿼리에서 limit 값을 통해 10개의 행만 결과로 반환한 것을 알 수 있고 총 수행시간은 `204ms`이다.

 

다음은 index를 이용한 페이징 쿼리이다. 실습에서는 pk값을 이용하여 인덱스 페이징 쿼리를 작성하는 상황으로 진행하였다.

EXPLAIN ANALYZE SELECT * FROM item i WHERE i.id > 99999 LIMIT 10;

pk를 이용한 인덱스 페이징 쿼리의 경우 첫 pk 위치로 인덱스 트리를 이용하여 접근하고 연속해서 10개를 읽고 작업이 끝나는 것을 확인할 수 있다. 총 수행시간은 `0.05 ms` 이다.

 

즉 EXPLAIN ANALYZE를 이용하여 분석만 해도 rows를 offset 수만큼 읽는 페이징 쿼리는 뒷페이지로 갈수록 조회성능이 떨어지는 것은 당연한 일이었다.

 

3. performance_schema.file_summary_by_insance  사용

performance_schema.file_summary_by_insance 테이블을 이용하여 각 쿼리의 물리적 페이지 읽기 횟수와 버퍼풀 캐시 적중률을 확인할 수 있다.

해당 테이블을 이용하여 비교하기 전에 노트북에 설치된 mysql의 데이터 페이지 크기와 버퍼풀의 크기를 확인하였다.

데이터 페이지 크기 확인
SELECT @@innodb_page_size;

버퍼풀 총 크기 확인
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

버퍼풀 총 페이지 수 확인
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_total';

 

 

이를 통해, 대략 버퍼풀 크기는 약 `134mb`임을 확인할 수 있었다.

 

이제 각 페이징 쿼리에 대해 현재 사용중인 페이지, I/O 집계 쿼리 그리고 버퍼 풀 캐시 적중률을 확인해보며 어떤 차이가 있는지 살펴보려고 한다.

 

실습을 하기 앞서 각 쿼리마다 작업관리자를 이용하여 mysql을 재시작했다.

[ 작업 관리자 -> 서비스 -> mysql 검색 -> 재시작]

 

Offset 페이징 쿼리

use example;
TRUNCATE TABLE performance_schema.file_summary_by_instance;

-- 현재 사용중인 페이지
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_data';

-- 버퍼풀 캐시 적중률
SHOW GLOBAL STATUS WHERE Variable_name 
IN ('Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads');

-- offset 페이징 쿼리
SELECT * FROM item LIMIT 10 OFFSET 100000;

-- I/O 집계 쿼리
SELECT SUM(COUNT_READ) AS read_ops, 
	   SUM(COUNT_WRITE) AS write_ops, 
	   SUM(SUM_NUMBER_OF_BYTES_READ) AS bytes_read, 
	   SUM(SUM_NUMBER_OF_BYTES_WRITE) AS bytes_written 
FROM performance_schema.file_summary_by_instance 
WHERE FILE_NAME LIKE '%item.ibd%';

 

`쿼리 실행 전 사용중인 페이지`

`쿼리 실행 전 버퍼풀 캐시 적중 횟수`

`offset 페이징 쿼리`

 

`I/O 집계 쿼리`

`쿼리 실행 이후 사용중인 페이지`

`쿼리 실행 후 버퍼풀 캐시 적중 횟수`

이 결과를 통해 파일 단위의 실제 디스크 I/O 요청(read_ops)이 644회 발생했으며, 그중에서 InnoDB 버퍼 풀에 새롭게 로드된 페이지(Innodb_buffer_pool_reads)는 37개였음을 확인할 수 있다.
Innodb_buffer_pool_reads는 버퍼 풀에 존재하지 않는 데이터 페이지를 디스크에서 읽어올 때만 증가하는 지표로, 즉 캐시 미스가 발생한 경우에만 집계된다. 이때, read_ops와 Innodb_buffer_pool_reads 증가량이 다르게 나오는 것이 궁금하여 여러 번 MYSQL을 종료하고 노트북을 종료하고 실행하였는데 다음과 같은 결과를 얻을 수 있었다.

  • 현재 사용중인 페이지(Innodb_buffer_pool_pages_data) + 1 = read_ops
  • Innodb_buffer_pool_reads  증가량 = 99 
이 결과가 계속 똑같이 나와 이 부분에 대해서는 나중에 MySQL을 더 학습하며 알아가 보려고 합니다. 일단 페이징 쿼리의 성능 차이를 확인하는데 집중하였습니다.

 

따라서 동일한 OFFSET 페이징 쿼리를 한 번 더 실행하면, 대부분의 페이지가 이미 버퍼 풀에 캐싱되어 있으므로 I/O 요청과 캐시 미스가 크게 줄어든 결과를 확인할 수 있습니다.

두 번째 페이징 쿼리의 경우 innoDB 버퍼 풀에 캐시 되어있기 때문에 파일 읽기 I/O 요청(read_ops)이 발생하지 않았으며 그 대신 Innodb_buffer_pool_read_requests가 증가한 것을 확인할 수 있다. 해당 수치는 버퍼 풀에서 논리적 페이지 조회가 시도된 횟수로 페이징 쿼리 요청이 버퍼 풀에서 해결되었음을 알 수 있다. Innodb_buffer_pool_reads가 1회 증가한 것은 I/O 집계 쿼리를 사용하며 증가하였다. 따라서 Innodb_buffer_pool_read_requests에도 이 값이 일부 포함되어 있다.

 

Index 페이징 쿼리

실행 전에 먼저 작업관리자를 통해 MySQL 서버를 재시작했다.

위 상황에서 페이징 쿼리만 변경하여 쿼리를 실행하였다.

SELECT * FROM item i WHERE i.id > 99999 LIMIT 10;

 

`쿼리 실행 이전 상태 + 쿼리 실행`

 

`쿼리 실행 이후`

 

Offset 페이징보다 확연하게 적은 I/O 접근과 메모리 사용량을 볼 수 있다.
즉, 인덱스 페이징 스캔을 사용 시 장점은 빠른 조회 속도뿐만 아니라 MYSQL의 버퍼 풀까지도 최적화시킬 수 있었다.

 

4. 마무리

항상 애플리케이션 단에서 페이징 쿼리 최적화 비교만 진행하여서 실제 MySQL의 내부에서는 어떤 동작을 하는지 의문이었는데 그 의문을 해소할 수 있는 시간이었습니다. `offset`페이징과 `index` 페이징의 동작 차이를 확인해 단순한 조회 속도 개선을 넘어, MySQL의 버퍼 풀(메모리) 사용 최적화까지 영향을 줄 수 있다는 점을 배울 수 있었습니다.

 

이번 글에서는 PK 기반 인덱스를 활용한 페이징 쿼리를 중심으로 실습을 진행하며, 서비스 로직보다는 쿼리 성능 비교에 초점을 맞췄습니다.
다음 글에서는 Spring Boot로 DateTime 기반 인덱스를 활용한 페이징 처리 방식을 실습을 통해 다뤄보겠습니다. 또, 중간에 의문점이 들었던 read_ops와 Innodb_buffer_pool_reads 증가량이 다르게나오는 부분도 이후에 MySQL을 더 학습하며 알아가 보려고 합니다.

 

티스토리툴바