페이징(Paging)에 대한 이해 - (3) LIMIT 와 TOP 을 이용한 게시물 가져오기

2019/01/21 - [Programming/기타] - 페이징(Paging)에 대한 이해 - (2) ROW NUMBER 을 이용한 게시물 가져오기

이번에는 MySQL 이나 PostgreSQL 등에서 사용하는 LIMIT 와 MS SQL Server 의 예전 페이징 기법인 TOP 에 대해서 알아보겠습니다.

먼저 LIMIT 는 게시물을 가져오는데 최적의 방법을 제공합니다. 하지만 동일한 LIMIT 라는 이름이라도 사용방법은 사실 좀 다릅니다.

앞선 글에서 ROW NUMBER 을 이용하는 방식에서 설명 드렸듯이 순차적으로 내용을 정렬한 뒤 여기에 번호를 붙여서 번호 범위를 이용해서 게시물을 가져온다고 하였습니다. 하지만, 이렇게 번호를 붙이는 것은 LIMIT 와 같은 기능을 가지고 있지 않을 때의 이야기입니다. LIMIT 에서는 정렬된(order by 된) 게시물의 범위를 지정해 해당 게시물만 가져오는 기능이 있고, 번호를 부여한 다음 그 번호를 이용해 다시 비교를 할 필요가 없기 때문에 ROW NUMBER 보다 속도가 빠릅니다.

MySQL 에서는 다음과 같이 countPage = 10 일 때 3 페이지의 게시물을 가져올 수 있습니다.

select id, name, content, createdate
from board
order by createdate
limit (3 - 1) * 10, 10

MySQL 의 LIMIT 은 두 개의 매개변수를 가지는데, 첫번째 매개변수는 시작 위치, 두번째 매개변수는 가져올 게시물 수 입니다. 그래서 20 이후의 게시물 10 개를 가져오게 됩니다. 주의할 것은 매개변수를 1 개만 가질 수도 있는데, 이 때에는 통상적인 것처럼 첫번째 매개변수만 사용하는 것이 아니라 두번째 매개변수만 사용한다는 것입니다. 그러므로 limit 10 이라고 하면 limit 0, 10 와 동일한 것이 된다는것입니다.

PostgreSQL 의 LIMIT 은 조금 다릅니다.

select id, name, content, createdate
from board
order by createdate
limit 10 offset (3 - 1) * 10

offset 이라는 것이 추가되고, 매개변수의 위치가 바뀌었습니다. MySQL 와 마찬가지로 offset 은 생략 가능합니다. 단지 순서가 다르고 사용법이 조금 다를 뿐, 동일한 기능입니다.

위 예제에서 보셨듯이 LIMIT 는 게시물을 가져올 때 최적의 성능과 최소한의 쿼리를 이용할 수 있다는 장점이 있습니다.

여담으로, 어떤 게시판 형태들을 보면 화면상에 ID(Index, Sequence) 대신에 화면상에 출력될 순서대로 번호를 보여주는 경우들이 있습니다. 직관적으로 게시물의 갯수 등을 파악할 때 용이합니다. 이 경우 해당 게시물에 마우스를 올려 링크 주소를 보면 화면에 표시되는 번호와 실제 이용에 이용하는 id 가 다른 번호를 가지고 있는 경우가 있습니다.

당연히 ROW NUMBER 을 이용할 때에는 순차적으로 번호를 부여한 뒤 가져오기 때문에 이 값을 이용하면 됩니다. 하지만, LIMIT 의 경우 순차적으로 번호를 부여한 것이 없기 때문에 프로그램에서 총 페이지와 현재 페이지 등을 이용해서 번호를 부여할 수 있습니다. 하지만 MySQL 등에서도 ROW NUMBER 을 부여하는 기능을 흉내낼 수 있는데, 게시물 뿐만 아니라 계층구조(오라클의 connect by 같은)을 만들 때도 이용할 수 있습니다. 방법은 다음과 같습니다.

select @ROWNUM := @ROWNUM + 1 as rnum, id, name, content, createdate
from board, (select @ROWNUM := 0) A
order by createdate
limit (3 - 1) * 10, 10

바로 변수를 이용해서 처리하는 방식입니다. 물론 총 게시물 수를 먼저 조회할 것이기 때문에 초기값을 0 이 아닌 전체 게시물 수로, 1 씩 증가시키는 대신 1 씩 감소를 시키는 형태로 변경하면 역순으로 번호를 발급할 수도 있습니다.

그럼 PostgreSQL 은???

PostgreSQL 에는 ROW NUMBER 발급을 해주는 row_number() 가 있습니다. 게시물 쪽에서는 모든 기능이 다 있는 것은 PostgreSQL 이라고 볼 수도 있겠네요. ^^;;;

