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

de_sj_awa·2021년 9월 30일

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

3. table 칼럼

MySQL의 실행 계획은 단위 SELECT 쿼리 기준이 아니라 테이블 기준으로 표시된다. 만약 테이블의 이름에 별칭이 부여된 경우에는 별칭이 표시된다. 다음 예제 쿼리와 같이 별도의 테이블을 사용하지 않는 SELECT 쿼리의 경우에는 table 칼럼에 NULL이 표시된다.

EXPLAIN SELECT NOW();
EXPLAIN SELECT NOW() FROM DUAL;

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	(NULL)						No tables used

일부 DBMS에서는 SELECT 문장이 반드시 FROM 절을 가져야 하는 제약이 있다. 이를 위해 DUAL이라는 스칼라(칼럼 1개짜리 레코드 1개를 가지는) 값을 가지는 테이블을 사용하곤 하는데, MySQL에서는 FROM 절이 없어도 쿼리 실행에 특별히 문제가 되지는 않는다. 하지만 다른 DBMS와의 호환을 위해 'FROM DUAL'로 사용해도 문제없이 실행된다. 위 예제에서 두 번째 쿼리의 "FROM DUAL"은 없어도 무방하다. 즉, 첫 번째 쿼리와 두 번째 쿼리는 같은 쿼리다.

Table 칼럼에 <derived> 또는 <union>와 같이 "<>"로 둘러싸인 이름이 명시되는 경우가 많은데, 이 테이블은 임시 테이블을 의미한다. 또한 "<>" 안에 항상 표시되는 숫자는 단위 SELECT 쿼리의 id를 지칭한다. 다음 실행 계획을 한번 살펴보자.

id	select_type	table	type	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL				10420
1	PRIMARY	e	eq_ref	PRIMARY	4	de1.emp_no	1
2	DERIVED	dept_emp	range	ix_fromdate	3		20550

위의 예에서 첫 번째 라인의 table 칼럼의 값이 <derived2>인데, 이것은 단위 SELECT 쿼리의 아이디가 2번인 실행 계획으로부터 만들어진 파생 테이블을 가리킨다. 단위 SELECT 쿼리의 id 2번(실행 계획의 최하위 라인)은 dept_emp 테이블로부터 SELECT된 결과가 저장된 파생 테이블이라는 점을 알 수 있다. 아래 그림은 실행 계획의 table 칼럼에 표시된 정보를 해석하는 방법을 보여준다.

지금까지 실행 계획의 id 칼럼과 select_type 그리고 table 칼럼을 살펴봤다. 이 3개의 칼럼은 실행 계획의 각 라인에 명시된 테이블이 어떤 순서로 실행되는지를 판단하는 근거로 표시해준다. 그러면 이 3개의 칼럼만으로 위의 실행 계획을 분석해 보자.

첫 번째 라인의 테이블이 <derived2>라는 것으로 보아 이 라인보다 쿼리의 id가 2번인 라인이 먼저 실행되고 그 결과가 파생 테이블로 준비돼야 한다는 것을 알 수 있다.
세 번째 라인의 쿼리 id 2번을 보면, select_type 칼럼의 값이 DERIVED로 표시돼 있다. 즉, 이 라인은 table 칼럼에 표시된 dept_emp 테이블을 읽어서 파생 테이블을 생성하는 것을 알 수 있다.
세 번째 라인의 분석이 끝났으므로 다시 실행 계획의 첫 번째 라인으로 돌아가자.
첫 번째 라인과 두 번째 라인은 같은 id 값을 가지고 있는 것으로 봐서 2개 테이블(첫 번째 라인의 <derived2>와 두 번째 라인의 e 테이블)이 조인되는 쿼리라는 사실을 알 수 있다. 그런데 <derived2> 테이블이 e 테이블보다 먼저(윗 라인)에 표시됐기 때문에 <derived2>가 드라이빙 테이블이 되고, e 테이블이 드리븐 테이블이 된다는 것을 알 수 있다. 즉, <derived2> 테이블을 먼저 읽어서 e 테이블로 조인을 실행했다는 것을 알 수 있다.

