TIL

10.3(3) 실행 계획 분석 (type)

10.3.5 type 칼럼

• 쿼리 실행 계획에서 type 칼럼은 MySQL 서버가 각 테이블 레코드를 어떤 방식으로 읽었는지를 나타낸다.
  • 인덱스를 사용했는지 풀 테이블 스캔을 했는지
• 아래부터 설명되는 type 칼럼들은 성능이 빠른 순서대로 나열된 것이다.

system

• 레코드가 1건 또는 0건 존재하는 테이블을 참조하는 형태의 접근 방법
• InnoDB 스토리지 엔진에선 나타나지 않고 MyISAM이나 MEMORY 테이블에서만 사용되는 접근 방법이다
• 쿼리에 따라 다르겠지만 InnoDB에서 레코드가 1 또는 0건인 테이블을 조회할 땐 ALL 또는 index 접근 방법이 사용될 가능성이 크다.

const

• 테이블 레코드 건수와 관계 없이 WHERE 절 조건에 프라이머리 키나 유니크 키 칼럼을 이용하여 반드시 1건을 반환하는 쿼리의 처리 방식
  • 다른 DBMS에선 유니크 인덱스 스캔이라고도 표현한다.
• 프라이머리 키나 유니크 키가 다중 칼럼일 경우 일부 칼럼만으로 검색하면 const 타입의 접근 방법을 사용할 수 없다.
  • 실제 레코드가 1건만 저장돼 있더라도 MySQL 서버는 직접 읽어보지 않고서는 확신할 수 없다.
  • 대신 ref 접근 방식을 사용한다.

• const인 실행 계획은 옵티마이저가 쿼리를 최적화하는 단계에서 쿼리를 먼저 실행해 통째로 상수화한다.

    SELECT COUNT(*) FROM emplyees e1
    WHERE first_name=(
    	SELECT first_name FROM employees2 WHERE emp_no=10001
    );
      
    -- // 위 쿼리는 최적화 후 아래와 같이 변환된다.
    SELECT COUNT(*) FROM employees e1
    WHERE first_name='Jasminko'; -- // emp_no=100001인 칼럼
  

eq_ref

• 조인 쿼리의 실행 계획에서만 표시된다.
• eq_ref는 조인에서 처음 읽은 테이블의 칼럼값을, 그 다음 읽어야 할 테이블의 프라이머리 키나 유니크 키 칼럼의 검색 조건에 사용할 때 표시된다.
  • 두 번째 이후 읽는 테이블의 type 칼럼에 eq_ref가 표시된다.
• 조인에서 두 번째 이후 읽는 테이블에서 반드시 1건만 존재한다는 보장이 있어야 한다.
  • 유니크 키로 검색할 때 그 유니크 인덱스는 NOT NULL이어야 한다.
  • 다중 칼럼 인덱스라면 인덱스의 모든 칼럼이 비교 조건에 사용되어야 한다.

• 예제 쿼리를 살펴보자.

    EXPLAIN SELECT *
    FROM dept_emp de, employees e
    WHERE e.emp_no=de.emp_no AND de.dept_no='d005';
  

id	select_type	table	type	key	rows
1	SIMPLE	de	ref	PRIMARY	165571
1	SIMPLE	e	eq_ref	PRIMARY	1

• id 값이 1로 같으므로 조인이 실행된다는 것을 알 수 있다.
• 실행 계획에서 먼저 표시된 dept_emp 테이블을 먼저 읽고, e.emp_no=de.emp_no 조건을 이용해 두 번째 테이블인 employees 테이블을 검색한다.
• 이 때 emp_no는 employees 테이블의 프라이머리 키이기 때문에 실행 계획의 두 번째 라인은 type이 eq_ref로 표시된다.

ref

• 인덱스 종류와 조인에 관계 없이 동등(Equal) 조건으로 검색할 때 ref 접근 방법이 사용된다.
• 다중 칼럼 인덱스에서 일부 칼럼으로만 검색하는 경우 ref가 사용된다.

const, eq_ref, ref는 매우 좋은 접근 방법이므로 인덱스 분포도가 나쁘지 않다면 성능 문제를 일으키지 않는다. 쿼리 튜닝 시 이 세 가지에 대해서는 크게 신경쓰지 않아도 무방하다.

fulltext

• fulltext 접근 방법은 전문 검색(Full-text Search) 인덱스를 사용해 레코드를 읽는 접근 방법이다.
• 전문 검색 인덱스는 통계 정보가 관리되지 않기에 사용하려면 전혀 다른 SQL 문법을 사용해야 한다.
  • MATCH … AGAINST …
  • 우선 순위가 높아서 일반 인덱스와 함께 사용된다면 옵티마이저는 전문 검색 인덱스를 사용하게 된다.
    • 프라이머리 키 기반의 const 등의 접근 방법은 예외인 경우도 있다.

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

