PHP & MySQL

페이징 쿼리, 페이징 기법

관리자 | 2014.08.13 01:48 | 조회 22146

일부 구간의 레코드를 조회하고자 할때 페이징 쿼리를 사용하지 않고 전체의 데이터를 ResultSet 객체의 NEXT 방식의 next()로 해당 시작위치를 찾을때까지 반복하거나, absolute(row)나 AbsolutePage를 사용하는 경우, DB 서버에서 접속한 클라이언트까지 네트워크를 통해서 전체의 데이터가 전송되어야 하므로 클라이언트와 DB 서버간의 네트워크 부하가 걸린다. 또한 레코드 조회 결과를 클라이언트에서 사용하기 위해서는 우선 메모리를 그 데이타의 양만큼 잡아주어야 하므로 RESOURCE를 낭비하게 된다. 그리고 next()를 통해서 해당 위치(중간 시작위치) 까지 이동하기 위한 불필요한 CPU 사용의 비효율성이 있다.


/* 위에서부터 5개만 가져오기-ORDER BY는 생략가능 {TOP 레코드개수} */
SELECT TOP 5 * FROM [myboard] ORDER BY [idx];

 

/* 같은 순위의 레코드를 모두 가져오기-ORDER BY는 생략불가 {TOP 레코드개수 WITH TIES} */
SELECT TOP 5 WITH TIES * FROM [myboard] ORDER BY [idx];

 

/* 지정된 퍼센트 만큼 가져오기-ORDER BY는 생략불가, percent는 소수로 지정가능하며 조회된 전체레코드개수에 곱한 개수만큼 반환 {TOP 레코드비율 percent} */
SELECT TOP 1.33 percent * FROM [myboard];

 

/* 조건절에 between 사용-관계연산자를 사용했을 때보다 속도는 느리다. */
SELECT * FROM [myboard] WHERE [idx] between 40 and 50;
SELECT * FROM [myboard] WHERE [idx] >= 40 AND [idx] <= 50;

 

/* PRIMARY KEY를 이용하여 특정 범위의 레코드를 가져오기-게시판의 게시물의 페이징 기법, 페이징 쿼리에 해당. 이 쿼리문은 51번부터 60번까지의 레코드를 조회한다. 이 방법은 반드시 PRIMARY KEY 필드를 사용해야만 한다. */
SELECT TOP 10 * FROM [myboard] WHERE [idx] NOT IN (SELECT TOP 50 [idx] FROM [myboard])

 

/* ROW_NUMBER() 함수를 이용하여 특정 범위의 레코드를 가져오기-게시판의 게시물의 페이징 기법, 페이징 쿼리에 해당. 이 쿼리문은 51번부터 60번까지의 레코드를 조회한다. 이 방법은 반드시 정렬할 필드가 지정되어야 한다. */
SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY idx ASC) rnum, * FROM [myboard]) T1
WHERE rnum >= 10 AND rnum <= 20;
 

또는

SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY idx ASC) rnum, * FROM [myboard]) T1
WHERE rnum between 10 AND 20; 

 

임의의 쿼리문에 대하여 페이징처리를 하는 쿼리문 예시 













 

SELECT * FROM

      (SELECT ROW_NUMBER() OVER (ORDER BY [정렬칼럼명] ASC) rnum, * FROM (사용자가 입력한 쿼리문) T0) T1
      WHERE rnum > (페이지번호-1 x 페이지당출력개수) AND rnum <= (페이지번호 x 페이지당출력개수);
  

 

idx, title, writer, wdate, contents1, contents2 로 칼럼이 구성된 myboard 테이블에 대하여 다음의 쿼리문을 적용할 수 있다.
사용자가 입력한 쿼리문에 ORDER BY 절이 있다면 SELECT 다음에 TOP 100 percent 를 삽입하여 사용해야 한다. 그렇지 않으면 

TOP, OFFSET 또는 FOR XML을 함께 지정하지 않으면 뷰, 인라인 함수, 파생 테이블, 하위 쿼리 및 공통 테이블 식에서 ORDER BY 절을 사용할 수 없습니다.

메시지가 출력된다.

SELECT * FROM
 (  SELECT *, ROW_NUMBER() OVER (ORDER BY [idx] ASC) rnum 
  FROM
   (SELECT TOP 100 percent * FROM [myboard] ORDER BY [idx] ASC) T1
 ) T2
 WHERE rnum > 12500 and rnum <= 13500;
 

 

참고로 MS-SQL의 기본 포트번호는 1433이다. 외부접속 시 방화벽에 해당 실행파일을 예외등록해주고, 1433 포트번호를 열어주도록 해야 한다.

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

 

