MySQL 페이징 성능 개선 및 LIMIT 최적화

데이터 조회를 하면서 슬로우쿼리가 발생하여 확인해보니 의문점이 들었다.
LIMIT 을 적용한 쿼리와 적용하지 않은 쿼리 속도차이가 존재한다는 것..

당연히 LIMIT을 적용한 쿼리 속도가 빠를 줄 알았지만 그게 아니었다.

 

페이징 쿼리 예시

SELECT * 
  FROM member
 WHERE gender = 'M'
 ORDER BY memberIndex DESC 
 LIMIT 1, 10

 

LIMIT 사용 시 OFFSET 번호가 뒤로 갈수록 느려진다는 점이다.

그 이유는 뒷 페이지를 읽더라도 앞에서 읽었던 행을 버리지만 그 전에 다시 읽기 때문이다.

즉  LIMIT 10000, 10   만건 데이터 중 10개를 불러온다고 가정하면 10010 개를 불러와서 

필요한 부분은 제외하고 버리는 방식으로 동작하기 때문이다. 

그래서 데이터가 많을 수록 페이징 처리에 많은 시간이 걸리는 이유이기도 하며 데이터 조회에 제한을 두는 경우도 종종 볼 수 있다.

 

1. LIMIT 최적화 방안 [NoOffset 방식, 인덱스이용]

- 조회 시작 부분을 조건절에 인덱스를 이용하여 해당 페이지만 읽도록 하는 방식

SELECT *
  FROM member
 WHERE gender = 'M' AND memberIndex < 마지막 조회 ID
 ORDER BY memberIndex DESC
 LIMIT 10

 페이지 직전 조회 결과의 마지막 Id를 조건으로 처리하여 해당 인덱스 위치를 탐색 후 필요한 데이터 10개만큼 가져온다.

 즉 전체 데이터가 아닌 필요한 데이터만 불러오기 때문에 처음 페이지를 읽은 것과 동일한 성능을 가질 수 있다. 

 

2. LIMIT 최적화 방안 [커버링 인덱스]

- 실제 레코드에 접근할 필요 없이 인덱스의 컬럼만으로 SELECT 쿼리의 결과를 만듦

   즉, SELECT, WHERE, GROUP BY 등에서 사용되는 모든 컬럼이 인덱스인 컬럼

SELECT * 
  FROM member AS M
  JOIN (
         SELECT memberIdx, memberId, gender
           FROM member
          WHERE gender = 'M'
          ORDER BY memberIdx DESC
          LIMIT 10000, 20
       ) AS TEMP 
    ON TEMP.memberIdx = M.memberIdx

JOIN 절에 있는 쿼리가 커버링 인덱스가 사용된 부분이다.

빠르게 처리 되는 이유는 

구성된 인덱스가 생성되어 있어 굳이 데이터 레코드를 읽지 않아도 처리가 가능하기 때문이다.

즉 인덱스 값들이 메모리에 이미 있으므로 인덱스 검색으로 빠르게 처리할 수 있다.

 

위 두가지 개선 방안으로 LIMIT 및 페이징을 개선할 수 있다.

 

참고 블로그

https://velog.io/@ddongh1122/MySQL-%ED%8E%98%EC%9D%B4%EC%A7%95-%EC%84%B1%EB%8A%A5-%EA%B0%9C%EC%84%A0

[MySQL] 페이징 성능 개선