페이징(Paging)에 대한 이해 - (2) ROW NUMBER 을 이용한 게시물 가져오기

2019/01/21 - [Programming/기타] - 페이징(Paging)에 대한 이해 - (1) 페이지 번호를 생성하자.

이번에는 DBMS 에서 ROW NUMBER(오라클의 rownum, SQL Server 의 ROW_NUMBER(), MySQL 의 변수를 이용한 번호 등)을 이용한 게시물 가져오기에 대해서 설명하겠습니다.

ROW NUMBER 는 쉽게 말해 출력되는 결과물에 순차적인 번호를 달아주는 기능입니다. 필요할 때 언제든지 번호를 달아달라고 시스템에 요청을 하면 사용자에게 보내줄 데이터에 번호를 달아주게 되죠. 문제는 이 번호를 달 때 정렬이 되어 있지 않다면 번호를 엉뚱하게 달 수도 있고, 번호를 단다는 것 자체가 시스템에 부하를 줘서 정말 많은 게시물을 가지고 있는 경우 특정 영역의 게시물들을 가져오는데 그 반응이 느려진다는데 있습니다.

DBMS 는 보통 인덱스(Index) 라는 것을 가지고 있습니다. 그래서 1 억 개 이상의 데이터를 가지고 있더라도 인덱스를 이용하면 원하는 자료를 순식간에 가져올 수 있습니다. 하지만, 게시판에서는 특정 번호를 가지고 데이터를 불러올 수 없습니다. 실시간으로 계속 자료가 쌓이다보면 해당 페이지에 보일 게시물의 내용이 지속적으로 바뀌기 때문입니다. 변동이 좀 덜하거나 하면 해당 페이지에 속한 게시물이 어떤 것인지 따로 기입한 뒤 그 정보를 통해 게시물을 가져오면 속도에 대한 문제가 발생하지 않지만, 추가 뿐만 아니라 삭제의 경우 이런 페이징을 어렵게 만드는 요인입니다. 그래서, 화면에 출력되어야 할 순서대로 정렬을 한 뒤 가상의 번호인 ROW NUMBER 을 앞에 달아줘서 원하는 영역의 자료만 가져올 수 있는 방식을 취합니다.

그런데, 일반적인 게시판은 아주 단순한 특징을 하나 가지고 있습니다. 사람들은 일반적으로 페이지 번호가 3 페이지 이상인 것을 거의 읽지 않는다는 것입니다. 즉, 페이지 번호가 낮은 것을 훨씬 더 많이 조회하고 있고, 그 비율이 제 경험상 5 페이지 이하의 페이지 조회율이 95% 이상이라고 생각하고 있습니다. 그러므로 총 페이지수가 1000 페이지인 많은 글이 있는 게시판이라도 1000 페이지를 읽는데 5 초가 걸리더라도 1 페이지를 읽는데 0.01 초 밖에 걸리지 않는다면 사용자들은 그렇게 느리다고 느끼지 않는다는 것입니다. 1 페이지나 1000 페이지나 모두 2 초씩 동일하게 소비되는 것보다 더 빠르고 좋은 게시판으로 사람들은 생각한다는 것이죠. 그래서 이런 특성을 이용한 게시물 가져오기 방식을 많이 쓰게 됩니다.

그럼 테스트 데이터 구조를 정의하겠습니다. 쿼리는 오라클로 하겠습니다. 먼저, board 라는 테이블이 존재하고, id, name, content, createdate 라는 필드가 존재한다고 하겠습니다. 여기서 createdate 는 date 형의 필드이고, id 는 sequence 형태의 number 형, name 은 varchar2, content 는 crob 의 형이라고 가정하겠습니다.

일반적으로 게시판을 검색할 때에는 최근에 작성한 순서대로 게시물을 조회합니다. 그럼 다음과 같은 쿼리를 작성하게 될겁니다.

select id, name, content, createdate
from board
order by createdate

