게시판에 대한 쿼리 및 인덱스 작성 방법

웹 개발자들을 대상으로 교육을 하다보면, 게시판에 대한 빠른 검색방법에 대하여 많은 질문을 받습니다. 아마도 웹 개발자들이 처음으로 구축하게 되는 예제가 게시판이어서 그런것 같습니다.

먼저, 게시판의 종류는 답글(질의응답)과 댓글(리플) 없는 자유게시판에서부터 답글이 있는 계층형(질의응답) 게시판, 댓글이 포함되는 게시판, 요즘에는 댓글도 계층형으로 구성되는 게시판도 있습니다.

여기서는 가장 간단한 자유 게시판을 예로 들어, 쿼리와 인덱스 작성에 대하여 알아보겠습니다.

실습을 위해 테이블을 다음과 같이 생성하였습니다.

create table freeboard
(no number,
subject varchar2(100),
content varchar2(4000));

게시판의 일련 번호 부여를 위해 시퀀스를 작성했습니다.

create sequence freeboard_no_seq
start with 1
increment by 1;

게시판의 성능 테스트를 위해서 10만건의 게시물을 입력하였습니다.

begin
  for i in 1..100000 loop
      insert into freeboard values(freeboard_no_seq.nextval, i||'번째 게시물 제목', lpad('*', 500, '*'));
      commit;
  end loop;
end;
/

쿼리의 성능 측정을 위해 AUTOTRACE를 사용하기로 하였습니다. 먼저, plustrace 롤을 만들어 사용자에게 부여하고, 실행계획이 저장될 plan_table을 생성하였습니다.

connect / as sysdba
@C:\oracle\product\10.2.0\db_1\sqlplus\admin\plustrce.sql
grant plustrace to scott;

connect scott/tiger
@C:\oracle\product\10.2.0\db_1\rdbms\admin\utlxplan.sql

set autotrace traceonly

<인덱스가 없는 상태에서 5만번째 게시물을 검색해봅니다.>

select no, subject, content from freeboard
where no=50000;

주의 깊게 보아야 할 내용은 다음과 같습니다.

Execution Plan

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |   528 |  1708   (1)| 00:00:21 |
|*  1 |  TABLE ACCESS FULL| FREEBOARD |     1 |   528 |  1708   (1)| 00:00:21 |
-------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
        165  recursive calls
          0  db block gets
       7769  consistent gets
          0  physical reads
          0  redo size
       1058  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed

=> no 컬럼에 인덱스가 존재하지 않기 때문에 오라클의 옵티마이저는 Full Table Scan 방식을 선택하였으며, 읽어온 블럭의 갯수는 7769(=7769+0)개 입니다.

<이번엔 페이지당 표시되는 게시물을 20개로 가정하고, 게시판의 첫번째 페이지(글번호, 제목)를 검색해봅니다.>

select t2.no, t2.subject
from (select rownum rn, t1.no, t1.subject
      from (select no, subject
            from freeboard
            order by no desc) t1
      where rownum <= 1*20) t2
where rn >= (1-1)*20+1;

=> 서브 쿼리를 사용한 이유는 게시물이 삭제되면, 글번호가 연속적이지 않기 때문입니다.

Execution Plan
--------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |    20 |  1560 |  1707   (1)| 00:00:21 |
|*  1 |  VIEW                    |           |    20 |  1560 |  1707   (1)| 00:00:21 |
|*  2 |   COUNT STOPKEY          |           |       |       |            |          |
|   3 |    VIEW                  |           |   100K|  6347K|  1707   (1)| 00:00:21 |
|*  4 |     SORT ORDER BY STOPKEY|           |   100K|  2636K|  1707   (1)| 00:00:21 |
|   5 |      TABLE ACCESS FULL   | FREEBOARD |   100K|  2636K|  1707   (1)| 00:00:21 |
--------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       7750  consistent gets
          0  physical reads
          0  redo size
       1251  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         20  rows processed

=> Full Table Scan 작업 후, 정렬 작업이 수행되었으며, 7750(=7750+0)개 블럭을 읽었습니다.

<마지막 페이지(5000번째) 검색>

select t2.no, t2.subject
from (select rownum rn, t1.no, t1.subject
      from (select no, subject
            from freeboard
            order by no desc) t1
      where rownum <= 5000*20) t2
where rn >= (5000-1)*20+1;

Execution Plan
----------------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |   100K|  7617K|       |  2479   (2)| 00:00:30 |
|*  1 |  VIEW                    |           |   100K|  7617K|       |  2479   (2)| 00:00:30 |
|*  2 |   COUNT STOPKEY          |           |       |       |       |            |          |
|   3 |    VIEW                  |           |   100K|  6347K|       |  2479   (2)| 00:00:30 |
|*  4 |     SORT ORDER BY STOPKEY|           |   100K|  2636K|  7096K|  2479   (2)| 00:00:30 |
|   5 |      TABLE ACCESS FULL   | FREEBOARD |   100K|  2636K|       |  1707   (1)| 00:00:21 |
----------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       7750  consistent gets
          0  physical reads
          0  redo size
       1143  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         20  rows processed

