[DBA] 게시판 페이징 처리하는 방법 (Scan VS Seek)

게시판의 구성 방식

---

구성	설명
블록	검정색 네모박스로, 페이지의 집합을 의미합니다.
페이지	주홍색 네모박스로, 게시글의 집합을 의미합니다.
이전/다음 블록 이동	녹색 네모박스로, 블록의 이동을 의미합니다.
이전/다음 페이지 이동	파란색 네모박스로, 페이지의 이동을 의미합니다.

테스트에 사용된 샘플 테이블은

SQL Server 에서 제공하는 AdventureWorks 데이터베이스이 Sales.SalesOrderDetail 테이블 입니다.

해당 테이블의 건수는 121,317 건이고, SalesOrderDetailID 컬럼에 DESC 정렬의 인덱스를 생성했습니다.

한 블록에는 10개의 페이지가, 한 페이지에는 10개의 게시글이 존재하고 있습니다.

페이징 기법이 처리되지 않은 프로시저를 먼저 보고, 페이징 기법이 처리된 프로시저를 보겠습니다.

페이징 기법이 처리되지 않는 프로시저

---


CREATE PROCEDURE Paging_Full

  @PageNo INT         -- 페이지 번호

  ,@PageSize INT      -- 페이지 당 게시물 수

AS

BEGIN

  SET NOCOUNT ON

  /* 서브쿼리에서 가장 최신의 데이터부터 출력하길 원하는 데이터까지 쭉 읽은 후 원하는 데이터만 조건절로 다시 출력 */

  SELECT X.SeqNo, X.SalesOrderDetailID, X.LineTotal

  FROM

    ( SELECT TOP (( @PageNo * @PageSize ) + 1)

        ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID DESC) AS SeqNo

        ,SalesOrderDetailID

        ,LineTotal

      FROM Sales.SalesOrderDetail WITH(NOLOCK)

      ORDER BY SalesOrderDetailID DESC ) X

  WHERE X.SeqNo BETWEEN (@PageNo - 1 ) * @PageSize + 1 AND @PageNo * @PageSize

  ORDER BY X.SalesOrderDetailID DESC

END

GO

위 프로시저는 1,001 페이지를 조회하길 원하는 경우 1 번째 페이지 부터 1,001 페이지까지 데이터를 순차적으로 일근 후

1,001 페이지에 존재하는 10,001 ~ 10,010 의 데이터를 출력해야 합니다.

10 개의 게시물을 출력하기 위해 10,010 번의 데이터를 읽어야만 합니다.

이는 페이지가 뒤로 가면 갈수록 읽는 데이터가 늘어날 수 밖에 없습니다.

<실행계획>

페이징 기법이 처리된 프로시저

---

프로시저를 보기 전에 변수 계산식이 어떻게 나왔는지

주석과 원리를 잘 읽어봐 주세요.

적어도 3 ~ 5번 정도는 처음부터 다시 봐야 이해가 조금씩 되기 시작할 거라 예상합니다.

CREATE PROCEDURE Paging_Part

  @PageNo INT                                 -- 페이지 번호

  ,@PageSize INT                              -- 페이지당 게시물 수

  ,@PageNo_Prev INT                           -- 이전에 위치하고 있던 페이지 번호

  ,@SalesOrderDetailID_BlockStart INT         -- 해당 페이지에서 가장 최신의 SalesOrderDetailID 값

  ,@SalesOrderDetailID_BlockStart_Prev INT    -- 이전 페이지가 속한 블록에서 가장 최신의 SalesOrderDetailID 값

AS

