데이터 인사이트
데이터 전문가 칼럼
데이터 전문가가 전하는 데이터 노하우
이병국의 개발자를 위한 DB 이야기: 페이징 처리에 대한 이해 (36회)
이병국의 개발자를 위한 DB 이야기: 페이징(36회)
페이징 처리에 대한 이해
|
성공과 실패의 경험을 나누자, 용기와 희망을 나누자 개발업무를 시작으로 IT계에 입문했던 필자가 10년 가까이 DB엔지니어로서 활동하면서 얻은 경험과 지식을 나누고자 한다. DB를 자주 접하는 SW 개발자뿐 아니라, DB 전문가를 꿈꾸는 대학생에서DB 분야에 입문한지 1~2년 된 기입문자가 쉽게 이해할 수 있도록 비유를 통해 쉽게 접근해볼 계획이다. 물론 전문가들이라도 다시 한번 개념을 정립하는 의미에서 필요한 내용이 될 수 있다.전체적으로 DB의 기본 원리와 개념을 이해하고 테이블, 인덱스, 쿼리, 튜닝, 플랜 등 개발자들이 알아야 하는 DB 전분야에 대해 쉽게 이해하도록 설명하겠다. DB 기술서적이나 번역서보다는 조금 더 부드럽게 접근할 계획이다. 그렇다고 흔히 서점에서 만날 수 있는 개발자 위주의 SQL 소개서도 아니다. 이 연재는 시리즈로 나갈 것이다. 연재를 끝까지 읽는 독자라면 준전문가 수준의 DB 원리를 아는 것을 목표로 한다. 이번 연재는 페이징에 관한 내용이다. 페이징이란 한 화면에서 보여주는 데이터의 범위를 결정하는 일련의 방법을 말한다. 우리가 흔히 접하는 일반적인 웹 게시판이나 조회 화면을 생각하면 된다. 조회 대상 데이터가 10만 건이라면 한 화면에서 모두 보여 줄 수는 없다. 한 화면에서 보여 줄 수 있는 범위를 결정하는 것이 페이징이다. 페이징을 처리하는 방법은 많이 있다. 그 중에서 좋은 방법도 있을 것이고 그러지 못한 방법도 있을 것이다. 지금까지의 경험을 따르면, 페이징 구현이 쉬우면 성능이 아쉽고, 성능이 만족되면 페이징 구현이 아쉬운 경우가 많았다. |
? 프로그램 페이징 처리와 서버 페이징 처리
프로그램 소스에서 페이징 처리를 구현한 것을 프로그램 페이징이라 하고, 서버에서 페이징 처리를 구현한 것을 서버 페이징이라 한다. 지금은 웹 환경에서의 개발이 대부분을 이루지만 과거에는 클라이언트/서버 환경에서의 개발이 주를 이루었다. 클라이언트/서버 환경에서는 DB 연결을 유지한 상태에서 결과 데이터를 계속해서 Fetch 할 수 있었다. 하지만 웹이 도입이 되면서 DB 연결을 유지한 상태에서 결과 데이터를 컨트롤할 수 없게 되었다. 따라서 개발자들은 개별적인 SQL 문을 반복 수행하는 방법으로 페이징 처리를 구현하였다. 초기에 구현되었던 페이징 방법 중에는 프로그램 소스를 통한 방법도 있었다. 그 중에서 아래와 같은 프로그램 페이징 구현 방법에는 많은 문제점이 있었다.?
|
i := 1; loop fetch data into record; exit when data not found; if (i > (페이지번호-1) * 페이지크기) and (i < 페이지번호 * 페이지크기 + 1) then print record; end if; i := i + 1; end loop; |
조회 페이지에 해당하는 레코드를 표시하기 위해서 검색된 전체 데이터를 Fetch 하는 비효율적인 방법이다. 불필요한 네트워크 트래픽을 유발하는 이런 방식의 구현은 시스템에 큰 악영향을 미친다. 아래 구문과 같이 약간의 수정을 통하여 개선의 여지가 있으나 전체 성능에는 크게 영향을 미치지 않으므로 무의미하다.
?
|
i := 1; loop fetch data into record; exit when data not found; exit when i > 페이지번호 * 페이지크기; if i > (페이지번호-1) * 페이지크기 then print record; end if; i := i + 1; end loop; |
위의 프로그램 페이징 구문은 앞 페이지 조회보다 뒤 페이지 조회에 더 많은 Fetch가 발생한다. 프로그램 소스에서 페이징을 처리하는 방식은 근본적인 개선의 여지가 거의 없다. 해결 방법은 페이징 처리 로직을 서버 단에 두는 것이다. 서버 단에서 페이징을 구현하고 프로그램 단에서는 실제 화면에 뿌려질 데이터만 Fetch 하는 것이다.
? 전체 범위 페이징 처리와 부분 범위 페이징 처리
프로그램 소스에서 페이징 처리를 구현한 것을 프로그램 페이징 처리라고 하였고, 서버에서 페이징 처리를 구현한 것을 서버 페이징 처리라고 이미 언급하였다. 서버 페이징 처리라고 해서 특별한 것은 아니고 그냥 단순하게 쿼리 페이징 처리라고 생각하면 쉬울 것이다. 즉 서버 단에서 페이징 처리를 구현한다는 의미는 쿼리문 안에서 페이징 처리를 구현한다는 의미와 동일하다.서버 페이징 처리는 크게 전체 범위 페이징 처리와 부분 범위 페이징 처리로 나눌 수 있다. 구체적인 내용을 설명하기 전에 전체 범위와 부분 범위에 대해서 한번 짚고 넘어 가야 할 필요가 있다. 아래 쿼리는 전체 범위 조회와 부분 범위 조회로 구분할 수 있다.
?
|
[전체 범위 조회] SELECT * FROM 고객 |
고객 테이블 전체 범위를 조회하는 쿼리이다. 고객 테이블 전체를 스캔한다.
?
|
[부분 범위 조회] SELECT * FROM 고객 WHERE ROWNUM <= 10 |
고객 테이블 부분 범위를 조회하는 쿼리이다. WHERE 절의 ROWNUM 건수만큼 스캔한다.
?
|
[부분 범위 조회] SELECT * FROM 고객 WHERE ROWNUM < = 10 ORDER BY 고객번호 |
고객 테이블 부분 범위를 조회하는 쿼리이다. WHERE 절의 ROWNUM 건수만큼 스캔 후에 고객번호로 정렬한다. 혹시나 고객번호로 정렬 후에 10건을 가지고 온다고 착각하지 말자. 쿼리문의 수행 순서는 조건절이 가장 먼저 수행되고, 그 다음 ORDER BY 절이다. 같은 조건절이라면 인덱스가 있는 조건절이 먼저 수행된다.
?
|
[전체 범위 조회] SELECT * FROM 고객 ORDER BY 고객번호 |
고객 테이블 전체 범위를 조회하는 쿼리이다. 고객 테이블 전체를 스캔 후에 고객번호로 정렬한다.
?
|
[부분 범위 조회 혹은 전체 범위 조회] SELECT * FROM 고객 WHERE 고객명 = ‘홍길동’ ORDER BY 고객번호 |
고객 테이블 부분 범위를 조회하거나 전체 범위를 조회하는 쿼리이다. 만약 조건절로 사용하는 고객명 컬럼에 인덱스가 있다면 부분 범위 조회를 한다. 반면에 고객명 컬럼에 인덱스가 없다면 전체 범위 조회를 한다.
?
|
[부분 범위 조회 혹은 전체 범위 조회] SELECT * FROM ( SELECT * FROM 고객 ORDER BY 고객번호 ) WHERE ROWNUM <= 10 |
고객 테이블 부분 범위를 조회하거나 전체 범위를 조회하는 쿼리이다. 만약 ORDER BY 절로 사용하는 고객번호 컬럼에 인덱스가 있다면 부분 범위 조회를 한다. 반면에 고객번호 컬럼에 인덱스가 없다면 전체 범위 조회를보여줄 수 있는 한 페이지 분량의 데이터를 조회하는 것을 말한다. 전체 범위 페이징 처리는 많은 데이터를 조회하지만 극히 일부분만 실제로 화면에 표시하는 데에 사용하므로 아주 비효율적인 페이징 처리 방법이다. 한 페이지 분량의 데이터만 필요함에도 화면에 보이지도 않을 전체 데이터를 불필요하게 조회하므로 성능에 나쁜 영향을 미친다. 아래 쿼리문은 전체 범위 페이징 처리에 대한 예시이다.
?
|
SELECT B.* FROM ( SELECT A.*, ROWNUM AS NUM FROM ( SELECT * FROM 고객 ORDER BY 등록일시 DESC ) A ) B WHERE NUM BETWEEN ? AND ? |
부분 범위 페이징 처리는 실제로 화면에 표시 하는 데에 필요한 분량만큼의 데이터를 조회해서 처리하므로 아주 효율적인 페이징 처리 방법이다. 화면에 보여줄 필요한 분량만큼만 조회해야 하므로 인덱스와 밀접한 관계가 있고 인덱스를 적절히 잘 활용하여야 한다. 아래 쿼리문은 부분 범위 페이징 처리에 대한 예시이다(단, 조건절에 있는 고객명 컬럼은 인덱스 있음).
?
|
SELECT B.* FROM ( SELECT A.*, ROWNUM AS NUM FROM ( SELECT * FROM 고객 WHERE 고객명 = ? ORDER BY 등록일시 DESC ) A WHERE ROWNUM < = ? // (페이지번호 * 페이지크기) ) B WHERE NUM >= ? // ((페이지번호 - 1) * 페이지크기 + 1) |
비록 인덱스를 활용하여 부분 범위 페이징 처리를 했어도 뒤 페이지로 갈수록 부하가 커지는 것은 불가피하다. 조회할 데이터의 범위가 크고 수행 빈도가 높다면 더욱 그러하다.
?
? MYSQL의 LIMIT와 ORACLE의 ROW_NUMBER
MYSQL은 페이징을 더 쉽게 할 수 있게 LIMIT를 제공한다. 아래 쿼리문은 21번째 레코드부터 30번째 레코드까지 조회하는 내용이다.?
|
SELECT * FROM 고객 WHERE 고객명 = ? ORDER BY 등록일시 DESC LIMIT 21 AND 10 |
ORACLE은 ROW_NUMBER를 활용하면 MYSQL의 LIMIT와 같은 기능을 구현할 수 있지만, MYSQL 만큼 편리하지는 않다. 아래 쿼리문은 21번째 레코드부터 30번째 레코드까지 조회하는 내용이다.
?
|
SELECT A.* FROM ( SELECT *, ROW_NUMBER() OVER(ORDER BY 등록일시 DESC) AS NUM FROM 고객 WHERE 고객명 = ? ) A WHERE NUM BETWEEN 21 AND 30 |
? 최고의 페이징 처리와 최적의 페이징 처리
지금까지 페이징 처리와 관련하여 여러 내용들을 확인하였다. 대부분의 개발자들은 페이징과 관련하여 한번쯤은 고민해 봤을 것이다. 필자도 고민을 많이 했으나 최고의 성능을 내는 가장 좋은 페이징 처리 방법은 존재하지 않음을 알았다. 단지 여러 물리적 상황과 프로젝트 여건 등 업무적 협의를 통해 성능 이슈가 발생하지 않도록 가장 적합한 페이징 방법을 찾아야 한다.인덱스를 활용한 부분 범위 페이징 처리뿐만 아니라 고객과의 업무 협의를 통한 화면 인터페이스 변경만으로도 충분한 효과를 볼 수 도 있다. 최고의 페이징 처리 방법을 찾을 것이 아니라 상황에 맞는 최적의 페이징 처리 구현을 목표로 삼아야 한다.
다음 연재의 내용은 쿼리문의 작성 규칙에 관한 것이다. 수많은 개발자들이 각자의 개성으로 쿼리문을 작성한다면 많은 문제점을 초래한다. 이러한 문제점을 파악하고 표준화된 작성 규칙을 실천한다면 유지보수 비용 절감 및 서버 성능 향상을 이룰 수 있다. 또한 그 동안 잠시 중단한 ‘원리를 이해하고 논리로 풀어가는, 쉬어가는 DB 문제’를 다시 시작할 계획이다. (다음 회에 계속)
?
출처 : 한국데이터진흥원
제공 : 데이터 전문가 지식포털 데이터온에어(dataonair.or.kr)