MySQL 성능 최적화

yoon4360 2025. 4. 1. 23:16

프로젝트 배경

악취 포집기 모니터링 앱은 포집 장비로부터 전송되는 악취, 온도, 습도, 풍향 등 다양한 센서 데이터를 수집하여

장비 상태를 실시간으로 확인하고 이상 발생 시 알림을 전송하는 모바일 기반 시스템이다.

이 데이터는 alarm_data 테이블에 저장되며, 하루 수천 건 이상의 알람이 발생하므로 빠른 조회 성능과 대용량 처리가 중요한 요소이다.

초기 테이블 구조

CREATE TABLE alarm_data (
  ad_idx INT AUTO_INCREMENT PRIMARY KEY,
  di_idx INT,
  ac_idx INT,
  reg_date DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (di_idx) REFERENCES device_info(di_idx),
  FOREIGN KEY (ac_idx) REFERENCES alarm_code(ac_idx)
);

reg_date에 인덱스가 없어 정렬 성능이 저하된다.
파티셔닝이 적용되지 않았다.
di_idx, ac_idx 조건으로 WHERE + ORDER BY 조합 시 성능이 급격히 저하된다.

문제해결

1. 데이터 더미 생성 및 성능 측정

테스트용 10만 건 더미 데이터 삽입

-- device_info, alarm_code 테이블도 먼저 생성 및 삽입
INSERT INTO alarm_data (di_idx, ac_idx, reg_date)
SELECT 
    FLOOR(1 + (RAND() * 10)),
    FLOOR(1 + (RAND() * 5)),
    NOW() - INTERVAL FLOOR(RAND() * 365) DAY
FROM ... -- 1,000건 x 10 x 10 방식으로 100,000건 삽입

초기 쿼리 성능 측정

SET profiling = 1;

SELECT * FROM alarm_data 
WHERE di_idx = 5 AND ac_idx = 2 
ORDER BY reg_date DESC;

SHOW PROFILES;

초기 조회 시간: 0.004333 초

2. 성능 최적화 전략 적용

인덱스 추가

ALTER TABLE alarm_data 
ADD INDEX idx_alarm_data_reg_date (reg_date),
ADD INDEX idx_alarm_data_device_alarm (di_idx, ac_idx);

SELECT * FROM alarm_data WHERE di_idx = ? AND ac_idx = ? ORDER BY reg_date DESC

형태의 쿼리가 반복적으로 사용되었다.
하지만 reg_date, di_idx, ac_idx에 인덱스가 없어 풀 테이블 스캔이 발생했고, 데이터가 많아질수록 조회 속도가 급격히 느려졌다.

이를 해결하기 위해 단일 인덱스와 복합 인덱스를 도입하여 검색 조건에 맞는 데이터만 빠르게 조회하도록 개선하였다.
특히 (di_idx, ac_idx) 복합 인덱스는 다중 컬럼 WHERE 조건에 대한 성능을 크게 높여줄 수 있어 도입하였다.

파티셔닝을 위한 기본키 변경 및 외래키 제거

ALTER TABLE alarm_data 
DROP FOREIGN KEY alarm_data_ibfk_1,
DROP FOREIGN KEY alarm_data_ibfk_2;

ALTER TABLE alarm_data 
DROP PRIMARY KEY, 
ADD PRIMARY KEY (ad_idx, reg_date);

MySQL은 파티셔닝을 적용할 경우, 해당 테이블에 설정된 모든 외래키 제약조건을 허용하지 않는다.

처음에는 이를 몰랐어서 에러를 여러번 마주했었다.
또한 파티셔닝 컬럼(reg_date)이 기본키 또는 모든 UNIQUE 키의 일부가 되어야 하므로,

기존 기본키(ad_idx)를 ad_idx + reg_date의 복합키로 변경했다.
외래키는 데이터 정합성에 도움이 되지만, 조회 성능을 개선을 위해 정합성은 응용 계층에서 처리하도록 하고, 제약조건은 제거했다.

연도 기반 파티셔닝 적용

ALTER TABLE alarm_data
PARTITION BY RANGE (YEAR(reg_date)) (
    PARTITION p0 VALUES LESS THAN (2024),
    PARTITION p1 VALUES LESS THAN (2025),
    PARTITION p2 VALUES LESS THAN (2026),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

alarm_data 테이블은 시간 순으로 알람 데이터가 지속적으로 쌓이는 구조이며 시간이 지남에 따라 데이터 양이 폭증할 수밖에 없다.

연도별로 데이터를 분리하면 특정 기간의 데이터만 조회할 때 불필요한 파티션은 제외되고 조회 범위를 좁힐 수 있다.

특히 WHERE reg_date BETWEEN '2025-01-01' AND '2025-12-31' 같은 조건에서

해당 파티션만 조회하게 되어 속도가 크게 향상될 수 있다.

3. 최적화 후 성능 측정

SET profiling = 1;

SELECT * FROM alarm_data 
WHERE di_idx = 5 AND ac_idx = 2 
ORDER BY reg_date DESC;

SHOW PROFILES;

이후 조회 시간: 0.001990 초
성능 개선율: 약 54% 향상

마무리

이번 작업을 통해 alarm_data 테이블의 성능 병목을 해결하기 위해 인덱스 최적화와 연도 기반 파티셔닝을 적용했다.

더미 데이터를 활용하여 성능 테스트를 수행했고, 향후 실제 장비 데이터가 도입되었을 때도 문제없이 대응할 수 있도록 구조를 마련했다.

앞으로 조회 외에 데이터 삭제, 보관 전략, 샤딩 또는 아카이빙 설계 등도 고려할 예정이다.
또한, 파티셔닝 적용 과정에서 발생한 외래키 관련 에러는 별도 포스팅에서 상세히 다룰 계획이다.