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

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

10. Extra

Using join buffer (MySQL 5.1부터)

일반적으로 빠른 쿼리 실행을 위해 조인이 되는 칼럼은 인덱스를 생성한다. 실제로 조인에 필요한 인덱스에 조인되는 양쪽 테이블 칼럼 모두가 필요한 것이 아니라 조인에서 뒤에 읽는 테이블의 칼럼에만 필요하다. MySQL 옵티마이저도 조인되는 두 테이블에 있는 각 칼럼에서 인덱스를 조사해도, 인덱스가 없는 테이블이 있으면 그 테이블을 먼저 읽어서 조인을 실행한다. 뒤에 읽는 테이블은 검색 위주로 사용되기 때문에 인덱스가 없으면 성능에 미치는 영향이 매우 크기 때문이다.

RDBMS에서 조인을 처리하는 방법은 2~3가지 정도 되지만 MySQL에서는 "중첩 루프 조인(Nested loop)" 방식만 지원한다. FORM 절에 아무리 테이블이 많아도 조인을 수행할 때는 반드시 두 개의 테이블이 비교되는 방식으로 처리된다. 그리고 두 개의 테이블이 조인될 때 먼저 읽는 테이블을 드라이빙(Driving) 테이블이라고 하며, 뒤에 읽는 테이블을 드리븐(Driven) 테이블이라고 한다. 예를 들어 조인이 다음과 같은 순서로 수행되는 쿼리가 있다고 가정해 보자.

A -> B -> C

A 테이블과 B 테이블이 조인되는 과정에서 드라이빙 테이블은 A이며, 드리븐 테이블은 B다. 그리고 B와 C가 조인되는 과정에서 드라이빙 테이블은 B가 되고 드리븐 테이블은 C가 된다. 일반적으로 3개 이상의 여러 개의 테이블이 조인되는 경우에도 가장 먼저 읽히는 드라이빙 테이블이 어떤 테이블이냐 따라 성능이 많이 좌우된다(일반적으로 쿼리 전체적으로 가장 먼저 읽히는 테이블을 드라이빙 테이블이라고 할 때가 많다).
가끔은 드라이빙 테이블을 아우터 테이블(Outer table), 드리븐 테이블을 이너 테이블(Inner table)이라고 표현하기도 한다.

조인이 수행될 때 드리븐 테이블의 조인 칼럼에 적절한 인덱스가 있다면 아무런 문제가 되지 않는다. 하지만 드리븐 테이블에 검색을 위한 적절한 인덱스가 없다면 드라이빙 테이블로부터 읽은 레코드의 건수만큼 매번 드리븐 테이블을 풀 테이블 스캔이나 인덱스 풀 스캔해야 할 것이다. 이때 드리븐 테이블의 비효율적인 검색을 보완하기 위해 MySQL 서버는 드라이빙 테이블에서 읽은 레코드를 임시 공간에 보관해두고 필요할 때 재사용할 수 있게 해준다. 읽은 레코드를 임시로 보관해두는 메모리 공간을 "조인 버퍼"라고 하며, 조인 버퍼가 사용되는 실행 계획의 Extra 칼럼에는 "Using join buffer"라는 메시지가 표시된다.

조인 버퍼는 join_buffer_size라는 시스템 설정 변수에 최대 사용 가능한 버퍼 크기를 설정할 수 있다. 만약 조인되는 칼럼에 인덱스가 적절하게 준비돼 있다면 조인 버퍼에 크게 신경쓰지 않아도 된다. 그렇지 않다면 조인 버퍼를 너무 부족하거나 너무 과다하게 사용되지 않게 적절히 제한해두는 것이 좋다. 일반적인 온라인 웹 서비스용 MySQL 서버라면 조인 버퍼는 1MB 정도로 충분하며, 더 크게 설정해야 할 필요는 없다. 다음 예제 쿼리는 조인 조건이 없는 카테시안 조인을 수행하는 쿼리다. 이런 카테시안 조인을 수행하는 쿼리는 항상 조인 버퍼를 사용한다.

EXPLAIN
SELECT *
FROM dept_emp de, employees e
WHERE de.from_date > '2005-01-01' AND e.emp_no < 10994;

