참조: Real MySQL(도서)

[MySQL] 실행계획

쿼리 실행 절차

1. 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MYSQL서버가 이해할 수 있는 수준으로 분리한다.

2. SQL의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블로부터 읽고 어떤 인덱스를 사용해 테이블을 읽을지 선택한다.

3. 두 번째 단계에서 결정된 테이블의 읽깃 순서나 선택된 인덱스를 이용해 스토리지 엔진 으로부터 데이터를 가져온다.

##

실행 계획 분석

쿼리문 앞에 EXPLAN 을 붙히면 된다.

EXPLAN SELECT * FROM employees e, salaries s WHERE e.emp_no=s.emp_no LIMIT 10

type 컬럼(제일 중요한 내용)

쿼리의 실행 계획에서 type 이후의 컬럼은 MySQL 서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 의미한다. 여기서 방식(인덱스를 사용해 레코드를 읽었는가 or 테이블을 처음부터 끝까지 읽는 풀 테이블 스캔방식 으로 읽었는가)을 의미한다.

type 종류

• system
• const
• eq_ref
• ref
• fulltext
• ref_or_null
• unique_subquery
• index_subquery
• range
• index_merge
• index
• ALL

여기서 ALL을 제외하면 나머지 모두 인덱스를 사용하는 접근 방식이다.

ALL은 테이블 처음부터 끝까지 읽는 풀 테이블 스캔 방식을 의미한다.

또한 index_merge를 제외한 나머지 접근 방법은 반드시 하나의 인덱스만 사용한다.

index_merge 이외의 type에서는 인덱스 항목에도 단 하나의 인덱스 이름만 표시된다.

• system

  레코드가 1건만 존재하는 테이블 또는 한 건도 존재하지 않는 테이블을 참조하는 형태의 접근 방법을 system이라고 한다.

EXPLAN select * from tb_dual;

단, 테이블이 InnoDB라면 type은 index,all 로 표시될 가능성이 높다.

• const

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

EXPLAN select * from employees where emp_no=10001;

단, 다중 컬럼으로 구성된 프라이머리 키나 유니크 키중에서 인덱스의 일부 컬럼만 조회 조건으로 사용할 때는 const타입의 접근 방법을 사용할 수 없다. 이 경우에는 실제 레코드가 1건만 저장돼 있더라도 데이터를 읽어보지 않고서는 레코드가 1건이라는 것을 확신할 수 없기 때문이다.

EXPLAN select * from dept_emp where dept_no='d005';

하지만 프라이머리 키나 유니크 인덱스의 모든 컬럼을 동등 조건으로 where절에 명시하면 다음 예제와 같이 const 접근 방법을 사용한다.

EXPLAN select * from dept_emp where dept_no='d005' and emp_no=10001;

• eq_ref

  여러 테이블이 조인되는 쿼리의 실행 계획에서 표시 된다. 조인에서 처음 읽은 테이블의 컬럼 값을, 그다음 읽어야할 테이블의 프라이머리 키나 유니크 키 컬럼의 검색조건에 사용할 때는 eq_ref라고 한다. 이때 두 번째 이후에 읽는 테이블의 type컬럼에 eq_ref가 표시된다.

두 번째 이후에 읽히는 테이블을 유니크 키로 검색할 때 그 유니크 인덱스는 not null 이어야 된다. 그리고 다중 컬럼으로 만들어진 프라이머리 키나 유니크 인덱스 라면 인덱스의 모든 컬럼이 비교 조건에 사용돼야만 eq_ref 접근 방법이 사용될 수 있다. 즉, 조인에서 두 번째 이후에 읽는 테이블에서 반드시 1건만 존재한다는 보장이 있어야 사용할 수 있다.

EXPLAN 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|key_len|ref|rows|Extra| |—|—|—|—|—|—|—|—|—| |1|SIMPLE|de|ref|PRIMARY|12|const|53288|Using index| |1|SIMPLE|e|eq_ref|PRIMARY|4|employees.de.emp_no|1||

예제 쿼리를 보면 첫 번째와 두 번째 의 id 가 1로 같으므로 두 개의 테이블이 조인으로 실행되는 것을 알 수 있다.

dept_emp 테이블이 실행 계획의 위쪽에 있으므로 dept_emp테이블을 먼저 읽고 “e.emp_no=de.emp_no” 조건을 이용해 employees 테이블을 검색하고 있다. employees 테이블의 emp_no는 프라이머리 키 라서 실행 계획의 두 번째 라인은 type 컬럼이 eq_ref로 표시되 있는 것이다.

• ref

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

EXPLAN select * from dept_emp where dept_no='d005';

예시에서 dept_emp 테이블의 프라이머리 키를 구성하는 컬럼(dept_no+emp_no)중에서 일부만 동등 조건으로 where 절에 명시됐기 때문에 조건에 일치하는 레코드가 1건이라는 보장이 없다.

const, eq_req, ref 요약

• const

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

• eq_req

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

• ref

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

3가지 방식 모두 매우 좋은 접근 방법으로 인덱스의 분포도가 나쁘지 않으면 성능상 문제를 일으키지 않는 접근 방법이다.