DB Server의 CPU 사용률이 높은 상황 - 4

MySQL에서 COUNT(*), GROUP BY, JOIN과 같은 연산은 단순해 보이지만, 실제로는 매우 많은 시스템 자원을 사용하는 작업입니다. 특히, 적절한 인덱스가 없거나 그룹 수가 많을 경우, 성능에 치명적인 영향을 줄 수 있습니다.

우선 COUNT(*)는 특정 조건을 만족하는 전체 행의 수를 세는 작업입니다. 조건이 없는 경우, 또는 조건이 있어도 인덱스를 타지 못하는 경우, MySQL은 테이블 전체를 스캔하여 결과를 계산하게 됩니다. 특히 InnoDB 스토리지 엔진은 MyISAM과 달리 테이블의 전체 행 수를 메타데이터로 관리하지 않기 때문에, 항상 실시간으로 계산을 수행해야 하고, 이 과정에서 디스크 I/O와 CPU 사용량이 급증하게 됩니다.

이와 함께 SUM이나 AVG와 같은 집계 함수 역시 단순 계산이 아닌, 각 그룹별로 중간 상태를 계속 유지해야 하기 때문에 메모리 사용량이 많아집니다. 예를 들어, SUM(price) GROUP BY category 라는 쿼리가 있다고 할 때, MySQL은 각 카테고리별로 합계를 누적하기 위해 메모리 내에 각 그룹별 상태를 저장하고 업데이트해야 합니다. 이때 메모리 사용량은 그룹 개수 × 각 집계의 중간 결과 크기로 산정되며, 그룹이 많아질수록 메모리 부담이 커지게 됩니다.

GROUP BY 연산 자체도 비용이 큰 작업입니다. MySQL은 GROUP BY를 수행하기 위해 일반적으로 두 가지 방식 중 하나를 선택합니다: 정렬(SORT) 기반과 해시(HASH) 기반입니다. 정렬 기반의 경우, 데이터를 먼저 정렬한 뒤 그룹핑하는 방식이며, 이 과정에서 정렬 버퍼(sort buffer)와 임시 테이블이 사용됩니다. 해시 기반의 경우에는 메모리에 해시 테이블을 만들어 그룹 키를 기준으로 데이터를 누적합니다. 문제는 이러한 해시 테이블이 너무 커져서 메모리에 다 담기지 못하면, tmp_table_size와 max_heap_table_size를 초과하게 되고, 이때부터는 디스크 기반의 임시 테이블로 Spill이 발생하게 됩니다. 메모리에서 디스크로 넘어가는 순간, 성능은 급격히 저하됩니다.

예를 들어, 수천 개의 그룹이 발생하는 GROUP BY 쿼리를 실행하면, 해시 테이블이 커지고 tmp_table_size 제한을 초과하여 디스크에 중간 결과를 기록하게 됩니다. 이로 인해 디스크 I/O가 증가하고, 실행 시간이 수 배에서 수십 배까지 늘어날 수 있습니다. 이와 같은 이유로 GROUP BY는 인덱스를 활용하거나, 임시 테이블 크기를 조정하거나, GROUP 수를 줄이는 방식으로 튜닝해야 합니다.

한편, JOIN 연산도 조인되는 테이블의 크기와 인덱스 존재 여부에 따라 성능 차이가 매우 큽니다. 일반적인 JOIN 처리 방식은 Nested Loop Join이나 Block Nested Loop Join, 그리고 MySQL 8.0 이상에서는 Hash Join도 사용됩니다. Nested Loop Join의 경우, 외부 테이블의 각 row마다 내부 테이블을 탐색하게 되며, 내부 테이블에 적절한 인덱스가 없으면 전체 row를 반복적으로 스캔해야 하는 비효율적인 상황이 발생합니다. 예를 들어 A 테이블에 10,000건, B 테이블에 100,000건의 데이터가 있는데 인덱스 없이 조인을 수행한다면, 최대 10억 건에 가까운 비교 연산이 발생할 수 있습니다. 이는 CPU와 메모리, 그리고 디스크 I/O 모두에 막대한 부하를 주게 되며, 쿼리 응답 시간이 매우 길어지는 원인이 됩니다.

따라서 JOIN의 성능을 확보하기 위해서는 조인 조건에 사용되는 컬럼에 인덱스를 반드시 생성해야 하며, 조인의 순서와 드라이빙 테이블의 선택에도 유의해야 합니다. 또한 조인에 사용되는 테이블이 모두 정렬되어 있거나 적절한 인덱스를 타는 경우에는 조인 알고리즘의 효율도 크게 향상됩니다.