[Real MySQL, 6장 - 실행계획] type 컬럼 분석

1. 알고자 하는 것

쿼리의 실행계획에서 type 컬럼은 MySQL 서버가 테이블의 레코드를 어떤 방식을 사용해 읽어왔는지를 의미한다.

type 컬럼에 따라 쿼리의 성능과 튜닝 필요 여부를 판단할 수 있다.

• 실행계획의 type 컬럼 비교

 

 

2. 알게 된 것

type은 const > eq_ref > ref > ref_or_null > unique_subquery > index_subquery > index > ALL 순으로 성능이 빠르다.

각 실행계획의 성능 순서대로 알아보도록 한다.

 

• const
  • WHERE 절에 PK 또는 유니크 컬럼을 동등 조건(=)으로 사용할 때 (결과가 반드시 1건일 때)
  • 타 DBMS에서는 이를 유니크 인덱스 스캔(UNIQUE INDEX SCAN)이라고 한다.
  • type 중 속도가 가장 빠르다.
    • PK, 유니크 컬럼은 index가 기본으로 생성된다.
    • index를 통해 데이터를 빠르게 조회하면서 값이 유일함이 보장되므로 중복 데이터에 대한 추가적인 scan 비용이 없다.

CREATE TABLE const_table(
    id bigint primary key,
    col_1 varchar(10) not null,
    unique_col bigint unique not null
);

INSERT INTO const_table VALUE (1, 'test', 10);

EXPLAIN SELECT * FROM const_table WHERE id = 1; # PK 단건 조회
EXPLAIN SELECT * FROM const_table WHERE unique_col = 10; # 유니크 컬럼 단건 조회
EXPLAIN SELECT * FROM const_table WHERE id = 1 AND unique_col = 10; # PK, 유니크 컬럼 단건 조회

 

PK 단건 조회

유니크 컬럼 단건 조회

PK, 유니크 컬럼 단건 조회

 

• eq_ref
  • join에서 처음 읽은 테이블의 컬럼 값이 다음 테이블의 PK 또는 유니크 컬럼의 검색조건으로 사용될 때 (결과가 반드시 1건일 때)
  • 이 때, 유니크 컬럼은 NOT NULL이어야 한다.
  • 다중컬럼으로 구성된 PK 또는 유니크 컬럼이라면 구성된 모든 컬럼이 동등 조건에 포함되어야 한다.

CREATE TABLE eq_ref_table1(
    id bigint primary key,
    col_1 bigint not null,
    unique_col bigint unique not null
);

CREATE TABLE eq_ref_table2(
    id bigint primary key,
    col_2 varchar(10) not null,
    unique_col_2 bigint unique not null
);

CREATE TABLE eq_ref_table3(
    col_3 varchar(10) not null,
    unique_col_3 bigint not null,
    constraint table3_pk primary key(col_3, unique_col_3) # 복합 PK
);

INSERT INTO eq_ref_table1 VALUE (1, 'test', 10);
INSERT INTO eq_ref_table2 VALUE (10, 'test2', 10);

# 2번째 테이블의 검색 조건이 1번째 테이블의 col_1이며, PK를 기준으로 조회
EXPLAIN SELECT * FROM eq_ref_table1 JOIN eq_ref_table2 ON eq_ref_table1.col_1 = eq_ref_table2.id;

# 2번째 테이블의 검색 조건이 1번째 테이블의 col_1이며, 유니크 컬럼을 기준으로 조회
EXPLAIN SELECT * FROM eq_ref_table1 JOIN eq_ref_table2 ON eq_ref_table1.col_1 = eq_ref_table2.unique_col_2;

# 3번째 테이블의 PK에 해당하는 컬럼들이 모두 검색 조건에 포함
EXPLAIN SELECT * FROM eq_ref_table1 
  JOIN eq_ref_table3 
    ON eq_ref_table1.col_1 = eq_ref_table3.unique_col_3 
   AND eq_ref_table1.col_1 = eq_ref_table3.col_3;

PK 기준 단건 조회

유니크 컬럼 기준 단건 조회

