목록 순서가 볼 때마다 달랐다

상품 목록에서 2페이지로 넘어가면 1페이지에 있던 상품이 또 나온다는 문의를 받았다. 같은 조건으로 조회하는데 순서가 달랐다.

Open Table of contents

정렬 기준 값이 대부분 같았다
끝에 유일 키를 뒀다
다른 목록도 확인했다
페이지 넘김 방식
정리

정렬 기준 값이 대부분 같았다

목록 쿼리는 sort_no 하나로 정렬하고 있었다. 그 값의 분포를 세어 봤다.

SELECT sort_no, COUNT(*) FROM product GROUP BY sort_no ORDER BY COUNT(*) DESC LIMIT 3;

sort_no  count
      0    3820
     10      42
     20      31

대부분이 0 이었다. 정렬 기준이 사실상 없는 것과 같았다.

매뉴얼이 이걸 그대로 적어 뒀다. ORDER BY 컬럼 값이 같은 행이 여럿이면 서버는 그 행들을 어떤 순서로든 돌려줄 수 있고 실행 계획에 따라 다르게 돌려주기도 한다는 것이다. 그 3,820건이 조회할 때마다 다른 순서로 나올 수 있다.

실행 계획을 바꾸는 것 중 하나가 LIMIT 이라는 대목도 있었다. 같은 ORDER BY 쿼리라도 LIMIT 이 붙고 안 붙고에 따라 순서가 달라질 수 있다. 페이지를 넘기는 화면이 정확히 그 상황이다.

끝에 유일 키를 뒀다

두 번째 정렬 컬럼으로 상품 번호를 붙였다.

ORDER BY sort_no ASC, product_no ASC

sort_no 가 같으면 product_no 로 정하고 그 값은 유일하므로 순서가 확정된다. 매뉴얼도 LIMIT 유무와 무관하게 같은 순서를 보장하려면 ORDER BY 에 컬럼을 더해 결정적으로 만들라고 안내한다.

두 번째 정렬도 중복될 수 있는 컬럼이면 여전히 불안정하다는 것이 요점이었다. 등록일로 두 번째를 삼으면 같은 초에 등록된 것들이 또 흔들린다. 정렬 목록의 마지막에 유일한 컬럼을 둬야 확정된다.

다른 목록도 확인했다

grep -rn 으로 같은 문제가 있는 자리를 찾았다.

$ grep -rn "order_by\|ORDER BY" --include=*.php application/models/ | wc -l
      47

47곳 중 유일 키로 끝나는 것은 12곳이고 31곳이 유일하지 않은 컬럼으로 끝났으며 4곳은 ORDER BY 가 아예 없었다. 정렬이 없으면 저장 순서로 나오는 것처럼 보이지만 그것도 보장이 아니다. 35곳을 고쳤다.

고친 뒤에는 같은 쿼리를 두 번 돌려 결과가 같은지 봤다.

SELECT GROUP_CONCAT(product_no ORDER BY NULL) FROM (
  SELECT product_no FROM product WHERE use_yn='Y'
  ORDER BY sort_no, product_no LIMIT 20
) t;

GROUP_CONCAT 으로 한 줄로 만들어 두 번 돌려 같은 문자열이 나오면 안정적이다. ORDER BY 를 뺀 쿼리로 같은 시험을 하면 결과가 달라지므로 그게 대조군이 됐다. 대조군이 같은 값을 냈다면 이 확인은 아무것도 가르지 못한 것이다.

페이지 넘김 방식

정렬을 확정해도 남는 문제가 있었다. OFFSET으로 페이지를 넘기면 1페이지를 본 뒤 새 상품이 등록됐을 때 2페이지에서 1페이지 마지막 상품이 다시 나온다.

SELECT * FROM product
WHERE use_yn = 'Y'
  AND (sort_no, product_no) > (10, 1204)
ORDER BY sort_no ASC, product_no ASC
LIMIT 20;

마지막으로 본 행의 정렬 값을 기준으로 다음을 가져오면 자료가 늘어도 안 겹친다. (sort_no, product_no) > (10, 1204) 처럼 두 컬럼을 묶어 비교하는 것이다.

다만 페이지 번호로 바로 가는 것은 안 된다. 다음 버튼만 있는 화면에는 이 방식이 낫고 페이지 번호가 필요하면 OFFSET 을 쓴다. 이 목록은 페이지 번호가 있어서 OFFSET 을 두고 ORDER BY 만 확정했다.

OFFSET 은 값이 클수록 느려진다는 것도 같이 봤다. LIMIT 10000, 20 이면 만 개를 읽고 버린 뒤 20개를 준다. 상품이 4천 개라 여기서는 문제가 안 됐지만 로그 표처럼 큰 곳은 앞의 방식으로 바꿨다.

정리

ORDER BY 는 같은 값끼리의 순서를 보장하지 않는다
LIMIT 이 실행 계획을 바꿔 붙고 안 붙고에 따라 순서가 달라진다
정렬 기준 값이 대부분 같으면 정렬이 없는 것과 비슷하다
ORDER BY 끝에 유일 키를 둬서 순서를 확정한다
ORDER BY 가 아예 없으면 저장 순서처럼 보여도 보장이 아니다
자료가 바뀌면 OFFSET 방식은 겹치거나 빠진다
(sort_no, product_no) > (10, 1204) 로 마지막 행 기준을 쓴다
LIMIT 10000, 20 은 만 개를 읽고 버린다
GROUP_CONCAT 으로 두 번 돌려 같은지 보고 대조군을 만든다