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

쿼리 관련 이슈가 있을 것이다

비효율적인 쿼리로 인해 인덱스를 타지 않고 풀테이블 스캔이 발생하면, CPU 사용률이 급격히 증가하게 됩니다. 이 중 특히 서브쿼리(Subquery)가 중첩되는 문제는 성능저하의 대표적인 원인 중 하나가 아닐까 합니다.

서브쿼리?

서브쿼리(Subquery)는 다른 쿼리 내부에 포함된 Select 문을 의미합니다. 일반적으로 Where, From, Select, Having 절 등에서 사용됩니다.

SELECT name
FROM employees
WHERE id IN (
  SELECT employee_id
  FROM salaries
  WHERE salary > (
    SELECT AVG(salary)
    FROM salaries
  )
);

실행 순서는 가장 안쪽부터 외부까지입니다.

1. 가장 안쪽은 전체 salaries 테이블에서 평균 급여를 계산합니다.

2. 중간 서브 쿼리는 해당 평균보다 높은 employee_id를 추출합니다.

3. 외부 쿼리는 위 ID를 기준으로 직원 이름을 조회합니다.

서브 쿼리의 성능은?

MySQL엔진에서는 서브쿼리를 분석해 스칼라 서브쿼리(Scalar Subquery), 상관 서브쿼리(Correlated Subquery) 등으로 분류하고 그에 따라 실행 전략이 달라집니다.

서브쿼리는 매 쿼리마다 반복적으로 실행될 수 있어 성능상 큰 부담이 될 수 있습니다. 특히 상관 서브쿼리는 외부 쿼리의 각 행마다 실행되므로 CPU 낭비가 큽니다. 아래에서 두가지 쿼리에 대해서 간단하게 설명하겠습니다.

스칼라 서브쿼리(Scalar Subquery)

단일 행, 단일 컬럼을 반환하는 서브쿼리입니다. WHERE column = (SELECT ...) 과 같은 형태에서 자주 사용됩니다.

SELECT name
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
);

위 서브쿼리는 평균 급여 단일 숫자를 반환하는 쿼리입니다. 스칼라 서브 쿼리는 옵티마이저가 가능할 경우 한 번만 계산하도록 변환할 수 있습니다. 반환값이 만약 여러개가 나온다면? "Subquery returns more than 1 row" 오류가 발생합니다.

상관 서브쿼리(Correlated Subquery)

외부 쿼리의 컬럼을 참조하는 서브 쿼리입니다. 외부 쿼리의 각 행마다 서브쿼리가 실행됩니다.

SELECT e.name
FROM employees e
WHERE e.salary > (
  SELECT AVG(s.salary)
  FROM salaries s
  WHERE s.department_id = e.department_id
);

s.department_id = e.department_id 조건 때문에, e의 각 행마다 서브 쿼리가 반복실행되며, 성능에 매우 취약합니다. 이때 MySQL의 옵티마이저는 다음과 같은 기법으로 이를 최적화하려고 시도합니다.

 

• Materialization
• Semi-Join 변환

1. Materialization

서브쿼리를 마치 테이블처럼 한 번만 실행하고 결과를 메모리에 유지합니다. 주로 상관 서브쿼리가 아닐 때 사용이 가능합니다. 방금 설명한 한가지 row만 나올 수 있게 합니다.

2. Semi-Join 변환

SELECT name
FROM employees
WHERE department_id IN (
  SELECT id
  FROM departments
  WHERE region = 'EU'
);

 

내부적으로 JOIN으로 변환되어 성능을 향상시킬 수 있습니다.

서브쿼리가 계속해서 발생하고 있다면, 그 서브쿼리의 동작을 이해해야합니다.

조인 미스에 관해서

옵티마이저가 잘못된 수서를 선택하거나, 사용자가 비효율적인 순서를 강제로 지정하게 되었다면 발생하게 됩니다. MySQL의 옵티마이저는 내부적으로 통계, 인덱스 정보 그리고 테이블 크기 등을 고려해 가장 효율적인 조인 순서를 선택합니다.

특히 옵티마이저는 통계 정보가 부정확하거나 누락된 경우 발생하고, 옵티마이저는 작은 테이블부터 읽는 것이 효율적입니다. 하지만, 잘못된 인덱스 구조로인해 큰 테이블부터 스캔하게 되었을때 발생합니다.

사용자가 STRAIGHT_JOIN 등으로 조인 순서를 강제했을 경우 발생하며, Derived Table은 미리 물리적으로 만들어져 비용이 커지게 됩니다.

SELECT *
FROM big_table b
JOIN small_table s ON b.id = s.ref_id;

big_table이 수백만 건이고, s.ref_id에 인덱스가 없어 먼저 big_table을 풀스캔하게 된다면 CPU/메모리 모두 낭비됩니다. 반대로, small_table을 먼저 읽어서 ref_id 목록을 얻고, 그 값들로 big_table에 인덱스를 타고 접근해야 더 효율적이게 될 것입니다.