id	select_type	table	type	possible_ keys	key	key_len	ref	rows	Extra
1	SIMPLE	de	range	ix_fromdate	ix_fromdate	3		1	Using where
1	SIMPLE	e	range	PRIMARY	PRIMARY	4		1520	Using where; Using join buffer

Using sort union(...), Using union(...), Using intersect(...)

쿼리가 Index_merge 접근 방식(실행 계획의 type 칼럼의 값이 index_merge)으로 실행되는 경우에는 2개 이상의 인덱스가 동시에 사용될 수 있다. 이때 실행 계획의 Extra 칼럼에는 두 인덱스로부터 읽은 결과를 어떻게 병합했는지 조금 더 상세하게 설명하기 위해 다음 3개 중에서 하나의 메시지를 선택적으로 출력한다.

1. Using intersect(...)
   각각의 인덱스를 사용할 수 있는 조건이 AND로 연결된 경우 각 처리 결과에서 교집합을 추출해내는 작업을 수행했다는 의미다.

2. Using union(...)
   각 인덱스를 사용할 수 있는 조건이 OR로 연결된 경우 각 처리 결과에서 합집합을 추출해내는 작업을 수행했다는 의미다.

3. Using sort_union(...)
   Using union과 같은 작업을 수행하지만 Using union으로 처리될 수 없는 경우(OR로 연결된 상대적으로 대량의 range 조건들) 이 방식으로 처리된다. Using sort_union과 Using union의 차이점은 Using sort_union은 프라이머리 키만 먼저 읽어서 정렬하고 병합한 후에야 비로소 레코드를 읽어서 반환할 수 있다는 것이다.

Using union()과 Using sort_union()은 둘 다 충분히 인덱스를 사용할 수 있는 조건이 OR로 연결된 경우에 사용된다. Using union()은 대체로 동등 비교(Equal)처럼 일치하는 레코드 건수가 많지 않을 때 사용되고, 각 조건이 크다 또는 작다와 같이 상대적으로 많은 레코드에 일치하는 조건이 사용되는 경우 Using sort_union()이 사용된다. 하지만 실제로는 레코드 건수에 거의 관계없이 각 WHERE 조건에 사용된 비교 조건이 모두 동등 조건이면 Using union()이 사용되며, 그렇지 않으면 Using sort_union()이 사용된다.

MySQL 내부적으로 이 둘의 차이는 정렬 알고리즘에서 싱글 패스 알고리즘과 투 패스 정렬 알고리즘의 차이와 같다. Using union()이 싱글 패스 정렬 알고리즘을 사용한다면 Using sort_union()은 투 패스 정렬 알고리즘을 사용한다.

Using temporary

MySQL이 쿼리를 처리하는 동안 중간 결과를 담아 두기 위해 임시 테이블(Temporary table)을 사용한다. 임시 테이블은 메모리상에 생성될 수도 있고 디스크상에 생성될 수도 있다. 쿼리의 실행 계획에서 Extra 칼럼에 "Using temporary" 키워드가 표시되면 임시 테이블을 사용한 것인데, 이때 사용된 임시 테이블이 메모리에 생성됐었는지 디스크에 생성됐었는지는 실행 계획만으로 판단할 수 없다.

EXPLAIN
SELECT * FROM employees GROUP BY gender ORDER BY MIN(emp_no);

위의 쿼리는 GROUP BY 칼럼과 ORDER BY 칼럼이 다르기 때문에 임시 테이블이 필요한 작업이다. 인덱스를 사용하지 못하는 GROUP BY 쿼리는 실행 계획에서 "Using temporary" 메시지가 표시되는 가장 대표적인 형태의 쿼리다.

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

실행 계획의 Extra 칼럼에 "Using temporary"가 표시되지는 않지만, 실제 내부적으로는 임시 테이블을 사용할 때도 많다. Exra 칼럼에 "Using temporary"가 표시되지 않았다고 해서 임시 테이블을 사용하지 않는다라고 판단하지 않도록 주의히야 한다. 대표적으로 메모리나 디스크에 임시 테이블을 생성하는 쿼리는 다음과 같다.