id	select_type	table	type	key	Extra
1	SIMPLE	employee_name	fulltext	fx_name	Using where; ft_hints: no_ranking

• 위 실행 게획을 보면 range 타입의 첫 번째 조건이 아닌 전문 검색 조건인 세 번째 조건을 선택했다.

ref_or_null

• ref 접근 방법과 같은데 NULL 비교가 추가된 형태이다.
  • 많이 활용되지는 않지만 사용되더라도 나쁘지 않은 접근 방법이다.

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

id	select_type	table	type	key	key_len	ref	rows
1	SIMPLE	titles	ref_or_null	ix_todate	4	const	2

unique_subquery

• unique_subquery는 WHERE 조건절에 IN(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
1	PRIMARY	departments	index	ux_deptnae	162
2	DEPENDENT SUBQUERY	dept_emp	unique_subquery	PRIMARY	20

MySQL 8.0 버전에는 세미 조인 최적화 기능이 많이 도입되었기 때문에 실제로는 더 최적화된 실행 계획이 보일 것이다. unique_subquery와 index_subquery 설멍에서 사용된 실행 계획은 semijoin 최적화 옵션을 비활성화한 항태에서 만들어진 실행 계획이다.

index_subquery

• index_subquery는 IN(subquery) 결과의 중복된 값을 인덱스를 이용해 제거할 수 있을 때 사용된다.
  • IN(subquery) 또는 IN(상수 나열) 형태 조건은 괄호 안의 값들 중 중복이 먼저 제거되어야 한다.
• unique_subquery와 달리 서브 쿼리가 유니크한 값만을 반환한다는 것을 확신할 수 없을 때 사용된다.

range

• range는 인덱스를 하나의 값이 아니라 범위로 검색하는 경우 사용된다.
  • <, >, IS NULL, BETWEEN, IN, LIKE 등 연산자에서 사용된다.
• 얼마나 많은 레코드가 필요하냐에 따라 차이는 있지만 상당히 빠른 방법이다.

EXPLAIN SELECT * FROM employees
WHERE emp_no BETWEEN 10002 AND 10004

index_merge

• index_merge 접근 방법은 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만든 후 병합해서 처리하는 방식이다.
• index_merge엔 다음의 특징이 있다.
  • 여러 인덱스를 읽어야 하므로 일반적인 range 보다 효율성이 떨어진다.
  • 전문 검색 인덱스에서는 index_merge가 적용되지 않는다.
  • index_merge로 처리된 결과는 항상 2개 이상의 집합이 되기 때문에 교집합이나 합집합 또는 중복 제거와 같은 부가 작업이 필요하다.

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

id	type	key	key_len	Extra
1	index_merge	PRIMARY, ix_firstname	4, 58	Using union(PRIMARY, ix_firstname); Using where

index

• index 접근 방법은 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔을 의미한다.
• 인덱스 풀 스캔은 테이블 풀 스캔 보다 빠르게 처리되는데 이유는 다음과 같다.
  • 비교하며 읽는 레코드 건수는 같다.
  • 인덱스는 일반적으로 데이터 파일 전체보다 크기가 작다.
  • 쿼리 내용에 따라 인덱스 풀 스캔은 정렬된 인덱스의 장점을 이용할 수 있다.
• index 접근 방법은 다음 조건 가운데 (1 + 2) 조건을 충족하거나 (1 + 3) 조건을 충족하는 쿼리에서 사용된다.
  1. range나 const, ref 같은 방법으로 인덱스를 사용하지 못하는 경우
  2. 인덱스에 포함된 칼럼만으로 처리할 수 있는 쿼리인 경우 (커버링 인덱스)
  3. 인덱스를 이용해 정렬이나 그루핑 작업이 가능한 경우

ALL

• ALL은 풀 테이블 스캔을 의미하며 지금까지 설명한 접근 방법으로 처리할 수 없을 때 선택하는 가장 비효율적인 방법이다.
• 테이블을 처음부터 끝까지 전부 읽어 불필요한 레코드를 제거하고 반호나한다.
• 풀 테이블 스캔은 대량의 디스크 I/O를 유발하기에 리드 어헤드 기능을 사용해 한 번에 여러 페이지를 읽어들인다.
• 일반적인 웹 서비스에서 index와 ALL 접근 방법은 적합하지 않다.