MySQL 실행계획 보는법, 성능최적화 튜닝

MySQL에서 SQL을 튜닝하기 위해 실행계획은 보는 방법은 단순히 EXPLAIN을 쿼리문 앞에 넣어 주기만 하면 됩니다. 하지만 실행계획을 보는 방법은 조금 다릅니다. 오라클의 실행계획과 비슷한 듯 다르기 때문이죠. 

 

MySQL 실행계획 보는법

실행계획 사용법

MySQL에서 실행계획 보는 법은 간단합니다. plan_table을 설치해야 하는 번거로움도 없이, 그냥 SQL 앞부분에 explain만 붙여 주면 됩니다. 아래 실행계획 사용법과 출력 형태를 참고하시기 바랍니다.  

1. 실행계획을 보기 위한 explain 

explain delete t1 from user as t1 JOIN usr_dtl t2 on (t1.uid = t2.usr_id) ;

 

2. 실행계획 출력 형태

MySQL의 실행계획 결과

 

그러면 출력된 살행계획을 어떻게 해석할까요? 각각의 컬럼의 해석을 위해 아래 표를 참고하시기 바랍니다. 하나하나 해석해 보면 오라클의 실행계획과 비슷한 면이 있습니다. 

컬럼 구분	표시 내용	내용 설명
id		SELECT 번호로 SQL내의 SELECT의 구분하는 번호
select_type (select 유형)	SIMPLE	단순 SELECT를 말함, UNION 또는 서브쿼리를 사용하지 않음을 의미
	PRIMARY	NL조인에서 가장 외부의 SELECT(드라이빙 테이블, Outer 테이블)
	UNION	UNION 에서의 두번째 혹은 나중에 오는 SELECT
	DEPENDENT UNION	UNION 에서의 두번째 혹은 나중에 오는 SELECT, 아웃터 쿼리에 의존적임
	UNION RESULT	UNION 의 결과라는 의미
	SUBQUERY	서브쿼리의 첫번째 SELECT
	DEPENDENT SUBQUERY	서브쿼리의 첫번째 SELECT, 아웃터 쿼리에 의존적임 - 의존적이라는 의미는 아웃터 테이블에서 값을 받아올 경우, 이 쿼리는 먼저 실행되지 못하고 다음에 실행되어 전체 성능 저하 원인 <- 제거할 수 있는 방법 모색이 튜닝 포인트
	DERIVED	뒤에 읽히는 테이블(Driven 테이블, Inner 테이블)
	UNCACHEABLE SUBQUERY	결과 집합을 캐쉬할 수 없어 각 행마다 다시 계산해야 하는 Sub 쿼리 - 예를 들어, 사용자가 변수를 사용한 경우, FROM절 이외에서 사용되는 Sub 쿼리는 재사용할 수 있게 유도하지만 어려울 경우는 uncacheable subquery로 표현
	UNCACHEABLE UNION	UNCACHEABLE SUBQUERY 중에 UNION에서 두 번째나 그 이후의 SELECT에서 나옴
