[MySQL] 실행계획 (Execution Plan) - (2) type

이전 포스팅

 

https://hwannny.tistory.com/99

[MySQL] 실행계획 (Execution Plan) - (1) id, select_type, table

 

 

 

 

type

 

테이블의 접근 방식을 나타내는 컬럼이다.

 

• ALL
• const
• eq_ref
• ref
• fulltext
• ref_or_null
• unique query 
• index_query 
• range
• index_merge
• index

 

1) ALL

 

인덱스를 사용하지 않고 테이블을 처음 부터 끝까지 읽는 full table scan 방식을 사용한다.

 

 

 

 

2) const

 

테이블에 존재하는 row 수에 상관없이 primary key 또는 unique key로 설정된 컬럼을 동등 조건으로 조회시 사용되는 접근 방식이며

반드시 1건의 데이터만 조회 된다. 1건의 데이터만 조회 되어야 하기 때문에 NOT NULL 제약이 있어야 하며, 인덱스로 설정된 컬럼이 복수개의 컬럼으로 구성되어있을 경우 복수개의 컬럼 모두 동등 조건에 사용되야 한다.

 

NULL이 허용된다거나 조건으로 인덱스의 일부 컬럼만 사용한다면 1건의 데이터 조회를 보장하지 못하므로 const 접근방식을 사용할 수 없다.

 

※ 기본적으로 primary key 설정시에는 primary index (클러스터형), unique key 설정시에는 unique index (비클러스터형)가 생성된다.

 

 

 

 

3) eq_ref

 

const 접근 방식과 동일하며 차이점으로는 다른 테이블과 조인시 조인 조건이 const 제약조건이 조인 조건에 사용되는 접근 방식이다.

 

CREATE TABLE `table1` (
  `table1_id` int(11) NOT NULL
  PRIMARY KEY (`table1_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `table2` (
  `table2_id` int(11) NOT NULL
  PRIMARY KEY (`table2_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

EXPLAIN SELECT * FROM table1 c1 
INNER JOIN table2 c2 ON c1.table1_id = c2.table2_id

 

조인시 ON 절의 조건에 const 접근방식의 제약 조건을 만족했기 때문에 실행계획의 두 번째 행을 확인해보면 type 컬럼 값이 eq_ref임을 확인할 수 있다.

 

 

 

 

4) ref

 

const 접근 방식의 제약 조건을 지키지 않아도 단순히 동등조건으로 비교시 사용가능한 접근방식이며 아래와 같이 primary key로 table3_id, second_id 컬럼을 가질 때 

 

CREATE TABLE `table3` (
  `table3_id` int(11) NOT NULL,
  `second_id` int(11) NOT NULL,
  `some_column` varchar(45) NOT NULL,
  PRIMARY KEY (`table3_id`,`second_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

EXPLAIN SELECT * FROM table3 WHERE table3_id = 1;

 

Primary key의 일부 컬럼인 table3_id 에만 동등 조건을 줬을 경우 ref 접근 방식으로 쿼리가 실행된 것을 확인할 수 있다.

 

단, 인덱스에 설정된 첫번째 컬럼은 반드시 동등 조건에 포함되야 인덱스가 수행되기 때문에 아래와 같이 쿼리를 실행한다면 ALL (full scan) 접근 방식이 사용된다.

 

EXPLAIN SELECT * FROM table3 WHERE second_id = 1;

 

 

 

 

5) fulltext

 

fulltext 인덱스와 MATCH, AGAINST 문을 사용해 쿼리를 실행할 때 사용되는 접근방식이다.

 

CREATE TABLE `table3` (
  `table3_id` int(11) NOT NULL,
  `second_id` int(11) NOT NULL,
  `some_column` varchar(45) NOT NULL,
  PRIMARY KEY (`table3_id`),
  FULLTEXT KEY `idx_fulltext` (`some_column`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

EXPLAIN SELECT * FROM table3 WHERE MATCH(some_column) AGAINST('test');

 

 

 

 

6) ref_or_null

 

ref 접근 방식에서 NULL비교(IS NULL) 조건이 추가됐을 때 사용할 수 있는 접근 방식이다.

 

 

 

 

7) unique_query

 

IN(subquery) 형태에서 subquery가 중복 값이 없는 유니크한 값만 반환할 경우 사용되는 접근 방식이다.

 

 

 

 

8) index_query

 

IN(subquery) 형태에서 subquery가 중복 값을 반환하게될 경우 인덱스로 중복 값을 제거 할 수 있을 때 사용되는 접근 방식이다.

 

 

 

 

9) range

 

index range scan을 사용하는 접근 방식이다. 아래의 포스팅에서 index range scan 부분을 참고하자.

https://hwannny.tistory.com/101

[MySQL] 인덱스 스캔 종류

 

 

 

 

10) index_merge

 

2개 이상의 인덱스를 사용해 각각의 조회결과를 얻어낸 후 그 조회 결과들을 병합하는 처리를 수행하는 접근 방식이다.

다음과 같은 특징을 지닌다.

 

• 2개 이상의 인덱스를 사용하므로 range 접근 방식보다 성능이 떨어진다.
• AND, OR이 복잡하게 사용된 쿼리에서는 최적화가 올바르게 되지 않는다.
• full text 인덱스를 사용하는 쿼리에서는 index_merge 접근방식을 사용할 수 없다.

 

 

 

 

11) index

 

index full scan 방식을 사용하는 접근 방식이다. 아래의 포스팅에서 Index full scan 부분을 참고하자.

https://hwannny.tistory.com/101

[MySQL] 인덱스 스캔 종류

 

 

 

 

참고 서적

 

Real MySQL (이성욱)