type 칼럼

• 쿼리의 실행 계획에서 type 이후의 칼럼은 MySQL 서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 나타낸다.
  • 인덱스를 사용해 레코드를 읽었는지
  • 풀 테이블 스캔으로 레코드를 읽었는지
• 일반적으로 쿼리를 튜닝할 때 인덱스를 효율적으로 사용하는지 확인하는 것이 중요하므로, type 칼럼은 반드시 체크해야 할 중요한 정보다.

type 칼럼의 해석

• MySQL 매뉴얼에서는 type 칼럼을 “조인 타입”으로 소개한다.
  • MySQL에서는 하나의 테이블로부터 레코드를 읽는 작업도 조인처럼 처리한다.
  • SELECT 쿼리의 테이블 개수에 관계없이 실행 계획의 type 칼럼을 “조인 타입”이라고 명시하고 있다.
• 하지만 type 칼럼의 값은 조인과 직접 연관 지어 생각하지 말고, 각 테이블의 접근 방법(Access type)으로 해석하면 된다.

type 칼럼의 값 (접근 방법)

• 실행 계획의 type 칼럼에 표시될 수 있는 값은 현재 많이 사용되는 대부분의 버전에서 거의 차이 없이 다음과 같이 표시된다.
  • system
  • const
  • eq_ref
  • ref
  • fulltext
  • ref_or_null
  • unique_subquery
  • index_subquery
  • range
  • index_merge
  • index
  • ALL
    • 위의 접근 방법들과 다르게 인덱스를 사용하지 않는다.
    • 풀 테이블 스캔 접근 방식
• 위에 나열된 순대로 성능이 빠르다.
• 하나의 단위 SELECT 쿼리는 위의 접근 방법 중에서 단 하나만 사용할 수 있다.
  • 실행 계획의 각 라인에 접근 방법이 2개 이상 표시되지 않는다.
• index_merge 를 제외한 나머지 접근 방법은 하나의 인덱스만 사용한다.
  • 실행 계획의 인덱스 항목에 단 하나의 인덱스 이름만 표시된다.

💡 동등 비교 연산자
동등 비교 연산자는 “=” 또는 “<=>”을 의미한다. “<=>” 연산자는 NULL에 대한 비교 방식만 조금 다를 뿐 “=”와 같은 연산자다.

system

• 레코드가 1건만 존재하는 테이블 또는 한 건도 존재하는 않는 테이블을 참조하는 형태의 접근 방식
• MyISAM이나 MEMORY 테이블에서만 사용되는 접근 방식이다.
  • InnoDB 스토리지 엔진을 사용하는 테이블에서는 나타나지 않는다.
  • InnoDB 스토리지 엔진을 사용하면 type 칼럼의 값이 ALL 또는 index로 표시될 가능성이 크다.
• 실제 애플리케이션에서 사용되는 쿼리에서는 거의 보이지 않는다.

const

• 테이블의 레코드 건수와 관계없이 다음 조건을 만족하는 쿼리의 처리 방식을 const 라고 한다.
  • 프라이머리 키나 유니크 키 칼럼을 이용하는 WHERE 조건절을 가지고 있다.
  • 반드시 1건을 반환한다.
• 다른 DBMS에서는 유니크 인덱스 스캔(UNIQUE INDEX SCAN)이라고도 표현한다.
• 다중 칼럼으로 구성된 프라이머리 키나 유니크 키 중에서 인덱스의 일부 칼럼만 조건으로 사용할 때는 const 타입의 접근 방법을 사용할 수 없다.
  • 프라이머리 키의 일부만 조건으로 사용할 때는 ref 로 표시된다.
• MySQL 옵티마이저가 쿼리를 최적화하는 단계에서 쿼리를 먼저 실행해서 통째로 상수화하기 때문에 “상수(const)” 표시된다.

eq_ref

• 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시되는 접근 방식이다.
• 조인에서 처음 읽은 테이블의 칼럼을, 그다음 읽어야 할 테이블의 프라이머리 키나 유니크 키 칼럼의 검색 조건에 사용할 때를 가르켜 eq_ref 라고 한다.
  • 두 번째 이후에 읽는 테이블의 type 칼럼에 eq_ref 가 표시된다.