관련내용1 출처 - http://sung.world.co.kr/index.php?document_srl=3382&mid=mssql

1. SELECT TOP [불러올 총 게시물수] [출력 필드명] FROM [테이블 명]

  1번 리스트는 이 쿼리 구문을 써서 했고요. 현제 개시물까지 이동을 Rs.Move(이동할수) 로 처리 했습니다.

 

2. SELECT TOP [불러올 총 게시물수] [출력 필드명] FROM [테이블 명]    - 1번과 동일

  1번과 쿼리구문은 동이하고요. 레코드셋의 AbsolutePage를 이용해서 페이징을 했습니다.

 

3. SELECT TOP [불러올 총 게시물수] [출력 필드명] FROM [테이블 명]

   WHERE [글번호필드] NOT IN (SELECT TOP [제거할 게시물수] [글번호필드] FROM [테이블명])
   예전에 태요 사이트에서 보았던 쿼리 구문입니다.. NOT IN 때문에.. 문제시 되었던 쿼리구문이죠.

 

4. SELECT TOP [불러올 총 게시물수] [출력 필드명] FROM [테이블 명]
   WHERE [글번호] IN (SELECT TOP [페이지출력 갯수] [글번호] FROM
   (SELECT TOP [불러올 총 게시물수] [글번호] FROM [테이블 명]) AS A ORDER BY [글번호])
   ORDER BY [글번호] DESC

   이 쿼리 구문은 3번의 쿼리 구문의 문제점을 보완한 구문입니다. NOT IN 대신에 IN을 사용 했습니다.

 

5. SELECT TOP [페이지 출력갯수] [출력 필드명] FROM [테이블 명]
   WHERE [글번호] <= (SELECT MIN([글번호])
   FROM (SELECT TOP [제거할 게시물수] + 1 [글번호] FROM [테이블명]) AS A)

   5번째 쿼리 구문은 IN, NOT IN이 아닌 출력할 마지막 글번호 바로 앞이 글번호를 찾아서 처리를 해주는
   쿼리 구문입니다.

 

6. SELECT TOP [페이지 출력갯수] [출력 필드명] FROM [테이블명]
   WHERE [글번호] <= (SELECT MIN([글번호])
   FROM (SELECT TOP [제거할 게시물수] + 1 [글번호]
   FROM [테이블명] WHERE [인덱스] = [시작인덱스번호] AND [인덱스] = [끝인덱스번호]) AS A
   WHERE [인덱스] = [시작인덱스번호] AND [인덱스] = [끝인덱스번호])
   AND [인덱스] = [시작인덱스번호] AND [인덱스] = [끝인덱스번호]

   6번재 쿼리는.. 글에 인덱스(가칭)라는 필드를 하나 더 추가 해서.. 글 기본 2000개마다 (가변적입니다)
   인덱스를 증가 시켰습니다. 즉 2000개를 하나의 묶음으로 만든것입니다.
   그 인덱스를 기준으로 처리를 해주었습니다.(기본 개념은 영어 사전 입니다 ㅡㅡ;)


위와 같이 간단한 기본 쿼리 구문을 설명? 했습니다.
게시물은 100만개를 넣고 테스트를 했습니다.
테스트 게시판을 보시려면.. 맨위에 각 게시판을 링크를 걸었습니다.
서버가 구려서.. 제대로 될지 모르지만..
제 노트북
(CPU : p4-1.8, RAM : 768Mb, 컴팩 프리자리오 2820AP, 환경 : 윈도우2003 MSSQL2000)
에서 테스트 해본 봐로는 처음페이지(1), 마지막 페이지(50000) 처리 시간이 아래와 같습니다.(단위 ms)

1 : 320    12289.06
2 : 273    11476.56
3 : 289    4406.25
4 : 289    2695.31
5 : 289    1218.75
6 : 7.81    23.44

 

 

 

관련내용2 출처 - http://sung.world.co.kr/index.php?document_srl=3384&mid=mssql

제목 : mysql의 LIMIT 와 같은 결과 얻기....  
-----------------------------------------------------------------------------
** 순차 데이타 정렬을 통해서 임의의 중간 일부분의 데이타를 가져오는 방법
MS-SQL 에서는 MYSQL 처럼 LIMIT 가 없으므로 
변칙 수법을 이용해야 한다. 

불행중 다행으로 
MS-SQL 에서는 TOP 을 이용해서 상위 몇 행을 얻을 수 있다. 

SQL 에서 부질의를 사용할 수 있는데 

임의의 테이블이 다음과 같을 때,

TABEL NAME : USER_INFO
FIELD    TYPE
------------------------
USER_ID    VARCHAR(10)
AGE  INT
ADDRESS    VARCHAR(100)



