DB 통계 정보의 노후화와 실행 계획 오류

증상 진단: 느려진 쿼리와 비정상적인 리소스 사용

갑자기 특정 SQL 쿼리의 실행 시간이 몇 배로 늘어나지 않았습니까? CPU 사용률이 정상 범위를 벗어나거나, 디스크 I/O가 비정상적으로 높아지는 현상이 관찰된다면, 데이터베이스 통계 정보의 노후화와 이로 인한 실행 계획 오류를 가장 먼저 의심해야 합니다. 이는 단순히 하드웨어 성능 부족처럼 보이지만. 실제 원인은 데이터베이스 옵티마이저가 잘못된 길을 선택하고 있기 때문입니다.

원인 분석: 옵티마이저의 눈이 흐려졌다

데이터베이스 옵티마이저는 쿼리를 가장 효율적으로 실행할 방법(실행 계획)을 수립하는 두뇌입니다. 이 두뇌가 결정을 내리는 핵심 자료가 바로 테이블, 인덱스의 행 수, 데이터 분포, 중복도 등을 기록한 ‘통계 정보’입니다. 이 통계 정보가 오래되어 현실을 반영하지 못하면, 옵티마이저는 마치 낡은 지도를 보고 길을 찾는 것과 같습니다. 예를 들어. 작은 테이블이라고 생각해 전체 스캔(full scan)을 선택했는데 특히는 수백만 행이 있는 대용량 테이블이라면, 시스템 성능은 즉시 붕괴됩니다. 실행 계획 오류는 바로 이러한 잘못된 정보에 기반한 비효율적인 실행 경로 선택을 의미합니다.

해결 방법 1: 통계 정보의 최신화

가장 기본적이고 효과적인 조치입니다. 모든 주요 RDBMS는 통계 정보를 수집하고 갱신하는 명령어를 제공합니다, 통계 정보 갱신은 일반적으로 부하가 적은 시간대에 수행하는 것이 원칙입니다.

Microsoft SQL Server에서의 조치

전체 데이터베이스 또는 특정 객체에 대한 통계 정보를 갱신할 수 있습니다. 운영 중인 시스템에서는 샘플링을 활용해 부하를 관리해야 합니다.

1. 특정 테이블의 통계 정보 갱신: SQL Server Management Studio(SSMS)에서 새 쿼리 창을 열고 다음 명령을 실행합니다.
   UPDATE STATISTICS [테이블명]; -- 기본 샘플링 사용
UPDATE STATISTICS [테이블명] [인덱스명] WITH FULLSCAN; -- 정확도 최대화 (부하 높음)
2. 데이터베이스 전체 통계 정보 갱신: 시스템 저장 프로시저를 실행합니다.
   EXEC sp_updatestats;
3. 자동 통계 업데이트 확인: 데이터베이스 속성의 옵션 메뉴에서 자동 통계 업데이트가 TRUE로 설정되어 있는지 확인합니다. 이 설정은 데이터 변경량이 임계값을 초과할 때 백그라운드에서 통계를 자동 갱신합니다.

MySQL / MariaDB에서의 조치

InnoDB 스토리지 엔진을 기준으로 안내합니다. ANALYZE TABLE 명령어가 핵심입니다.

1. 테이블 분석 실행: MySQL 클라이언트에 접속 후 다음을 실행합니다.
   ANALYZE TABLE [테이블명];
   실행 결과로 ‘Table’, ‘Op’, ‘Msg_type’, ‘Msg_text’를 확인하여 상태를 파악합니다. Msg_type이 ‘status’이고 Msg_text가 ‘OK’이면 성공입니다.
2. 자동 통계 갱신 설정: MySQL 5.6 이상/ MariaDB 10.0 이상에서는 innodb_stats_auto_recalc 시스템 변수가 기본적으로 활성화되어 있습니다. 확인 명령어:
   SHOW GLOBAL VARIABLES LIKE 'innodb_stats_auto_recalc';
3. 통계 정보 영구화 (MySQL 8.0+ / MariaDB 10.4+): 서버 재시작 후에도 통계 정보가 유지되도록 설정합니다.
   SET GLOBAL innodb_stats_persistent = ON;

주의사항: WITH FULLSCAN 옵션이나 ANALYZE TABLE은 대상 테이블의 모든 데이터를 읽어 정확한 통계를 생성하므로, 대용량 테이블에서는 상당한 I/O 부하와 시간이 소요됩니다. 반드시 운영 장비의 피크 시간을 피해 실행 계획을 수립해야 합니다.

해결 방법 2: 실행 계획 캐시 비우기 및 강제 재컴파일

통계 정보를 갱신했음에도 옵티마이저가 기존의 잘못된 실행 계획을 캐시에서 계속 사용할 수 있습니다. 이 경우 캐시를 제거하여 새로운 통계 정보로 실행 계획을 다시 수립하도록 강제해야 합니다.

SQL Server에서의 실행 계획 캐시 관리

1. 특정 쿼리 또는 전체 캐시 비우기:
   DBCC FREEPROCCACHE; — 전체 프로시저 캐시 비우기 (운영 시스템에서 신중히 사용)
   DBCC FREESYSTEMCACHE ('SQL Plans'); — SQL 실행 계획 캐시만 선택적 비우기
