MySQL 쿼리 최적화 팁 몇 가지

쿼리 성능을 높이려면 데이터 스캔 범위가 작아야 하고, 그러려면 최우선적으로 쿼리가 인덱스를 효율적으로 사용해야 한다.

인덱스를 잘 사용하면 스캔 범위를 줄이는 것은 물론, 심지어 인덱스만으로 데이터를 조회할 수도 있다.

따라서, 쿼리 최적화를 위해서는 항상 explain 문으로 쿼리 실행 계획(query execution plan)을 확인하는 것이 좋다.

쿼리 실행 계획을 참고하여 쿼리를 최적화하는 팁 몇 가지를 정리해보았다.

 

• 아래 예제들에서는 2023.12.09 - [DB] - MySQL 샘플 DB 적재의 employees 데이터를 사용함

 

Visual Explain (MySQLWorkbench)

explain 문으로 쿼리 실행 계획을 확인하면, 어떤 인덱스를 사용하고 어느 정도의 스캔이 발생하는지, 어떤 순서로 join이 되는지 등의 정보를 알 수 있다. 그런데, explain 결과가 테이블 형태의 텍스트여서 내용을 해석하기 쉽지 않다.

explain 결과 예시

 

MySQLWorkbench의 visual explain 기능을 사용하면 explain 결과를 한 눈에 파악할 수 있다.

Full Table Scan과 같이 성능에 악영향을 끼칠 위험이 있는 부분은 붉은색으로 표시되기도 한다.

Visual Explain 예시

 

visual explain은 쿼리문에 커서를 둔 상태에서 (맥 기준) Cmd+Option+X 를 누르면 실행된다.

 

최적화 예시

아래와 같이 employees 테이블에서 hire_date가 "2000-01-01" 이후인 직원들을 조회해보았다.

SELECT *
FROM employees.employees
WHERE hire_date >= '2000-01-01';

 

실행시간이 0.1초로 아주 길지는 않았지만, visual explain을 확인해보면 Full Table Scan이 발생하고 있다.

 

위의 Full Table Scan은 WHERE 절에서 사용한 hire_date 컬럼에 인덱스가 없기 때문인데, 다음과 같이 인덱스를 추가하고 다시 확인해보면 Full Table Scan 대신 Index Range Scan이 사용된 것을 볼 수 있다. 실행시간도 0.001초로 단축되었다.

ALTER TABLE employees.employees ADD INDEX idx_hire_date (hire_date);

 

인덱스 컬럼에 함수 사용 지양

WHERE 절에 사용한 컬럼에 인덱스가 있더라도, 이 컬럼이 함수 인자로 쓰이면 인덱스를 사용할 수 없다. 예를 들어, TRIM(text) 같이 text 컬럼에 함수를 사용하면, text 컬럼의 인덱스가 있더라도 무시된다. 꼭 함수가 아니더라도 emp_no + 1 = 100000처럼 덧셈/뺄셈 같은 연산이 들어가도 인덱스를 사용할 수 없다.

 

이렇게 잘못 작성된 쿼리는 레거시 코드의 날짜 관련 조건절에서 상당히 많이 접했었다.

예를 들어, "근속년수가 24년 미만인 직원들을 조회"하기 위해 다음 쿼리를 생각할 수 있다.

SELECT *
FROM employees.employees
WHERE TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) < 24;

 

근속년수를 계산하기 위해 TIMESTAMPDIFF(YEAR, hire_date, CURDATE())를 사용했는데, hire_date 컬럼에 인덱스가 있지만 함수 인자로 사용되어 인덱스가 무시된다. 그 결과 다음과 같이 Full Table Scan이 발생한다.

 

부등식 왼쪽의 TIMESTAMPDIFF 문은 CURDATE() - hire_date로 계산되는데, hire_date와 24의 위치를 바꿔서 부등식 한 쪽에 hire_date만을 남겨두면 문제가 해결된다. 수정된 쿼리는 다음과 같으며, Index Range Scan으로 바뀐 것을 확인할 수 있다.

SELECT *
FROM employees.employees
WHERE hire_date > CURDATE() - INTERVAL 24 YEAR;

 

가상 컬럼(Virtual Column) 인덱스 활용

"입사 당시 만 나이가 21살 미만인 직원을 조회"하기 위해 다음과 같이 쿼리할 수 있다.

SELECT *, TIMESTAMPDIFF(YEAR, birth_date, hire_date) hire_age
FROM employees.employees
WHERE TIMESTAMPDIFF(YEAR, birth_date, hire_date) < 21;

 

위 쿼리는 사용할 수 있는 인덱스가 없어서 Full Table Scan이 발생하는데, 앞선 예제와는 다르게 TIMESTAMPDIFF 함수를 제거해도 Index Range Scan을 사용할 수 없다. 부등식을 컬럼 1개 대 고정값의 식, "컬럼 < 상수" 같은 식으로 만들 수 없기 때문이다.

 

이럴 때에는 테이블에 hire_age 컬럼을 추가해서 hire_date - birth_date 결과를 넣어줘야 인덱스 적용이 가능하다.

그런데, 이렇게 계산된 값을 테이블의 실제 컬럼으로 추가하면 다음과 같은 이슈가 발생할 수 있다.

 

1. 기존 데이터 마이그레이션 필요
   -> 조금 귀찮지만 큰 문제는 아님
   
   
2. 레코드를 추가하거나 birth_date, hire_date를 업데이트하는 쿼리에 hire_age 값도 계산해서 넣도록 변경 필요
   -> 추가 및 업데이트 코드가 파편화돼있다면 버그 발생 위험이 커짐
   
   
3. DB에서 직접 birth_date나 hire_date를 변경하게 되면 hire_age의 데이터 정합성이 깨짐
   -> 이런 일이 있으면 안 되겠지만, 가끔씩 일어날 때 파장이 큰 경우가 있음

