8. [SQL기초] 결과 행 개수 제한과 페이징

결과 행수 제한

예제 데이터베이스의 테이블들은 들어있는 데이터 행 수가 몇 개 안되기 때문에 전체 테이블 데이터를 가져와 표시해도 문제가 없습니다. 실제로는 수만 행일 수도 있고, 수 십만 수백만 개의 행이 있는 테이블일 수도 있습니다.

많은 행이 있는 테이블 전체를 쿼리 결과로 가져오게 되면 시간도 오래 걸릴 뿐만 아니라 메모리도 많이 사용하고, 화면에 표시하는데도 시간이 오래 걸리게 됩니다.

수 천행 이상의 쿼리 결과를 웹 브라우저에 웹페이지로 표시를 하면 페이지 로딩 속도가 떨어지는 것을 바로 눈을 확인을 할 수 있습니다.

테이블 데이터를 가져오는 Select 문에는 가져오는 행 수를 제한할 수 있는 절이 제공됩니다.

키워드는 “limit” 이고, 쿼리 결과에서 앞에서부터 몇 개만 가져오도록 제한할 수 있습니다.

기본 쿼리문 구조는 다음과 같습니다. 앞서 배웠던 where 절과 order by 절을 포함해 쿼리문을 작성하는 순서에 주의해야 합니다. Limit 절은 order by 뒤에 와야 합니다. Order by 절이 없으면 where 뒤에 오게 됩니다.

Select 열 이름 from 테이블 이름 where 조건식 order by 열 이름 limit 제한 행 수;

Limit 절을 사용해 SMS 발송 로그를 기록하는 sms 테이블에서 최근 날짜 기준으로 첫 10행만 가져올 수 있습니다.

Select * from sms order by wt desc limit 10;

별도로 정렬을 하지 않으면 테이블에 저장된 순서대로 오래된 행부터 가져오게 됩니다.

결과 시작 행 위치 지정

Limit 절로 가져오는 행 수를 제한하는 것은 쿼리 결과의 처음 행 위치부터 개수만큼입니다. 사용하려는 행들이 결과 중간부터 있을 경우 건너뛸 행 수만큼 추가로 표시를 해야 합니다. 건너뛰는 행 수만큼을 오프셋이라고 합니다.

오프셋 행 수는 가져올 행 수 제한 숫자 표시 뒤에 쉼표로 구분해서 추가로 표시합니다. 문자열 발송 로그에서 최근 날짜 순으로 정렬된 데이터에서 10개를 건너뛰고 그다음 10개를 가져올 수 있습니다.

Select * from sms order by wt desc limit 10, 10;

오프셋 표시는 쉼표를 대신해 “offset” 키워드로 표시를 할 수 있습니다.

Select * from sms order by wt desc limit 10 offset 10;

오프셋 값을 표시하지 않은 limit 절은 오프셋 값이 0인 것과 같습니다. 오프셋 값이 없는 쿼리문은 다음 쿼리문과 동일합니다. 뒤에서 배우겠지만, 페이징을 구현할 때 offset 0을 사용하게 됩니다.

Select * from sms order by wt desc limit 10 offset 0;

Limit 절의 호환성

Limit 절은 ANSI SQL 표준 문법이 아닙니다. 표준은 아니지만 MySQL, PostgreSQL에서 지원됩니다. SQL Server와오라클은 이 방식을 지원하지 않으며 ANSI 표준인 “OFFSET 오프셋 행 수 ROWS FETCH NEXT 제한 행 수 ROWS ONLY”를 지원합니다.

표준 SQL은 오프셋이 있을 때 행 수를 제한해 가져오는 방법과 오프셋이 없을 때의 사용 방법이 약간 다릅니다.

표준 ANSI SQL 결과 행 수 제한 구문

select 열 이름 from 테이블 이름 FETCH FIRST 가져올 행 수 ROWS ONLY;

select 열 이름 from 테이블 이름 OFFSET 오프셋 행 수 ROWS FETCH NEXT 가져올 행 수 ROWS ONLY;

SMS 로그 테이블에서 최근 날짜 기준으로 정렬해서 첫 10행만 가져오는 쿼리문을 표준 SQL로 다음과 같이 작성할 수 있습니다.

select * from 는 order by wt desc FETCH FIRST 10 ROWS ONLY;

앞서 작성했던 limit 절을 사용한 쿼리문처럼 최근 날짜 기준으로 첫 10행을 건너뛰고 그다음 10행을 가져올 수도 있습니다.

select * from 는 order by wt desc OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

가져올 행 수를 제한하거나, 오프셋 행 수로 시장 위치를 건너뛰는 방법은 데이터베이스에 따라서 사용 방법이 조금씩 다릅니다. MySQL은 최신의 표준 SQL 문법을 아직 지원하지 못하기 때문에 limit 절을 사용해야 하므로 두 가지 모두를 알아 둘 필요가 있습니다.

페이징

페이징은 큰 데이터를 일정한 크기의 조각으로 쪼개서 현재 필요한 조각을 가져와 표시, 또는 사용하는 기법을 말합니다. 많은 데이터를 메모리에 한꺼번에 로딩하면 많은 메모리 공간이 필요해지고, 화면에 많은 데이터를 한꺼번에 표시를 하면서 화면 갱신 속도가 느려지게 됩니다.

Limit 절, 또는 fetch 절을 사용하면 데이터를 가져오는 단계에서 표시에 필요한 행만 가져오기 때문에, 가볍고 빠른 데이터 처리가 가능해집니다.

실제로 서버 프로그래밍을 하는 것은 아니므로, 페이지 정보를 가진 변수가 있다고 가정을 하고 쿼리문을 어떻게 작성하는지 알아봅니다.

서버 프로그래밍만 아니라 뒤에서 배우게 될 스토어드 프로시저를 만들 때도 같은 방법으로 변수를 활용해서 페이징 행의 데이터만 가져오는 기능을 구현합니다.

페이지 번호를 가진 변수를 “page”라고 가정하고, 페이지 1개로 가져오는 행 수는 “pagesize”로 하겠습니다. 다음과 같이 현재 페이지의 행들만 가져오는 쿼리문을 작성할 수 있습니다.

Select * from sms order by wt limit pagesize offset (page-1) * pagesize;

가상 쿼리문이므로 이대로는 실제 쿼리문을 실행할 수는 없습니다. “pagesize”가 10이고, 1페이지라면 다음과 같은 쿼리문이 실행됩니다.

Select * from sms order by wt limit 10 offset 0;

다음 페이지로 이동해서 2페이지의 행들을 가져오는 쿼리문은 다음과 같이 됩니다.

Select * from sms order by wt limit 10 offset 10;