[스프링 게시판][6] 페이징 기능 구현(페이징 쿼리 정리)

Git 주소 : github.com/sjinjin7/Blog_BoardProject

목표

 

페이징 기능 구현 쿼리 정리

 현재 작성된 '목록 페이지(list.jsp)' 경우 Database에 저장되어 있는 페이지 개수만큼 페이지 목록이 출력하도록 구현되어 있습니다. 해당 방식은 두 가지 문제점이 있습니다. 먼저 첫 번째 문제로 게시판 데이터가 많을수록 가독성이 떨어진다는 점입니다.  두 번째 문제점은 많은 데이터를 한꺼번에 한 페이지에 불러오고 출력하는 데 있어서 시스템의 자원을 너무 많이 사용한다는 점입니다.

 이러한 문제점을 보안하기 위해서 "페이징 기법"을 사용할 것입니다. 페이징 기법은 수많은 자료 데이터(행, 레코드)를 일정 크기로 나누어서, 나누어진 하나하나의 집단에 페이지 번호를 부여하는 방식입니다. 정해진 갯수와 원하는 영역의 게시판 데이터를 불러오고 출력한다면 '가독성'의 문제와 '자원 낭비' 문제점이 보완될 것입니다.

 

 

 현재의 프로젝트에서 "페이징 기능"을 적용하고자 할때 크게 2개의 작업을 진행해주어야 합니다.

 

그림1 서울특별시 자유게시판

 첫 번째 작업은 DB에서 정해진 개수와 특정 영역(페이지)의 데이터를 불러오고 출력하도록 쿼리 명령문을 수정해주어야 합니다.

 

 두 번째 작업은 사용자가 자신이 원하는 영역(페이지)으로 이동할 수 있도록 해주는 인터페이스를 제공하는 작업입니다. 위의 '그림 1'에서 파랑 표시가 이에 해당합니다.

 

 이번 포스팅에선 '페이징 처리'에 사용할 수 있는 쿼리의 종류에 대해 알아보고자 합니다.

 

순서

1. 페이징 쿼리

2. Rownum 방식 1

3. Rownum 방식 2

4. LIMIT 방식

 

 

1. 페이징 쿼리

 페이징 쿼리를 작성하는 데 있어서 크게 3가지 방법이 있습니다. Rownum을 통한 방식, limit을 통한 방식, top을 통한 방식이 있습니다. ROWNUM(ORACLE, Mari DB), LIMIT(MySQL, Maria DB), TOP(MSSQL) 방식 가각 해당 기능을 사용할 수 있는 DB가 정해져 있습니다.  반면 Rownum 기능은 거의 모든 DB에서 사용이 가능합니다. 현재 프로젝트 경우 Oracle과 MySQL을 사용하고 있기 때문에 Rownum 방식과 LIMIT 방식의 페이징 쿼리를 정리하고자 합니다. 

 

 Rownum과 LIMIT에 대해 간략한 설명입니다.

 

Rownum

 

 Rownum 모든 SQL에 그대로 삽입해서 사용할 수 있는 가상의 컬럼(Column) 입니다. 해당 컬럼의 값은 SQL이 실행되는 과정에서 발생하는 행의 일련번호입니다. 쉽게 말해 Select를 통해 출력되는 결과 테이블에 행(row) 번호를 매겨주는 기능입니다. 

 이러한 행 번호를 기준으로 '게시판 개수'와 '페이지 시작 번호' 데이터를 활용하여 페이징 처리를 할 수 있습니다. Oracle의 경우 Rownum을 '키워드'와 '함수' 두 가지 방식으로 사용이 가능합니다.(이번 포스팅에선 '키워드' 방식을 사용할 것입니다.) MySQL 경에도 '변수 선언'을 통해 편법으로 비슷한 기능 구현이 가능합니다.

 

 