BEGIN

  SET NOCOUNT ON

   

  DECLARE @BlockSize INT

  DECLARE @BlockNo INT

  DECLARE @BlockNo_Prev INT

  DECLARE @BlockStartPageNo INT

 

  SET @BlockSize = 10                                                     -- 한 블록에서 페이지 수

  SET @BlockNo = (@PageNo + @BlockSize - 1)/ @BlockSize                   -- 해당 페이지가 속한 블록 번호

  SET @BlockNo_Prev = (@PageNo_Prev + @BlockSize - 1 ) / @BlockSize       -- 이전에 있었던 페이지가 속한 블록 번호

  SET @BlockStartPageNo = ((@PageNo - 1) / @BlockSize ) * @BlockSize + 1  -- 블록에 속한 페이지 중 첫 번재 페이지 수

 

  /* 블록 번호가 1이라면 가장 최신의 데이터를 SalesOrderDetailID_BlockStart 변수에 저장 */

  IF(@BlockNo = 1)

  BEGIN

    SELECT TOP 1 @SalesOrderDetailID_BlockStart = SalesOrderDetailID

    FROM Sales.SalesOrderDetail WITH(NOLOCK)

    ORDER BY SalesOrderDetailID DESC

  END

  /* 블록 번호가 1이 아니라면 */

  ELSE IF (@PageNo_Prev IS NOT NULL AND @SalesOrderDetailID_BlockStart_Prev IS NOT NULL)

  BEGIN

    /* 이번에 조회한 페이지의 블록 번호와 이전에 조회한 페이지의 블록 번호가 같다면

         예) 2 -> 7 페이지로 이동

       이전 페이지가 속한 블록에서 시퀀스가 가장 큰 값으로 SalesOrderDetailID_BlockStart 변수에 저장 */

    IF (@BlockNo = @BlockNo_Prev)

    BEGIN

       

      SELECT @SalesOrderDetailID_BlockStart = @SalesOrderDetailID_BlockStart_Prev

    END

    /* 블록이 다음으로 넘어갔다면

         예) 10 -> 11 페이지로 이동, 2 -> 11 페이지로 이동

       이전 페이지가 속한 블록에서 가장 최신의 SalesOrderDetailID 값보다 작은

        내림차순으로 정렬된 SalesOrderDetailID 중

        페이지의 게시물 수 * 하나의 블록에 속한 페이지 수 + 1 만큼 뺀 가장 최신의 SalesOrderDetailID */

    ELSE IF (@BlockNo > @BlockNo_Prev)

    BEGIN

      SELECT TOP ( (@BlockNo - @BlockNo_Prev) * @PageSize * @BlockSize + 1)

        @SalesOrderDetailID_BlockStart = SalesOrderDetailID

      FROM Sales.SalesOrderDetail WITH(NOLOCK)

      WHERE SalesOrderDetailID <= @SalesOrderDetailID_BlockStart_Prev

      ORDER BY SalesOrderDetailID DESC

    END

    /* 블록이 이전으로 넘어갔다면

        예) 11 -> 10 페이지로 이동, 13 -> 7 페이지로 이동

       이전 페이지가 속한 블록에서 가장 최신의 SalesOrderDetailID 값보다 큰

        오름차순으로 정렬된 SalesOrderDetailID 중

        페이지의 게시물 수 * 하나의 블록에 속한 페이지 수 + 1 만큼 더한 가장 최신의 SalesOrderDetailID */

    ELSE IF (@BlockNo < @BlockNo_Prev)

    BEGIN

      SELECT TOP ( (@BlockNo_Prev - @BlockNo) * @PageSize * @BlockSize + 1)

        @SalesOrderDetailID_BlockStart = SalesOrderDetailID

      FROM Sales.SalesOrderDetail WITH(NOLOCK)

      WHERE SalesOrderDetailID >= @SalesOrderDetailID_BlockStart_Prev

      ORDER BY SalesOrderDetailID ASC

    END

  END

  /* 블록 번호가 1도 아니고, 인자에 들어온 @PageNo_Prev,  @SalesOrderDetailID_BlockStart_Prev 가 NULL 이라면 */

  ELSE

  BEGIN

    SELECT TOP ( ( @BlockNo - 1 ) * @PageSize * @BlockSize + 1 )

      @SalesOrderDetailID_BlockStart = SalesOrderDetailID

    FROM Sales.SalesOrderDetail WITH(NOLOCK)

    ORDER BY SalesOrderDetailID DESC

  END

 

  /* 실제 데이터 출력 */

  SELECT TOP(@PageSize)

    X.SeqNo

    ,X.SalesOrderDetailID

    ,X.LineTotal

  FROM

    (

      SELECT TOP ( ( @PageNo - @BlockStartPageNo + 1 ) * @PageSize )

        ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID DESC) AS SeqNo

        ,SalesOrderDetailID

        ,LineTotal

      FROM Sales.SalesOrderDetail WITH(NOLOCK)

      WHERE SalesOrderDetailID <= @SalesOrderDetailID_BlockStart ) X

  WHERE X.SeqNo BETWEEN ( ( @PageNo - (@BlockNo - 1) * @BlockSize ) * @PageSize - @PageSize ) + 1

                    AND ( @PageNo - (@BlockNo - 1) * @BlockSize ) * @PageSize

  ORDER BY X.SalesOrderDetailID DESC

