❗ 해당 포스트는 MacOS 환경에서 MySQL 5.7 기준으로 작성되었습니다. 그리고 예제에서 사용되는 모든 테이블은 InnoDB 엔진을 사용합니다.

MySQL 성능 개선을 위한 프로파일링 2편

최근 웹 애플리케이션 서버의 API가 성능이 너무 낮아 튜닝을 해야 하는 상황이 찾아왔다. 해당 API는 유저가 생성한 트랜잭션의 목록을 반환한다. 병목 지점을 찾기 위해서는 먼저 부하 테스트를 수행해야 한다. 그 뒤에 웹 애플리케이션을 프로파일링 하고 나서 RDBMS를 프로파일링하는 것이 정석이다. 이 포스트는 RDBMS(MySQL) 프로파일링에만 집중하겠다.

이전 편에서는 슬로우 쿼리 로그를 활성화했다. 그 덕에 슬로우 쿼리를 쉽게 탐지할 수 있게 되었다. 이제 슬로우 쿼리를 개선하기 위해 실행 계획을 분석해보자.

---

1. 실행 계획 분석

쿼리의 실행 계획은 EXPLAIN Statement을 통해 분석할 수 있다. 자세한 설명은 공식 문서에서 확인할 수 있다. 간단한 예제를 사용해서 쿼리의 실행 계획을 분석해보자. 예제에 사용된 데이터베이스 덤프는 링크에서 내려받을 수 있다. 아래 예제는 dept_emp 테이블과 employees를 Inner Join하고 dept_no 컬럼으로 오름차순 정렬한 결과를 조회하는 쿼리이다. 쿼리 앞에 EXPLAIN을 붙였으므로 실제 쿼리 결과 대신 쿼리 실행 계획이 반환된다.

EXPLAIN
SELECT de.dept_no, de.emp_no, e.first_name, e.last_name
FROM dept_emp de, employees e
WHERE de.emp_no = e.emp_no
ORDER BY de.dept_no;

아래는 MySQL Workbench에서 위 쿼리를 수행했을 때 출력된 결과이다. 실행 계획의 각 컬럼에 대해서 알아보자.

id 컬럼

SELECT절에 부여되는 id이다. 쿼리에는 여러 개의 SELECT절이 포함될 수 있는데 각 SELECT절마다 고유한 ID가 부여된다. 위 예제는 하나의 SELECT만 사용돼서 id가 1인 레코드밖에 없다. 아래 처럼 SELECT절이 2개인 쿼리를 실행하면 id가 2까지 늘어나는 것을 확인할 수 있다.

EXPLAIN SELECT (SELECT COUNT(*) FROM employees);

select_type 컬럼

각 SELECT 절의 타입을 보여주는 컬럼이다. 해당 컬럼에는 여러 값이 올 수 있는데 공식 문서에서 어떤 값들이 표시될 수 있는지 확인할 수 있다.

select_type	의미
SIMPLE	UNION이나 SUBQUERY가 포함되지 않은 SELECT
PRIMARY	UNION이나 SUBQUERY가 포함되었을 때 가장 바깥쪽에 있는 SELECT
UNION	UNION 이 포함된 SELECT에서 2번째 이후의 SELECT (첫 번째 SELECT는 PRIMARY로 표시 됨)
DEPENDENT UNION	select_type이 UNION이면서 바깥의 SELECT 컬럼에 의존하는 SELECT이다. 내부 쿼리보다 외부 쿼리가 먼저 수행되어야 해서 효율이 낮다.
UNION RESULT	UNION 결과를 저장하는 임시 테이블
SUBQUERY	FROM절 이외에 사용된 SUBQUERY를 의미
DEPENDENT SUBQUERY	select_type이 SUBQUERY이면서 바깥족 SELECT 컬럼에 의존하는 SELECT이다. 내부 쿼리보다 외부 쿼리가 먼저 수행되어야 해서 효율이 낮다.
DERIVED	FROM 절에 사용된 SUBQUERY이며 임시 테이블이 생성된다(옵티마이저가 임시 테이블을 사용하지 않고 쿼리를 수행할 수 있으면 임시 테이블을 생성하지 않는 것 같다). 이렇게 생성된 임시 테이블에는 MySQL 5.6 버전 이상에서부터 인덱스가 자동으로 생성된다
MATERIALIZED	IN 절에 포함된 쿼리를 임시 테이블로 생성
UNCACHEABLE SUBQUERY	SUBQUERY의 결과를 캐시하지 못하는 SELECT이다. 매번 SUBQUERY를 실행해야 하기때문에 비효율적이다.
UNCACHEABLE UNION	UNCACHEABLE SUBQUERY와 마찬가지로 결과를 캐시하지 못하는 UNION SELECT

