MS-SQL 페이징 처리

MS-SQL 페이징 처리

mysql 에선 limit 문을 사용하고

orcle 에서는 rownum 을 사용하여 페이징 처리를 하는 것이 일반적이다.

자...

MS-SQL 에서는 어떻게 할까?

검색해 봤더니 전부 약간씩의 단점들이 있더라.

검색 가능하고, 삭제한 글은 목록에서 사라져야 일반적인 게시판인 것이 아닌가?

용량이 크다고 기본 기능을 빼버리는 것은 왠지 맘에 안 든다.

그래서 하룻동안 고민한 결과 원래 내가 쓰던 게시판 로직에 맞는 기능을 구현 할 수 있었다. ^^

일단 기억나는 것은 TOP 기능이다.

상위부터 몇개의 데이터를 추출할 것인가를 지정해 주는 기능이다.

이것만 가지고 페이징이 가능할까?

일단 테스트 데이터로 1000만건 이상을 입력하였다.

1000만건 정도는 처리해줘야지 그래도 뭐 좀 했다고 할 것이 아닌가? ^^

목표 대상은 일반적으로 페이징 처리하는 게시판 형태로 하였다.

1000만건 정도 되면 (1만건이나 혹은 그 이하라고 해도) 일단 테이블 설계 단계부터 신경을 써주지 않으면 않된다.

자.. 어떤 게시판을 만들어 볼까?

다중 게시판을 만들어보기로 결정했다.

뭐.. 게시판 만드는 방법에는 여러가지가 있겠지만

게시판 마다 테이블 생성해주는 것은 개인적으로 관리측면에서 너무 불편하다고 생각하기 때문에

몇개의 테이블로 기능을 압축시켜보았다.

다음은 해당 게시판의 스키마이다.

/*==============================================================*/
/* Table: T_BOARD_MASTER */
/*==============================================================*/
create table T_BOARD_MASTER (
BOARD_IDX bigint identity,
BOARD_NAME varchar(30) not null,
USE_SCRIPT char(1) not null default 'N',
USE_ANSWER char(1) not null default 'N',
USE_COMMENT char(1) not null default 'N',
USE_RECOMMAND char(1) not null default 'N',
MEMBER_GUBUN varchar(4) not null,
USE_FILE char(1) not null default 'N',
FILE_PATH varchar(150) null,
FILE_LIMIT bigint null default -1,
USE_BOARD char(1) not null default 'Y',
DATA_COUNT bigint not null default 0,
THEME varchar(50) null,
CDATE char(14) not null,
constraint PK_T_BOARD_MASTER primary key (BOARD_IDX)
)
go

/*==============================================================*/
/* Table: T_BOARD_DATA */
/*==============================================================*/
create table T_BOARD_DATA (
BOARD_IDX bigint not null,
DATA_IDX bigint identity,
GROUP_NO bigint not null default 0,
GROUP_ORDER int not null default 0,
GROUP_DEPTH int not null default 0,
PARENT_IDX bigint not null default 0,
CLASS_NAME varchar(50) null,
NAME varchar(24) not null,
NICKNAME varchar(24) null,
EMAIL varchar(40) not null,
ID varchar(24) null,
MEMBER_GUBUN varchar(4) not null,
PSWD varchar(16) null,
SUBJECT varchar(60) not null,
CONTENT text not null,
PRIORITY int not null default 0,
VIEW_COUNT bigint not null default 0,
IP varchar(20) null,
IS_HTML char(1) not null default 'N',
IS_DEL char(1) not null default 'N',
CDATE char(8) not null,
CTIME char(6) not null,
UDATE char(8) not null,
UTIME char(6) not null,
RECOM_COUNT bigint not null default 0,
COMMENT_COUNT bigint not null default 0,
FILE_COUNT int not null default 0,
constraint PK_T_BOARD_DATA primary key (DATA_IDX)
)
go

/*==============================================================*/
/* Index: IDX_BDATA_DESC */
/*==============================================================*/
create index IDX_BDATA_DESC on T_BOARD_DATA (
BOARD_IDX ASC,
IS_DEL ASC,
GROUP_NO ASC,
GROUP_ORDER ASC
)
go

/*==============================================================*/
/* Index: IDX_BDATA_SUBJECT */
/*==============================================================*/
create index IDX_BDATA_SUBJECT on T_BOARD_DATA (
BOARD_IDX ASC,

IS_DEL ASC,
SUBJECT ASC
)
go

