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

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

11. EXPLAIN EXTENDED(Filtered 칼럼)

MySQL 5.1 이상의 버전이라 하더라도 스토리지 엔진에서 최종적으로 사용자에게 전달되는 레코드만 가져오는 것은 아니다. 조인과 같은 여러 가지 이유로 여전히 각 스토리지 엔진에서 읽어온 레코드를 MySQL 엔진에서 필터링하는데, 이 과정에서 버려지는 레코드가 발생할 수 밖에 없다. 하지만 MySQL 5.1.12 미만의 버전에서는 MySQL 엔진에 의해 필터링 가정을 거치면서 얼마나 많은 레코드가 버려졌고, 그래서 얼마나 남았는지를 알 방법이 없다.

MySLQ 5.1.12 버전부터는 필터링이 얼마나 효율적으로 실행됐는지를 사용자에게 알려주기 위해 실행 계획에 Filtered라는 칼럼이 새로 추가됐다. 실행 계획에서 Filtered 칼럼을 함께 조회하려면 EXPLAIN 명령 뒤에 "EXTENDED"라는 키워드를 지정하면 된다. "EXTENDED" 키워드가 사용된 실행 계획 예제를 한번 살펴보자.

EXPLAIN EXTENDED
SELECT * FROM employees
WHERE emp_no BETWEEN 10001 AND 10100 AND gender='F';

"EXPLAIN EXTENDED" 명령을 사용해 쿼리의 실행 계획을 조회하면 다음과 같이 실행 계획의 "rows" 칼럼 뒤에 "Filtered"라는 새로운 칼럼이 같이 표시된다.

id	select_type	table	type	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	employees	range	PRIMARY	4	NULL	100	20	Using where

실행 계획에서 filtered 칼럼에는 MySQL 엔진에 의해 필터링되어 제거된 레코드는 제외하고 최종적으로 레코드가 얼마나 남았는지의 비율(Percentage)이 표시된다. 위의 예제에서는 rows 칼럼의 값이 100건이고 filtered 칼럼의 값이 20%이고, 스토리지 엔진이 전체 100건의 레코드를 읽어서 MySQL 엔진에 전달했는데, MySQL 엔진에 의해 필터링되고 20%만 남았다는 것을 의미한다. 즉, MySQL 엔진에 의해 필터링되고 남은 레코드는 20건(100건 * 20%)이라는 의미다. 여기에 출력되는 filtered 칼럼의 정보 또한 실제 값이 아니라 단순히 통계 정보로부터 예측된 값일 뿐이다(이 예제의 filtered 칼럼은 임의로 편집된 것임). 아래 그림은 위의 쿼리가 실행되면서 스토리지 엔진과 MySQL 엔진에서 얼마나 레코드가 읽히고 버려졌는지를 표현한 것이다.

12. EXPLAIN EXTENDED(추가 옵티마이저 정보)

EXPLAIN 명령의 EXTENDED 옵션은 숨은 기능이 하나 더 있다. MySQL 엔진에서 쿼리의 실행 계획을 산출하기 위해 쿼리 문장을 분석해 파스 트리를 생성한다. 또한 일부 최적화 작업도 이 파스 트리를 이용해 수행한다. "EXPLAIN EXTENDED" 명령의 또 다른 기능의 분석된 파스 트리를 재조합해서 쿼리 문장과 비슷한 순서대로 나열해서 보여주는 것이다. 간단히 예제를 한번 살펴보자.

EXPLAIN EXTENDED
SELECT e.first_name,
  (SELECT COUNT(*) FROM dept_emp de, dept_manager dm WHERE dm.dept_no=de.dept_no) AS cnt
FROM employees e
WHERE e.emp_no=10001;

EXPLAIN EXTENDED 명령을 실행하면 EXTENDED 옵션이 없을 때와 같이 쿼리의 실행 계획만 화면에 출력된다. 하지만 EXPLAIN EXTENDED 명령을 실행해 실행 계획이 출력된 직후, "SHOW WARNINGS" 명령을 실행하면 옵티마이저가 분석해서 다시 재조합한 쿼리 문장을 다음과 같이 확인할 수 있다.

mysql > SHOW WARNINGS;
SELECT 'Georgi' AS 'first_name;,
(SELECT COUNT(0)
FROM 'employees'.'dept_emp' 'de'
JOIN 'employees'.'dept_manager' 'dm'
WHERE ('employees'.'de'.'dept_no'='employeew'.'dm'.'dept_no')) AS 'cnt'
FROM 'employees'.'employees' 'e' WHERE 1

SHOW WARNINGS 명령으로 출력된 내용은 표준 SQL 문장이 아니다. 지금의 예제는 상당히 비슷하게 출력됐지만 최적화 정보가 태그 형태로 포함된 것들도 있으며 쉽게 알아보기는 어려운 경우도 많다. 위의 예제에서는 COUNT(*)가 내부적으로는 COUNT(0)으로 변환되어 처리된다는 것과 emp_no=10001 조건을 옵티마이저가 미리 실행해서 상수화된 값으로 'Georgi'가 사용됐다는 것도 알 수 있다.