• FROM 절에 사용된 서브 쿼리는 무조건 임시 테이블을 생성한다. 물론 이 테이블을 파생 테이블(Derived table)이라고 부르긴 하지만 결국 실체는 임시 테이블이다.
• 'COUNT(DISTINCT column1)"를 포함하는 쿼리도 인덱스를 사용할 수 없는 경우에는 임시 테이블이 만들어진다.
• UNION이나 UNION ALL이 사용된 쿼리도 항상 임시 테이블을 사용해서 결과를 병합한다.
• 인덱스를 사용하지 못하는 정렬 작업 또한 임시 버퍼 공간을 사용했는데 정렬해야 할 레코드가 많아지면 결국 디스크를 사용한다. 정렬에 사용되는 버퍼도 결국 실체는 임시 테이블과 같다. 쿼리가 정렬을 수행할 때는 실행 계획의 Extra 칼럼에 "Using filesort"라고 표시된다.

그리고 임시 테이블이나 버퍼가 메모리에 저장됐는지, 디스크에 저장됐는지는 MySQL 서버의 상태 변수 값으로 확인할 수 있다.

Using where

이미 MySQL의 아키텍처 부분에서 언급했듯이 MySQL은 내부적으로 크게 MySQL 엔진과 스토리지 엔진이라는 두 개의 레이어로 나눠서 볼 수 있다. 각 스토리지 엔진은 디스크나 메모리상에서 필요한 레코드를 읽거나 저장하는 역할을 하며, MySQL 엔진은 스토리지 엔진으로부터 받은 레코드를 가공 또는 연산하는 작업을 수행한다. MySQL 엔진 레이어에서 별도의 가공을 해서 필터링(여과) 작업을 처리한 경우에만 Extra 칼럼에 "Using where" 코멘트가 표시된다.

위의 그림과 같이 각 스토리지 엔진에서 전체 200건의 레코드를 읽었는데, MySQL 엔진에서 별도의 필터링이나 가공 없이 그 데이터를 그대로 클라이언트로 전달하면 "Using where"가 표시되지 않는다. 실제로 작업 범위 제한 조건은 각 스토리지 엔진 레벨에서 처리되지만 체크 조건은 MysQL 엔진 레이어에서 처리된다. 다음의 쿼리를 한번 살펴보자.

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

이 쿼리에서 작업 범위 제한 조건은 "emp_no BETWEEN 10001 AND 10100"이며 "gender='F'"는 체크 조건임을 쉽게 알 수 있다. 그런데 처음에 emp_no 조건만을 만족하는 레코드 건수는 100건이지만 두 조건을 모두 만족하는 레코드는 37건밖에 안 된다. 이는 스토리지 엔진은 100개를 읽어서 MySQL 엔진에 넘겨줬지만 MySQL 엔진은 그중에서 63건의 레코드를 그냥 필터링해서 버렸다는 의미다. 여기서 "Using where"는 63건의 레코드를 버리는 처리를 의미한다.

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

MySQL 실행 계획에서 Extra 칼럼에 가장 흔하게 표시되는 내용이 "Using where"다. 그래서 가장 쉽게 무시해버리는 메시지이기도 하다. 실제로 왜 "Using where"가 표시됐는지 전혀 이해할 수 없을 때도 많다. 더욱이 MySQL 5.0에서는 프라이머리 키로 한 건의 레코드만 존재해도 "Using where"로 출력되는 버그가 있었다. 그래서 실행 계획의 Extra 칼럼에 표시되는 "Using where"가 성능상의 문제를 일으킬지 아닐지를 적절히 선별하는 능력이 필요한데, MySLQ 5.1부터는 실행 계획에 Filtered 칼럼이 함께 표시되므로 쉽게 성능상의 이슈가 있는지 없는지를 알아낼 수 있다.

위의 쿼리 예제를 통해 인덱스 최적화를 조금 더 살펴보자. 위 처리 과정에서 최종적으로 쿼리에 일치하는 레코드는 37건밖에 안 되지만 스토리지 엔진은 100건의 레코드를 읽은 것이다. 상당히 비효율적인 과정이라고 볼 수 있다. 그런데 만약 employees 테이블에 (emp_no + gender)로 인덱스가 준비돼 있었다면 어떻게 될까? 이때는 두 조건 모두 작업 범위의 제한 조건으로 사용되어, 필요한 37개의 레코드만 정확하게 읽을 수 있다. 일반적으로 Extra 칼럼에 "Using where"가 표시되는 경우에는 MySQL 엔진에서 한번 필터링 작업을 했다는 것을 의미한다. 그리고 그와 동시에 스토리지 엔진에 전혀 쓸모 없는 일을 추가로 시켰다는 것을 의미한다. 이는 MySQL이 스토리지 엔진과 MySQL 엔진으로 이원화된 구조 때문에 발생하는 문제점으로 볼 수 있다.
똑같이 MySQL 엔진과 스토리지 엔진의 이원화된 구조 탓에 발생하는 문제점을 하나 더 살펴보자.