alter table T_BOARD_DATA
add constraint FK_BDATA_REF_BMASTER foreign key (BOARD_IDX)
references T_BOARD_MASTER (BOARD_IDX)
go

T_BOARD_MASTER 에는 해당 게시판에 대한 정보를 입력하고

이 테이블의 정보를 바탕으로 게시판의 기능을 제한한다.

(이 테이블은 게시판에 접근 할 때마다 접근하게 되므로 캐쉬 기능이 있다면 캐쉬하는 것이 성능 측면에서 좋다. 실제로 개발할 때는 요건에 맞다면 캐쉬를 이용하는 것이 성능 향상에 도움이 된다. 물론 남발하면 좋지 않다.)

T_BOARD_DATA 에서 중요한 컬럼은

GROUP_NO, GROUP_ORDER, GROUP_DEPTH 이다.

GROUP_NO 컬럼은 해당 글의 글 번호 묶음이라고 생각하면 된다.

답글이 아닌 최초 글이 등록될 때 이 번호를 생성하고

그 글의 답글들에게는 같은 번호를 사용한다.

primary key 컬럼링 DATA_IDX 는 자동 증가 컬럼이고 데이터형은 BIGINT 이므로

GROUP_NO 의 생성 조건은

GROUP_NO = 9223372036854775807 - DATA_IDX 로 하였다.

번호 생성 조건으로는 충분하다고 생각된다(100경의 스페이스가 보장되니깐).

따라서 DATA_IDX 는 증가하므로 GROUP_NO 값은 감소하게 된다.

이것이 인덱스가 ascending 인 이유이다.

DATA_IDX 가 7 인 글이 작성되었을 때

GROUP_NO 의 값은 9223372036854775800 이 되고

그 글에 어떤 답글이 달려도 그 글들의 GROUP_NO 도 9223372036854775800 이 되는 것이다.

GROUP_ORDER 컬럼은 같은 값의 GROUP_NO 그룹에서의 글 순서를 의미한다.

처음글이 0 번이고 답글들은 양의 정수값을 가진다.

GROUP_DEPTH 은 답글들의 인덴트(indent) 값이다.

즉 답글의 위치를 나타내는 것이다.

이런 구조의 게시판을 계층형 계시판이라고들 하더라.

자세한 것은 다른 사람의 글을 보기 바란다.

내 게시판에서 특이한 것은

PARENT_IDX 컬럼인데

이 컬럼은 게시판의 글 구조를 recursive 로 표현하기 위해서 쓰이는 컬럼이다.

기본값은 0 이고 DATA_IDX 값이 15400 인 글에 답글이 달린다면

그 답글의 PARENT_IDX 컬럼의 값은 15400 이 되는 것이다.

이 컬럼의 사용 목적은 답글이나 글 삭제시에 적절한 GROUP_ORDER 를 계산하기 위해서 쓰인다.

이 게시판에서는 삭제된 글을 포함한 그 삭제 글의 답글들은 보여지는 목록에서 제거한다.

따라서 기본 정렬 인덱스는 다음과 같이 만들어 진다.

create index IDX_BDATA_DESC on T_BOARD_DATA (
BOARD_IDX ASC,
IS_DEL ASC,
GROUP_NO ASC,
GROUP_ORDER ASC
)

이 게시판은 인덱스를 사용한 정렬을 사용한다.

따라서 sort 가 최소화 되므로 그만큼의 속도 향상을 가져 올 수 있다.

인덱스는 기본 정렬 인덱스와 제목 검색 관련 인덱스만 만들어보았다

검색 요건에 따라서 다른 인덱스가 걸릴수도 있지만

인덱스가 많아질 수록 부하가 커지므로 만드시 필요한 기능외에는 쓸데없이 인덱스를 늘리지 않는 것이 좋다.

기타 여분의 테이블들 (파일,덧글,추천)은 생략하기로 한다.

해당 게시물을 빠르게 찾을 수 있다면 이 테이블의 데이터 역시 빠르게 접근할수 있기 때문이다.

솔직히 주로 oracle로 작업해왔고 mysql 도 많이 다루었지만

MS-SQL로 천만건 이상의 데이터를 다루기는 처음이다.