EXPLAIN EXTENDED 명령을 이용해 옵티마이저가 쿼리를 어떻게 해석했고, 어떻게 쿼리르 반환햇으며, 어떤 특수한 처리가 수행됐는지 등을 판단할 수 있으므로 알아두면 도움될 것이다.

14. EXPLAIN PARTITONS(Partitions 칼럼)

EXPLAIN 명령에 사용할 수 있는 옵션이 또 하나 있는데, 이 옵션으로 파티션 테이블의 실행 계획 정보를 더 자세히 확인할 수 있다. 단순히 EXPLAIN 명령으로는 파티션 테이블이 어떻게 사용됐는지 확인할 수 없다. 하지만 EXPLAIN 명령 뒤에 PARTITIONS 옵션을 사용하면 쿼리를 실행하기 위해 테이블의 파티션 중에서 어떤 파티션을 사용했는지 등의 정보를 조회할 수 있다. 우선 다음의 예제를 한번 살펴보자.

CREATE TABLE tb_partition (
  reg_date DATE DEFAULT NULL,
  id INT DEFAULT NULL,
  name VARCHAR(50) DEFAULT NULL
) ENGINE=INNODB
partition BY range (YEAR(reg_date)) (
  partition p0 VALUES less than (2008) ENGINE = INNODB,
  partition p1 VALUES less than (2009) ENGINE = INNODB,
  partition p2 VALUES less than (2010) ENGINE = INNODB,
  partition p3 VALUES less than (2011) ENGINE = INNODB
);

EXPLAIN PARTITIONS
SELECT * FROM tb_partition
WHERE reg_date BETWEEN '2010-01-01' AND '2010-12-30';

위 예제의 tb_partition 테이블은 reg_date 칼럼의 값을 이용해 년도별로 구분된 파티션 4개를 가진다. 그리고 이 테이블에서 reg_date 칼럼의 값이 "2010-01-01"부터 "2010-12-30"까지의 레코드를 조회하는 쿼리에 대해 실행 계획을 확인해 보자. 이 쿼리에서 조회하려는 데이터는 모두 2010년도 데이터이고 3번째 파티션인 p3에 저장돼 있음을 알 수 있다. 실제로 옵티마이저는 이 쿼리를 처리하기 위해 p3 파티션만 읽으면 된다는 것을 알아채고, 그 파티션에만 접근하도록 실행 계획을 수립한다. 이처럼 파티션이 여러 개인 테이블에서 불필요한 파티션을 빼고 쿼리를 수행하기 위해 접근해야 할 것으로 판단되는 테이블만 골라내는 과정을 파티션 프루팅(Partition pruning)이라고 한다.

그렇다면 이 쿼리의 실행 계획이 정말 꼭 필요한 p3 파티션만 읽는지 확인해 볼 수 있어야 쿼리의 튜닝이 가능할 것이다. 이때 옵티마이저가 이 쿼리를 실행하기 위해 접근하는 테이블을 확인해 볼 수 있는 명령이 EXPLAIN PARTITIONS다. EXPLAIN PARTITIONS 명령으로 출력된 실행 계획에는 partitions라는 새로운 칼럼을 포함해서 표시한다. Partitions 칼럼에는 이 쿼리가 사용한 파티션 목록이 수행되는데, 예상한대로 p3 파티션만 참조했음을 알 수 있다.

id	select_type	table	partitions	type	key	key_len	ref	rows	Extra
1	SIMPLE	tb_partition	p3	ALL				2	Using where

EXPLAIN PARTITIONS 명령은 파티션 테이블에 실행되는 쿼리가 얼마나 파티션 기능을 잘 활용하고 있는지를 판단할 수 있는 자료를 제공한다. EXPLAIN 명령에서는 EXTENDED와 PARTITIONS 옵션을 함께 사용할 수 있다.

TO_DAYS() 함수는 입력된 날짜 값의 포맷이 잘못돼 있다면 NULL을 반환할 수도 있다. 이렇게 MySQL의 파티션 키가 TO_DAYS()와 같이 NULL을 반환할 수 있는 함수를 사용할 때는 쿼리의 실행 계획에서 partitions 칼럼에 테이블의 첫 번째 파티션이 포함되기도 한다. 레인지 파티션을 사용하는 테이블에서 NULL은 항상 첫 번째 파티션에 저장되기 때문에 실행 계획의 partitions 칼럼에 첫 번째 파티션도 포함되는 것이다. 하지만 이렇게 실제 필요한 파티션과 테이블의 첫 번째 파티션이 함께 partitions 칼럼에 표시된다 하더라도 성능 이슈는 없으므로 크게 걱정하지 않아도 된다.

참고

• Real MySQL