[MySQL][Optimizer] Query 실행 계획 이해

■ EXPLAIN으로 쿼리 최적화

EXPLAIN 문은 MySQL이 명령문을 실행하는 방법에 대한 정보를 제공합니다.

+ EXPLAIN은 SELECT, DELETE, INSERT, REPLACE 및 UPDATE문과 함께 작동합니다.

+ EXPLAIN을 설명문과 함께 사용하면 MySQL은 명령문 실행 계획에 대한 정보를 옵티마이저로부터 표시합니다. 즉, MySQL은 테이블을 조인하는 방법과 순서에 대한 정보를 포함하여 명령문을 처리하는 방법을 설명합니다. EXPLAIN을 사용하여 실행 계획 정보를 얻는 방법에 대한 내용은 아래에서 설명합니다.

+ EXPLAIN을 설명 가능한 명령문이 아닌 FOR CONNECTION connection_id와 함께 사용하면 명명된 연결에서 실행되는 명령문의 실행 계획이 표시됩니다.

+ SELECT문의 경우 EXPLAIN은 SHOW WARNINGS를 사용하여 표시 할 수 있는 추가 실행 계획 정보를 생성합니다.

+ EXPLAIN은 파티션 테이블과 관련된 쿼리를 검사하는 데 유용합니다.

+ FORMAT 옵션을 사용하여 출력 형식을 선택할 수 있습니다. TRADITIONAL은 출력을 테이블 형식으로 표시합니다. FORMAT옵션이 없는 경우 이것이 기본값입니다. JSON 형식은 정보를 JSON 형식으로 표시합니다.

 

EXPLAIN을 사용하면 명령문이 원하는 행을 더 빨리 찾을 수 있도록 테이블에 인덱스를 추가해야하는 위치를 확인할 수 있습니다. EXPLAIN을 사용하여 옵티마이저가 테이블을 최적 순서로 조인하는지 여부를 확인할 수도 있습니다. SELECT 문에서 테이블의 이름이 지정된 순서에 해당하는 조인 순서를 사용하도록 옵티 마이저에 힌트를 주려면 SELECT가 아닌 SELECT STRAIGHT_JOIN으로 명령문을 시작합니다. 그러나 STRAIGHT_JOIN은 세미 조인 변환을 비활성화하므로 인덱스가 사용되지 않을 수 있습니다.

 

옵티마이저 추적은 때때로 EXPLAIN의 정보와 보완적인 정보를 제공할 수 있습니다. 그러나 옵티 마이저 추적 형식 및 내용은 버전마다 변경될 수 있습니다.

 

인덱스를 사용해야 한다고 생각할 때 사용하지 않는 인덱스에 문제가 있는 경우 ANALYZE TABLE을 실행하여 키 카디널리티와 같은 테이블 통계를 업데이트하여 옵티마이저의 선택에 영향을 줄 수 있습니다.

 

노트

EXPLAIN을 사용하여 테이블의 열에 대한 정보를 얻을 수도 있습니다. EXPLAIN tbl_name은 DESCRIBE tbl_name 및 SHOW COLUMNS FROM tbl_name과 동일한 명령입니다.

 

■ EXPLAIN 출력 형식

EXPLAIN문은 MySQL이 명령문을 실행하는 방법에 대한 정보를 제공합니다. EXPLAIN은 SELECT, DELETE, INSERT, REPLACE 및 UPDATE 문과 함께 작동합니다.

 

EXPLAIN은 SELECT문에 사용된 각 테이블에 대한 정보 행을 리턴합니다. 명령문을 처리하는 동안 MySQL이 읽은 순서대로 출력에 테이블을 나열합니다. MySQL은 중첩 루프 조인 방법을 사용하여 모든 조인을 해결합니다. 즉, MySQL은 첫 번째 테이블에서 행을 읽은 다음 두 번째 테이블, 세 번째 테이블 등에서 일치하는 행을 찾습니다. 모든 테이블이 처리되면 MySQL은 더 일치하는 행이 있는 테이블이 발견될 때까지 테이블 목록을 통해 선택한 열과 역추적을 출력합니다. 이 테이블에서 다음 행을 읽고 프로세스는 다음 테이블에서 계속됩니다.

 

EXPLAIN 출력에는 파티션 정보가 포함됩니다. 또한 SELECT문의 경우 EXPLAIN은 EXPLAIN에 따라 SHOW WARNINGS와 함께 표시될 수 있는 확장 정보를 생성합니다.

 

노트

이전 MySQL릴리스에서는 파티션 및 확장 정보가 EXPLAIN PARTITIONS 및 EXPLAIN EXTENDED를 사용하여 생성되었습니다. 이러한 구문은 여전히 ​​이전 버전과의 호환성을 위해 인식되지만 파티션 및 확장 출력은 이제 기본적으로 사용 가능하므로 PARTITIONS 및 EXTENDED 키워드는 불필요하고 더 이상 사용되지 않습니다. 이들을 사용하면 경고가 발생하며 향후 MySQL 릴리스에서는 EXPLAIN 구문에서 제거됩니다.

더 이상 사용되지 않는 PARTITIONS 및 EXTENDED 키워드를 동일한 EXPLAIN 문에서 함께 사용할 수 없습니다. 또한이 키워드 중 어느것도 FORMAT 옵션과 함께 사용할 수 없습니다.

 

노트

MySQL Workbench에는 EXPLAIN 출력의 시각적 표현을 제공하는 Visual Explain 기능이 있습니다. MySQL 홈페이지에 [학습서 : Explain]을 사용하여 쿼리 성능 개선을 참조하면 더 좋은 내용이 있습니다.

 

+ EXPLAIN Output Columns

+ EXPLAIN Join Types

+ EXPLAIN Extra Information

+ EXPLAIN Output Interpretation

+ EXPLAIN 출력 열

+ EXPLAIN 조인 유형

+ 추가 정보 설명

+ EXPLAIN 출력 해석

 

▶︎ EXPLAIN 출력 컬럼

EXPLAIN의 각 출력 행은 하나의 테이블에 대한 정보를 제공합니다. 각 행에는 아래 표 8.1.“EXPLAIN 출력 열”에 요약되어 있고 표 다음에 더 자세히 설명된 값이 있습니다. 컬럼 이름은 테이블의 첫 번째 열에 표시됩니다. 두 번째 열은 FORMAT = JSON이 사용될때 출력에 표시되는 동등한 특성 이름을 제공합니다.

 

표 8.1 EXPLAIN 출력 열