이런 이유에서 hire_age를 실제 컬럼 대신 생성된 컬럼(Generated Column)으로 만드는 것이 더 이점이 있다.

TIMESTAMPDIFF(YEAR, birth_date, hire_date) 표현식을 generated column으로 추가하면, 레코드를 추가하거나 birth_date, hire_date가 업데이될 때 hire_age 컬럼을 직접 업데이트할 필요가 없어진다. 또한, generated column에 인덱스를 적용할 수도 있다.

generated column에는 virtual과 stored 두 가지 방식이 있다. virtual 타입은 표현식 결과를 디스크에 저장하지 않고, 쿼리 런타임에 매번 계산하는 방식이다. 쿼리 시간이 stored 타입보다 오래 걸리지만 저장공간이 늘어나지 않고, 레코드가 추가되거나 표현식에 포함된 종속 컬럼(birth_date, hire_date)이 업데이트될 때에도 아무 부하가 없는 장점이 있다. stored 타입은 반대로 쿼리 시간은 줄어들지만, 저장공간이나 insert 및 update 시 부하 측면에서 단점이 있다.

경험상 가상 컬럼(Virtual Column) + 인덱스를 추가하는 것이 매우 유용할 때가 많았다.

 

hire_age 가상 컬럼과 인덱스를 추가하는 쿼리는 다음과 같다.

ALTER TABLE employees.employees
  ADD COLUMN hire_age INT UNSIGNED GENERATED ALWAYS AS (TIMESTAMPDIFF(YEAR, birth_date, hire_date)) VIRTUAL,
  ADD INDEX idx_hire_age (hire_age);

 

이제 조건절에 hire_age 컬럼을 사용하면, idx_hire_age 인덱스가 적용되어 쿼리 실행이 이루어진다.

SELECT *
FROM employees.employees
WHERE hire_age < 21;

 

참고로 MySQL 8.0에서는 생성된 컬럼을 만들지 않아도 표현식 자체를 인덱스로 추가할 수 있다.

 

쿼리 최적화를 위한 인덱스 구성 규칙

쿼리에서 인덱스를 최대한 활용하기 위한 인덱스 구성 규칙은 다음과 같다.

 

1. WHERE 절의 컬럼들을 인덱스에 추가
   1. "컬럼 = 고정값" 조건에 사용된 컬럼들을 모두 인덱스에 포함한다. (순서 무관)
      조건식들이 AND로 연결되어 있어야 하며, OR 조건이 있는 경우에는 Index Merge 여부에 따라 최적화가 가능하다.
   2. 부등호, BETWEEN과 같은 범위 조건에 사용한 컬럼을 한 개까지 인덱스에 포함할 수 있다.
2. GROUP BY 절의 컬럼들을 순서대로 인덱스에 추가
   (1-2. 범위 조건 컬럼이 인덱스에 쓰이지 않았다면)
   
   
3. ORDER BY 절의 컬럼들을 순서대로 인덱스에 추가
   (1-2. 범위 조건 컬럼, 2. GROUP BY 컬럼이 인덱스에 쓰이지 않았다면)

 

GROUP BY 쿼리 최적화 예시

GROUP BY가 포함된 쿼리는 임시 테이블(temporary table) 사용으로 인해 쿼리 성능이 떨어지는 경우가 종종 있는데, 위의 규칙에 따라 GROUP BY 컬럼을 인덱스에 포함시킴으로써 임시 테이블을 피할 수 있다.

예를 들어, 마케팅 부서(dept_no='d001')의 연도별 입사자 수를 다음 쿼리로 조회했다고 하자.

SELECT YEAR(from_date), COUNT(*)
FROM employees.dept_emp
WHERE dept_no = 'd001'
GROUP BY 1;

 

explain 결과에서 보이듯이 임시 테이블(tmp table)이 사용되고 있다.

이 때, 위 인덱스 구성 규칙에 따라 다음과 같이 인덱스를 추가하면, 임시 테이블 없이 쿼리가 실행된다.

ALTER TABLE employees.dept_emp
  ADD INDEX idx_dept_no_from_date_year(dept_no, (YEAR(from_date))); # note: 함수 기반 인덱스는 MySQL 8.0 이전에는 불가

 

YEAR(from_date)가 포함된 idx_dept_no_from_date_year 인덱스를 사용함으로써 임시 테이블이 사라진 것을 확인할 수 있다.

 

참고: https://mysql.rjweb.org/doc.php/index_cookbook_mysql

MySQL: Building the best INDEX for a given SELECT

 

서브쿼리로 JOIN + GROUP BY 쿼리 최적화 (explode-implode 케이스)

FROM 절의 원본 테이블에 1-to-many 또는 many-to-many 관계의 테이블을 JOIN한 후에, GROUP BY로 원본 테이블 레코드 별 집계 결과를 쿼리하는 경우가 많이 있다.

예를 들어, 각 부서의 직원 수를 다음과 같이 조회할 수 있다.

SELECT d.*, COUNT(*)
FROM employees.departments d
JOIN employees.dept_emp de USING(dept_no) # explode
GROUP BY dept_no; # implode

 

이런 방식의 쿼리는 원본 테이블(departments)과 JOIN 테이블(dept_emp)의 JOIN 결과가 폭발적으로 늘어나면서 쿼리 성능이 매우 떨어질 수 있다.

그럴 때에는 JOIN + GROUP BY 대신 SELECT 절에 서브쿼리를 사용하면 성능이 개선될 수 있다.

SELECT d.*,
  (
    SELECT COUNT(*)
    FROM employees.dept_emp de
    WHERE de.dept_no = d.dept_no
  ) count
FROM employees.departments d;