LIMIT

 

 LIMIT이란 select 문을 통해 데이터를 검색할 때, 검색 결과의 특정부분만 반환 받고자 할때 사용할 수 있는 기능입니다. 해당 기능은 MySQL, Maria DB에서 '키워드'를 통해 사용이 가능하며, 검색을 위해 검색하고자 하는 '시작 행의 위치'를 의미하는 데이터와 검색하고자 하는 '행의 개수'를 의미하는 데이터를 활용합니다.

 

 

 

 각 방식을 소개하며 직접 실습해보기 위해서 '재귀 복사'를 통해 약 200만 개의 행을 생성하였습니다.

 

 
-- 재귀 복사
insert into vam_board(title,content,writer)(select title,content, writer from vam_board);
 
-- 행 확인
select count(*) from vam_board;
 

 

그림 2 Oracle

 

그림 3

 

 

 

2. Rownum 방식 1 (Oracle, MySQL)

 rownum 칼럼(Column) 추가와 bno 기준으로 역순으로 정렬된 테이블에서 11번째 행과 20번째 행을 출력하는 쿼리입니다.

 

Oracle

 

 
select rn, bno, title, content, writer, regdate, updatedate from(
 
        select /*+INDEX_DESC(vam_board pk_board) */ rownum as rn, bno, title, content, writer, regdate, updatedate
 
        from vam_board)
        -- select rownum as rownum as rn, bno, title, content, writer, regdate, updatedate from vam_board order by bno desc
 
where rn between 11 and 20;
    -- rn > 10 and rn <= 20;
 

 

그림 4

 

● rownum 사용법

 

 Oracle에서 rownum을 사용하기 위해서 단순히 select 문에 키워드를 넣기만 하면 됩니다. (그림 4)

 

그림 5

 

● 특정 행 번호만 출력되는 과정

 

  해당 방식 경우 서브 쿼리(그림 5)에서 Rownum이 포함된 가상의 테이블(View)이 만들어지면, 그 테이블을 다시 rwonum으로 필터링하여 테이블을 출력하는 방식입니다. 

 

 서브 쿼리만 따로 분리하여 실행을 하면 아래와 같습니다.

 

 

 

 위와 같이 출력된 결과 테이블을 대상으로 원하는 행(rownum)으로 필터링 후 출력하게 됩니다. 

 

그림 8

 

그림 9

 

그림 10 

 

 

● 힌트(Hint)와 실행계획(execution plan)이란?

 

 쿼리의 결과를 보면 최근에 저장된 행부터 출력된 것을 볼 수 있습니다. order by bno desc를 사용하지 않았음에도 이러한 결과가 나오는 이유는 오라클 힌트(Hint)를 사용하였기 때문입니다.

 

 

 힌트(Hint)란 개발자가 데이터베이스에 어떤 방식을 샐 행 해줘야 하는지를 명시하는 기능입니다. 힌트가 왜 필요한지를 이해하기 위해선 "실행 계획(execution plan)"에 대한 이해가 필요로 합니다.

 

 오라클 DB에서는 개발자가 작성한 쿼리를 실행할 때 "SQL파싱" => "SQL 최적화" => "SQL 실행" 단계 거치게 됩니다.  "SQL파싱"에선 SQL 구문에 오류가 있는지, SQL을 실행해야 하는 대상 객체(테이블, 제약 조건, 권한 등)가 존재하는지를 체크합니다. "SQL 최적화"에서는 SQL이 실행되는데 필요한 비용(cost)을 계산하게 됩니다. "SQL 실행"에서는 계산된 비용(cost) 값을 기초로 하여 어떠한 방식으로 실행하는 것이 좋은지를 판단한 "실행 계획(execution plan)"을 세웁니다.

 

 처리해야 할 데이터가 매우 많을 때 "실행 계획"이 일관적으로 세워지지 않을 수 있습니다. DBMS에서는 시스템의 상황, DB의 상황을 고려하여 "실행 계획"을 세우기 때문에, SQL문이 실행될 때 이전과 다른 실행계획을 가질 수도 있습니다. 문제는 최대한 빠른 결과를 내는 "실행 계획"이 있음에도 다른 "실행 계획"을 세울 수도 있다는 점입니다. 이런 경우에 힌트(hint)를 사용하여 특정 '실행 계획'만을 사용하도록 지정해줄 수 있습니다.

 

 이러한 용도 외에도 매우 복잡한 구조의 테이블을 다루어야 하는 경우 개발자가 인위적으로 실행 순서를 조작하여 DB가 작성한 '실행계획'보다 더 나은 성능의 '실행계획' 작성하기 위해서 힌트(hint)를 사용합니다.

 

 

 

