[Mysql] slow query 발생에 의한 mysqld 프로세스 cpu 사용률 급증 이슈

현재 운영중인 서버는 멀쩡하나, 클라이언트가 튕기거나 기능이 온전하게 작동하지 않는 이슈가 있다.

모니터링을 해보니, mysqld 프로세스의 cpu 점유율이 100%에 달하였으며, 해당 상태가 10~40분 정도 지속되는 현상이 확인되었다.

 

다행히도 서비스는 죽지 않고 일정 시간이 지난 후 자연 해소되었으며,

구글링과 gpt의 도움을 받아 원인을 분석해보니 지속,반복적으로 실행되는 slow 쿼리(aes_decrypt 함수 포함) 등으로 인하여 cpu 사용량이 급증하는 것으로 파악하였다.

 

아래는 해당 slow 쿼리에 대한 분석 및 대응 방향이다.

🔎 slow 쿼리 분석

1. 주요 특징

• 대량의 데이터를 조회하는 복잡한 SELECT 쿼리
• AES_DECRYPT, FROM_BASE64 연산이 다수 포함되어 있어 CPU 부하 증가
• LEFT JOIN을 사용하여 user_team과 user 테이블을 조인
• 필터 조건: WHERE this_.team_idx=7 AND this_.pc_update_date >= '2025-03-12 14:25:57.742'
• pc_update_date가 특정 시점 이후인 데이터를 조회하여 대량의 데이터가 검색될 가능성 높음

---

✅ 해결 방안

1. 인덱스 최적화

🔍 문제점

• team_idx, pc_update_date가 WHERE 절에서 사용되므로 적절한 인덱스가 없을 경우 풀 테이블 스캔 발생
• ON this_.user_idx = user1_.idx 조인 조건에서 user_idx에도 인덱스 필요

🔧 해결 방법
1️⃣ 인덱스 추가

ALTER TABLE user_team ADD INDEX idx_team_pc_update (team_idx, pc_update_date); 
ALTER TABLE user ADD INDEX idx_user_idx (idx);

• team_idx, pc_update_date를 복합 인덱스로 생성하여 범위 검색 최적화
• user.idx에도 인덱스 추가하여 조인 성능 개선

2️⃣ 인덱스 확인

EXPLAIN ANALYZE 
SELECT ... FROM user_team this_ 
LEFT OUTER JOIN user user1_ 
ON this_.user_idx = user1_.idx 
WHERE this_.team_idx = 7 AND this_.pc_update_date >= '2025-03-12 14:25:57.742';

• 실행 계획을 확인하여 Using index가 표시되는지 확인

---

2. AES_DECRYPT 성능 최적화

🔍 문제점

• CONVERT(AES_DECRYPT(FROM_BASE64(this_.mobile), @aessalt) USING utf8mb4)와 같은 암호화 해제 연산이 다수 포함됨
• AES_DECRYPT는 CPU 부하를 증가시킴 (특히 대량의 데이터 처리 시)
• 해당 컬럼이 인덱스 적용 불가능 → 암호화된 데이터 조회가 비효율적

🔧 해결 방법
1️⃣ 암호화된 데이터 별도 저장

• mobile, phone, birthday 등 조회 빈도가 높은 필드는 복호화된 값을 별도 컬럼으로 저장하여 직접 조회 가능하게 변경

ALTER TABLE user_team ADD COLUMN mobile_plaintext VARCHAR(255); 
ALTER TABLE user ADD COLUMN mobile_plaintext VARCHAR(255);

• 데이터 삽입/업데이트 시 복호화된 데이터를 별도 컬럼에 저장

UPDATE user 
SET mobile_plaintext = CONVERT(AES_DECRYPT(FROM_BASE64(mobile), @aessalt) USING utf8mb4);

• 이후 복호화 없이 mobile_plaintext 컬럼을 직접 조회하여 CPU 부하 감소

2️⃣ 뷰(View) 사용

