10. 실행 계획-2

Extra 컬럼

쿼리의 실행 계획에서 성능에 중요한 내용이 Extra 컬럼에 자주 표시된다.주로 내부적인 처리 알고리즘에 대한 내용이 출력된다.

const row not found

const 접근 방법으로 테이블을 읽었지만 실제로 해당 테이블에 레코드가 1건도 존재하지 않으면 표시된다.

Deleting all rows

MyISAM 스토리지 엔진처럼 스토리지 엔진의 핸들러 차원에서 테이블의 모든 레코드를 삭제하는 기능을 제공하는 스토리지 엔진 테이블에서 표시된다.스토리지 엔진의 핸들러 함수를 레코드 건수만큼 호출하지 않고, 테이블의 모든 레코드를 삭제하는 핸들러 기능(API)을 한 번 호출함으로써 처리됐음을 의미한다.

주로 WHERE 조건절이 없는 DELETE 문장의 실행 계획에서 표시된다.

 

참고: 8.0 버전에서는 MyISAM 에서도 "Deleteing all rows" 최적화는 표시되지 않는다. 테이블의 모든 레코드를 삭제하고자 한다면 WHERE 조건절이 없는 DELETE 보다 TRUNCATE TABLE 명령을 사용할 것을 권장한다.

Distinct

 

departments 테이블과 dept_emp 테이블에 모두 존재하는 dept_no 만 중복 없이 유니크하게 가져오기 위한 쿼리

두 테이블을 조인해서 그 결과에 DISTINCT 처리를 한다.

 

Extra: DISTINCT 의 처리 방식

 

departments 테이블의 인덱스를 읽으면서 dept_emp 테이블에서 dept_no 가 중복되는 레코드는 읽지 않고 꼭 필요한 레코드만 조인한다.

FirstMatch

세미 조인 최적화 전략 중 FirstMatch 가 사용된 경우

Extra: FirstMatch(table_name)

기준 테이블(table_name)에서 첫 번째로 일치하는 한 건만 검색하는 방식으로 작동한다.

 

 

• FirstMatch 최적화에서 서브쿼리는 그 서브쿼리가 참조하는 모든 아우터 테이블이 먼저 조회된 이후에 실행된다. (e->t)
• employees 테이블(Outer)을 기준으로 titles 테이블(Sub)에서 첫 번째로 일치하는 한 건만 검색한다.

Full scan on NULL key

col IN (subquery) 에서 col 에 NOT NULL 제약이 없다면 NULL 값이 들어갈 수 있다.

 

"NULL IN (subquery)" 에 대한 NULL 비교 규칙

• 서브쿼리가 1건이라도 결과 레코드를 가진다면 최종 비교 결과는 NULL
• 서브쿼리가 1건도 결과 레코드를 가지지 않는다면 최종 비교 결과는 FALSE

col 이 NULL이면 서브쿼리에 사용된 테이블에 대해 풀 테이블 스캔(Full scan)을 해야만 결과를 알아낼 수 있다.

"Full scan on NULL key"는 쿼리 실행 중에 col에 NULL 값이 들어오면 풀 테이블 스캔이 일어날 수 있다는 키워드이다.

만약 outer 쿼리에서 NULL 값이 전달되지 않는다면 실제 풀 테이블 스캔은 일어나지 않는다.

 

 

컬럼이 NOT NULL로 정의되지는 않았지만 NULL 비교 규칙을 무시해도 된다면 col1이 절대 NULL은 될 수 없다는 것을 옵티마이저에게 알려주면 된다.

 

 

col1이 NULL이면 후속 조건인 "col1 IN (SELECT col2 FROM tb_test2)" 조건은 실행하지 않는다.

Inpossible HAVING

쿼리에 사용된 HAVING 절의 조건을 만족하는 레코드가 없을 때 표시된다.

 

 

HAVING 절의 "e.emp_no IS NULL" 조건이 있지만, employees.emp_no 컬럼은 프라이머리 키면서 NOT NULL 컬럼이므로 해당 조건을 만족할 가능성이 없으므로 Extra 컬럼에서 "Impossible HAVING" 키워드를 표시한다.

Impossible WHERE

Impossible HAVING 과 유사하게 WHERE 조건이 항상 FALSE 가 될 수 밖에 없는 경우 표시된다.

LooseScan

세미 조인 최적화 중 LooseScan 최적화 전략이 사용되면 표시된다.

 

