[MySQL] 실행계획 알아보기

 

이번에 <SQL 레벨업>을 읽으면서 DBMS 아키텍처에 대해서도 배우게 되고 실행 계획의 중요성에 대해 알게 되면서 최근 프로젝트에 사용했었던 MySQL에서는 실행 계획을 어떻게 살펴볼 수 있는지에 대해 글로 남겨보려고 한다.

 

 

들어가기 전에

DBMS 아키텍처 내에는 성능에 중요한 영향을 미치는 모듈인 쿼리 평가 엔진이 있다. 쿼리 평가 엔진은 SQL 구문을 처음 읽어들여서 분석하고, 어떤 순서로 데이터에 접근할지를 결정한다. 이때 결정되는 계획을 실행 계획이라고 하며 이 실행 계획에 따라 데이터에 접근하는 방법을 접근 메서드(access method)라고 한다. 

 

그렇다면 이 실행 계획은 왜 중요할까? 

옵티마이저가 선택하는 실행 계획이 언제나 최적의 방법이지 않기 때문이다. DB 성능의 최후의 튜닝 수단은 실행 계획을 수동으로 변경해주는 것이다. 

 

 

DBMS의 쿼리 처리 흐름

출처 : <SQL 레벨업>

⒈ 파서(parser)

SQL 구문을 분석하고 정형적인 형식으로 변환하여 DBMS 내부에서 후속 처리를 효율적으로 할 수 있게 한다.

 

⒉ 옵티마이저(optimizer)

❷ 인덱스 유무, 데이터 분산 또는 편향 정도, DBMS 내부 매개변수 등의 조건을 고려해서, 선택 가능한 많은 실행 계획을 작성한다.

❸ 실행 계획들의 비용을 연산하고, 실행 계획 중 가장 낮은 비용을 가진 계획을 선택한다.

 

⒊ 카탈로그 매니저(catalog manager)

카탈로그란 DBMS의 내부 정보를 모아놓은 테이블들로 테이블 또는 인덱스의 통계 정보가 저장되어 있다. 실행 계획을 세울 때 카탈로그 매너지에서 옵티마이저에 이 정보들을 제공한다.

 

⒋ 실행 평가

위에서 세운 여러 실행 계획들을 받아 최적의 실행 결과를 선택하는 것이 계획 평가이다. 실행 평가 단계가 지나면 DBMS가 실행 계획을 절차적인 코드로 변환한 후 데이터 접근을 수행한다.

 

 

주의할 점

카탈로그 매니저가 제공하는 정보와 실제 테이블의 데이터 정보가 불일치 할 수 있기 때문에 데이터베이스 엔지니어가 항상 신경써야 하는데 이렇게 정보가 일치하지 않은 경우에 옵티마이저가 최적의 성능을 내지 못하게 된다.

 

카탈로그에 포함되어 있는 통계 정보는 대부분 다음과 같다.

• 각 테이블의 레코드 수
• 각 테이블의 필드 수와 필드의 크기
• 필드의 카디널리티(값의 개수)
• 필드값의 히스토그램(어떤 값이 얼마나 분포되어 있는가)
• 필드 내부에 있는 NULL 수
• 인덱스 정보

 

MySQL 8.0 시스템 설정 변수

시스템 설정 변수 중 통계 정보와 관련된 변수는 다음과 같다.

• innodb_stats_persistent(default : ON) : 영구적으로 통계 정보를 디스크에 저장하는 기능 활성화 여부
• innodb_stats_auto_recalc(default : ON) : 테이블 행의 10% 이상이 변경될 때 통계 정보를 자동으로 갱신할지 여부
• innodb_stats_persistent_sample_pages(default : 20) : 통계를 계산할 때 샘플링할 인덱스 페이지 수 지정(통계가 정확하지 않다고 판단되면 값을 늘려야 한다)

 

정보를 확인하고 싶을 때는 다음과 같이 작성하면 된다.

show global variables like '%innodb_stats%'

 

EXPLAIN 구문

EXPLAIN 구문을 사용하면 쿼리문에 대한 실행 계획을 확인할 수 있다.