DERIVED에 대해서만 조금 더 알아보자. 아래 쿼리는 select_type으로 DERIVED 를 출력하기 위해 FROM절에 서브쿼리를 사용했는데 예상과 다른 결과가 나왔다.

EXPLAIN 
SELECT *
FROM (SELECT * FROM employees WHERE emp_no BETWEEN 10000 AND 20000) e, dept_emp de
WHERE e.emp_no = de.emp_no;

id가 모두 1인 것을 보니 하나의 SELECT로 병합된 것 같다. SHOW WARNINGS; 명령어를 사용해서 옵티마이저가 어떻게 쿼리를 수정했는지 알 수 있다. 아래는 옵티마이저가 위 쿼리를 수정한 결과이다 (SELECT 문은 각 컬럼에 ALIAS가 붙다 보니 너무 길어져서 * 로 대체했다). 쿼리를 보면 알겠지만 옵티마이저가 임시 테이블을 만들지 않고 똑똑하게 쿼리를 변경해서 수행했다.

select *
from `employees`.`employees` join `employees`.`dept_emp` `de`
where ((`employees`.`de`.`emp_no` = `employees`.`employees`.`emp_no`) and (`employees`.`employees`.`emp_no` between 10000 and 20000));

음.. 그러면 DERIVED를 어떻게 만들어 볼 수 있을까.. GROUP BY를 사용해서 DERIVED를 만들 수 있었다. 아래 예제를 보고 실행 계획을 살펴보자.

EXPLAIN
SELECT *
FROM (SELECT de.emp_no FROM dept_emp de GROUP BY de.emp_no) t, employees e
WHERE t.emp_no = e.emp_no;

3번 째 record를 보면 select_type이 DERIVED다. 1번 째 record를 보면 table 컬럼이 <drived2>이다. <>은 임시 테이블을 의미하고 <> 안에 숫자는 해당 임시 테이블을 만든 id를 표시한다. 즉 id가 2인(3번째 record) select 결과로 생성된 임시 테이블이란 것이다. 여기서는 임시테이블을 DRIVING TABLE(조인에서 첫 번째로 접근하는 테이블)로 사용했다. 그리고 employees 테이블을 DRIVEN TABLE(뒤이어 접근하는 테이블)로 사용한다. JOIN할 때 인덱스를 사용하기 위해서는 DRIVEN TABLE에만 인덱스가 있으면 되기 때문에 인덱스가 있는 employees 테이블이 DRIVEN TABLE이 된 것 같다. Inner JOIN은 어떤 테이블이든 DRIVING TABLE이나 DRIVEN TABLE이 될 수 있어서 옵티마이저가 이러한 실행 계획을 세운 것 같다. 그럼 아래와 같이 임시 테이블이 DRIVEN TABLE이 될 수 밖에 없는 쿼리를 수행해 보자.

EXPLAIN
SELECT *
FROM employees e LEFT OUTER JOIN (SELECT de.emp_no FROM dept_emp de GROUP BY de.emp_no) t 
ON t.emp_no = e.emp_no;

LEFT OUTER JOIN을 사용했기 때문에 왼쪽 테이블(employees)이 DRIVING TABLE이 될 수 밖에 없다. 따라서 임시 테이블은 DRIVEN 테이블이 된다. 이 때 눈여겨 볼만한 것은 임시 테이블에 가상의 인덱스 <auto_key0>가 자동 생성되었다는 것이다.

table 컬럼

해당 SELECT에 사용된 테이블이다. ALIAS를 붙였으면 ALIAS가 표시된다. 보통 실행 계획 레코드 개수는 쿼리에 사용된 테이블의 개수와 같다. 주의할 것은 <>로 둘러쌓인 테이블이다. select_type에서 설명했듯이 <derived2> 처럼 <>에 둘러쌓인 테이블은 임시 테이블이다. <>안의 숫자는 해당 임시 테이블을 만든 SELECT의 id를 표시한다.

type 컬럼

아주 중요한 컬럼이다. 쿼리가 테이블을 어떻게 읽는지 보여준다. 링크에서 자세한 정보를 얻을 수 있다. 아래 표는 위에 있을수록 효율이 좋은 방법이라고 나와있지만 상황에 따라 달라질 수 있는 것 같다.

