[Real MySQL 8.0] 10장 - 실행 계획

 

9장에서 옵티마이저가 어떤 실행 계획을 선택하는지에 대해 공부했다면, 10장에서는 실제로 표기되는 실행계획을 읽고 해석하기 위한 공부를 해 보려고 한다.

 

10.2 실행 계획 확인

• MySQL 서버의 실행 계획은 DESC또는 EXPLAIN 명령으로 확인할 수 있다(결과는 같음).

10.2.1 실행 계획 출력 포맷

• MySQL 8.0부터는 EXTENDED(옵티마이저가 변환한 쿼리 확인), PARTITIONS(파티션 목록 확인) 옵션은 제거됨
• MySQL 8.0부터 FORMAT 옵션을 통해 실행 계획을 JSON이나 TREE, 단순 테이블 형태로 표시할 수 있다.
  - EXPLAIN (단순 테이블 형태) - 대부분 실행 순서는 위에서 아래로 순서대로 표시
  - EXPLAIN FORMAT=TREE (트리 형태로 표시) - 별도로 순서 규칙이 있음
  - EXPLAIN FORMAT=JSON (JSON 형식으로 표시)

10.2.2 쿼리의 실행 시간 확인

• MySQL 8.0부터는 EXPLAIN ANALYZE 기능이 추가되어 쿼리의 실행 계획, 단계별 소요된 시간 정보를 확인할 수 있다.
  
  • actual time : 테이블에서 일치하는 레코드를 검색하는 데 거린 시간. 첫 번째 숫자는 첫번째 레코드를 가져오는데 걸린 평균 시간, 두 번째 숫자는 마지막 레코드를 가져오는데 걸린 평균 시간.
  • rows : 조건에 일치하는 테이블의 평균 레코드 건수
  • loops : 테이블의 레코드를 찾는 작업이 반복된 횟수.
• EXPLAIN과 달리 실행 계획만 추출하는 것이 아니라 실제 쿼리를 실행하고 사용된 실행 계획과 소요된 시간을 보여준다. 

10.3 실행 계획 분석

• 실행 계획에 표시되는 칼럼들 : id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra

10.3.1 id

• 단위 SELECT 쿼리별로 부여되는 식별자 값
• 조인 쿼리의 경우 테이블 개수만큼 실행 계획 레코드가 출력되지만 같은 id 값이 부여된다.
• id 칼럼이 테이블의 접근 순서를 위미하지는 않는다(419p.. 순서 모르겠음).

10.3.2 select_type

SIMPLE	UNION이나 서브쿼리를 사용하지 않는 단순 SELECT 쿼리(조인 포함)에서 표시. 하나만 존재한다.
PRIMARY	UNION이나 서브쿼리를 가지는 SELECT 쿼리에서 가장 바깥쪽에 있는 단위 쿼리에서 표시. 하나만 존재한다.
UNION	UNION으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째 제외 두번째 이후 단위 SELECT 쿼리에서 표시.
DEPENDENT UNION	UNION이나 UNION ALL로 집합을 결합하는 쿼리에서 표시. 내부 쿼리가 외부의 값을 참조해서 처리될 때.
UNION RESULT	임시 테이블에 UNION 결과를 버퍼링 하는 경우 표시. 단위 쿼리가 아니라서 id는 부여되지 않는다.
SUBQUERY	select_type의 SUBQUERY는 FROM절 이외에서 사용되는 서브쿼리만을 의미한다.
DEPENDENT SUBQUERY	서브쿼리가 외부 쿼리에서 정의된 칼럼을 사용하는 경우.
DERIVED	서브쿼리가 FROM절에 사용된 경우(옵티마이저가 서브쿼리 최적화를 하지 못하였을 경우).
DEPENDENT DERIVED	FROM 절의 서브쿼리에서 외부 칼럼을 참조하는 경우.
UNCACHEABLE SUBQUERY	조건이 똑같은 서브쿼리가 실행될 때 다시 실행하지 않고 캐싱된 결과를 사용하는데, 그렇지 못할 경우.
UNCACHEABLE UNION	UNION으로 결합된 서브쿼리에서 캐싱된 결과를 사용하지 못하는 경우.
MATERIALIZED	서브쿼리 부분을 임시 테이블로 구체화 한 후 처리되었을 경우.

 

10.3.3 table

• MySQL 서버의 실행 계획은 테이블 기준으로 표시된다.
• 별도의 테이블을 사용하지 않는 SELECT 쿼리의 경우 table 칼럼에 NULL이 표시된다.
• "<>"로 둘러싸인 경우 임시 테이블을 의미한다.
  (select_type이 MATERIALIZED일 경우 테이블명은 "<subquery N>"으로 표시된다)

10.3.4 partitions

• 파티션 프루닝 : 파티셔닝 테이블에서 접근해야 할 것으로 판단되는 테이블만 골라내는 과정.
• 파티션을 참조하는 쿼리의 경우 실행 계획의 partitions칼럼에 필요한 파티션들의 목록을 표시해준다.

10.3.5 type

