MySql에는 SQL_CALC_FOUND_ROWS과 FOUND_ROWS() 라는 기능이 있다.일반적인 쿼리를 작성하는 상황에서는 크게 사용할 일이 없는 기능들이지만 알아두면 정말 유용하게 사용할 수 있다.
어디다 활용을 할지에 대해 보기 전에 각 기능이 무슨 역할을 하지는지부터 알아보자.
기본적인 동작은 mysqlkorea에 설명되어 있으니 참고하면 된다.
SQL_CALC_FOUND_ROWS
SQL_CALC_FOUND_ROWS는 SELECT 쿼리에 사용할 수 있는 MySQL 힌트로 쿼리 결과의 전체 row 수를 임시로 저장하게 합니다. 여기서 중요한 점은 row 수가 반환되는 row 수가 아니라 조건에 해당하는 전체 row 수를 가진다는 것이다. 따라서 LIMIT 0, 10을 지정하여 10개의 row만 가져온다고 하더라도 LIMIT이 없을 때의 쿼리 결과와 같은 row 수를 계산한다. 이 부분이 상당히 중요한 부분이다. 나중에 다시 설명하겠지만 LIMIT에 영향을 받지 않으므로 쿼리 성능을 떨어뜨릴 수 있다.
FOUND_ROWS()
FOUND_ROWS는 직전 쿼리에서 검색된 결과 row 수를 반환한다. 이해를 돕기위해 간단한 쿼리의 결과를 아래에 추가했으니 참고하면 바로 이해가 되리라 생각한다.
mysql> SELECT * FROM article;
...
...
mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 120 |
+--------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM article limit 10;
...
...
mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 10 |
+--------------+
1 row in set (0.00 sec)
mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
단, 여기서 주의할 것은 맨 마지막 결과다. "왜 10이 아닐까?" 하는 생각을 할 수 있으나 맨처음 적었듯이 "직전" 쿼리의 결과 row 수를 반환하기 때문에 1이 나오는 것이다.(이전 select FOUND_ROWS();의 결과 row 수 반환)
SQL_CALC_FOUND_ROWS와 FOUND_ROWS()의 결합
각각의 기능을 확인했으니 둘이 만나면 어떻게 되는지 알아보자. 일단 아래의 쿼리 결과를 보자.
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM article limit 10;
...
...
mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 120 |
+--------------+
1 row in set (0.00 sec)
이해가 되는가? 앞선 예시와 결과가 다르다. SQL_CALC_FOUND_ROWS가 없었다면 FOUND_ROWS()의 결과는 10이었을 것이다. 하지만 SQL_CALC_FOUND_ROWS에서 설명했던 것과 같이 SQL_CALC_FOUND_ROWS 힌트가 추가되면 limit은 무시되고 검색 조건에 해당하는 모든 row 수를 계산하기 때문에 FOUND_ROWS()의 결과가 120이 나오는 것이다.
활용
페이징
게시판 같은 서비스를 만들다보면 필수적으로 들어가는 기능이 페이징 기능이다. 일반적으로 페이징 처리를 위해서는 total row수가 필요한데 이를 처리하기 위해 동일 조건의 쿼리를 두번 사용하여 한번은 count(1), 한번은 필요한 필드를 넣어 쿼리한다. 보통은 대게 이렇게 개발해서 사용하고 크게 문제가 없다. 하지만 데이터의 양이 많거나 쿼리가 복잡해서 성능이 떨어지는 상황을 생각해보자. 예를들어 성능이 떨어지는 쿼리가 처리되는데 0.5초가 걸린다고 가정할 때 페이징을 위해 두번의 쿼리를 해야 한다면 쿼리에 소요되는 시간은 1초가 걸린다. 이 부분을 개선하기 위해 FOUND_ROWS()를 사용할 수 있다.
다시 일반적인 처리로 돌아가서 보면 count 쿼리를 해서 total row 수를 이용해 페이징을 한 후 limit을 지정해 데이터 쿼리를 질의하여 값을 돌려 받는다.
그럼 FOUND_ROWS()를 사용할 때는 어떻게 하는가? 데이터 쿼리를 먼저하되 SQL_CALC_FOUND_ROWS 힌트를 추가하여 질의한다. 그 다음 FOUND_ROWS()를 사용하여 total row 수를 가져와서 페이징 처리를 한다. 간단히 얘기해서 두 쿼리의 순서가 바뀐다.
당연한 얘기지만 빈번히 사용되는 쿼리라면 이렇게까지 성능이 떨어지기 전에 쿼리를 튜닝하고 설계를 변경하는 것이 옳다. 하지만 다양한 쿼리를 필요로 하는 프로그램을 만들다보면 모든 쿼리에 맞는 최적화를 하기는 어려우므로 이런 방법들을 적용하는 것이 필요할 수 있다.
주의
앞선 설명에서는 원리를 설명하기 위해 간단히 설명했지만 여기에서 중요한 부분이 빠져 있다. FOUND_ROWS가 모든 상황에 최적화되는 것은 아니라는 것이다. DB의 쿼리 성능에 관련해서는 변수와 고려해야할 사항이 워낙 많기 때문에 여기서 다 설명하기는 어렵고 중요한 부분만 설명하겠다.
서두에 SQL_CALC_FOUND_ROWS를 설명하면서 limit과 무관하게 동작함에 따라 성능이 떨어질 수 있다는 점을 적었다. 여기서 하고자하는 이야기의 중요한 부분이 이 부분이다.
쿼리가 index를 탈 수 있다면 limit 범위만 탐색하여 결과를 찾게되므로 쿼리 성능이 떨어지지 않는다. 그렇다면 "index를 타지 않는다면?" 어떤가? row들을 차례로 스캔하면서 조건을 검사해 limit 범위가 채워지면 스캔을 멈춘다. limit 0, 10을 지정했을 때를 가정하고 운이 좋아서 조건에 해당하는 데이터가 상위 10개에 속해 있다면 성능은 index를 타는 것과 거의 동일할 것이다. 반대로 데이터가 맨 마지막 10개라면 전체를 스캔하게 되므로 성능은 떨어질 것이다. 최선과 최악의 경우만 있는 것은 아니니 평균을 내자면 50% 정도를 스캔하면 결과를 얻을 수 있다는 결론을 얻는다.(실제론 맞지 않을 수 있다. 그냥 이론상 그렇다는 것이다.)
자 다시 SQL_CALC_FOUND_ROWS로 돌아가보면 SQL_CALC_FOUND_ROWS가 없다면 평균 50%만 스캔하면 끝날 쿼리를 SQL_CALC_FOUND_ROWS를 넣음으로 인해서 100%를 스캔하게 된다(전체 row수를 세어야 하므로). 따라서 성능이 떨어질 수 있다는 것이다.
그렇다면 "이걸 쓰지말아야 한다는 것인가?"라고 생각될 수 있다. 하지만 다음의 경우를 생각해보자. 앞서 설명에서 데이터를 스캔하는 과정에서는 정렬에 대한 문제를 제시하지 않았다. 그럼 정렬을 포함해보자. 정렬된 결과에서 10개를 가져와야 한다면, 게대가 정렬이 index에 의해 정렬이되지 않는다면... 결국은 정렬을 위해서 전체 스캔이 일어나고 heap 테이블이 만들어지고 정렬을 해야한다.
결론
결론은 위의 경우는 어짜피 100% 스캔이 일어난다는 것이다. 따라서 어짜피 100% 스캔을 두번 해야 할 상황이라면 SQL_CALC_FOUND_ROWS를 사용하여 1번으로 줄임으로써 쿼리 질의 시간을 절반으로 줄일 수 있다.
사실 쿼리라는 것이 검색 조건, 정렬, index 등등 여러가지 변수에 의해 성능이 천차만별로 달라질 수 있기 때문에 여기서 여러가지 경우를 예를들어 설명하기는 힘들다. 이런 내용으로만 정리해도 책이 몇권이다.(유명한 책중에 대용량 데이터베이스 솔루션이란 책이 있다. 이와 관련된 내용도 포함되어 있다.)
일부 사람들은 index를 타지 목하면 SQL_CALC_FOUND_ROWS를 사용하는게 낫다라고 하기도 한다. 위에 설명을 했듯이 index를 타지 못하더라도 전체 스캔을 하는 것보다 더 나은 상황이 만들어질 수 있는 경우는 많기 때문에 index를 타지 못하는 것만으로 SQL_CALC_FOUND_ROWS를 선택하는 것은 맞지 않다.
최종 결론은 어짜피 전체 스캔을 피할 수 없다면 이왕 스캔한 김에 row수를 세어두어서 count 쿼리를 위해 다시 성능을 죽이지는 말자는 것이다.
Group By 결과 row 수
앞의 페이징에서 너무 긴 이야기를 했으므로 여기는 조금 간단히 하겠다. 어짜피 비슷한 얘기가 되므로. Group By 결과 row 수를 얻으려면 어떻게 해야할까? 간단할 듯 하지만 그리 간단하지는 않다.(내가 모르는 방법이 있다면 할 수 없고...)
가장 쉽게 접근하는 방법은 서브 쿼리를 사용하는 방법일 것이다. Group By 쿼리를 서브 쿼리로 넣어 count를 사용하는 방법. 대략 아래와 같다.
mysql> select count(1) from (select count(1) as cnt from article group by apartmentIdx) APT;
+----------+
| count(1) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
다음은 distinct를 사용하는 방법이다. distinct에 대해서는 주제를 벗어나니 여기서는 생략.
mysql> select count(distinct apartmentIdx) as cnt from article;
+-----+
| cnt |
+-----+
| 4 |
+-----+
1 row in set (0.00 sec)
그외에도 오라클이나 다른 DB에서는 또 다른 방법들도 있다.
그렇다 하더라도 group by를 한다는 것은 어짜피 전체를 스캔해서 결과를 얻는 과정(다른 조건에 의해 아닌 경우도 있을 수는 있다)을 거치므로 SQL_CALC_FOUND_ROWS를 사용하여 결과 row를 세어두는 것이 성능에 많은 도움을 줄 수 있다.
정리
그 외에도 여러가지 활용 방법이 있을 수 있으나 대표적인 사례만 나열했다. 다시 한번 강조하지만 이 방법은 만능이 아니다. 전체 스캔(Full Scan)을 피할 수 없는 상황에서 이왕 전체 스캔한거 count를 위해 다시 하지 않도록 활용하는 전략이 가장 적합하다.
또한 index를 타지 않는다고 무조건 full scan이 일어나지는 않는다. 이 부분에서 가장 중요한 것은 검색조건 보다 정렬이다. order by를 사용하지 않고 원하는 정렬이 되도록 설계한다면 full scan을 충분히 배제할 수 있다.(이 부분은 주제에서도 많이 벗어나고 설명하자면 얘기가 다시 무지하게 길어지므로 일단 패스.)