Column	JSON Name	Meaning
id	select_id	The SELECT identifier
select_type	None	The SELECT type
table	table_name	The table for the output row
partitions	partitions	The matching partitions
type	access_type	The join type
possible_keys	possible_keys	The possible indexes to choose
key	key	The index actually chosen
key_len	key_length	The length of the chosen key
ref	ref	The columns compared to the index
rows	rows	Estimate of rows to be examined
filtered	filtered	Percentage of rows filtered by table condition
Extra	None	Additional information

 

 

노트

NULL 인 JSON 특성은 JSON 형식의 EXPLAIN 출력에 표시되지 않습니다.

 

+ id (JSON 이름 : select_id)

SELECT 식별자 쿼리 내에서 SELECT의 일련 번호입니다. 행이 다른 행의 통합 결과를 나타내는 경우 값은 NULL 일 수 있습니다. 이 경우 테이블 열에는 <unionM, N>과 같은 값이 표시되어 행이 id 값이 M 및 N 인 행의 합집합을 나타냅니다.

 

+ select_type (JSON 이름 : 없음)

SELECT 유형으로, 다음 표에 표시된 유형 중 하나 일 수 있습니다. JSON 형식의 EXPLAIN은 SIMPLE 또는 PRIMARY가 아닌 경우 SELECT 유형을 query_block의 특성으로 표시합니다. JSON 이름 (해당되는 경우)도 표에 표시되어 있습니다.

 

select_type Value	JSON Name	Meaning
SIMPLE	None	Simple SELECT (not using UNION or subqueries)
PRIMARY	None	Outermost SELECT
UNION	None	Second or later SELECT statement in a UNION
DEPENDENT UNION	dependent (true)	Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT	union_result	Result of a UNION.
SUBQUERY	None	First SELECT in subquery
DEPENDENT SUBQUERY	dependent (true)	First SELECT in subquery, dependent on outer query
DERIVED	None	Derived table
MATERIALIZED	materialized_from_subquery	Materialized subquery
UNCACHEABLE SUBQUERY	cacheable (false)	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION	cacheable (false)	The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

 

 

 

UNCACHEABLE UNION cacheable (false) 캐시 할 수없는 서브 쿼리에 속하는 UNION에서 두 번째 또는 그 이후의 선택 (UNCACHEABLE SUBQUERY 참조)

DEPENDENT는 일반적으로 상관 된 하위 쿼리 사용을 나타냅니다. 13.2.10.7 절.“관련된 하위 쿼리”를 참조하십시오.

 

종속 서브 쿼리(DEPENDENT SUBQUERY) 평가는 캐시 불가능(UNCACHEABLE SUBQUERY) 서브 쿼리 평가와 다릅니다. DEPENDENT SUBQUERY의 경우 외부 쿼리에서 변수의 서로 다른 값 집합마다 하위 쿼리가 한번만 다시 평가됩니다. UNCACHEABLE SUBQUERY의 경우 외부 컨텍스트의 각 행에 대해 하위 쿼리가 재평가됩니다.

 

하위 쿼리의 캐시 가능성은 쿼리 캐시에서 쿼리 결과를 캐싱하는 것과 다릅니다. 하위 쿼리 캐싱은 쿼리 실행 중에 발생하지만 쿼리 캐시는 쿼리 실행이 완료된 후에만 ​​결과를 저장하는 데 사용됩니다.

 

EXPLAIN과 함께 FORMAT=JSON을 지정하면 출력에 select_type과 직접적으로 동일한 단일 특성이 없습니다. query_block속성은 주어진 SELECT에 해당합니다. 방금 표시된 대부분의 SELECT 하위 쿼리 유형과 동등한 속성을 사용할 수 있으며 (예 : MATERIALIZED의 materialized_from_subquery) 적절한 경우 표시됩니다. SIMPLE 또는 PRIMARY에 해당하는 JSON이 없습니다.

 

비 SELECT 문에 대한 select_type 값은 영향을 받는 테이블에 대한 명령문 유형을 표시합니다. 예를 들어, select_type은 DELETE 문에 대해 DELETE입니다.

 

+ table (JSON 이름 : table_name)

출력 행이 참조하는 테이블의 이름입니다. 다음 값 중 하나 일 수도 있습니다.

-<unionM, N> : 행은 id 값이 M 및 N 인 행의 합집합을 나타냅니다.

-<derivedN> : 행은 id 값이 N 인 행에 대한 파생 테이블 결과를 나타냅니다. 파생 테이블은 예를 들어 FROM 절의 하위 쿼리에서 생성 될 수 있습니다.

-<subqueryN> : 행은 id 값이 N 인 행에 대한 구체화 된 하위 쿼리의 결과를 나타냅니다. 8.2.2.2 절.“구체화로 하위 쿼리 최적화”를 참조하십시오.

 

+ partitions (JSON 이름 : 파티션)

쿼리가 레코드를 일치시키는 파티션입니다. 파티션되지 않은 테이블의 경우 값은 NULL입니다.

 

+ type (JSON 이름 : access_type)

조인 유형 다른 유형에 대한 설명은 EXPLAIN 조인 유형을 참조하십시오.

 

+ possible_keys (JSON 이름 : possible_keys)

possible_keys 열은 MySQL이이 테이블에서 행을 찾기 위해 선택할 수있는 인덱스를 나타냅니다. 이 열은 EXPLAIN의 출력에 표시되는 테이블 순서와 완전히 독립적입니다. 이는 possible_keys의 일부 키가 생성 된 테이블 순서로 실제로 사용하지 못할 수 있음을 의미합니다.

 

이 열이 NULL (또는 JSON 형식 출력에서 ​​정의되지 않은 경우)이면 관련 인덱스가 없습니다. 이 경우 WHERE 절을 검사하여 인덱싱에 적합한 일부 컬럼을 참조하는지 확인하여 쿼리 성능을 향상시킬 수 있습니다. 그렇다면 알맞은 인덱스를 작성하고 EXPLAIN을 사용하여 query를 다시 확인합니다.

 

테이블에 어떤 인덱스가 있는지 보려면 SHOW INDEX FROM tbl_name을 사용합니다.

 

+ key (JSON 이름 : 키)

key 컬럼은 MySQL이 실제로 사용하기로 결정한 key(index)를 나타냅니다. MySQL이 possible_keys 인덱스 중 하나를 사용하여 행을 찾으려면 해당 인덱스가 key 값으로 나열됩니다.

 

