[17] 기본적인 웹 게시물 관리 - 오라클 데이터베이스 페이징 처리

 

구현된 기능들 중 가장 미숙한 부분은 목록 페이지이다! 목록 페이지는 기본적으로 페이징 처리가 필요한데 수많은 데이터를 한 페이지에서 보여주면, 처리 성능에 영향을 미친다. 또한, 브라우저에서도 역시 데이터의 양이나 처리 속도에 문제를 일으키게 된다.

 

일반적으로 페이징 처리는 크게 번호를 이용하거나 '계속 보기'의 형태로 구현된다. 번호를 이용한 페이징 처리는 과거 웹 초기부터 이어오던 방식이고, '계속 보기'는 Ajax와 앱이 등장한 이후에 '무한 스크롤'이나 '더 보기'와 같은 형태로 구현된다. 예제에서 목록 페이지는 전통적인 번호를 이용하는 방식으로 처리하게 된다. 오라클에서 페이징 처리하는 것은 MySQL에 비해서 추가적인 지식이 필요하므로 이에 대한 학습을 선행해야한다.

 

1. order by의 문제

 

프로그램을 이용해서 정렬을 해 본 적이 있다면 데이터의 양이 많을수록 정렬이라는 직업이 얼마나 많은 리소스를 소모하는지 알 수 있다. 데이터 베이스는 경우에 따라서 수백만 혹은 천 만개 이상의 데이터를 처리하기 때문에 이 경우 정렬을 하게 되면 엄청나게 많은 시간과 리소스를 소모하게 된다.

 

데이터베이스를 이용할 때 웹이나 애플리케이션에 가장 신경쓰는 부분은 

1) 빠르게 처리되는 것

2) 필요한 양만큼만 데이터를 가져오는 것

이다. 예를 들어, 거의 모든 웹페이지에서 페이징을 하는 이유는 최소한의 필요한 데이터만을 가져와서 빠르게 화면에 보여주기 위함이다.

 

만일 수백 만개의 데이터를 매번 정렬을 해야 하는 상황이라면 사용자는 정렬된 결과를 볼 때까지 오랜 시간을 기다려야만 하고, 특히 웹에서 동시에 여러 명의 사용자가 정렬이 필요한 데이터를 요청하게 된다면 시스템에는 많은 부하가 걸리게 되고 연결 가능한 커넥션의 개수가 저점 줄어서 서비스가 멈추는 상황을 초래하게 된다.

 

빠르게 동작한는 SQL을 위해서는 먼저 order by를 이용하는 작업을 가능하면 하지 말아야 한다. order by는 데이터가 많은 경우에 엄청난 성능의 저하를 가져오기 때문에 1) 데이터가 적은 경우 2) 정렬을 빠르게 할 수 있는 방법이 있는 경우가 아니라면 order by는 주의해야 한다.

 

1-1. 실행 계획과 order by

 

오라클의 페이징 처리를 제대로 이해하기 위해서 반드시 알아두어야 하는 것이 실행 계획이다. 실행 계획은 말 그대로 'SQL을 데이터베이스에서 어떻게 처리할 것인가?'에 대한 것이다. SQL이 데이터베이스에 전달되면 데이터베이스는 여러 단계를 거쳐서 해당 SQL을 어떤 순서와 방식으로 처리할 것인지 계획을 세우게 된다. 

 

데이터베이스에 전달된 SQL문은 아래와 같은 과정을 거쳐서 처리된다.

 

SQL 파싱 단계에서는 SQL 구문에 오류가 있는지 SQL을 실행해야 하는 대상 객체가 존재하는지를 검사한다. SQL 최적화 단계에서는 SQL이 실행되는데 필요한 비용을 계산하게 된다. 이 계산된 값을 기초로 해서 어떤 방식으로 실행하는 것이 가장 좋다는 것을 판단하는 '실행계획'을 세우가 된다.

 

SQL 실행 단계에서는 세워진 실행 계획을 통해서 메모리상에서 데이터를 읽거나 물리적인 공간에서 데이터를 로딩하는 등의 작업을 하게 된다. 개발자들은 도구를 이용하거나 SQL Plus 등을 이용해서 SQL에 대한 실행 계획을 알아볼 수 있다. SQL Developer에서는 간단히 버튼을 클릭해서 실행 계획을 확인 할 수 있다.

 

예를 들어, '게시물 번호의 역순으로 출력하라'는 처리를 한다면 SQL Developer에서 다음과 같은 쿼리문을 사용한다.

 