● 실행계획 보는 방법

 

 Oracle 경우 sqldeveloper에서는 쉽게 SQL 문의 '실행 계획'을 볼 수 있습니다. '실행 계획'을 보고 싶은 쿼리문을 한번 클릭하여 선택되도록 한 후 상단의 실행계획 버튼을 누르면 볼 수 있습니다.

 

그림 12

 

 MySQL의 경우 실행계획을 보고싶은 쿼리 문의 제일 앞에 explain을 붙여서 쿼리문을 실행을하면 해당 쿼리문의 '실행계획'을 볼 수 있습니다.

 

그림 12-1

 

 

 

● 왜 'order by bno desc'가 아닌 인덱스 힌트(hint) 사용 이유

 

 아래의 SQL 명령문을 통해 힌트(hint)를 왜 사용했는지를 이해해보고자 합니다.

 

 
select * from vam_board order by bno desc;
 

 

 보통의 경우 위 SQL 문의 실행 계획은 '인덱스'를 활용하는 실행계획을 세우게 됩니다. 왜냐하면 제일 적은 비용(cost)으로 실행할 수 있는 방법이기 때문입니다.

 

 데이터베이스에서 인덱스란 도서 뛰 쪽에 정리되어 있는 '색인'과 같은 역할을 해줍니다. 보통 컬럼에 PRIMARY KEY(기본키) 설정을 하게 되면 자동적으로 해당 컬럼(Column)의 '인덱스'가 생성됩니다. 

 

아래는 '인덱스'를 활용한 실행계획입니다.

 

그림 13

 

그림 14

 

 위의 실행 계획을 해석해보면 PK_BOARD '인덱스'를 전체 역(DESC) 순으로 스캔을 합니다. 그리고 '인덱스'에는 실제 테이블에 저장되어 있는 각행의 주소인 'ROWID'가 존재하는데, 해당 'ROWID'를 사용해서 실제 테이블(VAM_BOARD)의 데이터들을 연결해주는 작업이 진행됩니다.

 

 

 매우 많은 데이터가 저장된 경우 인덱스를 활용하지 않은 실행계획이 세워지는 경우도 있는데, 해당 실행계획은 아래와 같습니다. 

 

그림 15

 해석을 하면  VAM_BOARD 테이블 전체를 스캔(FULL) 한 후 이를 다시 정렬(ORDER BY) 하는 순으로 계획되어 있습니다. 정렬을 하는 과정에서 상당히 많은 비용(COST)가 발생합니다. 인덱스를 사용하는 방식과는 다르게 정렬(ORDER BY) 과정에서 상당히 많은 비용(COST)가 발생하였습니다. 

 

 '인덱스'를 사용하지 않는 경우에는 테이블을 전체 스캔한 후 다시 정렬(sort)의 과정을 거치기 때문에 좀 더 많은 비용(cost)으로 실행되고, '인덱스'를 통한 접근의 경우 이미 처음 스캔을 할 때 정렬되어있는 인덱스를 활용하면 되기 때문에 다소 적은 비용(cost)으로 실행됩니다.

 

 이렇게 분명히 더 나은 성능의 "실행계획"이 있음에도, 정렬(sort)을 사용하는 다소 낮은 성능의 "실행계획"을 사용할 가능성을 없애기 위해서 힌트(hint)를 사용합니다. 인덱스와 관련된 힌트(hint)는 "INDEX_ASC(테이블명 , 인덱스명)", "INDEX_DESC(테이블명 , 인덱스명)"가 있습니다. 인덱스를 순서대로 이용할 경우는 "INDEX_ASC(테이블명 , 인덱스명)"를 사용하고, 인덱스를 역손으로 이용하고 싶은 경우는 "INDEX_DESC(테이블명 , 인덱스명)"를 사용하면 됩니다. 그리고 해당 인덱스 힌트를 사용할 경우는 "order by "를 사용할 필요가 없습니다. 인덱스 자체가 정렬이 되어있기 때문에 새로이 정렬(SORT)을 해줄 필요는 없습니다. 

 

 힌트는 대용량의 데이터를 가진 테이블 또는 복잡한 구조(ex. JOIN)를 가진 테이블을 검색할 때 성능을 향상하기 위해서 주로 사용합니다. 지금 현재의 테이블 경우 아주 단순한 구조와 상황이 때문에 사용해도 큰 문제는 없지만, 힌트(hint) 사용에 확신을 할 수 없는 상황에는 되도록이면 사용을 지양하는 것이 좋습니다. 어중간한 힌트(hint)로 인해 오히려 검색의 성능이 더 떨어질 수 있기 때문입니다.

 

 