No matching min/max row

쿼리의 WHERE 조건절을 만족하는 레코드가 한 건도 없는 경우 일반적으로 "Impossible WHERE ..." 문장이 표시되는데, MIN()이나 MAX()와 같은 집합 함수가 있는 쿼리의 조건절에 일치하는 레코드가 한 건도 없을 때는 "No matching min/max row" 메세지가 출력된다. 그리고 MIN()이나 MAX() 의 결과로 NULL 이 반환된다.

 

 

참고: "No matching..." 이나 "Impossible WHERE..."과 같은 메세지는 쿼리의 문법적 오류가 있는 것은 아니고 쿼리 처리를 위한 데이터가 없다는 의미다.

no matching row in const table

조인에 사용된 테이블에서 const 방법으로 접근할 때 일치하는 레코드가 없을 때 표시된다.

"Impossible WHERE ..."와 같은 종류로, 실행 계획을 만들기 위한 기초 자료가 없음을 의미한다.

 

No matching rows after partition pruning

파티션된 테이블에 대한 UPDATE 또는 DELETE 명령의 실행 계획에서 WHERE 조건에 부합하는 파티션이 존재하지 않을 때 표시된다.

 

 

• 파티션된 테이블 employees_parted 에는 hire_date < '2006-01-01' 까지만 파티션이 정의돼 있다.
• partitions: NULL, Extra: No matching rows after partition pruning
• 삭제할 레코드도 없고, 파티션도 정의되어 있지 않다. 

 

• partitions: p1986_1990, Extra: No matcing~ 이 없음
• 실제 삭제할 레코드는 없지만 대상 파티션은 존재한다.

No tables used

FROM 절이 없는 쿼리 문장이나 "FROM DUAL" 형태의 쿼리 실행 계획에 출력된다.

DUAL: 컬럼과 레코드를 각각 1개씩만 가지는 가상의 상수 테이블

다른 DBMS와 달리 MySQL 서버는 FROM 절이 없는 쿼리도 허용한다.

 

Not exists

A 테이블에는 존재하지만 B 테이블에는 없는 값(A-B)을 조회하는 쿼리에서 주로 NOT IN (subquery) 형태나 NOT EXISTS 연산자를 사용한다. -> Anti-JOIN

레코드의 건수가 많을 때 똑같은 쿼리를 아우터 조인(LEFT OUTER JOIN)으로 바꿔서 빠른 성능을 낼 수 있다.

아우터 조인을 이용해 안티-조인을 수행하는 쿼리의 실행 계획 Extra 컬럼에 "Not exists" 메세지가 표시된다.

*SQL의 NOT EXISTS 연산자를 사용했다는 의미가 아님

 

 

• dept_emp 테이블에는 있지만 departments 테이블에는 없는 dept_no 를 조회하는 쿼리
• departments 테이블을 아우터 조인해서 WHERE 절에 아우터 테이블(departments)의 dept_no 컬럼이 NULL인 레코드만 체크해서 가져온다. 
• "Not exists" 메세지는 조인할 때 옵티마이저가 departments 테이블의 레코드가 존재하는지 아닌지만 판단한다. 
• 즉, departments 테이블에 조인 조건에 일치하는 레코드가 여러 건 있어도 딱 1건만 조회하고 처리를 완료하는 최적화를 의미한다.

Plan isn't ready yet

8.0 버전에서는 다음과 같이 다른 커넥션에서 실행 중인 쿼리의 실행 계획을 살펴볼 수 있다.

"EXPLAIN FOR CONNECTION conn_id;"

 

EXPLAIN FOR CONNECTION 명령을 실행할 때 Extra 컬럼에 "Plan is not ready yet"이라는 메시지가 표시되면 해당 커넥션에서 아직 쿼리의 실행 계획을 수립하지 못한 상태에서 EXPLAIN FOR CONNECTION 명령이 실행된 것을 의미한다.

 

SHOW PROCESSLIST

conn_id=9 에서 각 프로세스에서 실행 중인 쿼리 확인

 

EXPLAIN FOR CONNECTION

conn_id=9 에서 conn_id=8에서 실행중인 쿼리의 실행 계획 확인

Range checked for each record(index map:N)

레코드마다 인덱스 레인지 스캔을 체크하는 방법

 

 

조인 조건에 상수가 없고 둘 다 변수인 경우