상단의 버튼 중에는 SQL에 대해서 '실행 계획'을 쉽게 볼 수 있도록 버튼이 제공 된다.

 

실행 계획을 보면 트리 구조로 방금 전 실행한 SQL이 어떻게 처리된 것인지를 알려준다. 흔히 SQL 튜닝이라고 하는 작업은 이를 보고 어떤 방식이 더 효과적인지를 판단해서 수정하게 된다. 

 

가장 간단하게 실행 계획을 보는 방법은 '안쪽에서 바깥쪽으로, 위에서 아래로' 보면 된다! 위 그림의 내용을 해석하자면 'TBL_BOARD' 테이블을 'FULL'로 접근하고 정렬했다는 것을 의미한다. 'FULL'이라는 의미는 테이블 내의 모든 데이터를 스캔했다는 의미다. 실행 계획을 세우는 것은 데이터베이스에서 하는 역할이기 때문에 데이터의 양이나 제약 조건 등의 여러 상황에 따라서 데이터베이스는 실행계획을 다르게 작성한다. 

 

테스트를 위해서 데이터가 좀 많아지도록 아래의 SQL을 여러 번 실행해서 데이터를 수백 만개로 만든 후에 커밋을 한다. 

 

위의 insert 문은 여러 번 실행하게 되면 현재 tbl_board 테이블의 데이터 수만큼 다시 insert가 진행된다. 결과를 보면 insert 문을 실행할 때마다 2배씩 데이터가 늘어나게 된다. 아래 그림은 여러 번 실행해서 한 번에 27만 개의 데이터를 복사해서 넣는 것을 보여준다. 

 

commit 후에 

select count(*) from tbl_board;

를 실행해 보면 데이터의 수가 엄청나게 늘어난 것을 확인할 수 있다.

 

데이터가 많아지면 정렬에 그만큼의 시간을 소모하게 된다. 고의적으로는 bno라는 칼럽의 값에다 1을 추가한 값을 역순으로 정렬하는 SQL을 만든다면 다음과 같다. 

 

연산 작업이 추가되기는 했지만 SQL문의 결과가 나오는데는 나의 경우에는 7.029초가 나왔따. (실행 시간은 현재 시스템의 상황이나 데이터베이스의 상황에 따라 차이가 난다. 반복적으로 몇 번 실행하면 데이터베이스가 메모리상에 보관하는 데이터를 가져오는 상황이 되고 대략 1초대의 결과가 나올 수도 있다.)

 

위의 SQL을 실행한 결과는 테이블 전체를 스캔하는 것을 볼 수 있다.

 

실행 계획을 잠깐 살펴보면 TBL_BOARD를 'FULL'로 조사했고, 바깥쪽으로 가면서 'SORT'가 일어난 것을 볼 수 있다. 이때 가장 많은 시간을 소모하는 작업은 정렬하는 작업이다. 

 

위의 SQL에서 'order by bno + 1 desc'라는 조건에서 '+1' 하는 것은 정렬에 아무런 도움을 주지 않으므로 아래와 같이 SQL을 수정해서 실행한다. (+1을 그럼 왜한거지...??)

SELECT * FROM tbl_board ORDER BY bno DESC;

 

연산이라는 차이가 있기는 하지만 실행에 걸리는 시간은 차이가 많이 나게 된다

 

이전에 8초 이상이 걸리던 작업이 거의 0초만에(나는1초지만..) 실행되고 차이가 나게 된다. 이 결과의 차이에는 실행 계획도 기존과 다르게 동작한다. 

 

기존의 SQL은 TBL_BOARD 테이블 전체를 스캔했지만, 이번에는 PK_BOARD라는 것을 이용해서 접근하고 기존과 달리 맨 위의 SORT 과정이 없는 것을 볼 수 있다.

 

이것을 이해하려면 데이터 베이스의 인덱스에 대해서 조금 알아둘 필요가 있다.

 

2. order by 보다는 인덱스

 

데이터가 많은 상태에서 정렬 작업이 문제가 된다는 사실을 알았다면, 이 문제를 어떻게 해결해야 하는지를 살펴보겠따. 가장 일반적인 해결책은 '인덱스'를 이용해서 정렬을 생략하는 방법이다. 결론부터 말하자면 '인덱스'라는 존재가 이미 정렬된 구조이므로 이를 이용해서 별도의 정렬을 하지 않는 방법이다. 인덱스가 무엇인지에 대해서는 조금 더 뒤에 살펴볼 예정이고, 우선 위와 같은 상황에서 다음과 같은 SQL을 실행해 보겠다.

 

 