이제 MySQL에서 쿼리의 실행 계획을 어떤 순서로 읽는지 대략 파악됐을 것이다. 방금 분석해 본 실행 계획의 실제 쿼리를 한 번 살펴보자.

SELECT *
FROM
  (SELECT de.emp_no FROM dept_emp de) tb,
  employees e
WHERE e.emp=tb.emp_no;

MySQL은 다른 DBMS와 달리 FROM 절에 사용된 서브 쿼리(Derived, 파생 테이블)는 반드시 별칭을 가져야 한다. 그렇지 않으면 별칭이 부여되지 않았다는 에러 메시지가 출력되고 쿼리는 실행되지 않을 것이다. 쿼리를 작성하거나 실행 계획을 확인할 때는 임시 테이블의 별칭을 잊지 말고 명시해야 한다.
mysql > SELECT dttm FROM (SELECT NOW() AS dttm);
ERROR 1248 (42000) : Every derived table must have its own alias
mysql > SELECT dttm FROM (SELECT NOW() AS dttm) derived_table_alias;
+----------------------+
| datetime             |
+----------------------+
| 2011-02-05 14:57:23  |
+----------------------+

4. type 칼럼

쿼리의 실행 계획에서 type 이후의 칼럼은 MySQL 서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 의미한다. 여기서 방식이라 함은 인덱스를 사용해 레코드를 읽었는지 아니면 테이블을 처음부터 끝까지 읽는 풀 테이블 스캔으로 레코드를 읽었는지 등을 의미한다. 일반적으로 쿼리를 튜닝할 때 인덱스를 효율적으로 사용하는지 확인하는 것이 중요하므로 실행 계획에서 type 칼럼은 반드시 체크해야 할 중요한 정보다.

MySQL의 매뉴얼에서는 type 칼럼을 "조인 타입"으로 소개한다. 또한 MySQL에서는 하나의 테이블로부터 레코드를 읽는 작업도 조인처럼 처리한다. 그래서 SELECT 쿼리의 테이블 개수와 관계없이 실행 계획의 type 칼럼을 "조인 타입"이라고 명시하고 있다. 하지만 크게 조인과 연관 지어 생각하지 말고, 각 테이블의 접근 방식(Access type)으로 해석하면 된다.

실행 계획의 type 칼럼에 표시될 수 있는 값은 버전에 따라 조금씩 차이가 있을 수 있지만, 현재 많이 사용되는 MySQL 5.0과 MySQL 5.1 버전에서는 다음과 같은 값이 표시된다.

system
const
eq_ref
ref
fulltest
ref_or_null
unique_subquery
index_subquery
range
index_merge
index
ALL

위의 12가지 접근 방법 중에서 하단의 ALL을 제외한 나머지는 모두 인덱스를 사용하는 접근 방법이다. ALL은 인덱스를 사용하지 않고, 테이블을 처음부터 끝까지 읽어서 레코드를 가져오는 풀 테이블 스캔 접근 방식을 의미한다. 하나의 단위 SELECT 쿼리는 위의 접근 방법 중에서 단 하나만 사용할 수 있다. 또한 index_merge를 제외한 나머지 접근 방법은 반드시 하나의 인덱스만 사용한다. 그러므로 실행 계획의 각 라인에 접근 방법이 2개 이상 표시되지 않으며, index_merge 이외의 type에서는 인덱스 항목에도 단 하나의 인덱스 이름만 표시된다.

이제 실행 계획의 type 칼럼에 표시될 수 있는 값들을 위의 순서대로 하나씩 살펴보자. 참고로 위에 표시된 각 접근 방식은 성능이 빠른 순서대로 나열된 것(MySQL에서 부여한 우선순위임)이다. MySQL 옵티마이저는 이런 접근 방식과 비용을 함께 계산해서 최소의 비용이 필요한 접근 방식을 선택한다.

system