key가 possible_keys 값에 없는 인덱스의 이름을 지정할 수 있습니다. possible_keys 인덱스 중 어느 것도 행을 찾는데 적합하지 않지만 쿼리에 의해 선택된 모든 열이 다른 인덱스의 열인 경우 발생할 수 있습니다. 즉, 명명된 인덱스는 선택된 열을 다루므로 검색할 행을 결정하는 데 사용되지는 않지만 인덱스 스캔은 데이터 행 스캔보다 효율적입니다.

 

InnoDB의 경우 InnoDB는 기본키 값을 각 보조 인덱스와 함께 저장하기 때문에 쿼리에서 프리머리키를 선택하더라도 보조 인덱스가 선택된 컬럼을 포함할 수 있습니다. key가 NULL이면 MySQL은 쿼리를 보다 효율적으로 실행하는데 사용할 인덱스를 찾지 못했음을 뜻합니다.

 

MySQL이 possible_keys컬럼에 나열된 인덱스를 사용하거나 무시하도록하려면 쿼리에 FORCE INDEX, USE INDEX 또는 IGNORE INDEX를 사용합니다.

 

MyISAM테이블의 경우 ANALYZE TABLE을 실행하면 옵티마이저가 더 나은 인덱스를 선택할 수 있습니다. MyISAM 테이블의 경우 myisamchk --analyze도 동일합니다.

 

+ key_len (JSON 이름 : key_length)

key_len컬럼은 MySQL이 사용하기로 결정한 키의 길이를 나타냅니다. key_len의 값을 사용하면 MySQL이 실제로 사용하는 다중 파트(multiple-part)키의 파트가 몇개인지 확인할 수 있습니다. 키 열에 NULL이 있으면 key_len 열에 NULL도 표시됩니다.

 

키 저장 형식으로 인해 키 길이는 NOT NULL열보다 NULL일 수 있는 컬럼의 길이가 한 개 더 깁니다.

 

+ ref (JSON 이름 : ref)

ref 컬럼은 테이블에서 행을 선택하기 위해 키 열에 명명된 인덱스와 비교할 컬럼 또는 상수를 보여줍니다.

 

값이 func이면 사용된 값이 일부 기능의 결과입니다. 어떤 기능을 보려면 EXPLAIN 뒤에 SHOW WARNINGS를 사용하여 확장된 EXPLAIN 출력을 확인합니다. 함수는 실제로 산술 연산자와 같은 연산자 일 수 있습니다.

 

+  rows (JSON 이름 : rows)

rows 열은 MySQL이 쿼리를 실행하기 위해 검사해야 한다고 생각하는 행 수를 나타냅니다.

 

InnoDB 테이블의 경우 이 숫자는 추정치이며 항상 정확한 것은 아닙니다.

 

+ filtered (JSON 이름 : filtered)

필터링된 열은 테이블 조건에 의해 필터링될 테이블 행의 예상 백분율을 나타냅니다. 최대 값은 100이며 이는 행 필터링이 발생하지 않았음을 의미합니다. 100에서 감소하는 값은 증가하는 필터링 양을 나타냅니다. rows는 검사된 예상 행 수를 표시하고 rows × filters는 다음 표와 결합할 행 수를 표시합니다. 예를 들어 행이 1000이고 필터링 된 값이 50.00 (50 %) 인 경우 다음 표와 결합 할 행 수는 1000 × 50 % = 500입니다.

 

+ Extra (JSON 이름 : none)

이 열에는 MySQL이 쿼리를 해결하는 방법에 대한 추가 정보가 포함되어 있습니다. 다른 값에 대한 설명은 EXPLAIN 추가 정보를 참조합니다.

 

Extra 열에 해당하는 단일 JSON 속성이 없습니다. 그러나 이 열에서 발생할 수 있는 값은 JSON 속성 또는 메시지 속성의 텍스트로 노출됩니다.

 

▶︎ EXPLAIN 조인 타입

EXPLAIN 출력의 유형 열은 테이블을 조인하는 방법을 설명합니다. JSON 형식 출력에서 ​​이들은 access_type특성의 값으로 발견됩니다. 다음 목록은 최상의 유형에서 최악의 순서로 결합 유형을 설명합니다.

 

+ system

The table has only one row (= system table). This is a special case of the const join type.

 

+ system

테이블에는 하나의 행만 있습니다(=system 테이블). 이것은 const조인 유형의 특별한 경우입니다.

 

+ const

테이블에는 최대 하나의 일치하는 행이 있으며 쿼리 시작시 읽습니다. 행이 하나만 있기 때문에이 행의 열 값은 나머지 옵티마이저에서 상수로 간주 될 수 있습니다. const 테이블은 한 번만 읽으므로 매우 빠릅니다.

 

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

SELECT * FROM tbl_name WHERE primary_key=1;

 

SELECT * FROM tbl_name

  WHERE primary_key_part1=1 AND primary_key_part2=2;

 

+ eq_ref

이전 테이블의 각 행 조합에 대해 이 테이블에서 하나의 행을 읽습니다. 시스템 및 const 유형 이외의 가능한 조인 유형입니다. 조인에서 인덱스의 모든 부분을 사용하고 인덱스가 PRIMARY KEY 또는 UNIQUE NOT NULL 인덱스인 경우에 사용됩니다.

 

eq_ref는 = 연산자를 사용하여 비교되는 색인화된 컬럼에 사용할 수 있습니다. 비교 값은 상수이거나 이 테이블 전에 읽은 테이블의 컬럼을 사용하는 표현식일 수 있습니다. 다음 예제에서 MySQL은 eq_ref조인을 사용하여 ref_table을 처리할 수 ​​있습니다.

SELECT * FROM ref_table,other_table

  WHERE ref_table.key_column=other_table.column;

 

SELECT * FROM ref_table,other_table

  WHERE ref_table.key_column_part1=other_table.column

  AND ref_table.key_column_part2=1;

 

+ ref

이전 테이블의 각 행 조합에 대해 색인 값이 일치하는 모든 행을 이 테이블에서 읽습니다. 조인은 키의 가장 왼쪽 접두사만 사용하거나 키가 PRIMARY KEY 또는 UNIQUE 인덱스가 아닌 경우(즉, 조인이 키 값을 기준으로 단일 행을 선택할 수없는 경우)사용됩니다. 사용 된 키가 몇 개의 행과만 일치하면 이는 좋은 조인 유형입니다.

 

ref는 = 또는 <=> 연산자를 사용하여 비교되는 인덱싱 된 열에 사용할 수 있습니다. 다음 예제에서 MySQL은 ref 조인을 사용하여 ref_table을 처리 할 수 ​​있습니다.

SELECT * FROM ref_table WHERE key_column=expr;

 