table		나타난 결과가 참조하는 테이블명
partition		파티션 테이블의 경우, 어떤 파티션에서 실행된 SELECT문인지 표시함
type	system, const, eq_ref, ref, ref_or_null, index_merge, unique_subquery, range 등	이 부분은 조인 타입 또는 형태, MySQL 튜닝에서 가장 중요하게 확인해야 하는 부분임
possible_keys		MySQL 옵티마이저가 해당 테이블의 검색에 사용할수 있는 인덱스들을 표시함 - 주의할 점은 explain 결과에서 나타난 테이블들의 순서와는 무관함 - possible_keys에 나타난 Index들이 결과에 나타난 테이블 순서에서 실제 사용할 수 없을수도 있다는 것을 의미함 - 이 부분이 NULL이면 사용 가능한 인덱스가 없다는 의미이며, 이러한 경우에는 검색조건을 고려하여 인덱스를 추가함으로써 성능을 개선할 수 있음 - 인덱스를 추가나 변경 후 다시한번 EXPLAIN 을 실행 필요
key		이 컬럼은 MySQL 이 실제 사용한 key(index) 를 나타냄 - 만약 사용한 인덱스가 없다면 NULL로 표현 - MySQL 이 possible_keys 에 나타난 인덱스를 사용하거나 사용하지 않도록 강제하려면 힌트를 사용하면 됨 - 예를 들어, FORCE INDEX, USE INDEX, 혹은 IGNORE INDEX 등
key_len		MySQL 이 사용한 인덱스의 길이를 나타냄 - key 컬럼값이 NULL 이면 이 값도 NULL임 - key_len 값으로 MySQL이 실제 복합 인덱스(복수컬럼 키)중 얼마나 많은 부분을 사용할 것인지 알 수 있음 계산 방법은 아래와 같음 - 문자타입 : 2 + 길이*3 - bigint : 8 - tinyint : 2
ref		행(row)을 추출하는데 키와 함께 사용된 컬럼이나 상수값을 나타냄
rows		이 값은 쿼리 수행에서 MySQL 이 예상하는 검색해야할 row수를 표시
filtered		수행 시 필터되고 남은 레코드 수의 비율 나타냄 - 예를 들어, 100.00 - 실행계획에 따른 예측값이라 실제와 다를 수 있음
Extra	distinct, Full scan on NULL key, No table, Not exists, range checked for each record (index map: #)  등	MySQL이 쿼리를 해석한 추가적인 정보를 표시한 내용

 

Type을 이용한 튜닝 방법

Type은 테이블이나 서브 쿼리가 어떤 형태로 조인하는지에 대한 내용을 담고 있습니다. 그 부분을 잘 보면 성능에 좋은 조인이 있고, 나쁜 조인 방식이 있습니다. 여기서 튜닝 포인트는 나쁜 조인방식은 지양하고, 좋은 방식으로 실행계획이 나오게 변경해 주면 됩니다. 

system

테이블에서 단 하나의 row만 return 한 경우를 말합니다. 가장 효유적인 액세스 방법으로 추가 튜닝이 필요하지 않습니다. 

const

값이 상수로 간주된다 하여 const라고 합니다. 예를 들어, 하나의 매치되는 row만 존재하는 경우입니다. 하나의 row만 매치되기 때문에 각 컬럼 값은 나머지 연산에서 상수나 마찬가지입니다. 한 번만 읽어 들이면 되기 때문에 빠른 연산을 합니다. 이것 역시 추가 튜닝이 필요 없습니다. 

• 오라클의 unique scan과 유사함
• PRIMARY KEY 나 UNIQUE index를 이용해 상수와 비교하는 경우

아래의 경우에서 a_table 은 const table 로 조인된다. SELECT * FROM a_table WHERE primary_key=1; SELECT * FROM a_table WHERE primary_key_part1=1 AND primary_key_part2=2;

 

eq_ref

조인을 위해 각 테이블에서 하나의 row만이 읽히는 형태를 말합니다. const 타입 이외에 가장 빠른 조인 유형입니다. 

• 조인연산에 PRIMARY KEY 나 UNIQUE index 인덱스가 사용되는 경우
• 인덱스된 컬럼이 = 연산에 사용되는 경우
• 비교되는 값은 상수이거나 이전 조인결과의 컬럼일 수 있음

다음 예에서 MySQL 은 a_table 을 처리하는데 eq_ref 조인을 사용합니다. 매우 효율적인 방식이나 튜닝 포이트는 적절한 인덱스를 사용하고 있는지 확인하는 것입니다. 

SELECT *  FROM a_table          , b_table WHERE a_table.key_column = b_table.column ;

SELECT *  FROM a_table           , b_table WHERE a_table.key_column_part1= b_table.column AND a_table.key_column_part2=1 ;

 

ref 

이전 테이블과의 조인에 사용될 매치되는 인덱스의 모든 row가 해당 테이블에서 읽힌다는 의미입니다. 만약 사용된 키가 작은 수의 행과 매치될 때 이것은 나쁘지 않은 조인 타입입니다.

• 복합 키의 첫번째 컬럼만을 사용하거나 사용된 키가 PRIMARY KEY 나 UNIQUE 가 아닐 경우
• 즉, 키 값으로 단일row를 추출할 수 없을 때 사용되는 조인
• ref는 인덱스 된 컬럼과 equal(=) 연산에서 사용됨

MySQL은 아래 예시와 같은 경우 a_table 처리에 ref 조인 타입을 사용합니다. 튜닝 포인트는 필요한 경우 복합 인덱스를 추가하여 성능을 개선할 수 있습니다. 

SELECT *  FROM a_table           , b_table WHERE a_table.key_column_part1= b_table.column ;

SELECT *  FROM a_table          , b_table WHERE a.index_column = b.column ;

 

ref_or_null

ref 와 같지만 NULL 값을 포함하는 row에 대한 검색이 발생됩니다.

• 서브쿼리 처리에서 대개 사용

아래 예에서 MySQL 은 a_table 처리에 ref_or_null 조인타입을 사용한다.

SELECT *  FROM a_table WHERE key_column=expr OR key_column IS NULL ;

 

index_merge

인덱스 병합 최적화가 적용되는 조인 타입입니다. 이 경우, key 컬럼은 사용된 인덱스의 리스트를 나타내며 key_len 컬럼은 사용된 인덱스 중 가장 긴 key 명을 표시합니다.

unique_subquery

이것은 아래와 같은 몇몇 IN 서브쿼리 처리에서 ref 타입대신 사용됩니다. 

• value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery 는 성능향상을 위해 서브쿼리를 단순 index 검색 함수로 대체합니다.

index_subquery

unique_subquery 와 마찬가지로 IN 서브쿼리를 대체합니다. 그러나 이것은 아래와 같이 서브쿼리에서 non-unique 인덱스를 사용할 때 동작합니다.

value IN (SELECT index_column FROM single_table WHERE some_expr)

range

인덱스를 사용하여 주어진 범위 내의 행들만 추출되는 조인 방식입니다. 오라클의 range scan과 유사합니다. key 컬럼은 사용된 인덱스를 나타내고 key_len는 사용된 가장 긴 key 부분을 나타냅니다. 

• ref 컬럼은 이 타입의 조인에서 NULL임
• range 타입은 키 컬럼이 상수와 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN 또는 IN 연산에 사용될 때 적용됨

튜닝 포인트는 인덱스를 잘 활용하고 있는지 확인해야 합니다. 쿼리를 재구성하여 범위 검색을 줄일 수 있는지 검토해야 합니다. 

SELECT *  FROM a_table WHERE index_column = 10 ;

SELECT *  FROM a_table WHERE index_column BETWEEN 10 and 20 ;

SELECT *  FROM a_table WHERE index_column IN (10,20,30) ;

Index

이 타입은 인덱스 scan된다는걸 제외하면 ALL과 같습니다. 오라클의 Index Full Scan과 유사합니다. 일반적으로 인덱스 용량이 데이터 파일보다 적기 때문에 ALL 보다는 빠릅니다.

• MySQL 은 쿼리에서 단일 인덱스의 일부분인 컬럼을 사용할 때 이 조인타입을 적용함

튜닝 포인트는 인덱스만 사용하여 데이터를 검색하므로 적절한 방식을 수 있지만, 불필요한 인덱스 스캔을 줄이도록 쿼리 최적화 필요가 있습니다. 

 

ALL

이전 테이블과의 조인을 위해 테이블을 풀스캔 합니다. 만약 조인에 사용되는 첫 번째 테이블이 고정이 아니라면 비효율적입니다. 

• Full Scan이라 대부분의 경우에 아주 느린 성능을 보임 
• 보통 상수 값이나 상수인 컬럼으로 인덱스를 추가함으로써 ALL 타입을 피할 수 있음

FULL 스캔을 하기 때문에 가장 비효율적인 방식입니다. 필요한 인덱스를 추가하거나 쿼리를 재구성하여 전체 테이블 스캔을 피하도록 최적화야 합니다. 단 배치와 같이 테이블을 모두 읽어야 하는 경우는 올바른 방식일 수 있습니다.