SQL ORDER BY 정렬과 페이징 - NULL 정렬 위치, LIMIT OFFSET FETCH (SQL 초급 3단원)

이 단원에서 배우는 것

2단원까지 오면서 필요한 행만 골라내는 데까지는 왔다. 그런데 골라낸 행이 어떤 순서로 나오는지는 아직 아무도 보장해 주지 않았다. 1단원에서 DISTINCT 결과를 보며 "순서는 보장되지 않는다"고 넘어갔던 그 부분이다. 이번 단원에서 ORDER BY 로 순서를 확정하고, 그 위에 "앞에서 N건만" 같은 행 제한을 얹어 목록 화면의 페이징을 만든다. 처리 순서에서 ORDER BY 가 마지막이라는 점이 여기서 결정적으로 쓰인다.

• 여러 컬럼으로 정렬하고, NULL 이 앞뒤 어디로 가는지 DB별로 구분한다.
• LIMIT·OFFSET·FETCH FIRST 로 행을 제한하고, 네 DB 의 문법 차이를 정리한다.
• OFFSET 페이징이 왜 뒤로 갈수록 느려지는지 알고, 키셋 페이징으로 바꾼다.

개념

관계형 데이터베이스의 테이블은 집합이다. 집합에는 순서가 없다. 그래서 ORDER BY 를 붙이지 않은 조회의 행 순서는 문법적으로 아무 의미가 없다. 지금 넣은 순서대로 나오는 것처럼 보이더라도 그건 우연이다. 인덱스가 하나 생기거나, 실행 계획이 바뀌거나, 병렬 처리가 켜지면 순서가 달라진다. "어제까지 잘 나오던 목록이 오늘 순서가 뒤집혔다"는 장애의 절반은 ORDER BY 를 안 쓴 쿼리에서 나온다.

행 제한도 마찬가지다. "상위 10건"이라는 말 자체가 정렬이 정해진 다음에만 뜻이 있다. 그래서 ORDER BY 없는 LIMIT 은 "아무 10건"과 같다. SQL Server 가 OFFSET ... FETCH 를 쓸 때 ORDER BY 를 문법적으로 강제하는 이유도 이것이다.

표준 SQL 문법과 예제

ORDER BY

-- 연봉 내림차순, 같으면 사번 오름차순
SELECT emp_id, emp_name, salary
FROM   emp
ORDER BY salary DESC, emp_id ASC;

ASC 가 기본값이라 생략해도 된다. 방향은 컬럼마다 따로 지정한다. ORDER BY a, b DESC 는 a 오름차순, b 내림차순이지 둘 다 내림차순이 아니다.

1단원에서 정리한 처리 순서 덕분에, ORDER BY 에서는 SELECT 절의 별칭을 쓸 수 있다. WHERE 에서는 못 쓰던 그 별칭이다.

SELECT emp_name, salary * 1.05 AS new_salary
FROM   emp
ORDER BY new_salary DESC;   -- 별칭 사용 가능

ORDER BY 2 DESC 처럼 SELECT 절의 몇 번째 컬럼인지를 번호로 쓸 수도 있다. 동작은 하지만 쓰지 않는 편이 낫다. 나중에 SELECT 절에 컬럼 하나가 끼어들면 정렬 기준이 조용히 다른 컬럼으로 옮겨간다.

NULL 은 어디로 정렬되는가

SELECT emp_name, bonus
FROM   emp
ORDER BY bonus, emp_id;

MySQL 에서의 결과다.

+----------+---------+
| emp_name | bonus   |
+----------+---------+
| 김유신   |    NULL |
| 을지문덕 |    NULL |
| 윤봉길   |    NULL |
| 홍범도   |    NULL |
| 김구     |    NULL |
| 신채호   |  200.00 |
| 이순신   |  300.00 |
| 강감찬   |  500.00 |
| 안중근   |  900.00 |
| 유관순   | 1000.00 |
+----------+---------+
10 rows in set (0.00 sec)

같은 쿼리를 PostgreSQL 이나 Oracle 에서 돌리면 NULL 이 맨 뒤로 간다. 표준 SQL 은 어느 쪽이 기본인지 정하지 않았고, 대신 명시하는 문법을 뒀다.

-- 표준 문법. PostgreSQL 과 Oracle 에서 동작한다
SELECT emp_name, bonus FROM emp ORDER BY bonus NULLS LAST, emp_id;

-- MySQL 8.0 과 MariaDB 에는 이 구문이 없다. 불리언을 앞에 세워 우회한다
-- (bonus IS NULL) 은 NULL 인 행에서 1, 아닌 행에서 0 이다
SELECT emp_name, bonus FROM emp ORDER BY (bonus IS NULL), bonus, emp_id;

행 제한과 페이징

10건씩 3페이지를 보여준다면 21번째 행부터 10건이 필요하다. OFFSET 은 "건너뛸 행 수"이므로 (페이지번호 - 1) × 페이지크기가 된다.

-- MySQL / MariaDB / PostgreSQL
SELECT emp_id, emp_name, salary
FROM   emp
ORDER BY salary DESC, emp_id
LIMIT 10 OFFSET 20;

-- 표준 SQL. PostgreSQL, Oracle 12c 이상, SQL Server 2012 이상
SELECT emp_id, emp_name, salary
FROM   emp
ORDER BY salary DESC, emp_id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

DB별 차이

