데이터베이스 페이징 다루기 - MySQL, SQL Server, Oracle

웹 서비스가 다양화되고 클라이언트/서버 구조의 프로그램에서 조차 "검색"은 일반화된 기능이다보니 프로그래머의 입장에서 검색 결과의 일부분을 체계적으로 보여주는 페이징은 필수 불가결한 기능이라 할 수 있습니다. 그런데, 페이징 기능을 수행하려면 검색 결과의 개수를 먼저 파악해야하고 그중에 특정 부분의 행들을 추출할 수 있어야 합니다. 그런데 조건에 해당하는 모든 행의 개수를 파악하는 것도 테이블 전체를 스캔하므로 데이터베이스 시스템에 부담을 주지만 검색 결과를 특정 조건에 따라 정렬한 다음에 일부분을 뽑아내는 것은 개수를 파악하는 것보다 더 심한 부담을 주는 것이 사실입니다. 그렇다고 데이터베이스 수준에서 페이징을 수행하지 않고 클라이언트로 모든 데이터를 가져온다면 트래픽도 감당하기 어려울 뿐만 아니라 클라이언트의 자원 한계에 부딛혀 프로그램의 비정상 동작을 초래할 수 있습니다.

이런 배경 때문에 최근 DBMS(데이터베이스 관리 시스템)에서는 프로그래머가 페이징 기능을 간편하게 사용할 수 있도록 SQL을 지원하고 있으며 몇몇 DBMS에서는 질의 결과를 캐시(Result Caching) 처리해서 동일한 질의로 동일한 스캔 및 정렬 작업을 반복하지 않도록 해서 성능을 개선하려는 시도도 있습니다. 인덱스의 적절한 활용과 더불어 최적화된 페이징은 데이터베이스 시스템과 클라이언트의 안정적 운용과 성능 개선 모두에 도움을 줄 것입니다.

이번 포스팅은 특정 테이블에 대하여 검색 조건과 정렬 조건이 있는 상황의 페이징을 MySQL, SQL Server, Oracle 의 경우로 각각 다루어 보겠습니다.

위의 테이블에 대해서 2015-10-22에 일자에 해당하는 내역의 개수를 파악하거나 실제 내역을 질의하려면 공통적으로 "WHERE dt >= '2015-10-22 00:00:00' AND dt <= '2015-10-22 23:59:59' "와 같은 질의를 사용 해야 합니다. 오라클의 경우 문자열을 일시 데이터로 자동 변환하지 않아 TO_DATE 함수를 문자열 상수에 붙여야 하지만 개수 파악과 실 데이터 추출에 사용하는 WHERE 절이 동일한 것은 마찬가지입니다.

SELECT COUNT(*) FROM tags  WHERE dt >= '2015-10-22 00:00:00' AND dt <= '2015-10-22 23:59:59'

SELECT * FROM tags WHERE dt >= '2015-10-22 00:00:00' AND dt <= '2015-10-22 23:59:59'

문제는 정렬된 결과에서 특정 페이지에 해당하는 부분하는 것인데 MySQL의 경우에는 LIMIT 구문을, SQL Server와 오라클의 경우에는 ROW_NUMBER() OVER ()구문을 사용합니다. 한 페이지에 30개씩 출력한다고 가정하고 각 DBMS별로 2페이지에 있는 내용을 추출할 경우의 질의문은 아래와 같습니다.

== MySQL ==

SELECT * FROM tags WHERE dt >= '2015-10-22 00:00:00' AND dt <= '2015-10-22 23:59:59'

ORDER BY dt DESC, tagname LIMIT 30, 30;

== SQL Server ==

SELECT * FROM (

  SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.dt DESC, t.tagname) rnum FROM tags t

  WHERE dt >= '2015-10-22 00:00:00' AND dt <= '2015-10-22 23:59:59' 

) tmptbl 

WHERE rnum BETWEEN 31 AND 60;

== Oracle ==

SELECT * FROM (

  SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.dt DESC, t.tagname) rnum FROM tags t

  WHERE dt >= TO_DATE('2015-10-22 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

    AND dt <= TO_DATE('2015-10-22 23:59:59', 'YYYY-MM-DD HH24:MI:SS')

) 

WHERE rnum BETWEEN 31 AND 60;

MySQL의 경우 LIMIT 구문은 "LIMIT 시작 위치, 개수"의 형식으로 시작 위치는 맨 처음을 0으로 지정합니다. 위의 예제에서 "LIMIT 30, 30"은 31번째 위치 부터 30개를 의미합니다.

SQL Server와 오라클은 동일한 구문을 사용하는데 차이점이라면 "SELECT * FROM (....)"로 사용한 서브쿼리(Subquery)에 대해서 SQL Server에서는 "tmptbl"라고 이름을 붙인것처럼 이름을 필수로 붙여야 하고 오라클은 붙이지 않아도 됩니다. 또다른 차이점은 앞서 언급한대로 오라클의 경우 일자시간을 표현한 문자열에 TO_DATE() 함수로 명시적으로 타입을 변환 시켜주어야 한다는 것입니다. OVER 구문에 ORDER BY 정렬 구문을 배치하고 이구문을 통해 생성된 순번(예제에서는 rnum이라 이름 붙임, 매 처음 값은 1)으로 범위를 제한해서 질의를 수행하면 됩니다.