10.8.2 EXPLAIN Output Format

10.8.2 EXPLAIN Output Format

EXPLAIN statement는 MySQL이 statement를 어떻게 실행하는지에 대한 정보를 제공합니다. EXPLAIN은 SELECT, DELETE, INSERT, REPLACE, UPDATE statement와 함께 동작합니다.

EXPLAIN은 SELECT statement에서 사용되는 각 테이블마다 한 로우의 정보를 반환합니다. MySQL이 statement를 처리하면서 테이블을 읽는 순서대로 테이블을 출력에 나열합니다. 이는 MySQL이 첫 번째 테이블에서 한 로우를 읽은 다음, 두 번째 테이블에서 일치하는 로우를 찾고, 그다음 세 번째 테이블에서 찾는 식으로 진행한다는 것을 의미합니다.

모든 테이블이 처리되면 MySQL은 선택된 컬럼을 출력하고, 테이블 목록을 역순으로 거슬러 올라가면서 더 많은 일치 로우가 있는 테이블을 찾습니다. 이 테이블에서 다음 로우를 읽고, 같은 방식으로 다음 테이블에 대해 처리를 계속합니다.

참고

MySQL Workbench에는 EXPLAIN output을 시각적으로 표현하는 Visual Explain 기능이 있습니다. Tutorial: Using Explain to Improve Query Performance을 참고하십시오.

• EXPLAIN Output Columns

• EXPLAIN Join Types

• EXPLAIN Extra Information

• EXPLAIN Output Interpretation

EXPLAIN Output Columns

이 섹션에서는 EXPLAIN에 의해 생성되는 output 컬럼을 설명합니다. 뒤이어 나오는 섹션에서는 type 및 Extra 컬럼에 대해 추가 정보를 제공합니다.

EXPLAIN의 각 output 로우는 하나의 테이블에 대한 정보를 제공합니다. 각 로우에는 Table 10.1, “EXPLAIN Output Columns”에 요약된 값이 포함되며, 테이블 뒤에서 각 컬럼에 대해 더 자세히 설명합니다. 컬럼 이름은 테이블의 첫 번째 컬럼에 표시되며, 두 번째 컬럼은 FORMAT=JSON이 사용될 때 output에 표시되는 동등한 프로퍼티 이름을 제공합니다.

Table 10.1 EXPLAIN Output Columns

Column	JSON Name	Meaning
id	select_id	SELECT identifier
select_type	None	SELECT 타입
table	table_name	output 로우에 대한 테이블
partitions	partitions	매칭되는 파티션
type	access_type	조인 타입
possible_keys	possible_keys	선택 가능한 인덱스
key	key	실제 선택된 인덱스
key_len	key_length	선택된 키의 길이
ref	ref	인덱스와 비교되는 컬럼
rows	rows	검사할 로우 수 추정값
filtered	filtered	테이블 조건에 의해 필터링되는 로우 비율(%)
Extra	None	추가 정보

Column	JSON Name	Meaning

참고

값이 NULL인 JSON 프로퍼티는 JSON 형식의 EXPLAIN output에는 표시되지 않습니다.

• id (JSON name: select_id)

SELECT identifier입니다. 이는 쿼리 내에서 SELECT의 순차 번호입니다. 로우가 다른 로우의 union 결과를 나타내는 경우 값은 NULL일 수 있습니다. 이 경우 table 컬럼은 <unionM,N>과 같은 값을 보여주며, 이 로우가 id 값이 _M_과 _N_인 로우의 union을 참조함을 나타냅니다.

• select_type (JSON name: none)

SELECT의 타입입니다. 다음 테이블에 나오는 값 중 하나일 수 있습니다. JSON 형식의 EXPLAIN에서는 SELECT 타입이 query_block의 프로퍼티로 표시되며, SIMPLE 또는 PRIMARY인 경우는 제외됩니다. JSON 이름(있는 경우)은 테이블에도 같이 표시됩니다.

select_type Value	JSON Name	Meaning
SIMPLE	None	간단한 SELECT ( UNION 또는 서브쿼리 사용 안 함)
PRIMARY	None	가장 바깥쪽 SELECT
UNION	None	UNION 내의 두 번째 또는 그 이후 SELECT statement
DEPENDENT UNION	dependent (true)	바깥 쿼리에 의존하는<br> UNION 내의 두 번째 또는 그 이후 SELECT statement
UNION RESULT	union_result	UNION의 결과
SUBQUERY	None	서브쿼리 내의 첫 번째 SELECT
DEPENDENT SUBQUERY	dependent (true)	바깥 쿼리에 의존하는 서브쿼리 내의 첫 번째<br> SELECT
DERIVED	None	파생 테이블
DEPENDENT DERIVED	dependent (true)	다른 테이블에 의존하는 파생 테이블
MATERIALIZED	materialized_from_subquery	머티리얼라이즈된 서브쿼리
UNCACHEABLE SUBQUERY	cacheable (false)	결과를 캐시할 수 없고 outer 쿼리의 각 로우마다 다시 계산해야 하는 서브쿼리
UNCACHEABLE UNION	cacheable (false)	uncacheable 서브쿼리에 속하는<br> UNION 내 두 번째 이후의 SELECT ( UNCACHEABLE SUBQUERY 참조)

select_type Value	JSON Name	Meaning

DEPENDENT는 일반적으로 상관 서브쿼리의 사용을 의미합니다. Section 15.2.15.7, “Correlated Subqueries”를 참고하십시오.

DEPENDENT SUBQUERY의 평가 방식은 UNCACHEABLE SUBQUERY 평가와 다릅니다. DEPENDENT SUBQUERY의 경우, 서브쿼리는 outer 컨텍스트의 변수 값이 서로 다른 각 집합에 대해 한 번만 다시 평가됩니다. UNCACHEABLE SUBQUERY의 경우, 서브쿼리는 outer 컨텍스트의 각 로우마다 다시 평가됩니다.