드라이빙 테이블(e1)의 레코드를 하나씩 읽을 때마다 조인 조건(범위)이 바뀌어서 어느 접근 방법으로 드리븐 테이블(e2)을 읽는 것이 좋을지 판단할 수 없다.

위 쿼리의 경우 e1.emp_no가 작을 때는 e2 테이블을 풀 테이블 스캔으로 접근하고, e1 테이블의 emp_no 가 클 때는 e2 테이블을 인덱스 레인지 스캔으로 접근하는 형태가 최적의 조인 방법이다.

 

레코드별로 range와 ALL 중에서 선택해야 하는 실행 계획

 

 

• (index map: 0x1): 사용할지 말지를 판단하는 후보 인덱스의 순번. 16진수로 표시 => e2 테이블의 첫 번째 인덱스를 사용할지, 풀 테이블 스캔할지를 레코드 단위로 결정하면서 처리함
• 인덱스의 순번은 CREATE TABLE 명령에 나열된 인덱스의 순번을 의미한다.
• Extra 컬럼에 "Range checked for each record" 가 표시되면 type 은 ALL 로 표시된다.

예) Extra: Range checked for each record (index map: 0x19)

0x19 - 비트(이진) 값으로 변환 -> 11001

 

CREATE TABLE 명령

 

 

실행 계획에서 자릿수의 값이 1인 다음 인덱스를 사용 가능한 인덱스 후보로 선정했다. 

• PRIMARY KEY
• ix_nick_gender
• ix_nick_phone

실제로 어떤 인덱스가 사용됐는지는 알 수 없다. 단지 비트맵의 자릿수가 1인 순번의 인덱스가 대상이다.

Recursive

8.0 버전부터는 CTE(Common Table Expression)을 이용해 재귀 쿼리를 작성할 수 있게 됐다.

MySQL 서버에서 재귀 쿼리는 다음과 같이 WITH 구문을 이용해 CTE를 사용하면 된다.

 

 

WITH 절에서 실행하는 작업

1. "n"이라는 컬럼 하나를 가진 cte라는 이름의 내부 임시 테이블을 생성
2. "n" 컬럼 값을 1부터 5까지 증가시켜 만든 레코드 5건을 cte 내부 임시 테이블에 저장

WITH 절 다음의 SELECT 쿼리에서 WITH 절에서 생성된 내부 임시 테이블을 (WHERE 절이 없으므로) 풀 스캔해서 결과를 반환한다.

 

CTE를 이용한 재귀 쿼리의 실행 계획

 

참고: WITH 구문을 이용한 CTE가 사용됐다고 해서 무조건 Extra 컬럼에 "Recursive" 문구가 표시되는 것은 아니다. WITH 구문이 재귀 CTE로 사용될 경우에만 "Recursive" 메세지가 표시된다.

Rematerialize

8.0 버전부터 추가된 래터럴 조인(LATERAL JOIN) 기능을 사용하면 선행 테이블의 레코드 별로 서브쿼리를 실행해서 그 결과를 임시 테이블에 저장한다. 이 과정을 "Rematerializing"이라고 한다.

래터럴 조인: LATERAL 키워드를 통해 서브쿼리에서 메인 쿼리 FROM 절에 있는 테이블의 컬럼을 참조할 수 있다

 

 

1. employees 테이블의 레코드마다 salaries 테이블에서 emp_no가 일치하는 레코드 중에서 from_date 컬럼의 역순(DESC)으로 2건(LIMIT 2)만 가져와 임시 테이블 "derived2"로 저장한다.
2. employees 테이블과 "derived2" 테이블을 조인한다. "derived2" 임시 테이블은 employees 테이블의 레코드마다 새로 내부 임시 테이블이 생성된다.

employees 테이블의 레코드마다 임시 테이블이 새로 생성되기 때문에 Extra 컬럼에 "Rematerialize" 문구가 표시된다.

Select tables optimized away

• SELECT 절에 MIN() 또는 MAX() 만 사용된 쿼리
• GROUP BY로 MIN(), MAX()를 조회하는 쿼리

에서 인덱스를 오름차순 또는 내림차 순으로 1건만 읽는 최적화가 적용될 때 표시된다.

 

MyISAM 테이블에서는 GROUP BY나 WHERE 조건절 없이 COUND(*)만 SELECT 할 때도 이런 최적화가 적용된다.

