MySQL 실행계획 분석 (2) - type

 

type : 테이블에 접근하는 방법 

 

type 컬럼은 각 테이블에 접근하는 방법을 나타냅니다. 

예를들면 , 인덱스를 사용해 레코드를 읽었는지 , 풀 테이블 스캔으로 레코드를 읽었는지 와 같은 정보를 줍니다. 

일반적으로 인덱스를 잘 타는지 체크하는 건 매우 중요하기에 , type 컬럼은 반드시 체크해야 합니다. 

 

본 글에서는 type 컬럼을 크게 두 가지 그룹으로 나누어서 정리해 보겠습니다. 

 

그룹 1 : const , eq_ref , ref - 주로 유니크한 키(PK , Unique Key) 나 고정된 상수를 활용 할 때 체크 합니다. 

 

const : 인덱스를 타고 내려왔을 때 , 1건 보장 

eq_ref : 드리븐 테이블에서 인덱스를 타고 내려왔을 때 , 1건 보장

ref : 인덱스 타고 내려왔을 때 1건 보장 X 

 

그룹 2 : range , index , ALL - 인덱스가 의도한 대로 동작하는지 체크합니다. 

 

range : 인덱스 레인지 스캔 

index : 인덱스 풀 스캔 

ALL : 풀 테이블 스캔 

---

예시 테이블 

---

const , eq_ref , ref 

 

1. const : 인덱스 타고 내려왔을 때 , 1건 보장

 

const 가 되기 위해 WHERE 절의 필수 조건은 다음 3가지 입니다. 

• 반드시 primary key 혹은 unique index 의 key 를 사용해야 함 
• 복합 primary key , 복합 unique index 의 경우 , 모든 컬럼을 조건문에 넣어야 함 
• 동등 비교 연산자 (=) 여야 함 

즉 , 인덱스를 타고 내려왔을 때 1건이 보장되어야 합니다. 

 

 

데이터를 한 번 읽은 후 , 실행 계획의 나머지 부분에서는 해당 컬럼 값을 실제 테이블에서 다시 읽지 않고 상수로 치환해 버립니다. 

즉 , 옵티마이저에 의해 상수화된 다음 쿼리 실행기로 전달되기 때문에 접근 방법이 const 인 것 입니다. 

 

2. eq_ref :  드리븐 테이블에서 인덱스 타고 내려왔을 때 , 1건 보장  

 

 

JOIN 은 보통 두가지 테이블로 나뉩니다.

• 드라이빙 테이블 (먼저 읽는 테이블)
• 드리븐 테이블 (나중에 읽는 테이블) 

eq_ref 가 되기 위해 WHERE 절의 필수 조건은 다음 4가지 입니다. 

• 조인 연산 
• 드리븐 테이블의 조인 컬럼이 primary key 혹은 unique index 여야 함 
• unique index 라면 모든 컬럼이 NOT NULL 이어야 함 
• 동등 비교 연산자 (=) 여야함 

즉 , 드리븐 테이블에서 1건임이 보장될 때 , type 으로 eq_ref 이 나타나게 됩니다. 

드라이빙 테이블 , 드리븐 테이블을 강제하기 위해 STRAIGHT_JOIN 사용

 

드라이빙 테이블을 읽고 , 조인 키를 추출 한 후 

드리븐 테이블의 프라이머리 키(클러스터링 인덱스)나 유니크 인덱스로 가서 , 수직 탐색 합니다. 

유니크 하기 때문에 타고 내려간 순간 , 데이터를 추출하고 다음 드라이빙 행으로 넘어 갑니다. 

레코드를 찾아 내는 가정 효율적인 조인 방식 입니다. 

 

3. ref :  인덱스 타고 내려왔을 때 1건 보장 X 

 

인덱스 타고 내려왔을 때 1건이 보장 되지 않으면 ref 입니다. 

아래와 같은 상황에서 실행계획에 보이게 됩니다. 

• 유니크 하지 않은 일반 인덱스를 사용 
• 복합 유니크 인덱스의 일부 컬럼만 사용
• 조인에서 드리븐 테이블이 일반 인덱스를 참조

 

