2022-10-06-TIL

2022-10-06-TIL

Today I Learned

Pagination with cursor in MySQL

MySQL에서 흔히 쓰는 페이지네이션은 LIMIT offset, row_count다. 첫 번째 인자는 몇 번째 행부터 돌려줄지(0부터 시작), 두 번째 인자는 최대 몇 행을 돌려줄지를 뜻한다. LIMIT 5, 10은 6번째부터 15번째 행이다(MySQL: SELECT Statement).

문제는 offset이 커질수록 느려진다는 점이다. 데이터베이스는 건너뛸 행도 정렬된 순서대로 읽은 뒤 버려야 하므로, 뒤쪽 페이지일수록 앞에서 버리는 행이 늘어난다. 또 페이지를 넘기는 사이에 새 행이 끼어들면 같은 행이 다음 페이지에 다시 나오는 현상도 생긴다. Markus Winand는 이를 대신하는 방법을 seek method 또는 keyset pagination이라고 부른다(Use The Index, Luke: No Offset).

커서 기반 페이지네이션은 “몇 개를 건너뛸지” 대신 “마지막으로 본 값 다음부터”를 조건으로 준다.

SELECT id, title, created_at
FROM post
WHERE id < ?          -- 이전 페이지 마지막 id
ORDER BY id DESC
LIMIT 20;

id에 인덱스가 있으면 인덱스에서 바로 시작 위치를 찾으므로 페이지가 뒤로 가도 비용이 거의 같다. MySQL도 ORDER BY를 인덱스로 처리할 수 있으면 LIMIT만큼 찾은 뒤 바로 멈춘다고 설명한다(LIMIT Query Optimization).

정렬 기준이 created_at처럼 중복될 수 있는 컬럼이면 커서가 하나로 정해지지 않는다. 같은 값을 가진 행끼리는 순서가 실행 계획에 따라 달라질 수 있어서, MySQL 문서도 정렬을 결정적으로 만들려면 ORDER BY에 컬럼을 더 넣으라고 한다. 그래서 보통 고유한 id를 두 번째 기준으로 붙인다. 이때 행 생성자 비교 (created_at, id) < (?, ?)를 쓸 수 있지만, MySQL 문서는 행 생성자가 인덱스 접두사를 덮지 못하면 옵티마이저가 인덱스를 덜 쓴다고 경고하고 OR로 풀어 쓴 형태를 권한다(Row Constructor Expression Optimization).

WHERE created_at < ?
   OR (created_at = ? AND id < ?)
ORDER BY created_at DESC, id DESC
LIMIT 20;

단점도 있다. 커서 방식은 “다음 페이지”와 “이전 페이지”로만 이동하기 쉽고, 임의의 N번째 페이지로 바로 가기는 어렵다. 무한 스크롤에는 잘 맞고, 페이지 번호가 필요한 관리자 화면에는 offset 방식이 여전히 편하다.

마지막 링크는 UPDATE ... LIMIT에 관한 것이다. 단일 테이블 UPDATE는 ORDER BY와 LIMIT을 지원해서 대량 갱신을 일정 개수씩 나눠 실행할 수 있다. 여러 테이블을 조인하는 UPDATE에서는 둘 다 쓸 수 없다(MySQL: UPDATE Statement).

• https://bojithapiyathilake.medium.com/pagination-offset-vs-cursor-in-mysql-92cbf1a02cfa
• https://stackoverflow.com/questions/38017054/mysql-cursor-based-pagination-with-multiple-columns
• https://velog.io/@minsangk/%EC%BB%A4%EC%84%9C-%EA%B8%B0%EB%B0%98-%ED%8E%98%EC%9D%B4%EC%A7%80%EB%84%A4%EC%9D%B4%EC%85%98-Cursor-based-Pagination-%EA%B5%AC%ED%98%84%ED%95%98%EA%B8%B0
• https://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow/
• https://stackoverflow.com/questions/6289729/mysql-update-query-with-limit

참고한 자료외부 출처 5

외부 출처

• MySQL 8.0 Reference Manual: SELECT StatementMySQL Docs
• MySQL 8.0 Reference Manual: LIMIT Query OptimizationMySQL Docs
• MySQL 8.0 Reference Manual: Row Constructor Expression OptimizationMySQL Docs
• MySQL 8.0 Reference Manual: UPDATE StatementMySQL Docs
• No OffsetUse The Index, Luke