[1] SELECT * FROM USER_INFO WHERE USER_ID IN (SELECT USER_ID FROM 
USER_INFO WHERE AGE > 10 )
위의 [1] 은 다음과 같다.
SELECT * FROM USER_INFO WHERE AGE > 10

만약 , 다음과 같은 QUERY를 사용한다면

즉 , NOT IN 을 사용한다.

[2] SELECT * FROM USER_INFO WHERE USER_ID NOT IN (SELECT USER_ID FROM
USER_INFO WHERE AGE > 10 )
 
[1]의 결과는 10 살 이상인 사용자 정보를 얻는 것이고
[2]의 결과는 10 살 이상인 사용자를 제외한 사람들을 얻는 것이다.

[1] , [2] 를 통해서 SUB QUERY 의 용도는 대충 알 수 있다.
그러면 , 어떻게 해서

어떤 조건에 맞는 ( 조건이 있는) 데이타 중에서 원하는 중간의 데이타를 얻을 수 있을까???

가상의 순서   데이타
1              .......
2             ......
3             ......
4             ......
5             .....
6        ......
.

MS-SQL 에서 TOP 이라는 놈을 사용하면
출력된 결과셋(RESULTSET) 에서

원하는 만큼의 행을 얻을 수 있다.

즉 ,[3] SELECT TOP 10 * FROM USER_INFO WHERE AGE >  10

위의 QUERY 는 10살 이상인 사용자들을 대상으로
상위 10 명에 대한 정보만을 얻어서 CLIENT ( APPLICATION) 에 돌려 주게 된다.

만약 ,[3]의 QUERY에 ORDER BY USER_ID 를 사용한다면 , 언제나 같은 순서로 데이타를 얻
을 것이다.

[4]  SELECT TOP 10 * FROM USER_INFO WHERE AGE >  10 ORDER BY USER_ID

이제 한 발짝  더 나아가서 ,
[1]의 query에 top 을 추가한 결과는 어떻게 될까

[1] SELECT * FROM USER_INFO WHERE USER_ID IN (SELECT USER_ID FROM
USER_INFO WHERE AGE > 10 ORDER BY USER_ID)

==>[5] SELECT * FROM USER_INFO WHERE USER_ID IN (SELECT top 10 USER_ID FROM
USER_INFO WHERE AGE > 10 ORDER BY USER_ID)

[6] SELECT TOP 3 * FROM USER_INFO WHERE USER_ID IN (SELECT top 10 USER_ID
FROM USER_INFO WHERE AGE > 10 ORDER BY USER_ID)

[5]는 10살 이상의 사용자들을 정렬해서 상위 10명의 정보를 가져온다.
[6]은 [5] 에서 얻은 사용자들 중에서 상위 3명의 정보를 얻는다.

 
만약 , 다음과 같이 한다면 ,

[7] SELECT TOP 3 * FROM USER_INFO
WHERE USER_ID NOT IN (SELECT top 10 USER_ID FROM USER_INFO WHERE AGE > 10
ORDER BY USER_ID)

아주 중요한 부분인데 ,
[7]의 결과는 10살 이상인 사용자들 중에서 상위 10 번째 다음인 11, 12 ,13 번째 행의 데이타를
얻을 것으로 생각하기 쉽다.
그러나 위의 결과는 그렇지 않다.
위의 결과는 SUB QUERY에서 얻은 사용자를 제외한 모든 사용자가 된다.
따라서 상위 3명에 대해 얻은결과에는 10살 이하의 사용자도 포함된다.

결론 ,

말이 길어졌는데.

우리는 TABLE에서 어떤 조건을 만족하는 결과를 만들고
이 결과에서 상위 몇 행을 제외한 그 다음 행에서 부터 원하는 행 만큼 데이타를 가져오려한다.

예로 ,
(1)10살 이상의 사용자 라는 조건을 만족하는 결과
이것은 다음과 같다.
=> SELECT * FROM USER_INFO WHERE AGE > 10
(2) 이결과  집합에서 상위 10명
=> SELECT TOP 10 * FROM USER_INFO WHERE AGE > 10

(3) 이 10명을 제외한 사용자들
=>  SELECT  * FROM USER_INFO

WHERE USER_ID NOT IN (SELECT top 10 USER_ID FROM USER_INFO WHERE AGE > 10 )

(4) (3)에서 얻은 결과집합에는 10살 이하도 있다. 따라서 이들은 조건에 맞지 않으므로

=> SELECT  * FROM USER_INFO
   WHERE USER_ID NOT IN
                (SELECT top 10 USER_ID FROM USER_INFO WHERE AGE > 10 )
   AND  AGE > 10