MyISAM 테이블은 전체 레코드 건수를 별도로 관리하기 때문에 인덱스나 데이터를 읽지 않고도 전체 건수를 빠르게 조회할 수 있다. 

 

 

WHERE 조건이 없는 첫 번째 쿼리의 최적화

 

첫 번째 쿼리는 employees 테이블의 emp_no 컬럼에 인덱스가 생성돼 있으므로 "Select tables optimized away" 최적화가 가능하다. 인덱스에서 첫 번째 레코드와 마지막 레코드만 읽어 최솟값과 최댓값을 가져온다.

 

WHERE 조건이 있는 두 번째 쿼리의 최적화

 

두 번째 쿼리는 salaries 테이블에 (emp_no, from_date) 인덱스가 생성돼 있으므로 emp_no=10002 인 레코드를 검색하고, 검색된 결과 중에서 오름차순 또는 내림차순으로 하나만 조회하면 된다.

Start temporary, End temporary

세미 조인 최적화 중에서 Duplicate Weed-out 최적화 전략이 사용된 경우 표시된다.

 

 

Duplicate Weed out 최적화 전략 (INNER JOIN + GROUP BY)

불필요한 중복을 제거하기 위해 내부 임시 테이블을 사용

조인의 첫 번째 테이블 -> Start temporary

조인이 끝나는 부분 -> End temporary

unique row not found

두 개의 테이블이 프라이머리 키 또는 유니크 컬럼으로 아우터 조인을 수행하는 쿼리에서 아우터 테이블(드라이빙 테이블)과 일치하는 레코드가 이너 테이블에 존재하지 않을 때 표시된다.

 

 

tb_test2 테이블에는 fdpk=2인 레코드가 없으므로 "unique row not found"가 표시된다.

Using filesort

ORDER BY를 처리하기 위한 적절한 인덱스가 없을 때 표시된다.

MySQL 서버가 조회된 레코드를 정렬용 메모리 버퍼(Sort buffer)에 복사하여 퀵 소트 또는 힙 소트 알고리즘을 이용해 정렬을 수행한다는 의미이다.

 

 

• last_name 컬럼에는 인덱스가 없으므로 이 쿼리의 정렬 작업에 인덱스를 이용할 수 없다.
• "Using filesort"가 출력되는 쿼리는 많은 부하를 일으키므로 가능하다면 쿼리를 튜닝하거나 인덱스를 생성하는 것이 좋다.

Using index(커버링 인덱스)

데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있을 때 표시된다.

인덱스를 이용해 처리하는 쿼리에서 가장 큰 부하를 차지하는 부분은 인덱스 검색에서 일치하는 키 값의 레코드를 읽기 위해 데이터 파일을 검색하는 작업이다.

 

B-Tree 인덱스 검색 후 데이터 레코드 읽기

 

 

인덱스(ix_firstname) 사용하는 방법

1. employees 테이블의 first_name 컬럼에 생성된 인덱스(ix_firstname)를 이용해 일치하는 레코드 5만여 건 검색
2. 각 레코드의 birth_date 컬럼 값을 읽기 위해 각 레코드가 저장된 데이터 페이지를 5만여 번 읽기

옵티아미저는 이렇게 인덱스를 사용하는 것보다 풀 테이블 스캔으로 처리하는 편이 더 효율적이라고 판단했다.

 

만일 같은 쿼리에서 first_name 컬럼만 SELECT 한다면 인덱스 레인지 스캔으로 처리될 것이다.

인덱스(ix_firstname)를 통해 필요한 레코드를 검색하고 필요한 컬럼(first_name)까지 가져올 수 있기 때문에 데이터 파일을 읽어올 필요가 없다. 

이렇게 인덱스만으로 쿼리를 수행할 수 있을 때 Extra 컬럼에 "Using index"라는 메세지가 출력된다.

 

 

InnoDB 테이블은 클러스터링 인덱스를 사용하기 때문에 모든 세컨더리 인덱스는 데이터 레코드의 주솟값으로 프라이머리 키 값을 가진다. 

 

InnoDB에서의 B-Tree 인덱스와 데이터 레코드

 

InnoDB 테이블에서는 first_name 컬럼만으로 인덱스를 만들어도 결국 emp_no 컬럼이 같이 저장되는 효과가 있다.

이러한 클러스터링 인덱스 특성 때문에 쿼리가 "커버링 인덱스"로 처리될 가능성이 상당히 높다.

 

 

