배경
offset 방식의 페이지네이션은 페이지 깊이가 깊어질수록 속도가 느려진다는 단점이 있다.
- 느려지는 이유 → DB는 어떤 정렬 순서에서 “N번째 row”를 바로 알 수 없기 떄문에 OFSSET 값이 커질수록 인덱스 스캔량이 증가해서 성능이 저하
OFFSET 10 LIMIT 10→ 0 ~20의 데이터를 읽어온 후 10~20을 반환
이를 cursor 방식을 통해서 개선해보았다.
- Cursor는 몇 번째 이후가 아닌, 어떤 값 이후부터 값을 찾기 때문에 DB에서 불필요한 데이터까지 읽을 필요가 없다.
- 대신 바로 N페이지로 이동과 같이 중간 탐색을 할 수 없다.
그렇다면 쿠팡과 같은 대형 이커머스 사이트들은 페이지네이션을 구현했을까?
with Claude
- 제한된 페이지 범위를 제공
- 구글 → 최대 40페이지 정도까지만 제공
- 쿠팡 → 제품이 존재하든 말든 UI적으로 27페이지까지 표기
자주 검색되는 키워드의 1~10 페이지는 캐시 처리하고, 이후 페이지부터는 커버링 인덱스로 설계
요구사항
쿠팡의 상품 검색을 참고하여 요구사항을 작성
- 정렬 기준 : 최신순[default], 높은 가격 순, 낮은 가격 순
- 이름 검색은 필수
- ownerId를 통한 필터링 가능
사용 기술
kotlin, spring boot, QueryDsl, mysql
애플리케이션 구현
QueryDslProductQueryRepositoryImpl
override fun findProductPage(
ownerId: Long?, size: Int, productName: String, sortRequest: ProductSorter
): List<ProductPageResult> {
val qProduct = QProduct.product
val qMember = QMember.member
val conditions = BooleanBuilder()
conditions.and(qProduct.name.startsWithIgnoreCase(productName))
if (ownerId != null) {
conditions.and(qProduct.ownerId.eq(ownerId))
}
**val sorter = QueryDslProductSorter.of(sortRequest)
// 정렬 조건에 맞게 WHERE문을 추가
sorter.buildCursorCondition(qProduct)?.let {
conditions.and(it)
}**
val sortOrders = buildList {
**// 정렬 조건을 추가**
**addAll(sorter.buildSortOrder(qProduct))**
add(qProduct.id.desc())
}
return jpaQueryFactory.select(
QProductPageResult(
qProduct.id,
qProduct.name,
qProduct.price,
QProductPageResult_OwnerResponse(
qMember.id, qMember.name
),
qProduct.createdAt
)
).from(qProduct).join(qMember).on(qProduct.ownerId.eq(qMember.id))
.where(conditions)
.orderBy(*sortOrders.toTypedArray())
.limit(size.toLong() + HAS_NEXT_CHECK_SIZE)
.fetch()
}cursor는 정렬 기준에 따라 WHERE 문에 오는 조건이 달라진다.
- 최신순인 경우 →
WHERE created_at < :created_at OR created_at = :created_at AND id < :idcreated_at = :created_at AND id < :id조건이OR로 존재하는 이유- 만약 id 10, 11, 12의
created_at이 같다고 가정했을 때, 1~10까지 조회를 한후 11부터 12까지의 조회를 해야하는데 created_at이 똑같기 때문에 다시 10부터 조회될 수 있다. - 이를 방지하기 위해 단조 증가하는 id를 타이브레이커로 활용하여 문제를 해결했다.
- 만약 id 10, 11, 12의
정렬 조건 코드
sealed class QueryDslProductSorter() {
abstract val lastProductId: Long?
abstract fun buildSortOrder(qProduct: QProduct): List<OrderSpecifier<*>>
protected abstract fun buildCondition(qProduct: QProduct): Predicate
protected abstract fun hasCursor(): Boolean
fun buildCursorCondition(qProduct: QProduct): Predicate? {
if (!hasCursor()) {
return null
}
return buildCondition(qProduct)
}
companion object {
fun of(request: ProductSorter): QueryDslProductSorter {
return when (request) {
is ProductSorter.LatestSorter -> {
LatestSorter(
lastProductId = request.lastProductId,
createdAt = request.createdAt
)
}
is ProductSorter.SalePriceAsc -> {
SalePriceAsc(
lastProductId = request.lastProductId,
price = request.price
)
}
is ProductSorter.SalePriceDesc -> {
SalePriceDesc(
lastProductId = request.lastProductId,
price = request.price
)
}
}
}
}
class LatestSorter(
override val lastProductId: Long?,
val createdAt: LocalDateTime?
) : QueryDslProductSorter() {
...
}
}
class SalePriceAsc(
override val lastProductId: Long?,
val price: BigDecimal?
) : QueryDslProductSorter() {
...
}
class SalePriceDesc(
override val lastProductId: Long?,
val price: BigDecimal?
) : QueryDslProductSorter() {
...
}
}sealed class를 활용하여 메인 메서드는 수정하지 않고 정렬 조건만 확장할 수 있게 구현하였다.
- 조건마다 변경되는 멤버변수, 로직을
findProductPage()에서 일관되게 사용할 수 있다. - 만약 조건이 추가되면
QueryDslProductSorter를 상속하여 새로운 클래스르 만들고, 이를 생성자 팩토리에 추가하면 된다.
Cursor vs offset 성능 테스트
products 테이블
create table products
(
product_price decimal(19, 2) null,
created_at datetime(6) not null,
deleted_at datetime(6) null,
id bigint auto_increment
primary key,
owner_id bigint null,
stock bigint null,
updated_at datetime(6) not null,
name varchar(20) null,
product_code varchar(255) null,
product_currency_code enum ('KOR') null,
constraint UK922x4t23nx64422orei4meb2y
unique (product_code)
);products에 들어갈 100만 건의 product 데이터 -> 클로드를 활용해 작성 후 실행
프로시저 실행 결과
SELECT COUNT(*) FROM products
테스트 시작
💡가격 내림차순으로 비교
SELECT *
FROM products JOIN members ON products.owner_id = members.id
ORDER BY products.product_price DESC, products.owner_id DESC
LIMIT 20;
-- price 내림차순 결괏값 --
price
999998.00
999997.00
999996.00
999994.00
999992.00
999989.00
999988.00
999987.00
999985.00
999984.00
999982.00
999982.00
999982.00
999982.00
999980.00
999979.00
999978.00
999977.00
999972.00
999971.00EXPLAIN ANALYZE 를 활용해서 실행계획을 분석
-- first-page --
SELECT *
FROM products JOIN members ON products.owner_id = members.id
ORDER BY products.product_price DESC, products.owner_id DESC
LIMIT 10;
-- offset --
SELECT *
FROM products JOIN members ON products.owner_id = members.id
ORDER BY products.product_price DESC, products.owner_id DESC
LIMIT 10 OFFSET 10;
-- cursor --
SELECT *
FROM products JOIN members ON products.owner_id = members.id
WHERE product_price < 999984.00 OR (product_price = 999984.00 AND products.id < 673417)
ORDER BY products.product_price DESC, products.owner_id DESC
LIMIT 10;인덱스가 없는 경우
-- first-page --
-> Limit: 10 row(s) (cost=449504 rows=10) (**actual time=948..948** rows=10 loops=1)
-> Nested loop inner join (cost=449504 rows=993187) (actual time=948..948 rows=10 loops=1)
-> Sort: products.product_price DESC, products.owner_id DESC (cost=101889 rows=993187) (actual time=948..948 rows=10 loops=1)
-> Filter: (products.owner_id is not null) (cost=101889 rows=993187) (actual time=0.13..445 rows=1e+6 loops=1)
-> **Table scan on products** (cost=101889 rows=993187) (actual time=0.128..398 rows=1e+6 loops=1)
-> Single-row index lookup on members using PRIMARY (id = products.owner_id) (cost=0.25 rows=1) (actual time=0.00982..0.00983 rows=1 loops=10)
-- offset --
-> Limit/Offset: 10/10 row(s) (cost=449504 rows=10) (**actual time=831..831** rows=10 loops=1)
-> Nested loop inner join (cost=449504 rows=993187) (actual time=831..831 rows=20 loops=1)
-> **Sort**: products.product_price DESC, products.owner_id DESC (cost=101889 rows=993187) (actual time=831..831 rows=20 loops=1)
-> Filter: (products.owner_id is not null) (cost=101889 rows=993187) (actual time=0.121..415 rows=1e+6 loops=1)
-> **Table scan on products** (cost=101889 rows=993187) (actual time=0.12..368 rows=1e+6 loops=1)
-> Single-row index lookup on members using PRIMARY (id = products.owner_id) (cost=0.25 rows=1) (actual time=0.00181..0.00182 rows=1 loops=20)
-- cursor --
-> Limit: 10 row(s) (cost=289482 rows=10) (**actual time=888..888** rows=10 loops=1)
-> Nested loop inner join (cost=289482 rows=993187) (actual time=888..888 rows=10 loops=1)
-> **Sort**: products.product_price DESC, products.owner_id DESC (cost=101889 rows=993187) (actual time=888..888 rows=10 loops=1)
-> Filter: (((products.product_price < 999984.00) or ((products.product_price = 999984.00) and (products.id < 673417))) and (products.owner_id is not null)) (cost=101889 rows=993187) (actual time=0.139..438 rows=999990 loops=1)
-> **Table scan on products** (cost=101889 rows=993187) (actual time=0.135..350 rows=1e+6 loops=1)
-> Single-row index lookup on members using PRIMARY (id = products.owner_id) (cost=0.25 rows=1) (actual time=0.0032..0.00321 rows=1 loops=10)
- first-page, offset, cursor 모두
actual time이 비슷비슷하다.- 그 이유는 인덱스가 없기 때문이다. → 인덱스가 없기에 데이터베이스는 정렬된 결과를 몰라 전체 테이블을 스캔하고 정렬하는 과정을 거쳐야한다.
Table scan on porducts→Sort
- 즉,
products테이블 전체를 스캔하고 정렬한 후, 그 값을 가져오는 것이기 때문에 속도에 큰 차이가 없다.
- 그 이유는 인덱스가 없기 때문이다. → 인덱스가 없기에 데이터베이스는 정렬된 결과를 몰라 전체 테이블을 스캔하고 정렬하는 과정을 거쳐야한다.
- 여기서 알 수 있는건 offset이든 cursor든 인덱스 설계가 선행되어야 한다는 점이다.
인덱스를 생성한 후
-- 가격, id 복합 인덱스 작성 --
CREATE INDEX idx_products_price_id ON products(product_price DESC, id DESC);
completed in 1 s 58 ms -- first-page --
-> Limit: 10 row(s) (cost=248298 rows=10) (**actual time=0.676..0.865** rows=10 loops=1)
-> Nested loop inner join (cost=248298 rows=10) (actual time=0.675..0.864 rows=10 loops=1)
-> Filter: (products.owner_id is not null) (cost=0.0172 rows=10) (actual time=0.567..0.745 rows=10 loops=1)
-> **Index scan on** products using idx_products_price_id (cost=0.0172 rows=10) (actual time=0.559..0.737 rows=10 loops=1)
-> Single-row index lookup on members using PRIMARY (id = products.owner_id) (cost=0.25 rows=1) (actual time=0.00972..0.00975 rows=1 loops=10)
-- offset --
-> Limit/Offset: 10/10 row(s) (cost=248299 rows=10) (**actual time=4.8..4.88** rows=10 loops=1)
-> Nested loop inner join (cost=248299 rows=20) (actual time=0.0898..4.88 rows=20 loops=1)
-> Filter: (products.owner_id is not null) (cost=0.0344 rows=20) (actual time=0.0761..4.83 rows=20 loops=1)
-> **Index scan on** products using idx_products_price_id (cost=0.0344 **rows=20**) (actual time=0.0752..4.83 rows=20 loops=1)
-> Single-row index lookup on members using PRIMARY (id = products.owner_id) (cost=0.25 rows=1) (actual time=0.00206..0.00209 rows=1 loops=20)
-- cursor --
-> Limit: 10 row(s) (cost=274833 rows=10) (**actual time=2.38..2.4** rows=10 loops=1)
-> Nested loop inner join (cost=274833 rows=496594) (actual time=2.38..2.4 rows=10 loops=1)
-> Filter: (products.owner_id is not null) (cost=101025 rows=496594) (actual time=2.36..2.36 rows=10 loops=1)
-> **Index range scan** on products using idx_products_price_id over (product_price = 999984.00 AND 673417 < id) OR (999984.00 < product_price < NULL), with index condition: ((products.product_price < 999984.00) or ((products.product_price = 999984.00) and (products.id < 673417))) (cost=101025 rows=496594) (actual time=2.36..2.36 rows=10 loops=1)
-> Single-row index lookup on members using PRIMARY (id = products.owner_id) (cost=0.25 rows=1) (actual time=0.00263..0.00267 rows=1 loops=10)- offset 방식에서 눈에 띄는 점은 index scan 부분에서 20개의 행을 읽어왔고, 이후에는 Limit 조건에 의해서 10개를 버렸다는 점이다.
- DB는 사용자가 원하는 데이터의 시작점을 모르기에, 인덱스를 따라가면서 수를 세어야 한다.
- 즉, 순서상 몇 번째인지는 실제로 읽어봐야 알 수 있기에 OFFSET 조건에 숫자가 커질수록 더 느려진다.
- 반면 cursor 방식은 index range scan을 통해서 시작점을 바로 찾고 그 이후 LIMT 만큼만 조회하기에 성능상 이점을 가져갈 수 있다.
- 즉, 페이지의 깊이와 상관없이 늘 일정한 속도로 조회할 수 있다.
의문점
해당 쿼리들에 대한 실행 계획을 반복적으로 실행해본 결과, 초반에는 실행 속도가 오히려 OFFSET이 더 빠르다가, 어느 순간부터 cursor가 더 빨리지기 시작했다.
offset 10
-- offset --
-> Limit/Offset: 10/10 row(s) (cost=248299 rows=10) (actual time=0.922..1.01 rows=10 loops=1)
-> Nested loop inner join (cost=248299 rows=20) (actual time=0.639..0.994 rows=20 loops=1)
-> Filter: (products.owner_id is not null) (cost=0.0344 rows=20) (actual time=0.521..0.857 rows=20 loops=1)
-> Index scan on products using idx_products_price_id (cost=0.0344 rows=20) (actual time=0.513..0.848 rows=20 loops=1)
-> Single-row index lookup on members using PRIMARY (id = products.owner_id) (cost=0.25 rows=1) (actual time=0.00495..0.00497 rows=1 loops=20)
-- cursor --
-> Limit: 10 row(s) (cost=274833 rows=10) (actual time=1.84..1.86 rows=10 loops=1)
-> Nested loop inner join (cost=274833 rows=496594) (actual time=1.84..1.85 rows=10 loops=1)
-> Filter: (products.owner_id is not null) (cost=101025 rows=496594) (actual time=1.83..1.83 rows=10 loops=1)
-> Index range scan on products using idx_products_price_id over (product_price = 999984.00 AND 673417 < id) OR (999984.00 < product_price < NULL), with index condition: ((products.product_price < 999984.00) or ((products.product_price = 999984.00) and (products.id < 673417))) (cost=101025 rows=496594) (actual time=1.83..1.83 rows=10 loops=1)
-> Single-row index lookup on members using PRIMARY (id = products.owner_id) (cost=0.25 rows=1) (actual time=0.00159..0.00161 rows=1 loops=10)offset 100
-- offset --
-> Limit/Offset: 10/100 row(s) (cost=248308 rows=10) (actual time=0.987..1.03 rows=10 loops=1)
-> Nested loop inner join (cost=248308 rows=110) (actual time=0.191..1.02 rows=110 loops=1)
-> Filter: (products.owner_id is not null) (cost=0.19 rows=110) (actual time=0.165..0.889 rows=110 loops=1)
-> Index scan on products using idx_products_price_id (cost=0.19 rows=110) (actual time=0.153..0.869 rows=110 loops=1)
-> Single-row index lookup on members using PRIMARY (id = products.owner_id) (cost=0.25 rows=1) (actual time=881e-6..914e-6 rows=1 loops=110)
-- cursor --
-> Limit: 10 row(s) (cost=274833 rows=10) (actual time=0.757..0.778 rows=10 loops=1)
-> Nested loop inner join (cost=274833 rows=496594) (actual time=0.756..0.776 rows=10 loops=1)
-> Filter: (products.owner_id is not null) (cost=101025 rows=496594) (actual time=0.741..0.746 rows=10 loops=1)
-> Index range scan on products using idx_products_price_id over (product_price = 999861.00 AND 1508913 < id) OR (999861.00 < product_price < NULL), with index condition: ((products.product_price < 999861.00) or ((products.product_price = 999861.00) and (products.id < 1508913))) (cost=101025 rows=496594) (actual time=0.74..0.744 rows=10 loops=1)
-> Single-row index lookup on members using PRIMARY (id = products.owner_id) (cost=0.25 rows=1) (actual time=0.00255..0.00259 rows=1 loops=10)
offset 500
-- offset --
-> Limit/Offset: 10/500 row(s) (cost=248349 rows=10) (actual time=2.55..2.56 rows=10 loops=1)
-> Nested loop inner join (cost=248349 rows=510) (actual time=0.484..2.54 rows=510 loops=1)
-> Filter: (products.owner_id is not null) (cost=0.902 rows=510) (actual time=0.433..1.96 rows=510 loops=1)
-> Index scan on products using idx_products_price_id (cost=0.902 rows=510) (actual time=0.432..1.92 rows=510 loops=1)
-> Single-row index lookup on members using PRIMARY (id = products.owner_id) (cost=0.25 rows=1) (actual time=943e-6..973e-6 rows=1 loops=510)
-- cursor --
-> Limit: 10 row(s) (cost=274833 rows=10) (actual time=0.897..0.907 rows=10 loops=1)
-> Nested loop inner join (cost=274833 rows=496594) (actual time=0.896..0.906 rows=10 loops=1)
-> Filter: (products.owner_id is not null) (cost=101025 rows=496594) (actual time=0.888..0.89 rows=10 loops=1)
-> Index range scan on products using idx_products_price_id over (product_price = 999473.00 AND 1507279 < id) OR (999473.00 < product_price < NULL), with index condition: ((products.product_price < 999473.00) or ((products.product_price = 999473.00) and (products.id < 1507279))) (cost=101025 rows=496594) (actual time=0.887..0.889 rows=10 loops=1)
-> Single-row index lookup on members using PRIMARY (id = products.owner_id) (cost=0.25 rows=1) (actual time=0.00123..0.00125 rows=1 loops=10)
offset 1000
-- offset --
-> Limit/Offset: 10/1000 row(s) (cost=248400 rows=10) (actual time=7.23..7.25 rows=10 loops=1)
-> Nested loop inner join (cost=248400 rows=1010) (actual time=1.52..7.21 rows=1010 loops=1)
-> Filter: (products.owner_id is not null) (cost=1.84 rows=1010) (actual time=1.47..6.33 rows=1010 loops=1)
-> Index scan on products using idx_products_price_id (cost=1.84 rows=1010) (actual time=1.46..6.27 rows=1010 loops=1)
-> Single-row index lookup on members using PRIMARY (id = products.owner_id) (cost=0.25 rows=1) (actual time=691e-6..716e-6 rows=1 loops=1010)
-- cursor --
-> Limit: 10 row(s) (cost=274833 rows=10) (actual time=0.0677..1.14 rows=10 loops=1)
-> Nested loop inner join (cost=274833 rows=496594) (actual time=0.0667..1.13 rows=10 loops=1)
-> Filter: (products.owner_id is not null) (cost=101025 rows=496594) (actual time=0.0553..1.11 rows=10 loops=1)
-> Index range scan on products using idx_products_price_id over (product_price = 998999.00 AND 1072042 < id) OR (998999.00 < product_price < NULL), with index condition: ((products.product_price < 998999.00) or ((products.product_price = 998999.00) and (products.id < 1072042))) (cost=101025 rows=496594) (actual time=0.0543..1.1 rows=10 loops=1)
-> Single-row index lookup on members using PRIMARY (id = products.owner_id) (cost=0.25 rows=1) (actual time=0.00243..0.00246 rows=1 loops=10)
offset 10000
-- offset --
-> Limit/Offset: 10/10000 row(s) (cost=448641 rows=10) (actual time=894..894 rows=10 loops=1)
-> Nested loop inner join (cost=448641 rows=993187) (actual time=887..894 rows=10010 loops=1)
-> Sort: products.product_price DESC, products.id DESC (cost=101025 rows=993187) (actual time=887..887 rows=10010 loops=1)
-> Filter: (products.owner_id is not null) (cost=101025 rows=993187) (actual time=0.63..384 rows=1e+6 loops=1)
-> Table scan on products (cost=101025 rows=993187) (actual time=0.611..338 rows=1e+6 loops=1)
-> Single-row index lookup on members using PRIMARY (id = products.owner_id) (cost=0.25 rows=1) (actual time=501e-6..522e-6 rows=1 loops=10010)
-- cursor --
-> Limit: 10 row(s) (cost=274833 rows=10) (actual time=0.136..0.559 rows=10 loops=1)
-> Nested loop inner join (cost=274833 rows=496595) (actual time=0.135..0.558 rows=10 loops=1)
-> Filter: (products.owner_id is not null) (cost=101025 rows=496595) (actual time=0.125..0.538 rows=10 loops=1)
-> Index range scan on products using idx_products_price_id over (product_price = 990017.00 AND 775901 < id) OR (990017.00 < product_price < NULL), with index condition: ((products.product_price < 990017.00) or ((products.product_price = 990017.00) and (products.id < 775901))) (cost=101025 rows=496595) (actual time=0.124..0.536 rows=10 loops=1)
-> Single-row index lookup on members using PRIMARY (id = products.owner_id) (cost=0.25 rows=1) (actual time=0.00153..0.00157 rows=1 loops=10)
- offset이 Inedx scan on에서 Table Scan으로 변경되었다.
- 옵티마이저가 10000개를 인덱스로 읽는 것보다 전체 스캔 후 정렬이 더 효율적이라고 판단
offset 500000
-- offset --
-> Limit/Offset: 10/500000 row(s) (cost=448641 rows=10) (actual time=1267..1267 rows=10 loops=1)
-> Nested loop inner join (cost=448641 rows=993187) (actual time=902..1255 rows=500010 loops=1)
-> Sort: products.product_price DESC, products.id DESC (cost=101025 rows=993187) (actual time=902..940 rows=500010 loops=1)
-> Filter: (products.owner_id is not null) (cost=101025 rows=993187) (actual time=0.338..372 rows=1e+6 loops=1)
-> Table scan on products (cost=101025 rows=993187) (actual time=0.332..326 rows=1e+6 loops=1)
-> Single-row index lookup on members using PRIMARY (id = products.owner_id) (cost=0.25 rows=1) (actual time=501e-6..522e-6 rows=1 loops=500010)
-- cursor --
-> Limit: 10 row(s) (cost=274833 rows=10) (actual time=0.859..0.873 rows=10 loops=1)
-> Nested loop inner join (cost=274833 rows=496594) (actual time=0.858..0.871 rows=10 loops=1)
-> Filter: (products.owner_id is not null) (cost=101025 rows=496594) (actual time=0.843..0.847 rows=10 loops=1)
-> Index range scan on products using idx_products_price_id over (product_price = 499771.00 AND 612222 < id) OR (499771.00 < product_price < NULL), with index condition: ((products.product_price < 499771.00) or ((products.product_price = 499771.00) and (products.id < 612222))) (cost=101025 rows=496594) (actual time=0.841..0.844 rows=10 loops=1)
-> Single-row index lookup on members using PRIMARY (id = products.owner_id) (cost=0.25 rows=1) (actual time=0.00195..0.00198 rows=1 loops=10)
offset이 500부터 점점 차이가 보이더니, 1000부터 격차가 확 벌어졌다.
결론
초반에는 성능차가 미미하니, 사용자가 데이터를 어떻게 조회하는지 분석하고 설계하는 것이 좋을것 같다.
-
모바일 환경에서 무한 스크롤이 필요한 경우, cursor를 활용
- 모바일 환경에서는 사용자가 스크롤을 쉽게 내릴 수 있어 깊은 페이지에 쉽게 접근할 수 있다.
-
중간 페이지 탐색이 필요한 경우는 offset을 활용
- 정렬과 필터링을 통해서 원하는 데이터를 페이지의 앞단에 배치한다.
- 쿠팡과 구글과 같이 조회 가능한 최대 페이지를 제한할 경우, 성능 저하를 막을 수 있다.
잘 살고 싶은 사람