*** (4) 의 QUERY 를 자세히 보면 WHERE 절의 조건이 서로 같은 것을 알 수 있다.
        또한 SUB QUERY 에서 얻은 결과를 MAIN QUERY에서 조건의 일부로 사용하고 있음을
알 수 있다.

    위의 QUERY를 분석하면 다음과 같다.
  10살 이상인 사용자들 중에서 상위 10명을 제외한 모든 사용자들의 정보를 얻어라!!!


결국, 10살 이상(조건)의 사용자들중에서   11번째에서 15번째의 데이타를 얻기 위해서는

(4) 의 QUERY 에 TOP 을 한번 더 사용해서

(5)=> SELECT TOP 5 * FROM USER_INFO
   WHERE USER_ID NOT IN (SELECT top 10 USER_ID FROM USER_INFO WHERE AGE >
10 )
   AND  AGE > 10

(5)의 결과는 결국 우리가 얻으려는 11번째 에서 15번째의 데이타이다.
물론 조건도 만족한다. ( 10살 이상)

UPDATE QUERY ( INSERT , DELETE , UPDATE) 등의 연산으로부터  정확한 데이타를 얻으
려면

다음과 같이 하면 된다.

   SELECT TOP 5 * FROM USER_INFO
   WHERE USER_ID NOT IN (SELECT top 10 USER_ID FROM USER_INFO WHERE AGE >
10 ORDER BY USER_ID)
   AND  AGE > 10 ORDER BY USER_ID

[마무리]

조건을 만족하는 데이타 중에서
중간의 데이타를 얻기위한 방법 *** 중요한 것은 조건이 두번 들어 간다는 것이다.

SELECT TOP [얻으려는 행의수] * FROM [TABLE]
WHERE [PRIMARY KEY] NOT IN ( SELECT TOP [중간의 시작위치 - 1] [PRIMARY KEY]
FROM [TABLE] WHERE [조건] ORDER BY [PRIMARY KEY])
AND [조건] ORDER BY [PRIMARY KEY]


지금까지 알아본 QUERY 는 매우 중요하다고 할 수 있다.
이유는 DB 서버에서의 부하를 줄이는 것도 중요하지만 ,
만약 , 위와 같이 중간의 데이타를 얻는 방식이 아닌 , 전체의 데이타를 RESULTSET으로 얻어

NEXT 로 해당 위치를 찾는다면 ,
그 폐단은 다음과 같다.
(1)DB 서버 에서 CLIENT(APPLICATION) 까지  NETWORK 을 통해서
   전체의 데이타가 전송되어야 하므로 CLIENT 와 DB SERVER간의 NETWORK 부하가 걸린
다.
(2)얻은 결과를 CLIENT 에서 사용하기 위해서는 우선 메모리를 그 데이타의 양만큼 잡아 주어
야 하므로
RESOURCE 를 낭비하게 된다.

(3) NEXT 를 통해서 해당 위치 (중간 시작위치) 까지 가기 위한 CPU 사용의 비효율성 ,

등등, 이러한 문제 점들은 전체 SYSTEM의 성능을 저하시키고 RESOURCE 를 낭비하게 되므


심각하게는 SYSTEM DOWN 이라는 최악의 상황으로 이어질 수 있다.

 

 

관련내용3 출처 - http://blog.naver.com/PostView.nhn?blogId=kjs077&logNo=10135948557

 

ORCLE이 정말 좋은 RDB라는 것을 알게 하는 것이 바로 페이징 기법일거라 생각되네요.
MySQL도 내부적으로 페이징이 가능한 쿼리를 지원해주지만 MSSQL은 최근 2005버전까지도... 좋은 페이징 기법을 소개하지 못하고 있는 것 같네요. 성능 좋은 페이징 기법이 공개된 것이 있기도 하지만... 초심자에게 쉽지 않은 쿼리들인 것 같습니다.
이에 조금 쉽게... 어떻게 하면 성능이 향상되는지를 설명해 보려고 합니다.

아래의 내용은 온라인상에서 바로 작성하는 내용이고 맞춤법등이 틀릴 수 있기 때문에 copy해서 사용하지 마시기 바랍니다.

개념을 잡는 정도로 활용하시면 좋을 것 같습니다.

MS SQL의 페이징 기법의 키는 TOP 키워드입니다.

#1. TOP과  클러스터드 인덱스

SELECT TOP 10 ID, subject, contents FROM TBL