SELECT * FROM ref_table,other_table

  WHERE ref_table.key_column=other_table.column;

 

SELECT * FROM ref_table,other_table

  WHERE ref_table.key_column_part1=other_table.column

  AND ref_table.key_column_part2=1;

 

+ fulltext

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

 

+ ref_or_null

이 조인 유형은 ref와 비슷하지만 MySQL은 NULL 값을 포함하는 행을 추가로 검색합니다. 이 조인 유형 최적화는 하위 쿼리를 해결하는데 가장 자주 사용됩니다. 다음 예제에서 MySQL은 ref_table을 처리하기 위해 ref_or_null 조인을 사용할 수 있습니다.

SELECT * FROM ref_table

  WHERE key_column=expr OR key_column IS NULL;

 

+ index_merge

이 조인 유형은 인덱스 병합 최적화가 사용되었음을 나타냅니다. 이 경우 출력행의 키 열에는 사용된 인덱스 목록이 포함되고 key_len에는 사용 된 인덱스의 가장 긴 키 부분 목록이 포함됩니다.

 

+ unique_subquery

이 형식은 다음 형식의 일부 IN 하위 쿼리에 대해 eq_ref를 대체합니다.

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery는 효율성을 높이기 위해 하위 쿼리를 완전히 대체하는 인덱스 조회 함수입니다.

 

+ index_subquery

이 조인 유형은 unique_subquery와 유사합니다. IN 서브 쿼리를 대체하지만 다음과 같은 형식의 서브 쿼리에서 고유하지 않은 인덱스에 대해 작동합니다.

value IN (SELECT key_column FROM single_table WHERE some_expr)

 

+ range

인덱스를 사용하여 행을 선택하면 지정된 범위에 있는 행만 검색됩니다. 출력 행의 키 열은 사용되는 인덱스를 나타냅니다. key_len에는 가장긴 키 부분이 포함되어 있습니다. 이 유형에 대한 참조 열은 NULL입니다.

 

범위는 키 열이 =, <>,>,> =, <, <=, IS NULL, <=>, BETWEEN, LIKE 또는 IN() 연산자 중 하나를 사용하여 상수와 비교될 때 사용할 수 있습니다.

SELECT * FROM tbl_name

  WHERE key_column = 10;

 

SELECT * FROM tbl_name

  WHERE key_column BETWEEN 10 and 20;

 

SELECT * FROM tbl_name

  WHERE key_column IN (10,20,30);

 

SELECT * FROM tbl_name

  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

 

+ index

인덱스 조인 유형은 인덱스 트리가 스캔된다는 점을 제외하고 ALL과 동일합니다. 이것은 두 가지 방법으로 발생합니다.

- 인덱스가 쿼리에 대한 커버링 인덱스이고 테이블에서 필요한 모든 데이터를 만족시키는 데 사용될 수있는 경우 인덱스 트리만 스캔됩니다. 이 경우 추가 컬럼에 색인 사용이 표시됩니다. 인덱스 크기는 일반적으로 테이블 데이터보다 작기 때문에 인덱스 전용 스캔은 일반적으로 ALL보다 빠릅니다.

- 전체 테이블 스캔은 인덱스에서 읽기를 사용하여 인덱스 순서로 데이터 행을 조회합니다. 추가 열에 색인 사용이 나타나지 않습니다.

 

쿼리에서 단일 인덱스의 일부인 열만 사용하는 경우 MySQL은이 조인 유형을 사용할 수 있습니다.

 

 

 

+ all

이전 테이블의 각 행 조합에 대해 전체 테이블 스캔이 수행됩니다. 테이블이 const로 표시되지 않은 첫번째 테이블인 경우 일반적으로 좋지 않으며 다른 모든 경우에는 일반적으로 매우 나쁩니다. 일반적으로 상수 값 또는 이전 테이블의 컬럼 값을 기반으로 테이블에서 행 검색을 가능하게하는 인덱스를 추가하여 ALL을 피할 수 있습니다.

 

▶︎ EXPLAIN 추가 정보

EXPLAIN 출력의 Extra 컬럼에는 MySQL이 쿼리를 해결하는 방법에 대한 추가 정보가 포함되어 있습니다. 다음 목록은 이 컬럼에 나타날 수 있는 값을 설명합니다. 각 항목은 또한 JSON 형식 출력에 대해 추가 값을 표시하는 속성을 나타냅니다. 이들 중 일부에는 특정 속성이 있습니다. 나머지는 메시지 속성의 텍스트로 표시됩니다.

쿼리를 최대한 빨리 수행하려면 파일 정렬 사용 및 tempory(임시공간) 사용 또는 JSON 형식의 EXPLAIN 출력에서 ​​using_filesort 및 using_temporary_table 속성이 true 인 Extra 컬럼 값을 찾습니다.

 

+ Child of 'table' pushed join@1 (JSON : message text)

이 테이블은 NDB 커널로 푸시 다운 될 수 있는 조인에서 테이블의 하위로 참조됩니다. 푸시 다운 조인이 활성화 된 경우 NDB 클러스터에만 적용됩니다.

 

+ const row not found (JSON 속성 : const_row_not_found)

SELECT ... FROM tbl_name과 같은 쿼리의 경우 테이블이 비어있었습니다.

 

+ Deleting all rows (JSON 속성 : message)

DELETE의 경우, 일부 스토리지 엔진 (예 : MyISAM)은 단순하고 빠른 방식으로 모든 테이블 행을 제거하는 핸들러 메소드를 지원합니다. 엔진이이 최적화를 사용하는 경우이 추가 값이 표시됩니다.

 

+ Distinct(고유) (JSON 속성 : distinct)

MySQL은 고유 한 값을 찾고 있으므로 첫 번째 일치하는 행을 찾은 후 현재 행 조합에 대해 더 많은 행을 검색하지 않습니다.

 

+ FirstMatch (tbl_name) (JSON 속성 : first_match)

semijoin FirstMatch조인 바로 가기 전략은 tbl_name에 사용됩니다.

 

+ Full scan on NULL key (JSON 특성 : 메시지)

최적화 프로그램이 인덱스 조회 액세스 방법을 사용할 수 없는 경우 하위 쿼리 최적화의 경우 대체 전략으로 발생합니다.

 

+ Impossible HAVING (JSON 속성 : message)

HAVING 절은 항상 false이며 행을 선택할 수 없습니다.

 

+ Impossible WHERE (JSON 속성 : message)

WHERE 절은 항상 false이며 행을 선택할 수 없습니다.

 

+ Impossible WHERE noticed after reading const tables (JSON 속성 : 메시지)