• EXPLAIN 구문은 SELECT, DELETE, INSERT, REPLACES, UPDATE 구문과 같이 사용할 수 있다. MySQL 8.0.19 버전 이후부터는 TABLE 구문과도 사용이 가능하다.
• 테이블이 조인되는 방법과 순서에 대한 정보를 포함한 구문들이 어떻게 실행될지에 대한 정보를 알 수 있다.

그외 다양한 정보는 MySQL 8.0 Reference Manual/13.8.2 EXPLAIN Statement에서 더 자세히 확인할 수 있다.

 

 

간단한 쿼리문에 대한 실행 계획을 확인해 보자.

EXPLAIN select * from std_user;

총 열 개의 열에 대한 정보가 출력됐는데 각 필드에 대한 설명은 표와 같다. 내가 만든 예제 테이블은 PK 키만 지정되어 있고 쿼리문 자체가 단순해서 여러 정보가 나온 것 같진 않고 가장 단순한 정보들만 나온 것 같다.

출처 : MySQL 8.0 Reference Manual/8.8.2&nbsp;EXPLAIN Output Format

1. id : select 쿼리 내에서의 일련 번호
2. select_type : select문의 타입
3. table : 참조하는 테이블의 이름
4. type : 테이블들의 조인 유형(인덱스와 관련된 유형들이 있다)
5. possible_keys : MySQL이 레코드를 찾기 위해 선택하는 인덱스들
6. key : MySQL이 실제로 사용하기로 결정한 키, possible_keys 중 하나를 사용하여 검색하면 해당 인덱스가 키 값으로 나열됨
7. key_len : MySQL이 실제로 사용하기로 결정한 키의 길이
8. ref : 테이블에서 레코드를 선택하기 위해 사용된 컬럼
9. rows : MySQL이 쿼리를 실행하기 위해 검사해야 한다고 생각하는 레코드의 수
10. Extra : MySQL이 쿼리를 해결하는 방법에 대한 추가 정보 

• select_type은 단순한 SELECT문이었기 때문에 SIMPLE이다.
• type은 조인이나 인덱스 설정이 없기 때문에 ALL로 풀 테이블 스캔이다.
• 설정된 인덱스 값이 없기 때문에 possible_keys와 key, key_len 필드 값은 NULL 값이다.
• 조건에 사용된 필드가 없기 때문에 ref 필드 값은 NULL이다.
• Extra 필드의 'Using where'은 MySQL 엔진이 별도의 필터링 처리를 한 경우에 나타난다.

 

다음은 <SQL 레벨업>에서 예제로 사용했던 쿼리문이다. 

비집약 테이블에서 id 필드로 그룹화하고 집약 함수를 사용해서 여러 개의 레코드를 한 개의 레코드로 집약한다.

EXPLAIN SELECT id,
       MAX(CASE WHEN data_type = 'A' THEN data_1 ELSE NULL END) AS data_1,
       MAX(CASE WHEN data_type = 'A' THEN data_2 ELSE NULL END) AS data_2,
       MAX(CASE WHEN data_type = 'B' THEN data_3 ELSE NULL END) AS data_3,
       MAX(CASE WHEN data_type = 'B' THEN data_4 ELSE NULL END) AS data_4,
       MAX(CASE WHEN data_type = 'B' THEN data_5 ELSE NULL END) AS data_5,
       MAX(CASE WHEN data_type = 'C' THEN data_6 ELSE NULL END) AS data_6
FROM NonAggTbl
GROUP BY id;

Extra 필드에는 여러 가지 정보가 들어갈 수 있는데 여기서는 Using temporary와 Using filesort를 확인할 수 있다.

• Using temporary : GROUP BY 절과 ORDER BY 절이 포함된 경우 임시 테이블을 사용해야 한다는 것을 나타낸다.
• Using filesort : 정렬에 대한 정보이다.
• 두 정보가 모두 나타난 것은 임시 테이블에 레코드를 저장 후 정렬한다는 의미이다.

 

 

SELECT TYPE