이 쿼리는 누구나 알고 있는 쿼리입니다. 여기서 중요한 것이 TOP 10 입니다.  상위 10개만 갖고 오겠다는 뜻입니다.MSSQL은 내용저장을 클러스터드 인덱스 순으로 저장을 하게 됩니다. 만약 ID를 PK로 지정하셨거나 따로 클러스터드 인덱스로써 ID를 지정해 놓으셨다면 ID순으로 자동으로 정렬이 되어 상위 10개만 가져오게 됩니다.

기본키는 반드시 설정할 필요는 없습니다. 다만 레코드를 구분하는 아이디값을 대게의 경우 기본키로 놓게 되는데요.

사실 기본키가 성능에 있어서의 의미는 없다고 봅니다.(확인된 바 없음)

기본키는 자동으로 인덱스 컬럼이 된다는게 의미가 있겠죠. 그것도 테이블당 딱 하나 사용할 수 있는 클러스터드 인덱스로 자동 설정됩니다. 헌데 문제는 ASC로 설정된다는 것입니다.

웹페이지에서 최근 글의 경우는 대부분 DESC로 정렬합니다. 즉 최근 글을 먼저 표시해주지요. PK로 만들더라도 꼭 ID를 DESC로 인덱스하도록 만들어야 합니다.

바로 이 유일 인덱스 컬럼이 성능을 좌우하는 핵입니다.

초심자의 경우 인덱스의 중요성을 넘기는 경우가 많은데요. PK가 중요한 것이 아니라 정렬을 원하는 컬럼을 클러스터드 인덱스로 만들어 놓는 것이 성능에 가장 중요합니다.(레코드 수가 늘 수록 엄청난 성능 향상이 있습니다.)

팁) TOP 10 PERCENT라는 키워드도 가능합니다. 말 그대로 전체의 10%만 가져온다는 것입니다. 쓸모가 많은 팁이라 생각되네요.


 

#2. 가장 많이 쓰이는 페이징 쿼리

SELECT TOP 10 ID, subject, contents FROM TBL where ID not in (SELECT TOP 현재페이진 이전까지의 모든 게시물 수 ID FROM TBL order by ID DESC) order by DESC

참 좋은 쿼리입니다. MS SQL에서 나올 수 있는 가장 간결하고 좋은 쿼리가 아닌가 싶습니다.

저역시 작은 규모의 게시판 종류는 무조건 이 쿼리를 이용합니다. 유지보수가 편하기 때문입니다. 누구나 쉽게 알아 볼 수 있기 때문에 제가 도저히 못 봐줄 유지보수 프로젝트라면 다른 사람이 대신할 수 있는 쿼리이기 때문에... 이 쿼리를 즐겨사용합니다.

다만 이 쿼리에는 조건이 하나 붙습니다.

사용자들이 100페이징 이하(게시물 수로 2000개 이하정도)의 글을 되도록 조회한다.

이 쿼리는 2만건 이하의 테이블에 적당하다고 생각됩니다.

1페이지라고 하면 성능이 최고가 되며
2페이지라면 not in 안의 쿼리문에 의해 한페이지가 10개의 글이라고 가정하면 10개를 일단 불러 들이게 됩니다. 제거를 위해서죠.
3페이지라면 20개를 불러들여서 제거를 해야 하겠고
4페이지라면 30개를...

그럼 100페이지라면 990개의 글을 읽어 들여야 겠군요. 990개를 먼저 불러 들이는 것이 문제입니다.

가장 심플한 쿼리이지만 DB에 어느정도의 부하를 주는 쿼리라는 것을 알 수 있습니다.

개인적으로 레코드셋종류의 객체에 내용을 불러오는 쿼리는 0.1초 이전에 끝나야 한다고 생각됩니다. 물론 검색이 들어가면 이야기가 달라지지만 기본적인 형태(아무런 검색이 없는 경우)에서는 0.1초 이하에서 OPEN을 끝내는 것이 좋다고 생각되네요.

 

#3. ORDER BY문

아시다시피 ORDER BY문은 정렬의 조건입니다. 위 쿼리의 경우 ORDER BY ID DESC이니깐 ID에 대해 내림차순으로 정렬하겠네요. 사실 ORDER BY 문은 안써주는 것이 성능에 가장 좋습니다.

만약 ID를 클러스터드 인덱스로 지정해 놨다면 안써도 될 것 같습니다. 클러스터드 인덱스를 내림차순으로 지정해 놨다면 ORDER BY ID DESC는 성능에 전혀 영향을 미치지 않습니다. 가끔 MSSQL에서 서브쿼리를 썼을 경우 원하는 값을 리턴하지 않습니다. 이유는 모르겠습니다. 서브쿼리문을 가져오는 방법에서 더 빨리 가져오려고 하는 그 부분에서 나오는 문제일거라 생각되네요.