• 테이블의 접근 방법이라고 볼 수 있다.
• 서버가 각 테이블의 레코드를 어떤 방식(풀 테이블 스캔, 인덱스 스캔 등..)으로 읽었는지를 나타내기 때문에 중요한 정보이다.

all 을 제외한 나머지는 인덱스를 사용한 접근 방법이다.

하나의 SELECT 쿼리는 단 하나의 접근 방법만 사용할 수 있다.

위에서부터 아래 순서로 성능이 빠른 순서이다.

system	레코드가 1건만 존재하거나, 한 건도 존재하지 않는 테이블을 참조하는 형태. InnoDB스토리지 엔진을 사용하는 테이블에서는 나타나지 않고, MyISAM이나 MEMORY테이블에서만 사용된다.
const	프라이머리 키나 유니크 인덱스의 모든 칼럼을 동등 조건으로 걸었을 때.
eq_ref	조인에서 처음 읽은 테이블의 칼럼값을 그 다음 읽어야 할 테이블의 프라이머리 키나 유니크 키 칼럼의 검색 조건에 사용할 때.
ref	인덱스 종류와 관계 없이 동등 조건으로 검색할 때.
fulltext	전문 검색 인덱스를 사용해 레코드를 읽는 접근 방식.
ref_or_null	ref + NULL 비교 접근 방식(실제로는 잘 사용되지 않는다).
unique_subquery	IN(subquery) 형태의 쿼리일 때, 서브쿼리에서 중복되지 않는 유니크한 값만 반환할 때. (8.0에선 잘 안보일 것..)
index_subquery	IN(subquery) 형태일 때, 서브쿼리에서 중복이 발생하는 경우에 중복을 인덱스를 이용해서 제거할 수 있을 때.
range	인덱스 레인지 스캔. 일반적인 BETWEEN, >, IN 등 연산자를 이용해 검색할 때.
index_merge	2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후, 그 결과를 병합해서 처리하는 방식.
index	인덱스 풀 스캔.
ALL	풀 테이블 스캔.

 

10.3.6 possible_keys

• 옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 접근 방법에서 사용하는 인덱스의 목록.
• 무시해도 된다.

10.3.7 key

• 최종 선택된 실행 계획에서 사용하는 인덱스
• 접근 방법(type)이 index_merge가 아닌 경우에는 반드시 테이블 하나당 하나의 인덱스만 이용할 수 있다.

10.3.8 key_len

• 다중 컬럼으로 구성된 인덱스에서 몇 개의 칼럼까지 사용했는지를 알 수 있다.

10.3.9 ref

• 접근 방법(type)이 ref이면 참조 조건으로 어떤 값이 제공되었는지 보여준다.
  
  • const - 상수
  • 테이블과 컬럼명(eg. table.column) - 컬럼일 경우
  • func - 사용자나 MySQL서버가 값을 변환했을 경우(사용자 함수나 타입이 다른 컬럼의 조인)

10.3.10 rows

• 실행 계획의 효율성 판단을 위해 예측했던 레코드 건수. 쿼리를 처리하기 위해 읽고 체크해야 하는 레코드의 건수를 의미한다.

10.3.11 filtered

• 필터링되고 남은 레코드의 비율을 의미한다.
• MySQL 8.0부터 filtered 컬럼의 값을 더 정확히 예측할 수 있도록 히스토그램 기능이 도입되었다.
• filtered가 정확히 예측되면 어느 테이블을 먼저 읽어야 조인 횟수를 줄일 수 있을지 더 정확히 판단할 수 있다.

10.3.12 Extra

• 성능에 관련된 중요한 내용(내부적인 처리 알고리즘에 대해 조금 더 깊이있는 내용)을 보여주는 경우가 많다.
• const row now found
  cosnt 방식으로 테이블을 읽었지만 실제로 해당 테이블에 레코드가 1건도 존재하지 않으면 표시된다.
• Deleting all rows
  WHERE 조건절이 없는 DELETE 문장의 실행 계획에서 자주 표시된다.
• Distinct
  쿼리의 DISTINCT를 처리하기 위해 조인하지 않아도 되는 항목은 무시하고 필요한 것만 읽었다는 것을 표시한다.
• FirstMatch
  세미 조인 최적화 중에서 FirstMatch전략이 사용되었을 때
• Full sacn on NULL key
  col1 IN (SELECT ... ) 절에서 col1이 NULL일 경우 서브쿼리 테이블을 풀 테이블 스캔 할 것이라는 사실을 알려준다.
  NULL이 없다면 풀스캔 하지 않겠지만, 하게 된다면 성능 저하가 일어날 수 있다.
• Impossible HAVING
  HAVING 절의 조건을 만족하는 레코드가 없을 때
• Impossible WHERE
  WHERE 조건이 항상 FALSE가 될 수밖에 없는 경우
• LooseScan
  세미 조인 최적화 중에서 LooseScan 최적화 전략이 사용되었을 때
• No matching min/max row
  MIN(), MAX() 등 집합 함수가 있는 쿼리의 조건절에 일치하는 레코드가 한 건도 없을 때