MySQL은 모든 const (및 시스템) 테이블을 읽었으며 WHERE 절이 항상 false임을 확인했습니다.

 

+ LooseScan (m..n) (JSON 속성 : 메시지)

semijoin LooseScan전략이 사용됩니다. m과 n은 핵심 파트 번호입니다.

 

+ No matching min/max row (JSON 속성 : message)

SELECT MIN (...) FROM ... WHERE 조건과 같은 쿼리 조건을 만족하는 행은 없습니다.

 

+ no matching row in const table (JSON 속성 : message)

조인이 있는 쿼리의 경우 빈 테이블 또는 고유 인덱스 조건을 만족하는 행이 없는 테이블이 있었습니다.

 

+ No matching rows after partition pruning (JSON 특성 : message).

DELETE 또는 UPDATE의 경우 옵티마이저는 파티션 정리 후 삭제하거나 업데이트 할 항목을 찾지 못했습니다. SELECT 문에 대해 불가능한 위치와 의미가 비슷합니다.

 

+ No tables used (JSON 속성 : message)

쿼리에 FROM 절이 없거나 FROM DUAL 절이 있습니다.

INSERT 또는 REPLACE 문의 경우 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 기준과 일치하는 하나의 행을 찾은 후 이전 행 조합에 대해이 테이블에서 더 많은 행을 검사하지 않습니다. 이러한 방식으로 최적화 할 수있는 쿼리 유형의 예는 다음과 같습니다.

SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id

  WHERE t2.id IS NULL;

t2.id가 NOT NULL로 정의되었다고 가정합니다. 이 경우 MySQL은 t1을 스캔하고 t1.id 값을 사용하여 t2의 행을 찾습니다. MySQL이 t2에서 일치하는 행을 찾으면 t2.id가 NULL 일 수 없다는 것을 알고, id 값이 동일한 t2의 나머지 행을 스캔하지 않습니다. 즉, t1의 각 행에 대해 MySQL은 t2에서 실제로 일치하는 행 수에 관계없이 t2에서 단일 조회만 수행하면 됩니다.

 

+ Plan isn't ready yet (JSON 속성 : none)

옵티마이 저가 명명 된 연결에서 실행중인 명령문에 대한 실행 계획 작성을 완료하지 않은 경우 EXPLAIN FOR CONNECTION에서이 값이 발생합니다. 실행 계획 출력이 여러 행을 포함하는 경우 전체 실행 계획을 판별 할 때 옵티마이 저의 진행 상황에 따라 이들 중 일부 또는 전부가이 Extra 값을 가질 수 있습니다.

 

+ Range checked for each record (index map : N) (JSON 속성 : message)

MySQL은 사용하기에 좋은 인덱스를 찾지 못했지만 이전 테이블의 컬럼 값을 알고 난 후에 일부 인덱스가 사용될 수 있음을 발견했습니다. 이전 테이블의 각 행 조합에 대해 MySQL은 range 또는 index_merge 액세스 방법을 사용하여 행을 검색 할 수 있는지 확인합니다. 이것은 빠르지는 않지만 인덱스없이 조인을 수행하는 것보다 빠릅니다. 단, 앞의 표에 대한 모든 컬럼 값이 알려져 있고 상수로 간주됩니다.

 

인덱스는 테이블에 대해 SHOW INDEX에 표시된 순서대로 1부터 시작하여 번호가 매겨집니다. 인덱스 맵 값 N은 어떤 인덱스가 후보인지를 나타내는 비트 마스크 값입니다. 예를 들어, 값 0x19 (이진 11001)는 인덱스 1, 4 및 5가 고려됨을 의미합니다.

 

+ Scanned N databases(JSON 속성 : message)

INFORMATION_SCHEMA 테이블에 대한 쿼리를 처리할 때 서버가 수행하는 디렉토리 스캔 수를 나타냅니다. N의 값은 0, 1 또는 모두 일 수 있습니다.

 

+ Select tables optimized away (JSON 속성 : message)

옵티마이저는 1) 최대 하나의 행이 리턴되어야하고 2)이 행을 생성하려면 결정적인 행 세트를 읽어야 한다고 결정했습니다. 최적화 단계에서 읽을 행을 읽을 수 있는 경우(예 : 인덱스 행 읽기) 쿼리 실행 중에 테이블을 읽을 필요가 없습니다.

 

쿼리가 암시적으로 그룹화될 때 첫 번째 조건이 충족됩니다 (집합 함수는 포함하지만 GROUP BY 절은 포함되지 않음). 두 번째 조건은 사용된 인덱스당 하나의 행 조회가 수행 될 때 충족됩니다. 읽은 인덱스 수는 읽을 행 수를 결정합니다.

 

다음과 같이 암시 적으로 그룹화 된 쿼리가 있습니다.

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

하나의 인덱스 행을 읽어 MIN (c1)을 검색 할 수 있고 다른 인덱스에서 한 행을 읽어 MIN (c2)을 검색 할 수 있다고 가정합니다. 즉, 각 열 c1 및 c2에 대해 컬럼이 인덱스의 첫 번째 컬럼인 인덱스가 있습니다. 이 경우 두 개의 정확한 행을 읽어서 하나의 행이 리턴됩니다.

 

읽을 행이 결정적이지 않은 경우 이 추가 값이 발생하지 않습니다. 다음과 같은 쿼리가 있습니다.

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

(c1, c2)가 포함 지수라고 가정합니다. 이 색인을 사용하여 최소 c2 값을 찾으려면 c1 <= 10 인 모든 행을 스캔해야 합니다. 반대로 이 쿼리를 확인해봅니다.

SELECT 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 : 테이블의 .frm 파일만 열어야 합니다.

-Open_full_table : 최적화되지 않은 정보 조회. .frm, .MYD 및 .MYI 파일을 열어야합니다.

 

+ Start temporary, End temporary (JSON 속성 : message)

이는 semijoin Dedlicate Weedout 전략에 임시 테이블 사용을 나타냅니다.

 

+ unique row not found (JSON 속성 : 메시지)

SELECT ... FROM tbl_name과 같은 쿼리의 경우 테이블의 UNIQUE 인덱스 또는 PRIMARY KEY 조건을 만족하는 행이 없습니다.

 

+ Using filesort (JSON 속성 : using_filesort)

MySQL은 행을 정렬된 순서로 검색하는 방법을 찾으려면 추가 패스를 수행해야 합니다. 정렬은 조인 유형에 따라 모든 행을 거치고 WHERE 절과 일치하는 모든 행에 대한 정렬 키와 포인터를 저장하여 수행됩니다. 그런 다음 키가 정렬되고 행이 정렬 된 순서로 검색됩니다.

 