클러스터드 인덱스로 지정했을 경우 ORDER BY ID DESC는 되도록이면 넣으시는게 좋습니다. 정확성을 위해서!;


 

#4. 성능저하 요소 SELECT COUNT(*) FROM TBL

NOT IN 쿼리보다 심플한 페이징 쿼리는 MSSQL에서 없는 것 같습니다. 개발자 입장에서 프로그래밍하기도 정말 쉬운 쿼리입니다. 현재 페이지 번호만 넘겨주면 모든 것이 가능합니다. 가장 큰 장점이 바로 개발하기 편하다는 것이 겠고, 사용자의 웹접근 액션과도 상당히 잘 맞아 떨어집니다. 사람들은 10페이지 이상은 잘 보지 않으려는 경향이 있기 때문입니다.

NOT IN 쿼리는 생각보다 좋은 쿼리라는 것을 일단 말씀 드립니다. 단 조건은 INDEX를 잘 설정해줬다는 조건이 붙습니다.


 

게시판 등을 개발할 때 전체 게시물 수를 구해오는데 가장 많이 쓰는 쿼리문이 SELECT COUNT(*) FROM TBL입니다. 페이지 바로 가기 버튼을 위해서도 필요하고 게시물 번호를 붙히는 데도 쓰이기 때문에 쓰는 경우가 많습니다.

헌데 이 쿼리문 속도가 상당히 늦습니다. 100만건을 조회할 경우 1초가 넘어가 버리는 무식한 쿼리입니다. 야야~ 그럼 IDEX행을 가져와봐... 라고 하실 것 같습니다. SELECT COUNT(ID) FROM TBL ... 허나 애석하게 속도가 똑같습니다.

그리고 COUNT함수는 가능하면 (*)를 사용하시기 바랍니다. 이것이 정확한 방법입니다. COUNT(*)에서 속도향상을 위한 방법은 솔직히 말씀드려서 없습니다.

가장 좋은 방법은 테이블 하나를 만들어서 데이타 입력/삭제시마다 업데이트 하면서 게시물 수를 저장해 놓는 방법입니다. MSSQL 내에서는 트리거라는 기능을 제공합니다. INPUT/DELETE 시 UPDATE TBL_SETTING set TBL_COUNT = xxx 뭐 이런 식으로 짜 놓으면 되겠죠.

전체 게시물 수를 가져올 때는 되도록이면 트리거를 쓰거나 프로그램을 통해 정보를 저장하는 테이블에 업데이트하고 이 자료를 페이징시 가져오는 것이 성능을 위한 좋은 방법입니다.

1만건 이하라면 COUNT(*)를 쓰던 NOT IN을 쓰던 별 지장이 없다는 게 제 생각입니다. 고로, 자꾸 써먹어도 좋은 쿼리 들임을 일단 알려드립니다.


 

#5 성능향상에 가장 좋은 방법

1000만건을 테스트 해보지는 않았지만, 1페이지와 100만 페이지가 같은(해보진 않았죠^^)-혹은 비슷한- 성능을 보이는 쿼리는 다름 아닌 NOT IN보다 훨씬 더 간결한 쿼리입니다.

SELECT TOP 11 ID, subject, contents FROM TBL where ID <= 현재페이지 최상위 ID (order by ID DESC)

이 쿼리에서 가장 중요한 것은 ID가 클러스터드 인덱스로 ID를 설정하고 내림차순으로 지정되어 있어야 한다는 것입니다.

아무리 많은 글들도 0.1초안에 해결될만한 가장 성능이 좋은 쿼리문입니다. 동접자수 엄청나고 글 수가 많다면 이방법 이외의 방법은 사용하지 마시기 바랍니다.

허나 장점이 있으면 단점도 있습니다. 이 쿼리를 사용할 경우 ID값에 바로 접근하긴하기 때문에

이전 1 2 3 4 5 6 7 8 9 10 다음 <- 이와 같은 구성이 불가능하다는 것입니다.

그래서 TOP 10이 아닌 11을 사용한 것입니다. 1부터 10까지 구성은 힘들지만 다음페이지 버튼은 가능하기 때문입니다. 갯수가 11개면 다음 페이지가 있다는 이야기가 되며, 다음페이지의 최상위 ID값도 가져올 수 있게 됩니다.

어쨌거나 위 쿼리는 성능에 있어서는 더이상 좋을 수 없는 쿼리입니다. 분명 글이 많고 사용자 액션이 단순한 사이트라면 반드시 고려해보셔야 할 쿼리입니다. 이전 페이지 구현은 프로그램적으로 머리를 좀 굴려야 할 부분이긴 합니다.

