Laravel paginate에서 값이 매번 달라진 이유, ORDER BY가 없었다

회사 통계 시스템에서 이상한 이슈가 등록된 적이 있다. 같은 화면을 새로고침하는데 일정 확률로 다른 값이 나온다는 제보였다.

처음에는 프런트엔드에서 데이터 취합을 잘못하는 줄 알았다. 그런데 백엔드에서 같은 쿼리를 반복해서 돌려보니 결과값이 실제로 달라졌다.

의심했던 것들

• 리드 레플리카를 여러 대 쓰는데 복제 지연으로 싱크가 안 맞는 문제
• 쿼리 자체의 문제
• 캐시나 기타 문제

원인: paginate에 ORDER BY가 없었다

결론부터 말하면 paginate를 쓸 때는 order by를 반드시 해줘야 한다.

SQL 표준상 ORDER BY가 없는 SELECT는 행 순서를 보장하지 않는다. 이건 버그가 아니라 명세다. DB는 가장 빠르게 가져올 수 있는 순서로 반환할 자유가 있다.

그런데 paginate()는 내부적으로 LIMIT ... OFFSET ... 을 붙인다. 순서가 보장되지 않는 결과 집합에서 "20번째부터 20건"을 잘라오는 것이므로, 매번 다른 20건이 나올 수 있다.

// 위험: 순서 보장 없음
$rows = Order::where('status', 'paid')->paginate(20);

// 안전
$rows = Order::where('status', 'paid')
    ->orderBy('created_at', 'desc')
    ->orderBy('id', 'desc')
    ->paginate(20);

왜 "일정 확률로" 였는가

항상 틀렸다면 오히려 금방 찾았을 것이다. 확률적으로만 재현된 이유는 몇 가지가 겹쳐서다.

• 실행 계획이 상황에 따라 바뀐다. 옵티마이저가 인덱스 스캔을 고를 때와 풀 스캔을 고를 때 반환 순서가 다르다. 통계 정보가 갱신되면 같은 쿼리도 계획이 바뀔 수 있다.
• 리드 레플리카가 여러 대면 물리적 저장 순서가 다를 수 있다. 어느 노드로 붙느냐에 따라 결과 순서가 달라진다.
• 동시에 쓰기가 일어나면 페이지 경계가 밀린다. 이건 ORDER BY를 넣어도 남는 문제인데, 뒤에서 따로 얘기하겠다.

그래서 개발 환경에서는 재현이 안 되고 운영에서만, 그것도 가끔 터졌던 것이다.

정렬 컬럼이 중복되면 ORDER BY를 넣어도 부족하다

여기서 한 단계 더 들어가야 한다. ORDER BY created_at DESC 만 넣었다고 안심하면 안 된다. 같은 created_at 값을 가진 행이 여러 개면, 그들 사이의 순서는 여전히 미정이다.

배치로 대량 insert된 데이터는 created_at이 초 단위로 같은 경우가 흔하다. 이러면 페이지 경계에 걸친 행들이 왔다 갔다 한다.

// 부족
->orderBy('created_at', 'desc')

// 충분: 유니크 컬럼을 마지막에 붙인다
->orderBy('created_at', 'desc')
->orderBy('id', 'desc')

규칙은 간단하다. 정렬 키의 마지막에는 항상 유니크한 컬럼(보통 PK)을 붙인다. 이걸 tie-breaker라고 부른다.

남는 문제: 페이지를 넘기는 사이의 쓰기

ORDER BY를 제대로 넣어도 OFFSET 방식에는 구조적 한계가 있다. 사용자가 1페이지를 보는 동안 새 데이터가 들어오면, 2페이지에서 이미 본 행을 다시 보게 된다(중복). 반대로 삭제되면 봐야 할 행을 건너뛴다(누락).

실시간으로 데이터가 들어오는 목록이라면 커서 기반 페이지네이션을 검토할 만하다. 이건 별도로 정리한 글이 있으니 참고하시면 된다.

통계 화면이라면 추가로 볼 것

이번 이슈는 정렬 문제였지만, 통계 시스템에서 값이 흔들리는 원인은 하나 더 있다. 리드 레플리카의 복제 지연이다.

쓰기는 마스터에, 읽기는 레플리카에 보내는 구조에서 방금 쓴 데이터를 바로 읽으면 아직 반영이 안 됐을 수 있다. Laravel은 이 상황을 위한 옵션을 제공한다.

// config/database.php
'mysql' => [
    'read'  => ['host' => [...]],
    'write' => ['host' => [...]],
    'sticky' => true,   // 같은 요청 안에서 쓰기 후 읽기는 마스터로
],

sticky를 켜면 한 요청 사이클 안에서 쓰기가 발생한 뒤의 읽기는 마스터로 보낸다. 다만 요청이 끝나면 초기화되므로 만능은 아니다.

정리

• paginate()에는 반드시 orderBy를 명시한다
• ORDER BY 없는 SELECT는 행 순서를 보장하지 않는다. 명세가 그렇다
• 정렬 키 마지막에 유니크 컬럼(PK)을 붙여 tie-breaker로 쓴다
• 운영에서만 확률적으로 재현되는 이유는 실행 계획과 레플리카 때문이다
• 실시간 목록이면 커서 기반도 검토
• 통계 화면에서 값이 흔들리면 복제 지연(sticky)도 같이 확인