MySQL

 

 MySQL에서도 변수 선언을 통해 Oracle에서의 Rownum을 사용한 쿼리를 비슷하게 구현이 가능합니다. 

 

 
select * from 
        (select @rownum := @rownum + 1 as rn, bno, title, writer, regdate, updatedate 
        from vam_board, (select @rownum := 0)  as rowcolumn order by bno desc) as rownum_table  
where rn > 10 and rn <=20;
-- where rn between 10 and 20;
 

 

 MySQL경우 rownum '키워드'와 '함수'를 제공하지 않기 때문에 변수를 사용하여 구현해야 합니다. 위의 제가 장성한 쿼리문은 from 구문에서 rownum 변수의 값을 0으로 초기화하고, select 구문에서 rownum 변수의 값이 + 1씩 추가되도록 작성하였습니다. 

 

그림 16

 

 이렇게 변수를 사용한 방법의 경우 오라클의 rownum과 같이 특정 목적 사용되도록 만들어진 기능이 아니라 사용자가 직접 정의하여 값을 추가하는 방식이기에 성능면에서 그리 좋지 못합니다. 테이블에 많은 데이터를 가진 경우엔 해당 방식 대신, MySQL에서 LIMIT 키워드를 사용하는 것이 좋습니다.

 

 

 

3. Rownum 방식 2(Oracle)

Oracle

 

 
select rn, bno, title, content, writer, regdate, updatedate from(
        select /*+INDEX_DESC(vam_board pk_board) */ rownum  as rn, bno, title, content, writer, regdate, updatedate 
        from vam_board where rownum <= 20) 
where rn > 10;
 

 

 이번 방식은 'Rownum 방식 1'과 큰 틀에서는 비슷합니다. 이번 방식도 서브 쿼리의 실행 결과인 행 번호를 가진 테이블을 반환받은 후 을 다시 rownum으로 필터링합니다. 하지만 조금 다른 점은 rownum 필터링에 대한 일부를 서브 쿼리에서 진행한다는 점입니다.

 

그림 17

 

 'Rownum 방식 2'는 매우 많은 데이터를 가진 테이블에서 rownum 앞번호를 검색하는 경우 'Rownum 방식 1'에 비해 매우 빠른 속도를 보여줍니다.

 

 아래는 Rownum 11에서 20까지의 행에 대한 정보를 'Rownum 방식 1'과 'Rownum 방식 2' 각각 사용하여 출력한 결과입니다.

 

그림 18  'Rownum 방식 1'

 

그림 19  'Rownum 방식 2'

 

 

  rownum 앞 번호를 검색하는 경우 위의 그림 18, 그림 19와 같이 매우 큰 성능 차이가 보입니다. 하지만  rownum 뒷번호(1,800,000 ~ 1,800,010)를 검색한 경우는 속도 차이가 거의 없습니다.

 

 
-- Rownum 방식 1
select rn, bno, title, content, writer, regdate, updatedate from(
        select /*+INDEX_DESC(vam_board pk_board) */ rownum as rn, bno, title, content, writer, regdate, updatedate
        from vam_board) t1
        -- select rownum as rownum as rn, bno, title, content, writer, regdate, updatedate from vam_board order by bno desc