이렇게 하면 최신 순서대로 모든 게시물이 다 출력이 되겠죠. 예전처럼 ResultSet 을 직접 받아와서 처리하면 DBMS 에서 커서를 가지고 대기하다가 데이터를 요청할 때 전송하지만, 요즘 많이 쓰는 MyBatis 등은 데이터를 즉시 받아와서 resultType 이나 resultMap 에 정의된 대로 데이터를 bean 에 저장을 해버립니다. 그래서 모두 받아오면 문제가 발생하겠죠.

그래서 원하는 범위의 게시물을 가져와야 합니다.

총 게시물 수는 25 개, 한 페이지에 보여줄 게시물 수는 10 개, 현재 페이지는 3 페이지라고 가정하겠습니다. 그럼 가져와야할 게시물의 범위는? 딱 봐도 21 ~ 25입니다. 하지만, 아주 다행스럽게도 SQL 의 범위 검색은 범위가 벗어나도 있는데까지만 가져오기 때문에 21 ~ 30 사이의 게시물을 가져오라고 시켜도 알아서 21 ~ 25 까지의 게시물만 가져옵니다. 그래서 프로그램에서는 다음과 같이 시작 게시물 번호와 끝 게시물 번호를 계산해서 쿼리를 만들면 됩니다. 특별히 계산식의 해설은 하지 않겠습니다.

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

int startCount = (page - 1) * countPage + 1;  // 21 이 되겠죠

int endCount = page * countPage;  // 30 이 될 겁니다.

이렇게 만들어진 startCount 와 endCount 을 이용해서 DBMS 에서 원하는 게시물을 조회하는쿼리를 만들어보겠습니다. 위에서 언급했듯이 오라클의 쿼리입니다. 오라클은 ROW NUMBER 을 rownum 이라는 것으로 제공해줍니다. 사용의 편의를 위해 별칭(alias, as 을 이용해서 붙임)을 사용합니다.

먼저 페이지 번호를 붙여보겠습니다.

select rownum as rnum, id, name, content, createdate
from board
order by createdate

이렇게 해주면...순서대로 출력이 안되는걸 보실 수 있습니다. rownum 은 order by 가 이루어지기 전에 이루어진다는 걸 아실 수 있습니다. 그래서 아래와 같이 번호를 부여해야 합니다. 서브쿼리를 쓰는 거죠.

select rownum as rnum, A.id, A.name, A.content, A.createdate
from (
    select id, name, content, createdate
    from board
    order by createdate) A

이렇게 출력하면 순서대로 출력됩니다. 그럼 우리가 원하는 21 ~ 30 까지의 게시물을 가져오는 쿼리로 확장해보겠습니다.

select rownum as rnum, A.id, A.name, A.content, A.createdate
from (
    select id, name, content, createdate
    from board
    order by createdate) A
where rownum between 21 and 30
-- where rownum >= 21 and rownum <= 30

숫자 범위를 이용해 값을 가져오는 가장 대표적인 방법은 between 정도가 되겠네요. 아주 쉽게 21 ~ 30(실제로는 21 ~ 25) 의 게시물을 가져왔습니다.

끝일까요?

아쉽게도 아닙니다. DBMS 의 내부 동작 원리 상 위와 같이 페이지 내의 게시물을 가져오면 위에서 언급한 "모든 페이지 내 게시물을 가져올 때 2 초가 걸리는" 페이지가 될 수 있습니다. 아니,어쩌면 모든 페이지를 5 초 걸려서 가져올 겁니다. 

왜냐하면, 저렇게 범위 검색을 하게 되면 모든 테이블 내용을 정렬한 뒤 1 부터 번호를 쭉 달아서 끝번호까지 번호를 부여한 뒤 21 ~ 30 까지의 데이터를 가져오기 때문입니다. 예제에서는 게시물이 많이 없기 때문에 충분히 빠르지만, 실제 수백, 수천 건의 데이터가 들어있으면 속도 저하가 눈에 보입니다.

그럼 어떻게 해야 할까요?

해결책 중 하나는 다음과 같은 방법입니다.

select X.rnum, X.id, X.name, X.content, X.createdate
from (
    select rownum as rnum, A.id, A.name, A.content, A.createdate
    from (
        select id, name, content, createdate
        from board
        order by createdate) A
    where rownum <= 30) X