레코드가 1건만 존재하는 테이블 또는 한 건도 존재하지 않는 테이블을 참조하는 형태의 접근 방법을 system이라고 한다. 이 접근 방식은 InnoDB 테이블에서는 나타나지 않고, MyISAM이나 MEMORY 테이블에서만 사용되는 접근 방식이다.

EXPLAIN
SELECT * FROM tb_dual;

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	tb_dual	system				1

위 예제에서 tb_dual 테이블은 레코드가 1건만 들어 있는 MyISAM 테이블이다. 만약 이 테이블을 InnoDB로 변환하면 어떻게 될까?

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	tb_dual	index	PRIMARY	1		1	Using index

쿼리의 모양에 따라 조금은 다르겠지만 접근 방법(type 칼럼)이 ALL 또는 index로 표시될 가능성이 크다. system은 테이블에 레코드가 1건 이하인 경우에만 사용할 수 있는 접근 방법이므로 실제 애플리케이션에서 사용되는 쿼리의 실행 계획에서는 거의 보이지 않는다.

const

테이블의 레코드의 건수에 관계없이 쿼리가 프라이머리 키나 유니크 키 칼럼을 이용하는 WHERE 조건절을 가지고 있으며, 반드시 1건을 반환하는 쿼리의 처리 방식을 const라고 한다. 다른 DBMS에서는 이를 유니크 인덱스 스캔(UNIQUE INDEX SCAN)이라고도 표현한다.

EXPLAIN
SELECT * FROM employees WHERE emp_no=10001;

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	employees	const	PRIMARY	4	const	1

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	de	ref	PRIMARY	12	const	53288	Using where
1	SIMPLE	e	eq_ref	PRIMARY	4	employees.de.emp_no	1

ref

ref 접근 방법은 eq_ref와는 달리 조인의 순서와 관계없이 사용되며, 또한 프라이머리 키나 유니크 키 등의 제약 조건도 없다. 인덱스의 종류와 관계없이 동등(Equal) 조건으로 검색할 때는 ref 접근 방법이 사용된다. ref 타입은 반환되는 레코드가 반드시 1건이라는 보장이 없으므로 const나 eq_ref보다는 빠르지 않다. 하지만 동등한 조건으로만 비교되므로 매우 빠른 레코드 조회 방법의 하나다.

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

위의 예에서는 dept_emp 테이블의 프라이머리 키를 구성하는 칼럼(dept_no + emp_no) 중에서 일부만 동등(Equal) 조건으로 WHERE 절에 명시됐기 때문에 조건에 일치하는 레코드가 1건이라는 보장이 없다. 그래서 const가 아닌 ref 접근 방법이 사용됐으며 실행 계획의 ref 칼럼 값에는 const가 명시됐다. 이 const는 접근 방식이 아니라, ref 비교 방식으로 사용된 입력 값이 상수('d005')였음을 의미한다.

지금까지 나온 실행 계획의 type에 대해 간단히 비교하면 다음과 같이 정리할 수 있다.

const

조인의 순서와 관계없이 프라이머리 키나 유니크 키의 모든 칼럼에 대해 동등(Equal) 조건으로 검색(반드시 1건의 레코드만 반환)

eq_req

조인에서 첫 번째 읽은 테이블의 칼럼값을 이용해 두 번째 테이블을 프라이머리 키나 유니크 키로 동등(Equal) 조건 검색(두 번째 테이블은 반드시 1건의 레코드만 반환)

ref

조인의 순서와 인덱스의 종류에 관계없이 동등(Equal) 조건으로 검색(1건의 레코드만 반환된다는 보장이 없어도 됨)

이 세 가지 접근 방식 모두 WHERE 조건절에 사용되는 비교 연산자는 동등 비교 연산자이어야 한다는 공통점이 있다. 동등 비교 연산자는 "=" 또는 "<=>"을 의미한다. "<=>" 연산자는 NULL에 대한 비교 방식만 조금 다를 뿐 "=" 연산자와 같은 연산자다.

