Mysql 에서 Limit/Offset 을 사용하면 Order By 느려지는 이유는 무엇일까?

공부용으로 직역해놓은 정보입니다. 직역이 틀린 부분이 있다면 지적 감사히 받겠습니다.

 

Mysql에서 빠른 페이지네이션 - Limit와 Offset을 사용하면 Order By가 느린 이유는?

Limit와 Offset이 포함된 쿼리는 페이지네이션이 필요한 애플리케이션에서 일반적으로 쓰이며 경우에 따라서는 한동안 잘 작동한다.

하지만 많은 경우에 Offset이 높은 값을 가지면 느리며 고통을 가져온다.

 

OFFSET은 왜 느릴까?

많은 케이스에서 낮은 오프셋 쿼리는 느리지 않다. 문제는 높은 오프셋 값으로 시작된다.

쿼리의 리미트 절: "LIMIT 50000, 20" 을 사용한다면, 그것은 실제로 50,020행을 요청하고 처음 50,000 행을 삭제 요청을 한다. 이 작업은 높은 비용과 충격적으로 느린 응답의 시간을 줄 수 있다.

 

너는 스스로 "나의 어플리케이션에서 50,000 페이지를 스킵하는 일이 있을까?라고 물을 것이다.

몇 가지 가능한 케이스에 대해 알아보자 :

- 너가 좋아하는 검색 엔진 (Google / Bing / Yahoo / DuckDuckGo /  어떤것이든) 이 전자 상거래 웹 사이트를 인덱싱 하려고 한다. 웹사이트 안에  100,000 페이지를 가지고 있을 것이다. 검색 봇이 마지막 50,000페이지를 인덱싱하기 위해 가져오려고 할 때 너의 애플리케이션은 어떻게 반응할까? 얼마나 자주 발생할까?

- 많은 웹 어플리케이션에서 유저는 다음 페이지로 뿐만 아니라 마지막 페이지로 넘어갈 수 있다. 유저는 2페이지 방문 후 50,000페이지로 넘어가려고 노력하는 경우에는 어떻게 될까?

- 만약 유저가 구글 검색 결과로부터 2만 페이지에 도달하고, 거기에 있는 무언가를 좋아하여, 다른 1000명의 친구들이 읽을 수 있도록 페이스북에 게시한다면 어떻게 될까?

 

우리는 OFFSET 증가함에 따라 성능이 악화되는지를 나타내기 위해 다음 쿼리를 사용하여 OFFET 값 테스트를 진행했다.

쿼리는 150,000레코드가 있는 유저 수행 events(분석 테이블)를 저장하는 테이블에서 실행됬다.

데이터는 실제 유저의 정보이고 자동으로 생성시키지 않았다.

 

 

 

 

 

 

 

 

 

느린 OFFSET 쿼리를 최적화하는 방법은?

느린 OFFSET 쿼리를 최적화하는 방법은 페이지네이션 안에 허용된 페이지 수를 제한하거나 OFFSET을 사용하지 않을 수 있다.

OFFSET을 사용하는 좋은 대책으로는 Lukas Eder와 Markus Win이 블로그에서 강력하게 추천하는 Seek Method가 있을 것이다.

간단히 말해서, seek method는 각 행을 식별하는 고유한 열 또는 열 집합을 찾는 것이다.

OFFSET 절을 대신 사용하려면, 이 값을 가져온 마지막 행의 위치를 나타내는 북마크를 사용하고 WHERE절의 이 위치에서 시작하여 다음 행 집합을 쿼리할 수 있다.

 

예를 들어, 이전에 실행한 쿼리를 살펴보면, Offset 999,999의 마지막 event id가 '111866'이라고 가정하면 쿼리는 다음과 같다.

SELECT    *
FROM
    events
WHERE
    (date,id) > ('2010-07-12T10:29:47-07:00',111866)
        AND event = 'editstart'
ORDER BY date, id
LIMIT 10

 

쿼리를 작성하는 또 다른 방법 :

SELECT    *
FROM
    events
WHERE
    date>='2010-07-12T10:29:47-07:00' and not (date='2010-07-12T10:29:47-07:00' and id < 111866)
        AND event = 'editstart'
ORDER BY date, id
LIMIT 10

 

페이지 같에 순서가 항상 동일하게 유지되도록 고유한 열별로 Order해야 한다. 그렇지 않으면 예상하지못한 동작이 발생할 수 있다.

 

이것은 두가지의 메소드의 성능을 비교했다. 여기서 흥미로운 관측은 Seek Method의 성능이 더 우수할 뿐만 아니라, 테이블에 아무리 멀리 페이지네이트를 하여도 더 안정적이라는 것이다.

 

 

 

 

 

 

 

 

 

 

발생 가능한 위험 / 도전

- 마지막으로 가져온 행을 저장하여 이 method를 작동시키려면 애플리케이션에서 일부 코드를 변경해야 한다. (해당 오프셋 값을 조정할 필요는 없다.)

- 그것에는 고유한 seek 열/열 집합에 인덱스가 있어야 한다.

-  고유한 열 집합의 각 열에는 NOT NULL 제약이 있어야 한다. 그렇지않으면 예상치 못한 작동이 발생할 수 있다.

- 유저가 페이지를 건너뛸때 먼저 해당 페이지의 위치를 적절히 가져와야 한다.

40,000 페이지를 건너뛰길 싶다고 가정 : 

SELECT date, id 
FROM events 
ORDER BY date, id 
LIMIT 1 OFFSET 39999;

이 쿼리는 커버링 인덱스를 사용하기 때문에 매우 빠를 것이다.

 

 

 

 

 

 

 

 

 

 

결론

우리는 MySql의 페이징 능력을 시행할때 OFFSET 능력을 사용하는걸 추천하지 않는다. 데이터가 성장하게 된때 성능 이슈가 발견되기 시작할 것이다. 대신에 Seek Method를 사용하는걸 고려해야 한다.

 

 

 

 

 

 

 

본문 링크 클릭 !!

Why Order By With Limit and Offset is Slow - Faster Pagination in Mysql