MySQL 실행 계획 : 실행 계획 분석(3)

MySQL 실행 계획 : 실행 계획 분석(3)

5. possible_keys

실행 계획의 이 칼럼 또한 사용자의 오해를 자주 불러일으키곤 한다. MySQL 옵티마이저는 쿼리를 처리하기 위해 여러 가지 처리 방법을 고려하고 그중에서 비용이 가장 낮을 것으로 예상하는 실행 계획을 선택해서 쿼리를 실행한다. 그런데 possible_keys 칼럼에 있는 내용은 MySQL 옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 접근했던 접근 방식에서 사용되는 인덱스의 목록일 뿐이다. 즉, 말 그대로 "사용될 법했던 인덱스의 목록"인 것이다. 실제로 실행 계획을 보면 그 테이블의 모든 인덱스가 목록에 포함되어 나오는 경우가 허다하기에 쿼리를 튜닝하기에 아무런 도움이 되지 않는다. 그래서 실행 계획을 확인할 때는 Possible_keys 칼럼은 그냥 무시하자. 절대 Possible_keys 칼럼에 인덱스 이름이 나열됐다고 해서 그 인덱스를 사용한다고 판단하지 않도록 주의하자.

6. key

Possible_keys 칼럼의 인덱스가 사용 후보였던 반면 Key 칼럼에 표시되는 인덱스는 최종 선택된 실행 계획에서 사용하는 인덱스를 의미한다. 그러므로 쿼리를 튜닝할 때는 Key 칼럼에 의도했던 인덱스가 표시되는지 확인하는 것이 중요하다. Key 칼럼에 표시되는 값이 PRIMARY인 경우에는 프라이머리 키를 사용한다는 의미하며, 그 이외의 값은 모두 테이블이나 인덱스를 생성할 때 부여했던 고유 이름이다.

실행 계획의 type 칼럼이 index_merge가 아닌 경우에는 반드시 테이블 하나당 하나의 인덱스만 이용할 수 있다. 하지만 index_merge 실행 계획이 사용될 때는 2개 이상의 인덱스가 사용되는데, 이때는 Key 칼럼에 여러 개의 인덱스가 ","로 구분되어 표시된다. 위에서 살펴본 index_merge 실행 계획을 다시 한번 살펴보자. 다음의 실행 계획은 WHERE 절의 각 조건이 PRIMARY와 ix_firstname 인덱스를 사용한다는 것을 알 수 있다.

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	employees	index_merge	PRIMARY, ix _firstname	4,44		1521	...

그리고 실행 계획의 type이 ALL일 때와 같이 인덱스를 전혀 사용하지 못하면 Key 칼럼은 NULL로 표시된다.

MySQL에서 프라이머리 키는 별도의 이름을 부여할 수 없으며, 기본적으로 PRIMARY라는 이름을 가진다. 그 밖의 나머지 인덱스는 모두 테이블을 생성하거나 인덱스를 생성할 때 이름을 부여할 수 있다. 실행 계획뿐 아니라 쿼리의 힌트를 사용할 때도 프라이머리 키를 지칭하고 싶다면 PRIMARY라는 키워드를 사용하면 된다.

7. key_len

key_len 칼럼은 많은 사용자가 쉽게 무시하는 정보지만 사실은 매우 중요한 정보 중 하나다. 실제 업무에서 사용하는 테이블은 단일 칼럼으로만 만들어진 인덱스보다 다중 칼럼으로 만들어진 인덱스가 더 많다. 실행 계획의 key_len 칼럼의 값은, 쿼리를 처리하기 위해 다중 칼럼으로 구성된 인덱스에서 몇 개의 칼럼까지 사용했는지 우리에게 알려 준다. 더 정확하게는 인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려주는 값이다. 그래서 다중 칼럼 인덱스뿐 아니라 단일 칼럼으로 만들어진 인덱스에서도 같은 지표를 제공한다.

