--- title: "LIMIT 최적화: large offset의 비용과 seek pagination 설계" description: "MySQL에서 large offset pagination이 느려지는 내부 원인과 seek 방식의 인덱스 설계·일관성·운영 기준을 정리한다." tags: [ MySQL, 성능최적화, 인덱스, 운영 ] image: "mysql-report-bg.png" published: "2026-07-12" updated: "2026-07-12" author: "MySQL 기술 노트" source_url: "" --- 목록 화면과 배치 API에서 `LIMIT offset, count`는 구현하기 쉽다. 그러나 페이지 번호가 커질수록 응답 시간이 증가하고, 같은 쿼리가 더 많은 버퍼 페이지와 CPU를 소비한다. 이 문제는 반환 행이 20건이라는 사실만 보고는 설명되지 않는다. MySQL은 반환하지 않을 앞쪽 행도 정렬 순서에 따라 찾아가며 버려야 하기 때문이다. 이 글은 large offset의 실행 경로를 먼저 살펴보고, 마지막으로 읽은 정렬 키를 다음 요청에 넘기는 seek pagination(keyset pagination)을 설계하는 방법을 설명한다. 단순 SQL 치환뿐 아니라 복합 정렬 키, 중복과 누락, 데이터 변경 중 일관성, 인덱스 구성, Aurora MySQL 운영 시 주의점까지 함께 다룬다. ## 1. OFFSET은 결과를 건너뛰는 명령이지 작업을 생략하는 명령이 아니다 다음 쿼리가 한 페이지에 20건을 반환한다고 가정한다. ```text SELECT id, created_at, title FROM posts WHERE status = 'PUBLISHED' ORDER BY created_at DESC, id DESC LIMIT 100000, 20; ``` MySQL은 논리적으로 조건을 만족하는 행을 정렬 순서대로 읽은 뒤, 앞의 100,000건을 버리고 20건을 반환한다. 적절한 인덱스가 있어도 대체로 `offset + count`개의 인덱스 엔트리를 방문해야 한다. 보조 인덱스가 쿼리를 covering하지 않으면 후보마다 clustered primary key를 따라가 원본 행을 읽는 비용까지 추가될 수 있다. 적절한 정렬 인덱스가 없으면 상황은 더 나쁘다. 조건에 맞는 행을 모아 filesort를 수행한 뒤 큰 offset을 폐기할 수 있다. 따라서 large offset은 다음 자원을 함께 소비한다. - B-tree leaf page 순회와 버퍼 풀 접근 - 조건 평가 및 행 폐기 CPU - non-covering 보조 인덱스의 primary key lookup - 정렬이 필요할 때 sort buffer와 내부 임시 공간 - 긴 실행 시간에 따른 동시 쿼리 증가와 connection 점유 ```mermaid graph LR A[조건에 맞는 인덱스 범위 시작] --> B[offset + count 행 방문] B --> C[offset 행 폐기] C --> D[count 행 반환] E[seek 경계 탐색] --> F[count 행만 순차 방문] F --> G[count 행 반환] ``` OFFSET 방식의 비용은 페이지 번호에 비례해 증가하지만, seek 방식은 B-tree에서 경계 키를 찾은 뒤 필요한 개수만 읽으므로 페이지가 깊어져도 작업량이 비교적 일정하다. ## 2. 재현 예제로 실행 계획 비교하기 다음 예제는 1,000행을 만들고 primary key 순서의 깊은 페이지를 조회한다. 운영 테이블의 I/O 규모를 재현하려는 벤치마크가 아니라, optimizer가 OFFSET과 seek 조건에서 예상하는 방문 범위의 차이를 확인하는 축소 예제다. ```sql DROP TABLE IF EXISTS pagination_demo; CREATE TABLE pagination_demo ( id BIGINT NOT NULL, created_at DATETIME NOT NULL, status VARCHAR(16) NOT NULL, payload VARCHAR(100) NOT NULL, PRIMARY KEY (id), KEY ix_status_created_id (status, created_at, id) ) ENGINE=InnoDB; INSERT INTO pagination_demo (id, created_at, status, payload) SELECT n, TIMESTAMP('2026-01-01 00:00:00') + INTERVAL n SECOND, 'PUBLISHED', CONCAT('row-', n) FROM ( SELECT ones.n + tens.n * 10 + hundreds.n * 100 + 1 AS n FROM (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) ones CROSS JOIN (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) tens CROSS JOIN (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) hundreds ) numbers; ANALYZE TABLE pagination_demo; SELECT COUNT(*) AS row_count, MIN(id) AS min_id, MAX(id) AS max_id FROM pagination_demo; ``` 실행 결과(MySQL 8.0.x): ```text mysql> CREATE TABLE pagination_demo (...); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO pagination_demo (...) SELECT ...; Query OK, 1000 rows affected (0.03 sec) Records: 1000 Duplicates: 0 Warnings: 0 mysql> ANALYZE TABLE pagination_demo; +---------------------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------------------------+---------+----------+----------+ | mysql_tech_note.pagination_demo | analyze | status | OK | +---------------------------------+---------+----------+----------+ 1 row in set (0.01 sec) mysql> SELECT COUNT(*) AS row_count, MIN(id) AS min_id, MAX(id) AS max_id -> FROM pagination_demo; +-----------+--------+--------+ | row_count | min_id | max_id | +-----------+--------+--------+ | 1000 | 1 | 1000 | +-----------+--------+--------+ 1 row in set (0.00 sec) ``` 첫 번째 계획은 900행을 건너뛰고 10행을 가져온다. 두 번째 계획은 직전 페이지의 마지막 키가 `900`이었다고 보고 그 경계보다 큰 행 10건만 읽는다. ```sql EXPLAIN SELECT id, created_at, payload FROM pagination_demo ORDER BY id LIMIT 900, 10; EXPLAIN SELECT id, created_at, payload FROM pagination_demo WHERE id > 900 ORDER BY id LIMIT 10; ``` 실행 결과(MySQL 8.0.x): ```text mysql> EXPLAIN -> SELECT id, created_at, payload -> FROM pagination_demo -> ORDER BY id -> LIMIT 900, 10; +----+-------------+-----------------+------------+-------+---------------+---------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+-------+---------------+---------+---------+------+------+----------+-------+ | 1 | SIMPLE | pagination_demo | NULL | index | NULL | PRIMARY | 8 | NULL | 910 | 100.00 | NULL | +----+-------------+-----------------+------------+-------+---------------+---------+---------+------+------+----------+-------+ 1 row in set (0.00 sec) mysql> EXPLAIN -> SELECT id, created_at, payload -> FROM pagination_demo -> WHERE id > 900 -> ORDER BY id -> LIMIT 10; +----+-------------+-----------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | pagination_demo | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 100 | 100.00 | Using where | +----+-------------+-----------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set (0.00 sec) ``` `EXPLAIN.rows`는 통계에 따른 추정치이므로 실행마다 조금 달라질 수 있다. 비교의 핵심은 OFFSET 쿼리가 선두부터 많은 엔트리를 방문하는 `index` 접근인 반면, seek 쿼리는 `id > 900` 경계에서 시작하는 `range` 접근이라는 점이다. 실제 운영에서는 `EXPLAIN ANALYZE`로 추정 행 수가 아니라 각 iterator의 실제 `rows`와 시간을 확인한다. 다만 운영 트래픽에서 `EXPLAIN ANALYZE`는 쿼리를 실제 실행하므로 읽기 부하를 고려해야 한다. 두 방식이 같은 논리 구간을 반환하는지도 확인할 수 있다. ```sql SELECT id FROM pagination_demo ORDER BY id LIMIT 900, 10; SELECT id FROM pagination_demo WHERE id > 900 ORDER BY id LIMIT 10; ``` 실행 결과(MySQL 8.0.x): ```text mysql> SELECT id -> FROM pagination_demo -> ORDER BY id -> LIMIT 900, 10; +-----+ | id | +-----+ | 901 | | 902 | | 903 | | 904 | | 905 | | 906 | | 907 | | 908 | | 909 | | 910 | +-----+ 10 rows in set (0.00 sec) mysql> SELECT id -> FROM pagination_demo -> WHERE id > 900 -> ORDER BY id -> LIMIT 10; +-----+ | id | +-----+ | 901 | | 902 | | 903 | | 904 | | 905 | | 906 | | 907 | | 908 | | 909 | | 910 | +-----+ 10 rows in set (0.00 sec) ``` 마지막에는 예제 객체를 정리한다. ```sql DROP TABLE pagination_demo; ``` 실행 결과(MySQL 8.0.x): ```text mysql> DROP TABLE pagination_demo; Query OK, 0 rows affected (0.03 sec) ``` ## 3. seek pagination의 핵심은 전체 순서를 만드는 것이다 seek pagination은 이전 페이지의 마지막 행이 가진 정렬 키를 cursor로 사용한다. 단일 증가 primary key 정렬이라면 조건은 단순하다. ```text -- 첫 페이지 SELECT id, created_at, title FROM posts WHERE status = 'PUBLISHED' ORDER BY id LIMIT 20; -- 다음 페이지: :last_id는 직전 응답의 마지막 id SELECT id, created_at, title FROM posts WHERE status = 'PUBLISHED' AND id > :last_id ORDER BY id LIMIT 20; ``` 실무 목록은 보통 최신순이며 `created_at` 값이 중복될 수 있다. `created_at`만 cursor로 사용하면 같은 시각의 행이 페이지 경계에서 빠지거나 다시 나타난다. 반드시 유일한 tie-breaker를 추가해 전체 순서(total order)를 만들어야 한다. ```text SELECT id, created_at, title FROM posts WHERE status = 'PUBLISHED' AND ( created_at < :last_created_at OR (created_at = :last_created_at AND id < :last_id) ) ORDER BY created_at DESC, id DESC LIMIT 20; ``` 이에 맞는 대표 인덱스는 다음과 같다. ```text CREATE INDEX ix_posts_page ON posts (status, created_at DESC, id DESC); ``` 인덱스 설계 순서는 쿼리 모양과 맞아야 한다. 1. `status`처럼 동등 비교하는 필터 컬럼을 선두에 둔다. 2. 그 뒤에 정렬 컬럼 `created_at`을 둔다. 3. 마지막에 유일한 tie-breaker `id`를 둔다. 4. projection 컬럼을 무조건 인덱스에 모두 넣지는 않는다. covering 이득과 인덱스 크기·쓰기 증폭을 함께 평가한다. MySQL 8.0은 descending index를 지원한다. 다만 모든 정렬 방향이 같은 경우 B-tree를 역방향으로 읽는 것으로 충분할 수도 있다. 혼합 방향 정렬과 실제 쿼리 빈도를 기준으로 `EXPLAIN`의 `key`, `type`, `Extra`를 확인해야 한다. ## 4. cursor는 값만이 아니라 조회 계약을 담아야 한다 API cursor에는 보통 `created_at`과 `id`를 직접 노출하지 않고 불투명 토큰으로 인코딩한다. 단순 Base64는 난독화일 뿐 위변조 방지가 아니다. 외부 사용자가 cursor를 전달하는 API라면 다음 내용을 포함하고 서명하는 편이 안전하다. - 마지막 행의 정렬 키: `created_at`, `id` - 정렬 방향과 버전 - 목록 필터의 식별 정보 - 필요하면 조회 snapshot의 상한 - 만료 시각 - HMAC 또는 서버 측 상태 키 cursor를 다른 필터에 재사용하면 누락 또는 중복이 생길 수 있다. 예를 들어 `status=PUBLISHED`에서 받은 cursor를 `category=DBA` 목록에 적용해서는 안 된다. 서버는 cursor가 현재 요청의 정렬·필터 계약과 일치하는지 검증해야 한다. 또한 문자열로 직렬화한 시간은 정밀도를 보존해야 한다. 컬럼이 `DATETIME(6)`인데 cursor가 초 단위까지만 저장하면 동일 초 안의 순서 경계가 달라진다. 시간대 변환도 애플리케이션 계층에서 일관되게 처리해야 한다. ## 5. 데이터가 바뀌는 동안 페이지는 어떻게 보이는가 각 페이지 요청이 별도 트랜잭션이면 OFFSET과 seek 모두 요청 사이의 변경을 관찰한다. 그러나 변경의 영향은 다르다. ### 5.1 OFFSET에서 발생하는 위치 이동 1페이지를 읽은 뒤 앞쪽에 새 행이 삽입되면 기존 행의 위치가 뒤로 밀린다. 이후 `OFFSET 20`을 요청하면 1페이지 마지막 행이 다시 나타날 수 있다. 반대로 앞쪽 행이 삭제되면 아직 읽지 않은 행이 앞으로 당겨져 건너뛸 수 있다. ### 5.2 seek가 제공하는 안정성 seek는 위치가 아니라 값 경계를 사용하므로 cursor 이전 구간의 삽입·삭제에 덜 민감하다. `created_at DESC, id DESC`에서 마지막 키보다 작은 다음 구간을 읽기 때문에 앞쪽에 새 최신 행이 생겨도 이미 읽은 페이지의 경계는 이동하지 않는다. 그러나 seek도 완전한 snapshot은 아니다. 아직 읽지 않은 행의 정렬 키가 수정되어 cursor 반대편으로 이동하면 누락되거나 중복될 수 있다. 강한 일관성이 필요하면 다음 중 하나를 선택한다. - 첫 요청 시 최대 `id`나 기준 시각을 고정하고 모든 페이지에 상한 조건을 적용한다. - export 작업은 하나의 consistent snapshot에서 처리하되 긴 트랜잭션의 undo 보존 비용을 관리한다. - 변경 불가능한 정렬 키를 사용한다. - 검색 결과 ID를 별도 작업 테이블이나 객체에 materialize한다. 웹 탐색에는 대개 seek의 실용적 안정성이 충분하지만, 회계 보고서나 전체 export에는 “페이지 탐색”과 “snapshot 추출”을 같은 문제로 취급하면 안 된다. ## 6. seek 방식의 제약과 혼합 전략 seek pagination은 임의의 5000페이지로 즉시 이동하는 기능을 자연스럽게 제공하지 않는다. 직전 경계를 알아야 하기 때문이다. 제품 요구가 페이지 번호 직접 이동을 강제한다면 다음 전략을 검토한다. - 처음 몇 페이지는 OFFSET을 허용하고 깊은 구간은 cursor 기반으로 전환한다. - 날짜·ID 범위별 anchor를 미리 두고 가까운 anchor부터 seek한다. - 전체 페이지 번호 대신 “이전/다음” 탐색 UX를 제공한다. - 정확한 총 건수 대신 별도 집계나 근사치를 사용한다. - 관리용 export는 온라인 페이지 API가 아니라 비동기 snapshot 작업으로 분리한다. “전체 2,483,921건 중 1,000번째 페이지” 같은 UI는 정확한 `COUNT(*)`와 임의 offset까지 동시에 요구할 수 있다. InnoDB는 MVCC 때문에 테이블의 정확한 행 수를 상수 시간에 제공하지 않는다. 따라서 pagination SQL만 바꾸기 전에 정확한 총 건수와 임의 점프가 정말 필요한지 제품 계약부터 재검토해야 한다. ## 7. 운영 진단: 무엇을 측정할 것인가 large offset 문제를 확인할 때 단일 slow query의 실행 시간만 보지 않는다. offset 구간별로 다음 신호를 비교한다. - `EXPLAIN ANALYZE`의 실제 iterator 행 수와 실행 시간 - `performance_schema.events_statements_summary_by_digest`의 평균·최대 latency와 examined rows - 애플리케이션의 page depth 또는 offset 구간별 p95/p99 latency - `Rows_examined / Rows_sent` 비율 - 버퍼 풀 read request와 물리 read 변화 - 같은 endpoint의 동시 실행 수와 timeout 비율 SQL digest는 literal offset 값을 정규화할 수 있으므로 하나의 digest 평균만 보면 깊은 페이지의 tail latency가 가려질 수 있다. 애플리케이션 telemetry에 offset 구간 또는 pagination 방식이라는 low-cardinality attribute를 추가하는 것이 유용하다. 반대로 raw cursor나 사용자 검색어를 metric label로 넣으면 cardinality가 폭증하므로 피해야 한다. 운영 전환은 다음 순서가 안전하다. 1. 기존 OFFSET endpoint의 page depth 분포와 latency를 측정한다. 2. 동일 필터·정렬 계약의 cursor endpoint를 추가한다. 3. 실제 데이터 분포에서 인덱스와 실행 계획을 검증한다. 4. shadow query 또는 제한된 트래픽으로 결과 연속성과 성능을 비교한다. 5. cursor 위변조, 만료, 필터 불일치, 삭제된 경계 행을 시험한다. 6. deep pagination 요청을 점진적으로 seek 방식으로 이동한다. ## 8. Aurora MySQL에서의 해석 Aurora MySQL도 MySQL 호환 optimizer와 B-tree 인덱스를 사용하므로 large offset의 논리적 비용은 사라지지 않는다. 분산 스토리지가 물리 I/O 특성을 바꾸더라도 앞쪽 인덱스 엔트리를 방문하고 폐기하는 CPU·버퍼 접근·행 lookup 비용은 남는다. Reader endpoint로 목록 읽기를 분산하면 writer 부하는 줄일 수 있지만 다음 사항을 고려해야 한다. - replica lag 또는 read-after-write 요구 때문에 페이지 사이에 관찰되는 데이터가 달라질 수 있다. - 요청마다 다른 reader에 연결되면 캐시 상태와 지연 시간이 달라질 수 있다. - failover나 reader 교체 후에도 cursor가 특정 인스턴스 상태에 의존해서는 안 된다. - Performance Insights 또는 Performance Schema에서 digest 부하를 보되, 깊은 offset의 tail을 애플리케이션 지표와 함께 분석한다. cursor는 Aurora 인스턴스 식별자나 세션 상태가 아니라 데이터의 정렬 키와 조회 계약을 담아야 한다. 강한 snapshot이 필요한 대용량 추출은 reader endpoint의 반복 페이지 호출보다 별도의 export·ETL 경로가 더 적합할 수 있다. ## 9. 흔한 오해와 실패 패턴 ### `LIMIT 20`이므로 20행만 읽는다는 오해 `LIMIT 100000, 20`은 100,000행의 작업을 제거하지 않는다. 반환량과 방문량을 구분해야 한다. ### 정렬 컬럼 하나만 cursor로 사용 중복 가능한 `created_at`만 경계로 쓰면 같은 값의 행이 페이지 사이에서 누락된다. 유일한 tie-breaker를 포함해야 한다. ### 인덱스를 추가하면 OFFSET 비용이 상수가 된다는 오해 정렬과 필터에 맞는 인덱스는 filesort와 불필요한 scan을 줄이지만, 큰 offset을 순회하고 폐기하는 선형 비용까지 없애지는 못한다. ### `id > last_id`를 모든 정렬에 적용 화면이 `created_at DESC, id DESC`로 정렬되는데 `id`만 경계로 쓰면 순서 계약이 달라진다. `WHERE` 경계 조건과 `ORDER BY`의 전체 키·방향이 일치해야 한다. ### 긴 트랜잭션으로 모든 페이지를 묶기 일관된 snapshot을 얻을 수 있지만 긴 read transaction은 purge를 지연시키고 undo history를 키울 수 있다. 대화형 웹 페이지에 세션을 걸쳐 트랜잭션을 유지하는 방식은 적합하지 않다. ## 10. 설계 및 운영 체크리스트 - [ ] 최대 offset과 page depth의 실제 분포를 측정했는가? - [ ] 정렬이 유일한 전체 순서를 이루도록 tie-breaker를 추가했는가? - [ ] cursor의 비교 조건과 `ORDER BY` 컬럼·방향이 정확히 일치하는가? - [ ] 동등 필터, 정렬 키, tie-breaker 순서에 맞는 복합 인덱스가 있는가? - [ ] projection을 covering할 이득과 인덱스 크기·쓰기 비용을 비교했는가? - [ ] cursor가 필터·정렬 버전·정밀도를 보존하며 위변조를 방지하는가? - [ ] 삽입·삭제·정렬 키 변경 중 중복과 누락 허용 범위를 정의했는가? - [ ] snapshot이 필요한 export를 대화형 pagination과 분리했는가? - [ ] `EXPLAIN`뿐 아니라 실제 데이터에서 latency와 examined rows를 측정했는가? - [ ] Aurora reader 전환과 replica 지연이 읽기 일관성에 미치는 영향을 검토했는가? - [ ] 임의 페이지 이동과 정확한 총 건수 요구를 제품 수준에서 재검토했는가? ## 맺음말 large offset의 본질은 MySQL이 많은 행을 반환해서가 아니라, 반환하지 않을 행까지 순서대로 방문하고 폐기해야 한다는 데 있다. 적절한 인덱스는 이 비용을 줄이지만 깊이에 비례하는 구조 자체를 없애지는 못한다. seek pagination은 유일한 정렬 키 경계를 B-tree range 조건으로 바꾸어 페이지 깊이와 작업량을 분리한다. 다만 안정적인 전환에는 SQL 한 줄 이상의 설계가 필요하다. 전체 순서, 복합 인덱스, cursor 계약, 변경 중 일관성, 임의 페이지 이동 요구를 함께 결정해야 한다. 다음 단계에서는 pagination을 포함한 범위 조회가 복합 인덱스의 leftmost prefix와 정렬 방향에 따라 어떻게 실행 계획을 바꾸는지 더 깊게 살펴볼 수 있다.