복합 PK 컬럼 모두 포함 단건 조회

 

• ref
  • eq_ref과 달리 join 순서에 대한 관계가 없다.
  • PK, 유니크 제약조건도 없다.
  • 단건 조회 보장되지 않아도 된다.
  • 인덱스 종류와 관계없이 동등 조건(=)으로 검색할 때
  • const, eq_ref 보다 빠르지는 않지만 동등 조건으로 비교하므로 매우 빠른 레코드 조회 방법 중 하나
    • const, eq_ref와 달리 유니크 제약이 없으므로 단건이라는 보장이 없음
    • 이로 인해 추가적인 scan을 거치므로 const, eq_ref보다는 느림

CREATE TABLE ref_table1(
    id bigint primary key,
    col_1 varchar(10) not null
);

CREATE TABLE ref_table2(
    id bigint primary key,
    col_2 varchar(10) not null
);

# table1, table2 - 유니크하지 않은 일반 컬럼에 대한 인덱스 생성
CREATE INDEX ref_table1_idx ON ref_table1(col_1);
CREATE INDEX ref_table2_idx ON ref_table2(col_2);

# 단건이 아닌 다중 건 조회
INSERT INTO ref_table1 value (1, 'test1');
INSERT INTO ref_table1 value (2, 'test1');
INSERT INTO ref_table1 value (3, 'test1');
INSERT INTO ref_table2 value (1, 'test1');
INSERT INTO ref_table2 value (2, 'test1');
INSERT INTO ref_table2 value (3, 'test1');

EXPLAIN SELECT * FROM ref_table1 
  JOIN ref_table2 
    ON ref_table1.col_1 = ref_table2.col_2 # 유니크하지 않은 컬럼에 대한 동등 조건
WHERE col_1 = 'test1'; # 유니크하지 않은 컬럼에 대한 동등 조건

다중 건 조회 / Non-Unique 컬럼 동등 조건

 

 

• ref_or_null
  • ref에 NULL 비교가 추가된 형태

CREATE TABLE ref_table3(
    id bigint primary key,
    col_3 varchar(10) null # nullable
);

# 유니크하지 않은 컬럼 & nullable 컬럼에 대한 인덱스 생성
CREATE INDEX ref_table3_idx ON ref_table3(col_3);

EXPLAIN SELECT * FROM ref_table3 
WHERE col_3 = 'test1' OR col_3 IS NULL; # 유니크하지 않은 컬럼에 대한 동등 조건 & NULL 조건 추가

Non-Unique 컬럼 동등 조건 / IS NULL 조건

 

• unique_subquery
  • WHERE 절에 사용되는 IN (subquery) 쿼리에서 사용되는 방식
  • 서브쿼리에서 유니크 값을 반환할 때
  • MySQL 8.0 버전부터는 WHERE절에 사용된 IN(subquery) 세미조인 최적화를 위한 semijoin optimizer 옵션이 추가됐다.
    • 이로 인해 semijoin 최적화가 켜져있는 상태에서는 unique_subquery가 아닌 최적화된 ref / eq_ref 타입으로 나온다.

CREATE TABLE unique_subquery_table1(
    order_no bigint not null,
    table_no bigint not null,
    constraint table1_pk primary key(order_no, table_no) # 복합키
);

CREATE TABLE unique_subquery_table2(
    id bigint primary key,
    col bigint
);

# table2의 IN절 대상 column 인덱스 생성
CREATE INDEX t2_idx ON unique_subquery_table2(col);

# 세미조인 OFF
SET optimizer_switch ='semijoin=off';

EXPLAIN SELECT * FROM unique_subquery_table2 
WHERE col IN (
SELECT order_no FROM unique_subquery_table1 WHERE table_no = 1 # order_no + table_no 복합키이므로 중복되지 않는 order_no 반환
);

 

IN절 - 중복되지 않는 값 반환하는 서브쿼리

 

 