EXPLAIN에서 FORMAT=JSON을 지정하면 output에는 select_type에 직접 대응되는 단일 프로퍼티가 없습니다. query_block 프로퍼티가 특정 SELECT에 대응합니다. 바로 앞에서 나열한 대부분의 SELECT 서브쿼리 타입에 해당하는 프로퍼티(예: MATERIALIZED에 대한 materialized_from_subquery)가 존재하며, 필요한 경우 표시됩니다. SIMPLE 또는 PRIMARY에 대한 JSON 대응 값은 없습니다.

non- SELECT statement에 대해서는 select_type 값이 영향을 받는 테이블에 대한 statement 타입을 표시합니다. 예를 들어 DELETE statement에 대해서는 select_type이 DELETE입니다.

• table (JSON name: table_name)

output 로우가 참조하는 테이블의 이름입니다. 다음 값 중 하나가 될 수도 있습니다:

• <unionM,N>:
  이 로우는 id 값이 _M_과 _N_인 로우의 union을 참조합니다.

• <derivedN>:
  이 로우는 id 값이 _N_인 로우에 대한 파생 테이블 결과를 참조합니다. 예를 들어 FROM 절의 서브쿼리로부터 파생 테이블이 생성될 수 있습니다.

• <subqueryN>:
  이 로우는 id 값이 _N_인 로우에 대한 머티리얼라이즈된 서브쿼리의 결과를 참조합니다. Section 10.2.2.2, “Optimizing Subqueries with Materialization”를 참조하십시오.

• partitions (JSON name: partitions)

쿼리에 의해 레코드가 매칭될 파티션입니다. 비파티션 테이블의 경우 값은 NULL입니다. Section 26.3.5, “Obtaining Information About Partitions”를 참고하십시오.

• type (JSON name: access_type)

조인 타입입니다. 각 타입의 설명은 EXPLAIN Join Types를 참조하십시오.

• possible_keys (JSON name: possible_keys)

possible_keys 컬럼은 MySQL이 이 테이블에서 로우를 찾기 위해 선택할 수 있는 인덱스를 나타냅니다. 이 컬럼은 EXPLAIN output에 표시되는 테이블 순서와 완전히 독립적입니다. 즉, possible_keys의 일부 키는 실제 생성된 테이블 순서에서는 사용할 수 없을 수도 있습니다.

이 컬럼이 NULL(또는 JSON 형식 output에서 undefined)인 경우, 관련된 인덱스가 없다는 뜻입니다. 이 경우, WHERE 절이 인덱싱에 적합한 컬럼을 참조하는지 확인하여 쿼리의 성능을 개선할 수 있습니다. 그렇다면 적절한 인덱스를 생성하고 EXPLAIN으로 쿼리를 다시 확인하십시오. Section 15.1.11, “ALTER TABLE Statement”를 참조하십시오.

테이블의 인덱스를 확인하려면 SHOW INDEX FROM tbl_name을 사용하십시오.

• key (JSON name: key)

key 컬럼은 MySQL이 실제로 사용하기로 결정한 키(인덱스)를 나타냅니다. MySQL이 로우를 찾기 위해 possible_keys 인덱스 중 하나를 사용하기로 결정한 경우, 해당 인덱스가 key 값으로 표시됩니다.

key가 possible_keys 값에 존재하지 않는 인덱스 이름을 나타낼 수도 있습니다. 이는 possible_keys 인덱스가 로우 lookup에 적합하지 않지만, 쿼리에서 선택한 모든 컬럼이 다른 인덱스의 컬럼에 포함되어 있는 경우에 발생할 수 있습니다. 즉, 해당 인덱스가 선택된 컬럼을 커버하므로 어느 로우를 가져올지 결정하는 데는 사용되지 않더라도, 데이터 로우 스캔보다 인덱스 스캔이 더 효율적입니다.

InnoDB의 경우, 쿼리가 프라이머리 키도 선택하더라도 세컨더리 인덱스가 선택된 컬럼을 커버할 수 있습니다. 이는 InnoDB가 각 세컨더리 인덱스에 프라이머리 키 값을 저장하기 때문입니다. key가 NULL이면 MySQL은 쿼리를 더 효율적으로 실행하기 위해 사용할 인덱스를 찾지 못한 것입니다.

possible_keys 컬럼에 나열된 인덱스의 사용 여부를 강제하거나 무시하려면, 쿼리에서 FORCE INDEX, USE INDEX, IGNORE INDEX를 사용하십시오. Section 10.9.4, “Index Hints”를 참조하십시오.

MyISAM 테이블의 경우, ANALYZE TABLE을 실행하면 옵티마이저가 더 좋은 인덱스를 선택하는 데 도움이 됩니다. MyISAM 테이블에서는 myisamchk --analyze도 같은 작업을 수행합니다. Section 15.7.3.1, “ANALYZE TABLE Statement”와 Section 9.6, “MyISAM Table Maintenance and Crash Recovery”를 참조하십시오.

• key_len (JSON name: key_length)

key_len 컬럼은 MySQL이 사용하기로 결정한 키의 길이를 나타냅니다. key_len 값으로 MySQL이 멀티파트 키의 몇 부분을 실제로 사용하는지 알 수 있습니다. key 컬럼이 NULL이면 key_len 컬럼도 NULL입니다.

키 저장 포맷 때문에, NULL이 될 수 있는 컬럼은 NOT NULL 컬럼보다 키 길이가 1 더 큽니다.

• ref (JSON name: ref)

ref 컬럼은 테이블에서 로우를 선택하기 위해 key 컬럼에 지정된 인덱스와 비교되는 컬럼이나 상수를 보여줍니다.

값이 func인 경우, 사용되는 값은 어떤 함수의 결과입니다. 어떤 함수가 사용되는지 확인하려면 EXPLAIN 이후에 SHOW WARNINGS를 사용하여 확장된 EXPLAIN output을 보십시오. 여기서 말하는 함수는 산술 연산자와 같은 연산자일 수도 있습니다.

• rows (JSON name: rows)

rows 컬럼은 MySQL이 쿼리를 실행하기 위해 검사해야 한다고 판단한 로우 수를 나타냅니다.