어짜피 다음 페이지 버튼을 눌러야만 가능하니깐 이전 페이지의 ID값은 무조건 가지고 갈 수 있습니다. POST방식으로 이전 페이지 정보를 계속 넘기는 방법도 괜찮은 방법일 것입니다.


 

SELECT TOP 10 ID FROM TBL where ID > 현재페이지 최상위 ID order by ID ASC 를 어쩔 수 없이 쓰는 것도 한 방법이겠구요. 그래도 다른 쿼리들보다는 빠른 방식이니깐요. <- 이건 저도 테스트 해보진 않았습니다.


 

#6. 사용자 편의성도 좀 생각해 보자

대부분 웹사이트에서는 NOT IN쿼리가 좋은 방법입니다. 사용자 편의성에 있어서 좋은 선택이니깐요.

이전 1 2 3 4 5 6 7 8 9 10 다음 <- 이게 얼마나 편한 방법입니까^^; 그리고 사람들은 10페이지 이상 조회를 거의 하지 않기도 합니다.

성능을 생각한다면 #5번의 방법이 정말 좋은 방법이지요. 다만 사용자 편의를 위한 인터페이스 구현은 사실상 불가능 합니다. 이전 페이지 구현도 쉽지 않죠.

목표가 생겼습니다.

이전 1 2 3 4 5 6 7 8 9 10 다음

이 기능을 한번 구현해 보죠. 약간 성능 저하가 있더라도... 전체 NOT IN보다는 훨씬 더 빠르게 한다는 목표를 가지고...

일단 쿼리를 하나 보죠.

SELECT TOP pageSize*10+1 ID from TBL where ID <= 1페이지 11페이지 21페이지 등 각 1페이지의 처음 ID

이 쿼리는 페이지 바로가기 버튼 구현을 위한 쿼리입니다.

페이지 사이즈가 10개라고 하면 101개를 가져옵니다. 101개면 너무 많지 않냐 하겠지만 적은 갯수입니다.^^

컬럼이 하나밖에 없기 때문에 속도 저하가 거의 없는 쿼리죠.

이 결과를 가지고 1 2 3 4 5 6 7 8 9 10 다음 버튼 구현이 가능합니다. 이전 버튼 구현은 역시 약간 복잡하죠?

PageInfo = rs.getrows() 등의 좋은 메소드 등을 통해 배열로 만든 후 이 기능을 구현하는 것이 가장 좋은 방법일거라 생각됩니다.


 

SELECT TOP 10 ID, subject, contents from TBL where ID not in(SELECT TOP pageSize*(현재페이지의한자리숫자-1 / 0일때는 10) ID from TBL  where ID <= 페이지 11페이지 21페이지 등 각 1페이지의 처음 ID order by ID DESC)  and ID <= 페이지 11페이지 21페이지 등 각 1페이지의 처음 ID order by ID DESC


 

이 쿼리는 실제로 글을 뿌려주는 쿼리입니다. NOT IN이 쓰였네요. 하지만 10페이지 단위로 끊어서 10페이지 글 내에서 NOT IN을 사용하기 때문에 항상 빠른 속도를 내줄 수 있는 쿼리입니다.

페이지당 글 수가 아무리 많아도 100개 이하가 대부분이기 때문에 10페이지 단위로 끊는다고 해도 1000개의 글 내에서 모든 작업이 이루어지기 때문에 성능 저하는 거의(아예) 없다고 보시면 됩니다. 1000개정도 레코드는 아무것도 아니지요.


 

다만 걸리는 것은 페이지 바로 가기 버튼을 구현하기 위해 비슷한 쿼리를 두번 날렸기 때문에 두배의 비용이 든다는 거겠지요.


 

#7. 검색에 대한 이야기

검색 속도를 위한 가장 좋은 방법은 고가의 검색 엔진을 사용하는 것입니다. 검색엔진에 DB를 설정하고 URL 저장 방법만 설정해 놓고 스케쥴링만 해 놓으면 검색엔진은 알아서 DB를 검색하고 고쳐진 값에 대해서 URL 링크를 인덱싱해 놓습니다.

속도도 빠르고 한글의 경우 형태소 분석기를 통해 별에 별 검색도 가능하며 서비스 측면에서는 하일라이트 생성기등을 통해 사용자 편의성을 제공해주고... 뭐... 문제는 쩐이군요^^;

MSSQL에서 우리는 like '%xxx%' 검색을 많이 활용하게 됩니다.

SELECT ID, subject, contents from TBL where subject like '%xxx%'

이 쿼리는 xxx를 가지는 모든 subject 컬럼에 대해 검색을 하게 됩니다. 그럼 어떻게 해야 할까요. 넵~ 인덱스를 설정해야 합니다. subject에 대해서 넌클러스터드 인덱스를 설정해 주어야 합니다. 그러면 엄청난 속도 향상을 느낄 수 있습니다.