where X.rnum >= 21

좀 달라졌죠? 번거롭게 한 번 더 둘러싼 뒤 게시물을 가져옵니다. 무슨 차이가 있을까요? DBMS 는 ROW NUMBER 을 부여하다가 위와 같이 첫번째 조건을 만족하게 되면 최적화를 통해 그 아래 데이터에 대한 정보 수집을 중지합니다. 그래서 30 개까지만 임시 테이블에 저장해둔 뒤 번호를 부여하고 나머지 값들은 버립니다. 그 뒤에 30개 안에서 앞의 20 개를 버리고 21 개째부터 나머지(=30)을 가져오기 때문에 속도가 빠릅니다.

즉, 총 게시물 수가 천만개라면, 정렬 후 천만개 모두를 번호를 매기면서 21 보다 같거나 크고 30 보다 작거나 같은 것을 찾는 것과 정렬 후 천만개 중 30 개까지 번호를 매긴 뒤 그걸 따로 떼서 21 보다 같거나 큰 것만 따로 떼서 사용자에게 제공하는 것의 차이가 발생하기 때문에 속도 차이가 눈에 띄게 나타납니다.

그래서, 만약 현재 자신이 사용중인 페이지 쿼리가 between 을 쓰고 있거나 한 번의 서브쿼리을 이용한 범위 검색을 쓰고 있다면, 두 번의 서브쿼리 형태로 바꾸시길 바랍니다.

추가로, 저번 글에서 말씀드렸던 부분인데...하나의 쿼리에서 총 게시물 수를 ROW_NUMBER() 등을 통해서 위의 페이지 쿼리와 동시에 쓰는 분들이 있습니다. 위와 비슷한 이유로 속도가 느려집니다.

습관적으로 위와 같이 페이징을 할 때에는 전체 게시물 수를 얻는 쿼리 따로, 그리고 페이지 게시물을 가져오는 쿼리를 따로 쓰고, ROW NUMBER 을 이용할 때에는 between 형태를 이용하지 않도록 주의하시면 페이징을 90% 마스터 하시는 겁니다.

물론, content 나 name 은 like 검색을 통해 전후방 일치 검색을 하게 되는데 게시물이 매우 많아지면 어쩔 수 없이 느립니다. 이 때에는 검색엔진의 힘을 빌리는게 가장 좋은데, 검색엔진은 보통 결과로 위에서 설정한 id 와 같은 pk 나 uq 값을 페이징 처리까지 해서 결과로 제공합니다. 그러므로 더 쉬운 게시판 페이징을 할 수 있습니다.

마지막으로, 위와 같은 쿼리를 할 때 또 한가지 주의점을 알려드립니다. 위에서 createdate 는 일부러 날짜형으로 지정을 했습니다. 그런데, 프로그램에서는 이를 변환해서 String 형태로 받는 경우가 더 많습니다. 그래서 DBMS 에 쿼리로 질의를 할 때 변환 함수를 써서 변환을 하는데 이걸 언제하는지 고민하지 않고 개발하는 분들이 계십니다. 아래와 같이 처리하는 거죠.

select X.rnum, X.id, X.name, X.content, X.createdate
from (
    select rownum as rnum, A.id, A.name, A.content, A.createdate
    from (
        select id, name, content, to_char(createdate, 'yyyy-MM-dd') as createdate
        from board
        order by createdate) A
    where rownum <= 30) X
where X.rnum >= 21

하지만, 이렇게 할 경우 모든 게시물에 대한 날짜 정보를 변환을 한 뒤에 번호를 붙여주게 됩니다. 그래서 이런 변환 함수는 해줄 수 있는 가장 마지막 단계에서 해주는게 좋습니다.

select X.rnum, X.id, X.name, X.content,  to_char(X.createdate, 'yyyy-MM-dd') as createdate
from (
    select rownum as rnum, A.id, A.name, A.content, A.createdate
    from (
        select id, name, content, createdate
        from board
        order by createdate) A
    where rownum <= 30) X
where X.rnum >= 21

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