InnoDB 테이블의 경우, 이 수는 추정치이며 항상 정확하지는 않을 수 있습니다.

• filtered (JSON name: filtered)

filtered 컬럼은 테이블 조건에 의해 필터링되는 테이블 로우의 예상 비율을 나타냅니다. 최대 값은 100이며, 이는 로우 필터링이 전혀 발생하지 않았음을 의미합니다. 100에서 멀어질수록 필터링량이 증가함을 의미합니다. rows는 검사할 것으로 추정되는 로우 수를 보여주며, rows × filtered는 다음 테이블과 조인되는 로우 수를 나타냅니다. 예를 들어 rows가 1000이고 filtered가 50.00(50%)이면, 다음 테이블과 조인할 로우 수는 1000 × 50% = 500입니다.

• Extra (JSON name: none)

이 컬럼은 MySQL이 쿼리를 어떻게 처리하는지에 대한 추가 정보를 포함합니다. 각 값에 대한 설명은 EXPLAIN Extra Information를 참조하십시오.

JSON에서는 Extra 컬럼에 직접 대응되는 단일 프로퍼티는 없지만, 이 컬럼에 나타날 수 있는 값은 JSON 프로퍼티이거나 message 프로퍼티의 텍스트로 노출됩니다.

EXPLAIN Join Types

EXPLAIN output의 type 컬럼은 테이블이 어떻게 조인되는지를 설명합니다. JSON 형식의 output에서는 이것이 access_type 프로퍼티의 값으로 나타납니다. 다음 목록은 조인 타입을 가장 좋은 것에서 가장 나쁜 순으로 설명합니다:

• system

테이블에 로우가 하나만 있는 경우(= 시스템 테이블)입니다. 이는 const 조인 타입의 특수한 경우입니다.

• const

테이블에 최대 하나의 매칭 로우만 있으며, 쿼리 시작 시 읽힙니다. 로우가 하나뿐이므로, 이 로우의 컬럼 값은 나머지 옵티마이저에 의해 상수로 취급될 수 있습니다. const 테이블은 한 번만 읽기 때문에 매우 빠릅니다.

const는 PRIMARY KEY 또는 UNIQUE 인덱스의 모든 부분을 상수 값과 비교할 때 사용됩니다. 다음 쿼리에서 _tbl_name_은 const 테이블로 사용될 수 있습니다:

1SELECT * FROM tbl_name WHERE primary_key=1;
2
3SELECT * FROM tbl_name
4    WHERE primary_key_part1=1 AND primary_key_part2=2;

• eq_ref

이 타입에서는 이전 테이블에서 나온 로우 조합 하나당 이 테이블에서 로우 하나가 읽힙니다. system 및 const 타입을 제외하면 가장 좋은 조인 타입입니다. 조인에서 인덱스의 모든 부분이 사용되고 그 인덱스가 PRIMARY KEY 또는 UNIQUE NOT NULL 인덱스인 경우에 사용됩니다.

eq_ref는 = 연산자를 사용하여 비교되는 인덱스 컬럼에 사용할 수 있습니다. 비교 값은 상수이거나, 이 테이블보다 먼저 읽힌 테이블의 컬럼을 사용하는 표현식일 수 있습니다. 다음 예제에서 MySQL은 _ref_table_을 처리하기 위해 eq_ref 조인을 사용할 수 있습니다:

1SELECT * FROM ref_table,other_table
2    WHERE ref_table.key_column=other_table.column;
3
4SELECT * FROM ref_table,other_table
5    WHERE ref_table.key_column_part1=other_table.column
6    AND ref_table.key_column_part2=1;

• ref

이 타입에서는 이전 테이블의 로우 조합 하나당 이 테이블에서 매칭 인덱스 값을 가진 모든 로우가 읽힙니다. 조인이 키의 leftmost prefix만 사용하거나, 키가 PRIMARY KEY 또는 UNIQUE 인덱스가 아닌 경우(즉, 키 값 기반으로 단일 로우를 선택할 수 없는 경우)에 ref가 사용됩니다. 사용된 키가 소수의 로우만 매칭하는 경우 좋은 조인 타입입니다.

ref는 = 또는 <=> 연산자를 사용하여 비교되는 인덱스 컬럼에 사용할 수 있습니다. 다음 예제에서 MySQL은 _ref_table_을 처리하기 위해 ref 조인을 사용할 수 있습니다:

1SELECT * FROM ref_table WHERE key_column=expr;
2
3SELECT * FROM ref_table,other_table
4    WHERE ref_table.key_column=other_table.column;
5
6SELECT * FROM ref_table,other_table
7    WHERE ref_table.key_column_part1=other_table.column
8    AND ref_table.key_column_part2=1;

• fulltext

조인이 FULLTEXT 인덱스를 사용하여 수행됩니다.

• ref_or_null

이 조인 타입은 ref와 비슷하지만, 여기에 MySQL이 NULL 값을 가진 로우를 위한 추가 검색을 수행한다는 점이 더해집니다. 이 조인 타입 최적화는 서브쿼리를 해결할 때 가장 자주 사용됩니다. 다음 예에서 MySQL은 _ref_table_을 처리하기 위해 ref_or_null 조인을 사용할 수 있습니다:

1SELECT * FROM ref_table
2    WHERE key_column=expr OR key_column IS NULL;

Section 10.2.1.15, “IS NULL Optimization”를 참고하십시오.

• index_merge

이 조인 타입은 Index Merge 최적화가 사용되고 있음을 나타냅니다. 이 경우 output 로우의 key 컬럼에는 사용된 인덱스 목록이 포함되고, key_len에는 사용된 인덱스의 가장 긴 키 파트의 목록이 포함됩니다. 자세한 내용은 Section 10.2.1.3, “Index Merge Optimization”을 참조하십시오.

• unique_subquery

이 타입은 다음 형식의 일부 IN 서브쿼리에 대해 eq_ref를 대체합니다:

1value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery는 단순히 서브쿼리를 완전히 대체하여 효율성을 높이는 인덱스 lookup 함수입니다.