인덱스를 사용하는 실행 계획에서는 모두 Extra 컬럼에 "Using index"가 표시될 수 있다.

인덱스 레인지 스캔(type: eq_ref, ref, index_merge)을 사용할 때만 커버링 인덱스로 처리되는 것은 아니다.

인덱스 풀스캔(type: index)을 실행할 때도 커버링 인덱스로 처리될 수 있는데 똑같은 인덱스 풀 스캔이라도 커버링 인덱스가 아닌 경우보다 훨씬 빠르게 처리된다.

 

• Extra 컬럼의 "Using index" : 인덱스만으로 쿼리 처리(SELECT) -> 커버링 인덱스
• type 컬럼의 "index": 인덱스 풀 스캔으로 처리

Using index condition

옵티마이저가 인덱스 컨디션 푸시 다운(Index condition pushdown) 최적화를 사용하면 표시된다.

 

 

• ix_lastname_firstname(last_name, first_name)
• 체크 조건 "first_name LIKE '%sal' 에 대하여 인덱스의 두 번째 컬럼을 이용하여 조회하고 결과 레코드만을 읽어온다.

Using index for group-by

 GROUP BY 처리에는 다음과 같은 작업이 필요하다.

1. 그루핑 기준 컬럼에 대한 정렬
2. 정렬된 결과를 그루핑

하지만 GROUP BY 처리가 B-Tree 인덱스를 이용하면 이미 정렬된 컬럼을 순서대로 읽으면서 그루핑 작업만 수행하면 된다. 이때 루스 인덱스 스캔을 이용해 인덱스의 필요한 부분만 듬성듬성 읽으며 효율적으로 처리한다.

인덱스를 이용한 GROUP BY 처리에서 "Using index for group-by" 메세지가 표시된다.

 

 

타이트 인덱스 스캔(인덱스 스캔)을 통한 GROUP BY 처리

인덱스를 이용해 GROUP BY 절을 처리할 수 있더라도 조회하는 값이 AVG(), SUM(), COUND() 처럼 모든 인덱스를 다 읽어야 할 때는 루스 인덱스 스캔을 사용할 수 없다.

이런 쿼리의 실행 계획에는 "Using index for group-by" 메세지가 출력되지 않는다.

 

커버링 인덱스, 인덱스 풀 스캔으로 GROUP BY 처리

 

루스 인덱스 스캔을 통한 GROUP BY 처리

 

GROUP BY 처리를 위해 루스 인덱스 스캔을 사용할 수 있는 쿼리

• 단일 컬럼 인덱스: 그루핑 컬럼 말고 아무 것도 조회하지 않는 쿼리
• 다중 컬럼 인덱스: GROUP BY 절이 인덱스를 사용할 수 있고, MIN()이나 MAX() 처럼 조회하는 값이 인덱스의 첫 번째 또는 마지막 레코드만 읽어도 되는 쿼리

 

PRIMARY (emp_no, from_date) 

• 그루핑 컬럼 emp_no
• emp_no 그룹별로 첫 번째 from_date 값(MIN)과 마지막 from_date 값(MAX)을 인덱스로부터 읽으면 되기 때문에 루스 인덱스 스캔 방식으로 처리할 수 있다.

 

WHERE 절에서 사용하는 인덱스에 의해서도 GROUP BY 절의 인덱스 사용 여부가 영향을 받는다.

• WHERE 조건절이 없는 경우: GROUP BY 절과 SELECT 절의 컬럼이 "루스 인덱스 스캔"을 사용할 수 있는 조건만 갖추면 된다. 그렇지 못한 쿼리는 타이트 인덱스 스캔(인덱스 스캔)이나 별도의 정렬 과정을 통해 처리된다.
• WHERE 조건절이 검색을 위해 인덱스를 사용하지 못하는 경우: GROUP BY를 위해 인덱스를 읽은 후, WHERE 조건의 비교를 위해 데이터 레코드를 읽어야 한다. 그래서 "루스 인덱스 스캔"을 이용할 수 없으며, 타이트 인덱스 스캔을 통해 GROUP BY가 처리된다.
• WHERE 조건절이 검색을 위해 인덱스를 사용하는 경우: 하나의 단위 쿼리가 실행되는 경우, index_merge 이외의 접근 방법에서는 단 하나의 인덱스만 사용할 수 있다. 즉 WHERE 절의 조건과 GROUP BY 처리가 똑같은 인덱스를 공통으로 사용할 수 있을 때만 루스 인덱스 스캔을 사용할 수 있다. 서로 사용할 수 있는 인덱스가 다른 경우 일반적으로 옵티마이저는 WHERE 절이 인덱스를 사용하도록 실행 계획을 수립하는 경향이 있다. 작업 범위를 좁히지 못하는 WHERE 조건이라도 GROUP BY 보다 WHERE 조건이 먼저 인덱스를 사용할 수 있게 실행 계획이 수립되기도 한다.