CREATE TABLE tb_likefilter (
  category INT,
  name VARCHAR(30),
  INDEX ix_category_name(category, name)
);
SELECT * FROM tb_likefilter WHERE category=10 AND LIKE '%abc%';

위 쿼리의 경우, category 칼럼과 name 칼럼이 인덱스로 생성돼 있다. 하지만 name LIKE '%abc%' 조건은 작업 범위 제한 조건으로 사용되지 못한다. 이처럼 작업 범위 제한 조건으로 사용되지 못하는 조건은 스토리지 엔진에서 인덱스를 통해 체크되는 것이 아니라 MySQL 엔진에서 처리된다. 즉, 스토리지 엔진에서는 category=10을 만족하는 모든 레코드를 읽어서 MySQL 엔진으로 넘겨주고 MySQL 엔진에서 name LIKE '%abc%' 조건 체크를 수행해서 일치하지 않는 레코드는 버리는 것이다.
예를 들어, category=10을 만족하는 레코드가 100건, 그중에서 name LIKE '%abc%' 조건을 만족하는 레코드가 10건이라면 MySQL 엔진은 10건의 레코드를 위해 그 10배의 작업을 스토리지 엔진에 요청한다. 상당히 불합리한 처리 방식이기도 하지만 MySQL 5.0 이하 버전에서는 피할 수 없는 문제점이었다. InnoDB나 MyISAM과 같은 스토리지 엔진과 MySQL 엔진은 모두 하나의 프로세스로 동작하기 때문에 성능에 미치는 영향이 그다지 크지 않다. 하지만 스토리지 엔진이 MySQL 엔진 외부에서 작동하는 NDB 클러스터는 네트워크 전송 부하까지 겹치기 때문에 성능에 미치는 영향이 더 큰 편이다.
MySQL 5.1의 InnoDB 플러그인 버전부터는 이원화된 구조의 불합리를 제거하기 위해 WHERE 절의 범위 제한 조건뿐 아니라 체크 조건까지 모두 스토리지 엔진으로 전달된다. 스토리지 엔진에서는 그 조건에 정확히 일치하는 레코드만 읽고 MySQL 엔진으로 전달하기 위해 이런 비효율적인 부분이 사라진 것이다. 즉, MySQL 5.1부터는 위의 시나리오에서도 스토리지 엔진이 꼭 필요한 10건의 레코드만 읽게 되는 것이다. MySQL에서 이러한 기능을 "Condition push down"이라고 표현한다.

Using where with pushed condition

실행 계획의 Extra 칼럼에 표시되는 "Using where with pushed condition" 메시지는 "Condition push down"이 적용됐음을 의미하는 메시지다. MySQL 5.1부터는 "Condition push down"이 InnoDB나 MyISAM 스토리지 엔진에도 도입되어 각 스토리지 엔진의 비효율이 상당히 개선됐다고 볼 수 있다.

하지만 MyISAM이나 InnoDB 스토리지 엔진을 사용하는 테이블의 실행 계획에는 "Using where with pushed condition" 메시지가 표시되지 않는다. 이 메시지는 NDB 클러스터 스토리지 엔진을 사용하는 테이블에서만 표시되는 메시지다. 아래 그림과 같이 NDB 클러스터는 MySQL 엔진의 외부에서 작동하는 스토리지 엔진이라서 스토리지 엔진으로부터 읽은 레코드는 네트워크를 통해 MySQL 엔진으로 전달된다. NDB 클러스터는 여러 개의 노드로 구성되는데, 아래 그림에서 "SQL 노드"는 MySQL 엔진 역할을 담당하며, "데이터 노드"는 스토리지 엔진 역할을 담당한다. 그리고 데이터 노드와 SQL 노드는 네트워크를 통해 TCP/IP 통신을 한다. 그래서 실제 "Condition push down"이 사용되지 못하면 상당한 성능 저하가 발생할 수 있다.

참고

• Real MySQL