또한 세 가지 모두 매우 좋은 접근 방법으로 인덱스의 분포도가 나쁘지 않다면 성능상의 문제를 일으키지 않는 접근 방법이다. 쿼리를 튜닝할 때도 이 세 가지 접근 방법에 대해서는 크게 신경 쓰지 않고 넘어가도 무방하다.

fulltext

fulltext 접근 방법은 MySQL의 전문 검색(Fulltext) 인덱스를 사용해 레코드를 읽는 접근 방법을 의미한다. 지금 살펴보는 type의 순서가 일반적으로 처리 성능의 순서이긴 하지만 실제로 데이터의 분포나 레코드의 건수에 따라 빠른 순서는 달라질 수 있다. 이는 비용 기반의 옵티마이저에서 통계 정보를 이용해 비용을 계산하는 이유이기도 하다. 하지만 전문 검색 인덱스는 통계 정보가 관리되지 않으며, 전문 검색 인덱스를 사용하려면 전혀 다른 SQL 문법을 사용해야 한다. 그래서 MySQL 옵티마이저는 전문 인덱스를 사용할 수 있는 SQL에서는 쿼리의 비용과는 관계없이 거의 매번 fulltext 접근 방법을 사용한다. 물론, fulltext 접근 방법보다 명백히 빠른 const나 eq_ref 또는 ref 접근 방법을 사용할 수 있는 쿼리에서는 억지로 fulltext 접근 방법을 선택하지는 않는다.

MySQL의 전문 검색 조건은 우선순위가 상당히 높다. 쿼리에서 전문 인덱스를 사용하는 조건과 그 이외의 일반 인덱스를 사용하는 조건을 함께 사용하면 일반 인덱스의 접근 방법이 const나 eq_ref, 그리고 ref가 아니면 일반적으로 MySQL은 전문 인덱스를 사용하는 조건을 선택해서 처리한다.

전문 검색은 "MATCH ... AGAINST ..." 구문을 사용해서 실행하는데, 반드시 해당 테이블에 전문 검색용 인덱스가 준비돼 있어야만 한다. 만약 테이블에 전문 인덱스가 없다면 쿼리는 오류가 발생하고 중지될 것이다. 다음의 "MATCH ... AGAINST ..." 예제 쿼리를 한 번 살펴보자.

EXPLAIN
SELECT *
FROM employee_name
WHERE emp_no=10001
  AND emp_no BETWEEN 10001 AND 10005
AND MATCH(first_name, last_name) AGAINST('Facello' IN BOOLEAN MODE);

위 쿼리 문장은 3개의 조건을 가지고 있다. 첫 번째 조건은 employee_name 테이블의 프라이머리 키를 1건만 조회하는 const 타입의 조건이며, 두 번째 조건은 밑에서 설명할 range 타입의 조건이다. 그리고 마지막 세 번째 조건은 전문 검색(Fulltext) 조건이다. 이 문장의 실행 계획을 보면 다음과 같다.

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	employee_name	const	PRIMARY	4	const	1	Using where

최종적으로 MySQL 옵티마이저가 선택한 것은 첫 번째 조건인 const 타입의 조건이다. 만약 const 타입의 첫 번째 조건이 없으면 둘 중에서 어느 것을 선택할까? 다음의 실행 계획은 첫 번째 조건을 빼고 실행 계획을 확인해 본 결과다.

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	employee_name	fulltext	fx_name	0		1	Using where

이번에는 range 타입의 두 번째 조건이 아니라 전문 검색(Fulltext) 조건인 세 번째 조건을 선택했다. 일반적으로 쿼리에 전문 검색 조건(MATCH ... AGAINST ...)을 사용하면 MySQL은 아무런 주저 없이 fulltext 접근 방식을 사용하는 경향이 있다. 하지만 지금까지의 경험으로 보면 전문 검색 인덱스를 이용하는 fulltext보다 일반 인덱스를 이용하는 range 접근 방법이 더 빨리 처리되는 경우가 더 많았다. 전문 검색 쿼리를 사용할 때는 각 조건별로 성능을 확인해 보는 편이 좋다.

ref_or_null

