DB 실행 계획 분석 EXPLAIN - 1

데이터 베이스에서 실행 계획을 분석함으로써 효과적인 쿼리를 사용하는지 분석할 수 있다.

그 명령에 사용되는 것이 EXPLAIN 명령어이다.

 

EXPLAIN 명령을 실행하면 실행 계획을 산출한다.

 

 

실행 계획을 나타내는 각 컬럼이 무슨 정보를 가지고 있는지 알아보자.

 

1. id 칼럼

실행계획에 대한 id 정보 같은 id를 가진다면 조인을 한다는 것.

 

2. select_type 칼럼

쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼으로 다음 값을 가지고 있다.

• SIMPLE
  • 서브쿼리나 유니온을 사용하지 않은 아주 단순한 쿼리
• PRIMARY
  • 유니온이나 서브쿼리가 포함된 쿼리에서 가장 바깥에 실행되는 쿼리

• UNION
  • 유니온 연산이 필요한 쿼리
  • B의 경우를 보면 알 수 있다.
• UNION RESULT
  • 유니온의 결과를 담아두는 테이블을 의미한다.
  • 실제 쿼리에서 단위 쿼리가 아니기 때문에 별도로 id값은 부여되지 않는다.
• DEPENDENT UNION
  
  • 바깥 쿼리에 의존성을 가지고 있는 유니온 쿼리를 말한다.

에러가 나는 쿼리지만 EXPLAIN을 뽑아낼 수는 있다.

image 테이블에 영향을 받은 B 테이블 조회 쿼리

• SUBQUERY
  • FROM 절 이외에서 사용되는 서브 쿼리
• DERIVED
  • FROM 절에 사용되는 서브 쿼리 => 파생테이블
• DEPENDENT SUBQUERY
  • 바깥 쿼리에 의존성을 가지고 있는 서브쿼리
• UNCACHEABLE SUBQUERY
  • 캐시를 사용하지 못하는 서브 쿼리
• UNCACHEABLE UNION
  • 캐시를 사용하지 못하는 유니온 쿼리

 

3. table 칼럼

실행 계획의 단위 테이블을 알려주는 칼럼이다. 테이블을 사용하지 않는 경우는 NULL이 표시된다.

SELECT * FROM USER user; -> user 별칭 SELECT NOW(); -> 테이블 사용 X (NULL)

 

4. type 칼럼 = Access type

MySQL 서버가 각 테이블의 레코드를 어떤 방식(풀 테이블 스캔, 인덱스 레인지 스캔 등)으로 읽었는지를 나타내는 칼럼이다.

 

인덱스를 효율적으로 사용하고 있는지 판단하기 위한 좋은 지표로 사용이 가능하다.

 

type 칼럼의 값은 다음과 같다. 적은 숫자인 타입 요소가 일반적으로 효율적인 접근 방법이다.

1. system 2. const 3. eq_ref 4. ref 5. fulltext 6. ref_or_null 7. unique_subquery 8. index_subquery 9. range 10.index_merge 11. index 12. ALL

 

system

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

 

const

반드시 1건을 반환하는 쿼리의 처리방식, 다른 DBMS에서는 이를 유니크 인덱스 스캔(UNIQUE INDEX SCAN)이라고 함

 

eq_ref

- 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시

- 조인에서 처음 읽은 테이블(Driving table)의 칼럼 값을 그다음 읽어야 할 테이블(Driven table)의 유니크 키 칼럼의 검색 조건에 사용할 때 그 결과가 단 1건이 보장될 때의 접근 방법

- Driven table의 필터 결과가 단 1건일 때.

 

ref

- 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시

- 인덱스 종류와 관계없이 동등 조건으로 검색할 때 ref 접근 방법이 사용됨

- 동등 조건 연산자? = 두 개의 값이 같은지를 비교하는 연산자 ex) =, >, <, <=, >=, !=

 

fulltext

- 전문 검색 인덱스 (fulltext index)를 사용해 레코드를 읽는 접근 방법

- 각 조건별로 성능을 확인하는 것이 좋음

 

ref_or_null

- ref 방식에 null 비교 연산이 추가된 방법

 

unique_subquery

- where절에서 IN (subquery) 형태로 사용된 서브쿼리 접근 방식

- 서브 쿼리에서 중복되지 않은 유니크한 값만 반환할 때 이 방식을 사용한다.

- 서브 쿼리에서 중복되지 않은 값을 반환하기 때문에 중복 제거 작업이 불필요

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

 

index_subquery

- where절에서 IN (subquery) 형태로 사용된 서브쿼리 접근 방식

- 서브 쿼리에서 중복되지 않은 값을 반환한다는 보장이 없기 때문에 중복 제거 작업이 필요

- 인덱스를 이용해 중복 값을 제거할 수 있음

 

range

- 인덱스 레인지 스캔 형태의 접근 방법

- 인덱스를 하나의 값이 아니라 범위로 검색하는 경우

 

index_merge

- 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후 그 결과를 병합하는 처리 방식

- 여러 인덱스를 읽어야 하므로 일반적으로 range 접근 방식보다 효율성이 떨어짐

- AND와 OR 연산이 복잡하게 연결된 쿼리에서는 제대로 최적화되지 못할 때가 많다.

- Index_merge 접근 방식으로 처리된 결과는 항상 2개 이상의 집합이 되기 때문에 그 두 집합의 교집합이나 합집합 또는 중복 제거와 같은 부가적인 작업이 필요.

 

index

- 인덱스 풀 스캔 방식

- 이름의 분위기 만큼 성능이 보장되지 않음

- 다음 조건을 만족하는 경우에 사용을 권장함.

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

 

ALL

- 풀 테이블 스캔

- 가장 안좋은 방식

 

이어서 ...

 

 

DB 실행 계획 분석 EXPLAIN - 2

 

 

출처 : Real MySQL 이성욱 지음