버전은 내 컴퓨터에 깔린 MS-SQL 2000 developer edition 이다.

MS-SQL은 옛날에 알바루 데이터 별루 없는 게시판을 아주 심플하게 만들때 외엔

써 본적이 없다. -_-;;;;

SQL HELP 보면서 작업했다.....

자...

페이징이란 우선 필요한 데이터 부분만을 가져오는 기술이 되겠다.

왜 이런 기능에 별루 신경 안 써주는지 모르겠다 정말.... -_-

서브 쿼리로 인라인 뷰(?)를 만들어야 겠다고 생각했다.

용어가 맞나 모르겠다. 용어에 굉장히 약해서...쩝....

자 우선 페이지 크기는 10개 이고 전체 데이터는 9506754개 라고 해보자.

우리가 보고자 하는 페이지가 95555 번째 페이지라고 할때

일단 보아야 할 가장 마지막 로우는 95555 * 10 번쨰 로우일 것이다.

그 로우까지 가보자.

1.

SELECT TOP 955550 BOARD_IDX, DATA_IDX, GROUP_NO, GROUP_ORDER
FROM T_BOARD_DATA WITH(INDEX(IDX_BDATA_DESC), NOLOCK)
WHERE BOARD_IDX = @BOARD_IDX AND IS_DEL = 'N'

WHERE 절의 조건에 따라서 SQL 서버가 자동으로 인덱스를 사용하겠지만

그래도 불안해서 힌트를 주었다.

그 담에는 보아야 할 마지막 페이지의 10 개의 목록만을 얻는 것이다.

2.

SELECT TOP 10 *
FROM
(
SELECT TOP 955550 BOARD_IDX, DATA_IDX, GROUP_NO, GROUP_ORDER
FROM T_BOARD_DATA WITH(INDEX(IDX_BDATA_DESC), NOLOCK)
WHERE BOARD_IDX = @BOARD_IDX AND IS_DEL = 'N'

) t2
ORDER BY GROUP_NO DESC, GROUP_ORDER DESC

1 번 쿼리를 인라인 뷰로 하고 이 뷰를 거꾸로 뒤집어 다시 워하는 수만큼 TOP 으로 꺼낸다.

ORDER BY GROUP_NO DESC, GROUP_ORDER DESC 는 뷰의 목록들의 순서를 뒤집는 역활을 한다.

2번 쿼리의 TOP 에는 페이지 크기가 들어가면 된다.

20 개의 row 를 한 페이지로 할 경우엔 TOP 20 이면 된다.

t1 에 인덱스를 사용 할수 있다면 얼마나 좋을까.

솔직히 ORDER BY GROUP_NO DESC, GROUP_ORDER DESC 가 부담 스럽다.

3.

SELECT a.BOARD_IDX, a.DATA_IDX, a.GROUP_NO, a.GROUP_ORDER, a.GROUP_DEPTH, a.CDATE, a.CTIME, a.VIEW_COUNT, a.NAME, a.NICKNAME, a.EMAIL, a.ID, a.SUBJECT
FROM
(
SELECT TOP 10 *
FROM
(
SELECT TOP 955550 BOARD_IDX, DATA_IDX, GROUP_NO, GROUP_ORDER
FROM T_BOARD_DATA WITH(INDEX(IDX_BDATA_DESC), NOLOCK)
WHERE BOARD_IDX = @BOARD_IDX AND IS_DEL = 'N'
) t2
ORDER BY GROUP_NO DESC, GROUP_ORDER DESC
) t1, T_BOARD_DATA a
WHERE t1.DATA_IDX = a.DATA_IDX
ORDER BY a.GROUP_NO ASC, a.GROUP_ORDER ASC

2번 쿼리의 상태로는 게시물이 역순(오래된 글이 가장 위에 나타나는)으로 보이므로

다시 이 순서를 뒤집는다(ORDER BY a.GROUP_NO ASC, a.GROUP_ORDER ASC).

그리고 이 때 다시 T_BOARD_DATA 테이블과 조인하여

필요한 데이터를 추출한다.

이 때는 primary key(clusted index) 조인 이므로 부하가 없다.

인라인 뷰 안에는 인덱스 이외의 컬럼명을 적지 않는다.

다른 컬럼명을 적을 경우 수행 개획을 보면

bookmark lookup 이 일어나는데 이것은 해당 데이터를 읽는 과정이며 따라서 느려질수 밖에 없다.