• index_subquery

이 조인 타입은 unique_subquery와 유사합니다. IN 서브쿼리를 대체하지만, 다음 형식의 서브쿼리에서 nonunique 인덱스에 대해 동작합니다:

1value IN (SELECT key_column FROM single_table WHERE some_expr)

• range

이 타입에서는 주어진 범위에 있는 로우만 인덱스를 사용하여 검색됩니다. output 로우의 key 컬럼은 사용된 인덱스를 나타냅니다. key_len은 사용된 가장 긴 키 파트를 포함합니다. 이 타입에서는 ref 컬럼이 NULL입니다.

range는 키 컬럼이 상수와 비교될 때 다음 연산자 중 어느 것이든 사용될 수 있습니다: =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, IN() 연산자:

1SELECT * FROM tbl_name
2    WHERE key_column = 10;
3
4SELECT * FROM tbl_name
5    WHERE key_column BETWEEN 10 and 20;
6
7SELECT * FROM tbl_name
8    WHERE key_column IN (10,20,30);
9
10SELECT * FROM tbl_name
11    WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

• index

index 조인 타입은 ALL과 같지만, 인덱스 트리가 스캔된다는 점이 다릅니다. 이는 두 가지 방식으로 발생합니다:

• 인덱스가 쿼리에 대한 커버링 인덱스이고, 테이블에서 요구되는 모든 데이터를 만족할 수 있는 경우, 인덱스 트리만 스캔됩니다.
  이 경우 Extra 컬럼은 Using index라고 표시합니다. 인덱스만을 사용하는 스캔은 일반적으로 ALL보다 빠른데, 인덱스의 크기가 일반적으로 테이블 데이터보다 작기 때문입니다.

• 풀 테이블 스캔이 수행되지만, 인덱스에서 읽은 내용을 사용하여 데이터 로우를 인덱스 순서대로 lookup합니다.
  이때 Extra 컬럼에는 Uses index가 나타나지 않습니다.

MySQL은 쿼리가 단일 인덱트에 속한 컬럼만 사용하는 경우 이 조인 타입을 사용할 수 있습니다.

• ALL

이 타입에서는 이전 테이블의 로우 조합 하나당 풀 테이블 스캔이 수행됩니다. 테이블이 const로 표시되지 않은 첫 번째 테이블인 경우 일반적으로 좋지 않으며, 다른 경우에는 대부분 매우 나쁩니다. 일반적으로, 상수 값이나 이전 테이블의 컬럼 값에 기반하여 테이블에서 로우를 검색할 수 있도록 인덱스를 추가하여 ALL을 피할 수 있습니다.

EXPLAIN Extra Information

EXPLAIN output의 Extra 컬럼은 MySQL이 쿼리를 어떻게 처리하는지에 대한 추가 정보를 포함합니다. 다음 목록은 이 컬럼에 나타날 수 있는 값과, JSON 형식의 output에서 어떤 프로퍼티가 Extra 값을 표시하는지를 설명합니다. 일부 값은 특정 프로퍼티에 표시되고, 나머지는 message 프로퍼티의 텍스트로 표시됩니다.

쿼리를 가능한 한 빠르게 만들고 싶다면, Extra 컬럼의 Using filesort 및 Using temporary 값을 주의 깊게 살펴보십시오. 또는 JSON 형식의 EXPLAIN output에서 using_filesort 및 using_temporary_table 프로퍼티가 true인지 확인하십시오.

• Backward index scan (JSON: backward_index_scan)

옵티마이저가 InnoDB 테이블에서 내림차순 인덱스를 사용할 수 있습니다. Using index와 함께 표시됩니다. 자세한 내용은 Section 10.3.13, “Descending Indexes”를 참조하십시오.

• Child of 'table' pushed join@1 (JSON: message 텍스트)

이 테이블은 NDB 커널로 push-down될 수 있는 조인에서 _table_의 child로 참조됩니다. pushed-down 조인이 활성화된 NDB Cluster에서만 적용됩니다. 자세한 내용과 예제는 ndb_join_pushdown 서버 시스템 변수에 대한 설명을 참조하십시오.

• const row not found (JSON 프로퍼티: const_row_not_found)

SELECT ... FROM tbl_name와 같은 쿼리에서 테이블이 비어 있는 경우입니다.

• Deleting all rows (JSON 프로퍼티: message)

DELETE의 경우, 일부 스토리지 엔진(예: MyISAM)은 테이블 로우 전체를 간단하고 빠르게 제거하는 핸들러 메서드를 지원합니다. 엔진이 이 최적화를 사용하는 경우 이 Extra 값이 표시됩니다.

• Distinct (JSON 프로퍼티: distinct)

MySQL은 distinct 값을 찾고 있으며, 현재 로우 조합에 대해 첫 번째 매칭 로우를 찾은 후에는 추가 로우 검색을 중단합니다.

• FirstMatch(tbl_name) (JSON 프로퍼티: first_match)

세미조인 FirstMatch 조인 shortcutting 전략이 _tbl_name_에 대해 사용됩니다.

• Full scan on NULL key (JSON 프로퍼티: message)

옵티마이저가 인덱스 lookup 액세스 메서드를 사용할 수 없을 때 fallback 전략으로, 서브쿼리 최적화에서 발생합니다.

• Impossible HAVING (JSON 프로퍼티: message)

HAVING 절이 항상 false이며 어떤 로우도 선택할 수 없습니다.

• Impossible WHERE (JSON 프로퍼티: message)

WHERE 절이 항상 false이며 어떤 로우도 선택할 수 없습니다.

• Impossible WHERE noticed after reading const tables (JSON 프로퍼티: message)

MySQL이 모든 const (및 system) 테이블을 읽은 후 WHERE 절이 항상 false임을 알게 된 경우입니다.

• LooseScan(m..n) (JSON 프로퍼티: message)

세미조인 LooseScan 전략이 사용됩니다. _m_과 _n_은 키 파트 번호입니다.