subject 는 varchar(255)형입니다. 그렇기 때문에 인덱스 설정이 가능합니다.

그럼 contents like '%xxx%'는?

헌데 contents가 text형이거나 varchar(max) (이건 2005에서 지원)라면?...

불행히도 인덱스를 줄수가 없습니다. MSSQL은 그다지 많은 인덱스 공간을 지원해주지 않습니다.

이경우 가능하면 input쪽에 varchar(4000)정도 만큼만 글을 입력하도록 제한하거나 varchar(4000)을 몇개 더 만들어서 DB저장시 나누어서 넣어주는게 좋습니다.

편법이고 지저분한 방법입니다.^^; 그래도 뭐 속도 향상이 있다면야... 모두들 어쩔 수 없이 text형도 검색을 하지만, 좋은 방법이 아닙니다. 글이 1만개만 넘어가다 상당한 부하가 걸릴 거라 생각됩니다.

넌클러스터드 인덱스만이라도 걸려 있다면, 성능은 무척 좋아집니다.

하지만 너무 방법이 지저분 하네요.

더좋은 방법이 있겠죠? MSSQL은 이런 경우를 위해 풀텍스트검색서비스를 지원합니다. varchar(MAX)나 text형 등을 시중의 검색엔진과 비슷한 방식으로 인덱싱하고 검색할 수 있는 방법은 제공합니다. 속도가 그렇게 좋지는 못합니다만...  형태소 분석기도 들어 있고 유사어 검색도 가능하고 정확도 정렬도 가능합니다. 다만 문제는 속도가 생각처럼 나오지는 않는 다는 것입니다. 특히 정확도 정렬을 위한 정렬의 경우 100만건 이상으로 테스트시 1초가 넘어가더군요.

그리고 MSSQL 2000에서 한글은 제대로 지원되지 않았습니다. 2005에서 한글 지원이 되는데... 실제로 띄어쓰기를 하지 않아도 검색이 되는 정도를 확인하였습니다. 동의어 검색도 가능하다고 하는데... 저역시 많은 테스트가 필요할 것 같습니다.

2000에서는 전혀 안쓰는 기능이었지만, 2005에서는 충분히 활용가치가 있을 것 같네요. 다만 호스팅 업체에서 이 기능을 지원하지 않는 다는 것이 가장 큰 문제입니다.^^; 인덱싱 속도는 꽤 빠르나 하드디스크를 많이 차지하기 때문에, 그리고 인덱싱시 꽤 부하를 주기 때문에 지원하지 않는 것 같으며, 2000의 경우 한글 인덱싱 자체가 잘 안되기 때문에 지원을 하지 않는 것 같습니다.

다만 MSSQL 2005라면 프로젝트에 충분히 쓸 수 있을 것도 같습니다. 제가 이번에 활용해보도록 하겠습니다.

상용검색엔진에 비하면 못하겠지만 어느정도 흉내를 내주고 CONTAINS(TABLE)/ FREETEXT(TABLE)등의 4개의 함수를 통해 SQL쿼리문 내에서 사용하기 때문에 개발하기가 수월합니다.

풀텍스트 검색엔진에 대한 이야기는 또 다음에 계속 하도록 하겠습니다.

twitter facebook me2day 요즘
11개(1/1페이지)
PHP & MySQL
번호 제목 글쓴이 조회 날짜
11 [MYSQL] 게시판 페이징(?) 속도 향상 [1] 관리자 11682 2014.08.13 02:18
>> [MYSQL] 페이징 쿼리, 페이징 기법 관리자 22147 2014.08.13 01:48
9 [MYSQL] 이메일 필터 정규식 관리자 8118 2014.07.25 11:32
8 [MYSQL] MYSQL EXPLAIN 관리자 6193 2013.10.28 12:10
7 [MYSQL] mysql성능 향상 첨부파일 관리자 4484 2013.10.07 12:52
6 [MYSQL] mysql 최적화 관리자 5238 2013.10.07 12:35
5 [MYSQL] semi join으로 인한 비효율을 제거. 관리자 6118 2013.05.15 12:14
4 [MYSQL] MYSQL DB튜닝 관리자 6146 2013.04.05 10:15
3 [MYSQL] 자주쓰는 MYSQL 관리자 4925 2013.04.05 10:14
2 [MYSQL] DB생성+ 계정 셋팅 관리자 3690 2013.01.21 15:15
1 [MYSQL] union order by 관리자 3869 2013.01.21 14:24
많이 본 글
댓글 많은 글