MySQL 실행 계획 분석: Type 칼럼

Type 칼럼

실행 계획의 Type 칼럼은 테이블 레코드의 접근 방법을 기술하는 것이다. 

• 테이블 풀스캔인지, 인덱스를 사용했는지
• 프라이머리 키/유니크 키의 인덱스를 사용했는지, Fulltext 와 같은 인덱스를 사용해서 접근하는지
• 인덱스에서 동등 조건으로 검색하는지, range 로 검색하는지, 인덱스 풀스캔으로 검색하는지 등을 말한다.

 

Type 칼럼에 있는 유형은 다음과 같다. 이를 하나씩 분석해보자.:

• system
• const
• eq_ref
• ref
• fulltext
• ref_or_null
• unique_subquery
• index_subquery
• range
• index_range
• Index
• ALL

 

system

테이블에 있는 레코드가 한 건만 있는 데이터를 참조하거나 레코드가 없는 테이블을 참조할 때 사용되는 레코드 접근 유형이 ‘system’ 이다.

 

innoDB 스토로지 엔진에는 이게 없고, MyISAM 이나 메모리 엔진 중에 MEMTABLE 에만 system 유형의 접근 방법이 있다.

 

const

WHERE 조건절에서 프라이머리 키나 유니크 키의 인덱스를 사용해서 접근하고, 데이터를 1건만 가져올 때 사용되는 레코드 접근 유형이 ‘const’ 이다.

 

다른 DBMS 에서는 이를 유니크 인덱스 스캔(UNIQUE INDEX SCAN) 이라고도 한다.

 

실제 예시 쿼리와 실행 계획을 보면 다음과 같다:

 

const 타입에서 중요한 건 “1건만 반환할 수 있을 때” 사용되는 레코드 접근 타입이라는 것이다.

 

만약에 PK 가 복합 칼럼으로 구성되어 있다면 해당 키를 구성하는 모든 칼럼을 WHERE 절에 명시하지 않는다면 1건만 반환됨을 보장할 수 없어서 const 타입을 사용하지 못한다.

 

eq_ref

이 레코드 접근 유형은 여러 조건이 만족되어야 한다:

• 여러 테이블을 조인하는 쿼리에서 실행되는 유형이여야 함.
• 드라이빙 테이블에서 읽은 칼럼을 드리븐 테이블에서 바로 사용되며, 이때 드리븐 테이블을 읽는 키가 프라이머리 키나 유니크 키의 인덱스야만 함.
• const 와 마찬가지로 반환되는 레코드 건 수가 1건임을 보장할 수 있어야 한다. 즉 PK 가 복합 칼럼으로 구성되어 있다면 모든 칼럼을 조건절에 사용되어야 한다.
• eq_ref 에서 유니크 키로 읽을 때 유니크 키는 NOT NULL 이라는 제약 조건이 있어야한다.

 

실제 예시 쿼리와 실행 계획을 보면 다음과 같다:

 

 

ref

이 레코드 접근 유형은 조인의 순서와도 상관없고, 프라이머리 키나 유니크 키의 인덱스 여부와도 관련없이, 동등 조건 (=, >=, <=) 에만 사용된다면 실행되는 레코드 접근 유형이다.

 

즉 이 방식은 1건만 반환된다는 보장이 없다.

 

실제 예시 쿼리와 실행 계획을 보면 다음과 같다.

• dept_no=‘d005’ 인 레코드는 여러 건이 있을 수 있다.

 

지금까지 설명한 const, eq_ref, ref 는 동등 조건으로 검색하는 것이기 때문에 상당히 괜찮은 실행 계획들이다. 그래서 이것들은 별다른 튜닝을 하지 않아도 된다.

 

Fulltext

Fulltext 인덱스는 동등 조건으로 사용되는 레코드 접근 유형들 (= const, eq_ref, ref) 다음으로 MySQL 이 우선적으로 사용하려고 하는 실행 계획이다.

 

그러나 Fulltext 인덱스는 테이블의 레코드 분포도에 따라서 성능이 잘 안나올 수 도 있는 인덱스이기도 하다.

 

그리고 Fulltext 인덱스는 통계 정보가 관리되지 않아서 MySQL 의 실행 계획은 이런 통계 자료를 활용할 수도 없다. 그래서 이러한 레코드 접근 유형을 이용하고 있다면 실제로 성능이 잘 나오는지 확인을 해봐야한다.

 

ref_or_null