where rn between 1800000 and 1800010;
 
-- Rownum 방식 2
select rn, bno, title, content, writer, regdate, updatedate from(
        select /*+INDEX_DESC(vam_board pk_board) */ rownum  as rn, bno, title, content, writer, regdate, updatedate 
        from vam_board where rownum <= 1800010) 
where rn > 1800000;
 

 

 

그림 20  'Rownum 방식 1'

 

그림 21  'Rownum 방식 2'

 

 

 rownum 앞번호를 검색하는 경우에는 'Rownum 방식 1'의 서브 쿼리에서 반환하는 테이블의 크기와 'Rownum 방식 2'의 서브쿼리에서 반환하는 테이블의 크기의 차이 때문에 검색 속도가 차이 납니다. rownum 11부터 20까지의 행을 검색하는 쿼리를 예로 들면 'Rownum 방식 1'의 경우 서브 쿼리에서 테이블이 가지고 있는 200만 개의 행을 모두 검색한 테이블을 반환합니다. 하지만 'Rownum 방식 2'의 경우 서브 쿼리는 검색조건에 의해 20개의 행만 검색 후 테이블을 반환합니다. 정리하면 서브 쿼리의 조건으로 인해 불필요한 검색을 하지 않음으로써 시간을 절약하여 검색 속도를 향상했습니다.

 

그림 22

 

 rownum 뒷번호를 검색하는 경우에는 'Rownum 방식 1'의 서브 쿼리에서 반환하는 테이블의 크기와 'Rownum 방식 2'의 서브쿼리에서 반환하는 테이블의 크기가 거의 같기 때문에 검색 속도가 비슷합니다.

 

그림 23

 

 뒤의 행을 검색할 때는 큰속도차이가 없더라도 앞의 행을 검색할때 앞도적으로 속도가 차이 나기 때문에 'Rownum 방식 2'를 사용하는 것이 좋습니다. 

 

 

 

4. LIMIT 방식(MySQL)

 LIMIT 방식을 활용하여 bno기준 역순으로 정렬된 테이블에서 11번째 행과 20번째 행을 출력하는 쿼리입니다.

 

 
select bno, title, writer, regdate, updatedate  
from vam_board order by bno desc 
limit 10, 10;
 

 

 위 코드의 limit은 'limit <skip> <count>'를 의미합니다. 위의 쿼리를 해석하면 10개의 행을 skip 하고 10개(count) 행을 선택한다는 의미입니다. 만약 51행부터 60행을 원한다면 'limit 50, 10'을 작성하면 되고 301행부터 310행을 원하면 'limit 300, 10'을 작성하면 됩니다.

 

 

 LIMIT방식은 Rownum방식에 비해 전체적으로 빠른 검색 속도를 보여줍니다. 하지만 LIMIT 방식도 'Rownum 방식 2'와 똑같이 뒷 행으로 갈수록 검색 속도가 느려지는 현상이 나타납니다. 아래는 11~20행을 검색 한경 우와 1,800,000~1,800,010행을 검색한 경우 걸린 속도입니다.

 

그림 24

 

그림 25

 

 

REFERENCE

• madnix.tistory.com/entry/ROWNUM%EC%9D%B4%EB%9E%80-%EB%AD%A3%EC%9D%B8%EA%B0%80
• m.blog.naver.com/wideeyed/221796538283
• 코드로 배우는 스프링 프로젝트(남가람북스)
• sang12.co.kr/36/MYSQL-LIMIT%EB%A5%BC%EC%9D%B4%EC%9A%A9%ED%95%9C-%ED%8E%98%EC%9D%B4%EC%A7%95%28PAGING%29-%EC%BF%BC%EB%A6%AC-%EB%A7%8C%EB%93%A4%EA%B8%B0-with-Mybatis
• aljjabaegi.tistory.com/83
• www.gurubee.net/

 

DATE

• 2020.02.25