+ Using index (JSON 속성 : using_index)

실제 행을 읽기 위해 추가 검색을 수행 할 필요없이 인덱스 트리의 정보만 사용하여 테이블에서 컬러ㅁ 정보를 검색합니다. 이 전략은 쿼리가 단일 인덱스의 일부인 컬럼만 사용할때 사용할 수 있습니다.

 

사용자 정의 클러스터형 인덱스가 있는 InnoDB 테이블의 경우 인덱스 사용이 Extra 컬럼에 없는 경우에도 해당 인덱스를 사용할 수 있습니다. type이 index이고 key가 PRIMARY인 경우입니다.

 

+ Using index condition (JSON 속성 : using_index_condition)

인덱스 튜플에 액세스하고 테이블을 먼저 테스트하여 전체 테이블 행을 읽을지 여부를 판별하여 테이블을 읽습니다. 이런 식으로 인덱스 정보는 필요한 경우가 아니면 전체 테이블 행 읽기를 지연( "push down")하는 데 사용됩니다.

 

+ Using index for group-by (JSON 속성 : using_index_for_group_by)

인덱스 테이블 액세스 방법 사용과 유사하게 group-by에 인덱스 사용은 MySQL이 실제 테이블에 대한 추가 디스크 액세스 없이 GROUP BY 또는 DISTINCT 쿼리의 모든 열을 검색하는 데 사용할 수 있는 인덱스를 찾았음을 나타냅니다. 또한 인덱스는 가장 효율적인 방식으로 사용되므로 각 그룹마다 몇 개의 인덱스 항목만 읽을 수 있습니다.

 

+ Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access) (JSON 속성 : using_join_buffer)

이전 조인의 테이블을 조인 버퍼로 부분적으로 읽은 다음 해당 행을 버퍼에서 사용하여 현재 테이블과의 조인을 수행합니다. (Block Nested Loop)는 Block Nested-Loop 알고리즘의 사용을 나타내고 (Batched Key Access)는 Batched Key Access 알고리즘의 사용을 나타냅니다. 즉, EXPLAIN 출력의 이전 행에 있는 테이블의 키가 버퍼링되고 결합 버퍼 사용이 나타나는 행으로 표시되는 테이블에서 일치하는 행이 일괄 적으로 페치됩니다.

 

JSON 형식 출력에서 ​​using_join_buffer의 값은 항상 블록 중첩 루프 또는 배치 키 액세스 중 하나입니다.

 

+ Using MRR (JSON 속성 : 메시지)

다중 범위 읽기 최적화 전략을 사용하여 테이블을 읽습니다.

 

+ Using sort_union(...), Using union(...), Using intersect(...) (JSON 속성 : 메시지)

이들은 인덱스 스캔이 index_merge 조인 유형에 대해 병합되는 방법을 보여주는 특정 알고리즘을 나타냅니다.

 

+ Using temporary (JSON 속성 : using_temporary_table)

쿼리를 해결하기 위해 MySQL은 결과를 보유할 임시 테이블을 생성해야 합니다. 일반적으로 쿼리에 컬럼을 다르게 나열하는 GROUP BY 및 ORDER BY 절이 포함된 경우에 발생합니다.

 

+ Using where (JSON 속성 : attached_condition)

WHERE 절은 다음 테이블과 일치하거나 클라이언트로 보낼 행을 제한하는데 사용됩니다. 테이블에서 모든 행을 페치하거나 검사하려는 경우가 아니라면 Extra 값이 Using where가 아니고 테이블 조인 유형이 ALL 또는 인덱스인 경우 쿼리에 문제가 있을 수 있습니다.

 

JSON 형식의 출력에 직접적인 대응이없는 where를 사용하면; attached_condition 특성에는 사용 된 WHERE 조건이 포함됩니다.

 

+ Using where with pushed condition (JSON 속성 : 메시지)

이 항목은 NDB 테이블에만 적용됩니다. 이는 NDB 클러스터가 인덱싱되지 않은 컬럼과 상수 사이의 직접 비교의 효율성을 개선하기 위해 조건 푸시 다운 최적화를 사용하고 있음을 의미합니다. 이러한 경우 조건이 클러스터의 데이터 노드로 "push down"되고 모든 데이터 노드에서 동시에 평가됩니다. 이렇게하면 일치하지 않는 행을 네트워크를 통해 전송할 필요가 없으며 조건 푸시 다운을 사용할 수 있지만 사용하지 않는 경우보다 이러한 쿼리의 속도를 5-10 배 높일 수 있습니다.

 

+ Zero limit (JSON 속성 : 메시지)

쿼리에 LIMIT 0 절이 있어서 행을 선택할 수 없습니다.

 

 

 

▶︎ EXPLAIN 출력 해석

EXPLAIN 출력의 행 컬럼에 있는 값을 확인해보면 조인을 할때 얼마나 좋은지를 알 수 있습니다. 이것은 대략 MySQL이 쿼리를 실행하기 위해 검사해야 하는 행 수를 대략 알려줍니다. max_join_size 시스템 변수를 사용하여 쿼리를 제한하면 이 행 값을 사용하여 실행할 다중 테이블 SELECT 문과 중단 할 대상을 결정합니다.

 

다음 예는 EXPLAIN이 제공한 정보를 기반으로 다중 테이블 조인을 점진적으로 최적화하는 방법을 보여줍니다.

여기에 SELECT 문이 표시되고 EXPLAIN을 사용하여 이를 검토하려고한다고 가정합니다.

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
               tt.ProjectReference, tt.EstimatedShipDate,
               tt.ActualShipDate, tt.ClientID,
               tt.ServiceCodes, tt.RepetitiveID,
               tt.CurrentProcess, tt.CurrentDPPerson,
               tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
               et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
          AND tt.ActualPC = et.EMPLOYID
          AND tt.AssignedPC = et_1.EMPLOYID
          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)

The tt.ActualPC values are not evenly distributed.

tt.ActualPC 값은 고르게 분포되지 않습니다.

 

처음에 최적화가 수행되기 전에 EXPLAIN 문은 다음 정보를 생성합니다.

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

Range checked for each record (index map: 0x23)

 

유형이 각 테이블에 대해 ALL이므로, 이 출력은 MySQL이 모든 테이블의 카티전(Cartesian)곱을 생성하고 있음을 나타냅니다. 즉, 모든 행 조합입니다. 각 테이블에 있는 행 수의 곱을 검사해야하므로 시간이 오래 걸립니다. 현재 이 출력의 예상행은 74 × 2135 × 74 × 3872 = 45,268,558,720 행입니다. 테이블이 더 크면 시간이 얼마나 걸릴지 상상해볼 수 있습니다.

 