다음 예제는 (dept_no + emp_no)로 두 개의 칼럼으로 만들어진 프라이머리 키를 포함한 dept_emp 테이블을 조회하는 쿼리다. 이 쿼리는 dept_emp 테이블의 프라이머리 키 중에서 dept_no만 비교하는 데 사용하고 있다.

EXPLAIN
SELECT * FROM dept_emp WHERE dept_no='d005';

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	dept_emp	ref	PRIMARY	12	const	53288	Using where

그래서 key_len 칼럼의 값이 12로 표시된 것이다. 즉, dept_no 칼럼의 타입이 CHAR(4)이기 때문에 프라이머리 키에서 앞쪽 12바이트만 유효하게 사용했다는 의미다. 이 테이블의 dept_no 칼럼은 utf8 문자집합을 사용하고 있다. 실제 utf8 문자 하나가 차지하는 공간은 1바이트에서 3바이트까지 가변적이다. 하지만 MySQL 서버가 utf8 문자를 위해 메모리 공간을 할당해야 할 때는 문자에 관계없이 고정적으로 3바이트로 계산한다. 그래서 위의 실행 계획에서 key_len 칼럼의 값은 12바이트(4*3 바이트)가 표시된 것이다.

이제 똑같은 인덱스를 사용하지만 dept_no 칼럼과 emp_no 칼럼에 대해 각각 조건을 하나씩 가지고 있는 다음의 쿼리를 한번 살펴보자.

EXPLAIN
SELECT * FROM dept_emp WHERE dept_no='d005' AND emp_no=10001;

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	dept_emp	const	PRIMARY	16	const, const	1

dept_emp 테이블의 emp_no의 칼럼 타입은 INTEGER이며, INTEGER 타입은 4바이트를 차지한다. 위의 쿼리 문장은 프라이머리 키의 dept_no 칼럼뿐 아니라 emp_no까지 사용할 수 있게 적절히 조건이 제공됐다. 그래서 key_len 칼럼이 dept_no 칼럼의 길이와 emp_no 칼럼의 길이 합인 16이 표시된 것이다.

그런데 key_len의 값을 표시하는 기준이 MySQL의 버전별로 다르다. 다음 쿼리의 실행 계획을 MySQL 5.0.68 버전과 MySQL 5.1.54 버전에서 각각 확인해보자.

EXPLAIN
SELECT * FROM dept_emp WHERE dept_no='d005' AND emp_no <> 10001;

MySQL 5.0 이하의 버전

쿼리 문장은 프라이머리 키를 구성하는 emp_no와 dept_no의 조건을 줬지만 key_len 값은 12로 바뀌었다. 왜 16이 아닌 12로 줄어들었을까? 그 이유는 Key_len에 표시되는 값은 인덱스를 이용해 범위를 제한하는 조건의 칼럼까지만 포함되며, 단순히 체크 조건으로 사용된 칼럼은 Key_len에 포함되지 않기 때문이다. 그래서 MySQL 5.0에서는 key_len 칼럼의 값으로 인덱스의 몇 바이트까지가 범위 제한 조건으로 사용됐는지 판단할 수 있다.

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	dept_emp	ref	PRIMARY	12	const	53298	Using where

MySQL 5.1 이상의 버전

MySQL 5.1 버전에서는 실행 계획의 key_len이 16으로 표시됐다. 하지만 type 칼럼의 값이 ref가 아니고 range로 바뀐 것을 확인할 수 있다. 하지만 "emp_no <> 10001" 조건은 단순한 체크 조건임에도 key_len에 같이 포함되어 계산됐다. 결과적으로 MySQL 5.1에서는 key_len 칼럼의 값으로 인덱스의 몇 바이트까지가 범위 제한 조건으로 사용됐을지를 알아낼 수는 없다.

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	dept_emp	range	PRIMARY	16		53298	Using where