• No matching min/max row (JSON 프로퍼티: message)

SELECT MIN(...) FROM ... WHERE condition과 같은 쿼리에서 조건을 만족하는 로우가 없는 경우입니다.

• no matching row in const table (JSON 프로퍼티: message)

조인이 있는 쿼리에서 테이블이 비어 있거나, 유니크 인덱스 조건을 만족하는 로우가 없는 테이블이 있는 경우입니다.

• No matching rows after partition pruning (JSON 프로퍼티: message)

DELETE 또는 UPDATE에 대해, 옵티마이저가 파티션 프루닝 후 삭제하거나 업데이트할 대상이 없음을 발견한 경우입니다. 이는 SELECT statement에서 Impossible WHERE와 유사한 의미입니다.

• No tables used (JSON 프로퍼티: message)

쿼리에 FROM 절이 없거나 FROM DUAL 절이 있는 경우입니다.

INSERT 또는 REPLACE statement에 대해서는, EXPLAIN에 SELECT 부분이 없을 때 이 값이 표시됩니다. 예를 들어 EXPLAIN INSERT INTO t VALUES(10)은 EXPLAIN INSERT INTO t SELECT 10 FROM DUAL과 동일하므로 이 값을 표시합니다.

• Not exists (JSON 프로퍼티: message)

MySQL이 쿼리에서 LEFT JOIN 최적화를 수행했으며, 이전 로우 조합에 대해 LEFT JOIN 조건에 일치하는 로우를 찾은 후 이 테이블에서 더 많은 로우를 검사하지 않는 경우입니다. 다음과 같은 유형의 쿼리에서 이런 최적화를 할 수 있습니다:

1SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id
2    WHERE t2.id IS NULL;

t2.id가 NOT NULL로 정의되어 있다고 가정합니다. 이 경우 MySQL은 t1을 스캔하고 t1.id 값을 사용하여 t2의 로우를 lookup합니다. MySQL이 t2에서 매칭 로우를 찾으면 t2.id가 절대 NULL이 될 수 없다는 것을 알게 되며, 같은 id 값을 가진 t2의 나머지 로우는 스캔하지 않습니다. 즉, t1의 각 로우에 대해 MySQL은 t2에서 매칭 로우의 실제 수와 관계없이 t2에서 lookup을 한 번만 수행하면 됩니다.

이는 또한 NOT IN (subquery) 또는 NOT EXISTS (subquery) 형태의 WHERE 조건이 내부적으로 antijoin으로 변환되었음을 나타낼 수도 있습니다. 이는 서브쿼리를 제거하고, 그 테이블을 top-level 쿼리의 플랜으로 가져와 비용 플랜을 개선합니다. 세미조인과 antijoin을 병합함으로써 옵티마이저는 실행 플랜에서 테이블의 순서를 보다 자유롭게 재조정할 수 있으며, 경우에 따라 더 빠른 플랜을 생성할 수 있습니다.

특정 쿼리에 대해 antijoin 변환이 수행되었는지는 EXPLAIN 실행 후 SHOW WARNINGS의 Message 컬럼을 확인하거나, EXPLAIN FORMAT=TREE output에서 확인할 수 있습니다.

참고

antijoin은 세미조인 table_a JOIN table_b ON condition의 여집합입니다. antijoin은 _table_b_에서 _condition_과 일치하는 로우가 없는 모든 _table_a_의 로우를 반환합니다.

• Plan is not ready yet (JSON 프로퍼티: none)

이 값은 EXPLAIN FOR CONNECTION을 사용할 때 지정된 커넥션에서 실행 중인 statement에 대해 옵티마이저가 실행 플랜 생성을 완료하지 않았을 때 발생합니다. 실행 플랜 output이 여러 줄로 구성된 경우, 옵티마이저가 전체 실행 플랜을 결정하는 진행 상황에 따라 이 줄들 중 일부 또는 전부가 이 Extra 값을 가질 수 있습니다.

• Range checked for each record (index map: N) (JSON 프로퍼티: message)

MySQL은 사용할 만한 좋은 인덱스를 찾지 못했지만, 이전 테이블의 컬럼 값이 알려진 후에는 일부 인덱스를 사용할 수 있을 것이라고 판단했습니다. 이전 테이블의 로우 조합마다 MySQL은 로우를 검색하기 위해 range 또는 index_merge 액세스 메서드를 사용할 수 있는지 확인합니다. 이 방법은 매우 빠른 편은 아니지만, 인덱스 없이 조인을 수행하는 것보다는 빠릅니다. 적용 가능성 기준은 Section 10.2.1.2, “Range Optimization” 및 Section 10.2.1.3, “Index Merge Optimization”에 기술된 것과 같지만, 이전 테이블에 대해 모든 컬럼 값이 알려져 있고 상수로 간주된다는 점이 예외입니다.

인덱스는 테이블에 대해 SHOW INDEX가 표시하는 순서대로 1부터 번호가 매겨집니다. 인덱스 맵 값 _N_은 어떤 인덱스가 후보인지 나타내는 비트마스크 값입니다. 예를 들어 값이 0x19(2진수 11001)인 경우 인덱스 1, 4, 5가 고려됩니다.

• Recursive (JSON 프로퍼티: recursive)

이 값은 로우가 재귀 common table expression의 재귀 SELECT 부분에 해당함을 나타냅니다. Section 15.2.20, “WITH (Common Table Expressions)”를 참조하십시오.

• Rematerialize (JSON 프로퍼티: rematerialize)

Rematerialize (X,...)는 테이블 T에 대한 EXPLAIN 로우에 표시되며, 여기서 X는 T의 새 로우가 읽힐 때 rematerialization이 트리거되는 임의의 lateral 파생 테이블입니다. 예를 들어:

1SELECT
2    ...
3FROM
4    t,
5    LATERAL (derived table that refers to t) AS dt
6...

T의 새로운 로우가 top-level 쿼리에 의해 처리될 때마다, t에 대한 파생 테이블의 내용이 최신 상태가 되도록 다시 머티리얼라이즈됩니다.

