MySQL Total Row Count를 한 줄에 만드는 FOUND_ROWS

안녕하세요. 이번 포스트는 mysql에 대해 다뤄보겠습니다. mysql 주제로는 처음 다뤄보네요. 제 첫 mysql 포스트의 주제는 FOUND_ROWS() 입니다.

1. SQL_CALC_FOUND_ROWS(FOUND_ROWS)의 역할

아마 FOUND_ROWS() 자체도 처음 들어보시는 분도 많을 것이라 생각합니다.
FOUND_ROWS의 역할을 간단하게 정의하면 Limit을 제외하고 count를 저장할 수 있는 역할을 합니다. 다르게 얘기하자면, LIMIT과 상관 없이 조건에 해당하는 ROW를 전체 스캔한다는 것이죠. 특히 page nation(페이지 정보로 나눠서 정보 전달하는 기술)을 사용할 때, WHERE 절로 걸리는 filtering(필터링)이 같이 적용됩니다. 이 때문에 백엔드에서 API의 total count 정보를 전달 해줄 때, 유용합니다.

2. SQL_CALC_FOUND_ROWS(FOUND_ROWS)의 사용법

제가 작성한 쿼리문으로 예시를 확인해보겠습니다.

SELECT
    SQL_CALC_FOUND_ROWS
    P.created_at as productRegistDate,
    I.image_small as productSmallImageUrl,
    PD.name as productName,
    P.product_no as productNo,
    P.product_code as productCode,
    ROUND(PD.price, -1) as sellPrice,
    discountRate,
    ROUND(PD.price * (100-discountRate)/100, -1) AS discountPrice,
    CASE
        WHEN discountRate = 0
            THEN "미할인"
        ELSE "할인"
    END AS discountYn,
    IF(PD.is_displayed = 1, "진열", "미진열") as productExhibitYn,
    IF(PD.is_activated = 1, "판매", "미판매") as productSellYn

FROM (
    SELECT products.*,product_details.close_time,
    CASE
        WHEN (product_details.discount_end_date IS NULL AND product_details.discount_start_date IS NULL) AND product_details.discount_rate IS NOT NULL
            THEN product_details.discount_rate
        WHEN (product_details.discount_end_date IS NOT NULL AND product_details.discount_start_date IS NOT NULL) AND (product_details.discount_start_date <= now() AND product_details.discount_end_date >= now())
            THEN product_details.discount_rate
        ELSE 0
    END AS discountRate
    FROM products

INNER JOIN product_details
ON product_details.product_id = products.product_no
AND product_details.close_time = '9999-12-31 23:59:59'
) AS P

INNER JOIN product_images as PI
ON P.product_no = PI.product_id
AND PI.close_time = '9999-12-31 23:59:59'
AND PI.is_main = 1

INNER JOIN images as I
ON PI.image_id = I.image_no
AND I.is_deleted = 0

INNER JOIN product_details as PD
ON PD.product_id = P.product_no
AND PD.close_time = '9999-12-31 23:59:59'

WHERE
    P.is_deleted = False
    
ORDER BY
P.product_no DESC

LIMIT 30
OFFSET 0;

사용법은 위와 같이 전체 count 정보가 필요한 쿼리문의 SELECT 구문 시작의 최상단에 'SQL_CALC_FOUND_ROWS' 한 줄을 기입하면 됩니다.

쿼리가 정상수행 되고 나면, 이제 전체 count를 확인할 수 있습니다. count를 확인할 때도 쿼리 한 줄로 확인할 수 있습니다.

SELECT FOUND_ROWS();

 

생각보다 굉장히 간단함을 확인할 수 있습니다.
주의할 부분은 직전의 쿼리 결과만을 반영하기 때문에 다른 작업을 수행하기 전에 total count를 구하고자 하는 대상의 count를 미리 기록해두어야 한다는 것 입니다.

3.SQL_CALC_FOUND_ROWS(FOUND_ROWS)의 성능

이 포스트를 적게된 주요 원인이기도 합니다. 사실 사용은 간단하지만 정확히 이 Command(명령어)가 어떤 역할을 하는가를 아는 것이 중요합니다.

우선 제가 말씀드리는 상황이 굉장히 일반적일 것 입니다.- Filtering 조건(WHERE)을 활용해 필요한 Column을 LIMIT으로 Paged된 결과를 얻는다.
- Filtering 조건(WHERE)을 활용해 COUNT 결과를 얻는다.

이 경우 SELECT COUNT(1)을 사용하기 때문에 동일한 조건의 쿼리가 두 번 수행될 수 밖에 없습니다. INDEX를 타지 않는다면, COUNT를 한다는 관점에서만 바라본다면 이 방법은 비효율적일 수 밖에 없습니다.

그러나 SQL_CALC_FOUND_ROWS는 쿼리에 포함될 때, 쿼리는 전체를 스캔하게 됩니다. 즉, INDEX를 잘 타게 되서 쿼리의 비용이 엄청나게 줄어드는 경우가 있는데도 저 방법을 사용한다면 쿼리를 풀스캔(Full Scan) 하게 됩니다.

즉, 일반적인 경우라면, SQL_CALC_FOUND_ROWS가 권장되지 않는다는 것 입니다. 그 만큼 index가 중요한 것이죠.

Index의 중요성을 보여주는 그림

그러나 또 다른 경우가 발생합니다. ORDER BY로 정렬하게 된다면, 해당 쿼리가 Full Scan을 할 수 밖에 없게 됩니다. 이 경우라면 쿼리를 한 번이라도 줄이는 것이 더 현명하기 때문에 Full Scan을 한 번이라도 덜 하는 것이 효과적이게 됩니다.

결론은 DB의 INDEXING(인덱싱)이 잘 되어 있고 ORDER BY와 같은 정렬을 타지 않는다면 SQL_CALC_FOUND_ROWS를 사용하는 것이 좋을 수 있습니다. 그러나 인덱싱이 잘 되어 있고 Full Scan을 하지 않는 경우(정렬등을 하지 않을 때)에는 SELECT COUNT(1)과 같은 쿼리로 두 번 쿼리 수행이 더 빠를 수 있습니다.

위 내용도 쿼리가 어떻게 수행되느냐에 따라서 전혀 다른 결과가 되기 때문에 일반적이라기 보다는 참조만 하시는 것을 추천 드립니다.

reference:
www.percona.com/blog/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/
blog.asamaru.net/2015/09/11/using-sql-calc-found-rows-and-found-rows-with-mysql/