SQL CALC FOUND ROWS

• 성능 향상이 아닌, 개발자의 편의를 위해 만들어진 힌트다.
• MySQL의 LIMIT를 사용하는 경우, 조건을 만족하는 레코드가 LIMIT에 명시된 수보다 더 많다고 하더라도 LIMIT에 명시된 수만큼 만족하는 레코드를 찾으면 즉시 검색 작업을 멈춘다.
• 하지만 SQL_CALC_FOUND_ROWS 힌트가 포함된 쿼리의 경우에는 LIMIT를 만족하는 수만큼의 레코드를 찾았더라도 끝까지 검색을 수행한다.
  • 사용자에게는 최종적으로 LIMIT에 제한된 수만큼의 결과 레코드만 반환된다.
• SQL_CALC_FOUND_ROWS 힌트가 사용된 쿼리가 실행된 경우에는 FOUND_ROWS() 함수를 이용해 LIMIT를 제외한 조건을 만족하는 레코드가 전체 몇 건이었는지 알아낼 수 있다.

  SELECT SQL_CALC_FOUND_ROWS * FROM employees LIMIT 5;
  SELECT FOUND_ROWS() AS total_record_count;

페이징 처리

SQL_CALC_FOUND_ROWS 힌트를 사용한 페이징 처리

SELECT SQL_CALC_FOUND_ROWS * FROM employees WHERE first_name='Georgi' LIMIT 0, 20;
SELECT FOUND_ROWS() AS total_record_count;

• 한 번의 쿼리 실행으로 필요한 정보 2가지를 모두 가져오는 것처럼 보이지만, FOUND_ROWS 함수의 실행을 위해 또 한 번의 쿼리가 필요하기 때문에 쿼리를 2번 실행해야 한다.
• SQL_CALC_FOUND_ROWS 힌트 때문에 조건을 만족하는 레코드 전부를 읽어봐야 한다.
  • 디스크 헤드가 특정 위치로 움직일 때까지 기다려야 하는 랜덤 I/O가 레코드 수만큼 일어난다.

COUNT(*) 쿼리를 사용하는 페이징 처리

SELECT COUNT(*) FROM employees WHERE first_name='Georgi';
SELECT * FROM employees WHERE first_name='Georgi' LIMIT 0, 20;

• 기존 2개의 쿼리로 쪼개어 실행하는 방법이다. (쿼리 2번 실행)
• 첫 번째 쿼리에서는 건수만 가져오면 되기 때문에, 실제로 데이터 레코드를 찾아가기 위한 랜덤 I/O는 발생하지 않는다.
  • 커버링 인덱스(Covering index) 쿼리이기 때문이다.
• 두 번째 쿼리는 실제로 데이터 레코드를 읽으러 가야 하기 때문에 랜덤 I/O가 발생하지만, LIMIT 0, 20 의 제한으로 랜덤 I/O는 20번만 실행한다.

두 방식의 비교

• SQL_CALC_FOUND_ROWS 힌트를 사용하는 경우가 비교할 수 없을만큼 느리다.
  • 전기적 처리인 메모리나 CPU의 연산 작업에 비해 기계적 처리인 디스크 작업이 얼마나 느린 작업인지를 고려한다.
• SELECT 쿼리 문장이 UNION(혹은 UNION DISTINCT)으로 연결된 경우에는 SQL_CALC_FOUND_ROWS 힌트를 사용해도 FOUND_ROWS 함수로 정확한 레코드 건수를 가져올 수 없다.
• 따라서 인덱스나 쿼리의 튜닝이 제대로 되었다면 COUNT(*) 쿼리를 사용하는 방식이 빠르게 실행될 것이므로, SQL_CALC_FOUND_ROWS 는 사용하지 않는 것을 추천한다.
• 다음과 같은 경우 SQL_CALC_FOUND_ROWS 로 처리하는 것이 빠른 경우도 있다.
  • COUNT(*) 쿼리나 칼럼값을 읽어오는 SELECT 쿼리가 적절히 튜닝되지 않았을 경우
  • WHERE 조건에 대해서 적절한 인덱스가 준비되지 않은 경우
  • 그러나 이러한 경우라면 쿼리나 인덱스를 튜닝하는 편이 훨씬 더 빠른 결과를 만들어낼 수 있을 것이다.
• 일반적인 관점에서, SQL_CALC_FOUND_ROWS 보다는 레코드 카운터용 쿼리와 데이터를 조회하는 쿼리는 분리하는 것이 더 효율적일 것 같다.

Reference

참고 서적

📔 Real MySQL 8.0