Backend Developer

1 분 소요

서브쿼리의 Order by는 왜 무시되는걸까요?

1.서론

커서기반페이징을 구현하면서 하나 맞췄던 패턴은 다음과 같다.

  • After 커서로 접근한 요청이라면 time desc, idx asc limit N
  • Before 커서로 접근한 요청이라면 time asc, idx desc limit N

여기서 Before의 경우에는 위의 쿼리를 서브쿼리로 취급하여 Reverse해주는 메인(외부)쿼리로 감싸준다는 특징이 있다.

왜 외부쿼리로 감쌀까?

  • After의 기준인 time desc, idx asc가 API의 기본 정렬 스펙이여서 Before도 동일하게 맞춰 줄 필요가 있었다
    • 예를들어 After의 결과 순서대로 5,6,7,8이 출력된다면 Before의 결과도 4,3,2,1이 아니라 1,2,3,4이여야 하기 때문이다.

물론 Before의 결과를 받는 어플리케이션단 코드에서 Reverse해주는 방법도 있지만,

최대한 쿼리선에서 순서보장까지 받고싶었다.

2.본론

2.1.현상

그래서 결과적으로 외부쿼리와 서브쿼리가 존재하는 구조가 만들어지게되는데,

나는 테스트를 위해 쿼리를 단순한버전->복잡한버전(요구사항을 충족하는)으로 개선시켜가며 한번씩 테스트해보는 경향이 있으므로,

서브쿼리에 limit이 잡히기 전 order by만 존재하는, 마치 아래의 쿼리를 테스트하는 단계에 들어서게되었다.

select b.*
from (
  select a.*
  from member
  order by time asc, idx desc #limit N은 아직 제외된 상황
) a
join member b on a.idx = b.idx
order by time desc, idx asc

그 결과,

놀랍게도, time과 idx의 order by 절, 즉 아래의 구문이 아예 작동을 안하는 결과를 받을 수 있었다.

order by time asc, idx desc #limit N은 아직 제외된 상황

2.2.원인

생각보다 조금 더 복잡한것같다.

원인은 크게 2가지의 영향을 받는것 같다.

(2가지로 나눠놨지만 크게 다른 이유는 아니라고생각한다)

2.2.1.서브쿼리는 테이블이기 때문이다

서브쿼리 역시 한 종류의 테이블이다. (힌트 : 쿼리의 결과를 하나의 테이블데이터 처럼 활용하고 싶어서 우리는 서브쿼리를 사용한다)

-> 테이블(서브쿼리)은 SQL 표준에 따라 순서가 지정되지 않은 행 집합이다.

-> 테이블(서브쿼리)의 행은 특정 순서로 표시되지 않는다.

-> 따라서 서브쿼리도 order by가 동작하지않는것이 맞다

(참조 : https://mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored/)

2.2.2.최적화 프로그램이 효율성을 따지기위해서 order by 를 무시했다.

일반적으로 서브쿼리의 order by는 메인쿼리에 영향을 주지 않는다.

생각해보자면,

서브쿼리를 사용할때는 서브쿼리의 결과로 받는 데이터들을 메인쿼리에서 활용하기위해 사용하지

그 서브쿼리의 결과로 받은 데이터의 순서가 중요할때는 없었던것같다.

따라서 Mysql최적화 프로그램은 아래 세 이유로 서브쿼리의 Order by를 무시하는것 같다

  • 데이터 정렬 오버헤드
  • 불필요한 작업
  • 최적화 어려움

3.결론

3.1.해결방안 - 1

따라서 테이블(서브쿼리)의 정렬을 위해서이므로

Order By는 메인(외부)쿼리에 배치되어야 한다.

그러면 정렬된 데이터를 출력하게 된다.

3.2.해결방안 - 2

서브쿼리에 order by를 해줌과 동시에 limit을 걸어주면 정렬이된 데이터를 받을 수 있다.

이건 목차 2.2.2에 나타난 최적화 프로그램과 연관이있는데,

limit를 사용하게되면 서브쿼리 데이터의 크기를 제한하게되므로

**데이터를 이전보다(limit가 없는) 효율적으로 처리할 수 있게되기 때문에 order by도 무시하지않고 동작하게 된다. **

참고