본 게시물의 내용과 이미지는 도서 Real MySQL의 내용을 재구성하여 작성되었습니다. 저자, 출판사에 의해 저작권 문제 발생시 게시물이 비공개 될 수 있음을 알립니다.
이전 포스팅 ( Real MySQL [1] 실행계획 - id, select_type 칼럼) 에서 계속...
table 칼럼
MySQL의 실행 계획은 단위 SELECT 쿼리 기준이 아니라 테이블 기준으로 표시된다. 아래와 같이 별도의 테이블을 사용하지 않는 SELECT 쿼리의 경우에는 table 이 null로 표시된다.
EXPLAIN SELECT NOW();
EXPLAIN SELECT NOW() FROM DUAL;
일부 DBMS에서는 SELECT문장이 반드시 FROM 절을 가져야 하는 제약이 있어 이를 위해 'DUAL'이라는 스칼라(칼럼1개짜리 레코드 1개를 가지는) 값을 가지는 테이블을 사용하곤 한다. MySQL은 FROM 절이 없어도 쿼리 실행하는데 문제가 없다.
Table 컬럼에 <derived> 또는 <union>과 같이 "<>"로 둘러싸인 이름이 명시되는 경우가 많은데, 이 테이블은 임시 테이블을 의미한다. "<>"안에 항상 표시되는 숫자는 단위 SELECT 쿼리의 id를 지칭한다.
첫 번째 라인의 table 컬럼이 <derived 2> 인데, 단위 SELECT 쿼리의 아이디가 2번인 실행 계획으로부터 만들어진 파생 테이블을 가리킨다. 단위 SELECT 쿼리의 id 2번은 dept_emp 테이블로부터 SELECT된 결과가 저장된 파생 테이블이다.
실행 계획의 id, select_type, table 칼럼은 실행 계획의 각 라인에 명시된 테이블이 어떤 순서로 실행되는지를 판단하는 근거를 표시해준다. 위의 실행계획을 분석해보자
1) 첫 번째 라인의 테이블이 <derived2>라는 것으로 보아 이 라인보다 쿼리의 id가 2번인 라인이 먼저 실행되고 그 결과가 파생 테이블로 준비돼야 한다는 것을 알 수 있다.
2) 세 번째 라인의 쿼리 id 2번을 보면, select_type 칼럼의 값이 derived로 표시돼 있다. 즉, 이 라인은 table 칼럼에 표시된 dept_emp 테이블을 읽어서 파생 테이블을 생성하는 것을 알 수 있다.
3) 세 번째 라인의 분석이 끝났으므로 다시 실행 계획의 첫 번째 라인으로 돌아가자
4) 첫 번째 라인과 두 번째 라인은 같은 id 값을 가지고 있는 것으로 봐서 2개 테이블(첫 번째 라인의 <derived2>와 두 번째 라인의 e 테이블)이 조인되는 쿼리라는 것을 알 수 있다. <derived2> 테이블이 e 테이블보다 먼저 표시되었기 때문에 드라이빙 테이블이 되고 e 테이블은 드리븐 테이블이 된다. 즉 <derived2> 테이블을 먼저 읽어서 e 테이블로 조인을 실행했다는 것을 알 수 있다.
type 칼럼
쿼리의 실행계획에서 type 이후 칼럼은 MySQL 서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 의미한다. 방식이라 함은 인덱스를 사용했는지, 테이블을 풀 테이블 스캔으로 레코드를 읽었는지 등을 의미한다. 쿼리를 튜닝할 때 인덱스를 효율적으로 사용하는지 확인하는 것이 중요하므로 type 칼럼은 반드시 체크해야 한다.
MySQL 의 메뉴얼에서는 type 칼럼을 "조인 타입"으로 소개한다. 또한 하나의 테이블로부터 레코드를 읽는 작업도 조인처럼 처리한다. 그래서 SELECT 쿼리의 테이블 개수에 관계없이 실행 계획의 type 칼럼을 "조인 타입"이라고 명시하고 있다. 하지만 크게 조인과 연관 지어 생각하지 말고, 각 테이블의 접근 방식(Access Type)으로 해석하면 된다.
실행계획의 type 칼럼에 표시되는 값은 다음과 같다(MySQL 5.0, MySQL 5.1)
- system
- const
- eq_ref
- ref
- fulltext
- ref_or_null
- unique_subquery
- index_subquery
- range
- index_merge
- index
- ALL
12가지 중 ALL을 제외한 나머지는 모두 인덱스를 사용하는 접근방법이다. ALL은 인덱스를 사용하지 않고 테이블을 처음부터 끝까지 읽어서 레코드를 가져오는 풀 테이블 스캔 접근 방식을 의미한다.
하나의 단위 SELECT 쿼리는 위의 접근 방법 중 단 하나만 사용할 수 있다. 또한 index_merge를 제외한 나머지 접근 방법은 반드시 하나의 인덱스만 사용한다. 접근 방법이 2개 이상 표시되지 않으며, index_merge 이외의 type 에서는 인덱스 항목에도 단 하나의 인덱스 이름만 표시된다.
접근 방식의 성능이 빠른 순서대로 알아보자.
system
레코드가 1건만 존재하는 테이블 또는 한 건도 존재하지 않는 테이블을 참조하는 형태의 접근 방법을 system이라 한다. InnoDB 테이블에서는 나타나지 않고, MyISAM이나 MEMORY 테이블에서만 사용되는 접근 방법이다.
EXPLAIN
SELECT * FROM tb_dual;
위 예제에서 tb_dual 테이블은 레코드가 1건만 들어있는 MyISAM 테이블이다. 이 테이블을 InnoDB로 변환하면 어떻게 될까?
system은 테이블에 레코드가 1건 이하인 경우에만 사용할 수 있는 접근 방법이므로 실제 애플리케이션에서 사용되는 쿼리의 실행 계획에서는 거의 보이지 않는다.
const
테이블의 레코드의 건수에 관계없이 쿼리가 프라이머리 키나 유티크 키 칼럼을 이용하는 WHERE 조건절을 가지고 있으며, 반드시 1건을 반환하는 쿼리의 처리방식을 const라고 한다. 다른 DBMS에서는 이를 유니크 인덱스 스캔(UNIQUE INDEX SCAN) 이라고도 표현한다.
EXPLAIN
SELECT * FROM employees WHERE emp_no=10001;
다음과 같이 다중 컬럼으로 구성된 프라이머리 키나 유니크 키 중에서 인덱스의 일부 칼럼만 조건으로 사용할 때는 const 타입의 접근 방법을 사용할 수 없다. 이런 경우 실제 레코드가 1건만 저장돼 있더라도 MySQL 엔진이 데이터를 읽어보지 않고서는 레코드가 1건이라는 것을 확신할 수 없기 때문이다.
EXPLAIN
SELECT * FROM dept_emp WHERE dept_no='d005';
프라이머리 키의 일부만 조건으로 사용할 때는 접근 방식이 const 가 아닌 ref 로 표시된다.
하지만 프라이머리 키나 유니크 인덱스의 모든 칼럼을 동등 조건으로 WHERE 절에 명시하면 다음 예제와 같이 const 접근 방법을 사용한다.
EXPLIAN
SELECT * FROM dept_emp WHERE dept_no='d005' AND emp_no=10001;
eq_ref
eq_ref 접근 방법은 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시된다. 조인에서 처음 읽은 테이블의 칼럼 값을, 그 다음 읽어야 할 테이블의 프라이머리 키나 유니크 키 칼럼의 검색 조건에 사용할 때를 eq_ref라고 한다. 이 때 두번째 이후에 읽는 테이블의 type 칼럼에 eq_ref가 표시된다.
또한 두 번째 이후에 읽히는 테이블을 유니크 키로 검색할 때 그 유니크 인덱스는 NOT NULL이어야 하며, 다중 칼럼으로 만들어진 프라이머리 키나 유니크 인덱스라면 인덱스의 모든 칼럼이 비교 조건에 사용돼야만 eq_ref 접근 방법이 사용될 수 있다. 즉, 조인에서 두 번째 이후에 읽는 테이블에서 반드시 1건만 존재한다는 보장이 있어야 사용할 수 있는 접근법이다.
EXPLAIN
SELECT * FROM dept_emp de, employees e
WHERE e.emp_no=de.emp_no AND de.dept_no='d005';
위 쿼리 실행계획에서 id가 1로 같으므로 두 개의 테이블이 조인으로 실행된다는 것을 알 수 있다. dept_emp 테이블이 실행 계획의 위쪽에 있으므로 dept_emp 테이블을 읽고 "e.emp_no=de.emp_no" 조건을 이용해 employees 테이블을 검색하고 있다. employees 테이블의 emp_no는 프라이머리 키라서 실행 계획의 두 번째 라인의 type 칼럼이 eq_ref 로 표시되었다.
ref
ref 접근 방법은 eq_ref와 달리 조인의 순서와 관계없이 사용되며, 프라이머리 키, 유니크 키 등의 제약 조건도 없다. 인덱스 조건에 관계없이 동등(Equal) 조건으로 검색할 때는 ref 접근 방법이 사용된다. 동등조건으로만 비교되므로 매우 빠른 레코드 조회 방법의 하나이다.
EXPLAIN
SELECT * FROM dept_emp WHERE dept_no='d0005';
위의 예에서 WHERE 조건에 동등(Equal) 조건에 일치하는 레코드가 1건이라는 보장이 없어서 ref 접근 방법이 사용되었으며 실행 계획의 ref 컬럼 값에는 const가 명시되었다. 이 const는 접근 방식이아니라 ref 비교 방식으로 사용된 입력값이 상수('d005') 였음을 의미한다.
지금까지 3가지 type 을 정리해보자.
const
조인의 순서에 관계없이 프라이머리 키나 유니크 키의 모든 칼럼에 대해 동등(Equal) 조건으로 검색(반드시 1건 레코드만 반환)
eq_ref
조인에서 첫 번째 읽은 테이블의 컬럼값을 이용해 두 번째 테이블을 프라이머리 키나 유니크 키로 동등(Equal) 조건 검색(두 번째 테이블은 반드시 1건의 레코드만 반환)
ref
조인의 순서와 인덱스의 종류에 관계없이 동등(Equal) 조건으로 검색 (1건의 레코드만 반환된다는 보장이 없어도 됨)
세 가지 접근방식 모두 WHERE 조건절에 비교 연산자는 동등 비교 연산자(=) 이여야 한다는 공통점이 있다. "<=>" 연산자는 NULL에 대한 비교 방식만 다를뿐 "=" 연산자와 동일하다. 세 가지 모두 매우 좋은 접근 방법으로 인덱스의 분포도가 나쁘지 않다면 성능상의 문제를 일으키지 않는 접근 방법이다.
다음 포스팅에서 계속...