이 접근 방법은 ref 접근 방식과 같은데, NULL 비교가 추가된 형태다. 접근 방식의 이름 그대로 ref 방식 또는 NULL 비교(IS NULL) 접근 방식을 의미한다. 실제 업무에서 많이 보이지도 않고, 별로 존재감이 없는 접근 방법이므로 대략의 의미만 기억해두어도 충분하다.

EXPLAIN
SELECT * FROM titles WHERE to_date='1985-03-31' OR to_date IS NULL;

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	titles	ref_or_null	ix_todate	4	const	2	Using where; Using index

unique_subquery

WHERE 조건절에서 사용될 수 있는 IN (subquery) 형태의 쿼리를 위한 접근 방식이다. unique_subquery의 의미 그대로 서브 쿼리에서 중복되지 않은 유니크한 값만 반환할 때 이 접근 방법을 사용한다.

EXPLAIN
SELECT * FROM departments WHERE dept_no IN (
  SELECT dept_no FROM dept_emp WHERE emp_no=10001);

id	select_type	table	type	key	key_len	ref	rows	Extra
1	PRIMARY	departments	index	ux_deptname	123		9	Using where; Using index
2	DEPENDENT SUBQUERY	dept_emp	unique _subquery	PRIMARY	16	func, const	1	Using index; Using where

위 쿼리 문장의 IN (subquery) 부분에서 subquery를 살펴보자. emp_no=10001인 레코드 중에서 부서 번호는 중복이 없기 때문에(dept_emp 테이블에서 프라이머리 키가 dept_no + emp_no 이므로) 실행 계획의 두 번째 라인의 dept_emp 테이블의 접근 방식은 unique_subquery로 표시된 것이다.

index_subquery

IN 연산자의 특성상, IN (subquery) 또는 IN (상수 나열) 형태의 조건은 괄호 안에 있는 값의 목록에서 중복된 값이 먼저 제거돼야 한다. 방금 살펴본 unique_subquery 접근 방법은 IN (subquery) 조건의 subquery가 중복된 값을 만들어내지 않는다는 보장이 있으므로 별도의 중복을 제거할 필요가 없었다. 하지만 IN (subquery)에서 subquery가 중복된 값을 반환할 수는 있지만 중복된 값을 인덱스를 이용해 제거할 수 있을 때 index_subquery 접근 방법이 사용된다.

명확한 이해를 위해 index_subquery와 unique_subquery 접근 방법의 차이를 다시 한 번 정리해 보자.

unique_subquery

IN (subquery) 형태의 조건에서 subquery의 반환 값에는 중복이 없으므로 별도의 중복 제거 작업이 필요하지 않음

index_subquery

IN (subquery) 형태의 조건에서 subquery의 반환 값에 중복된 값이 있을 수 있지만 인덱스를 이용해 중복된 값을 제거할 수 있음

사실 index_subquery나 unique_subquery 모두 IN() 안에 있는 중복 값을 아주 낮은 비용으로 제거한다.

다음 쿼리에서 IN 연산자 내의 서브 쿼리는 dept_emp 테이블을 dept_no로 검색한다. dept_emp 테이블의 프라이머리 키가 (dept_no + emp_no)로 만들어져 있으므로 서브 쿼리는 프라이머리 키의 dept_no 칼럼을 'd001'부터 'd003'까지 읽으면서 dept_no 값만 가져오면 된다. 또한 이미 프라이머리 키는 dept_no 칼럼의 값 기준으로 정렬돼 있어서 중복된 dept_no를 제거하기 위해 별도의 정렬 작업이 필요하지 않다.

EXPLAIN
SELECT * FROM departments WHERE dept_no IN (
  SELECT dept_no FROM dept_emp WHERE dept_no BETWEEN 'd001' AND 'd003');

id	select_type	table	type	key	key_len	ref	rows	Extra
1	PRIMARY	departments	index	ux_ deptname	122		9	Using where; Using index
2	DEPENDENT SUBQUERY	dept_emp	index subquery	PRIMARY	12	func	18626	Using index; Using where

range

