Database/MySQL 2007/04/25 01:06
<서문>
본 내용은 필자가 직접 구현해보고 테스트해본 것을 토대로 이야기하고자 한다.
PostgreSQL 환경에서 구현한 것으로 어쩌면 이 내용이 개개인에 따라 전혀 쓸모가 없을지도 모른다. 그런 사람들에게는 이런 방법도 있구나... 소개하는 글로 봐 주길 바란다.
이 방법이 결코 그 어떤 방법보다 좋다 라고 말하고 싶지는 않다. 이것보다 훨씬 더 좋은 방법이 무수히 많고 각자 사정에 따라 차이가 있을것이기 때문이다.
단지 이 여기서는 이런 방법도 있구나... 라는 것과 속도개선의 1% 라도 도움이 되고자 하는데 그 목적이 있으며, 그 외에는 다른 의미를 두지 않는다.
이 방법의 채택은 각자 상황에 맞게 하길 바란다.
본인은 DB 에 해박한 지식을 가지고 있지도 않으며, 전문가는 더더욱 아니다.
그러므로 예제로 사용된 쿼리문은 최적화가 되어 있지 않다. 쿼리의 최적화는 개개인의 몫이다.
<테스트환경>
linux 2.6, PostgreSQL 7.4, P4 1.8G, 512RAM, 삼성 IDE 30G, 개인용 컴퓨터
테스트자료: 실전과 같은 환경을 구성하기 위해 실제 데이타를 입력하였다.
(랜덤방식의 자료생성 혹은 간단한 내용으로 구성된 페이지가 절대 아니다.)
사용된 자료는 옛날 적수네동네에 있던 자료로 LSN 이 파장하기전에 백업해 둔 자료를 그대로 이용하였다.
(이 자료는 아직도 그곳에 있다. ftp://linux.sarang.net/pub
여기에 qa 와 tip 의 백업자료가 있다. tip 는 약 9만건, qa 는 약 18만건이다)
입력의 편리를 위해 하위구조로의 데이타 입력은 하지 않았다.
하지만 계층형으로 입력했다 하더라도 결과는 같다고 생각한다.
자료갯수: 145250건
테스트결과: 전체리스트에서 어느페이지를 가던지 0.5초 이내로 출력완료
(offset 을 뒤에서 시작하므로 뒤로갈수록 오히려 더 좋은 속도(0.1 정도)
검색리스트에서 첫페이지 2초 정도 다음부터는 1초내에서 모두 출력완료
(캐쉬덕분인지.. fti row 가 지금 거의 7백만인데.. 검색속도가
느린편인가.. 관련DB 의 총 용량이 2.5기가 --;
검색대상은 전체이며, 제목, 내용 등의 구분없이 통합검색)
글쓴이검색리스트의 경우 0.1초 내로 모두 출력완료
여기서 출력속도는 페이지의 템플릿 처리속도가 모두 포함됨
어때요? 이정도면 시도해볼만한가요? 솔직히 용량에는 부담이 마니가네요
15만건이면 그냥 테이블 하나면 적어도 200메가 선에서 끝날텐데.. 파생된거,
인덱스 등등 모두 포함하니 2.5기가나 나오네요..
stopword 만 제대로 구성한다면, 훨씬 빠른 검색속도가 나올거 같네요
물론 용량도 마니 줄어들테고요..
<목적>
계층형 구조에서의 검색과 일반 리스트에 대한 속도향상이 그 주된 목적이다.
물론 지극히 당연한 이야기이지만 속도향상을 위한 가장 좋은 방법은
장비의 업그레이드이다.
<특징, 규칙>
fulltext index 를 구현한다. - PostgreSQL 의 fulltext index 기법에 MySQL 의 fulltext index 를 적용하여 직접 구현하였다. (pgsql, mysql 모두 한글을 지원하지 않기에 - 지금은 지원될려나..-)
최소한의 쿼리를 사용한다. - 여기서 최소한의 쿼리란, 어떤 처리를 위하여 내부적으로 행해지는 쿼리의 전체를 의미하는것이 아니라, 웹상에서 사용자가 날리는 쿼리수를 말하는것이다. (보통 1회, 가끔 2회)
DB 가 할 수 있는 것은 DB 에게 맡긴다. - 위의 쿼리수와도 관계가 깊다. 거의 모든 실제적인 처리는 사용자평션과 트리거가 담당하게 하여 불필요한 쿼리를 줄이고, 데이타의 꼬임 등을 방지한다.
용량과 데이타 갱신속도가 많이 걸린다. - fulltext index 의 영향이다. 검색속도의 향상을 위해 감수해야 할 부분이다.
되도록이면 index 를 타게 한다.
유니코드를 사용한다. (유니코드하에서 한글한자도 길이가 1 로 나타나기에)
기타등등 - 구현방법에서 나머지 부분을 설명하도록 하겠다.
<비업데이트형>
대용량자료의 계층형 구조에 대한 속도향상에 대한 많은 논의가 있었다. 그 방법은 크게 풀업데이트, 반업데이트, 비업데이트 형으로 나뉜다. 반업데이트형을 택할려다가
정렬을 위한 업데이트 과정이 포함되므로, 차라리 비업데이트형을 택했다.
thread 표현 방식에 대한 많은 이야기도 있었다. 그중 괜찮은 방법이 소숫점을 이용하거나 정수,소수 부분을 분리한 형태가 와닿았다.
소숫점을 이용한 실수형 컬럼은 많은 제약이 따르며, 또한 실수연산에 있어 조그마한 오차나 오류가 발견되기도 하여, 실수형은 적합하지 않다고 판단, 결국 문자형 thread 를 택했다. 문자형은 2자리로 표현하며, 아래 테이블 구조에서 ptr 컬럼이 이에 해당한다. 16?
수형태를 사용한다. (00 ~ FF 의 문자사용으로 256개의 리플과 무한깊이를 지원한다.) 만일 256 개가 작다 생각되면 3 자리를 사용하라(000 ~ fff 로 256 x 16 = 4096개의 리플수를 지원해줄 것이다.)
숫자형이 좋겠지만 숫자형은 9개만을 사용하므로, 많은 리플수를 지원할 경우
thread 길이가 많이 필요하게 된다. 제대로된 깊이까지 지원할려면 int4 로는 무리고
int8 을 사용해야 하므로 차라리 문자형을 택했다.
<DB 의 선택>
본인은 MySQL 이 정말 좋은 것이라 생각한다. 하지만, 이것은 속도에 너무 치중한 나머지 많은 부분이 구현되지 않았거나 미약하다. 많이 발전하고는 있지만, 아직은 그렇다고 생각한다.
같은 환경에서 단순쿼리의 경우 MySQL 이 당연히 속도가 빠르다. 하지만, 실무에서는 단순한 테이블구조보다는 복합적인 구조가 대부분이므로, 이럴 생각한다면 PostgreSQL 을 한번쯤 사용해보기를 추천한다. 그래도 공개용 중에선 거의 최고라 생각한다.
복합적인 테이블 구조에서의 원하는 결과를 출력하는데 오히려 복잡하면 복잡할수록
MySQL 이 속도가 더 떨어지거나 비슷할 것으로 예상된다.
사실 처리속도가 상당히 중요하지만, 0.001 과 0.1 초는 사람이 그렇게 차이를 느끼지 못한다. 이런 속도차이를 감수할수 있다면,
또한 MySQL 을 사용하다. 더 많은 기능의 필요성이 느껴졌다면, 한번 사용해 보길
바란다.
<숙제>
fulltext index 를 구현해 놓았기 때문에 fulltext index 를 최대한 활용해야 한다.
하지만 무작정 어절(공백)을 기준으로짤라 넣기만 한다면, 엄청난 row 가 생기게 될 것이다. 또한 검색속도와 용량에 직접적 관계를 가지기 때문에, 어떤 규칙을 만드느냐에 따라 엄청난 차이를 보일수 있다.
물론 단어사전 등을 이용할수도 있지만, 그것을 이용할려면 많은 스킬이 필요하게 될 것이다.
아무튼 fulltext 의 규칙은 정말 중요하니만큼 잘 세워야 한다.
본인은 테스트를 위해 간단히 그 룰을 만들었다.
stopword 라는 테이블을 만들고 그 안에서 검색에서 제외시킬 단어를 입력하였다.
또한 특수문자들은 모두 공백으로 처리하며, 한자(영문 혹은 한글)의 어절은 모두 제외시켰다.
stopword 테이블은 MySQL 의 ft_state.c 인가 에서 힌트를 얻었다.
원래는 단어사전을 만들고 그것에 등록되어 있지 않는 것은 제외할려고 했으나,
사전의 크기가 엄청난데다가 비교쿼리시간만 해도 오래 걸릴뿐더러, 이럴 제대로 이용할려면 어절별로 형태소 분석 등을 해야 한다. 그래서 포기하고 차라리 검색어로서의 가치가 없거나 사용될 확률이 아주 희박한 글자들을 등록하고 이럴 제외 대상으로 삼는게 좋다?
생각했다.
참고로 '한다, 합니다, 하고, 하여, 했다, 했습니다' 등등은 검색어의 자질이 전혀 없으며, 검색어로 사용할 확률도 극악으로 낮다. 이런 말들은 제외하는게 좋으며, 테스트 자료로 LSN 의 것을 사용했다. 사이트 특성상 리눅스를 다루는곳으로, 리눅스 혹은 linux 를
탔텝모든 게시물에서 사용하고있었다. 이런 단어들은 제외시키는게 좋다.(리눅스 게시판에서 리눅스로 검색할 사람이 과연 얼마나 있을까..? 다른 게시판이라면 삭제하면 곤란하겠지...)
암튼 이 문제는 각자 룰을 설정해보기 바란다.
또한 소개된 쿼리문이 복잡하게 느껴질지도 모른다.
좀 더 간단하게 하기 위하여 사용자평션을 이용하던지, view 를 만들던지 하여 간단하게 표현해도 좋을거 같다. 여기서는 어떤식으로 쿼리가 이루어지는지를 보여주기 위함이니 이런 것은 각자 자신의 스킬에 맞게 하면 될 것이다.
<실전>
이제 구현해보도록 하자. 본인은 이 방법을 사용함으로써 검색 및 리스트 출력에 많은 속도향상을 느꼈다. 검색에서 무지막지한 like '%str%' 는 정말 좋은 방법이 아닐상 싶다. 그래서 이 방법을 소개할 따름이다.
실무에의 적용은 각자 환경을 생각해야 할 것이다.
테이블구조 - 게시판 하나당 총 5개가 필요하며, 공통으로 1개의 테이블이 더 필요하다. 경우에 따라 모두 필요하지는 않다.
테이블 이름은 기본테이블의 문자열을 포함한 파생된이름을 갖는것이 관리상 좋을 것이다.
컬럼은 중요한 부분만 표시하고 나머지는 알아서..넣길 바란다.
또한 일일이 아래처럼 하지 않고 foriegn key 를 이용해도 좋다. 각자 스킬에 맡기겠다
bd - 기본 테이블, 한개의 테이블로 운영할때의 컬럼 구성과 동일하다. 오직, insert, update, delete 쿼리와 본문보기를 위한 테이블이다.
bd_info - 게시판 정보들을 넣는다. 게시물 전체의 갯수를 저장하는 필드도 필히 넣도록 한다.
bd_title - 리스트 출력을 위한 테이블, 필드구성은 목록보기에서 보여줄 필드만 있으면 된다. 평균적으로 본문이 길 경우 분리하는게좋다. 만일 리스트에서 코맨트수를 포시해야 한다면, 그 컬럼도 같이 넣는다, (물론 bd 테이블에도 있어야 한다.)
어떤이는 코맨트수를 보여주기 위하여 리스트 출력에서 루프로 코맨트수를 구하는 로직을 구사하기도 한다. 좀 미련한 방법이라 생각한다)
bd_comment - 코맨트용 테이블, 일반적인 코맨트테이블 구성과 동일하다.
bd_fti - fulltext index 를 위한 테이블, 검색을 위한 테이블로 어절들이 모두 이곳에 들어간다.
stopword - 말그대로이다. bd_fti 에 저장하지 말아야 할 단어를 이곳에 저장한다.
검색에서 제외시킬 단어와도 상통한다.
create table bd ( -- 기본 메인 테이블
uid serial primary key, -- 고유번호, auto_increment
pid int, -- 원문글번호
ptr text, -- thread
id text,
passwd char(32),
----- -- 그외 필요한 필드들
);
인덱스 걸기 (uid 는 Primary 로 걸필요없다)
create table bd_info (
total int, -- 총 게시물수
---- -- 기타등등 필요한 설정필드들
);
create table bd_title ( -- 기본적으로 이 테이블은 기본(메인) 테이블을 그대로 따르고, 목록출력에만 필요한 필드들로만 구성한다.
uid int, -- auto_increment 안한다.
pid int,
ptr text,
--- -- 기타등등 목록출력에 필요한 필드들
);
인덱스 걸기 (uid, (pid,ptr) pid 와 ptr 은 묶어서 건다)
create bd_comment ( --이 부분은 알아서..구현하도록
uid int, -- 원문번호
id text,
--- -- 기타등등
);
인덱스 (uid)
create table bd_fti (
uid int, -- 게시물번호
ft text, -- 자른 단어
kind text -- 제목, 내용, 커맨트 등의 구분, 숫자로 해도 된다. 편한데로)
);
(여기서 kind 는 해당파트별 검색에 쓰이기도 하지만 중요한 이유가 있다.
트리거에서 해당글을 수정하거나 삭제하는데 중요한 역활을 한다.)
인덱스 (uid, ft)
create table stopword ( word text primary key);
테이블 초기화
원문글이 -1 이 되게 하기 위해 시퀸스 번경
alter sequence bd_uid_seq increment -1 restart 2147483647;
(21억부터 시작하며 추가시 -1 씩 자동부여된다, 만일 21 억으로 모자란다면
테이블 생성시 uid 를 bigserial 로 하고 관련 테이블 전체에서 uid 와 pid 를 bigint 로 한다.
시퀸스 시작번호는 9223372036854775807 까지 가능하므로 그렇게 바꾼다)
고유번호를 거꾸로 기록하는 이유는 pid 와 ptr 을 같은 방향으로 정렬하기 위함이고,
만일 다른 방향으로 정렬하면 인덱스를 타지 않기 때문이다. 또한
최근 게시물이 위쪽이며, 최초 리플이 위쪽에 출력되는 방식을 택하기 위해서이다
insert into bd_info values(0, .....);
total 값을 0 으로넣는다.
bd 테이블과 bd_comment 의 insert, update, delete 이벤트에 대한 트리거를 건다. (그 전에 사용자 평션을 먼저 만들어놓아야 한다.)
사용자평선작성요령은 필요한 곳에서 설명한다.
stopword 에 제외한 단어들을 수동 입력한다.
이제 테이블을 다 만들었으니 자료를 입력하자.
실제 자료를 입력하기 전에 반드시 필요한 모든 사용자평션과 트리거 제외문자열
등을 미리 완성시켜 놓자. (vacuum 시간이 장난이 아니다..)
자료는 위에 밝혔듯이 LNS ftp 에 아직도 백업파일이 그대로 남아있다.
다운받은 자료를 그대로 입력하는데는 시간이 얼마 걸리지 않는다.
하지만 fti 구현을 위한 테이블에 다시 입력할때는 index 작업으로 수시간의 시간이 소요될 것이다. 인내를 가지도록...
입력, 수정 시간이 오래 걸리긴 하나 하나하나 게시판에서 직접 글을 적어보면 그렇게 오래 걸리는것도 아니다. 대량으로 그대로 넣는거니 감수하도록 하자.
이제 웹작업을 해보자.
웹에서의 쿼리는 단발성으로 하는것을 원칙으로 한다.
하지만 때에 따라 2번의 쿼리가 필요하긴 하다.
일반리스트에서의 게시물수는 bd_info 를 이용하고 검색된 게시물갯수는
xx_num_rows() 보다 select count(*) 를 이용하도록 하자.
어떠한 방법으로던지 게시물수를 구하는것은 페이징에 필요하므로 반드시 필요하다.
목록보기에서 먼저 temp table 을 만들자
(주의: 이 temp 는 세션에 종속되어야 하며 세션별 공유가 절대 일어나서는 안 된다.
예를 들어 일반 테이블처럼 어떤이가 수정을 가하면 다른이에게도 수정된 내용이 보인다. 이렇게 되면 문제가 발생되므로 일반 테이블이 아닌 temp 테이블을 만들도록 한다. 이 테이블의 row 의 수는 많지 않다. 제목테이블과의 조인을 위해 필요하다. 검색이 아닌 일?
적인 목록출력에서도 이용하자(나쁘지 않다) 이 테이블은 접속이 종료되면 자동삭제(gc) 가 되므로 항상 먼저 생성하여야 한다)
create temp table _tb (idx serial, uid int);
필요한 목록출력 쿼리내용을 temp 에 넣는다.(고유번호만 넣는다)
쿼리를 어떻게 줄 것인가..
일단 전체일 경우는 일반적인 쿼리를 준다.
내용검색의 경우 이렇게 한다.
(주의: 글쓴이의 검색은 제목테이블에 직접 하고, 내용, 코맨트, 제목 검색 혹은 통합검색일 경우는 fti 테이블에 한다.)
$query = "select a.uid from ".$table."_title as a, (select distinct on (uid) uid from ".$table."_fti where ft like '$word%') as b where a.uid = b.uid order by a.pid desc";
대충 이 정도가 될 것이다. 간단하게 fti 테이블에서 $word 를 검색하여 나온것을 중복을 없앤 것에서 uid 필드만 취한다.
여기서 pid desc 정렬을 했는데 인덱스 잘 탄다. 또한 원래는 asc 로 해야 하나
limit 의 offset 을 뒤에서 부터 구하기 위해 desc 를 사용했다. 나중에 최종 결과는 temp 에서 다시 desc 를 하니 제대로된 결과가 나온다.
이제 총개시물수(검색포함) 를구했으니, limit offset 값을 구해보자
$offset = 총게시물수 - (해당페이지번호 * 페이지당게시물수);
if($offset < 0) {
$limit += $offset;
$offset = 0;
}
여기서 $limit 의 초기값은 페이지당게시물수 이다.
이제 limit 값과 offset 값을 구했다.
이제 최종(?) 결과물을 temp 에 넣어 출력해보자
insert into _tb (uid) ($query limit $limit offset $offset);
select a.*, (length(a.ptr)/2)-1 as depth from ".$table."_title as a, _tb as b where a.uid = b.uid order by b.idx desc;
이 select 의 결과물은 해당 페이지의 출력을 해 줄 것이다.
목록출력에서 나머지 루틴은 기존 해 왔던 방식으로 하면 될 것이다.
위에서 depth 구하는부분 length(a.ptr)/2)-1 이 있는데
ptr 을 2자리씩 만들었기 때문에 2를 나누었다.
출력될 순서화된 게시물 번호(화면상의 게시물 번호) 계산은 이렇게 한다.
$no = 총게시물수 - (페이지당게시물수 * (해당페이지 - 1)) - $i;
여기서 $i 는 출력을 위하여 루프를 돌리는데 사용되는 변수이다.
for($i = 0; $i < 페이지당게시물수; $i++) {
마지막row 이면 break;
$uid = xxxx;
어쩌고 저쩌고;
}
여기에서 $i 가 이에 속한다.
목록 출력은 검색페이지 겸용 해서 모두 끝났다.
이제 본문출력을 해보자.
본문출력은 bd 테이블에 uid 로 뽑아내면 아주 쉽게 할수 있으리라 본다.
본문 아래에
이전글, 현재글, 다음글 등이 나오게 하는 쿼리만 간단히 소개한다.
여기서는 한가지만 나오므로 temp 에 넣어 다시 조인할 필요가 전혀 없다.
_tb 이용은 리스트 출력만으로 족하다.
이전글: 현재글보다 최근의 글로 바로 위의 글이며, 리플글이 아닌 원문글이다.
검색일경우 글쓴이는 생략한다. (누구나 할수 있을것으로 예상)
전체:
select uid, subject from ".$table."_title where pid < $pid and ptr = '00' order by pid desc limit 1
검색:
select a.uid, a.subject from ".$table."_title as a, ".$table."_fti as b where b.ft like '$word%' and a.uid = b.uid and a.pid < $pid and a.ptr = '00' order by a.pid desc limit 1";
현재글: 현재글을 포함한 현재글과 관련된 모든 글을 표시한다. 원문글로부터 관련된 모든 글이 이에 해당한다.
전체:
select uid, subject, (length(ptr)/2)-1 as depth from ".$table."_title where pid = $pid order by ptr asc;
검색:상황에 맞게 여러개의 row 가 나타날것이다 순서대로 depth 에 맞게 표현하기만 하면된다.
select a.uid, a.subject, (length(a.ptr)/2)-1 as depth from ".$table."_title as a, (select distinct on (uid) uid from ".$table."_fti where ft like '$word%') as b where a.pid = $pid and a.uid = b.uid order by a.ptr asc;
다음글: 현재글보다 이전의 글로 가장 최근의 이전의 글이 이에 해당한다. 원글만 나타난다. 이전글과 반대이다.
이제 출력부분은 모두 끝이 난거 같다.
(잠깐, 여기서 왜 이런 쿼리들이 사용되었는지는 이것만 봐서는 도무지 알 길이 없다. 먼저 insert 부분을 봐야 이해가 갈거 같다. thread 의 기본은 비업데이트형이며, 문자컬럼을 사용하기 때문에 그렇게 낮설지 않을 것이다.
이제 입력부분을보도록 하자
먼저 처음으로 돌아가서 각 필드의 역활과 운영방법을 먼저 이야기해야 할것 같다.
uid : 고유번호이며, 자동감소를 한다.
즉, 가장 최근의 글은 가장 숫자가 낮은 것이며, 가장 오래된 글은 가장 숫자가 높은 곳이다.
pid : 원문글의 번호이다. (두말할 필요는 없을거 같다. 이것이 없으면, 글의 뿌리를 알수 없다. 새글일 경우 자기자신의 고유번호가 pid 가 되며, 리플글일 경우, 가장 윗단계의 부모글이 Pid 가 된다. 만일 1 번 아래 2 번 그 아래에 리플을 단다면 리플달 게시물의
id 는 1 이 된다(2 가 아니다!!) 만일 이 경우라면 pid 가 2 나 3 이 사용될 곳은 어디에도 없게 된다. 즉, 반드시 최상의 부모글(시조글)이 pid 가 됨에 유의하자)
ptr : 흔히 말하는 thread 가 이에 해당한다 여기에서 알수 있는 것은
바로위 부모글과 깊이이다.
ptr 은 두자리씩 한 쌍을 이루어 구성된다.
(총 256개의 리플이 가능하다(그 하위가 아닌 동급의 리플)
이보다 더 많은 수를 원한다면 3자리로 구성하라.
사용되는 문자열은 0 ~9 까지의 자연수와 a ~ f 까지의 영문자이다.
영문자는 한쪽으로 통일하라(소문자 혹은 대문자로)
이 문자만을 사용하는 이유는 계산의 편함을 위해서 이다. 이 문자의 구성은 16 진수와 똑같으니...
새글의 경우 무조건 00 이다
답글의 경우는 아래롤 보라..
depth 는 ptr의 문자열 길이를 반으로 나눈후 1 을 빼면 된다.
빼는 이유는 원문글이 무조건 00 으로 첫 두 자리를 차지하기 때문이다.
새글쓰기:
새글쓰기는 그리 어려워 보이지 않는다.
ptr 는 무조건 "00" 이며, 어려운점이 있다면 pid 를 위해 쿼리를 한번 더 날려야 하겠다. pgsql 에서는 그럴 필요가 전혀 없다.
uid = nextval('bd_uid_seq') 이고 pid 는 uid 와 같으므로 currval('bd_uid_seq') 하면 될 것이다.
답글쓰기:
답글도 그리 어려워 보이진 않는다.
pid 는 원문글에서 pid 를 가져오면 될 것이고, 여기서는 ptr 이 문제라면 문제다.
thread 계산을 해보자.
select substring(ptr from '$ptr(..)') as thread from ".$table."_title where pid = $pid order by ptr desc limit 1;
이렇게 하면 현재 글에 대한 원문에 대해서 현재글의 깊이의 한단계 아래글들 중에서 가장 나중의 글 중 두자리의 thread 값을 읽어온다.
좀 더 쉽게 풀이하면
현재의 글의 Ptr 이 01AB 이라고 한다면
01AB 을 포함하는 게시물중에서 가장 나중에 달린것이 나오게 된다.
01AB, 01AB00, 01AB33, 01AB0001, 01AB3322 뭐 이런식으로 있다면 01AB3322 이 선택되고 그 중 33 이 결과로 뽑혀 나온다.
잠쉬 구조를 보자
01AB
01AB00
01AB0001
01AB33
01AB3322
01AB34
이런식의 트리구조를 가지며 위에서 01AB 에 리플을 다는것이니 01AB33 아래에 달려야 할 것이다 (01AB34)
만일 이 값이 없으면 그 게시물에 대한 리플이 하나도 없는 것이므로 다음에 달릴 두자리는 00 이 된다.
만일 있다면 0x 를 앞에 붙여 + 계산을 한다.
더한값이 0xff 보다 많으면 에러를 내고 종료한다.(제한리플수(256) 가 다 채워진 경우이다.)
만일 한자리 수라면 (0x4) 앞에 0 을 강제로 붙인다.
이제 구한 thread 값 앞에 원문의 ptr 을 붙인다.
위의 예에서 계산된것은 34 이고 원래것이 01AB 이니 최종 입력되어야 할 것은 01AB34 가 되겠다
이렇게 입력하는 쿼리를 한방 날리면 insert 는 끝난다. ????)
물론 다 끝난건 아니다. 이것은 기본 테이블에 넣는것이고
나머지관련 테이블은 웹에서 하는것이 아니라 db 서버에서 맡아서 처리하도록 해야한다.
여기에 사용될 것이 트리거와 사용자 정의 평션이다.
평션에서 Insert 부분은 이런식으로 구현한다.
bd_info 에 total 을 +1 해준다.
bd_title 에 필요한 필드만을 골라서 inssert 한다.
bd_fti 테이블에 insert 한다.
(이부분은 위에 좀 언급했다. 이 규칙을 잘만들어야 한다.
temp table 을 만든다 (bd_fti 와 구조가 똑같이 한다)
먼저 특수문자들을 공백처리 하고
전체 문장을 공백을 기준으로 짜른다.
한 어절로 된 것들은 모두 제외한다.
너무도 흔한 단어들은 제외한다.
예를 들어 phpschool 에서 게시판을 운영하는데
php 를 검색어로 사용한다는건 좀 이상한 검색이라 생각되므로 제외대상의 단어가 될 것이다.
각각의 어절을 소문자(혹은 대문자)로 통일한 후 temp 에 넣는다.
stopword 에서 비교하여 있는 데이타는 삭제한다.
ex: delete from _fti_tmp where ft in (select ft from _fti_tmp where ft in (select word from stopword));
(이 과정은 시간이 얼마 안 걸린다. ft row 가 많아야 몇천개이고 stopword 에 등록된 단어들도 많아야 만개이다 이 두 테이블간의 데이타 처리는 엄청나게 빠를것이다.
(2000 개 정도만 넣어도 엄청나게 줄일수 있다. 필자는 850 개 정도 (영문은 MySQL 에서 가져와 350 정도 한글 500 정도) 인데도 실제 삭제해보니 fti 테이블에서 백만개가 넘게 지워졌다.)
이제 중복데이타를 제거하고 실제 bd_fti 테이블에 입력하면 된다.
insert into bd_fti (select * from _fti_tmp intersect select * from _fti_tmp);
(여기서 중복제거를위해 intersect 를 사용하였다. 위의 stopword 를 제외할수 있는
except 등과 distinct 등등 편한대로 요리하도록 하자)
(주의: 본문내용만 할 것이 아니라 제목도 하는걸 잊지 말도록 한다.)
이제 트리거에서 insert 부분도 모두 끝났다.
수정:
웹상에서의 수정쿼리는 지극히 단순하다.
bd (기본테이블에) 수정 쿼리문 (일반적으로 사용하는 쿼리문) 그대로 사용하면 된다.
여기서도 트리거에서의 설정을 해줘야 한다.
트리거에서 어떤부분이 업데이트 되었는지 조사한다
제목이 업데이트 되었으면 제목부분을 처리하고 내용이라면 내용부분을 처리하고
둘다라면 둘다 처리한다.
조회수가 변경되었다면 제목테이블에서 hit 수를 +1 해준다.
코맨트가 하나 더 달렸다면 comment 수를 +1 한다.
(물론 웹상의 쿼리는 bd 에 한다.)
한가지만 예로 설명한다.
제목이 업 되었을 경우
원문글의 번호로 fti 테이블에서 검색하여 삭제한다.
delete from bd_fti where uid = $uid and kind = "title";
(주의 위에서 $uid 처럼 php 변수를 사용했는데 실제 SQL 문에서 사용하는 변수를 사용해야 한다. 알기쉽게하기 위해 일부러 그랬다)
insert 때처럼 제목부분을 fti 처리한다.
또한 bd_title 에서도 subject 필드를 update 명령어를 이용하여 업한다.
대충 수정 부분도 끝난거 같다.
이제 삭제 부분이다.
여기서의 삭제는
삭제되는 글 이후에 달린 모든 코맨트, 모든 하위리플들을 삭제하는 방식이다.
그렇지 않고 하위글부터 삭제를 유도할 경우 이런 방법은 필요는 없고 해당 게시물만 삭제하면 된다.
웹상에서 삭제 쿼리를 내린다.
delete from bd where uid = $uid;
이것 하나로 족하다 --;
(참고로 파일이 있을 경우 파일도 함께 처리해야 하는데
먼저 삭제 쿼리를 내리기 전에 삭제될 모든 게시물(하위리플들 모두 포함)에 대하여
파일명을 뽑아 배열에 미리 담아 두어야 한다.
그리고 위의 삭제쿼리 한번 내리고 이상이 없을 경우
배열에 쌓아둔 파일명을 차례차례 웹상에서 삭제한다.
이제 이에 대한 트리거에서의 처리를 보자
먼저 트리거에서 삭제 쿼리를 날린다.
delete from bd where pid = $pid and ptr like substring(ptr from '$ptr.*');
(위에서 php 스타일의 변수 역시 이해를 돕기 위해 썼다. SQL 에 맞게 써야한다)
delete from bd_fti where uid = $uid;
delete from bd_comment where uid = $uid;
delete from bd_title where uid = $uid;
update bd_info set total = total -1;
이렇게 하면 된다.
여기서 주의해야 할 점이 있다.
위의 첫 쿼리는 bd 테이블에서 관련글을 모두 삭제하는데 반해
아래의 4개의 쿼리는 각각 하나씩만 삭제하고 있다.
트리거의 delete 이벤트에 대한 처리가 있기 때문에 한꺼번에 삭제를 해버리면
문제가 발생한다. 그러므로 하나하나씩만을 처리해야한다.
위에서 전체 삭제에 의해 트리거의 delete 구문이 반복적으로 호출되기 때문이다.
또한 아래의 3개의 delete 문에 사용된 uid 는 첫줄의 $uid 와는 완전 틀리다.
첫줄의 쿼리에서 삭제되는 대상의 uid 값인 것이다.
반드시 조심해야 한다.
이렇게 하면 대충 delete 부분도 끝이 났다.
comment 의 수정 삭제 추가 등에 대한 내용은 스스로 채워보기 바란다.
거의 같은 구조로 되어 있으니..
단 comment 일 경우 테이블 자체가 틀리고
웹상에서의 쿼리도 bd 가 아닌 bd_comment 로 날리기 때문에
이에 대한 처리를 위해
bd_comment 테이블에 따로 처리하는 트리거를 걸어야 한다.
마지막으로 결과물의 갯수를 어떻게 구해야 효율적인지를 생각해보자.
여기서 갯수를 구하는것에는 갯수 뿐만 아니라 여러가지 계산 등등
집계함수의 모든 기능을 다 포괄적으로 말한다.
단지 예로서 갯수를 구하는것을 든 것 뿐이다.
가장 손쉬운 방법은 xx_num_rows() 함수를 이용하거나, select count(*) query
스타일일 것이다. 이런식의 사용은 데이타가 많아짐에 따라 현저한 속도저하를
가져온다.
그렇다면 어떻게 샐 것인가..
별도의 테이블을 구성하여 메인 테이블에서 트리거를 걸어 누적상태를 별도의
테이블에 저장하고 결과물은 그곳에서 가져오는 방식이다.
특히 연말정산, 월별정산 또는 통계 등등을 낼 경우
경우에 따라서는 루프를 돌려야 할때도 있고, 그 많은 건수를 (물론 구간이 있지만)
일일이 집계한다는건 엄청난 시간낭비이다.
입출력이 생길때마다 별도의 테이블에 갱신만 시키고 해당 필드를 액세스 하면
단 한줄의 row 에서 읽어오게 되므로 엄청난 속도향상을 가져올수 있다.
select count(*) from bd;
select total from bd_info;
어느것이 속도가 빠를지는 각자 판단하길 바란다.
그렇다면... 검색의 결과에 대한 갯수는 어떻게 샐 것인가..
참으로 어려운 과제인거 같다.
결과를 출력하기 위해선 어차피 한번은 검색쿼리를 줘야하고
페이징을 위해선 반드시 갯수 카운트를 해야한다.
검색조건이 어떻게 될지도 모르는데 위처럼 미리 정보를 갱신해둘수도 없다.
필자의 경우는 여러가지 고민끝에
결과물의 전체중 고유번호필드만 뽑아 임시테이블에 집어넣고
임시테이블에서 select count(*) 를 사용했다.
숫자컬럼 하나 뿐이니 그래도 그중 빠른 카운트가 아닐까 싶다.
더군다나
나온 결과물(고유번호만있찌만) 과 본 테이블과의 조인에서 원하는 결과를 바로
출력할수도 있으니..
해당 검색 쿼리를 두번 주지 않아도 된다고 생각한다..
여러분들은 이런 문제를 어떤식으로 해결하는지 궁금하기만 하다.
총갯수가 많으면 많을수록 검색속도가 느려지는데 이런 두번의 쿼리가 속도저하를
더더욱 가중시킬수 있기 때문에..
이에대한 좀 더 참신한 아이디어로의 논의가 필요할 것 같다.
대충 끝난거 같습니다.
이해가 될수도 있고 안 될수도 있겠지요..
또 어쩌면 복잡할수도 있고, 상당한 도움이 될수도 있고,
또는 전혀 도움이 되지 않을수도 있겠찌요..
제각기 자신의 환경이라는것이 있으니깐요..
이글의 본 목적에서도 밝혔듯이..
속도개선을 위한 1%라도 도움이 되길 바라는 마음에서 적는 것입니다.
자신의 현실과 맞지 않는다고 투덜될 필요도 없으며,
이방법이 나쁘다고 욕할 필요도 없을거 같습니다.
언제나 그러하듯이 필요한 사람에겐 도움이 될 것이며,
필요하지 않는사람에겐 전혀 도움되지 않는 쓰레기에 불과할테니깐요
이 글에 대한 문의와 의견, 질타는 좋으나,
비방류나 욕설류는 사양하도록 하겠습니다.
아울러 정해진 사양내에서 좀 더 속도향상을 할 수 있는 좋은 생각이 있다면
코맨트 주시면 더 좋을거 같습니다.
fti 구현으로 검색속도가 상당히 빠릅니다.(물론 기간이 전혀 없는 전체 게시물에서입니다.)
이것이 얼마나 효율성을 가지며, 좋은 기능을 발휘하는가는
위의 숙제 부분에서와 같이 그 규칙을어떻게 정하느냐가 아닐까 싶습니다.
입력시간은 느린게 사실입니다. 하지만, 눈에 거슬릴 정도로 그렇게 느리진 않습니다.
관심있으신분은 한번쯤 테스트 해보고, 괜찮다고 느껴질때 실무에 활용이 될수 있다면
더 없는 영광이겠습니다.
좋은 의견이나 아이디어 등이 있으신 분은 언제던지 댓글 달아 주시길 바랍니다.
긴글, 두서없는글 끝까지 읽어 주셔서 감사합니다.
본 내용은 필자가 직접 구현해보고 테스트해본 것을 토대로 이야기하고자 한다.
PostgreSQL 환경에서 구현한 것으로 어쩌면 이 내용이 개개인에 따라 전혀 쓸모가 없을지도 모른다. 그런 사람들에게는 이런 방법도 있구나... 소개하는 글로 봐 주길 바란다.
이 방법이 결코 그 어떤 방법보다 좋다 라고 말하고 싶지는 않다. 이것보다 훨씬 더 좋은 방법이 무수히 많고 각자 사정에 따라 차이가 있을것이기 때문이다.
단지 이 여기서는 이런 방법도 있구나... 라는 것과 속도개선의 1% 라도 도움이 되고자 하는데 그 목적이 있으며, 그 외에는 다른 의미를 두지 않는다.
이 방법의 채택은 각자 상황에 맞게 하길 바란다.
본인은 DB 에 해박한 지식을 가지고 있지도 않으며, 전문가는 더더욱 아니다.
그러므로 예제로 사용된 쿼리문은 최적화가 되어 있지 않다. 쿼리의 최적화는 개개인의 몫이다.
<테스트환경>
linux 2.6, PostgreSQL 7.4, P4 1.8G, 512RAM, 삼성 IDE 30G, 개인용 컴퓨터
테스트자료: 실전과 같은 환경을 구성하기 위해 실제 데이타를 입력하였다.
(랜덤방식의 자료생성 혹은 간단한 내용으로 구성된 페이지가 절대 아니다.)
사용된 자료는 옛날 적수네동네에 있던 자료로 LSN 이 파장하기전에 백업해 둔 자료를 그대로 이용하였다.
(이 자료는 아직도 그곳에 있다. ftp://linux.sarang.net/pub
여기에 qa 와 tip 의 백업자료가 있다. tip 는 약 9만건, qa 는 약 18만건이다)
입력의 편리를 위해 하위구조로의 데이타 입력은 하지 않았다.
하지만 계층형으로 입력했다 하더라도 결과는 같다고 생각한다.
자료갯수: 145250건
테스트결과: 전체리스트에서 어느페이지를 가던지 0.5초 이내로 출력완료
(offset 을 뒤에서 시작하므로 뒤로갈수록 오히려 더 좋은 속도(0.1 정도)
검색리스트에서 첫페이지 2초 정도 다음부터는 1초내에서 모두 출력완료
(캐쉬덕분인지.. fti row 가 지금 거의 7백만인데.. 검색속도가
느린편인가.. 관련DB 의 총 용량이 2.5기가 --;
검색대상은 전체이며, 제목, 내용 등의 구분없이 통합검색)
글쓴이검색리스트의 경우 0.1초 내로 모두 출력완료
여기서 출력속도는 페이지의 템플릿 처리속도가 모두 포함됨
어때요? 이정도면 시도해볼만한가요? 솔직히 용량에는 부담이 마니가네요
15만건이면 그냥 테이블 하나면 적어도 200메가 선에서 끝날텐데.. 파생된거,
인덱스 등등 모두 포함하니 2.5기가나 나오네요..
stopword 만 제대로 구성한다면, 훨씬 빠른 검색속도가 나올거 같네요
물론 용량도 마니 줄어들테고요..
<목적>
계층형 구조에서의 검색과 일반 리스트에 대한 속도향상이 그 주된 목적이다.
물론 지극히 당연한 이야기이지만 속도향상을 위한 가장 좋은 방법은
장비의 업그레이드이다.
<특징, 규칙>
fulltext index 를 구현한다. - PostgreSQL 의 fulltext index 기법에 MySQL 의 fulltext index 를 적용하여 직접 구현하였다. (pgsql, mysql 모두 한글을 지원하지 않기에 - 지금은 지원될려나..-)
최소한의 쿼리를 사용한다. - 여기서 최소한의 쿼리란, 어떤 처리를 위하여 내부적으로 행해지는 쿼리의 전체를 의미하는것이 아니라, 웹상에서 사용자가 날리는 쿼리수를 말하는것이다. (보통 1회, 가끔 2회)
DB 가 할 수 있는 것은 DB 에게 맡긴다. - 위의 쿼리수와도 관계가 깊다. 거의 모든 실제적인 처리는 사용자평션과 트리거가 담당하게 하여 불필요한 쿼리를 줄이고, 데이타의 꼬임 등을 방지한다.
용량과 데이타 갱신속도가 많이 걸린다. - fulltext index 의 영향이다. 검색속도의 향상을 위해 감수해야 할 부분이다.
되도록이면 index 를 타게 한다.
유니코드를 사용한다. (유니코드하에서 한글한자도 길이가 1 로 나타나기에)
기타등등 - 구현방법에서 나머지 부분을 설명하도록 하겠다.
<비업데이트형>
대용량자료의 계층형 구조에 대한 속도향상에 대한 많은 논의가 있었다. 그 방법은 크게 풀업데이트, 반업데이트, 비업데이트 형으로 나뉜다. 반업데이트형을 택할려다가
정렬을 위한 업데이트 과정이 포함되므로, 차라리 비업데이트형을 택했다.
thread 표현 방식에 대한 많은 이야기도 있었다. 그중 괜찮은 방법이 소숫점을 이용하거나 정수,소수 부분을 분리한 형태가 와닿았다.
소숫점을 이용한 실수형 컬럼은 많은 제약이 따르며, 또한 실수연산에 있어 조그마한 오차나 오류가 발견되기도 하여, 실수형은 적합하지 않다고 판단, 결국 문자형 thread 를 택했다. 문자형은 2자리로 표현하며, 아래 테이블 구조에서 ptr 컬럼이 이에 해당한다. 16?
수형태를 사용한다. (00 ~ FF 의 문자사용으로 256개의 리플과 무한깊이를 지원한다.) 만일 256 개가 작다 생각되면 3 자리를 사용하라(000 ~ fff 로 256 x 16 = 4096개의 리플수를 지원해줄 것이다.)
숫자형이 좋겠지만 숫자형은 9개만을 사용하므로, 많은 리플수를 지원할 경우
thread 길이가 많이 필요하게 된다. 제대로된 깊이까지 지원할려면 int4 로는 무리고
int8 을 사용해야 하므로 차라리 문자형을 택했다.
<DB 의 선택>
본인은 MySQL 이 정말 좋은 것이라 생각한다. 하지만, 이것은 속도에 너무 치중한 나머지 많은 부분이 구현되지 않았거나 미약하다. 많이 발전하고는 있지만, 아직은 그렇다고 생각한다.
같은 환경에서 단순쿼리의 경우 MySQL 이 당연히 속도가 빠르다. 하지만, 실무에서는 단순한 테이블구조보다는 복합적인 구조가 대부분이므로, 이럴 생각한다면 PostgreSQL 을 한번쯤 사용해보기를 추천한다. 그래도 공개용 중에선 거의 최고라 생각한다.
복합적인 테이블 구조에서의 원하는 결과를 출력하는데 오히려 복잡하면 복잡할수록
MySQL 이 속도가 더 떨어지거나 비슷할 것으로 예상된다.
사실 처리속도가 상당히 중요하지만, 0.001 과 0.1 초는 사람이 그렇게 차이를 느끼지 못한다. 이런 속도차이를 감수할수 있다면,
또한 MySQL 을 사용하다. 더 많은 기능의 필요성이 느껴졌다면, 한번 사용해 보길
바란다.
<숙제>
fulltext index 를 구현해 놓았기 때문에 fulltext index 를 최대한 활용해야 한다.
하지만 무작정 어절(공백)을 기준으로짤라 넣기만 한다면, 엄청난 row 가 생기게 될 것이다. 또한 검색속도와 용량에 직접적 관계를 가지기 때문에, 어떤 규칙을 만드느냐에 따라 엄청난 차이를 보일수 있다.
물론 단어사전 등을 이용할수도 있지만, 그것을 이용할려면 많은 스킬이 필요하게 될 것이다.
아무튼 fulltext 의 규칙은 정말 중요하니만큼 잘 세워야 한다.
본인은 테스트를 위해 간단히 그 룰을 만들었다.
stopword 라는 테이블을 만들고 그 안에서 검색에서 제외시킬 단어를 입력하였다.
또한 특수문자들은 모두 공백으로 처리하며, 한자(영문 혹은 한글)의 어절은 모두 제외시켰다.
stopword 테이블은 MySQL 의 ft_state.c 인가 에서 힌트를 얻었다.
원래는 단어사전을 만들고 그것에 등록되어 있지 않는 것은 제외할려고 했으나,
사전의 크기가 엄청난데다가 비교쿼리시간만 해도 오래 걸릴뿐더러, 이럴 제대로 이용할려면 어절별로 형태소 분석 등을 해야 한다. 그래서 포기하고 차라리 검색어로서의 가치가 없거나 사용될 확률이 아주 희박한 글자들을 등록하고 이럴 제외 대상으로 삼는게 좋다?
생각했다.
참고로 '한다, 합니다, 하고, 하여, 했다, 했습니다' 등등은 검색어의 자질이 전혀 없으며, 검색어로 사용할 확률도 극악으로 낮다. 이런 말들은 제외하는게 좋으며, 테스트 자료로 LSN 의 것을 사용했다. 사이트 특성상 리눅스를 다루는곳으로, 리눅스 혹은 linux 를
탔텝모든 게시물에서 사용하고있었다. 이런 단어들은 제외시키는게 좋다.(리눅스 게시판에서 리눅스로 검색할 사람이 과연 얼마나 있을까..? 다른 게시판이라면 삭제하면 곤란하겠지...)
암튼 이 문제는 각자 룰을 설정해보기 바란다.
또한 소개된 쿼리문이 복잡하게 느껴질지도 모른다.
좀 더 간단하게 하기 위하여 사용자평션을 이용하던지, view 를 만들던지 하여 간단하게 표현해도 좋을거 같다. 여기서는 어떤식으로 쿼리가 이루어지는지를 보여주기 위함이니 이런 것은 각자 자신의 스킬에 맞게 하면 될 것이다.
<실전>
이제 구현해보도록 하자. 본인은 이 방법을 사용함으로써 검색 및 리스트 출력에 많은 속도향상을 느꼈다. 검색에서 무지막지한 like '%str%' 는 정말 좋은 방법이 아닐상 싶다. 그래서 이 방법을 소개할 따름이다.
실무에의 적용은 각자 환경을 생각해야 할 것이다.
테이블구조 - 게시판 하나당 총 5개가 필요하며, 공통으로 1개의 테이블이 더 필요하다. 경우에 따라 모두 필요하지는 않다.
테이블 이름은 기본테이블의 문자열을 포함한 파생된이름을 갖는것이 관리상 좋을 것이다.
컬럼은 중요한 부분만 표시하고 나머지는 알아서..넣길 바란다.
또한 일일이 아래처럼 하지 않고 foriegn key 를 이용해도 좋다. 각자 스킬에 맡기겠다
bd - 기본 테이블, 한개의 테이블로 운영할때의 컬럼 구성과 동일하다. 오직, insert, update, delete 쿼리와 본문보기를 위한 테이블이다.
bd_info - 게시판 정보들을 넣는다. 게시물 전체의 갯수를 저장하는 필드도 필히 넣도록 한다.
bd_title - 리스트 출력을 위한 테이블, 필드구성은 목록보기에서 보여줄 필드만 있으면 된다. 평균적으로 본문이 길 경우 분리하는게좋다. 만일 리스트에서 코맨트수를 포시해야 한다면, 그 컬럼도 같이 넣는다, (물론 bd 테이블에도 있어야 한다.)
어떤이는 코맨트수를 보여주기 위하여 리스트 출력에서 루프로 코맨트수를 구하는 로직을 구사하기도 한다. 좀 미련한 방법이라 생각한다)
bd_comment - 코맨트용 테이블, 일반적인 코맨트테이블 구성과 동일하다.
bd_fti - fulltext index 를 위한 테이블, 검색을 위한 테이블로 어절들이 모두 이곳에 들어간다.
stopword - 말그대로이다. bd_fti 에 저장하지 말아야 할 단어를 이곳에 저장한다.
검색에서 제외시킬 단어와도 상통한다.
create table bd ( -- 기본 메인 테이블
uid serial primary key, -- 고유번호, auto_increment
pid int, -- 원문글번호
ptr text, -- thread
id text,
passwd char(32),
----- -- 그외 필요한 필드들
);
인덱스 걸기 (uid 는 Primary 로 걸필요없다)
create table bd_info (
total int, -- 총 게시물수
---- -- 기타등등 필요한 설정필드들
);
create table bd_title ( -- 기본적으로 이 테이블은 기본(메인) 테이블을 그대로 따르고, 목록출력에만 필요한 필드들로만 구성한다.
uid int, -- auto_increment 안한다.
pid int,
ptr text,
--- -- 기타등등 목록출력에 필요한 필드들
);
인덱스 걸기 (uid, (pid,ptr) pid 와 ptr 은 묶어서 건다)
create bd_comment ( --이 부분은 알아서..구현하도록
uid int, -- 원문번호
id text,
--- -- 기타등등
);
인덱스 (uid)
create table bd_fti (
uid int, -- 게시물번호
ft text, -- 자른 단어
kind text -- 제목, 내용, 커맨트 등의 구분, 숫자로 해도 된다. 편한데로)
);
(여기서 kind 는 해당파트별 검색에 쓰이기도 하지만 중요한 이유가 있다.
트리거에서 해당글을 수정하거나 삭제하는데 중요한 역활을 한다.)
인덱스 (uid, ft)
create table stopword ( word text primary key);
테이블 초기화
원문글이 -1 이 되게 하기 위해 시퀸스 번경
alter sequence bd_uid_seq increment -1 restart 2147483647;
(21억부터 시작하며 추가시 -1 씩 자동부여된다, 만일 21 억으로 모자란다면
테이블 생성시 uid 를 bigserial 로 하고 관련 테이블 전체에서 uid 와 pid 를 bigint 로 한다.
시퀸스 시작번호는 9223372036854775807 까지 가능하므로 그렇게 바꾼다)
고유번호를 거꾸로 기록하는 이유는 pid 와 ptr 을 같은 방향으로 정렬하기 위함이고,
만일 다른 방향으로 정렬하면 인덱스를 타지 않기 때문이다. 또한
최근 게시물이 위쪽이며, 최초 리플이 위쪽에 출력되는 방식을 택하기 위해서이다
insert into bd_info values(0, .....);
total 값을 0 으로넣는다.
bd 테이블과 bd_comment 의 insert, update, delete 이벤트에 대한 트리거를 건다. (그 전에 사용자 평션을 먼저 만들어놓아야 한다.)
사용자평선작성요령은 필요한 곳에서 설명한다.
stopword 에 제외한 단어들을 수동 입력한다.
이제 테이블을 다 만들었으니 자료를 입력하자.
실제 자료를 입력하기 전에 반드시 필요한 모든 사용자평션과 트리거 제외문자열
등을 미리 완성시켜 놓자. (vacuum 시간이 장난이 아니다..)
자료는 위에 밝혔듯이 LNS ftp 에 아직도 백업파일이 그대로 남아있다.
다운받은 자료를 그대로 입력하는데는 시간이 얼마 걸리지 않는다.
하지만 fti 구현을 위한 테이블에 다시 입력할때는 index 작업으로 수시간의 시간이 소요될 것이다. 인내를 가지도록...
입력, 수정 시간이 오래 걸리긴 하나 하나하나 게시판에서 직접 글을 적어보면 그렇게 오래 걸리는것도 아니다. 대량으로 그대로 넣는거니 감수하도록 하자.
이제 웹작업을 해보자.
웹에서의 쿼리는 단발성으로 하는것을 원칙으로 한다.
하지만 때에 따라 2번의 쿼리가 필요하긴 하다.
일반리스트에서의 게시물수는 bd_info 를 이용하고 검색된 게시물갯수는
xx_num_rows() 보다 select count(*) 를 이용하도록 하자.
어떠한 방법으로던지 게시물수를 구하는것은 페이징에 필요하므로 반드시 필요하다.
목록보기에서 먼저 temp table 을 만들자
(주의: 이 temp 는 세션에 종속되어야 하며 세션별 공유가 절대 일어나서는 안 된다.
예를 들어 일반 테이블처럼 어떤이가 수정을 가하면 다른이에게도 수정된 내용이 보인다. 이렇게 되면 문제가 발생되므로 일반 테이블이 아닌 temp 테이블을 만들도록 한다. 이 테이블의 row 의 수는 많지 않다. 제목테이블과의 조인을 위해 필요하다. 검색이 아닌 일?
적인 목록출력에서도 이용하자(나쁘지 않다) 이 테이블은 접속이 종료되면 자동삭제(gc) 가 되므로 항상 먼저 생성하여야 한다)
create temp table _tb (idx serial, uid int);
필요한 목록출력 쿼리내용을 temp 에 넣는다.(고유번호만 넣는다)
쿼리를 어떻게 줄 것인가..
일단 전체일 경우는 일반적인 쿼리를 준다.
내용검색의 경우 이렇게 한다.
(주의: 글쓴이의 검색은 제목테이블에 직접 하고, 내용, 코맨트, 제목 검색 혹은 통합검색일 경우는 fti 테이블에 한다.)
$query = "select a.uid from ".$table."_title as a, (select distinct on (uid) uid from ".$table."_fti where ft like '$word%') as b where a.uid = b.uid order by a.pid desc";
대충 이 정도가 될 것이다. 간단하게 fti 테이블에서 $word 를 검색하여 나온것을 중복을 없앤 것에서 uid 필드만 취한다.
여기서 pid desc 정렬을 했는데 인덱스 잘 탄다. 또한 원래는 asc 로 해야 하나
limit 의 offset 을 뒤에서 부터 구하기 위해 desc 를 사용했다. 나중에 최종 결과는 temp 에서 다시 desc 를 하니 제대로된 결과가 나온다.
이제 총개시물수(검색포함) 를구했으니, limit offset 값을 구해보자
$offset = 총게시물수 - (해당페이지번호 * 페이지당게시물수);
if($offset < 0) {
$limit += $offset;
$offset = 0;
}
여기서 $limit 의 초기값은 페이지당게시물수 이다.
이제 limit 값과 offset 값을 구했다.
이제 최종(?) 결과물을 temp 에 넣어 출력해보자
insert into _tb (uid) ($query limit $limit offset $offset);
select a.*, (length(a.ptr)/2)-1 as depth from ".$table."_title as a, _tb as b where a.uid = b.uid order by b.idx desc;
이 select 의 결과물은 해당 페이지의 출력을 해 줄 것이다.
목록출력에서 나머지 루틴은 기존 해 왔던 방식으로 하면 될 것이다.
위에서 depth 구하는부분 length(a.ptr)/2)-1 이 있는데
ptr 을 2자리씩 만들었기 때문에 2를 나누었다.
출력될 순서화된 게시물 번호(화면상의 게시물 번호) 계산은 이렇게 한다.
$no = 총게시물수 - (페이지당게시물수 * (해당페이지 - 1)) - $i;
여기서 $i 는 출력을 위하여 루프를 돌리는데 사용되는 변수이다.
for($i = 0; $i < 페이지당게시물수; $i++) {
마지막row 이면 break;
$uid = xxxx;
어쩌고 저쩌고;
}
여기에서 $i 가 이에 속한다.
목록 출력은 검색페이지 겸용 해서 모두 끝났다.
이제 본문출력을 해보자.
본문출력은 bd 테이블에 uid 로 뽑아내면 아주 쉽게 할수 있으리라 본다.
본문 아래에
이전글, 현재글, 다음글 등이 나오게 하는 쿼리만 간단히 소개한다.
여기서는 한가지만 나오므로 temp 에 넣어 다시 조인할 필요가 전혀 없다.
_tb 이용은 리스트 출력만으로 족하다.
이전글: 현재글보다 최근의 글로 바로 위의 글이며, 리플글이 아닌 원문글이다.
검색일경우 글쓴이는 생략한다. (누구나 할수 있을것으로 예상)
전체:
select uid, subject from ".$table."_title where pid < $pid and ptr = '00' order by pid desc limit 1
검색:
select a.uid, a.subject from ".$table."_title as a, ".$table."_fti as b where b.ft like '$word%' and a.uid = b.uid and a.pid < $pid and a.ptr = '00' order by a.pid desc limit 1";
현재글: 현재글을 포함한 현재글과 관련된 모든 글을 표시한다. 원문글로부터 관련된 모든 글이 이에 해당한다.
전체:
select uid, subject, (length(ptr)/2)-1 as depth from ".$table."_title where pid = $pid order by ptr asc;
검색:상황에 맞게 여러개의 row 가 나타날것이다 순서대로 depth 에 맞게 표현하기만 하면된다.
select a.uid, a.subject, (length(a.ptr)/2)-1 as depth from ".$table."_title as a, (select distinct on (uid) uid from ".$table."_fti where ft like '$word%') as b where a.pid = $pid and a.uid = b.uid order by a.ptr asc;
다음글: 현재글보다 이전의 글로 가장 최근의 이전의 글이 이에 해당한다. 원글만 나타난다. 이전글과 반대이다.
이제 출력부분은 모두 끝이 난거 같다.
(잠깐, 여기서 왜 이런 쿼리들이 사용되었는지는 이것만 봐서는 도무지 알 길이 없다. 먼저 insert 부분을 봐야 이해가 갈거 같다. thread 의 기본은 비업데이트형이며, 문자컬럼을 사용하기 때문에 그렇게 낮설지 않을 것이다.
이제 입력부분을보도록 하자
먼저 처음으로 돌아가서 각 필드의 역활과 운영방법을 먼저 이야기해야 할것 같다.
uid : 고유번호이며, 자동감소를 한다.
즉, 가장 최근의 글은 가장 숫자가 낮은 것이며, 가장 오래된 글은 가장 숫자가 높은 곳이다.
pid : 원문글의 번호이다. (두말할 필요는 없을거 같다. 이것이 없으면, 글의 뿌리를 알수 없다. 새글일 경우 자기자신의 고유번호가 pid 가 되며, 리플글일 경우, 가장 윗단계의 부모글이 Pid 가 된다. 만일 1 번 아래 2 번 그 아래에 리플을 단다면 리플달 게시물의
id 는 1 이 된다(2 가 아니다!!) 만일 이 경우라면 pid 가 2 나 3 이 사용될 곳은 어디에도 없게 된다. 즉, 반드시 최상의 부모글(시조글)이 pid 가 됨에 유의하자)
ptr : 흔히 말하는 thread 가 이에 해당한다 여기에서 알수 있는 것은
바로위 부모글과 깊이이다.
ptr 은 두자리씩 한 쌍을 이루어 구성된다.
(총 256개의 리플이 가능하다(그 하위가 아닌 동급의 리플)
이보다 더 많은 수를 원한다면 3자리로 구성하라.
사용되는 문자열은 0 ~9 까지의 자연수와 a ~ f 까지의 영문자이다.
영문자는 한쪽으로 통일하라(소문자 혹은 대문자로)
이 문자만을 사용하는 이유는 계산의 편함을 위해서 이다. 이 문자의 구성은 16 진수와 똑같으니...
새글의 경우 무조건 00 이다
답글의 경우는 아래롤 보라..
depth 는 ptr의 문자열 길이를 반으로 나눈후 1 을 빼면 된다.
빼는 이유는 원문글이 무조건 00 으로 첫 두 자리를 차지하기 때문이다.
새글쓰기:
새글쓰기는 그리 어려워 보이지 않는다.
ptr 는 무조건 "00" 이며, 어려운점이 있다면 pid 를 위해 쿼리를 한번 더 날려야 하겠다. pgsql 에서는 그럴 필요가 전혀 없다.
uid = nextval('bd_uid_seq') 이고 pid 는 uid 와 같으므로 currval('bd_uid_seq') 하면 될 것이다.
답글쓰기:
답글도 그리 어려워 보이진 않는다.
pid 는 원문글에서 pid 를 가져오면 될 것이고, 여기서는 ptr 이 문제라면 문제다.
thread 계산을 해보자.
select substring(ptr from '$ptr(..)') as thread from ".$table."_title where pid = $pid order by ptr desc limit 1;
이렇게 하면 현재 글에 대한 원문에 대해서 현재글의 깊이의 한단계 아래글들 중에서 가장 나중의 글 중 두자리의 thread 값을 읽어온다.
좀 더 쉽게 풀이하면
현재의 글의 Ptr 이 01AB 이라고 한다면
01AB 을 포함하는 게시물중에서 가장 나중에 달린것이 나오게 된다.
01AB, 01AB00, 01AB33, 01AB0001, 01AB3322 뭐 이런식으로 있다면 01AB3322 이 선택되고 그 중 33 이 결과로 뽑혀 나온다.
잠쉬 구조를 보자
01AB
01AB00
01AB0001
01AB33
01AB3322
01AB34
이런식의 트리구조를 가지며 위에서 01AB 에 리플을 다는것이니 01AB33 아래에 달려야 할 것이다 (01AB34)
만일 이 값이 없으면 그 게시물에 대한 리플이 하나도 없는 것이므로 다음에 달릴 두자리는 00 이 된다.
만일 있다면 0x 를 앞에 붙여 + 계산을 한다.
더한값이 0xff 보다 많으면 에러를 내고 종료한다.(제한리플수(256) 가 다 채워진 경우이다.)
만일 한자리 수라면 (0x4) 앞에 0 을 강제로 붙인다.
이제 구한 thread 값 앞에 원문의 ptr 을 붙인다.
위의 예에서 계산된것은 34 이고 원래것이 01AB 이니 최종 입력되어야 할 것은 01AB34 가 되겠다
이렇게 입력하는 쿼리를 한방 날리면 insert 는 끝난다. ????)
물론 다 끝난건 아니다. 이것은 기본 테이블에 넣는것이고
나머지관련 테이블은 웹에서 하는것이 아니라 db 서버에서 맡아서 처리하도록 해야한다.
여기에 사용될 것이 트리거와 사용자 정의 평션이다.
평션에서 Insert 부분은 이런식으로 구현한다.
bd_info 에 total 을 +1 해준다.
bd_title 에 필요한 필드만을 골라서 inssert 한다.
bd_fti 테이블에 insert 한다.
(이부분은 위에 좀 언급했다. 이 규칙을 잘만들어야 한다.
temp table 을 만든다 (bd_fti 와 구조가 똑같이 한다)
먼저 특수문자들을 공백처리 하고
전체 문장을 공백을 기준으로 짜른다.
한 어절로 된 것들은 모두 제외한다.
너무도 흔한 단어들은 제외한다.
예를 들어 phpschool 에서 게시판을 운영하는데
php 를 검색어로 사용한다는건 좀 이상한 검색이라 생각되므로 제외대상의 단어가 될 것이다.
각각의 어절을 소문자(혹은 대문자)로 통일한 후 temp 에 넣는다.
stopword 에서 비교하여 있는 데이타는 삭제한다.
ex: delete from _fti_tmp where ft in (select ft from _fti_tmp where ft in (select word from stopword));
(이 과정은 시간이 얼마 안 걸린다. ft row 가 많아야 몇천개이고 stopword 에 등록된 단어들도 많아야 만개이다 이 두 테이블간의 데이타 처리는 엄청나게 빠를것이다.
(2000 개 정도만 넣어도 엄청나게 줄일수 있다. 필자는 850 개 정도 (영문은 MySQL 에서 가져와 350 정도 한글 500 정도) 인데도 실제 삭제해보니 fti 테이블에서 백만개가 넘게 지워졌다.)
이제 중복데이타를 제거하고 실제 bd_fti 테이블에 입력하면 된다.
insert into bd_fti (select * from _fti_tmp intersect select * from _fti_tmp);
(여기서 중복제거를위해 intersect 를 사용하였다. 위의 stopword 를 제외할수 있는
except 등과 distinct 등등 편한대로 요리하도록 하자)
(주의: 본문내용만 할 것이 아니라 제목도 하는걸 잊지 말도록 한다.)
이제 트리거에서 insert 부분도 모두 끝났다.
수정:
웹상에서의 수정쿼리는 지극히 단순하다.
bd (기본테이블에) 수정 쿼리문 (일반적으로 사용하는 쿼리문) 그대로 사용하면 된다.
여기서도 트리거에서의 설정을 해줘야 한다.
트리거에서 어떤부분이 업데이트 되었는지 조사한다
제목이 업데이트 되었으면 제목부분을 처리하고 내용이라면 내용부분을 처리하고
둘다라면 둘다 처리한다.
조회수가 변경되었다면 제목테이블에서 hit 수를 +1 해준다.
코맨트가 하나 더 달렸다면 comment 수를 +1 한다.
(물론 웹상의 쿼리는 bd 에 한다.)
한가지만 예로 설명한다.
제목이 업 되었을 경우
원문글의 번호로 fti 테이블에서 검색하여 삭제한다.
delete from bd_fti where uid = $uid and kind = "title";
(주의 위에서 $uid 처럼 php 변수를 사용했는데 실제 SQL 문에서 사용하는 변수를 사용해야 한다. 알기쉽게하기 위해 일부러 그랬다)
insert 때처럼 제목부분을 fti 처리한다.
또한 bd_title 에서도 subject 필드를 update 명령어를 이용하여 업한다.
대충 수정 부분도 끝난거 같다.
이제 삭제 부분이다.
여기서의 삭제는
삭제되는 글 이후에 달린 모든 코맨트, 모든 하위리플들을 삭제하는 방식이다.
그렇지 않고 하위글부터 삭제를 유도할 경우 이런 방법은 필요는 없고 해당 게시물만 삭제하면 된다.
웹상에서 삭제 쿼리를 내린다.
delete from bd where uid = $uid;
이것 하나로 족하다 --;
(참고로 파일이 있을 경우 파일도 함께 처리해야 하는데
먼저 삭제 쿼리를 내리기 전에 삭제될 모든 게시물(하위리플들 모두 포함)에 대하여
파일명을 뽑아 배열에 미리 담아 두어야 한다.
그리고 위의 삭제쿼리 한번 내리고 이상이 없을 경우
배열에 쌓아둔 파일명을 차례차례 웹상에서 삭제한다.
이제 이에 대한 트리거에서의 처리를 보자
먼저 트리거에서 삭제 쿼리를 날린다.
delete from bd where pid = $pid and ptr like substring(ptr from '$ptr.*');
(위에서 php 스타일의 변수 역시 이해를 돕기 위해 썼다. SQL 에 맞게 써야한다)
delete from bd_fti where uid = $uid;
delete from bd_comment where uid = $uid;
delete from bd_title where uid = $uid;
update bd_info set total = total -1;
이렇게 하면 된다.
여기서 주의해야 할 점이 있다.
위의 첫 쿼리는 bd 테이블에서 관련글을 모두 삭제하는데 반해
아래의 4개의 쿼리는 각각 하나씩만 삭제하고 있다.
트리거의 delete 이벤트에 대한 처리가 있기 때문에 한꺼번에 삭제를 해버리면
문제가 발생한다. 그러므로 하나하나씩만을 처리해야한다.
위에서 전체 삭제에 의해 트리거의 delete 구문이 반복적으로 호출되기 때문이다.
또한 아래의 3개의 delete 문에 사용된 uid 는 첫줄의 $uid 와는 완전 틀리다.
첫줄의 쿼리에서 삭제되는 대상의 uid 값인 것이다.
반드시 조심해야 한다.
이렇게 하면 대충 delete 부분도 끝이 났다.
comment 의 수정 삭제 추가 등에 대한 내용은 스스로 채워보기 바란다.
거의 같은 구조로 되어 있으니..
단 comment 일 경우 테이블 자체가 틀리고
웹상에서의 쿼리도 bd 가 아닌 bd_comment 로 날리기 때문에
이에 대한 처리를 위해
bd_comment 테이블에 따로 처리하는 트리거를 걸어야 한다.
마지막으로 결과물의 갯수를 어떻게 구해야 효율적인지를 생각해보자.
여기서 갯수를 구하는것에는 갯수 뿐만 아니라 여러가지 계산 등등
집계함수의 모든 기능을 다 포괄적으로 말한다.
단지 예로서 갯수를 구하는것을 든 것 뿐이다.
가장 손쉬운 방법은 xx_num_rows() 함수를 이용하거나, select count(*) query
스타일일 것이다. 이런식의 사용은 데이타가 많아짐에 따라 현저한 속도저하를
가져온다.
그렇다면 어떻게 샐 것인가..
별도의 테이블을 구성하여 메인 테이블에서 트리거를 걸어 누적상태를 별도의
테이블에 저장하고 결과물은 그곳에서 가져오는 방식이다.
특히 연말정산, 월별정산 또는 통계 등등을 낼 경우
경우에 따라서는 루프를 돌려야 할때도 있고, 그 많은 건수를 (물론 구간이 있지만)
일일이 집계한다는건 엄청난 시간낭비이다.
입출력이 생길때마다 별도의 테이블에 갱신만 시키고 해당 필드를 액세스 하면
단 한줄의 row 에서 읽어오게 되므로 엄청난 속도향상을 가져올수 있다.
select count(*) from bd;
select total from bd_info;
어느것이 속도가 빠를지는 각자 판단하길 바란다.
그렇다면... 검색의 결과에 대한 갯수는 어떻게 샐 것인가..
참으로 어려운 과제인거 같다.
결과를 출력하기 위해선 어차피 한번은 검색쿼리를 줘야하고
페이징을 위해선 반드시 갯수 카운트를 해야한다.
검색조건이 어떻게 될지도 모르는데 위처럼 미리 정보를 갱신해둘수도 없다.
필자의 경우는 여러가지 고민끝에
결과물의 전체중 고유번호필드만 뽑아 임시테이블에 집어넣고
임시테이블에서 select count(*) 를 사용했다.
숫자컬럼 하나 뿐이니 그래도 그중 빠른 카운트가 아닐까 싶다.
더군다나
나온 결과물(고유번호만있찌만) 과 본 테이블과의 조인에서 원하는 결과를 바로
출력할수도 있으니..
해당 검색 쿼리를 두번 주지 않아도 된다고 생각한다..
여러분들은 이런 문제를 어떤식으로 해결하는지 궁금하기만 하다.
총갯수가 많으면 많을수록 검색속도가 느려지는데 이런 두번의 쿼리가 속도저하를
더더욱 가중시킬수 있기 때문에..
이에대한 좀 더 참신한 아이디어로의 논의가 필요할 것 같다.
대충 끝난거 같습니다.
이해가 될수도 있고 안 될수도 있겠지요..
또 어쩌면 복잡할수도 있고, 상당한 도움이 될수도 있고,
또는 전혀 도움이 되지 않을수도 있겠찌요..
제각기 자신의 환경이라는것이 있으니깐요..
이글의 본 목적에서도 밝혔듯이..
속도개선을 위한 1%라도 도움이 되길 바라는 마음에서 적는 것입니다.
자신의 현실과 맞지 않는다고 투덜될 필요도 없으며,
이방법이 나쁘다고 욕할 필요도 없을거 같습니다.
언제나 그러하듯이 필요한 사람에겐 도움이 될 것이며,
필요하지 않는사람에겐 전혀 도움되지 않는 쓰레기에 불과할테니깐요
이 글에 대한 문의와 의견, 질타는 좋으나,
비방류나 욕설류는 사양하도록 하겠습니다.
아울러 정해진 사양내에서 좀 더 속도향상을 할 수 있는 좋은 생각이 있다면
코맨트 주시면 더 좋을거 같습니다.
fti 구현으로 검색속도가 상당히 빠릅니다.(물론 기간이 전혀 없는 전체 게시물에서입니다.)
이것이 얼마나 효율성을 가지며, 좋은 기능을 발휘하는가는
위의 숙제 부분에서와 같이 그 규칙을어떻게 정하느냐가 아닐까 싶습니다.
입력시간은 느린게 사실입니다. 하지만, 눈에 거슬릴 정도로 그렇게 느리진 않습니다.
관심있으신분은 한번쯤 테스트 해보고, 괜찮다고 느껴질때 실무에 활용이 될수 있다면
더 없는 영광이겠습니다.
좋은 의견이나 아이디어 등이 있으신 분은 언제던지 댓글 달아 주시길 바랍니다.
긴글, 두서없는글 끝까지 읽어 주셔서 감사합니다.
http://jiny.kr/trackback/112
0