MySQL 실행 계획 분석: Extra 칼럼

Extra 칼럼이란?

Extra 칼럼에 가지고 오는 정보는 MySQL 에서 내부적으로 제공한 최적화나 처리 알고리즘이다.

 

여기서는 현재까지 나와있는 Extra 칼럼에서 나올 수 있는 메시지에 대해서 정리한 글이다.

 

const row not found

실행 계획에서 레코드를 읽을 때 const 방식으로 읽었지만 실제로 레코드가 없는 경우에 나오는 메시지이다.

 

Deleting all rows

MyISAM 스토로지 엔진에서 한번의 스토로지 핸들러 API 로 테이블의 레코드를 모두 지울 때 나오는 메시지이다.

• 즉 WHERE 절이 없는 DELETE 에서 한번의 레코드를 삭제할 때 발생하는 메시지이다.
• 테이블의 레코드를 모두 지울 때는 DELETE 보다 TRUNCATE 가 낫다.

 

MySQL 8.0 버전에서는 없는 메시지이다.

 

DISTINCT

SELECT 절로 가져오는 DISTINCT 칼럼과 JOIN 처리를 하는 칼럼이 같을 때, 조인을 한 번만 해도 되는 경우에 불필요한 조인을 줄이는 최적화이다.

 

예제 쿼리는 다음과 같다.

FirstMatch

세미 조인의 최적화 중에서 FirstMatch 최적화가 적용될 경우에 발생하는 메시지이다.

 

간략하게 FirstMatch 최적화에 대해서 소개하자면 세미 조인이 있는 즉 IN(subQuery) 쿼리에서 조인으로 풀 때 한 건의 레코드만 조인하고 이후는 하지 않아도 되서 스킵하는 방식의 최적화이다.

 

예제 쿼리는 다음과 같다.

• titles 테이블이 드리븐 테이블로서 조인될 때 titles 테이블의 레코드는 한 건만 조인하고 나머지 레코드의 조인은 스킵된다.
  

  

 

Full scan on NULL Key

col1 IN (SELECT col2 FROM ...) 와 같은 subQuery 와 col1 을 비교하는 쿼리에서 col1 이 NULL 이 된다면 subQuery 는 풀테이블 스캔을 하게 된다.

• NULL 은 알 수 없는 값이라서 서브쿼리는 풀테이블 스캔이 발생하게 된다고 함.

 

Extra 칼럼의 Full scan on NULL Key 메시지는 이렇게 서브쿼리가 풀테이블 스캔이 발생하는 경우에 나오는 경고 메시지 같은 것이다.

 

성능적으로 상당히 안좋기 때문에 쿼리 튜닝을 해야한다. 튜닝은 간단한데 col1 을 WHERE 절에서 IS NOT NULL 을 명시하면 된다.

• 애초에 NULL 이 될 수 없는 칼럼은 이 메시지가 발생하지 않는다.

 

다음은 Full Scan on NULL Key 가 발생하는 쿼리의 예시와 이를 IS NOT NULL 조건을 추가해서 발생하지 못하게 하는 예시이다.

 

 

Impossible HAVING

HAVING 절을 통해서 필터링을 하고난 이후 레코드가 아예 없는 경우에 발생하는 메시지이다.

 

이 메시지가 발생한다면 쿼리를 제대로 설계하지 못했는지 점검해봐야한다.

 

Impossible WHERE

WHERE 절을 통해서 필터링을 하고난 이후 레코드가 아예 없는 경우에 발생하는 메시지이다.

 

LooseScan

세미 조인 최적화 중에서 LooseScan 을 적용한 경우에 발생하는 메시지이다.

 

간단하게 이 최적화를 소개하자면 세미 조인의 IN(subQuery) 쿼리를 조인으로 풀어서 실행될 때,  서브쿼리를 드라이빙 테이블로 읽을 때 LooseScan 을 적용하는 최적화이다.