인덱스 컬럼 과 키 컬럼만을 추출하고 바깥쪽에서 조인하는 것이 가장 좋다고 생각한다.

ps.

SQL 2005 버전에서는

SELECT a.BOARD_IDX, a.DATA_IDX, a.GROUP_NO, a.GROUP_ORDER, a.GROUP_DEPTH, a.CDATE, a.CTIME, a.VIEW_COUNT, a.NAME, a.NICKNAME, a.EMAIL, a.ID, a.SUBJECT
FROM
(
SELECT TOP 10 *
FROM
(
SELECT TOP 955550 BOARD_IDX, DATA_IDX, GROUP_NO, GROUP_ORDER
FROM T_BOARD_DATA WITH(INDEX(IDX_BDATA_DESC), NOLOCK)
WHERE BOARD_IDX = @BOARD_IDX AND IS_DEL = 'N'
) t2
ORDER BY GROUP_NO DESC, GROUP_ORDER DESC
) t1, T_BOARD_DATA a
WHERE t1.DATA_IDX = a.DATA_IDX
ORDER BY a.GROUP_NO ASC, a.GROUP_ORDER ASC

이 쿼리로는 올바른 데이터가 나오지 않는다.(실제 2005 서버를 운영 중인 사람에세 부탁해서 확인까지 하였다)

대신에

SELECT a.BOARD_IDX, a.DATA_IDX, a.GROUP_NO, a.GROUP_ORDER, a.GROUP_DEPTH, a.CDATE, a.CTIME, a.VIEW_COUNT, a.NAME, a.NICKNAME, a.EMAIL, a.ID, a.SUBJECT
FROM
(
SELECT TOP 10 *
FROM
(
SELECT TOP 955550 BOARD_IDX, DATA_IDX, GROUP_NO, GROUP_ORDER
FROM T_BOARD_DATA WITH(NOLOCK)
WHERE BOARD_IDX = @BOARD_IDX AND IS_DEL = 'N'

ORDER BY GROUP_NO ASC, GROUP_ORDER ASC
) t2
ORDER BY GROUP_NO DESC, GROUP_ORDER DESC
) t1, T_BOARD_DATA a
WHERE t1.DATA_IDX = a.DATA_IDX
ORDER BY a.GROUP_NO ASC, a.GROUP_ORDER ASC

이와 같이해서 IDX_BDATA_DESC 를 사용하게 만들어주면 올바르게 작동했다.

잘은 모르겠지만 2005 버전의 버그가 아닌가 싶다.

WITH(INDEX(IDX_BDATA_DESC), NOLOCK) 로는 실행 계획이 전혀 엉뚱하게 나온다.

안쪽의 뷰의 실행계획은 같으나 뷰의 결과물을 사용하지 않고 전체 테이블에서 다시 TOP 10 을 해오는 어이없는 동작을 보여주었다.

ps.

2005 서버에서 row_number() 을 이용한 페이징 쿼리

SELECT t1.ROW_NUM, a.BOARD_IDX, a.DATA_IDX, a.GROUP_NO, a.GROUP_ORDER, a.GROUP_DEPTH, a.CDATE, a.CTIME, a.VIEW_COUNT, a.NAME, a.NICKNAME, a.EMAIL, a.ID, a.SUBJECT
FROM
(
SELECT t2.ROW_NUM, t2.BOARD_IDX, t2.DATA_IDX, t2.GROUP_NO, t2.GROUP_ORDER
FROM
(
SELECT TOP (@OFFSET_ZERO + @PAGE_SIZE) row_number() over (ORDER BY GROUP_NO ASC, GROUP_ORDER ASC) as ROW_NUM, BOARD_IDX, DATA_IDX, GROUP_NO, GROUP_ORDER
FROM T_BOARD_DATA WITH(NOLOCK)
WHERE BOARD_IDX = @BOARD_IDX AND IS_DEL = 'N'
) t2
WHERE t2.ROW_NUM > @OFFSET_ZERO
) t1, T_BOARD_DATA a
WHERE t1.DATA_IDX = a.DATA_IDX

세그먼트를 수행하므로 훨씬 빠르다~! 2005 한건 했구나! 버그는 좀 있지만...

궁금한게 있으면 본인에게 메일 주시라~!(hoon7208@naver.com)