mysql pagination의 성능을 향상시키자

mysql로 pagination을 구현하기 위해 사용하는 offset, limit의 한계를 알아봐요

참고자료: https://www.iheavy.com/2013/06/19/3-ways-to-optimize-for-paging-in-mysql/

일반적으로 mysql을 사용하여 pagination을 구현하게 되면 offset, limit를 사용하게 됩니다. 처음에는 괜찮지만 굉장히 많은 데이터를 페이징하게 되면 데이터베이스 성능에 큰 결함으로 작용하게 됩니다.

offset과 limit는 offset + limit만큼 데이터를 불러온 후 불러온 결과에서 limit만큼만 보여주도록 되어 있기때문에 데이터베이스 자체에 큰 부하가 걸립니다.

이러한 문제를 해결하기 위한 방법을 생각해보았습니다. 아래의 쿼리를 기준으로 성능을 개선해보겠습니다.

SELECT * FROM user ORDER BY id desc LIMIT 4000000, 10000;
...
10000 rows in set (3.35 sec)

위 쿼리는 일반적으로 페이징하는 쿼리입니다. 4,000,000번째 데이터에서 10,000개를 얻고자하는 쿼리입니다. 이 쿼리는 4,000,000개를 가저온 후 마지막 10,000개 를 리턴하게 됩니다.

대안 1. pk 와 같은 검색쿼리로 검색결과 한정시키기

select * from user where id > 4000000 limit 10000;
...
10000 rows in set (0.04 sec)

위 쿼리로 바꾸게 되면 훨씬 시간이 단축되어 질거에요. 이유는 user table의 id가 10,000 이상 데이터 중 0번째에서 100번째 데이터만 가저오라는 것이기 때문에 데이터베이스에서도 가저올 데이터의 수가 줄어 들게 됩니다. 이 방법은 제가 현업에서 많은 데이터를 쿼리하게 될때 자주 애용하는 방법입니다.

대안 2. JOIN을 사용한 성능개선

SELECT * FROM user INNER JOIN (SELECT id FROM user ORDER BY id desc LIMIT 4000000, 10000) as u USING(id);
...
10000 rows in set (1.24 sec)

위쿼리는 INNER JOIN을 사용한 방법으로 1.24초 소요되었습니다. 처음보단 개선되었지만 대안1보단 기대에 못미치는 결과입니다.

대안 3. 데이터에 페이지 혹은 위치에 대한 정보를 담는다.

개인적으로 추천하지 않습니다.

테이블에 데이터의 페이지 번호 혹은 위치번호를 담는것인데 이 방법을 좋지 않다고 판단됩니다.

마무리. 많은 양의 데이터를 페이징처리할 때 많은 고민을 하게 됩니다. 지금까지 개발하며 속소를 최적화 시키기 위해 많은 고민을 해왔지만 대안1이 가장 쉬운 접근법일 것같습니다.