ref 유형에다가 NULL 에 대한 검색이 추가될 때 나오는 레코드 접근 유형이다.

 

실제 예시 쿼리와 실행 게획을 보면 다음과 같다.

 

 

unique_subquery

WHERE 조건절에 IN(subquery) 를 사용하는 형태이고, subquery 가 중복이 없는 레코드일 때 사용되는 레코드 접근 유형이다.

 

실제 예시 쿼리와 실행 게획을 보면 다음과 같다.

 

 

index_subquery

unique_subquery 처럼 WHERE 조건절에 IN(subquery) 를 사용하는 형태이나, subquery 가 중복된 레코드를 반환할 수 있을 때, 그러나 이 중복을 인덱스를 이용해서 제거할 수 있을 때 사용되는 레코드 접근 유형이다.

 

원래 IN() 절을 사용할 때 중복된 레코드를 제거하는 절차를 가진다.

 

range

인덱스에서 범위를 이용해서 검색할 때 (<, >, IS NULL, BETWEEN, IN, LIKE) 사용되는 레코드 접근 유형이다.

 

우리가 일상에서 말하는 인덱스 레인지 스캔은 const, ref, range 를 포함해서 두루 뭉술하게 이야기 한다. 이것도 성능이 상당히 빠른 쿼리이기도 하다.

 

실제 예시 쿼리와 실행 게획을 보면 다음과 같다.

 

 

Index_merge

MySQL 에서 하나의 SELECT 문을 처리하기 위해서 일반적으로 인덱스를 하나만 이용하는데, 너무 탐색하는 레코드가 많고 WHERE 절에서 인덱스를 여러개 이용할 수 있다면 이들을 이용하고 병합하는 전략을 취하기도 한다. 이를 index_merge 라고 함.

 

사용할 수 있는 조건은 다음과 같다:

• WHERE 조건절이 복잡하고 여러 인덱스를 이용할 수 있어야 함.

 

실제 예시 쿼리와 실행 게획을 보면 다음과 같다.

 

Index

이 레코드 접근 유형은 인덱스 풀스캔을 말한다.

 

Index 풀스캔이 Table 풀스캔보다 효율적인 경우는 다음과 같다:

• 인덱스만 읽어서 처리하면 되는 경우 (즉 테이블 레코드에 엑세스하지 않아도 되는 경우를 말한다)
• 인덱스를 이용해서 정렬이나 group by 기능을 이용하고 싶은 경우

 

실제 예시 쿼리와 실행 게획을 보면 다음과 같다.

• 이 쿼리는 그래도 LIMIT 가 있어서 상당히 빠른 편에 해당된다.
  

  

 

ALL

테이블 풀스캔을 말한다.

 

인덱스 풀스캔과 테이블 풀스캔은 OLTP 프로그램에서 적합하지는 않다.

 

MySQL 에서는 Index 나 Table 풀스캔을 할 때 미리 읽어야하는 데이터 블록을 가져오는 Read Ahead 전략을 통해서 나름의 최적화를 한다.

• 관련 시스템 변수는 다음과 같다:
  • innodb_read_ahead_threshold: 
    • Read Ahead 를 트리거하기 위한 값이다. 기본 값은 56인데 56 개의 순차적인 페이지를 읽는다면 Read Ahead 가 트리거되서 비동기로 연속적인 페이지들을 읽어온다.
    • 최대값은 64이다.
    • 이 값이 0이면 innodb_read_ahead_threshold 를 비활성화한다.
    • 즉 이 값을 낮추면 Read Ahead 를 더 빠른 시점에 시작할 수 있다.
  • innodb_random_read_ahead: 
    • InnoDB Buffer Pool 에 있는 연속된 페이지가 여러개 있다면 (= 13개) 앞으로 이런 페이지들이 더 필요할 수 있다고 판단해서 데이터 블록을 디스크에서 가지고 오는 기능이다. 
    • 이를 활성화 하려면 ON 으로 변경하면 된다. 기본 값은 OFF 이다.

 

 

References: 

- https://www.yes24.com/Product/Goods/103415627

Real MySQL 8.0 (1권) - 예스24

- https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_read_ahead_threshold

MySQL :: MySQL 8.0 Reference Manual :: 15.14 InnoDB Startup Options and System Variables

- https://dev.mysql.com/doc/refman/8.0/en/innodb-performance-read_ahead.html

MySQL :: MySQL 8.0 Reference Manual :: 15.8.3.4 Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)