=> 마찬가지로 Full Table Scan 작업 후, 정렬 작업이 수행되었으며, 7750(=7750+0)개 블럭을 읽었습니다.

게시판의 게시물은 일련 번호에 따라 내림차순으로 출력되어야 하므로 내림차순 인덱스를 구성합니다.

create unique index freeboard_no_idx on freeboard(no desc);

일련 번호에 대하여 기본키 제약조건을 설정합니다.

alter table freeboard
add constraint freeboard_no_pk primary key(no);

테이블 통계와 인덱스 통계를 수집합니다.

exec dbms_stats.gather_table_stats('scott', 'freeboard');

exec dbms_stats.gather_index_stats('scott', 'freeboard_no_idx');

<인덱스가 있는 상태에서 5만번째 게시물을 검색해봅니다.>

select no, subject, content from freeboard
where no=50000;

Execution Plan

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |   528 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| FREEBOARD       |     1 |   528 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | FREEBOARD_NO_PK |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        969  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

=> 인덱스를 이용하여 검색하기 때문에 3(=3+0)개 블럭을 읽었습니다.

<페이지당 표시되는 게시물을 20개로 가정하고, 첫번째 페이지(글번호, 제목) 검색>

select t2.no, t2.subject
from (select rownum rn, t1.no, t1.subject
      from (select no, subject
            from freeboard
            order by no desc) t1
      where rownum <= 1*20) t2
where rn >= (1-1)*20+1;

Execution Plan
--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |    20 |  1560 |     4   (0)| 00:00:01 |
|*  1 |  VIEW                          |                 |    20 |  1560 |     4   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY                |                 |       |       |            |          |
|   3 |    VIEW                        |                 |    20 |  1300 |     4   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| FREEBOARD       |   100K|  2636K|     4   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN DESCENDING| FREEBOARD_NO_PK |    20 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          9  consistent gets
          8  physical reads
          0  redo size
       1251  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         20  rows processed

=> 인덱스를 내림차순으로 구성하였기 때문에 정렬작업이 수행되지 않고 17(=9+8)개 블럭을 읽었습니다.

<마지막 페이지(5000번째) 검색>

select t2.no, t2.subject
from (select rownum rn, t1.no, t1.subject
      from (select no, subject
            from freeboard
            order by no desc) t1
      where rownum <= 5000*20) t2
where rn >= (5000-1)*20+1;

Execution Plan
----------------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |   100K|  7617K|       |  2479   (2)| 00:00:30 |
|*  1 |  VIEW                    |           |   100K|  7617K|       |  2479   (2)| 00:00:30 |
|*  2 |   COUNT STOPKEY          |           |       |       |       |            |          |
|   3 |    VIEW                  |           |   100K|  6347K|       |  2479   (2)| 00:00:30 |
|*  4 |     SORT ORDER BY STOPKEY|           |   100K|  2636K|  7096K|  2479   (2)| 00:00:30 |
|   5 |      TABLE ACCESS FULL   | FREEBOARD |   100K|  2636K|       |  1707   (1)| 00:00:21 |
----------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       7750  consistent gets
          0  physical reads
          0  redo size
       1143  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         20  rows processed

=> 인덱스가 있지만, 오라클의 옵티마이저는 테이블 전체를 읽고 정렬을 수행하는 방법을 선택했습니다. 총 7750(=7750+0)개 블럭을 읽었습니다.

그렇다면, Hint를 주어 정렬 작업을 수행하지 않고 인덱스를 사용한 경우, 쿼리의 성능을 살펴보겠습니다.

select t2.no, t2.subject
from (select rownum rn, t1.no, t1.subject
      from (select /*+ index(freeboard freeboard_no_idx) */ no, subject
            from freeboard) t1
      where rownum <= 5000*20) t2
where rn >= (5000-1)*20+1;

Execution Plan
--------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |   100K|  7617K|  7932   (1)| 00:01:36 |
|*  1 |  VIEW                         |                  |   100K|  7617K|  7932   (1)| 00:01:36 |
|*  2 |   COUNT STOPKEY               |                  |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| FREEBOARD        |   100K|  2636K|  7932   (1)| 00:01:36 |
|   4 |     INDEX FULL SCAN           | FREEBOARD_NO_IDX |   100K|       |   227   (2)| 00:00:03 |
--------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       7920  consistent gets
          0  physical reads
          0  redo size
       1143  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         20  rows processed

=> 오라클 옵티마이저의 선택이 옳았습니다. 오히려, 더 많은 블럭(7920개)을 읽는군요.

어쨌든, 게시판에서 게시물을 글번호에 대한 Equal 조건으로 검색하는 경우, 오름차순이나 내림차순 인덱스와 관련없이
인덱스만 존재하면 검색속도는 충분히 보장 받을 수 있습니다. 그러나, 게시판을 최근 페이지 단위로 검색해야 하는 경우에는 인덱스를 내림차순으로 구성하여 정렬 작업을 피하는 편이 유리합니다. 물론, 오라클의 버전에 따라 오름차순 인덱스를 구성해도 옵티마이저가 인덱스를 역순으로 사용하여 정렬 작업을 회피하기도 하기 때문에 항상 실행계획을 확인하는 습관을 들이는것이 좋을 것 같습니다.