MySQL/MariaDB, 페이지네이션 적용시 슬로우 쿼리 현상 해결하기

개요

• 최근 슬로우 퀴리 이슈를 해결하면서 비약적인 속도 향상을 경험했는데, 사실 지금도 완전히 이해가 되지 않는 기묘한 부분이라 먼저 정리해둔다.

확인된 이슈

• 문제의 쿼리는 아래 모양새의 전형적인 페이지네이션 쿼리이다. (실제 운영 쿼리는 복잡한 여러 JOIN 절이 추가되어 있다.)

SELECT *
  FROM foo
 WHERE ...
 ORDER BY created_at, id
 LIMIT 500
 OFFSET 0;

• 신기한 점은 대부분의 쿼리 조건에서는 전혀 문제가 없었지만 특정 조건에서만 ORDER BY, LIMIT, OFFSET의 조합이 30~50초의 슬로우 쿼리로 모니터링되었다.
• 처음에는 WHERE 조건의 대상과 순서를 적절히 재배치하여 효과를 보았지만, 모니터링 결과 이 것이 단순한 인덱스 사용 효율의 문제가 아니라는 것을 깨달았다.

해결책: INNER JOIN

• 원인은 Late Row Lookups 이슈였다. [관련 링크] 해결책은 쿼리 대상 테이블에 대해 자기 자신을 다시 INNER JOIN하는 것이다. 사용 예는 아래와 같다.

SELECT t2.*
  FROM foo t2
 INNER JOIN (

     SELECT *
       FROM foo
      WHERE ...

 ) AS t1 ON (t2.id = t1.id)
 ORDER BY t2.created_at, t2.id
 LIMIT 500
 OFFSET 0;

• ORDER BY의 대상 테이블은 반드시 바깥에 위치한 테이블 앨리어스를 입력해야 한다. 안에 위치한 테이블의 앨리어스를 입력하면 슬로우 쿼리가 발생한다.
• LIMIT, OFFSET 또한 바깥에서 수행하면 슬로우 쿼리 현상이 완전히 사라진다.

참고 글

• MySQL ORDER BY / LIMIT performance: late row lookups
• Stackoverflow - Why is MySQL slow when using LIMIT in my query?