• 조인에서 두번째 이후에 읽는 테이블에서 반드시 1건만 존재하는 보장이 있어야 사용할 수 있는 접근 방법이다.
  • 두 번째 이후에 읽히는 테이블을 유니크 키로 검색할 때 그 유니크 인덱스는 NOT NULL 이어야 한다.
  • 다중 칼럼으로 만들어진 프라이머리 키나 유니크 인덱스라면 인덱스의 모든 칼럼이 비교 조건에 사용되어야 한다.

ref

• 인덱스의 종류와 관계없이 동등(Equal) 조건으로 검색할 때 사용되는 접근 방법이다.
  • 조인의 순서와 관계없이 사용된다.
  • 프라이머리 키나 유니크 키 등의 제약 조건도 없다.
• 반환되는 레코드가 반드시 1건이라는 보장이 없다.
  • const 나 eq_ref 보다는 빠르지 않지만, 동등한 조건으로 비교되므로 매우 빠른 레코드 조회 방법의 하나.

fulltext

• MySQL 서버의 전문 검색(Full-text Search) 인덱스를 사용해 레코드를 읽는 접근 방법을 의미한다.
• MySQL 서버에서 전문 검색 조건은 우선순위가 상당히 높다.
  • 쿼리에서 전문 인덱스를 사용하는 조건과 일반 인덱스를 사용하는 조건을 함께 사용하면 일반 인덱스의 접근 방법이 const , eq_ref , ref 가 아니면 일반적으로 전문 인덱스를 사용하는 조건을 선택해서 처맇ㄴ다.
• 전문 검색은 MATCH (...) AGAINST (...) 구문을 사용해서 실행하는데, 이때 반드시 해당 테이블에 전문 검색용 인덱스가 준비돼 있어야만 한다.
  • 없다면 쿼리는 오류가 발생하고 중지될 것이다.
• 전문 검색 인덱스를 이용하는 접근 방법(fulltext)보다 일반 인덱스를 이용하는 접근 방법(range)이 더 빨리 처리되는 경우가 많았다.
  • 따라서 전문 검색 쿼리를 사용할 때는 조건별로 성능을 확인해 보는 편이 좋다.

ref_or_null

• ref 방식 또는 NULL 비교(IS NULL) 접근 방법을 의미한다.
  • ref 접근 방법과 같은데, NULL 비교가 추가된 형태
• 실제 업무에선 많이 활용되지 않지만, 만약 사용된다면 나쁘지 않은 접근 방식

unique_subquery

• WHERE 조건절에서 사용될 수 있는 IN(subquery) 형태의 쿼리를 위한 접근 방법이다.
• 서브쿼리에서 중복되지 않는 유니크한 값만 반환할 때 이 접근 방법을 사용한다.
• MySQL 8.0에서는 IN(subquery) 형태의 세미 조인을 최적화하기 위한 많은 기능이 도입되어서, 실제로는 더 최적화된 다른 실행 계획이 보일 것이다.

index_subquery

• IN(subquery) 형태의 조건에서 서브쿼리가 중복된 값을 반환할 수 있지만, 인덱스를 이용해서 제거할 수 있을 때 사용되는 접근 방법이다.
  • IN 연산자의 특성상 IN(subquery) 또는 IN(상수 나열) 형태의 조건은 괄호 안에 있는 값의 목록에서 중복된 값이 먼저 제거되어야 한다.

range

• 인덱스 레인지 스캔 형태의 접근 방법이다.
  • 주로 <, >, IS NULL, BETWEEN, IN, LIKE 등의 연산자를 이용해 인덱스를 검색할 때 사용된다.
  • 보통 인덱스 레인지 스캔이라고 하면 const, ref, range 라는 세 가지 접근 방법을 모두 묶어서 지칭한다.
• 일반적으로 애플리케이션의 쿼리가 가장 많이 사용하는 접근 방법이다.
  • 우선순위는 상당히 낮은 편이지만.