• 이 최적화가 없다면 Outer 테이블을 드라이빙 테이블로 풀스캔 하면서 읽을 것이다. 
• 드리븐 테이블을 루스 스캔하는 건 딱히 의미 없음. 드라이빙 테이블을 루스 스캔해서 조인 횟수를 줄이는 처리이다.
• LooseScan 은 인덱스 스캔 방식 중 하나고 인덱스 Range Scan 과 달리 수평적인 스캔이 없고 수직적인 스캔만 있는 형태다.

 

예제 쿼리는 다음과 같다.

• departments 테이블에서는 dept_no 가 PK 이고, dept_emp 테이블에서는 (dept_no, emp_no) 가 PK 이다.
• dept_emp 테이블을 드라이빙 테이블로써 읽을 건데 풀스캔으로 데이터를 모두 읽는 것이 아니라 departments 테이블의 dept_no 의 유니크한 값만을 가지고 dept_emp 테이블에서 인덱스 LooseScan 방식으로 읽을 것이다. 그래서 드라이빙 테이블에서 읽어야 하는 레코드 수가 줄어든다.
  

  

 

No matching min/max row

MIN() 이나 MAX() 의 집합 함수로 데이터를 조회할 때 해당 레코드가 아예 없다면 발생하는 메시지이다.

 

no matching row in const table

const 방식으로 접근할 때 레코드가 없는 경우에 발생하는 메시지이다.

 

const 방식은 WHERE 절에서 상수로 비교한다는 뜻인데, 예시로 보면 간단하다.

• de.dept_no='d005' 가 const 방식을 말한다.
  

  

 

No matching rows after partition pruning

파티셔닝된 테이블이 있을 때, 해당 쿼리를 실행할 수 있는 적절한 파티션을 찾을 수 없을 때 발생하는 메시지이다.

 

No tables used

FROM 절이 없는 문장이나 FROM DUAL 과 같이 테이블을 참고하지 않는 SQL 문은 이와 같은 메시지가 발생한다.

 

Not exists

A 테이블에는 데이터가 있고, B 테이블에는 데이터가 없는 유형의 쿼리는 NOT IN(subQuery) 형식으로 이뤄진다.

• 이런 형태의 쿼리를 안티 조인(ANTI JOIN) 이라고도 한다.

 

이 안티 조인 쿼리를 OUTER JOIN 형식으로 풀어질 때 최적화를 수행할 수 있다.

 

아우터 조인은 안티 조인과 같은 결과 집합을 만들 수 있다.

• 아우터 조인은 조인 결과가 없으면 드라이빙 테이블의 결과만 나가니까 이를 이용하는 것이다.

아우터 조인 방식으로 안티 조인을 해결하는 최적화는 JOIN 을 했을 떄 결과 집합이 있다면 JOIN 을 멈추는 식으로 동작한다.

물론 JOIN 결과는 추가되지 않는다.

 

즉 JOIN 을 여러번 하고 필터링 하는게 아니라 한번만 수행하면 되는 최적화를 진행한다.

• 이렇게 조인을 멈출 수 있다면 멈추는 식의 최적화가 꽤 있는 것 같다.

 

예제 쿼리를 보면 다음과 같다.

• dept_emp 테이블에는 있지만, department 테이블에는 없는 데이터를 찾는 쿼리다.
• dept_emp 테이블에서 department 테이블로 조인을 한다. 조인을 할 때 department 테이블에도 데이터가 한 건이라도 있다면 남은 조인을 이어서 하지 않는다.
• 여기서 아우터 테이블은 department 이다.
  

  

Plan isn't ready yet

실행중인 쿼리가 아직 실행 계획을 수립하고 있지 못하고 있는 상태를 말한다.

 

현재 실행중인 쿼리들을 확인하려면 어떻게 해야할까?

• SHOW PROCESSLIST 명령을 통해서 현재 실행중인 쿼리들을 확인할 수 있다.

 