오라클이나 MS SQL Server 에서도 최근에 OFFSET FETCH 라는 기능이 추가되어(오라클 12c 이상, MS SQL Server 2012 이상) LIMIT 와 같은 기능을 수행합니다. 하지만 성능 테스트를 진행해보지 못해서...들리는 말에 의하면 MS SQL Server 는 성능이 실망스럽다고 하네요.

마지막으로 TOP 방식에 대해서 알아보겠습니다. 사실 TOP 방식은 그냥 이론적으로만 알고 계시기만 해도 됩니다. 사실 TOP 이라는 키워드는 MS SQL Server 에서 제공하는 것이고, MS SQL Server 의 근간이 되는 데이터베이스도 사실 유사한 기능으로 페이징을 제공합니다.

그런데 MS SQL Server 2005 부터 row_number() 제공하면서 이 TOP 을 이용한 게시물 가져오기는 쓰지 않게 되었습니다. 성능 상의 문제도 있고, 사실 좀 멋진 방법은 아니거든요. 그래서 이번에는 그 중 대표적인 두 가지 방식만 설명하겠습니다.

SQL Server 6, 7 을 거쳐 2000 이 주로 쓰이던 시절까지도 사실 게시물이 몇십만 건씩 쌓여있는 게시판이나 업무 시스템 등은 없다고 해도 무방했습니다. 그래서 초기에 나온 페이징 방법은 그다지 좋지 못한 방법이었습니다. 흔히 NOT IN 방식이라고 표현하는데, ROW NUMBER 방식의 최외각 부등호/MySQL 의 첫번째 매개변수/PostgreSQL 의 offset 에 해당하는 제외 기능(SKIP 이라고 표현하겠습니다)을 구현하기 위해 NOT IN 을 썼습니다. 그런데 이게 성능이 별로 안좋습니다.

원리는 매우 간단합니다. totalCount = 25, countPage = 10, page = 2 인 경우라면 정렬을 한 뒤 10 까지의 게시물을 제외하고 상위 10 개의 게시물을 가져오는 것입니다.

select top 10 id, name, content, createdate
from board
where id not in (
    select top (2 - 1) * 10 id
    from board
    order by createdate)
order by create date

아직도 오래된 글들을 보면 이 방식을 많이 소개하고 있을 겁니다. 문제는 NOT IN 이 성능이 꽤나 나쁘다는 것입니다. 그래서 후에 IN 을 이용한 방식도 나왔습니다만, 저는 주로 TOP 을 두 번 중첩해서 쓰는 방법을 이용했었습니다. 물론 성능은 여전히 안좋습니다.

TOP 을 두 번 이용하는 방식은 LIMIT 와 조금 비슷합니다. order by 을 이용해서 뒤집는 것인데, 인덱스 등을 이용할 수 없기 때문에 성능은 좋지 않습니다.

select top 10 A.id, A.name, A.content, A.createdate
from (
    select top 2 * 10 id, name, content, createdate
    from board
    order by createdate) A
order by createdate desc

2 페이지를 가져오기 위해 1 ~ 2 페이지의 게시물을 가져온 뒤(20개, 2 * 10) 이 결과를 뒤집어서 마지막 10 개(=countPage)을 가져오는 것이죠. 원리상 크게 어렵지는 않습니다. 실제 화면에 출력해 줄 때에는 ResultSet 이나 List 에서 역순으로 꺼내오기만 하면 됩니다.

하지만, 이 쿼리는 여기서 끝내면 안됩니다. 왜냐하면, 위 예제에서 3 페이지, 즉 마지막 페이지를 가져올 때에 문제가 생기기 때문입니다. 총 게시물이 25 개이기 때문에 3 페이지는 21 ~ 25 까지의 5 개의 게시물을 가져와야 합니다. 하지만, 위 쿼리대로라면 top 30 을 해도 실제로는 25 개의 게시물만 가져오기 때문에 외각의 top 10 을 만나면 21 ~ 25 가 아닌 16 ~ 25 사이의 게시물을 가져오게 됩니다. 그래서 중첩된 TOP 을 쓸 경우에는 반드시 외각의 TOP 은 totalCount 을 이용해서 몇 개의 게시물을 가져올지 계산한 뒤 처리해야 합니다.

다음은 Java 에서의 처리 예제입니다.

int totalCount = 25;
int countPage = 10;
int page = 3;

int countTop;

if (page * countPage > totalCount) {

    countTop = totalCount - (page - 1) * countPage;

} else {

    countTop = countPage;

}

위에서 계산된 countTop 을 이용해서 필요한 만큼만 가져오게 해야 한다는 것이죠.

혹시나 매우 오래된 시스템을 유지보수 해야할 경우 참고하시면 좋겠네요.