mysql 쿼리 실행 계획 #1

들어가며

앞전에 인덱스에 대해서 학습을 하였다. 인덱스를 잘 활용하면 성능이 개선되거나 잘못 활용하면 오히려 성능이 안좋아질 수 있다고 배웠는데, 얼마나 개선되고 얼마나 저하가 되는지 어떡하면 확인할 수 있을까?

 

쿼리 실행 계획

바로 쿼리 실행 계획을 통해 확인할 수 있는데,  쿼리 실행 계획이란 SQL문을 어떻게 데이터를 불러올 것인지에 관한 계획, 경로를 의미한다.

 

실습

위와 같은 구조의 스키마가 존재한다. 사원은 총 300024명이 존재한다.

 

다음의 쿼리문에 대한 실행 계획을 보자

컬럼은 (id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra)으로 구성되어 있다. 각 컬럼들을 해석하여 어떻게 튜닝할지 방향을 잡을 수 있다.

 

실행 계획 분석

자세한 정보는 다음의 링크에 잘 나와있으니 https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_type

간단하게 살펴보도록 하자

MySQL :: MySQL 8.0 Reference Manual :: 8.8.2 EXPLAIN Output Format

id

id는 실행 순서이다. SQL 문이 수행되는 순서를 나타낸다.

위의 이미지에서도 알 수 있듯이 select 문이 처음으로 실행된 후 where절 뒤에 오는 join문이 실행되는 것을 알 수 있다.

select_type

SELECT 문의 유형이다. FROM에 위치하는지, 서브쿼리인지, UNION 절로 묶인 SELECT 인지를 나타낸다.

table

보는바와 같이 table 명을 표시한다.

 

partition

파티션을 나눈 경우 표시된다.

type

테이블의 데이터를 어떻게 찾을지에 관한 정보다. 테이블 풀 스캔할지, 인덱스 탈 지 등 성능을 확인하는데에 있어 필수적인 정보다. 

system	테이블에 단 하나의 행만 존재(=시스템 테이블). const 조인의 특별한 형태이다.
const	하나의 매치되는 행만 존재하는 경우. 하나의 행이기 때문에 상수로 간주되며, 한번만 읽어들이기 때문에 무척 빠르다.
eq_ref	조인수행을 위해 각 테이블에서 하나의 행만이 읽혀지는 형태. const 타입 외에 가장 훌륭한 조인타입이다.
ref	ref조인에서 키의 가장 왼쪽 접두사 만 사용하거나 키가 a PRIMARY KEY또는 UNIQUE인덱스 가 아닌 경우 (즉, 조인이 키 값을 기반으로 단일 행을 선택할 수없는 경우) 사용된다. 사용되는 키가 몇 개의 행과 만 일치하는 경우 이는 좋은 조인 유형이다.
fulltext	fulltext 색인을 사용하여 수행된다.
ref_or_null	이 조인 유형은 비슷 ref하지만 MySQL이 NULL값 을 포함하는 행을 추가로 검색한다는 점이 다르다. 이 조인 유형 최적화는 하위 쿼리를 해결하는 데 가장 자주 사용된다.
index_merge	인덱스 병합 최적화가 적용되는 조인타입. 이 경우, key컬럼은 사용된 인덱스의 리스트를 나타내며 key_len 컬럼은 사용된 인덱스중 가장 긴 key명을 나타낸다.
range	인덱스를 사용하여 주어진 범위 내의 행들만 추출된다. key 컬럼은 사용된 인덱스를 나타내고 key_len은 사용된 가장 긴 key부분을 나타낸다. ref 컬럼은 이 타입의 조인에서 NULL 이다. range 타입은 키 컬럼이 상수와 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN 또는 IN 연산에 사용될때 적용된다.
index	이 타입은 인덱스가 스캔되는걸 제외하면 ALL과 같다. 보통 인덱스 파일이 데이터 파일보다 작기 때문에 ALL보다 빠르다.
ALL	이전 테이블과의 조인을 위해 풀스캔이 된다. 만약 조인에 쓰인 첫 테이블이 고정이 아니라면 비효율적이다. 그리고 대부분의 경우 아주 느리며, 보통 상수값이나 상수인 컬럼값으로 row를 추출하도록 인덱스를 추가하여 ALL 타입을 피할 수 있다.

쉽게말해 아래로 내려갈수록 성능이 나빠진다고 보면 된다.

 

possible_keys

옵티마지어가 사용할 수 있는 인덱스 목록 표시한다. 실제 사용된 것이 아닌 poosible 인덱스이다.

key

실제로 사용한 인덱스(기본키 또는 인덱스명)이다. 비효율적인 인덱스를 사용했거나 인덱스 사용하지 않은 경우(NULL)튜닝 대상이다.

key_len

사용한 인덱스 바이트 수이다. (UTF-8 기준 INT는 4바이트, VARCHAR는 3바이트)

사용한 인덱스가 INT라면 4로 나온다. 복합 인덱스라면 인덱스를 모두 더한 값이 나온다.

ref

조인할 때 어떤 테이블에 액세스 되었는지 표시된다.

rows

SQL 문을 수행할 때 접근한 데이터의 모든 행(row) 수를 나타내는 예측 항목이다. 

SQL 문의 실제 결과와 rows가 차이가 큰 경우 MySQL 엔진에서 데이터를 많이 가져왔다는 뜻으로 튜닝 대상이 될 수 있다.

 

filltered

SQL 문을 통해 MySQL 엔진이 스토리지 엔진으로부터 가져온 데이터를 필터 조건에 따라 제거된 비율이다. (단위 %)

스토리지 엔진으로부터 100건 가져왔고 이후 MySQL 엔진의 필터 조건을 거쳐 10건이 필터링된다면 filtered 값은 10.00이다.

 

간단하게 실행계획을 분석해 봤다. 글이 길어지니 다음 글에서 실제로 인덱스를 이용해 성능 튜닝을 해보자

 

reference

https://kukim.tistory.com/128