2. 쿼리 힌트를 이용한 재컴파일: 문제가 되는 쿼리 문장 끝에 힌트를 추가합니다.
   SELECT * FROM Orders WHERE OrderDate > '2023-01-01' OPTION (RECOMPILE);
   이 쿼리는 매번 실행 시 최신 통계를 기반으로 새 실행 계획을 생성합니다.
3. 저장 프로시저 강제 재컴파일: 프로시저를 생성 또는 실행할 때 옵션 지정.
   CREATE PROC usp_GetOrders ... WITH RECOMPILE;
EXEC usp_GetOrders WITH RECOMPILE;

MySQL에서의 실행 계획 캐시 및 테이블 최적화

1. 테이블 명시적 최적화: OPTIMIZE TABLE 명령은 데이터 파일 조각 모음과 통계 정보 갱신을 동시에 수행합니다. 주의해서 사용해야 합니다.
   OPTIMIZE TABLE [테이블명]; — InnoDB 테이블의 경우 온라인 DDL로 진행될 수 있으나 장시간 테이블 잠금이 발생할 수 있음.
2. 쿼리 캐시 비활성화 또는 플러시 (MySQL 5.7 이하): 쿼리 캐시 자체가 성능 병목이 되는 경우가 많아, MySQL 8.0부터는 제거되었습니다. 5.7 이하에서는 다음 명령으로 플러시 가능합니다.
   RESET QUERY CACHE;
FLUSH TABLES;

해결 방법 3: 실행 계획 분석 및 인덱스 전략 재검토

통계 갱신과 캐시 초기화 후에도 문제가 지속된다면, 실행 계획 자체를 심층 분석해야 합니다. 잘못된 인덱스 사용(인덱스 스캔 대신 테이블 스캔)이나 조인 순서 오류 등이 근본 원인일 수 있습니다.

실행 계획 획득 및 분석 절차

1. 실제 실행 계획 확인 (SQL Server): SSMS에서 실제 실행 계획 포함 아이콘을 활성화한 후 문제의 쿼리를 실행합니다. 그래픽 계획에서 두꺼운 화살표(많은 데이터 흐름), 비용이 높은 연산자(예: ‘Hash Match’, ‘Sort’)를 찾습니다. 연산자 위에 마우스를 올리면 예상 행 수와 실제 행 수의 큰 차이를 확인할 수 있으며, 이는 통계 정보 불일치의 직접적인 증거입니다.
2. 실행 계획 확인 (MySQL): EXPLAIN 또는 EXPLAIN ANALYZE(MySQL 8.0.18+) 명령을 사용합니다.
   EXPLAIN FORMAT=TREE SELECT * FROM ...;
   분석 포인트는 type 컬럼(접근 방식: ALL은 풀 스캔, ref, range는 인덱스 사용), rows 컬럼(예측 행 수), Extra 컬럼(‘Using filesort’, ‘Using temporary’는 주의 필요)입니다.
3. 누락된 인덱스 제안 활용 (SQL Server): DMV(Dynamic Management View)를 조회하여 시스템이 제안하는 인덱스를 확인합니다.
   SELECT * FROM sys.dm_db_missing_index_details;
SELECT * FROM sys.dm_db_missing_index_group_stats;
   이 정보를 바탕으로 고려된 인덱스를 생성할 수 있으나, 무분별한 인덱스 생성은 쓰기 성능을 저하시킬 수 있으므로 신중히 판단해야 합니다.

주의사항 및 예방 정책

통계 정보 관리는 반응형 조치보다 사전 예방이 더 중요합니다, 다음 정책을 수립하여 시스템을 안정화하십시오.

• 정기적 유지 관리 작업 스케줄링: sql server 에이전트 작업이나 linux cron job을 이용해 주말 또는 야간에 주요 테이블에 대한 통계 업데이트 작업을 자동화하십시오.
• 샘플링 전략 수립: 테라바이트 급 대용량 테이블의 경우 update statistics ... WITH SAMPLE 30 PERCENT;와 같이 적절한 샘플링 비율을 사용하여 정확도와 성능 사이의 균형을 찾으십시오.
• 변화 임계값 모니터링: SQL Server의 자동 통계 업데이트는 약 20%의 행이 변경되면 트리거됩니다. 이 임계값은 모든 경우에 최적이 아닐 수 있습니다. 변화가 심한 테이블은 더 짧은 주기로 모니터링하십시오.
• 인덱스 재구성/재구축 주기 설정: 인덱스 조각화가 심해지면 통계 정보 갱신만으로는 성능이 회복되지 않습니다. 인덱스 재구성(REORGANIZE) 또는 재구축(REBUILD) 작업을 통계 업데이트와 함께 정기적으로 수행하십시오.

전문가 팁: 실행 계획이 갑자기 악화되는 ‘실행 계획 회귀(Plan Regression)’ 문제는 프로덕션 환경에서 가장 까다로운 문제 중 하나입니다. SQL Server에서는 ‘쿼리 저장소(Query Store)’ 기능을 활성화하십시오. 이를 통해 특정 시점의 성능 좋은 실행 계획을 강제로 지정(Plan Forcing)할 수 있어, 통계 정보 갱신 후 발생할 수 있는 불안정성을 즉시 롤백하는 최후의 보험으로 활용 가능합니다. 설정 경로는 데이터베이스 속성 -> 쿼리 저장소 -> 작업 모드를 READ_WRITE로 변경입니다.