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

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

10. Extra

Skip_open_table, Open_frm_only, Open_tigger_only, Open_full_table (MySQL 5.1부터)

이 코멘트 또한 "Scanned N databases"와 같이 INFORMATION_SHCEMA DB의 메타 정보를 조회하는 SELECT 쿼리의 실행 계획에서만 표시되는 내용이다. 테이블의 메타 정보가 저장된 파일(*.FRM)과 트리거가 저장된 파일(*.TRG) 또는 데이터 파일 중에서 필요한 파일만 읽었는지 또는 불가피하게 모든 파일을 다 읽었는지 등의 정보를 보여준다. Extra 칼럼에 표시되는 메시지는 다음 4가지 중 하나이며, 그 의미는 다음과 같다.

• Skip_open_table : 테이블의 메타 정보가 저장된 파일을 별도로 읽을 필요가 없음
• Open_frm_only : 테이블의 메타 정보가 저장된 파일(*.FRM)만 열어서 읽음
• Open_trigger_only : 트리거 정보가 저장된 파일(*.TRG)만 열어서 읽음
• Open_full_table : 최적화되지 못해서 테이블 메타 정보 파일(*.FRM)과 데이터(*.MYD) 및 인덱스 파일(*.MY)까지 모두 읽음

위의 내용에서 데이터(*.FRM) 파일이나 인덱스(*.MYI)에 관련된 내용은 MyISAM만 해당하며, InnoDB 스토리지 엔진을 사용하는 테이블에는 적용되지 않는다.

unique row not found (MySQL 5.1부터)

두 개의 테이블이 각각 유니크(프라이머리 키 포함) 칼럼으로 아우터 조인을 수행하는 쿼리에서 아우터 테이블에 일치하는 레코드가 존재하지 않을 때 Extra 칼럼에 이 코멘트가 표시된다.

-- // 테스트 케이스를 위한 테스트용 테이블 생성
CREATE TABLE tb_test1 (fdpk INT, PRIMARY KEY(fdpk));
CREATE TABLE tb_test2 (fdpk INT, PRIMARY KEY(fdpk));
-- // 생성된 테이블에 레코드 INSERT
INSERT INTO tb_test1 VALUES (1), (2);
INSERT INTO tb_test2 VALUES (1);
EXPLAIN
SELECT t1.fdpk
FROM tb_test1 t1
  LEFT JOIN tb_test2 t2 ON t2.fdpk=t1.fdpk
WHERE t1.fdpk=2

이 쿼리가 실행되면 tb_test2 테이블에는 fdpk=2인 레코드가 없으므로 다음처럼 "unique row not found"라는 코멘트가 표시된다.

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	t1	const	PRIMARY	4	const	1	Using index
1	SIMPLE	t2	const	PRIMARY	4	const	0	unique row not found

Using filesort

ORDER BY를 처리하기 위해 인덱스를 이용할 수도 있지만 적절한 인덱스를 사용하지 못할 때는 MySQL 서버가 조회된 레코드를 다시 한 번 정렬해야 한다. ORDER BY 처리가 인덱스를 사용하지 못할 때만 실행 계획의 Extra 칼럼에는 "Using filesort" 코멘트가 표시되며, 이는 조회된 레코드를 정렬용 메모리 버퍼에 복사해 퀵 소트 알고리즘을 수행하게 된다. "Using filesort" 코멘트는 ORDER BY가 사용된 쿼리의 실행 계획에서만 나타날 수 있다.

EXPLAIN
SELECT * FROM employees ORDER BY last_name DESC;

hire_date 칼럼에는 인덱스가 없으므로 이 쿼리의 정렬 작업을 처리하기 위해 인덱스를 이용하는 것은 불가능하다. MySQL 옵티마이저는 레코드를 읽어서 소트 버퍼(Sort buffer)에 복사하고, 정렬해서 그 결과를 클라이언트에 보낸다.

id	select_type	table	type	possible_ keys	key	key_len	ref	rows	Extra
1	SIMPLE	employees	ALL					300584	Using filesort