여기서 하나의 문제는 MySQL이 동일한 유형과 크기로 선언된 경우 컬럼의 인덱스를 보다 효율적으로 사용할 수 있다는 것입니다. 이 컨텍스트에서 VARCHAR 및 CHAR은 동일한 크기로 선언 된 경우 동일한 것으로 간주됩니다. tt.ActualPC는 CHAR(10)로 선언되고 et.EMPLOYID는 CHAR(15)이므로 컬럼 타입은 같지만 크기가 같지 않습니다..

 

컬럼 길이 사이의 이러한 불일치를 수정하려면 ALTER TABLE을 사용하여 ActualPC를 10 자에서 15 자로 늘립니다.

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

 

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

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

이것은 완벽하지는 않지만 훨씬 낫습니다. 행 값의 곱이 74 배나 적습니다. 이 쿼리는 몇 초 안에 실행됩니다.

 

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

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

 

수정 후 EXPLAIN은 다음과 같은 출력을 생성합니다.

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

 

이 시점에서 쿼리는 거의 최대한 최적화됩니다. 나머지 문제는 기본적으로 MySQL이 tt.ActualPC 컬럼의 값이 균등하게 분산되어 있다고 가정하고 tt 테이블의 경우가 아니라는 것입니다. 다행스럽게도 MySQL에게 키 배포를 분석하도록 하는 것은 쉽습니다.

mysql> ANALYZE TABLE tt;

 

추가 인덱스 정보를 사용하면 결합이 완벽하고 EXPLAIN이 다음 결과를 생성합니다.

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

 

EXPLAIN의 출력에서 ​​행 컬럼은 MySQL 조인 옵티마이 저의 정확한 추측입니다. 쿼리에서 반환하는 실제 행 수와 행 곱을 비교하여 숫자가 실제와 가까운 지 확인합니다. 숫자가 상당히 다른 경우, SELECT 문에서 STRAIGHT_JOIN을 사용하고 FROM 절에서 다른 순서로 테이블을 나열하여 성능을 향상시킬 수 있습니다. 그러나 STRAIGHT_JOIN은 세미 조인 변환을 비활성화하므로 인덱스가 사용되지 않을 수 있습니다.

경우에 따라 EXPLAIN SELECT를 하위 쿼리와 함께 사용할 때 데이터를 수정하는 문을 실행할 수 있습니다.

 

■ 확장 된 EXPLAIN 출력 형식

SELECT 문의 경우, EXPLAIN 문은 EXPLAIN 출력의 일부가 아니지만 EXPLAIN 다음에 SHOW WARNINGS 문을 발행하여 볼수있는 추가 ( "확장 된") 정보를 생성합니다. SHOW WARNINGS 출력의 메시지 값은 옵티마이저가 SELECT 문에서 테이블 및 컬럼 이름을 규정하는 방법, 재 작성 및 최적화 규칙 적용 후 SELECT의 모양 및 최적화 프로세스에 대한 기타 참고 사항을 표시합니다.

 

EXPLAIN 다음에 SHOW WARNINGS 문으로 표시 할 수 있는 확장 정보는 SELECT 문에 대해서만 생성됩니다. SHOW WARNINGS는 다른 명령문 (DELETE, INSERT, REPLACE 및 UPDATE)에 대해 빈 결과를 표시합니다.

 

노트

이전 MySQL 릴리스에서는 EXPLAIN EXTENDED를 사용하여 확장 된 정보가 생성되었습니다. 이 구문은 여전히 ​​이전 버전과의 호환성을 위해 인식되지만 이제 확장 출력이 기본적으로 활성화되어 있으므로 EXTENDED 키워드가 불필요하고 더 이상 사용되지 않습니다. 사용하면 경고가 발생하며 향후 MySQL 릴리스의 EXPLAIN 구문에서 제거됩니다.

 

다음은 확장된 EXPLAIN 출력의 예입니다.

mysql> EXPLAIN
       SELECT t1.a, t1.a IN (SELECT t2.a FROM t2) FROM t1\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 2
  select_type: SUBQUERY
        table: t2
         type: index