range는 우리가 익히 알고 있는 인덱스 레인지 스캔 형태의 접근 방법이다. range는 인덱스를 하나의 값이 아니라 범위로 검색하는 경우를 의미하는데, 주로 "<, >, IS NULL, BETWEEN, IN, LIKE" 등의 연산자를 이용해 인덱스를 검색할 때 사용된다. 일반적으로 애플리케이션의 쿼리가 가장 많이 사용하는 접근 방법인데, MySQL이 가지고 있는 접근 방법 중에서 상당히 우선순위가 낮다. 하지만 이 접근 방법도 상당히 빠르며, 모든 쿼리가 이 접근 방법만 사용해도 어느 정도의 성능은 보장된다.

EXPLIAN
SELECT dept_no FROM dept_emp WHERE dept_no BETWEEN 'd001' AND 'd003';

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	dept_emp	range	PRIMARY	12		121890	Using where; Using index

인덱스 레인지 스캔이라고 하면 const, ref, range라는 세 가지 접근 방법을 모두 묶어서 지칭하는 것이다. 또한 "인덱스를 효율적으로 사용한다" 또는 "범위 제한 조건으로 인덱스를 사용한다"는 표현 모두 이 세 가지 접근 방법을 의미한다.

index_merge

지금까지 설명한 다른 접근 방식과는 달리 index_merge 접근 방식은 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후 그 결과를 병합하는 처리 방식이다. 하지만 여러 번의 경험을 보면 이름만큼 그렇게 효율적으로 작동하는 것 같지는 않다. index_merge 접근 방식에는 다음과 같은 특징이 있다.

여러 인덱스를 읽어야 하므로 일반적으로 range 접근 방식보다 효율성이 떨어진다.
AND와 OR 연산이 복잡하게 연결된 쿼리에서는 제대로 최적화되지 못할 때가 많다.
전문 검색 인덱스를 사용하는 쿼리에서는 index_merge가 적용되지 않는다.
index_merge 접근 방식으로 처리된 결과는 항상 2개 이상의 집합이 되기 때문에 그 두 집합의 교집합이나 합집합 또는 중복 제거와 같은 부가적인 작업이 더 필요하다.

다음은 두 개의 조건이 OR로 연결된 쿼리다. 그런데 OR로 연결된 두 개 조건이 모두 각각 다른 인덱스를 최적으로 사용할 수 있는 조건이다. 그래서 MySQL 옵티마이저는 "emp_no BETWEEN 10001 AND 11000" 조건은 employees 테이블의 프라이머리 키를 이용해 조회하고, "first_name='Smith'" 조건은 ix_firstname 인덱스를 이용해 조회한 후 두 결과를 병합하는 형태로 처리하는 실행 계획을 만들어 낸 것이다.

EXPLAIN
SELECT * FROM employees
WHERE emp_no BETWEEN 10001 AND 11000
	OR first_name='Smith';

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	employees	index _merge	PRIMARY,ix _firstname	4,44		1521	Using union (PRIMARY, ix _firstname); Using where

index

index 접근 방법은 많은 사람들이 자주 오해하는 접근 방법이다. 접근 방식의 이름이 index라서 MySQL에 익숙하지 않은 사람이 "효율적으로 인덱스를 사용하는구나"라고 생각하게 만드는 것 같다. 하지만 index 접근 방식은 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔을 의미한다. range 접근 방식과 같이 효율적으로 인덱스의 필요한 부분만 읽은 것을 의미하는 것은 아니라는 점을 잊지 말자.

index 접근 방식은 테이블을 처음부터 끝까지 읽는 풀 테이블 스캔 방식과 비교했을 때 비교하는 레코드 건수는 같다. 하지만 인덱스는 일반적으로 데이터 파일 전체보다는 크기가 작아서 풀 테이블 스캔보다는 효율적이므로 풀 테이블 스캔보다는 빠르게 처리된다. 또한 쿼리의 내용에 따라 정렬된 인덱스의 장점을 이용할 수 있으므로 풀 테이블 스캔보다는 훨씬 효율적으로 처리될 수도 있다. index 접근 방법은 다음의 조건 가운데 (첫 번째 + 두 번째) 조건을 충족하거나 (첫 번째 + 세 번째) 조건을 충족하는 쿼리에서 사용되는 읽기 방식이다.