구분	설명
SIMPLE	단순 SELECT(UNION이나 Sub Query가 없는 SELECT 문)
PRIMARY	Sub Query를 사용할 경우 Sub Query의 외부 쿼리(첫 번째 쿼리), UNION을 사용할 경우 UNION의 첫 번째 SELECT 쿼리
UNION	UNION 쿼리에서 PRIMARY를 제외한 나머지 SELECT
DEPENDENT_UNION	UNION과 동일하나, 외부 쿼리에 의존적임(값을 공급 받음)
UNION_RESULT	UNION 쿼리의 결과물
SUBQUERY	Sub Query 또는 Sub Query를 구성하는 여러 쿼리 중 첫 번째 SELECT문
DEPENDENT_SUBQUERY	Sub Query와 동일하나, 외부 쿼리에 의존적임(값을 공급 받음)
DERIVED	SELECT로 추출된 테이블(FROM절에서의 서브쿼리 또는 Inline View)
UNCACHEABLE SUBQUERY	Sub Query와 동일하지만 공급되는 모든 값에 대해 Sub Query를 재처리, 외부쿼리에서 공급되는 값이 동일하더라도 Cache된 결과를 사용할 수 없음
UNCACHEABLE UNION	UNION과 동일하지만 공급되는 모든 값에 대하여 UNION 쿼리를 재처리

출처 : https://nomadlee.com/mysql-explain-sql/

 

 

SIMPLE 결과 값만 나온 위의 쿼리문과 달리 이렇게 UNION을 활용한 쿼리문에 대해 실행 계획을 확인하면

EXPLAIN SELECT * FROM std_user 
WHERE user_id = 'keemsora' 
UNION 
SELECT * FROM ext_user 
WHERE user_id = 'keembora';

PRIMARY, UNION, UNION RESULT라는 SELECT_TYPE 값이 여러 행 출력되는 것을 확인할 수 있다. 어떤 table을 활용하느냐에 따라 table 컬럼 값도 변경된다.

 

 

TYPE

JOIN 타입에 관한 컬럼이다. 더 자세한 정보는 출처를 통해 확인할 수 있다.

구분	설명
system	레코드가 0건 또는 1건만 존재하는 테이블에 접근할 때의 방법
const	쿼리문 WHERE절에 PK나 UK 컬럼을 사용하면서 결과가 반드시 1건을 반환할 때의 방법(=UNIQUE INDEX SCAN)
eq_ref	여러 테이블이 JOIN되는 쿼리에서만 발생
ref	eq_ref와 달리 JOIN 순서에 상관없이 사용
fulltext	전문 검색 인덱스를 사용해서 레코드에 접근하는 방법
ref_or_null	ref와 같은데 NULL 비교가 추가된 형태
unique_subquery	WHERE 조건에 IN 형태를 사용할 때, 서브 쿼리에서 중복되지 않는 유니크한 값만 반환될 때 사용
index_subquery	유니크하지 않은 경우에 인덱스를 이용하여 중복을 제거하는 케이스
range	인덱스를 하나의 값이 아니라 범위로 검색하는 경우에 사용되는 접근 방법
index_merge	2개 이상의 인덱스를 이용해 각각의 검색 결과를 만든 후 결과를 합치는 방식
index	인덱스를 처음부터 끝까지 읽어야 하는 경우 사용
ALL	풀 테이블 스캔

출처 : https://jeong-pro.tistory.com/243

 

 

실행 계획을 읽는 방법

위와 같이 여러 개의 SELECT_TYPE이 나올 때는 어떻게 실행 계획을 읽어야 할까? 

• 실행 계획은 일반적으로 트리 구조이다.
• 중첩 단계가 깊을수록 먼저 실행된다.
• 같은 중첩 단계에서는 위에서 아래로 실행한다.

설명처럼 위에서는 같은 중첩 단계이기 때문에 순서대로 std_user, ext_user에 대해 접근한 후 UNION 결과물을 출력하게 되는 것이다.

 

 

정리

- 옵티마이저가 항상 최적의 성능을 내는 실행 계획을 선택하는 것이 아니기 때문에 수동으로 변경해줘야 하는데 그러기 위해서는 실행계획을 어떻게 확인하고 어떻게 읽는지 알아야 한다.

- 모집합의 데이터가 많을수록 풀 스캔(O(n))보다 인덱스 스캔(O(log n)) 접근 방식이 성능 상 좋기 때문에 인덱스를 잘 활용해야 한다.

 

 

참고

- <SQL 레벨업>

- https://dev.mysql.com/doc/refman/8.0/en

- https://nomadlee.com/mysql-explain-sql/

- https://jeong-pro.tistory.com/243