위의 SQL을 실행한 결과는 테이블 전체를 조사하고 정렬한 것과는 동일하지만 실행 시간은 엄청나게 차이가 나게 된다..!! (0.009초!)

 

가장 중요한 점은 SQL의 실행 시간이 거의 0초로 나온다는 점이다. SQL문의 실행 계획은 아래와 같은 모습을 가지게 된다. 

 

내 예시가 좀 이상한데... 왜 내꺼는 PK_BOARD가 안나오는지 모르겠지만 ㅠㅠ (왠지 저번에 pk 관련해서 쿼리문 실행할 때 제대로 안되었던 것 같음)

SQL의 실행 계획에서 주의해서 봐야 하는 부분은 

 

1) SORT를 하지 않았다는 점

2) TBL_BOARD를 바로 접근하는 것이 아니라 PK_BOARD를 이용해서 접근 한 점

3) RANGE SCAN DESCENDING, BY INDEX ROWID로 접근했다는 점

 

이다. 

 

2-1. PK_BOARD 라는 인덱스

 

tbl_board 테이블을 생성했을 때의 SQL을 다시 한 번 살펴보겠다. 

create table tbl_board (
	bno number(10,0), 
    title varchar2(200) not null,
    content varchar2(2000) not null,
    writer varchar2(50) not null,
    regdate date default sysdate,
    updatedate date default sysdate
);

alter table tbl_board add constraint pk_board primary key(bno);

 

테이블을 생성할 때 제약 조건으로 PK를 지정하고 PK의 이름이 'pk_board'라고 지정하였다. 데이터베이스에서는 pk는 상당히 중요한 의미를 가지는데, 흔히 말하는 '식별자'의 의미와 '인덱스'의 의미를 가진다.

 

'인덱스'는 말 그대로 '색인'이다. 우리가 가장 흔히 접하는 인덱스는 도서 뒤쪾에 정리되어 있는 색인이다. 색인을 이용하면 사용자들은 책 전체를 살펴볼 필요 없이 색인을 통해서 자신이 원하는 내용이 책의 어디에 있는지 알 수 있다. 데이터베이스에서 인덱스를 이해하는 가장 쉬운 방법은 데이터베이스의 테이블을 하나의 책이라고 생각하고 어떻게 데이터를 찾거나 정렬하는지를 생각하는 것이다. 색인은 사람들이 쉽게 찾아볼 수 있게 알파벳 순서나 한글 순서로 정렬한다. 이를 통해서 원하는 내용을 위에서부터 혹은 반대로 찾아나가는데 이를 '스캔'한다고 표현한다.

 

데이터베이스에 테이블을 만들 때 pk를 부여하면 지금까지 얘기한 '인덱스'라는 것인 만들어진다. 데이터베이스를 만들 때 pk를 지정하는 이유는 '식별'이라는 의미가 있지만, 구조상으로는 '인덱스'라는 존재가 만들어지는 것을 의미한다. tbl_board 테이블은 bno라는 칼럼을 기준으로 인덱스를 생성하게 된다.

 

출처 - 구글링 ㅎㅎ..

 

인ㄷ게스와 실제 테이블을 연결하는 고리는 ROWID라는 존재이다. ROWID는 데이터베이스 내의 주소에 해당하는데 모든 데이터는 자신만의 주소를 가지고 있다. 

 

SQL을 통해서 bno 값이 100번인 데이터를 찾고자 할 때에는 SQL은 'where bno = 100'과 같은 조건을 주게 된다. 이를 처리하는 데이터베이스 입장에서는 tbl_board라는 책에서 bno 값이 100인 데이터를 찾아야만 한다. 만일 책이 얇아서 내용이 많지 않다면 속히 전체를 살펴보는 것이 더 빠를 것이다. (이를 데이터베이스 쪽에서는 'FULL SCAN'이라고 표현한다) 하지만 내용이 많고, 색인이 존재한다면 당연히 색인을 찾고 색인에서 주소를 찾아서 접근하는 방식을 이용할 것이다. 

 