실행 계획의 Extra 칼럼에 "Using filesort"가 출력되는 쿼리는 많은 부하를 일으키므로 가능하다면 쿼리를 튜닝하거나 인덱스를 생성하는 것이 좋다.

Using index(커버링 인덱스)

데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있을 때 Extra 칼럼에 "Using index"가 표시된다. 인덱스를 이용해 처리하는 쿼리에서 가장 큰 부하를 차지하는 부분은 인덱스를 검색해 일치하는 레코드의 나머지 칼럼 값을 가져오기 위해 데이터 파일을 찾아서 가져오는 작업이다. 최악의 경우에는 인덱스를 통해 검색된 결과 레코드 한 건 한 건마다 디스크를 한 번씩 읽어야 할 수도 있다.

위의 그림과 같이 employees 테이블에 데이터가 저장돼 있고, 아래의 쿼리가 인덱스 레인지 스캔 집근 방식을 사용한다고 해보자. 만약 아래 쿼리가 인덱스 레인지 스캔으로 처리된다면 디스크에서 읽기 작업이 얼마나 필요한지 한 번 살펴보자.

SELECT first_name, birth_date
FROM employees WHERE first_name BETWEEN 'Babette' AND 'Gad';

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	employees	range	ix_firstname	42		...	Using where

1. 이 예제 쿼리는 employees 테이블의 first_name 칼럼에 생성된 인덱스(ix_firstname)을 이용해 일치하는 레코드를 검색할 것이다.

2. 그리고 일치하는 레코드 5건에 대해 birth_date 칼럼의 값을 읽기 위해 각 레코드가 저장된 데이터 페이지를 디스크로부터 읽어야 한다.

실제 ix_firstname 인덱스에서 일치하는 레코드 5건을 검색하기 위해 디스크 읽기 3~4번만으로 필요한 인덱스 페이지를 모두 가져올 수 있다. 하지만 각 레코드의 나머지 데이터를 가져오기 위해 최대 5번의 디스크 읽기를 더 해야 한다. 물론 이 예제는 아주 간단하고 적은 개수의 레코드만 처리하기 때문에 디스크 읽기가 적지만 실제로 복잡하고 많은 레코드를 검색해야 하는 쿼리에서는 나머지 레코드를 읽기 위해 수백 번의 디스크 읽기가 더 필요할 수도 있다.

그럼 이제 birth_date 칼럼은 빼고 fist_name 칼럼만 SELECT하는 쿼리를 한번 생각해보자. 이 쿼리도 마찬가지로 인덱스 레인지 스캔을 이용해 처리된다고 가정하자.

SELECT first_name
FROM employees WHERE first_name BETWEEN 'Babette' AND 'Gad';

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	employees	range	ix_firstname	42		...	Using index

이 예제 쿼리에서는 employees 테이블의 여러 칼럼 중에서 first_name 칼럼만 사용됐다. 즉, first_name 칼럼만 있으면 이 쿼리는 모두 처리되는 것이다. 그래서 이 쿼리는 위의 첫 번째 예제 쿼리의 두 작업 중에서 1번 과정만 실행하면 된다. 필요한 칼럼이 모두 인덱스에 있으므로 나머지 칼럼이 저장된 데이터 파일을 읽어올 필요가 없다. 이 쿼리는 디스크에서 4~5개의 페이지만 읽으면 되기 때문에 매우 빠른 속도로 처리된다.

두 번째 예제와 같이 인덱스만으로 쿼리를 수행할 수 있을 때 실행 계획의 Extra 칼럼에는 "Using index"라는 메시지가 출력된다. 이렇게 인덱스만으로 처리되는 것을 "커버링 인덱스(Covering index)"라고 한다. 인덱스 레인지 스캔을 사용하지만 쿼리의 성능이 만족스럽지 못한 경우라면 인덱스에 있는 칼럼만 사용하도록 쿼리를 변경해 큰 성능 향상을 볼 수 있다.