사실 두 버전 간의 차이는 MySQL 엔진과 InnoDB 스토리지 엔진의 역할 분담에 큰 변화가 생긴 것이 원인이다. MySQL 5.0에서는 범위 제한 조건으로 사용되는 칼럼만 스토리지 엔진으로 전달했다. 하지만 MySQL 5.1부터는 조건이 범위 제한 조건이든 체크 조건이든지 관계없이, 인덱스를 이용할 수만 있다면 모두 스토리지 엔진으로 전달하도록 바뀐 것이다. MySQL에서는 이를 "컨디션 푸시 다운(Condition push down)"이라고 한다.

8. ref

접근 방법이 ref 방식이면 참조 조건(Equal 비교 조건)으로 어떤 값이 제공됐는지 보여 준다. 만약 상수 값을 지정했다면 ref 칼럼의 값은 const로 표시되고, 다른 테이블의 칼럼값이면 그 테이블 명과 칼럼 명이 표시된다. 이 칼럼에 출력되는 내용은 크게 신경쓰지 않아도 무방한데, 아래와 같은 케이스는 조금 주의해서 볼 필요가 있다.

가끔 쿼리의 실행 계획에서 ref 칼럼의 값이 "func"라고 표시될 때가 있다. 이는 "Function"의 줄임말로 참조용으로 사용되는 값을 그대로 사용한 것이 아니라, 콜레이션 변환이나 값 자체의 연산을 거쳐서 참조됐다는 것을 의미한다. 간단히 아래 예제 쿼리의 실행 계획을 한번 살펴보자.

EXPLAIN
SELECT *
FROM employees e, dept_emp de
WHERE e.emp_no=de.emp_no;

이 쿼리는 employees 테이블과 dept_emp 테이블을 조인하는데, 조인 조건에 사용된 emp_no 칼럼의 값에 대해 아무런 변환이나 가공도 수행하지 않았다. 그래서 이 쿼리의 실행 계획은 아래와 같이 ref 칼럼에 조인 대상 칼럼의 이름이 그대로 표시된다.

id	select_type	table	type	key	key_len	ref	rows	Extra
id	select_type	table	type	key	key_len	Ref	rows	Extra

이번에는 위의 쿼리에서 조인 조건에 간단한 산술 표현식을 넣어 쿼리를 만들고, 실행 계획을 한번 확인해 보자.

EXPLAIN
SELECT *
FROM employees e, dept_emp de
WHERE e.emp_no=(de.emp_no-1);

위의 쿼리에서는 dept_emp 테이블을 읽어서 de.emp_no 값에서 1을 뺀 값으로 employees 테이블과 조인하고 있다. 이 쿼리의 실행 계획에서는 ref 값이 조인 칼럼의 이름이 아니라 "func"라고 표시되는 것을 확인할 수 있다.

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	de	ALL				334868
1	SIMPLE	e	eq_ref	PRIMARY	4	func	1	Using where

그런데 이렇게 사용자가 명시적으로 값을 변환할 때뿐만 아니라, MySQL 서버가 내부적으로 값을 변환해야 할 때도 ref 칼럼에는 "func"가 출력된다. 문자집합이 일치하지 않는 두 문자열 칼럼을 조인한다거나, 숫자 타입의 칼럼과 문자열 타입의 칼럼으로 조인할 때가 대표적인 예다. 가능하다면 MySQL 서버가 이런 변환을 하지 않아도 되도록 조인 칼럼의 타입은 일치시키는 편이 좋다.

9. rows

MySQL 옵티마이저는 각 조건에 대해 가능한 처리 방식을 나열하고, 각 처리 방식의 비용을 비교해 최종적으로 하나의 실행 계획을 수립한다. 이때 비용을 산정하는 방법은 각 처리 방식이 얼마나 많은 레코드를 읽고 비교해야 하는지 예측해 보는 것이다. 대상 테이블에 얼마나 많은 레코드가 포함돼 있는지 또는 각 인덱스 값의 분포도가 어떤지를 통계 정보를 기준으로 조사해서 예측한다.