실행 계획을 보면 이러한 생각이 데이터베이스 내에서 진행되는 것을 확인 할 수 있다. 안쪽을 먼저 보면 PK_BOARD는 인덱스이므로 먼저 인덱스를 이용해서 100번 데이터가 어디에 있는지 ROWID를 찾아내고, 바깥쪽을 보면 'BY INDEX ROWID'라고 되어 있는 말 그대로 ROWID를 통해서 테이블에 접근하게 된다. 

 

3. 인덱스를 이용하는 정렬

 

인덱스에서 가장 중요한 개념 중 하나는 '정렬이 되어 있다는 점'이다. 정렬이 이미 되어 있는 상태이므로 데이터를 찾아내서 이들을 SORT 하는 과정을 생략 할 수 있다.

 

'bno의 역순으로 정렬한 결과'를 원한다면 이미 정렬된 인덱스를 이용해서 뒤에서부터 찾아 올라가는 방식을 이용할 수 있다. 이때 '뒤에서부터 찾아 올라간다'는 개념이 'DESCENDING'이다.

 

인덱스를 역순으로 찾기 때문에 가장 먼저 찾은 bno 값은 가장 큰 값을 가진 데이터가 된다. 이후에는 테이블에 접근해서 데이터를 가져오게 되는데, 이런 과정이 반복되면 정렬을 하지 않아도 동일하게 정렬된 결과를 볼 수 있게 된다. 

 

하나의 예를 더 생각해 보면, 만일 사용자가 'bno의 순서로 정렬해 달라'고 요구하는 상황이라면 PK_BOARD 인덱스가 앞에서부터 찾아서 내려가는 구조를 이용하는 것이 효율적이다. SQL developer를 이용해서 실행해 보면 아래와 같은 실행 계획이 수립되는 것을 볼 수 있다. 

 

실행 계획 상으로 PK_BOARD 인덱스를 먼저 접근하고, TBL_BOARD를 이용하는 것을 볼 수 있다. SORT가 없기 때문에 0초에 가까운 성능을 보여준다. 실무에서도 데이터의 양이 많고 정렬이 필요한 상황이라면 우선적으로 생각하는 것이 '인덱스'를 작성하는 것이다. 데이터의 양이 수천, 수만개 정도의 정렬은 그다지 부하가 걸리지 않지만 그 이상의 데이터를 처리해야 하는 상황이라면 정렬을 안할 수 있는 방법에 대해서 고민해야만 한다.

 

3-1. 인덱스와 오라클 힌트(hint)

 

웹 페이지 목록은 주로 시간의 역순으로 정렬된 결과를 보여준다. 최신 데이터가 가장 중요하기 때문에 시간의 역순으로 정렬해서 최신 게시물들을 보여주게 된다. 이 경우 개발자의 입장에서는 정렬을 안 하는 방식으로 select문을 실행하고 싶어한다.

 

오라클은 select 문을 전달할 때 'hint'라는 것을 사용할 수 있다. 힌트는 말 그대로 데이터베이스에 '지금 내가 전달한 select문을 이렇게 실행해 주면 좋겠습니다'라는 힌트다. 힌트는 특이하게도 select문을 어떻게 처리하는지에 대한 얘기일 뿐이므로 힌트 구문에서 에러가 나도 전혀 SQL 실행에 지장을 주지 않는다. 따라서 힌트를 이용한 select문을 작성한 후에는 실행 계획을 통해서 개발자가 원하는 대로 SQL이 실행되는지를 확인하는 습관을 가져야 한다.

 

게시물 목록은 반드시 시간의 역순으로 나와야만 하기 때문에 SQL에서는 'order by bno desc'와 같은 구문을 추가할 수 있다. 문제는 'order by bno desc'와 같은 조건은 데이터베이스 상황에 따라서 테이블의 모든 데이터를 정렬하는 방식으로도 동작할 수 있다는 점이다. 

 

반면에 힌트는 개발자가 데이터 베이스에 어떤 방식으로 실행해 줘야 하는지를 명시하기 때문에 조금 강제성이 부여되는 방식이다. 

select * from tbl_board order by bno desc;

select /*+INDEX_DESC (tbl_board pk_board) */
*
from tbl_board;

위의 두 SQL은 동일한 결과를 생성하는 SQL이다. 

 

두 번째 select문은 order by 조건이 없어도 동일한 결과가 나온 것에 주목해야 한다. select 문에서 힌트를 부여했는데 힌트의 내용이 'tbl_board 테이블에 pk_board 인덱스를 역순으로 이용해 줄 것'이므로 실행 계획에서 이를 활용하고 있는 것을 확인할 수 있다.