InnoDB의 모든 테이블은 클러스터링 인덱스로 구성돼 있다. 그리고 이 때문에 InnoDB 테이블의 모든 보조 인덱스는 데이터 레코드의 주소 값으로 프라이머리 키값을 가진다. 아래 그림의 테이블이 만약 InnoDB 스토리지 엔진을 사용한다면 실제로 아래 그림과 같이 저장될 것이다. 인덱스의 "레코드 주소" 값에 employees 테이블의 프라이머리 키인 emp_no 값이 저장된 것을 볼 수 있다.

InnoDB 테이블에서는 first_name 칼럼만으로 인덱스를 만들어도, 결국 그 인덱스에 emp_no 칼럼이 같이 저장되는 효과를 낸다. 이러한 클러스터링 인덱스 특성 때문에 쿼리가 "커버링 인덱스"로 처리될 가능성이 상당히 높아진다. 간단히 다음 쿼리를 한번 살펴보자. 이 예제 쿼리도 인덱스 레인지로 처리된다고 가정하자.

SELECT emp_no, first_name
FROM employees WHERE first_name BETWWEN 'Babette' AND 'Gad';

이 쿼리에도 위의 첫 번째나 두 번째나 예체처럼 같은 WHERE 조건이 지정돼 있어서 first_name 칼럼의 인덱스를 이용해 일치하는 레코드를 검색할 것이다. 그런데 이 쿼리는 위의 두 번째 예제 쿼리와는 달리 first_name 칼럼 말고도 emp_no를 더 가져와야 한다. 하지만 emp_no는 employees 테이블의 프라이머리 키이기 때문에 이미 인덱스에 포함돼 있어 데이터 파일을 읽지 않아도 된다. 즉, InnoDB의 보조 인덱스에는 데이터 레코드를 찾아가기 위한 주소로 사용하기 위해 프라이머리 키를 저장해 두는 것이지만, 더불어 추가 칼럼을 하나 더 가지는 인덱스의 효과를 동시에 얻을 수 있게 되는 것이다.

레코드 건수에 따라 차이는 있겟지만 쿼리는 커버링 인덱스로 처리할 수 있을 때와 그렇지 못할 때의 성능 차이는 수십 배에서 수백 배까지 차이가 날 수 있다. 하지만 무조건 커버링 인덱스로 처리하려고 인덱스에 많은 칼럼을 추가하면 더 위험한 상황이 초래될 수도 있다. 너무 과도하게 인덱스의 칼럼이 많아지면 인덱스의 크기가 커져서 메모리 낭비가 심해지고 레코드를 저장하거나 변경하는 작업이 매우 느려질 수 있기 때문이다. 너무 커버링 인덱스 위주로 인덱스를 생성하지 않도록 주의하자.

접근 방법(실행 계획의 type 칼럼)이 eq_ref, ref, range, index_merge, index 등과 같이 인덱스를 사용하는 실행 계획에서는 모두 Extra 칼럼에 "Using index"가 표시될 수 있다. 즉 인덱스 레인지 스캔(eq_ref, range, index_merge 등의 접근 방법)을 사용할 때만 커버링 인덱스로 처리되는 것은 아니다. 인덱스를 풀 스캔(index 접근 방법)을 실행할 때도 커버링 인덱스로 처리될 수 있는데, 이때도 똑같은 인덱스 풀 스캔의 접근 방법이라면 커버링 인덱스가 아닌 경우보다 훨씬 빠르게 처리된다.

Extra 칼럼에 표시되는 "Using index"와 접근 방법(type 칼럼의 값)의 "index"를 자주 혼동할 때가 있는데, 사실 이 두 가지는 성능상 반대되는 개념이라서 반드시 구분해서 이해해야 한다. 실행 계획의 type 칼럼에 표시되는 "index"는 인덱스 풀 스캔으로 처리하는 방식을 의미하며, 이는 인덱스 레인지 스캔보다 훨씬 느린 처리 방식이다. "Using index"는 커버링 인덱스가 사용되지 않는 쿼리보다도 훨씬 빠르게 처리한다는 것을 의미하는 메시지다. 커버링 인덱스는 실행 계획의 type에 관계없이 사용될 수 있다.