• Scanned N databases (JSON 프로퍼티: message)

이 값은 Section 10.2.3, “Optimizing INFORMATION_SCHEMA Queries”에 설명된 대로, INFORMATION_SCHEMA 테이블에 대한 쿼리를 처리할 때 서버가 얼마나 많은 디렉터리 스캔을 수행하는지를 나타냅니다. N 값은 0, 1, 또는 all이 될 수 있습니다.

• Select tables optimized away (JSON 프로퍼티: message)

옵티마이저가 1) 최대 한 로우만 반환되어야 하고, 2) 이 로우를 생성하기 위해 디터미니스틱한 로우 집합을 읽어야 한다고 판단한 경우입니다. 읽어야 할 로우가 최적화 단계에서 읽힐 수 있는 경우(예: 인덱스 로우를 읽음으로써), 쿼리 실행 동안 테이블을 읽을 필요가 없습니다.

첫 번째 조건은 쿼리가 암시적으로 그룹된 경우(집계 함수는 있으나 GROUP BY 절이 없는 경우)에 만족됩니다. 두 번째 조건은 사용된 인덱스마다 로우 lookup이 하나씩 수행될 때 만족됩니다. 읽을 인덱스 수는 읽을 로우 수를 결정합니다.

다음과 같은 암시적으로 그룹된 쿼리를 생각해 보겠습니다:

1SELECT MIN(c1), MIN(c2) FROM t1;

MIN(c1)이 인덱스 로우 하나를 읽어 얻을 수 있고, MIN(c2)는 다른 인덱스에서 로우 하나를 읽어 얻을 수 있다고 가정합시다. 즉, 각 컬럼 c1 및 c2에 대해, 컬럼이 해당 인덱스의 첫 번째 컬럼인 인덱스가 존재합니다. 이 경우 두 개의 디터미니스틱한 로우를 읽어 반환되는 하나의 로우를 생성합니다.

읽어야 할 로우가 디터미니스틱하지 않은 경우에는 이 Extra 값이 발생하지 않습니다. 다음 쿼리를 생각해 보십시오:

1SELECT MIN(c2) FROM t1 WHERE c1 <= 10;

(c1, c2)가 커버링 인덱스라고 가정합시다. 이 인덱스를 사용할 때 c1 <= 10인 모든 로우를 스캔하여 최소 c2 값을 찾아야 합니다. 반면, 다음 쿼리를 고려해 보십시오:

1SELECT MIN(c2) FROM t1 WHERE c1 = 10;

이 경우 c1 = 10인 첫 번째 인덱스 로우에 최소 c2 값이 포함됩니다. 반환되는 로우를 생성하기 위해 로우 하나만 읽으면 됩니다.

테이블당 정확한 로우 카운트를 유지하는 스토리지 엔진(예: MyISAM, InnoDB는 아님)의 경우, WHERE 절이 없거나 항상 true이며 GROUP BY 절이 없는 COUNT(*) 쿼리에서 이 Extra 값이 발생할 수 있습니다. (이는 스토리지 엔진이 디터미니스틱한 로우 수를 읽을 수 있는지 여부에 영향을 미치는 암시적으로 그룹된 쿼리의 한 예입니다.)

• Skip_open_table, Open_frm_only, Open_full_table (JSON 프로퍼티: message)

이 값들은 INFORMATION_SCHEMA 테이블에 대한 쿼리에 적용되는 파일 열기 최적화를 나타냅니다.

• Skip_open_table: 테이블 파일을 열 필요가 없습니다. 정보는 이미 데이터 딕셔너리에 있습니다.

• Open_frm_only: 테이블 정보에 대해 데이터 딕셔너리만 읽으면 됩니다.

• Open_full_table: 최적화되지 않은 정보 lookup입니다. 테이블 정보는 데이터 딕셔너리 및 테이블 파일을 읽어야 합니다.

• Start temporary, End temporary (JSON 프로퍼티: message)

이는 세미조인 Duplicate Weedout 전략을 위해 임시 테이블이 사용됨을 나타냅니다.

• unique row not found (JSON 프로퍼티: message)

SELECT ... FROM tbl_name와 같은 쿼리에서, 테이블의 UNIQUE 인덱스 또는 PRIMARY KEY 조건을 만족하는 로우가 없는 경우입니다.

• Using filesort (JSON 프로퍼티: using_filesort)

MySQL이 로우를 정렬된 순서로 가져오는 방법을 찾기 위해 추가 패스를 수행해야 합니다. 정렬은 조인 타입에 따라 모든 로우를 순회하면서, WHERE 절과 일치하는 모든 로우에 대해 정렬 키와 로우 포인터를 저장함으로써 수행됩니다. 그런 다음 키를 정렬하고 로우를 정렬된 순서로 가져옵니다. Section 10.2.1.16, “ORDER BY Optimization”을 참조하십시오.

• Using index (JSON 프로퍼티: using_index)

컬럼 정보가 실제 로우를 읽기 위한 추가 seek 없이, 인덱스 트리에 있는 정보만으로 테이블에서 가져옵니다. 쿼리가 단일 인덱스에 속하는 컬럼만 사용하는 경우 이 전략을 사용할 수 있습니다.

user-defined 클러스터드 인덱스가 있는 InnoDB 테이블의 경우, Extra 컬럼에 Using index가 없더라도 그 인덱스를 사용할 수 있습니다. 이는 type이 index이고 key가 PRIMARY인 경우입니다.

사용된 커버링 인덱스에 대한 정보는 EXPLAIN FORMAT=TRADITIONAL과 EXPLAIN FORMAT=JSON에서 표시됩니다. 또한 EXPLAIN FORMAT=TREE에서도 표시됩니다.

• Using index condition (JSON 프로퍼티: using_index_condition)

테이블은 인덱스 튜플에 접근하고, 풀 테이블 로우를 읽을지 여부를 먼저 테스트한 다음 읽습니다. 이렇게 하면 풀 테이블 로우를 읽는 작업을 필요할 때까지(“push down”) 미룰 수 있도록 인덱스 정보를 사용합니다. Section 10.2.1.6, “Index Condition Pushdown Optimization”을 참조하십시오.

