MySQL LIMIT를 사용하여 페이징을 처리하는 방법

MySQL 데이터베이스를 사용하다 보면, 많은 양의 데이터를 처리하는 것이 까다롭다는 생각이 듭니다. 하지만 구글, 링크드인, 아마존, 네플릭스, 트위터 등에서는 잘 사용하고 있습니다. 이유는 하나입니다. 그들은 최적화하는 방법을 알기 때문이라 할 수 있죠. 

 

특히 많은 데이터를 한꺼번에 보여주기보다는 페이징 처리 하는 노하우가 그런 경우입니다. 이번 글에서는 페이징에 MySQL의 LIMIT 절을 효과적으로 사용하여 수천만 개의 행에서도 쿼리가 효율적으로 실행되도록 하는 방법을 알려 들리겠습니다. 

 

MySQL 페이징 처리

 

기본 LIMIT의 문제점

MySQL에는 limit와 offset이라는 기능(?)이 있습니다. 데이터가 많은 테이블에서 간단한 LIMIT 절을 사용하여 특정 데이터 페이지를 가져오도록 하는 옵션입니다. 써보신 분들은 아시겠지만 이게 처음에는 빠르다가 거듭할수록 속도가 느려지는 체감을 하게 됩니다.

 

예를 들어 'emp' 테이블에 1억 개의 행이 있고 행 99,999,990에서 시작하여 10개의 행을 가져오려는 경우 쿼리는 다음과 같습니다.

SELECT * FROM emp LIMIT 99999990, 10 ;

MySQL이 처음 99,999,990개 행을 건너뛰고 다음 10개 행을 반환하는 것처럼 보일 수도 있지만 작동 방식은 그렇지 않습니다. MySQL은 최대 99,999,990까지의 모든 행을 읽은 다음 가져온 것을 모두 삭제하고 10건만 보여주기 때문에 속도가 매우 느린 이유입니다.

 

해결 방법 1: BETWEEN에 PK 사용

속도를 높이는 한 가지 방법은 기본 키에 대해 범위 검색(range scan)을 사용하는 것입니다. 테이블의 기본 키가 'emp_no'인 경우 다음과 같은 쿼리를 사용할 수 있습니다. MySQL의 경우 기본 키(PK)가 정렬하여 데이터를 보관하기 때문에 인덱스틀 이용해 빠르게 찾아갈 수 있습니다. 

SELECT * FROM emp WHERE emp_no BETWEEN 10001 AND 10010 ;

특히, 기본 키 값이 연속적이고 간격이 없는 경우에 잘 작동합니다. 그러나 중간에 누락된 값이 있으면 올바른 row 수를 얻지 못할 수도 있습니다.

해결 방법 2: LIMIT와 범위 검색 결합

또 다른 방법은 서브쿼리(sub query)를 사용하여 시작점을 찾는 것입니다.

SELECT * FROM emp WHERE emp_no > (SELECT emp_no                                  FROM emp                                  ORDER BY emp_no                                  LIMIT 99999990, 1) ORDER BY emp_no LIMIT 10 ;

이 접근 방식은 'emp' 테이블을 두번 읽어야 한다는 점에서 기분이 나쁘지만, 그래도 전체 테이블을 스캔하지 않습니다. 서브쿼리 조건만큼 스캔하게 됩니다. 

 

해결 방법 3: 효율적인 페이징을 위해 세그먼트 사용

매우 큰 데이터 세트의 경우 데이터를 세그먼트로 나눌 수 있습니다. 마치 파티션의 개념처럼 검색 구간을 나누는 것입니다. 이렇게 하면 검색(scan) 범위를 제한하고 쿼리 속도를 높일 수 있습니다.

1단계: 검색 범위 계산

34,245개의 emp의 직원정보가 있고 11페이지를 보려고 하며 각 페이지에 10개의 게시물이 표시된다고 가정해 보겠습니다. 일단 게시물의 특성상 최근 row부터 보여준다고 생각할 때 아래와 같이 검색 범위를 계산할 수 있습니다.

Scan Range = Total Posts - (Page - 1) * Page Size                      = 34245 - (11 - 1) * 10                      = 34245 - 100                      = 34145

 

2단계: 세그먼트 결정

세그먼트 정보를 저장할 테이블을 생성합니다.

CREATE TABLE emp_segment (     no INT AUTO_INCREMENT PRIMARY KEY, -- 세그먼트 번호     count     INT NOT NULL -- 해당 세그먼트의 값을 갖는 게시물의 갯수 ) ;

각 세그먼트에 10,000개의 데이터를 보관한다고 하면 각 세그먼트 수는 다음 표와 같다고 할 수 있습니다. 단, 중간에 삭제되는 row는 일단 배제했습니다. 

no	count
1	10000
2	10000
3	10000
4	4245

내가 11 페이지를 보려고 하면 앞에서 계산한 것처럼 조회범위(scan range)는 34,145가 됩니다. 이 값을 세그먼트와 비교해 봅니다. 

계산식	결과
10000 >= 34145	False
10000 + 10000 = 20000 >= 34145	False
10000 + 10000 +10000 = 30000 >= 34145	False
10000 + 10000 + 10000 + 4245 = 34245 >= 34145	True

 

위 계산식을 통해 세그먼트 4에 내가 찾고자 하는 페이지가 있다는 것을 알 수 있습니다. 

3단계: 세그먼트 내에서 LIMIT 계산

이제 세그먼트 내 범위를 계산합니다.

LIMIT = Segment Count - (Previous Segment Sum - Page Total) - Page Size       = 4145 - (4145  - 11*10) - 10       = 4145 - 4035 - 10       = 100

현재 Segment Count는 4145, 최근부터 보여주기 위해 desc 하므로 이전이 현재 Segment와 동일합니다. 그래서 Previous Segment Sum 4145, Page 수는 11page를 10개씩 하니까 110입니다. 결론적으로 LIMIT의 결과는 100이 나옵니다. 

SELECT * FROM emp WHERE segment = 4 ORDER BY emp_no DESC LIMIT 100, 10 ;

 

위 SQL을 통해 11 페이지에 대한 게시물을 가져올 수 있습니다. 단 고려해야 할 부분은 row가 삭제하거나 하게 되면 세그먼트와 세그먼트끼리 걸치는 경우가 있기 때문에 해당 부분을 계산해 segment를 in조건으로 넣어야 합니다. 그리고 번거롭지만 게시물 수의 변동이 있을 때마다 세그먼트 테이블의 count값도 같이 변경해줘야 합니다.