특정 커넥션에서 실행중인 쿼리의 실행 계획을 보려면 어떻게 해야할까?

• SHOW PROCESSLIST 명령을 통해 실행중인 쿼리에서 id 값을 가지고 EXPLAIN FOR CONNECTION {id} 명령을 내리면 된다.

 

Range checked for each record(index map: N)

다음과 같이 조인을 할 때 조인 조건으로 두 개의 변수가 있으면 실행 계획은 각 변수의 값마다 (= 레코드마다) 달라지게 된다.

• 아래의 예시에서 e1.emp_no 값이 크다면 실행 계획은 인덱스 레인지 스캔을 적용하는게 낫겠지만, e1.emp_no 값이 작다면 풀테이블 스캔이 나을 수 있다.
  

  

  
  

  IMG_0082.jpg

  97.7 kB

이렇게 레코드마다 실행 계획이 달라져야 하는 경우에 Range checked for each record(index map: N) 메시지가 나온다.

 

이 메시지에서 나오는 index map 의 N 값은 해당 테이블에 있는 모든 인덱스를 비트맵 형식으로 표현했을 때, 사용할 수 있는 후보군의 인덱스 값을 말한다.

• 예시로 알면 쉽다. 해당 테이블의 인덱스가 5개가 있다면 5개의 비트가 사용된다. 이 중에서 모든 인덱스가 이 실행 계획에 사용될 수 있다면 11111 (N 값은 15) 이렇게 될 것이고, 두 번째 인덱스만 사용될 수 있다면 00010 (N 값은 2) 이렇게 표시될 것이다.

 

Recursive

다음과 같이 WITH 절과 cte 그리고 recursive 와 함께 SELECT 명령을 내린다면 실행 계획에 Recursive 라고 적힌다.

 

내부적으로는 임시 테이블을 만들고 여기에서 SELECT 를 해서 결과를 반환한다.

 

Rematerialize

외부 테이블과 서브쿼리가 레터럴 조인 (Lateral Join) 되서 처리될 때 실행 계획에 Rematerialize 메시지가 발생한다.

 

레터럴 조인 (Lateral Join) 이란?

• 서브쿼리에서 외부 테이블의 칼럼을 참조해서 결과를 반환할 수 있다.
• 반환된 결과는 내부 임시 테이블에 저장됨.

 

예시 쿼리와 동작 과정을 보자.

• employees 테이블에서 first_name='Matt'인 행의 emp_no 들이 subQuery 에서 사용된다.
• subQuery 에서는 salaries 테이블에서 실행되고 외부 테이블의 조건을 활용해 결과 집합을 2건 가지고 온다. 그리고 이를 내부 임시 테이블 (= derived2) 에 저장된다.
• 그리고 임시 테이블에 저장된 행과 외부 테이블이 조인되서 결과 집합이 나간다.
• employees 테이블의 레코드마다 subQuery 가 실행되며 새로운 내부 임시 테이블이 계속 생성되는 구조다.
  

  

 

Select tables optimized away

MIN() 과 MAX() 쿼리 또는 GROUP BY 와 MIN(), MAX() 쿼리를 처리할 떄 인덱스를 사용해 해당 조건에 맞는 인덱스의 첫 값과 끝 값인 한 건만 가져오면 되는 경우에 이 메시지가 출력된다.

 

예시 쿼리와 실행되는 그림은 다음과 같다.

 

 

Start temporary, End temporary

세미 조인 최적화 중 Duplicate weed out 가 실행될 때 출력되는 메시지다.

 

간단하게 Duplicate weed out 의 최적화를 소개하자면 세미 조인을 Inner Join 형태로 풀어서 내부 임시 테이블을 생성한 후 중북 레코드를 제거하는 기법이다.

 