참고: WHERE 절과 GROUP BY 절 처리를 위해 같은 인덱스를 사용할 수 있더라도 "루스 인덱스 스캔"을 사용하지 않을 수 있다. WHERE 조건에 의해 검색된 레코드 건수가 적으면 루스 인덱스 스캔을 사용하지 않아도 매우 빠르게 처리될 수 있기 때문이다. "루스 인덱스 스캔"은 주로 대량의 레코드를 GROUP BY 하는 경우 성능 향상 효과가 있기 때문에 옵티마이저가 적절히 손익 분기점을 판단한다.

예제 쿼리에서 WHERE 절의 검색 범위만 좁혀도 실행 계획 Extra 컬럼에 "Using index for group-by" 처리가 사라졌다.

Using index for skip scan

옵티마이저가 인덱스 스킵 스캔 최적화를 사용하면 표시된다.

8.0 버전부터 루스 인덱스 스캔 최적화를 확장한 인덱스 스킵 스캔 최적화가 도입됐다.

 

 

인덱스 첫 번째 컬럼(gender)의 조건이 없어도 두 번째 컬럼(birth_date)의 조건을 위해 인덱스를 사용할 수 있다.

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

일반적으로 빠른 쿼리 실행을 위해 조인되는 컬럼은 인덱스를 생성한다. 하지만 실제 조인에 필요한 인덱스는 조인에서 뒤에 읽는 테이블(드리븐 테이블)의 컬럼에만 필요하다. 

옵티마이저도 조인되는 두 테이블의 인덱스를 조사하고, 인덱스가 없는 테이블이 있으면 그 테이블을 먼저 읽어서 조인을 실행한다. 뒤에 읽는 테이블은 검색 위주로 사용되기 때문에 인덱스가 없으면 성능에 미치는 영향이 매우 크기 때문이다.

 

만일 드리븐 테이블에 검색을 위한 적절한 인덱스가 없다면 MySQL 서버는 블록 네스티드 루프 조인이나 해시 조인을 사용한다. 그 과정에서 조인 버퍼(join buffer)가 사용된다.

조인 버퍼가 사용되는 실행 계획의 Extra 컬럼에 "Using join buffer (join algorithm)"가 표시된다.

 

조인 조건이 없는 카테시안 조인은 항상 조인 버퍼를 사용한다.

 

카테시안 조인(cartesian join)

Using MRR

MySQL 엔진은 실행 계획을 수립하고 그 실행 계획에 맞게 스토리지 엔진의 API를 호출하여 쿼리를 처리한다.

스토리지 엔진은 MySQL 엔진이 넘겨주는 키 값을 기준으로 레코드를 한 건 한 건 읽어서 반환하는 방식으로 동작한다.

실제 반환하는 레코드가 동일 페이지에 있어도 레코드 단위로 API 호출이 필요하다.

 

MRR(Multi Range Read): 여러 개의 키 값을 한 번에 스토리지 엔진으로 전달하고, 스토리지 엔진은 넘겨받은 키 값을 정렬하여 최소한의 페이지 접근만으로 필요한 레코드를 읽을 수 있게 최적화한다.

 

 

• salaries 테이블의 검색 결과에서 조인 키 값(s.emp_no)들을 모아서 MRR 엔진에 전달
• MRR 엔진은 키 값을 정렬하여 employees 테이블을 최적화된 방법으로 접근

Using sort_union(...), Using union(...), Using intersect(...)

쿼리가 index_merge 접근 방법으로 실행되는 경우에는 2개 이상의 인덱스가 동시에 사용될 수 있다.

이때 실행 계획의 Extra 컬럼에는 두 인덱스로부터 읽은 결과를 어떻게 병합했는지 상세하게 설명하기 위에 다음 메세지 중 하나를 선택적으로 출력한다.