range나 const 또는 ref와 같은 접근 방식으로 인덱스를 사용하지 못하는 경우
인덱스에 포함된 칼럼만으로 처리할 수 있는 쿼리인 경우(쯕, 데이터 파일을 읽지 않아도 되는 경우)
인덱스를 이용해 정렬이나 그룹핑 작업이 가능한 경우(즉, 별도의 정렬 작업을 피할 수 있는 경우)

다음 쿼리는 아무런 WHERE 조건이 없으므로 range나 const 또는 ref 접근 방식을 사용할 수 없다. 하지만 정렬하려는 칼럼은 인덱스(ux_deptname)가 있으므로 별도의 정렬 처리를 피하려고 index 접근 방식이 사용된 예제다.

EXPLAIN
SELECT * FROM departments ORDER BY dept_name DESC LIMIT 10;

id	select_type	table	type	possible_ keys	key	key_len	ref	rows	Extra
1	SIMPLE	departments	index		ux_ deptname	123		9	Using index

이 예제의 실행 계획은 테이블의 인덱스를 처음부터 끝까지 읽는 index 접근 방식이지만 LIMIT 조건이 있기 때문에 상당히 효율적인 쿼리다. 단순히 인덱스를 거꾸로 (역순으로) 읽어서 10개만 가져오면 되기 때문이다. 하지만 LIMIT 조건이 없거나 가져와야 할 레코드 건수가 많아지면 상당히 느려질 것이다.

ALL

우리가 흔히 알고 있는 풀 테이블 스캔을 의미하는 접근 방식이다. 테이블을 처음부터 끝까지 전부 읽어서 불필요한 레코드를 제거(체크 조건이 존재할 때)하고 반환한다. 풀 테이블 스캔은 지금까지 설명한 접근 방법으로는 처리할 수 없을 때 가장 마지막에 선택되는 가장 비효율적인 방법이다.

다른 DBMS와 같이 InnoDB도 풀 테이블 스캔이나 인덱스 풀 스캔과 같은 대량의 디스크 I/O를 유발하는 작업을 위해 한꺼번에 많은 페이지를 읽어들이는 기능을 제공한다. InnoDB에서는 이 기능을 "리드 어헤드(Read Ahead)"라고 하며, 한 번에 여러 페이지를 읽어서 처리할 수 있다. 데이터웨어하우스나 배치 프로그램처럼 대용량의 레코드를 처리하는 쿼리에서는 잘못 튜닝된 쿼리(억지로 인덱스를 사용하도록 튜닝된 쿼리)보다 더 나은 접근 방법이 되기도 한다. 쿼리를 튜닝한다는 것이 무조건 인덱스 풀 스캔이나 테이블 풀 스캔을 사용하지 못하게 하는 것은 아니라는 점을 기억하자.

일반적으로 index나 ALL 접근 방법은 작업 범위를 제한하는 조건이 아니므로 빠른 응답을 사용자에게 보내 줘야 하는 웹 서비스 등과 같은 OLTP 환경에는 적합하지 않다. 테이블이 매우 작지 않다면 실제로 테이블에 어느 정도 저장한 상태에서 쿼리의 성능을 확인해 보고 적용하는 것이 좋다.

MySQL에서는 연속적으로 인접한 페이지가 연속해서 몇 번 읽히게 되면 백그라운드로 작동하는 읽기 스레드가 최대 한 번에 64개의 페이지씩 한꺼번에 디스크로부터 읽어들이기 때문에 한 번에 페이지 하나씩 읽어들이는 작업보다는 상당히 빠르게 레코드를 읽을 수 있다. 이러한 작동 방식을 리드 어헤드(Read Ahead)라고 한다.

참고

Real MySQL

de_sj_awa

이것저것 관심많은 개발자.

이전 포스트

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

다음 포스트

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