출력되는 메시지는 Start temporary 와 End temporary 이렇게 두 가지인데, Start temporary 는 조인을 시작하는 첫 번째 테이블에 출력되고, End temporary 는 조인을 당하는 마지막 테이블에 출력된다.

 

Unique row not found

조인되는 칼럼이 유니크 키/프라이머리 키 인덱스이고 LEFT OUTER JOIN 을 했을 때 조인의 결과 집합이 없는 경우 출력되는 메시지다.

 

예시 쿼리는 다음과 같다.

 

Using filesort

ORDER BY 와 같은 정렬 작업을 요구할 때 인덱스를 이용할 수 없다면 출력되는 메시지이다.

 

이 경우에 정렬 작업의 처리는 다음과 같다:

• 메모리에서 처리할 수 있는 경우에는 Sort Buffer 를 이용해서 정렬 작업을 수행한다.
• 메모리에서 모두 처리할 수 없는 경우 즉 레코드의 개수가 Sort Buffer 의 메모리 공간보다 크다면 레코드들을 내부 디스크 임시 테이블에 저장하고 데이터들을 Merge Sort 방식으로 정렬 작업을 수행한다.

 

Using index (커버링 인덱스)

테이블 레코드에 엑세스 하지 않고 인덱스만 읽어서 결과 집합을 반환할 수 있는 경우에 이 메시지가 출력된다.

 

커버링 인덱스로 결과가 나가는 경우에 응답 속도는 수십배 정도 차이가 날 수 있다.

 

그렇다고 모든 인덱스 자체를 커버링 인덱스로 설계할 경우 발생하는 문제는 다음과 같다:

• 불필요한 칼럼까지 인덱스에 들어가서 한 페이지에 들어갈 수 있는 인덱스 데이터 개수가 줄어들어서 인덱스 스캔의 성능이 안나옴.
• 데이터를 변경/삭제/삽입의 작업이 느려질 수 있다.

 

Using index condition

인덱스 컨디션 푸쉬다운 (index condition pushdown) 최적화를 적용할 수 있을 때 이 메시지가 출력된다.

 

ICP (Index Condition Pushdown) 최적화는 스토로지 레벨에서 인덱스의 리프 노드에 있는 데이터를 가지고 조건에 맞게 필터링한 후 MySQL 엔진으로 결과를 가지고 오는 것이다.

 

ICP 를 비활성화 한다면 인덱스 레벨에서 필터링 하는 것이 아니라 테이블 레코드를 MySQL 엔진으로 가지고와서 WHERE 절에 사용된 index 칼럼을 가지고 필터링을 할 것이다.

• 쿼리의 처리 과정을 간락하게 설명하자면 MySQL 엔진은 스토로지 엔진에게 레코드를 가지고 오라고 명령을 내린다. 스토로지 엔진이 레코드들을 가지고 오면 MySQL 엔진은 필터링이나 가공과 같은 작업을 하는 것이다.
• 즉 이 최적화를 한다면 스토로지 엔진은 테이블 레코드에 엑세스 하지 않고 인덱스 수준에서 필터링을 할 수 있다.

 

예시 쿼리는 다음과 같다.

Using index for group-by

GROUP BY 처리를 할 때 루스 인덱스 스캔 (Index Loose Scan) 을 이용하는 경우에 출력되는 메시지이다.

• GROUP BY 처리는 인덱스 레인지 스캔 (Index Range Scan) 을 이용할 수도 있고, 인덱스를 이용하지 않고 내부 임시 테이블을 이용해서 처리할 수도 있다. 이 경우에는 Using index for group-by 메시지가 출력되지 않는다.

GROUP BY 처리에 인덱스 레인지 스캔 (Index Range Scan) 이 이용되는 경우:

• AVG(), SUM(), COUNT() 처럼 조회하려는 값이 인덱스를 이용하더라도 인덱스 데이터를 한 건만 아니라 범위로 여러 건 읽어야 하는 경우라면 인덱스 레인지 스캔을 이용한다.
• Extra 칼럼에 Using index 가 표시된다.