• Using intersect(...): 인덱스를 사용할 수 있는 조건이 AND로 연결된 경우, 각 처리 결과에서 교집합을 추출하는 작업을 수행했다는 의미
• Using union(...): 인덱스를 사용할 수 있는 조건이 OR로 연결된 경우, 각 처리 결과에서 합집합을 추출하는 작업을 수행했다는 의미
• Using sort_union(...): Using union과 같은 작업을 수행하지만 Using union으로 처리할 수 없는 경우 (OR로 연결된 대량의 range 조건들) 이 방식으로 처리된다. Using sort_union은 프라이머리 키만 먼저 읽어서 정렬하고 병합한 후 비로소 레코드를 읽어서 반환할 수 있다.

Using union()과 Using sort_union()은 인덱스를 사용할 수 있는 조건이 OR 연산자로 연결된 경우에 사용된다.

 

실제로는 레코드 건수에 관계없이 WHERE 조건에 사용된 비교 조건이 동등 조건(Equal)이면 Using union()이 사용되며, 그렇지 않으면 Using sort_union()이 사용된다.

Using temporary

쿼리를 처리하는 동안 중간 결과를 담아두기 위해 임시 테이블(Temporary table)이 사용되면 표시된다.

임시 테이블이 메모리에 생성됐는지, 디스크에 생성됐는지는 실행 계획만으로 판단할 수 없다.

 

mysql> EXPLAIN
	SELECT * FROM employees
	GROUP BY gender
	ORDER BY MIN(emp_no);

 

• GROUP BY 컬럼과 ORDER BY 컬럼이 다르기 때문에 임시 테이블이 필요하다.
• 인덱스를 사용하지 못하는 GROUP BY 쿼리는 실행 계획에서 "Using temporary" 메세지가 표시되는 가장 대표적인 형태의 쿼리다.

참고: 실행 계획의 Extra 컬럼에 "Using temporary"가 표시되지 않아도 내부적으로 임시 테이블을 사용할 때도 많다.

• FROM 절에 사용된 서브쿼리 => 파생 테이블(Derived table)
• "COUNT(DISTINCT ...)"를 포함하면서 인덱스를 사용할 수 없는 쿼리
• UNION이나 UNION DISTINCT가 사용된 쿼리 (UNION ALL은 8.0 버전부터 내부 임시 테이블 사용X)
• 인덱스를 사용하지 못하는 정렬 작업. Sort buffer도 결국 임시 테이블과 같다. -> Extra: "Using filesort"

Using where

• 스토리지 엔진: 디스크나 메모리상에서 필요한 레코드를 읽거나 저장하는 작업 (작업 범위 결정 조건)
• MySQL 엔진: 스토리지 엔진으로부터 받은 레코드를 가공하거나 연산하는 작업 (체크 조건)

MySQL 엔진 레이어에서 별도의 가공을 해서 필터링 작업을 처리한 경우에만 표시된다.

별도의 필터링이나 가공 없이 데이터를 그대로 클라이언트에 전달하면 "Using where"가 표시되지 않는다.

 

 

• 스토리지 엔진이 작업 범위 결정 조건("emp_no BETWEEN 10001 AND 10100")에 해당하는 데이터를 읽어 MySQL 엔진에 넘겨준다. (rows: 100건)
• MySQL 엔진은 체크 조건("gender='F'")으로 데이터를 필터링 한다. 
• 필터링에서 걸러진 데이터는 버려진다. "Using where"는 이 작업을 의미한다.
• filtered 컬럼의 값이 50%이므로 옵티마이저는 rows 건의 레코드 중에 인덱스를 사용하지 못하는 조건(체크 조건)에 일치하는 비율이 50%라고 예측하고 있다. (100 * 0.50 = 50건)

실행 계획에서 가장 흔히 표시되는 내용이기도 하며 "filtered" 컬럼을 통해 "Using where"가 성능상 문제를 일으키는지 판단할 수 있다.

Zero limit

MySQL 서버에서 데이터 값이 아닌 쿼리 결과 값의 메타데이터만 필요한 경우, 쿼리의 마지막에 "LIMIT 0"을 붙이면 사용자의 의도를 알아채고 실제 테이블의 레코드는 전혀 읽지 않고 결과 값의 메타 정보만 반환한다.

ex) 쿼리의 결과가 몇 개의 컬럼을 가지고, 각 컬럼의 타입은 무엇인지 등

 

이때 실행 계획의 Extra 컬럼에 "Zero limit" 메세지가 출력된다.