대용량 게시판 만들기 강좌 #2/4 (게시판 로직)

본 게시판의 장점은

"게시물의 갯수와 상관없이 (DB용량이 받쳐주는대로) 페이징/페이지 검색 속도가 동일하다는 것"입니다.

대신 단점은

1. 오래된 게시물의 답변 글을 쓸 경우에는 timeout 이 발생할 수 있다.

2. 삭제시 레코드를 직접 삭제하는 방법보다는 "xxx에 의해 삭제되었다.."라는 형태를 쓰는 것을 권장한다.

1번같은 경우는 먼저도 말했지만

게시판이라는것이 주로 테이블의 윗부분(최근글)에서 빈번한 삽입과 답변이 이루어지는 것을 감안하였을 때

심각하게 고려할 만한 사항은 아니라고 봅니다. 아무리 중간에 삽입하는 것이 느리더라도

테스트해보시면 아시겠지만, 100페이지 정도에서 답변글을 달아도 느리다는 느낌을 전혀 주지 않습니다.

그럼... 설명에 앞서 준비를 하겠습니다.

두 개의 소스를 준비하였습니다. QA에서 실행시키면 될 것입니다.

*************************************************************************

1. 테이블 (편의상 [t3]이라는 테이블로 작성하였습니다.

*************************************************************************

/****** Object: Table [dbo].[t3] Script Date: 2003-07-19 오전 12:37:23 ******/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[t3]

GO

/****** Object: Table [dbo].[t3] Script Date: 2003-07-19 오전 12:37:23 ******/

CREATE TABLE [dbo].[t3] (

[seq] [int] IDENTITY (1, 1) NOT NULL ,

[ref] [int] NOT NULL ,

[depth] [smallint] NOT NULL ,

[name] [varchar] (20) COLLATE Korean_Wansung_CI_AS NOT NULL ,

[email] [varchar] (50) COLLATE Korean_Wansung_CI_AS NULL ,

[password] [varchar] (16) COLLATE Korean_Wansung_CI_AS NOT NULL ,

[title] [varchar] (100) COLLATE Korean_Wansung_CI_AS NOT NULL ,

[writeday] [datetime] NOT NULL ,

[readnum] [smallint] NOT NULL ,

[ip] [varchar] (15) COLLATE Korean_Wansung_CI_AS NOT NULL ,

[content] [text] COLLATE Korean_Wansung_CI_AS NOT NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[t3] WITH NOCHECK ADD

CONSTRAINT [PK_t3] PRIMARY KEY CLUSTERED

(

[seq] DESC

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[t3] ADD

CONSTRAINT [DF_t3_depth] DEFAULT (0) FOR [depth],

CONSTRAINT [DF_t3_writeday] DEFAULT (getdate()) FOR [writeday],

CONSTRAINT [DF_t3_readcount] DEFAULT (0) FOR [readnum]

GO

CREATE INDEX [IX_t3] ON [dbo].[t3]([ref] DESC ) ON [PRIMARY]

GO

*************************************************************************

2. 데이터 입력

*************************************************************************

set nocount on

--변수 선언

declare @name varchar(20)

declare @email varchar(50)

declare @password varchar(20)

declare @title varchar(100)

declare @ip varchar(15)

declare @content varchar(200)

--루프용변수

declare @iCount int

--초기화

set @name = '이성근'

set @email = 'rainn@rainn.pe.kr'

set @password = 'abcdefg'

set @ip = '127.0.0.1'

select @iCount = max(seq) from t3

set @iCount = @iCount + 1

while (@iCount <= 1000000) -- 이 조건문에 1,000,000 을 주면 100만개의 레코드를 만듭니다.

begin

set @title = cast(@iCount as varchar) + ' 번째 글입니다.'

set @content = cast(@iCount as varchar) + ' 번째 글의 내용입니다.'

insert into t3 (ref, name, email, password, title, ip, content) values (@iCount, @name, @email,@password, @title, @ip, @content)

set @iCount = @iCount + 1

end

이하 설명은 다음 강좌에서 하겠습니다. ^^*

집어치우라고 돌던지셔도 좋습니다 ^^;;

지난 시간에는 테이블 생성 스크립트와 자동 게시물 삽입 스크립트를 살펴 보았습니다. 이번 시간에는 게시판 테이블에 레코드가 삽입되는 로직에 대해서 알아보겠습니다.
/****** Object: Table [dbo].[t3] Script Date: 2003-07-19 오전 12:37:23 ******/ CREATE TABLE [dbo].[t3] ( [seq] [int] IDENTITY (1, 1) NOT NULL , [ref] [int] NOT NULL , [depth] [smallint] NOT NULL , [name] [varchar] (20) COLLATE Korean_Wansung_CI_AS NOT NULL , [email] [varchar] (50) COLLATE Korean_Wansung_CI_AS NULL , [password] [varchar] (16) COLLATE Korean_Wansung_CI_AS NOT NULL , [title] [varchar] (100) COLLATE Korean_Wansung_CI_AS NOT NULL , [writeday] [datetime] NOT NULL , [readnum] [smallint] NOT NULL , [ip] [varchar] (15) COLLATE Korean_Wansung_CI_AS NOT NULL , [content] [text] COLLATE Korean_Wansung_CI_AS NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[t3] WITH NOCHECK ADD CONSTRAINT [PK_t3] PRIMARY KEY CLUSTERED ( [seq] DESC ) ON [PRIMARY] GO ALTER TABLE [dbo].[t3] ADD CONSTRAINT [DF_t3_depth] DEFAULT (0) FOR [depth], CONSTRAINT [DF_t3_writeday] DEFAULT (getdate()) FOR [writeday], CONSTRAINT [DF_t3_readcount] DEFAULT (0) FOR [readnum] GO CREATE INDEX [IX_t3] ON [dbo].[t3]([ref] DESC ) ON [PRIMARY] GO 간략한 테이블 명세표 입니다. seq : 글번호 ref : 글순서 depth : 답글깊이 name : 이름 email : email password : 암호 title : 제목 writeday : 글쓴시각 readnum : 조회수 ip : 글쓴이의 IP content : 내용 특징은 seq : PK (DESC) ref : Index (DESC) 라는 점 이외엔 없지요. ref 라는 컬럼은 글순서 입니다. 여러가지 ref 컬럼의 설계 로직들이 있었으나,
본 게시판에서 ref는 단순 정수형의 컬럼이면서 글 순서가 들어갑니다. 즉 시나리오는 다음과 같습니다. ----------------------------------------------- 1. 사용자가 글을 입력

seq	ref	depth
1	1	0

2. 이후로 10개 입력

seq	ref	depth
10	10	0
9	9	0
8	8	0
7	7	0
6	6	0
5	5	0
4	4	0
3	3	0
2	2	0
1	1	0

3. 4번글의 답변글 입력

seq	ref	depth
10	11	0
9	10	0
8	9	0
7	8	0
6	7	0
5	6	0
4	5	0
11	4	1
3	3	0
2	2	0
1	1	0

빨간색 부분을 유심히 보아 주시기 바랍니다.
답변글이 입력 될때는 부모글(seq:4, ref:4)이 있지요.
일단 부모글의 ref(4) 가 지금 들어갈 글(11번) 의 ref(=4)로 결정하고,
부모글의 ref(4였겠죠) 이상인 ref를 모조리 +1 update 합니다.
(query : update [테이블] set ref = ref + 1 where ref >= [부모글의 ref] )
depth는 항상 부모글의 depth + 1 해주면 간단합니다.

오래된 게시물일 경우 시간이 오래 걸리는 이유는 바로 이 때문입니다.
하지만 무시할만큼의 이유는 충분하다고 생각합니다. 게시판의 특성상...

이해를 돕기 위해 세가지 경우를 더 보여드립니다.

4. 11번글(4번글의 첫번째 답변글)의 답변글 입력

먼저 추측을 하면,
11번글(seq:11, ref:4) 이 부모글이니까
이번글의 ref는 4가 되겠죠. 그래서 일단 ref가 4 이상인 ref를 +1 시켜주고
자신의 ref를 4로서 insert 합니다.
(순서에 주의하셔야 합니다! update가 먼저고, insert가 나중이어야 합니다.)

seq	ref	depth
10	12	0
9	11	0
8	10	0
7	9	0
6	8	0
5	7	0
4	6	0
11	5	1
12	4	2
3	3	0
2	2	0
1	1	0

5. 새글 입력

seq	ref	depth
13	13	0
10	12	0
9	11	0
8	10	0
7	9	0
6	8	0
5	7	0
4	6	0
11	5	1
12	4	2
3	3	0
2	2	0
1	1	0

6. 13번글의 답변글 입력

seq	ref	depth
13	14	0
14	13	1
10	12	0
9	11	0
8	10	0
7	9	0
6	8	0
5	7	0
4	6	0
11	5	1
12	4	2
3	3	0
2	2	0
1	1	0

이해가 되십니까?

가장 상위의 ref 값을 게시물의 개수로 활용할 수도 있습니다.
MAX 함수를 쓰실 필요 없이 "SELECT TOP 1 ref FROM t3" 하시면 됩니다. index 가 걸려 있기 때문에..


이해가 빠르신 분들은 여기까지만 들어도 구현하실 수 있으리라 봅니다.
다음시간에는 asp페이지로 구현하는 부분에 대해서 설명하도록 하겠습니다.
감사합니다.


===============================
이성근 (rainn@rainn.pe.kr)
Soongsil Univ. Computer Science '95
(주)산그림닷컴
===============================

------------------------------------------------------------------

지난 시간에는 게시판 테이블에 레코드가 삽입되는 로직에 대해서 알아 보았습니다.
이번 시간에는 list 페이지에 필요한 asp 코드들을 대략 살펴보겠습니다.

---------------------------------------------------------------------

지난 강좌를 보셔서 아시겠지만 현재 테이블은 ref의 역순이 그 글이 보여지는 순서가 됩니다.

페이징은 간단하게 처리할 수 있겠습니다.

우리가 원하는 글의 ref 만을 불러오면 되는 거죠!

(사실 이 방법을 쓰기 전까지
select top * ... 과 rs.move 를 이용한 게시판이 그나마 젤 나은 성능을 보였습니다.)

원하는 글의 ref 불러오는 방법은..

SELECT [컬럼1], [컬럼2], .... FROM [테이블] ref BETWEEN [가장아래에보여줄글번호의ref] AND [가장위에보여줄글번호의ref]
ORDER BY ref DESC

가 되겠습니다.
간단하지 않습니까?
"ORDER BY ref DESC"에 의한 속도 저하는 없다고 보시면 됩니다.
제가 알기로는 ref에 걸린 index로 인해 쿼리최적화기기가 무시하게 되는듯 합니다.
(즉 ORDER BY 이하를 생략하더라도 글 순서는 변하지 않습니다. index를 DESC로 만들어준 이유가 거기에 있는거죠..)

이 방법으로 인해 우리는 페이지 값에 상관없이 언제나 동일한 페이징 속도를 낼 수가 있게 되는 거심니다!

그럼 우리가 해야 할 일은
시작ref와 끝ref 만 구해주면 되겠습니다.

자, 답은 다음과 같습니다.

start_num = record_count - (page - 1) * record_size
end_num = start_num - record_size + 1

start_num : 시작번호
end_num : 끝번호
record_count : 레코드 수이면서 가장 최상위글의 ref 입니다.
record_size : 한 페이지에 보여줄 게시물의 수
page : 현재 페이지 번호

record_count 는 지난번에도 말씀드렸듯이

SELECT TOP 1 ref FROM t3

으로 구하시면 되겠지만, 중간에 레코드를 삭제하여 이빨이 빠져있으면 안됩니다.
그러면 최상위 ref = 레코드 수 라는 법칙이 깨져버리기 때문입니다.

굳이 레코드를 삭제하고 싶으시면 ref를 적절히 변경해 주셔야 하겠죠?
- 지우고자 하는 글의 ref를 기억한다음
- 일단 레코드를 지우고
- 지운 글의 ref 보다 큰 ref를 가지는 레코드들의 ref를 1씩 빼주시면 됩니다.

하지만 답변형 게시판에서의 삭제 로직은 레코드 삭제를 대부분 비추하는 형태이죠..
답변글의 부모글이 지워지는 경우 화면 구성이 이상하게 될 수도 있겠죠..


record_size 는 원하는 값으로 아무 값이나 주시면 되겠죠..

시작ref와 끝ref 를 다 구했으면
위에 설명한대로 원하는 레코드만 샤악~ 불러오면 됩니다!

sql = "SELECT seq, title, name, writeday, readnum, depth FROM t3 WHERE ref BETWEEN " & end_num & " AND " & start_num & " ORDER BY ref DESC"
rs.Open sql, dbcon, adOpenForwardOnly, adLockReadOnly, adCmdText

보다 나은 속도를 위해 CURSORTYPE, LOCKTYPE, OPTION을 명시해 주시면 되겠습니다.
주의할 점은 start_num 과 end_num 의 위치입니다.!! 꼭 순서에 주의해 주시기 바랍니다.
순서 바꾸고 제대로 안나온다고 저한테 돌던지지 마세요 ^^*

참고로 총 페이지 수(page_count)는

page_count = int(record_count / record_size)
if (page_count * record_size) <> record_count then page_count = page_count + 1

와 같이 구하시면 되겠습니다.




다음시간에는 write, reply 에 대해서 알아보겠습니다.
감사합니다.

지난 시간에는 list 페이지에 필요한 asp 코드들을 대략 살펴보았습니다.
이번 시간에는 write, reply 페이지에 필요한 asp 코드들을 살펴보겠습니다.

---------------------------------------------------------------------

이미 이전의 강좌들로 충분히 아실 수 있을거라 생각합니다.
간단하게 정리하겠습니다.

1. 새글 쓰기
- 새글의ref = 최상위ref + 1

코드 예제)

sql = "select top 1 ref from t3"
rs.open sql, dbcon, adOpenForwardOnly, adLockReadOnly, adCmdText

if (rs.bof or rs.eof) then
ref = 1
else
ref = rs(0) + 1
end if
rs.close

sql = "insert into t3 (ref, name, email, password, title, ip, content) values ("
... 이하 생략...


2. 답변글 쓰기
- 답변글의ref = 부모글의ref
- ref가 부모글의 ref 이상인 레코드들의 ref를 각각 +1 증가

코드 예제)

ref = view_ref ' 답변글의 ref = 부모글의 ref
depth = view_depth + 1 ' 답변글의 depth = 부모글의 depth + 1

sql = "update t3 set ref = ref + 1 where ref >= " & ref
dbcon.execute sql,, adCmdText + adExecuteNoRecords

sql = "insert into " & table_name & " (ref, depth, name, email, password, title, ip, content) values ("
... 이하 생략...



이상으로 허접한 강좌가 마무리 되겠습니다.
자, 여기서 욕심을 내셔서 stored procedure를 사용하는 게시판으로 변형하시는 것은
여러분들의 몫이고 또한 실험정신이겠죠! ^^

그동안 읽어주셔서 감사합니다.

강좌쓰기 힘들군요. ^^;;; 글솜씨가 없다보니..

기타 하시고 싶은 말씀이나, 질문은 이곳 데브피아 게시판 혹은 위 URL로 접속하셔서 남겨주세요 ^^


아... 그리고 소스를 공개해달라는 부탁을 받았는데요,,,
소스 공개는 하지 않을 생각입니다.
핵심 부분은 이미 다 말했고, 나머지는 여러분의 힘으로 가능하시리라 보니까요. ^^
왠지 고기를 잡아주기 보다는 고기낚는 법을 알려주고 싶어하고픈 욕심이라 보시면 되겠습니다. ^^;;;;; 에공..

===============================
이성근 (rainn@rainn.pe.kr)
Soongsil Univ. Computer Science '95
(주)산그림닷컴
===============================