GROUP BY 처리에 인덱스 루스 스캔 (Index Loose Scan) 이 이용되는 경우:

• MIN() 이나 MAX() 처럼 인덱스를 이용해서 한 건만 읽으면 되는 경우에 루스 스캔이 이용된다.

GROUP BY 와 WHERE 절의 유무에 따라서 처리되는 방식이 달라진다.

• WHERE 절이 없는 경우:
  • 위의 이론을 적용하면 된다.
• WHERE 절이 있고, GROUP BY 에서만 인덱스를 이용할 수 있는 경우:
  • 실제 테이블 레코드에 접근해야하니까 루스 인덱스 스캔으로 처리할 수 없다. 그래서 실행 계획에 Using index for group-by 가 출력되지 않음.
• WHERE 절이 있고 GROUP BY 에서 같은 인덱스가 사용되는 경우:
  • 인덱스 루스 스캔이 가능하다. 그러나 데이터 분포도에 따라서 사용이 안될 수도 있다. 인덱스로 사용되는 값에 유니크한 값들이 적다면 루스 스캔이 효율적이나, 유니크한 값들이 많다면 루스 스캔보다는 레인지 스캔으로도 처리될 수 있다.
• WHERE 절이 있고 GROUP BY 처리에서 서로 다른 인덱스가 사용될 수 있는 경우:
  • 일반적으로 WHERE 절 에서 사용되는 인덱스가 우선적으로 고려된다.
  • index merge 최적화가 아니라면 인덱스는 한 개만 사용이 될 것이다. 그래서 루스 스캔이 적용되지 않을 것.

 

Using index for skip scan

인덱스 스킵 스캔 (Index Skip Scan) 최적화를 사용할 수 있는 경우에 이 메시지가 출력된다.

 

인덱스 스킵 스캔 (Index Skip Scan) VS 인덱스 루스 스캔 (Index Loose Scan) 의 차이:

• 인덱스 루스 스캔은 GROUP BY 절의 처리를 위해서만 사용되었다. 이를 일반적인 WHERE 절에서도 사용하기 위해서 MySQL 8.0 에서 도입된 게 인덱스 스킵 스캔이다.

 

인덱스 스킵 스캔의 예시는 다음과 같다:

• 복합 칼럼으로 구성된 인덱스를 사용할 때 선행 조건이 WHERE 절에 포함되어 있지 않더라도 선행 조건의 카디널리티가 낮다면 (= 칼럼의 유니크한 값이 적다면) 인덱스 스킵 스캔이 이용될 수 있다.
  

  

 

Using join buffer (Block Nested Loop, Batched Key Access, Hash Join)

조인 버퍼 (Join Buffer) 를 이용해서 조인 처리를 수행하는 경우에 이 메시지가 출력된다.

 

조인 버퍼 (Join Buffer) 가 이용되는 경우:

• 조인을 하는 두 테이블에 인덱스가 없는 경우:
  • 드라이빙/드리븐 테이블 중 레코드 수가 적은 것을 조인 버퍼에 올리고 다른 쪽의 테이블을 풀스캔 하면서 조인을 수행한다. (= Hash Join 방식)
• 조인 조건이 없는 카테시안 조인 (Cross Join) 을 이용하는 경우에 사용될 수 있다.

 

카테시안 조인을 통해서 조인 버퍼를 사용하는 에시 쿼리:

 

OLTP 서비스에서 조인 버퍼는 1MB 정도로도 충분하다. 배치 서비스에서는 좀 더 커도 괜찮다고 함.

 

Using MRR

쿼리의 처리 과정을 보면 MySQL 엔진이 실행 계획을 수립한 이후에 스토로지 엔진의 API 를 호출해서 레코드를 가지고 오도록 시킨다.

 