possible_keys: a
          key: a
      key_len: 5
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.00 sec)

 

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a` AS `a`,
         <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in
         ( <materialize> (/* select#2 */ select `test`.`t2`.`a`
         from `test`.`t2` where 1 having 1 ),
         <primary_index_lookup>(`test`.`t1`.`a` in
         <temporary table> on <auto_key>
         where ((`test`.`t1`.`a` = `materialized-subquery`.`a`))))) AS `t1.a
         IN (SELECT t2.a FROM t2)` from `test`.`t1`
1 row in set (0.00 sec)

SHOW WARNINGS에 의해 표시되는 명령문에는 쿼리 재작성 또는 옵티마이저 조치에 대한 정보를 제공하는 특수 마커가 포함될 수 있으므로 명령문은 반드시 유효한 SQL일 필요는 없으며 실행되도록 의도되지 않았습니다. 출력에는 옵티마이저가 수행한 조치에 대한 추가 비 SQL 설명 메모를 제공하는 Message 값이있는 행이 포함될 수도 있습니다.

 

다음 목록은 SHOW WARNINGS로 표시되는 확장 출력에 나타날 수있는 특수 마커를 설명합니다.

+ <auto_key>

임시 테이블에 대해 자동으로 생성 된 키입니다.

 

+ <cache>(expr)

스칼라 하위 쿼리와 같은 표현식이 한 번 실행되고 결과 값은 나중에 사용하기 위해 메모리에 저장됩니다. 여러 값으로 구성된 결과의 경우 임시 테이블이 작성 될 수 있으며 대신 <임시 테이블>이 표시됩니다.

 

+ <exists>(query fragment)

서브쿼리 술어(조건절)는 EXISTS 술어(조건절)로 변환되고 서브쿼리는 EXISTS 술어(조건절)와 함께 사용될 수 있도록 변환됩니다.

 

+ <in_optimizer> (쿼리 조각)

이것은 사용자 입장에서 필요성이 없는 내부 최적화 프로그램 객체입니다.

 

+ <index_lookup>(query fragment)

쿼리 조각은 올바른 행을 찾기 위해 인덱스 조회를 사용하여 처리됩니다.

 

+ <if>(condition, expr1, expr2)

조건이 true이면 expr1로 평가하고, 그렇지 않으면 expr2로 평가합니다.

 

+ <is_not_null_test>(expr)

표현식이 NULL로 평가되지 않는지 테스트합니다.

 

+ <materialize>(query fragment)

서브쿼리 구체화(materialize)가 사용됩니다.

 

+ `materialized-subquery`.col_name

내부 임시 테이블의 열 col_name에 대한 참조는 서브 쿼리 평가 결과를 보유하도록 구체화(materialized)되었습니다.

 

+ <primary_index_lookup>(query fragment)

쿼리 조각은 프리머리키 조회를 사용하여 처리되어 알맞은 행을 찾습니다.

 

+ <ref_null_helper>(expr)

이것은 사용자 중요성이 없는 내부 최적화 프로그램 객체입니다.

 

+ /* select#N */ select_stmt

SELECT는 id 값이 N 인 확장되지 않은 EXPLAIN 출력의 행과 연관됩니다.

 

+ outer_tables semi join (inner_tables)

semijoin 작업 inner_tables는 꺼내지 않은 테이블을 보여줍니다.

 

+ <temporary table>

중간 결과를 캐시하기 위해 작성된 내부 임시 테이블을 나타냅니다.

 

일부 테이블이 const 또는 시스템 유형인 경우 이러한 테이블의 컬럼과 관련된 표현식은 옵티 마이저에서 초기에 평가되며 표시된 명령문의 일부가 아닙니다. 그러나 FORMAT = JSON을 사용하면 일부 const 테이블 액세스가 const 값을 사용하는 참조 액세스로 표시됩니다.

 

■ 이름이 지어진(명명된) 연결에 대한 실행 계획 정보 얻기

이름이 지정된(명명된) 연결에서 실행 가능한 설명 문의 실행 계획을 얻으려면 다음 명령문을 사용합니다.

 

EXPLAIN [options] FOR CONNECTION connection_id;

EXPLAIN FOR CONNECTION은 주어진 연결에서 쿼리를 실행하는데 현재 사용중인 EXPLAIN 정보를 반환합니다. 데이터 (및 지원 통계)의 변경으로 인해 동등한 쿼리 텍스트에서 EXPLAIN을 실행하는 것과 다른 결과가 생성될 수 있습니다. 이러한 동작 차이는 일시적인 성능 문제를 진단하는데 유용할 수 있습니다. 예를 들어, 완료하는 데 시간이 오래 걸리는 한 세션에서 명령문을 실행중인 경우 다른 세션에서 EXPLAIN FOR CONNECTION을 사용하면 지연 원인에 대한 유용한 정보가 생성 될 수 있습니다.

 

connection_id는 INFORMATION_SCHEMA PROCESSLIST테이블 또는 SHOW PROCESSLIST 문에서 얻은 연결 식별자입니다. PROCESS 권한이 있으면 모든 연결의 식별자를 지정할 수 있습니다. 그렇지 않으면 자신의 연결에 대해서만 식별자를 지정할 수 있습니다.

 

이름이 지정된(명명된) 연결이 명령문을 실행하지 않는 경우 결과는 비어 있습니다. 그렇지 않으면 EXPLAIN FOR CONNECTION은 이름이 지정된 연결에서 실행중인 명령문이 설명 가능한 경우에만 적용됩니다. 여기에는 SELECT, DELETE, INSERT, REPLACE 및 UPDATE가 포함됩니다. (그러나 EXPLAIN FOR CONNECTION은 준비된 명령문, 해당 유형의 준비된 명령문에 대해서는 작동하지 않습니다.)

 

이름이 지정된 연결이 설명 가능한 명령문을 실행중인 경우 명령문 자체에 EXPLAIN을 사용하여 결과를 얻을 수 있습니다.

 

이름이 지정된 연결이 설명할 수 없는 명령문을 실행중인 경우 오류가 발생합니다. 예를 들어 EXPLAIN을 설명할 수 없으므로 현재 세션의 연결 식별자 이름을 지정할 수 없습니다.

mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|             373 |
+-----------------+
1 row in set (0.00 sec)

mysql> EXPLAIN FOR CONNECTION 373;
ERROR 1889 (HY000): EXPLAIN FOR CONNECTION command is supported only for SELECT/UPDATE/INSERT/DELETE/REPLACE

 

Com_explain_other 상태 변수는 실행 된 EXPLAIN FOR CONNECTION 문의 수를 나타냅니다.

 

■ 쿼리 성능 추정

대부분의 경우 디스크 탐색을 계산하여 쿼리 성능을 추정할 수 있습니다. 작은 테이블의 경우 일반적으로 하나의 디스크 탐색에서 행을 찾을 수 있습니다 (인덱스가 캐시되어 있기 때문에). 더 큰 테이블의 경우 B- 트리 인덱스를 사용하여 log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1을 찾으려면 많은 수의 탐색이 필요하다고 추정할 수 있습니다.

 

MySQL에서 인덱스 블록은 일반적으로 1,024 바이트이고 데이터 포인터는 일반적으로 4바이트입니다. 키 값 길이가 3바이트 (MEDIUMINT의 크기)인 500,000행 테이블의 경우 공식은 log(500,000)/log(1024/3*2/(3+4)) + 1 = 4탐색을 나타냅니다.

 

이 인덱스는 약 500,000 * 7 * 3/2 = 5.2MB의 스토리지를 필요로하며 (일반적인 인덱스 버퍼 채우기 비율이 2/3인 경우) 메모리에 많은 인덱스가 있으므로 한두번의 호출만 필요합니다. 행을 찾기 위해 데이터를 읽습니다.

 

그러나 쓰기의 경우 새 인덱스 값을 배치할 위치를 찾으려면 4 개의 검색 요청이 필요하며 일반적으로 인덱스를 업데이트하고 행을 작성하려면 2개의 검색이 필요합니다.

 

앞에서 논의한 내용이 로그 N에 의해 ​​애플리케이션 성능이 느리게 저하된다는 의미는 아닙니다. 모든 것이 OS 또는 MySQL 서버에 의해 캐시되는 한, 테이블이 커질수록 상황이 약간 느려집니다. 캐시하기에 데이터가 너무 커지면 디스크 탐색(로그 N 증가)만으로 응용 프로그램이 바인딩 될때까지 상황이 훨씬 느리게 시작됩니다. 이를 피하려면 데이터가 커질수록 키 캐시 크기를 늘려야 합니다. MyISAM 테이블의 경우 키 캐시 크기는 key_buffer_size 시스템 변수에 의해 제어됩니다.

 

 

※도움이 되셨다면 광고클릭 한번 부탁드립니다.※