Pagination을 구현하며 100만 건이 이하의 자료에 대해서는 기본 Index 생성 외에 최적화를 신경 쓰지 않았습니다. 하지만 이번 프로젝트에서 약 1000만 건이 넘는 Row를 가진 Table을 검색하며 Paging 검색 쿼리의 최적화가 필요하다는 걸 알게 되었습니다.

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

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

위 방법 중 1. 검색 조건에 대한 index 생성은 기본적으로 개발자 & DBA의 도움을 얻어 쉽게 작성을 합니다. 하지만 2. sql 최적화 (LIMIT -> JOIN 변경)은 미처 생각하지 못한 부분입니다. 자세한 내용을 살펴보니 MySQL 동작 원리를 다시 공부하게 되는 좋은 기회라서 글로 정리합니다.

SQL 최적화 (LIMIT -> JOIN) 방법은?

Paging을 할 때 LIMIT, OFFSET을 이용하여 구현하는 경우가 많습니다. 하지만 이 때 WHERE 조건이 색인이 걸린 부분이라 당연하게 최적화가 되었다고 생각하지만 중요한 부분을 놓치고 있었습니다.

바로 Row lookup으로 인한 성능 저하입니다. Row lookup은 색인이 가르키는 Row를 접근하여 가져오는 행위입니다. 즉 LIMIT, OFFSET을 통해 페이지를 하는 동안 지나치는 Row들을 바라보며(lookup) 성능이 느려지게 됩니다. 자세한 내용은 먼저 성능 결과를 확인한 뒤 파악하겠습니다.

검색 성능을 확인하기 위해 v1, 2, 3 세 가지 쿼리를 300만 건 가량의 Row가 있는 Table에 수행해봅니다. 단순히 빨라지는 것이 아니라 다음 그래프가 같이 페이지에 따라 급격한 속도 차이가 발생합니다. 아래 그래프는 [참고1]에서 측정한 결과로 경향을 확인하기에 유용하여 첨부합니다.

Limit Join 성능비교 차트

위 그래프는 명시적으로 페이지 숫자가 커질수록 Limit의 경우 속도가 급격히 느려지지만 Join을 이용한 경우에는 평균적인 속도를 유지합니다. 실험을 위해 예제로 작성한 쿼리입니다.

  1. v1 처음 작성한 쿼리

    SELECT id, xValue, yValue, zValue, createdAt
    FROM Position
    LIMIT 2000000, 1000
    
  2. v2 FORCE INDEX 사용

    SELECT id, xValue, yValue, zValue, createdAt
    FROM Position
    LIMIT 2000000, 1000 FORCE INDEX (PRIMARY)
    
  3. 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
    

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

이러한 속도 차이는 JOIN을 사용할 때 Inner SELECT 구문에서 Position 테이블의 id index 내에서만 WHERE, ORDER를 처리하여 row lookup을 최소화하기 때문입니다. 즉 lookup이 이뤄지지 않도록 색인 내에서만 모든 검색을 하고 실제 데이터를 가져올 때 Join을 하는 방법입니다. 이를 Late row lookup이라고 합니다.

Row lookup와 Late row lookup

Row lookup이란 Index record와 Table record 사이에서 발생하는 Fetching을 의미합니다.

쉽게 설명하면 색인에서 연결된 테이블 레코드로 관계형 자료들의 값을 가져오는 행위입니다. 즉, 이를 위해서 테이블 레코드로 접근하여 해당 자료를 읽는 시간이 필요합니다. 이런 소요시간을 최소화/지연시키는 방법을 Late row lookup입니다. [참고2]

결국 색인을 적극적으로 활용하여 late row lookup을 최대화하는 것이 SELECT 성능을 큰 향상을 줍니다. 이러한 방법으로 색인의 값만을 이용하여 INNER SELECT를 동작한다음 실제 테이블 레코드와 JOIN을 하는 방법이 위에서 v3 예제로 작성된 내용입니다.

느낌

DB에서 인덱스를 잘 사용해야된다는 사실은 많이 듣고 느낍니다. 그 ‘잘’ 사용하기 위해서 동작원리(Row lookup)의 이해가 반드시 필요하다고 생각됩니다. 관계형 모델 그리고 깊게 MySQL 구현체 자체에 대한 이해는 개발자로서 DB 최적화 방안을 제안할 수 있는 최소한의 능력이라고 생각되었습니다.

프로그래밍에서 아직도 ‘잘’이라는 말이 너무 어렵고 공부할게 많네요. 그래서 생각만큼 재미있습니다 :)