• Using index for group-by (JSON 프로퍼티: using_index_for_group_by)

Using index 테이블 액세스 메서드와 유사하게, Using index for group-by는 MySQL이 추가 디스크 액세스 없이 GROUP BY 또는 DISTINCT 쿼리의 모든 컬럼을 가져올 수 있는 인덱스를 찾았음을 나타냅니다. 또한 이 인덱스는 각 그룹에 대해 소수의 인덱스 엔트리만 읽히도록 가장 효율적인 방식으로 사용됩니다. 자세한 내용은 Section 10.2.1.17, “GROUP BY Optimization”을 참조하십시오.

• Using index for skip scan (JSON 프로퍼티: using_index_for_skip_scan)

Skip Scan 액세스 메서드가 사용됨을 나타냅니다. Skip Scan Range Access Method를 참조하십시오.

• Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access), Using join buffer (hash join) (JSON 프로퍼티: using_join_buffer)

이전 조인에서 나온 테이블이 조인 버퍼에 일부씩 읽혀 들어간 다음, 버퍼에 저장된 로우가 이 테이블과 조인을 수행하는 데 사용됩니다. (Block Nested Loop)는 Block Nested-Loop 알고리즘 사용을, (Batched Key Access)는 Batched Key Access 알고리즘 사용을, (hash join)은 해시 조인 사용을 의미합니다. 즉, EXPLAIN output에서 Using join buffer가 나타나는 라인은, 바로 이전 라인에 있는 테이블의 키가 버퍼에 저장되고, 이 버퍼에서 매칭 로우가 해당 테이블에서 배치 단위로 fetch됨을 의미합니다.

JSON 형식 output에서 using_join_buffer의 값은 항상 Block Nested Loop, Batched Key Access, hash join 중 하나입니다.

해시 조인에 대한 자세한 내용은 Section 10.2.1.4, “Hash Join Optimization”을 참조하십시오.

Batched Key Access 알고리즘에 대해서는 Batched Key Access Joins를 참조하십시오.

• Using MRR (JSON 프로퍼티: message)

테이블이 Multi-Range Read 최적화 전략을 사용하여 읽힙니다. Section 10.2.1.11, “Multi-Range Read Optimization”을 참조하십시오.

• Using sort_union(...), Using union(...), Using intersect(...) (JSON 프로퍼티: message)

이 값들은 index_merge 조인 타입에 대해 인덱스 스캔이 어떻게 merge되는지에 대한 알고리즘을 나타냅니다. Section 10.2.1.3, “Index Merge Optimization”을 참조하십시오.

• Using temporary (JSON 프로퍼티: using_temporary_table)

쿼리를 해결하기 위해 MySQL이 결과를 보관할 임시 테이블을 생성해야 합니다. 이는 일반적으로 GROUP BY와 ORDER BY 절이 서로 다른 컬럼을 나열할 때 발생합니다.

• Using where (JSON 프로퍼티: attached_condition)

WHERE 절이 다음 테이블과 비교할 로우 또는 클라이언트로 전송할 로우를 제한하는 데 사용됩니다. 테이블 조인 타입이 ALL 또는 index이면서 Extra 값이 Using where가 아니라면, 테이블의 모든 로우를 가져오거나 검사하는 것이 의도된 것이 아니라면 쿼리에 문제가 있을 수 있습니다.

Using where에는 JSON 형식 output에서 직접 대응되는 항목이 없습니다. attached_condition 프로퍼티에 사용된 WHERE 조건이 포함됩니다.

• Using where with pushed condition (JSON 프로퍼티: message)

이 항목은 NDB 테이블에만 적용됩니다. 이는 NDB Cluster가 직접적인 nonindexed 컬럼과 상수 간 비교의 효율성을 개선하기 위해 Condition Pushdown 최적화를 사용하고 있음을 의미합니다. 이 경우 조건이 클러스터의 데이터 노드로 “pushed down”되며, 모든 데이터 노드에서 동시에 평가됩니다. 이로 인해 nonmatching 로우를 네트워크를 통해 전송할 필요가 없어지며, Condition Pushdown이 사용되지 않는 경우보다 이러한 쿼리가 5~10배까지 빨라질 수 있습니다. 자세한 내용은 Section 10.2.1.5, “Engine Condition Pushdown Optimization”을 참조하십시오.

• Zero limit (JSON 프로퍼티: message)

쿼리에 LIMIT 0 절이 있으며 어떤 로우도 선택될 수 없는 경우입니다.

EXPLAIN Output Interpretation

EXPLAIN output의 rows 컬럼 값의 곱을 계산하면 조인이 얼마나 좋은지에 대한 대략적인 지표를 얻을 수 있습니다. 이 값은 쿼리를 실행하기 위해 MySQL이 얼마나 많은 로우를 검사해야 하는지 대략 알려줍니다. max_join_size 시스템 변수로 쿼리를 제한하는 경우, 이 로우 곱은 어떤 multi-table SELECT statement를 실행하고 어떤 것을 중단할지 결정하는 데도 사용됩니다. Section 7.1.1, “Configuring the Server”를 참조하십시오.

다음 예제는 EXPLAIN에서 제공하는 정보를 기반으로 multi-table 조인이 점진적으로 어떻게 최적화될 수 있는지 보여줍니다.

다음과 같은 SELECT statement가 있고, 이를 EXPLAIN으로 분석하려 한다고 가정합니다:

1EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
2               tt.ProjectReference, tt.EstimatedShipDate,
3               tt.ActualShipDate, tt.ClientID,
4               tt.ServiceCodes, tt.RepetitiveID,
5               tt.CurrentProcess, tt.CurrentDPPerson,
6               tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
7               et_1.COUNTRY, do.CUSTNAME
8        FROM tt, et, et AS et_1, do
9        WHERE tt.SubmitTime IS NULL
10          AND tt.ActualPC = et.EMPLOYID
11          AND tt.AssignedPC = et_1.EMPLOYID
12          AND tt.ClientID = do.CUSTNMBR;

