데이터 마이그레이션 정합성 검증(Data Validation), 스토리지 용량 산정, 혹은 레거시 DB 정리를 진행할 때 수십 개의 스키마(Database)에 흩어져 있는 수백~수천 개 테이블의 전체 건수(COUNT(*))를 한 번에 조회해야 하는 상황이 자주 발생합니다.
이때 가장 흔히 작성하는 방식이 메타데이터를 조회해 SELECT '테이블명', COUNT(*) FROM 스키마.테이블 UNION ALL ... 형태의 거대한 쿼리를 만들어 실행하는 것입니다. 하지만 이 쿼리를 실무 운영 DB에서 실행해 보면 응답이 수십 분 이상 걸릴 뿐만 아니라, 순간적으로 디스크 I/O가 폭증하며 기존 온라인 트랜잭션(OLTP) 쿼리들까지 전부 느려지는 대형 장애로 이어지곤 합니다.
본 포스팅에서는 왜 UNION 기반의 COUNT(*) 쿼리가 치명적으로 느린지 InnoDB 스토리지 엔진 아키텍처 관점에서 분석하고, 실무에서 사용하는 4가지 대량 테이블 고속 카운트 기법과 버퍼 풀(Buffer Pool) 오염 방지 전략을 상세히 다뤄보겠습니다.
1. 왜 SELECT COUNT(*) ... UNION ALL 방식은 극단적으로 느릴까? (Internal Architecture)
단순히 "데이터가 많아서 느리다"고 생각하기 쉽지만, 여기에는 MySQL 옵티마이저와 InnoDB 엔진의 구조적인 한계 4가지가 복합적으로 작용합니다.
① 단일 스레드 순차 실행 (Single-threaded Serial Execution)의 한계
MySQL에서 UNION ALL로 묶인 수백 개의 서브쿼리는 병렬로 실행되지 않습니다. 기본적으로 하나의 커넥션(세션)은 단일 OS 스레드를 점유하므로, 첫 번째 테이블의 COUNT(*)가 완전히 끝날 때까지 두 번째 테이블은 대기합니다. (MySQL 8.0.14+부터 innodb_parallel_read_threads를 통한 클러스터드 인덱스 병렬 읽기가 도입되었으나, 수백 개 테이블의 UNION 쿼리 전체를 동시에 병렬로 쪼개서 돌리는 것과는 결이 다릅니다.)
② 가장 치명적인 실수: UNION vs UNION ALL
만약 쿼리를 작성할 때 UNION ALL이 아니라 습관적으로 UNION을 사용했다면 성능 저하는 배가 됩니다. UNION은 중복 제거(Deduplication)를 보장하기 위해 내부적으로 임시 테이블(Internal Temporary Table)을 생성하고 정렬(Filesort) 또는 해시 연산을 수행합니다. 단순 건수 취합에는 중복 제거가 전혀 필요 없으므로 반드시 UNION ALL을 써야 합니다.
③ InnoDB MVCC와 세컨더리 인덱스 풀 스캔 (Secondary Index Full Scan)
앞서 다룬 적이 있듯, InnoDB는 MVCC(다중 버전 동시성 제어) 특성상 테이블의 전체 건수를 별도로 들고 있지 않습니다. 따라서 WHERE 조건 없는 COUNT(*)를 실행하면 옵티마이저는 가장 크기가 작은 세컨더리 인덱스(Secondary Index)를 선택해 리프 노드의 처음부터 끝까지 전부 스캔(Full Index Scan)합니다. 만약 적절한 세컨더리 인덱스가 없는 테이블이 섞여 있다면? 거대한 데이터 페이지를 담고 있는 클러스터드 인덱스(PK) 전체를 디스크에서 퍼 올리는 참사(Full Table Scan)가 발생합니다.
④ 버퍼 풀 오염 (Buffer Pool Churning / Pollution)
가장 위험한 아키텍처적 문제입니다. 평소 애플리케이션에서 잘 조회하지 않는 수백 기가바이트짜리 로그성/이력성 테이블들을 COUNT(*)로 연속해서 풀 스캔하면, 디스크에 있던 차가운 데이터 페이지(Cold Pages)들이 메모리(InnoDB Buffer Pool)로 밀려 들어옵니다. 비록 InnoDB가 Midpoint Insertion Strategy(Old Sublist 37% 지점 삽입)로 버퍼 풀을 보호하긴 하지만, 수백 개 테이블을 연속으로 스캔하면 결국 기존에 메모리에 잘 올라와 있던 핵심 서비스의 핫 데이터(Hot Pages)가 메모리에서 쫓겨나고(Eviction), DB 전체의 Buffer Pool Hit Ratio가 급락하며 서비스 장애가 발생합니다.
2. 해결책 비교: 상황에 맞는 최적의 카운트 전략 선택하기
다수의 스키마와 테이블 건수를 파악할 때는 "100% 정확한 실시간 건수가 필요한가(정합성 검증)" 아니면 "대략적인 규모 파악이 목적인가(모니터링/용량 산정)"에 따라 접근 방식을 완전히 달리해야 합니다.
| 방식 | 정확도 | 수행 속도 | DB 부하 | 추천 사용 시나리오 |
| 1. information_schema.TABLES 조회 | 낮음 (추정치, 오차 10~50%) | 초고속 (0.1초 미만) | 없음 (Zero I/O) | 전체적인 테이블 볼륨 파악, 빈 테이블 색출, 모니터링 |
| 2. mysql.innodb_table_stats 조회 | 중간 (STATS_SAMPLE_PAGES 비례) | 초고속 (0.05초 미만) | 없음 | information_schema 호출 시의 MDL/통계 갱신 부하조차 피하고 싶을 때 |
| 3. 동적 SQL + 커버링 인덱스 힌트 (UNION ALL) | 100% 정확 | 느림 (단일 스레드 순차) | 높음 | 테이블 개수가 적고(50개 미만), 각 테이블 건수가 수백만 건 이하일 때 |
| 4. 외부 스크립트 멀티스레드 병렬 처리 (Parallel Count) | 100% 정확 | 빠름 (코어 수 비례 단축) | 제어 가능 (Connection Pool 제한) | 수억 건 이상 대용량 DB 마이그레이션 후 데이터 정합성 전수 검증 시 |
3. 실무 구현 가이드: 단계별 쿼리 및 스크립트
방법 1: 0.1초 만에 끝내는 초고속 추정치 조회 (통계 딕셔너리 활용)
1건의 오차도 허용하지 않는 마이그레이션 검증이 아니라면, 직접 COUNT(*)를 날릴 필요가 전혀 없습니다. 앞서 설명드린 InnoDB 영구 통계 테이블(mysql.innodb_table_stats)을 직접 조회하면 디스크 I/O 없이 모든 스키마와 테이블의 건수를 즉시 뽑아낼 수 있습니다.
주의: information_schema.TABLES를 조회할 때 information_schema_stats_expiry = 0(또는 MySQL 5.7의 innodb_stats_on_metadata = ON)으로 되어 있으면 조회 순간 통계 샘플링이 발생해 느려질 수 있으므로, 엔진 내부 통계 테이블인 mysql.innodb_table_stats를 직접 읽는 것이 훨씬 안전하고 빠릅니다.
-- 다중 스키마의 모든 테이블 추정 건수 및 인덱스 크기 초고속 조회
SELECT
database_name AS schema_name,
table_name,
n_rows AS estimated_row_count,
ROUND((clustered_index_size + sum_of_other_index_sizes) * @@innodb_page_size / 1024 / 1024, 2) AS total_size_mb,
last_update AS stats_updated_at
FROM mysql.innodb_table_stats
WHERE database_name IN ('service_db_1', 'service_db_2', 'log_db') -- 대상 스키마 지정
ORDER BY database_name, n_rows DESC;
방법 2: 정확한 COUNT(*)가 필요할 때 — 최적화된 동적 SQL 생성기
마이그레이션 검증 등을 위해 반드시 100% 정확한 COUNT(*)가 필요한 경우, 일일이 손으로 쿼리를 짜지 않고 메타데이터를 이용해 최적화된 UNION ALL 쿼리를 자동 생성합니다.
이때 성능을 극대화하는 핵심 튜닝 포인트는 두 가지입니다.
- 세션 레벨에서 innodb_parallel_read_threads 값을 높여 클러스터드 인덱스 스캔 시 멀티 스레드 병렬 읽기를 활성화합니다 (MySQL 8.0.14+).
- GROUP_CONCAT 잘림 방지를 위해 group_concat_max_len을 최대로 늘린 후 UNION ALL 쿼리를 생성합니다.
-- 1. 세션 파라미터 튜닝 (병렬 스캔 활성화 및 GROUP_CONCAT 버퍼 확장)
SET SESSION innodb_parallel_read_threads = 8; -- CPU 코어 여유에 맞춰 4~16 설정 (MySQL 8.0.14+)
SET SESSION group_concat_max_len = 10485760; -- 10MB로 확장 (쿼리 잘림 방지)
-- 2. 다중 스키마 대상 최적화된 COUNT(*) UNION ALL 쿼리 자동 생성
SELECT
GROUP_CONCAT(
CONCAT(
'SELECT ''', TABLE_SCHEMA, ''' AS schema_name, ',
'''', TABLE_NAME, ''' AS table_name, ',
'COUNT(*) AS exact_count ',
'FROM `', TABLE_SCHEMA, '`.`', TABLE_NAME, '`'
)
SEPARATOR '\nUNION ALL\n'
) AS generated_sql
FROM information_schema.TABLES
WHERE TABLE_SCHEMA IN ('service_db_1', 'service_db_2')
AND TABLE_TYPE = 'BASE TABLE';
만약, 복사해서 다시 실행하는 과정조차 자동화하고 싶다면, 아래와 같이 Prepared Statement(동적 SQL 실행)로 묶어 한 번에 결과를 출력할 수 있습니다.
-- 동적 SQL 생성 및 즉시 실행 (Stored Procedure 없이 세션 변수 활용)
SET SESSION group_concat_max_len = 10485760;
SET SESSION innodb_parallel_read_threads = 8;
SELECT
GROUP_CONCAT(
CONCAT(
'SELECT ''', TABLE_SCHEMA, ''' AS schema_name, ',
'''', TABLE_NAME, ''' AS table_name, ',
'COUNT(*) AS exact_count ',
'FROM `', TABLE_SCHEMA, '`.`', TABLE_NAME, '`'
)
SEPARATOR ' UNION ALL '
) INTO @count_sql
FROM information_schema.TABLES
WHERE TABLE_SCHEMA IN ('service_db_1', 'service_db_2')
AND TABLE_TYPE = 'BASE TABLE';
PREPARE stmt FROM @count_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
방법 3: 대용량 실무 환경의 정석 — 테이블 단위 병렬 실행 (Bash / Python)
테이블이 수백 개이고 전체 데이터가 수 테라바이트(TB)에 달한다면, 앞서 살펴본 UNION ALL 단일 쿼리는 아무리 튜닝해도 직렬 실행(Serial Execution)의 벽에 막혀 수 시간이 소요됩니다. 게다가 중간에 네트워크 타임아웃(net_read_timeout)이나 max_execution_time에 걸리면 처음부터 다시 돌려야 합니다.
따라서 시니어 DBA들은 거대한 UNION ALL 쿼리를 던지는 대신, 테이블 목록을 뽑아 OS 레벨에서 xargs -P(병렬 프로세스)나 Python ThreadPoolExecutor를 이용해 여러 세션에서 동시에 COUNT(*)를 수행합니다.
실무 원라이너 스크립트 (Bash + xargs 병렬 처리)
아래 스크립트는 대상 스키마의 모든 테이블을 추출한 뒤, 동시에 4개의 워커(Worker) 스레드(-P 4)를 띄워 병렬로 COUNT(*)를 집계합니다. 수행 속도가 정직하게 약 4배 빨라지며, 완료되는 테이블부터 실시간으로 화면에 찍힙니다.
#!/bin/bash
# 대상 스키마 목록 및 접속 정보
DB_USER="admin"
DB_PASS="your_password"
DB_HOST="127.0.0.1"
SCHEMAS="'service_db_1', 'service_db_2'"
PARALLEL_WORKERS=4 # DB CPU 여유에 따라 4~8 권장 (너무 높이면 I/O 포화 발생)
# 1. 대상 스키마.테이블 리스트 추출 후 xargs로 병렬 COUNT(*) 실행
mysql -u${DB_USER} -p${DB_PASS} -h${DB_HOST} -N -e "
SELECT CONCAT(TABLE_SCHEMA, '.', TABLE_NAME)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA IN (${SCHEMAS}) AND TABLE_TYPE = 'BASE TABLE';
" | xargs -P ${PARALLEL_WORKERS} -I {} mysql -u${DB_USER} -p${DB_PASS} -h${DB_HOST} -N -e "
SET SESSION innodb_parallel_read_threads = 4;
SET SESSION transaction_isolation = 'READ-UNCOMMITTED';
SELECT '{}', COUNT(*) FROM {};
"
4. 실무 트러블슈팅 & 안전장치 (Deep Dive)
운영 중인 DB(또는 Replica 서버)에서 위와 같이 대량 테이블 COUNT(*)를 수행할 때 장애를 예방하기 위한 3가지 고급 팁입니다.
① 왜 실행 계획(EXPLAIN)에서 느린 테이블이 발생할까? (Secondary Index 유무 확인)
UNION ALL이나 병렬 스크립트를 돌렸을 때 유독 특정 테이블에서만 수십 분씩 멈춰 있는 경우가 있습니다. 해당 테이블의 EXPLAIN SELECT COUNT(*) FROM 테이블을 떠보면 type: index가 아니라 type: ALL 또는 클러스터드 인덱스(PRIMARY)를 스캔하고 있을 확률이 99%입니다.
- 원인: 테이블에 NOT NULL로 선언된 세컨더리 인덱스가 하나도 없거나, PK만 존재하는 테이블입니다. 클러스터드 인덱스는 리프 노드에 모든 컬럼의 실제 데이터(Row Data)를 품고 있어 세컨더리 인덱스보다 용량이 수십 배 큽니다.
- 해결: 이런 초거대 이력성 테이블은 정확한 COUNT(*) 대상에서 제외하고 MAX(id) - MIN(id)로 추산하거나, 배치로 미리 집계된 메타 테이블을 활용해야 합니다.
② MVCC Undo Log 탐색 오버헤드 줄이기 (READ UNCOMMITTED)
운영 중에 DML(INSERT, UPDATE, DELETE)이 활발히 일어나는 테이블에 COUNT(*)를 날리면, 기본 격리 수준인 REPEATABLE READ에서는 일관된 읽기(Consistent Read) 스냅샷을 맞추기 위해 변경 중인 페이지마다 Undo Log를 뒤져서 이전 버전을 재조합해야 합니다. 이로 인해 스캔 속도가 수십 배 느려집니다.
약간의 더티 리드(Dirty Read) 오차를 감수할 수 있는 대량 집계라면, 세션 격리 수준을 READ UNCOMMITTED로 낮추고 실행하면 Undo Log 탐색 비용을 완전히 제거하여 훨씬 빠르게 스캔을 마칠 수 있습니다.
-- Undo Log 참조 오버헤드를 제거하여 대량 테이블 COUNT(*) 속도 극대화
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
③ InnoDB 버퍼 풀 오염 완벽 차단 (innodb_old_blocks_time 튜닝)
수백 개의 테이블을 연속으로 풀 스캔할 때 기존 서비스의 핫 데이터(Hot Page)가 메모리에서 밀려나는 현상을 막으려면, 작업 직전에 innodb_old_blocks_time 설정을 확인해야 합니다.
- 기본값은 1000(1초)입니다. 만약 테이블 스캔 중 동일한 데이터 페이지에 여러 번 접근하더라도 1초 이내에 스캔이 지나가면 LRU 리스트의 Young 영역(Hot 영역)으로 승격되지 않고 즉시 버려집니다.
- 대량 COUNT(*) 작업 시에는 이 값을 일시적으로 5000(5초) 이상으로 높이거나, 가급적 트래픽이 없는 Replica(Read Only) 장비에서 작업을 수행하여 마스터 DB의 버퍼 풀을 보호하는 것이 정석입니다.
5. 요약 정리
- 수백 개 테이블을 UNION으로 묶어 COUNT(*)를 실행하면 단일 스레드 직렬 처리 + 임시 테이블 정렬(UNION 사용 시) + 대규모 디스크 I/O가 겹쳐 극단적인 슬로우 쿼리가 발생합니다.
- 대략적인 건수 파악이 목적이라면 직접 세지 말고 mysql.innodb_table_stats의 n_rows를 조회하세요. 0.05초 만에 부하 없이 끝납니다.
- 마이그레이션 검증 등 100% 정확한 건수가 필요하다면, UNION 대신 UNION ALL 동적 SQL을 생성하되, 대용량 환경에서는 OS 레벨(xargs -P 등)에서 멀티 커넥션으로 테이블을 병렬 처리하고 세션 단위로 innodb_parallel_read_threads와 READ UNCOMMITTED 격리 수준을 조합하는 것이 가장 빠르고 안전합니다.