Pagination을 구현하며 100만 건이 이하의 자료에 대해서는 기본 index 생성 외에 최적화를 신경 쓰지 않았다. 하지만 이번 프로젝트에서 약 1000만 건이 넘는 Row를 가진 Table을 검색하며 최적화가 필요하였다.

Pagination 최적화는 다음의 방법이 있다.

  1. 검색 조건에 대한 index 생성
  2. sql 최적화 (LIMIT -> JOIN 변경)

위 방법 중 sql 최적 (LIMIT -> JOIN 변경)은 미처 생각하지 못 했다. 자세히 내용을 살펴보니 MySQL 동작 원리를 다시 공부하게 되는 좋은 기회였다.

LIMIT 보다는 JOIN을 사용

Table의 Id만 검색하여 나온 결과를 JOIN으로 받아서 처리하는 아이디어이다. 간단하게 생각하면 LIMIT, OFFSET을 이용한 방법과 차이가 없어 보이지만 실제 검색 시간의 차이는 매우 크다.

v1, 2, 3 세 가지 쿼리를 300만 건 가량의 Row가 있는 Table에 수행해보면 성능을 체감 가능하다. 단순히 빨라지는 것이 아니라 다음 그래프가 같이 급격히 속도 차이가 난다. 다음의 그래프는 참고[1]에 있는 그래프로 성능의 경향을 확인하기에 유용하여 첨부한다.

Select_Page_By_Offet_Performance

위 그래프에서 보듯이 Paging 숫자가 커질수록 limit의 경우 속도가 급격히 느려진다. 하지만 join을 이용한 경우에는 평균적인 속도를 유지한다.

간단하게 예제로 작성한 쿼리는 다음과 같다.

  1. v1 처음 작성한 쿼리
    SELECT id, xValue, yValue, zValue, createdAt
    FROM Position
    LIMIT 2000000, 1000
  1. v2 FORCE INDEX 사용
    SELECT id, xValue, yValue, zValue, createdAt
    FROM Position
    LIMIT 2000000, 1000 FORCE INDEX (PRIMARY)
  1. v3 JOIN 사용
    SELECT id, xValue, yValue, zValue, createdAt
    FROM (
        SELECT id
        FROM Position
        LIMIT 2000000, 1000
    ) q
    JOIN Position p
    ON p.id = q.id
    FROM Position

1번이 처음 작성한 쿼리, 2번은 index를 강제로 Primary로 사용하라고 정의한 쿼리, 3번은 Join을 이용한 쿼리이다. 1,2번의 경우 제한(row 수, where 조건 등)적인 경우에 유용하고 그 외 Pagination을 제공할 때는 3번이 매우 유용했다.

이러한 속도 차이는 index 내에서 WHERE, ORDER를 처리하여 row lookup을 최소화하기 때문이다. 이를 late row lookup이라고 한다.

late row lookup

먼저 row lookup이란 index record와 table record 사이에서 발생하는 fetching을 의미한다. 쉽게 indexd record가 table record의 컬럼들을 가져오는 행위라고 이해했다. 이러한 행위를 최소화/지연시키는 방법이 바로 late row lookup이다.

결국 index를 적극적으로 활용하여 late row lookup을 최대화하는 것이 SELECT 성능을 큰 향상을 준다.


index를 잘 사용해야된다는 것은 이미 알고 있었다. 하지만 알면서도 막상 구현 시에 바로 적용하기엔 항상 어렵다. 그리고 깊게 MySQL 혹은 데이터베이스에 대해 공부하고 고민한 적이 없어서 지금부터라도 이렇게 기록한다.

프로그램에서 아직도 '잘'이라는 말이 너무 어렵고 공부할게 많다.
역시 프로그래밍은 생각만큼 재미있다 :)

참고자료