MySQL 실행 계획의 rows 칼럼의 값은 실행 계획의 효율성 판단을 위해 예측했던 레코드 건수를 보여준다. 이 값은 스토리지 엔진별로 가지고 있는 통계 정보를 참조해 MySQL 옵티마이저가 산출해 낸 예상 값이라서 정확하지는 않다. 또한, rows 칼럼에 표시되는 값은 반환하는 레코드의 예측치가 아니라, 쿼리를 처리하기 위해 얼마나 많은 레코드를 디스크로부터 읽고 체크해야 하는지를 의미한다. 그래서 실행 계획의 rows 칼럼에 출력되는 값과 실제 쿼리 결과 반환된 레코드 건수는 일치하지 않는 경우가 많다.

다음 쿼리는 dept_emp 테이블에서 from_date가 "1985-01-01"보다 크거나 같은 레코드를 조회하는 쿼리다. 이 쿼리는 dept_emp 테이블의 from_date 칼럼으로 생성된 ix_fromdate 인덱스를 이용해 처리할 수도 있지만, 풀 테이블 스캔(ALL)을 선택했다는 것을 알 수 있다. 다음 쿼리의 실행 계획에서 rows 칼럼의 값을 확인해 보면 MySQL 옵티마이저가 이 쿼리를 처리하기 위해 대략 334,868건의 레코드를 읽어야 할 것이라고 예측했음을 알 수 있다. Dept_emp 테이블의 전체 레코드가 331,603건인 것을 고려한다면 레코드의 대부분을 비교해야봐야 한다고 판단한 것이다. 그래서 MySQL 옵티마이저는 인덱스 레인지 스캔이 아니라 풀 테이블 스캔을 선택한 것이다.

EXPLAIN
SELECT * FROM dept_emp WHERE from_Date>='1985-01-01';

id	select_type	table	type	possible_ keys	key	key_len	ref	rows	Extra
1	SIMPLE	dept_emp	ALL	ix_fromdate				334868	Using where

그럼 이제 범위를 더 줄인 쿼리의 실행 계획을 한번 비교해 보자. 다음 쿼리의 실행 계획을 보면 MySQL 옵티마이저는 대략 292건의 레코드만 읽고 체크해 보면 원하는 결과를 가져올 수 있을 것으로 예측했음을 알 수 있다. 물론 그래서 실행 계획도 풀 테이블 스캔이 아니라 range로 인덱스 레인지 스캔을 사용한 것이다.

EXPLAIN
SELECT * FROM dept_emp WHERE from_date>='2002-07-01';

id	select_type	table	type	possible_ keys	key	key_len	ref	rows	Extra
1	SIMPLE	dept_emp	range	ix _fromdate	ix _fromdate	3		292	Using where

이 예에서 옵티마이저는 from_date 칼럼의 값이 '2002-07-01'보다 큰 레코드가 292건만 존재할 것으로 예측했고, 이는 전체 테이블 건수와 비교하면 8.8%밖에 되지 않는다. 그래서 최종적으로 옵티마이저는 ix_fromdate 인덱스를 range 방식(인덱스 레인지 스킨)으로 처리한 것이다. 또한 인덱스에 포함된 from_date가 DATE 타입이므로 key_len은 3바이트로 표시됐다.

첫 번째 풀 테이블 스캔을 사용했던 예제 쿼리에 LIMIT 조건이 추가됐을 때 MySQL 옵티마이저가 예측하는 레코드 건수는 어떻게 변하는지 한번 살펴보자.

EXPLAIN
SELECT * FROM dept_emp WHERE from_date>='1985-01-01' LIMIT 10;

풀 테이블 스캔을 사용하면 rows 칼럼의 값이 334,868로 표시됐는데, LIMIT 10 조건을 추가하면 rows 칼럼의 값이 대략 반 정도로 줄어든 것을 알 수 있다. LIMIT가 포함되는 쿼리는 rows 칼럼에 표시되는 값이 오차가 너무 심해서 별로 도움이 되지 않는다는 것을 알 수 있다.

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	dept_emp	range	ix_fromdate	3		167631	Using where

참고

• Real MySQL