Using index for group-by

GROUP BY 처리를 위해 MySQL 서버는 그룹핑 기준 칼럼을 이용해 정렬 작업을 수행하고 다시 정렬된 결과를 그룹핑하는 형태의 고부하 작업을 필요로 한다. 하지만 GROUP BY 처리가 인덱스(B-Tree 인덱스에 한해)를 이용하면 정렬된 인덱스 칼럼을 순서대로 읽으면서 그룹핑 작업만 수행한다. 이렇게 GROUP BY 처리에 인덱스를 이용하면 레코드의 정렬이 필요하지 않고 인덱스의 필요한 부분만 읽으면 되기 때문에 상당히 효율적이고 빠르게 처리된다. GROUP BY 처리가 인덱스를 이용할 때 쿼리의 실행 계획에서는 Extra 칼럼에 "Using index for group-by" 메시지가 표시된다. GROUP BY 처리를 위해 인덱스를 읽는 방법을 "루스 인덱스 스캔"이라고 한다.

GROUP BY 처리를 위해 단순히 인덱스를 순서대로 쭉 읽는 타이트 인덱스 스캔과는 달리 루스 인덱스 스캔은 인덱스에서 필요한 부분만 듬성 듬성 읽는다.

1.타이트 인덱스 스캔(인덱스 스캔)을 통한 GROUP BY 처리

인덱스를 이용해 GROUP BY 절을 처리할 수 있더라도 AVG()나 SUM() 또는 COUNT(*)와 같이 조회하려는 값이 모든 인덱스를 다 읽어야 할 때는 필요한 레코드만 듬성듬성 읽을 수가 없다. 이런 쿼리는 단순히 GROUP BY를 위해 인덱스를 사용하기는 하지만 이를 루스 인덱스 스캔이라고 하지는 않는다. 또한 이런 쿼리의 실행 계획에는 "Using index for group-by" 메시지가 출력되지 않는다.

EXPLAIN
SELECT first_name, COUNT(*) AS counter FROM employees GROUP BY first_name;

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	employees	index	ix_firstname	44		299809	Using index

2.루스 인덱스 스캔을 통한 GROUP BY 처리

단일 칼럼으로 구성된 인덱스에서는 그룹핑 칼럼 말고는 아무것도 조회하지 않는 쿼리에서 루스 인덱스 스캔을 사용할 수 있다. 그리고 다중 칼럼으로 만들어진 인덱스에서는 GROUP BY 절이 인덱스를 사용할 수 있어야 함은 물론이고 MIN()이나 MAX()와 같이 조회하는 값이 인덱스의 첫 번째 또는 마지막 레코드만 읽어도 되는 쿼리는 "루스 인덱스 스캔"이 사용될 수 있다. 이때는 인덱스를 듬성듬성하게 필요한 부분만 읽는다. 다음 예제 쿼리는 salaries 테이블의 (emp_no + from_date) 칼럼으로 만들어진 인덱스에서 각 emp_no 그룹별로 첫 번째 from_date 값(최솟박)과 마지막 from_date 값(최댓값)을 인덱스로부터 읽으면 되기 때문에 "루스 인덱스 스캔" 방식으로 처리할 수 있다.

EXPLAIN
SELECT emp_no, MIN(from_date) AS first_changed_date, MAX(from_date) AS last_changed_date
FROM salaries
GROUP BY emp_no;

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	salaries	range	PRIMARY	4		711129	Using index for group-by

GROUP BY에서 인덱스를 사용하려면 우선 GROUP BY 조건의 인덱스 사용 요건이 갖춰줘야 한다. 하지만 그 이전에 WHERE 절에서 사용하는 인덱스에 의해서도 사용 여부가 영향을 받는다는 사실이 중요하다.

