제목은 MySQL 페이징 쿼리 성능 개선으로 하겠습니다

신현철·2023년 5월 30일

근데 이제 Index Hint를 곁들이고
SQL_CALC_FOUND_ROWS도 곁들이고,
FOUND_ROWS()도 곁들인,,, 🙋‍♂️

👉🏻 인트로

싸피에서 진행한 1학기 최종 프로젝트에서 일주일이라는 매우 짧은 시간이 주어졌다.
짧은 기간에 풀스택 개발을 하려하니 좋은 설계나 테스트 코드, 리팩토링 등의 디테일함은 챙기지 못했다.
그러나 성능적인 개선은 한 가지 남기고 프로젝트를 마무리하고 싶었고,
그래서 그 후기를 남기려한다. 😋

📗 문제 상황

📌 Input & Output

요청 : 여러 옵션(select form) + 검색 키워드(input textarea) + 페이지 번호로 검색을 한다.
응답 : DB의 특정 테이블에서 해당 요청에 맞는 조건(where)으로 select를 하고 pk를 기준으로 오름차순 정렬한 결과를 페이지네이션으로 응답해주는 상황. 따라서 response body의 필드에는 페이징 된 검색 결과 배열과 totalCount로 페이징한 결과말고 전체 결과의 수도 리턴해야한다.

📌 기술 스택

Vuejs 2.x
spring-Boot 2.7.11
mybatis-spring-boot 2.3.0
spring-boot-starter-data-jdbc
mysql 8.0.32
이하 생략

📙 기존 코드

해당 api 이전 DB 쿼리는 다음과 같았다.

# 페이징한 검색 결과
SELECT *
FROM `타겟 테이블`
WHERE `검색 조건`
LIMIT (페이지 번호),(페이지 크기)

# LIMIT 걸지 않은 전체 레코드 수
SELECT COUNT(*)
FROM `타겟 테이블`
WHERE `검색 조건`

위 두 쿼리를 하나의 DTO로 묶어서 response body로 보내주고 있는 구조였다.

그런데 이런 쿼리는 두 쿼리 간 정합성도 보장되지 못하고 쿼리도 두 번 실행하니까 비효율적일 것이라고 생각하고, 개선 방법을 열심히 검색해보았다.

📘 `SQL_CALC_FOUND_ROW` 그리고 `FOUND_ROWS()`

A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To obtain this row count, include an SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward:
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
    -> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

mysql 페이지네이션 count 최적화 따위의 키워드 등으로 검색한 결과,
MySQL 8.0 공식 문서에서 위와 같은 내용을 찾을 수 있었다.
예시의 상황도 나와 거의 동일한 경우였다.

페이징한 결과를 가져올 때, SQL_CALC_FOUND_ROWS 옵션을 넣어서 SELECT하고,
이 후 쿼리에서 FOUND_ROWS()라는 임시변수에 담긴 값을 다시 SELECT해서 가져오면 된다.

SQL_CALC_FOUND_ROWS 옵션은 쿼리의 결과를 임시변수에 담는 Index Hint 중 하나이다.

Index Hint는 옵티마이저가 쿼리 처리 시 어떻게 index들을 선택할지 정보를 제시하는 방법이다.

즉 옵티마이저가 쿼리 레코드 수를 저장하도록 만드는 것이 SQL_CALC_FOUND_ROWS의 기능이라고 할 수 있겠다.

만약 SQL_CALC_FOUND_ROWS이 없는 쿼리라면, 조건절에 해당하는 레코드들을 LIMIT에 해당하는 수만큼한 스캔한다. 그러나 해당 힌트가 붙는다면, 모든 레코드를 읽게 되고, 결국 full scan을 하게 된다. 따라서 쿼리에 대한 인덱싱이 잘되어 있는 테이블이라면, SQL_CALC_FOUND_ROWS는 사용하지 않는 것이 좋다.

그러나!

ORDER BY로 정렬을 때리게 된다면 어차피 full scan을 한다! 따라서 SQL_CALC_FOUND_ROWS를 쓰는게 이득이다!

내 눈으로 직접 확인하고 싶어 쿼리 코스트를 직접 구해서 비교해보았다.

📒 SQL_CALC_FOUND_ROWS 성능 비교

우선 order by의 유무와 index가 큰 영향을 미친다고 하는데, index까지 걸 시간은 프로젝트 마감일 때문에 힘들어서 order by 차이만 두고 비교해보았다.

PAGING QUERY COST COMPARE

ORDER BY를 사용하는 경우의 총 코스트
1. 페이징 쿼리 + COUNT(*) 쿼리 : 0.0064715
2. 페이징 쿼리 + FOUND_ROWS() : 0.00370975
ORDER BY를 사용하지 않는 경우의 총 코스트
1. 페이징 쿼리 + COUNT(*) 쿼리 : 0.003184
2. 페이징 쿼리 + FOUND_ROWS() : 0.00322225
예상대로 ORDER BY를 사용해야하는 경우에는 FOUND_ROWS를 사용하는 것이 2배 빠르고,
ORDER BY를 사용하지 않는 경우에는 COUNT를 사용하는 것이 조금 더 빨랐다.
나의 상황에서는 정렬을 해야하며, 인덱싱도 잘 되어 있지 않은 상황이므로 SQL_CALC_FOUND_ROWS을 도입하기로 결정했다. 👍