END

GO

인자들에 대한 값들은 Application 에서 계산하여 프로시저 매개변수에 대입을 해주어야 합니다.

가장 처음에 게시판 웹페이지에 들어가면 아래와 같은 프로시저가 호출됩니다.

/*

    첫 번째 페이지

    페이지 별 게시물 수

    이전 페이지 기록 없음

    첫 번째 페이지의 SalesOrderDetailID 의 가장 최신(큰) 값

    이전 페이지 기록 없음

*/

EXEC Paging_Part 1, 10, NULL, 121317, NULL

1 페이지에서 2 페이지로 이동하면 아래와 같은 프로시저가 다시 호출됩니다.

/*

    두 번째 페이지

    페이지 별 게시물 수

    이전 페이지 (첫 번째 페이지)

    두 번째 페이지의 SalesOrderDetailID 의 가장 최신(큰) 값

    첫 번째 페이지가 속한 블록에서 SalesOrderDetailID 의 가장 최신(큰) 값

*/

EXEC Paging_Part 2, 10, 1, 121307, 121317

2 페이지에서 다음을 눌러 11 페이지로 이동하면 아래와 같은 프로시저가 다시 호출됩니다.

/*

    11 번째 페이지

    페이지 별 게시물 수

    이전 페이지 (두 번째 페이지)

    11 번째 페이지의 SalesOrderDetailID 의 가장 최신(큰) 값

    두 번째 페이지가 속한 블록에서 SalesOrderDetailID 의 가장 최신(큰) 값

*/

EXEC Paging_Part 11, 10, 2, 121217, 121317

11 페이지에서 17 페이지로 이동하게 되면 아래와 같은 프로시저가 호출됩니다.

/*

    17 번째 페이지

    페이지 별 게시물 수

    이전 페이지 (11 번째 페이지)

    17 번째 페이지의 SalesOrderDetailID 의 가장 최신(큰) 값

    11 번째 페이지가 속한 블록에서 SalesOrderDetailID 의 가장 최신(큰) 값

*/

EXEC Paging_Part 17, 10, 12, 121157, 121217

17 페이지에서 이전 버튼을 눌러 1 페이지로 이동하게 되면 아래와 같은 프로시저가 호출됩니다.

/*

    1 번째 페이지

    페이지 별 게시물 수

    이전 페이지 (17 번째 페이지)

    1 번째 페이지의 SalesOrderDetailID 의 가장 최신(큰) 값

    17 번째 페이지가 속한 블록에서 SalesOrderDetailID 의 가장 최신(큰) 값

*/

EXEC Paging_Part 1, 10, 17, 121317, 121217

어떻게 되는지 대충 느낌이 오셨나요?

이번에는 1001 페이지를 조회해 보겠습니다.

/*

  1,001 페이지

  페이지 별 게시물 수

  이전 페이지 (1,000 번째 페이지)

  1,001 번째 페이지의 SalesOrderDetailID 의 가장 최신(큰) 값               -- 111,317

  1,000 번째 페이지가 속한 블록에서 SalesOrderDetailID 의 가장 최신(큰 값) -- 111,417

EXEC Paging_Part 1001, 10, 1000, 111317, 111417

<실행계획>

각 페이지 별 최신의 SalesOrderDetailID 는 다음 계산식으로 얻을 수 있습니다.

  ( SalesOrderDetailID 테이블의 총 건수 ) - ( 페이지 번호 * 페이지 별 게시글 수 ) + ( 페이지 별 게시글 수 )

해당 페이지가 속한 블록의 가장 최신 페이지는 다음 계산식으로 얻을 수 있습니다.

  ( ( 페이지 번호 - 1 ) / 블록 별 페이지 수 ) * 블록 별 페이지 수 + 1

대다수의 많은 블로그가 Application 관점에서 작성을 하였다면, 저는 Database 의 처리 관점에서 작성을 해보았습니다.

도움이 되셨다면 공감버튼 부탁드립니다.