• index_subquery
  • unique_subquery의 경우 subquery가 중복된 값을 반환하지 않는다는 보장이 있었다.
  • index_subquery는 subquery가 중복된 값을 반환할 수 있지만, index를 이용해 중복된 값을 제거할 수 있을 때 사용된다.
  • unique_subquery / index_subquery 모두 매우 낮은 비용으로 중복을 제거하므로 큰 차이는 없다.

EXPLAIN SELECT * FROM unique_subquery_table2 
WHERE col IN (
# BETWEEN 조건으로 중복 여부 존재 / table_no는 인덱스에 포함되므로 인덱스로 중복 제거 가능
SELECT order_no FROM unique_subquery_table1 WHERE table_no BETWEEN 1 AND 3
);

IN절 - 중복될 수 있지만 index를 통해 중복 제거 가능한 서브쿼리

 

 

• range
  • 인덱스를 범위로 검색하는 인덱스 범위 스캔 방식이다.
  • 주로 <, >, IS NULL, BETWEEN, IN, LIKE와 같이 인덱스에 대해 범위 검색할 때 사용한다.
  • 해당 접근 방법 역시 어느정도의 성능은 보장된다.

CREATE TABLE range_table (
    id bigint primary key,
    col bigint not null
);

EXPLAIN SELECT * FROM range_table WHERE id BETWEEN 1 AND 30; # 인덱스 존재하는 id를 기준 범위검색

인덱스에 대해 범위 검색

 

• index
  • 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔 방식이다.
  • range 접근 방식처럼 인덱스에서 필요한 부분만 읽는 효율적인 방식은 아니다.
  • 테이블 풀 스캔과 달리 테이블보다 크기가 작은 인덱스를 풀 스캔하므로 index 방식으로 처리가 가능하다면 테이블 풀 스캔보다 우선적으로 사용된다.
  • 다음 조건에 해당하면 사용된다.
    • range/const/ref 등의 접근방식으로 인덱스 사용 불가 + 조회하는 컬럼이 모두 인덱스에 포함된 경우
    • range/const/ref 등의 접근방식으로 인덱스 사용 불가 + 인덱스를 통해 정렬/그룹핑이 가능한 경우

CREATE TABLE index_full_scan_table (
    id bigint primary key,
    col varchar(10),
    col2 varchar(10)
);

# col, col2 순서로 인덱스 생성
CREATE INDEX index_full_scan_idx_1 ON index_full_scan_table(col, col2);

# 선행 컬럼인 col이 조건에 없으므로 range/const/ref 접근방식 처리 불가능
# id, col, col2는 인덱스에 포함되므로, 인덱스만으로 쿼리 처리가 가능
EXPLAIN SELECT * FROM index_full_scan_table WHERE col2 = 'test';

인덱스로 쿼리 처리 가능

 

• ALL
  • 인덱스의 이점을 받지 못하는 테이블 풀 스캔 방식이다.
  • 테이블을 처음부터 끝까지 읽으면서 조건에 해당하지 않는 데이터를 제거하는 방식이다.
  • 가장 오래걸리는 방식으로, 빠른 응답이 필요한 조건에 대해서는 index 방식과 함께 피해야 하는 접근 방식이다.

CREATE TABLE all_table (
    id bigint primary key,
    col varchar(10)
);

# 인덱스가 존재하지 않는 col 컬럼이 조건에 포함됨
EXPLAIN SELECT * FROM all_table WHERE col = 'test';

인덱스 사용 불가능

 

 

 

3. 정리

• 쿼리의 실행계획에서 type 컬럼은 MySQL 서버가 테이블의 레코드를 어떤 방식을 사용해 읽어왔는지를 의미한다.
  • type 컬럼을 통해 실행된 쿼리의 성능을 측정할 수 있으며, 쿼리 튜닝 여부를 파악할 수 있다.
• type은 const > eq_ref > ref > ref_or_null > unique_subquery > index_subquery > index > ALL 순으로 성능이 빠르다.
• index, ALL 방식은 성능이 많이 느리므로, type에 index, ALL 방식으로 되어있다면 쿼리 튜닝을 고려해보도록 하자.

 

---

Reference

• Real MySQL