const , eq_ref , ref 모두 인덱스 컬럼에 대해 동등 연산할 때 , 볼 수 있는 type 입니다. 

3가지 모두 좋은 접근 방법으로 , 쿼리를 튜닝할 때 이 3가지 type 에 대해서는 크게 신경 쓰지 않고 넘어갈 수 있습니다. 

---

range , index , ALL

 

1. range :  인덱스 레인지 스캔 

 

인덱스 레인지 스캔 (Index Range Scan) 을 의미 합니다. 

인덱스에서 , 특정 범위 안에 있는 레코드들만 차례대로 읽는 방식 입니다. 

 

인덱스 컬럼에 대해 범위 비교 연산자를 사용할 때 , 발생 합니다. 

• 범위 연산 : < , > , <= , >= , BETWEEN 
• 다중 값 비교 : IN 
• 패턴 매칭 : LIKE 'abc%' 
• NULL 비교 : IS NULL 

 

 

 

인덱스 레인지 스캔의 동작 방식은 다음과 같습니다. 

1. 인덱스 탐색(Index Seek) : 빨간 화살표에 해당하는 과정 입니다. 리프 노드에서의 시작점을 찾습니다. 
2. 인덱스 스캔(Index Scan) : 파란색 화살표에 해당하는 과정 입니다.리프 노드에서 해당하는 범위를 내려 갑니다. 
3. 스캔 종료(Scan Stop) : 인덱스의 리프노드들은 정렬되어 있기에 범위를 넘어서면 , 뒤에 데이터를 보지 않고 종료 합니다. 

 

2. index : 인덱스 풀 스캔 

 

데이터 파일 대신 인덱스 파일을 처음부터 끝까지 다 읽는 방식 입니다. 

전체 레코드 건수는 데이터 파일과 같지만 , 인덱스는 보통 데이터 파일보다 용량이 훨씬 작기 때문에 전체 테이블 스캔보다는 빠르게 동작합니다. 

 

<예시 1> covering Index 를 사용하는 경우 

 

• 조건 1 : range 등 특정 범위를 좁히는 인덱스 활용 불가 
• 조건 2 : 인덱스에 포함된 컬럼만으로 처리 가능 

 

WHERE 절이 없으므로 특정 범위를 타격하는 range 스캔은 불가능 합니다. 

하지만 , 사용자가 요청한 컬럼이 email 뿐이고 , 이 컬럼 정보는 인덱스 파일에 모두 들어가 있습니다. 

 

 

MySQL 은 무거운 데이터 파일 대신 , 가벼운 email 인덱스 파일만 통째로 읽어서 결과를 반환합니다. 

이것을 covering Index 라고 부릅니다.

 

<예시 2> 정렬/그루핑을 위해 인덱스를 읽는 경우

 

• 조건 1 : range 등 특정 범위를 좁히는 인덱스 활용 불가 
• 조건 2 : 인덱스에 포함된 컬럼만으로 처리 가능 

 

amount 인덱스는 이미 내부적으로 정렬되어 있습니다. 

옵티마이저는 데이터를 하나하나 찾아서 정렬하는 방식 대신 , 이미 예쁘게 정리된 amount 인덱스를 훑으며 필요한 갯수만큼 가져오는 방식을 선택합니다. 

 

 

3. ALL : 풀 테이블 스캔 

 

ALL 은 이름 그대로 인덱스의 도움 없이 테이블의 처음부터 끝까지 모든 레코드를 읽는 방식 입니다. 

지금까지 설명한 접근 방법으로는 처리할 수 없을 때 가장 마지막에 선택하는 가장 비효율적인 방법 입니다. 

 

인덱스는 데이터 일부만 담고 있어 가볍지만 , 데이터 파일 전체는 모든 컬럼 정보를 포함하고 있어 무겁습니다. 

이를 통째로 메모리에 올리고 읽어내는 과정은 디스크 I/O 에 부하를 줍니다. 

 

인덱스가 있지만 , 읽어야 할 데이터가 전체의 20~25% 를 넘어가면 , MySQL 은 인덱스를 읽고 랜덤 I/O 로 접근하는 것 보다 테이블에 바로 접근하는게 빠르다고 판단해 , 풀 테이블 스캔을 할 수도 있습니다.