• 암호화 데이터를 매번 복호화하는 대신 뷰(View)를 생성하여 캐싱 효과

CREATE VIEW decrypted_users 
AS SELECT idx, account, CONVERT(AES_DECRYPT(FROM_BASE64(mobile), @aessalt) USING utf8mb4) AS mobile 
FROM user;

• 이후 쿼리에서 decrypted_users 뷰를 사용

---

3. 조인(Join) 최적화

🔍 문제점

• LEFT OUTER JOIN user user1_ ON this_.user_idx=user1_.idx가 불필요한 데이터를 조회할 가능성
• user_team과 user 테이블 크기가 크면 조인으로 인해 CPU 및 메모리 사용량 급증

🔧 해결 방법
1️⃣ 필요한 데이터만 조회하도록 JOIN 최적화

SELECT this_.idx, this_.create_date, this_.pc_status, user1_.account, user1_.email 
FROM user_team this_ 
LEFT JOIN user user1_ 
ON this_.user_idx = user1_.idx 
WHERE this_.team_idx = 7 AND this_.pc_update_date >= '2025-03-12 14:25:57.742';

• 실제 필요한 컬럼만 조회 (SELECT * 지양)
• 조인된 user1_ 테이블에서 필요한 컬럼만 선택하여 불필요한 연산 줄이기

2️⃣ 조인 방식 변경

• MySQL에서는 Nested Loop Join을 수행하기 때문에 user_team의 필터링 후 user 테이블과 조인하는 것이 더 효율적
• 임시 테이블 또는 서브쿼리로 필터링된 데이터를 먼저 생성

SELECT this_.idx, this_.create_date, user1_.account 
FROM
	(SELECT idx, create_date, user_idx 
    FROM user_team 
    WHERE team_idx = 7 AND pc_update_date >= '2025-03-12 14:25:57.742') 
this_ JOIN user user1_ 
ON this_.user_idx = user1_.idx;

• 이렇게 하면 먼저 필터링된 데이터에서 조인을 수행하여 성능 향상 가능

---

4. 쿼리 캐싱 및 DB 설정 최적화

🔍 문제점

• 같은 쿼리가 반복적으로 실행되면 불필요한 연산 반복
• mysqld의 설정이 최적화되지 않은 경우 메모리 및 CPU 사용량 증가

🔧 해결 방법
1️⃣ MySQL 캐싱 활성화

• query_cache_size 확인 후 캐시 활성화 (MySQL 5.x에서 사용 가능, 8.x는 Redis 추천)

SHOW VARIABLES LIKE 'query_cache_size'; SET GLOBAL query_cache_size = 128000000;

• MySQL 8 이상이라면 Redis/Memcached 사용하여 캐싱
• 예를 들어, 자주 조회되는 데이터는 Redis에 저장 후 DB 부하 감소

2️⃣ MySQL 설정 튜닝

• my.cnf 설정 변경하여 성능 최적화 (서버의 스펙에 따라 적절하게 설정)

innodb_buffer_pool_size = 2G 
query_cache_size = 128M 
thread_cache_size = 16 
max_connections = 200

• 특히 innodb_buffer_pool_size를 RAM의 50~70%로 조정하면 성능 개선 가능

---

🚀 결론: 우선 적용할 해결책

✅ 즉각 적용

1. 복합 인덱스 추가
2. EXPLAIN 실행하여 쿼리 실행 계획 분석
3. AES_DECRYPT 컬럼을 복호화 없이 직접 저장할 수 있도록 데이터 구조 변경
4. 불필요한 SELECT * 제거 및 필요한 컬럼만 조회
5. LEFT JOIN 최적화 (필터링 후 조인 수행)

✅ 추가 적용

1. Redis/Memcached 캐싱 도입하여 반복적인 조회 최소화
2. DB 설정 튜닝 (innodb_buffer_pool_size 조정)
3. mysqld의 CPU 사용률 지속 모니터링 (SHOW PROCESSLIST)