• no matching row in const table
  const방법으로 접근할 때 일치하는 레코드가 없을 때
• No matching rows after partition pruning
  파티셔닝 테이블에서 UPDATE 하거나 DELETE할 대상 레코드가 없을 때. 단순히 삭제할 레코드가 없는 것이 아니라 대상 파티션이 없다는 것을 의미한다.
• No tables used
  FROM절이 없는 쿼리문이나 "FROM DUAL" 형태의 쿼리 실행 계획
• Not exists
  아우터 조인을 이용해 안티조인을 수행하는 쿼리일 때
• Plan isn't ready yet
  EXPLAIN FOR CONNECTION 명령을 사용해 실행 중인 쿼리의 실행계획을 볼 때, 아직 쿼리의 실행 계획을 수립하지 못한 경우.
• Range checked for each record(index map:N)
  각 레코드마다 최적의 접근 방식이 다를 때, 레코드마다 인덱스 레인지 스캔을 체크한다는 의미.
  index map에는 16진수로 어떤 인덱스를 후보로 선정했는지 확인할 수 있다.
• Recursive
  CTE를 이용한 재귀 쿼리일 경우
• Rematerialize
  래터럴 조인에서 조인되는 테이블은 선행 테이블의 레코드별로 서브쿼리를 실행해서 그 결과를 임시테이블에 저장한다. 이 때 표기된다.
• Select tables optimized away
  MIN(), MAX()만 SELECT 절에 사용되거나 GROUP BY로 MIN(), MAX()를 조회하는 쿼리가 인덱스를 오름차순 또는 내림차순으로 1건만 읽는 형태의 최적화가 적용되었을 때 표시된다.
• Start temporary, End temporary
  세미 조인 최적화 중에서 Duplicate Weed-out 최적화 전략이 사용되었을 때.
• unique row not found
  두 개의 테이블이 각각 유니크 컬럼으로 아우터 조인을 수행할 때, 아우터 테이블에 일치하는 레코드가 존재하지 않을 때 표시된다.
• Using filesort
  ORDER BY 처리가 인덱스를 사용하지 못할 때.
  이 경우 쿼리가 많은 부하를 일으키므로 가능하다면 쿼리를 튜닝하거나 인덱스를 생성하는 것이 좋다.
• Using index
  데이터 파일을 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있을 때
• Using index condition
  인덱스 컨디션 푸시 다운 최적화를 사용했을 때
• Using index for group-by
  GROUP BY 처리가 인덱스를 이용할 때(= 루스 인덱스 스캔을 할 때)
  - 타이트 인덱스 스캔을 할 때 : 인덱스를 이용해 GROUP BY를 처리할 수 있더라도 AVG(), COUNT()처럼 조회하려는 값이 모든 인덱스를 다 읽어야 할 때는 루스 인덱스 스캔 할 수 없다. 그래서 이 때에는 실행 계획에 Using index for group-by가 표시되지 않는다.
  - 루스 인덱스 스캔을 할 때 : 조회하는 값이 인덱스의 첫 번째 또는 마지막 레코드만 읽어도 될 때 루스 인덱스 스캔이 사용될 수 있다.
• Using index for skip scan
  인덱스 스킵 스캔 최적화를 사용했을 때
• Using join buffer
  블록 네스티드 루프 조인이나 해시 조인을 사용했을 때.
  괄호() 안에 조인 알고리즘이 추가로 표시된다. eg) Using join buffer (hash join)
• Using MRR
  레코드 단위로 API호출하던 부분을 보완하기 위해 MRR 최적화를 도입했다. MRR 최적화가 사용될 때 표시된다.
• Using sort_union(...), Using union(...), Using intersect(...)
  접근 방법(type)이 index_merge일 경우 두 인덱스로부터 읽은 결과를 어떻게 병합했는지 조금 더 상세하게 설명하기 위해 3개 중에서 하나의 메시지를 선택적으로 출력한다.
  - Using sort_union : 각각의 인덱스를 사용할 수 있는 조건이 AND로 연결된 경우 각 처리 결과에서 교집합을 추출해 내는 작업을 수행했다는 의미
  - Using union : 각각의 인덱스를 사용할 수 있는 조건이 OR로 연결된 경우 각 처리 결과에서 합집합을 추출해 내는 작업을 수행했다는 의미
  - Using intersect : Using onion으로 처리할 수 없는 대량의 range 조건들의 경우 프라이머리 키를 읽어서 정렬하고 병합한 이후 비로소 레코드를 읽어서 반환한다.
  (WHERE 조건에 사용된 비교 조건이 모두 동등 조건이면 Using union이 사용되고, 그렇지 않으면 Using sort_union이 사용된다.)
• Using temporary
  쿼리를 처리하는 동안 중간 결과를 담아 두기 위해 임시 테이블을 사용했을 때
• Using where
  MySQL 엔진에서 별도의 필터링을 했을 때
• Zero limit
  메타정보만 필요할 때 limit 0을 사용하는데, 이 때 표시되는 메시지