레코드를 건건히 가지고 온다면 스토로지 엔진은 레코드를 가지고 오는 것을 최적화를 할 수 없다. (여기서 최적화는 Disk Linear I/O 최적화를 말함)

 

Disk Linear I/O 최적화를 위해서 읽어야 할 레코드들을 한번에 스토로지 엔진에 전달하는 방법이 MRR (Multi Range Read) 이다. 이 최적화를 수행할 때 출력되는 메시지가 Using MRR 이다.

 

주로 조인을 수행할 때 조인 대상 칼럼의 키 값들을 조인 버퍼에 넣어놓고 스토로지 엔진에 이것들을 전달하는 방식으로 최적화를 수행할 수 있다.

 

예시 쿼리는 다음과 같다:

Using sort_union, union, intersect

index merge 를 이용하는 경우에 여러 인덱스에서 읽은 값들을 합치는 과정이 필요하다.

 

합치는 방식은 교집합 방식과 합집합 방식이 있다.

 

교집합 방식일 때 Using intersect 가 사용되고, 합집합 방식일 때 Using union 과 Using sort_union 이 사용된다.

 

합집합 방식이 두 종류인 이유는 인덱스 내부에 있는 PK 들이 정렬되어 있느냐의 여부에 따라서 다르다.

• 모든 세컨더리 인덱스는 내부적으로 PK 를 포함하고 있다.
• 인덱스를 = 동등 조건으로 검색된 경우에는 PK 들이 정렬되어 있을 것이고 이외에 범위 조건으로 검색한 경우에는 PK 가 정렬되어 있진 않을 수 있다.

 

Using temporary

쿼리를 처리할 때 중간 결과를 저장하기 위해서 임시 테이블이 사용되는 경우에 이 메시지가 출력된다.

 

주의할 건 Using temporary 가 사용되지 않더라도 내부적으로 임시 테이블을 사용할 수도 있다.

• FROM 절에 사용된 서브쿼리는 임시 테이블이 사용된다.
• COUNT (DISTINCT col1) 과 같은 쿼리에서 col1 이 인덱스를 이용할 수 없다면 임시 테이블이 사용된다.
• UNION 과 UNION DISTINCT 와 같은 쿼리도 임시 테이블이 사용된다.
• 인덱스를 사용하지 못하는 정렬도 임시 테이블이 사용된다. 이 경우에는 Using filesort 메시지가 출력된다.

 

Using temporary 명령 만으로는 디스크 임시 테이블이 생성되었는지, 메모리 임시 테이블이 생성되었는지 알 지 못한다.

 

그래서 SHOW STATUS 와 같은 명령을 내려서 확인해봐야 함.

 

Using Where

스토로지 엔진이 가지고 온 데이터를 MySQL 엔진이 필터링 하거나 가공하는 과정을 거친다면 이 메시지가 출력된다.

 

즉 스토로지 엔진이 가지고 온 데이터를 그대로 클라이언트에게 반환한다면 이 메시지는 출력되지 않는다.

 

(근데 이것도 출력 안되는 경우가 있긴 하더라. 그래서 실행 계획의 filtered 칼럼도 참조 해야함. filtered 가 있으면 WHERE 을 쓴 것이니까.) 

 

`Using Where` 로 인해서 레코드를 필터링 해서 남은 유효한 레코드의 개수를 보고 싶다면 실행 계획에서 filtered 를 보면 된다.

• filtered 에 10 이라고 적혀 있다면 10% 만이 유효한 데이터라고 알면 됨.

Zero Limit

MySQL 에서 쿼리를 실행할 때 테이블 레코드를 가지고 오지 않고 메타 데이터 (= 무슨 칼럼이 있는지, 칼럼의 타입은 뭔지 등) 만을 보고 싶다면 LIMIT 0 명령을 내리면 된다.

 

이렇게 명령을 내릴 경우에 출력되는 메시지가 Zero Limit 이다.