1.WHERE 조건절이 없는 경우

WHERE 절의 조건이 전혀 없는 쿼리는 GROUP BY와 조회하는 칼럼이 "루스 인덱스 스캔"을 사용할 수 있는 조건만 갖추면 된다. 그렇지 못한 쿼리는 타이트 인덱스 스캔(인덱스 스캔)이나 별도의 정렬 과정을 통해 처리된다.

2.WHERE 조건절이 있지만 검색을 위해 인덱스를 사용하지 못하는 경우

GROUP BY 절은 인덱스를 사용할 수 있지만 WHERE 조건절이 인덱스를 사용하지 못할 때는 먼저 GROUP BY를 위해 인덱스를 읽은 후, WHERE 조건의 비교를 위해 데이터 레코드를 읽어야만 한다. 그래서 이 경우도 "루스 인덱스 스캔"을 이용할 수 없으며, 타이트 인덱스 스캔(인덱스 스캔) 과정을 통해 GROUP BY가 처리된다. 다음의 쿼리는 WHERE 절은 인덱스를 사용하지 못하지만 GROUP BY가 인덱스를 사용하는 예제다.

EXPLAIN
SELECT first_name FROM employees
WHERE birth_date > '1994-01-01' GROUP BY first_name;

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	employees	index	ix_firstname	44		299809	Using where

3.WHERE 절의 조건이 있으며, 검색을 위해 인덱스를 사용하는 경우

하나의 단위 쿼리가 실행되는 경우에 index_merge 이외의 접근 방법에서는 단 하나의 인덱스만 사용할 수 있다. 그래서 WHERE 절의 조건이 인덱스를 사용할 수 있으면 GROUP BY가 인덱스를 사용할 수 있는 조건이 더 까다로워진다. 즉, WHERE 절의 조건이 검색하는 데 사용했던 인덱스를 GROUP BY 처리가 다시 사용할 수 있을 때만 루스 인덱스 스캔을 사용할 수 있다. 만약 WHERE 조건절이 사용할 수 있는 인덱스의 GROUP BY 절이 사용할 수 있는 인덱스가 다른 경우라면 일반적으로 옵티마이저는 WHERE 조건절이 인덱스를 사용하도록 실행 계획을 수립하는 경향이 있다. 때로는 전혀 작업 범위를 좁히지 못하는 WHERE 조건이라 하더라도 GROUP BY보다는 WHERE 조건이 먼저 인덱스를 사용할 수 있게 실행 계획이 수립된다.

EXPLAIN
SELECT emp_no
FROM salaries WHERE emp_no BETWEEN 10001 AND 200000
GROUP BY emp_no;

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	salaries	range	PRIMARY	4		207231	Using where; Using index for group-by

WHERE 절의 조건이 검색을 위해 인덱스를 이용하고, GROUP BY가 같은 인덱스를 사용할 수 있는 쿼리라 하더라도 인덱스 루스 스캔을 사용하지 않은 수 있다. 즉, WHERE 조건에 의해 검색된 레코드 건수가 적으면 루스 인덱스 스캔을 사용하지 않아도 매우 빠르게 처리될 수 있기 때문이다. 루스 인덱스 스캔은 주로 대량의 레코드를 GROUP BY하는 경우 성능 향상 효과가 있을 수 있기 때문에 옵티마이저가 적절히 손익 분기점을 판단하는 것이다.
다음 예제 쿼리는 바로 위에서 살펴본 쿼리와 같다. WHERE 절의 검색 범위만 더 좁혀졌는데, 실행 계획의 Extra 칼럼에 "Using index for group-by" 처리가 사라진 것을 확인할 수 있다.

EXPLAIN
SELECT emp_no
FROM salaries WHERE emp_no BETWEEN 10001 AND 10099
GROUP BY emp_no;

id	select_type	table	type	key	key_len	ref	Rows	Extra
1	SIMPLE	salaries	range	PRIMARY	4		1404	Using where; Using index

참고

• Real MySQL