이 예제에서 다음과 같이 가정합니다:

• 비교되는 컬럼이 다음과 같이 선언되어 있습니다.

Table	Column	Data Type
tt	ActualPC	CHAR(10)
tt	AssignedPC	CHAR(10)
tt	ClientID	CHAR(10)
et	EMPLOYID	CHAR(15)
do	CUSTNMBR	CHAR(15)

• 테이블에는 다음과 같은 인덱스가 있습니다.

Table	Index
tt	ActualPC
tt	AssignedPC
tt	ClientID
et	EMPLOYID (primary key)
do	CUSTNMBR (primary key)

• tt.ActualPC 값은 균등하게 분포되어 있지 않습니다.

초기에는, 최적화가 전혀 수행되지 않은 상태에서 EXPLAIN statement는 다음과 같은 정보를 생성합니다:

1table type possible_keys key  key_len ref  rows  Extra
2et    ALL  PRIMARY       NULL NULL    NULL 74
3do    ALL  PRIMARY       NULL NULL    NULL 2135
4et_1  ALL  PRIMARY       NULL NULL    NULL 74
5tt    ALL  AssignedPC,   NULL NULL    NULL 3872
6           ClientID,
7           ActualPC
8      Range checked for each record (index map: 0x23)

각 테이블에 대해 type이 ALL이므로, MySQL이 모든 테이블의 Cartesian product, 즉 가능한 모든 로우 조합을 생성하고 있음을 의미합니다. 이는 각 테이블의 로우 수의 곱만큼의 로우를 검사해야 하므로 시간이 상당히 오래 걸립니다. 이 경우 곱은 74 × 2135 × 74 × 3872 = 45,268,558,720 로우입니다.

테이블이 더 컸다면 얼마나 오래 걸릴지 짐작할 수 있을 것입니다.

MySQL은 컬럼이 같은 타입과 사이즈로 선언된 경우 인덱스를 더 효율적으로 사용할 수 있습니다. 이 문맥에서 VARCHAR와 CHAR는 같은 사이즈로 선언된 경우 동일하다고 간주됩니다. tt.ActualPC는 CHAR(10)으로 선언되어 있고 et.EMPLOYID는 CHAR(15)이므로 길이가 맞지 않습니다.

컬럼 길이의 불일치를 해결하려면, ALTER TABLE을 사용하여 ActualPC 길이를 10에서 15 문자로 늘립니다:

1mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

이제 tt.ActualPC와 et.EMPLOYID는 모두 VARCHAR(15)입니다. 다시 EXPLAIN을 실행하면 다음과 같은 결과가 생성됩니다:

1table type   possible_keys key     key_len ref         rows    Extra
2tt    ALL    AssignedPC,   NULL    NULL    NULL        3872    Using
3             ClientID,                                         where
4             ActualPC
5do    ALL    PRIMARY       NULL    NULL    NULL        2135
6      Range checked for each record (index map: 0x1)
7et_1  ALL    PRIMARY       NULL    NULL    NULL        74
8      Range checked for each record (index map: 0x1)
9et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC 1

완벽하지는 않지만 훨씬 좋아졌습니다. rows 값의 곱은 74배 감소했습니다. 이 버전은 몇 초 안에 실행됩니다.

두 번째 변경으로 tt.AssignedPC = et_1.EMPLOYID 및 tt.ClientID = do.CUSTNMBR 비교에 대한 컬럼 길이 불일치를 제거할 수 있습니다:

1mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
2                      MODIFY ClientID   VARCHAR(15);

이후 EXPLAIN은 다음과 같은 output을 생성합니다:

1table type   possible_keys key      key_len ref           rows Extra
2et    ALL    PRIMARY       NULL     NULL    NULL          74
3tt    ref    AssignedPC,   ActualPC 15      et.EMPLOYID   52   Using
4             ClientID,                                         where
5             ActualPC
6et_1  eq_ref PRIMARY       PRIMARY  15      tt.AssignedPC 1
7do    eq_ref PRIMARY       PRIMARY  15      tt.ClientID   1

이 시점에서 쿼리는 거의 최적으로 최적화되었습니다. 남은 문제는 기본적으로 MySQL이 tt 테이블의 tt.ActualPC 컬럼 값이 균일하게 분포되어 있다고 가정한다는 점이며, 실제로는 그렇지 않다는 것입니다.

다행히도 MySQL에 키 분포를 분석하도록 지시하는 것은 간단합니다:

1mysql> ANALYZE TABLE tt;

추가 인덱스 정보가 있으면 조인이 완벽해지고, EXPLAIN은 다음과 같은 결과를 생성합니다:

1table type   possible_keys key     key_len ref           rows Extra
2tt    ALL    AssignedPC    NULL    NULL    NULL          3872 Using
3             ClientID,                                        where
4             ActualPC
5et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC   1
6et_1  eq_ref PRIMARY       PRIMARY 15      tt.AssignedPC 1
7do    eq_ref PRIMARY       PRIMARY 15      tt.ClientID   1

EXPLAIN output의 rows 컬럼 값은 MySQL 조인 옵티마이저에 의한 추정값입니다. rows 곱을 쿼리가 실제로 반환하는 로우 수와 비교하여 값이 실제와 얼마나 가까운지 확인하십시오. 숫자가 매우 다르다면, SELECT statement에 STRAIGHT_JOIN을 사용하고 FROM 절에서 테이블을 다른 순서로 나열함으로써 더 나은 성능을 얻을 수 있습니다. (그러나 STRAIGHT_JOIN은 세미조인 변환을 비활성화하여 인덱스 사용을 방해할 수도 있습니다. Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations을 참조하십시오.)

일부 경우에는 서브쿼리와 함께 EXPLAIN SELECT을 사용할 때 데이터를 수정하는 statement를 실행하는 것이 가능합니다. 자세한 내용은 Section 15.2.15.8, “Derived Tables”를 참조하십시오.