항목	MySQL / MariaDB	PostgreSQL	Oracle	SQL Server
ASC 정렬에서 NULL 위치	맨 앞	맨 뒤	맨 뒤	맨 앞
NULLS FIRST / LAST	없음. (컬럼 IS NULL) 을 앞세워 우회	지원	지원	없음. CASE 로 우회
행 제한 문법	LIMIT n OFFSET m	LIMIT n OFFSET m 과 표준 FETCH 둘 다	12c 이상 OFFSET ... FETCH, 11g 이하는 ROWNUM	TOP n 또는 2012 이상 OFFSET ... FETCH
OFFSET·FETCH 에 ORDER BY 필수	아니오	아니오	아니오	예. 없으면 문법 오류
한글·대소문자 정렬 기준	컬럼 콜레이션	DB 로케일(COLLATE 지정 가능)	NLS_SORT	컬럼 콜레이션

Oracle 11g 이하에서 ROWNUM 으로 페이징할 때는 반드시 정렬을 서브쿼리 안에서 끝내야 한다. ROWNUM 은 정렬 전에 매겨지기 때문에 WHERE ROWNUM <= 10 ORDER BY salary DESC 라고 쓰면 아무 10건을 뽑아서 정렬한 결과가 나온다.

-- Oracle 11g 이하의 올바른 형태
SELECT * FROM (
    SELECT emp_id, emp_name, salary FROM emp ORDER BY salary DESC, emp_id
) WHERE ROWNUM <= 10;

실무에서 자주 틀리는 것

1. 정렬 기준이 유일하지 않아 페이지 경계에서 행이 새거나 겹친다

ORDER BY salary DESC LIMIT 10 OFFSET 0 으로 1페이지를, 같은 정렬로 OFFSET 10 을 줘서 2페이지를 가져온다고 하자. 연봉이 같은 사원이 여럿이면 두 번의 실행에서 그들 사이의 순서가 달라질 수 있다. 그러면 어떤 사원은 1·2페이지에 두 번 나오고, 어떤 사원은 어느 페이지에도 안 나온다. 데이터가 누락됐다는 문의는 대개 여기서 시작된다.

해결은 간단하다. 정렬 목록의 마지막에 유일한 값(대개 기본키)을 붙인다. 위 예제들이 전부 ORDER BY salary DESC, emp_id 로 끝나는 이유다.

2. OFFSET 이 커질수록 느려지는 것을 방치한다

LIMIT 10 OFFSET 100000 은 100,010 행을 읽어서 앞의 100,000 행을 버리는 동작이다. 페이지가 뒤로 갈수록 선형으로 느려진다. 무한 스크롤이나 배치 처리처럼 순차적으로 넘기는 경우라면, 마지막으로 본 값을 기억했다가 그 다음부터 읽는 방식으로 바꾼다. 키셋 페이징 또는 seek 방식이라고 부른다.

-- 1페이지
SELECT emp_id, emp_name, salary
FROM   emp
ORDER BY emp_id
LIMIT 10;

-- 다음 페이지: 직전 페이지의 마지막 emp_id 가 1010 이었다면
SELECT emp_id, emp_name, salary
FROM   emp
WHERE  emp_id > 1010
ORDER BY emp_id
LIMIT 10;

이 방식은 인덱스에서 시작 위치를 바로 찾으므로 몇 페이지째든 속도가 같다. 대신 "37페이지로 점프" 같은 임의 접근은 안 된다. 화면 성격에 따라 고른다.

3. 숫자를 문자열 컬럼에 넣어 두고 정렬한다

주문번호나 코드가 VARCHAR 인데 값이 숫자라면 정렬은 사전순이다. 2, 10, 9 는 '10', '2', '9' 순으로 나온다. 숫자로 정렬하려고 ORDER BY CAST(code AS UNSIGNED) 를 붙이면 정렬 대상이 계산식이 되어 인덱스 정렬을 못 쓰고 매번 파일 정렬을 한다. 애초에 숫자는 숫자 타입으로 저장한다. 이 이야기는 4단원에서 이어서 다룬다.

4. 정렬 비용을 공짜로 여긴다

ORDER BY 는 인덱스 순서와 일치하면 공짜지만, 아니면 결과 전체를 메모리나 임시 파일에 놓고 정렬한다. MySQL 실행 계획의 Using filesort, PostgreSQL 의 Sort (external merge Disk: ...) 가 그 신호다. 목록 화면에서 자주 쓰는 정렬 조합이 있다면 그 순서대로 복합 인덱스를 만드는 것이 가장 확실한 해법이다.

스스로 확인하기

1. 연봉이 높은 순으로 상위 3명의 이름과 연봉을 조회하라. 연봉이 같을 때도 결과가 흔들리지 않게 하라.
2. 보너스를 오름차순으로 정렬하되 보너스가 없는 사원을 맨 뒤에 놓아라. MySQL 8.0 기준으로 작성하라.
3. 입사일이 늦은 순으로 정렬한 목록에서 4번째부터 3명을 조회하라(2페이지, 페이지당 3건).

-- 1
SELECT emp_name, salary
FROM   emp
ORDER BY salary DESC, emp_id
LIMIT 3;

-- 2
SELECT emp_name, bonus
FROM   emp
ORDER BY (bonus IS NULL), bonus, emp_id;
-- PostgreSQL / Oracle 이라면 ORDER BY bonus NULLS LAST, emp_id

-- 3
SELECT emp_name, hire_date
FROM   emp
ORDER BY hire_date DESC, emp_id
LIMIT 3 OFFSET 3;