type	의미
system	하나의 레코드만 가지고 있는 테이블
const	PRIMARY KEY나 UNIQUE KEY을 구성하는 모든 컬럼이 WHERE 동등(=, <=>) 조건으로 사용됨
eq_ref	조인에서 처음 읽은 테이블의 컬럼을 다음 테이블의 PRIMARY KEY나 UNIQUE KEY의 WHERE 동등(=, <=>) 조건으로 사용 가능. (PRIMARY KEY나 UNIQUIE KEY를 구성하는 모든 컬럼이 사용 되어야함)
ref	인덱스 컬럼이 WHERE 동등 조건(=, <=>)으로 사용 됨. 인덱스를 구성하는 모든 컬럼이 포함될 필요는 없음
fulltext	FULLTEXT 인덱스를 사용하는 쿼리이며 MATCH AGAINST 구문이 사용되어야 함
ref_or_null	ref type과 NULL 비교가 추가된 쿼리
index_merge	2개 이상의 인덱스가 사용된 쿼리. 각각의 인덱스로 결과를 만들어 낸 뒤 병합
unique_subquery	IN 절에 사용된 subquery가 unique한 값만 반환
index_subquery	IN 절에 사용된 subquery가 중복된 값을 반환
range	인덱스 컬럼을 사용해서 범위 검색을 하는 경우. =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, IN 연산자가 사용되었을 때
index	인덱스 풀 스캔
ALL	테이블 풀 스캔

---

필자는 실행 계획을 분석할 줄 모를 때 이름 때문에 index type을 가장 오해했었다. 이름만 봐서는 index를 효율적으로 써야 할 것 같은데 사실 index를 가장 비효율적으로 쓰는 방법이다. 인덱스의 모든 데이터를 읽기 때문이다. 다만 인덱스는 전체 테이블 데이터보다는 크기가 작아서 ALL보다는 효율이 높다. (클러스터링 인덱스를 풀 스캔하는 경우에는 ALL과 효율이 같다고 봐야 할 것 같다)

possible_keys

쿼리를 수행할 때 사용할 수 있는 인덱스 목록이다. 옵티마이저는 쿼리를 처리하는데 가장 비용이 적게 드는 인덱스를 통계 정보를 통해 예측해서 어떤 인덱스를 사용할지 결정한다.

key

옵티마이저가 쿼리를 수행하는 데 사용한 인덱스이다. index_merge 타입을 제외하고는 한 개의 인덱스만 표시된다.

key_len

인덱스가 다중 컬럼으로 구성되어 있을 때 몇 개의 컬럼이 실제로 인덱스로 사용되었는지 알려준다. 더 정확히는 인덱스 중 몇 바이트가 실제로 사용되었는지 표시한다.

ref

인덱스와 비교된 값을 표시한다. 상수라면 const가 표시되고 다른 테이블의 컬럼 값이면 테이블명.컬럼명 형식으로 표시된다. func일 때는 주의해야 한다. 비교 값들에 변형이 이루어 졌을수도 있기 때문이다. (타입 변환, 문자 집합 변환, 콜레이션 변환 등)

rows

쿼리를 수행하기 위해 검사해야하는 record의 숫자이다. 정확하지는 않고 옵티마이저가 통계 정보를 사용해서 예측한 값이다.

filtered

조건절에 의해서 스토리지 엔진이 의해 필터링된 record 개수의 퍼센티지이다. 100이라면 스토리지 엔진에서 인덱스를 활용해서 모든 필터링을 끝마친 것이다. 만약 40이라면 스토리지에서 반환한 레코드 중 60%가 MySQL 엔진에 의해서 필터링 되었다는 것이다. 이것도 정확한 값은 아니고 예측된 값이다.

extra

쿼리 수행에 추가적인 정보가 보이는 컬럼이다. 성능에 중요한 내용이 포함되어있다. 몇 가지만 알아보자

extra	의미
Using filesort	Order By를 처리하는 데 인덱스를 사용하지 못해서 MySQL 엔진에서 퀵소트로 수행됨
Using index	데이터 파일 참조 없이 인덱스만으로 쿼리 수행 가능(커버링 인덱스)
Using temporary	쿼리를 수행하는데 임시 테이블이 생성되었음
Using where	스토리지 엔진에서 조건절이 모두 필터링 되지 못하고 MySQL 엔진에서 추가로 필터링 작업이 이루어짐
Using join buffer (Block Nested Loop)	조인에 인덱스를 사용하지 못했을 때 조인 버퍼를 생성해서 조인을 처리

Using filesort, Using join buffer는 상당히 비효율적으로 쿼리가 수행되는 것을 의미한다. 가능하다면 적절한 인덱스를 생성해서 효율적으로 쿼리가 수행되도록 만들어야 한다. Using index는 데이터 파일 참조 없이 인덱스만으로 쿼리를 수행할 수 있기 때문에 상당히 효율적인 쿼리 수행 방법이다.

2. 결론

EXPLAIN Statement를 통해 쿼리의 실행 계획을 분석할 수 있다. 분석한 결과를 통해 쿼리를 개선할 수 있을 것이다. 다음 편에서는 성능 저하를 가져왔던 슬로우 쿼리를 개선해 보겠다.