• 얼마나 많은 레코드를 필요로 하느냐에 따라 차이는 있겠지만 이 접근 방식도 상당히 빠르다.
  • 모든 쿼리가 이 접근 방법만 사용해도 최적의 성능이 보장된다고 볼 수 있다.

index_merge

• 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후, 그 결과를 병합해서 처리하는 방식이다.
• 다음과 같은 특징이 있다.
  • 여러 인덱스를 읽어야 하므로 일반적으로 range 접근 방법보다 효율성이 떨어진다.
  • 전문 검색 인덱스를 사용하는 쿼리에서는 index_merge 가 적용되지 않는다.
  • index_merge 접근 방법으로 처리된 결과는 항상 2개 이상의 집합이 되기 때문에, 그 두 집합의 교집합이나 합집합, 또는 중복 제거와 같은 부가적인 작업이 더 필요하다.
• index_merge 접근 방법이 사용될 때는 실행 계획에 조금 더 보완적인 내용이 표시된다.
  • 실행 계획의 Extra 부분의 설명 참고

index

• 인덱스 풀 스캔을 의미하는 접근 방법이다.
• 풀 테이블 스캔과 비교했을 때 비교하는 레코드 건수는 같지만 인덱스 풀 스캔이 더 빠르게 처리된다.
  • 일반적으로 인덱스는 데이터 파일 전체보다 크기가 작기 때문이다.
  • 인덱스 풀 스캔은 쿼리에 내용에 따라 정렬된 인덱스의 장점을 이용할 있다.
• 다음 조건 가운데 (첫 번째+두 번째) 조건을 충족하거나 (첫 번째+세 번째) 조건을 충족하는 쿼리에서 사용되는 읽기 방식이다.
  • range 나 const , ref 같은 접근 방법으로 인덱스를 사용하지 못하는 경우
  • 인덱스에 포함된 칼럼만으로 처리할 수 있는 쿼리인 경우
    • 데이터 파일을 읽지 않아도 된다.
  • 인덱스를 이용해 정렬이나 그루핑 작업이 가능한 경우
    • 별도의 정렬 작업을 피할 수 있다.
• LIMIT 조건이 없거나 가져와야 할 레코드 건수가 많아지면 상당히 느린 처리를 수행한다.

ALL

• 풀 테이블 스캔을 의미하는 접근 방법이다.
  • 처음부터 끝까지 전부 읽어서 불필요한 레코드를 제거(체크 조건이 존재할 때)하고 반환한다.
• 지금까지 설명한 접근 방법으로는 처리할 수 없을 때 가장 마지막에 선택하는 가장 비효율적인 방법이다.
• 데이터 웨어하우스(Data Warehous)나 배치 프로그램처럼 대용량의 레코드를 처리하는 쿼리에서는 잘못 튜닝된 쿼리보다 더 나은 접근 방법이기도 하다.
  • 여기서 말하는 잘못 튜닝된 쿼리는 억지로 인덱스를 사용하게 튜닝된 쿼리를 말한다.
• 일반적으로 index 와 ALL 접근 방법은 작업 범위를 제한하는 조건이 아니므로, 빠른 응답을 사용자에게 보내야 하는 웹 서비스 등과 같은 온라인 트랜잭션 처리 환경에는 적합하지 않다.

💡 리드 어헤드 (Read Ahead)

다른 DBMS와 같이 InnoDB도 풀 테이블 스캔이나 인덱스 풀 스캔과 같은 대량의 디스크 I/O를 유발하는 작업을 위해 다음과 같이 한꺼번에 많은 페이지를 읽어 들이는 기능을 제공한다.

인접한 페이지가 연속해서 몇 번 읽히면 백그라운드로 작동하는 읽기 스레드가 최대 64개의 페이지씩 한꺼번에 디스크를 읽어 들여서, 상당히 빠르게 레코드를 읽을 수 있다.

이러한 작동 방식을 “리드 어헤드”라고 하며, innodb_read_ahead_threshold 시스템 변수와 innodb_random_read_ahead 시스템 변수를 이용해 리드 어헤드를 언제 실행할지 제어할 수 있다.

Reference

참고 서적

📔 Real MySQL 8.0