[DB] ORDER BY를 했을 때, 정렬 결과가 달라지는 현상

사용하고 있는 DB는 postgreSQL이지만, 다른 DB에서도 다 똑같이 적용된다고 생각합니다.

 

 

문제

ORDER BY와 LIMIT를 함께 사용했을 때, LIMIT의 수가 바뀌면 ORDER BY 정렬 결과도 함께 달라지는 현상이 있었습니다.

예를 들어, LIMIT 30과 LIMIT 31의 ORDER BY 결과가 달라지는 것입니다.

 

실행계획의 차이도 없고 문제의 원인을 알 수 없어서, 구글에서 검색하다가 레딧에서 같은 현상을 겪는 사람을 발견했습니다.

그래서 레딧의 질문을 기준으로 이 현상에 대해 이야기해 보겠습니다.

 

 

 

레딧에서 발견한 동일한 문제

https://www.reddit.com/r/PostgreSQL/comments/ni2l9u/why_is_a_query_with_limit_returning_results_in_a/

Why is a query with LIMIT returning results in a different order as the same query without limit?

글쓴이의 문제 쿼리는 아래와 같습니다.

이 쿼리에서 맨 아래의 LIMIT 20을 넣고 실행한 결과와 넣지 않고 실행한 결과가 달랐다고 했습니다.

 

문제의 쿼리

SELECT
  users.id,
  users.name
FROM
  users
WHERE
  users.deleted_at IS NULL
  AND role = 'teacher'
ORDER BY
  users.name ASC
LIMIT
  20
;

 

문제의 결과

LIMIT 20을 포함할 경우 (좌) / LIMIT 20을 포함하지 않을 경우 (우)

쿼리를 보면 ORDER BY를 users.name으로 했지만, 9번 행의 id가 같지 않음을 볼 수 있습니다.

위의 경우 LIMIT 유무의 차이였지만, LIMIT 수가 달라져도 위와 같은 현상이 일어납니다.

 

어떻게 LIMIT가 달라지는 것 때문에 ORDER BY의 결과가 달라질까요?

 

 

원인

이유를 알려준 레딧의 댓글

ORDER BY를 할 때, 기준이 되는 컬럼이 고유값을 가지고 있지 않으면, DB 또는 옵티마이저에 의해 최적의 방법에 따라 결과가 달라질 수 있습니다.

 

위의 LIMIT 20 유무 차이를 보여줄 때, ORDER BY의 기준이 ID가 아니라 users.name이었습니다.

하지만 users.name은 똑같은 값을 가지는 행이 여러 있고, 같은 값은 명확하게 정렬할 수 없기 때문에 옵티마이저에 의해 최적의 방법으로 정렬되는 것입니다. 이것이 우리에겐 임의로 정렬되는 것처럼 보이는 것입니다.

 

그렇다면 어떻게 해야 LIMIT에 따라 달라지지 않고 똑같은 결과를 얻을 수 있을까요?

 

 

정답

ORDER BY 마지막에 고유값을 가지는 컬럼을 추가합니다.

아래의 쿼리처럼 users.id를 추가하여 고유값을 가지는 컬럼을 추가하면 DB에 명시적으로 ORDER BY 기준을 정해줄 수 있어 결과가 달라지는 현상을 막을 수 있습니다.

SELECT
  users.id,
  users.name
FROM
  users
WHERE
  users.deleted_at IS NULL
  AND role = 'teacher'
ORDER BY
  users.name ASC, 
	users.id -- 고유값을 가지는 users.id를 추가함
LIMIT
  20
;