실행계획

1. 통계 정보

(1) 테이블 및 인덱스 통계 정보

A. MySQL 서버의 통계정보

각 테이블의 통계 정보는 innodb_index_stats, innodb_table_stats 테이블로 관리할 수 있습니다.

B. 히스토그램

히스토그램 정보는 칼럼 단위로 관리됩니다. ANALYZE TABLE... UPDATE HISTOGRAM 명령을 실행해 수동으로 수집 및 관리됩니다. 이 히스토그램 정보는 시스템 딕셔너리에 저장됩니다.

히스토그램은 버킷 단위로 구분되어 레코드 건수나 칼럼값의 범위가 관리되는데, 싱글톤 히스토 그램은 칼람이 가지는 값별로 버킷이 할당되며, 높이 균형 히스토그램은 각 버킷이 칼럼의 값과 발생 빈도의 비율의 2개 값을 가집니다.

• 히스토그램의 용도
  히스토그램은 특정 칼럼이 가지는 모든 값에 대한 분포도 정보를 가지지는 않지만 각 범위별로 레코드의 건수와 유니크한 값의 개수 정보를 가지기 때문에 더 정확한 예측을 할 수 있습니다.

• 히스토그램과 인덱스
  인덱스된 칼럼을 검색 조건으로 사용하는 경우 그 칼럼의 히스토그램은 사용하지 않고 실제 인덱스 다이브를 통해 수집한 정보를 활용합니다. 이는 히스토그램보다 정확한 결과를 기대할 수 있기 떄문입니다. 그래서 히스토그램은 인덱스되지않은 칼럼에 대해 데이터 분포도를 참조하는 용도로 사용됩니다.

C. 코스트 모델

전체 쿼리의 비용을 계산하는 데 필요한 단위 작업들의 비용을 '코스트 모델'이라고 합니다.
2개 테이블에 저장돼 있는 설정값을 사용합니다.

• server_cost : 인덱스를 찾고 레코드를 비교하고 임시 테이블 처리에 대한 비용 관리
• engine_cost : 레코드를 가진 데이터 페이지를 가져오는 데 필요한 비용 처리

이 두 테이블은 5개의 칼럼을 공통으로 가지고 있습니다.

• cost_name : 코스트 모델의 각 단위 작업
• default_value : 각 단위 작업의 비용
• cost_value : DBMS 관리자가 설정한 값
• last_updated : 단위 작업의 비용이 변경된 시점
• comment : 비용에 대한 추가 설명

이 코스트모델에서 중요한 건, 각 단위 작업에 설정되는 비용 값이 커지면 어떤 실행계획들이 고비용으로 바뀌고 어떤 실행 계획들이 저비용으로 바뀌는지 파악하는 것입니다.

• key_compare_cost : 비용을 높이면 정렬을 수행하지 않는 방향의 실행계획 선택할 가능성이 높아집니다.

• row_evaluatae_cost : 비용을 높이면 풀 스캔을 실행하는 쿼리들의 비용이 높아지고, 옵티마이저는 가능하면 인덱스 레인지 스캔을 사용하는 실행 계획을 선택할 가능성이 높아집니다

• disk_temptable_create_cost와 disk_temptable_row_cost 비용을 높이면 MySQL 옵티마이저는 디스크에 임시 테이블을 만들지 않는 방향의 실행 게획 선택할 가능성이 높아집니다

• memory_temptable_create_cost와 memory_temptable_row_cost 비용을 높이면 MySQL 서버 옵티마이저는 메모리 임시 테이블을 만들지 않는 방향의 실행 계획 선택할 가능성이 높아집니다

• io_block_read_cost 비용이 높아지면 MySQL 서버 옵티마이저는 가능하면 innoDB 버퍼 풀에 데이터 페이지가 많이 적재돼 있는 인덱스를 사용하는 실행 계획 선택할 가능성이 높아집니다

• memory_block_read_cost 비용이 높아지면 innoDB버퍼 풀에 적재된 데이터 페이지가 상대적으로 적다고 하더라도 인덱스를 사용할 가능성이 높아집니다

2. 실행 계획 확인

실행 계획은 DESC 또는 EXPLAIN 명령으로 확인할 수 있습니다.

(1) 실행 계획 출력 포맷

FORMAT 옵션을 사용해서 실행 계획의 표시방법을 JSON 이나 TREE, 단순 테이블 형태로 선택할 수 있습니다.

(2) 쿼리 실행 시간 확인

쿼리의 실행 계획과 단계별 소요된 시간 정보를 확인할 수 있는 EXPLAIN ANALYZE 기능이 추가됐습니다. 항상 결과를 TREE 포맷으로 보여줍니다.
실행 계획만 추출하는게 아니라 실제 쿼리를 실행하고 사용된 실행계획과 소요된 시간을 보여줍니다.

(3) 실행 계획 분석

A. id 칼럼

B. select_type 칼럼

• SIMPLE
  UNION이나 서브쿼리를 사용하지않는 단순한 SELECT 쿼리인 경우 해당 쿼리 문장의 select_type은 SIMPLE로 표시됩니다. 일반적으로 제일 바깥 SELECT 쿼리의 select_type이 SIMPLE로 표시됩니다

• PRIMARY
  UNION이나 서브쿼리를 가지는 SELECT 쿼리의 실행 계획에서 가장 바깥쪽에 있는 단위 쿼리는 select_type이 PRIMARY로 표시됩니다. SIMPLE과 마찬가지로 select_type이 PRIMARY인 단위 SELECT 쿼리는 하나만 존재하며, 쿼리 지일 바깥쪽에 있는 SELECT 단위 쿼리가 PRIMARY로 표시됩니다.

• UNION
  SELECT 쿼리 가운데 첫 번째를 제외한 두 번째 이후 단위 SELECT 쿼리의 select_type은 UNION으로 표시됩니다.

• DEPENDENT UNION
  UNION select_type과 같이 UNION이나 UNION ALL로 집합을 결정하는 쿼리에서 표시됩니다.

• UNION RESULT
  UNION RESULT는 UNION 결과를 담아두는 테이블을 의미합니다.

• SUBQUERY
  select_type에 SUBQUERY는 FROM 절 이외에서 사용되는 서브쿼리만을 의미합니다.

• DEPENDENT SUBQUERY
  서브쿼리가 바깥쪽 SELECT 쿼리에서 정의된 칼럼을 사용하는 경우, select_type에 DEPENDENT SUBQUERY라고 표시됩니다.

• DERIVED
  DERIVED는 단위 SELECT 쿼리의 실행 결과로 메모리나 디스크에 임시 테이블을 생성하는 걸 의미합니다. select_type에 DERIVED인 경우 생성되는 임시 테이블을 파생 테이블이라고도 합니다.

• DEPENDENT DERIVED
  레터럴 조인 기능이 추가되면서 FROM 절의 서브쿼리에서도 외부 칼럼을 참조할 수 있게 됐습니다.

• UNCACHEABLE SUBQUERY
  하나의 쿼리 문장에 서브쿼리가 하나만 있더라도 실제 그 서브쿼리가 한 번만 실행되는게 아닙니다. 조건이 똑같은 서브쿼리가 실행될 때는 다시 실행하지 않고 이전의 실행 결과를 그대로 사용할 수 있게 서브쿼리의 결과를 내부 캐시공간에 담아둡니다.

• UNCACHEABLE UNION

• MATERIALIZED
  FROM 절이나 IN 형태의 쿼리에 사용된 서브쿼리의 최적화를 위해 사용됩니다.

C. table 칼럼

MySQL 서버 실행 계획은 단위 SELECT 쿼리 기준이 아니라 테이블 기준으로 표시됩니다.

D. partitions 칼럼

파티션 생성 시 제약 사항으로 인해 프라이머리 키에 emp_no칼럼과 함께 hire_date 칼럼을 추가해서 테이블을 생성했습니다.
테이블만 골라내는 과정을 '파티션 프루닝'이라고 합니다.

E. type 칼럼

MySQL 서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 나타냅니다.

• system
  레코드가 1건만 존재하는 테이블 또는 한 건도 존재하지않는 테이블을 참조하는 형태의 접근방법을 system이라고 합니다.

• const
  테이블의 레코드 건수와 관계없이 쿼리가 프라이머리 키나 유니크 키 칼럼을 이용하는 WHERE 조건절을 가지고 있으며, 반드시 1건을 반환하는 쿼리 처리방식을 const라고 합니다

• eq_ref
  여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시됩니다. 조인에서 처음 읽은 테이블 칼럼값을, 그 다음 읽어야 할 테이블의 프라이머리 키나 유니크 키 칼럼의 검색 조건에 사용할 때를 가리켜 eq_ref라고 합니다.

• ref
  eq_ref와 달리 조인의 순서와 관계없이 사용되며, 프라이머리 키나 유니크 키등 제약 조건이 없습니다. 인덱스의 종류와 상관없이 동등 조건으로 검색할 때 ref 접근 방법이 사용됩니다.

• fulltext
  fulltext는 전문 검색 인덱스를 사용해서 레코드를 읽는 접근방법을 의미합니다.

• ref_or_null
  ref 접근 방법과 같은데, NULL 비교가 추가된 형태입니다. 접근 방법 이름 그대로 ref방식 또는 NULL 비교 접근 방법을 의미합니다.

• unique_subquery
  WHERE 조건절에서 사용될 수 있는 IN 형태의 쿼리를 위한 접근 방법입니다. 서브쿼리에서 중복되지않는 유니크한 값만 반환할 때 이 접근 방법을 사용합니다.

• index_subquery
  IN 연산자의 특성상 IN(subquery) 또는 IN(상수 나열) 형태의 조건은 괄호 안에 있는 값의 목록에서 중복된 값이 먼저 제거돼야 합니다.

• range

• index_merge
  2개 이상의 인덱스를 이용해서 각각 검색 결과를 만들어낸 후에, 그 결과를 병합해서 처리하는 방식을 말합니다.
  - 여러 인덱스를 읽어야하므로 range 접근 방법보다 효율성이 떨어집니다
  - 전문 검색 인덱스를 사용하는 쿼리에서 index_merge가 적용되지않습니다.
  - index_merge 접근방법으로 처리된 결과는 2개 이상의 집합이 되기 때문에 두 집합의 교집합이나 합집합, 또는 중복 제거와 같은 부가적 작업이 더 필요합니다.
  

• index
  index접근 방법은 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔을 의미합니다.
  - range나 const, ref같은 접근 방법으로 인덱스를 사용하지 못한 경우
  - 인덱스에 포함된 칼럼만으로 처리할 수 있는 쿼리인 경우
  - 인덱스를 이용해 정렬이나 그루핑 작업이 가능한 경우
  

• ALL
  풀 테이블 스캔을 의미하는 접근 방법입니다. 테이블을 처음부터 끝까지 읽어서 불필요한 레코드를 제거하고 반환합니다. 가장 비효율적인 방법

F. possible_keys 칼럼

possible_keys 칼럼에 있는 내용은 옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 접근 방법에서 사용되는 인덱스의 목록일 뿐입니다.

G. key 칼럼

최종 선택된 실행 계획에서 사용하는 인덱스를 의미합니다. 쿼리를 튜닝할 때는 key 칼럼에 의도했던 인덱스가 표시되는지 확인하는 게 중요합니다.

H. key_len 칼럼

쿼리를 처리하기 위해서 다중 칼럼으로 구성된 인덱스에서 몇 개의 칼럼까지 사용했는지 알려줍니다

I. ref 칼럼

접근 방법이 ref면 참조 조건으로 어떤 값이 제공됐는지 보여줍니다.

J. rows 칼럼

rows 칼럼값은 실행 계획의 효율성 판단을 위해 예측했던 레코드 건수를 보여줍니다. rows 칼럼에 표시되는 값은 반환하는 레코드의 예측치가 아니라 쿼리를 처리하기 위해서 얼마나 많은 레코드를 읽고 체크해야하는지를 의미

K. filtered 칼럼

L. Extra 칼럼

• const row not found
  쿼리의 실행 계획에서 const접근 방법으로 테이블 읽었지만 해방 테이블에 레코드가 1건도 존재하지않으면 Extra 칼럼에 이 내용이 표시됩니다.

• Deleting all rows
  테이블의 모든 레코드를 삭제하는 핸들러 기능을 한번 호출함으로써 처리됐다는 걸 의미합니다.

• Distinct

• FirstMatch

• Full scan on NULL key

• Impossible HAVING
  쿼리에 사용된 HAVING 절의 조건을 만족하는 레코드가 없을 때 실행 계획의 Extra 칼럼에 'Impossible HAVING' 키워드가 표시됩니다.

• Impossible WHERE

• LooseScan

• No matching min/max row
  MIN() 이나 MAX()와 같은 집합 함수가 있는 쿼리의 조건절에 일치하는 레코드가 한 건도 없을 때 Extra칼럼에 No matching min/max row 라는 메세지가 출력됩니다.

• No matching rows after partition pruning
  파티션된 테이블에 대한 UPDATE 또는 DELETE 명령의 실행 계획에서 표시될 수 있는데, 해당 파티션에서 UPDATE하거나 DELETE 할 대상 레코드가 없을 때 표시됩니다.

• No tables used
  FROM 절이 없는 쿼리 문장이나 'FROM DUAL' 형태의 쿼리 실행 계획에서 Extra 칼럼에 'No tables used'라는 메세지가 출력됩니다.

• Not exists
  프로그램을 개발하다보면 A테이블에는 존재하지만 B테이블에는 없는 값을 조회해야 하는 쿼리가 자주 사용됩니다. 이럴 때 NOT IN 형태나 NOT EXISTS 연산자를 주로 사용합니다. 이러한 형태의 조인을 안티-조인이라고 합니다.

• Plan isn't ready yet
  다른 커넥션에서 실행 중인 쿼리 실행 계획 살펴볼 수 있습니다.

• Range checked for each record

• Recursive
  CTE(Common Table Expression)을 이용해서 재귀 쿼리를 작성할 수 있게 됐습니다.

• Rematerialize
  레터럴로 조인되는 테이블은 선행 테이블의 레코드별로 서브쿼리를 실행해서 그 결과를 임시 테이블에 저장합니다.

• Select tables optimized away

• unique row not found
  두 개의 테이블이 각각 유니크 칼럼으로 아우터 조인을 수행하는 쿼리에서 아우터 테이블에 일치하는 레코드가 존재하지않을 때 Extra 칼럼에 이 커멘트가 표시됩니다.

• Using filesort
  ORDER BY를 처리하기 위해 인덱스를 이용할 수도 있지만 적절한 인덱스를 사용하지 못할 때는 MySQL 서버가 조회된 레코드를 다시 한번 정렬해야합니다. ORDER BY 처리가 인덱스를 사용하지 못할 때만 실행 계획의 Extra 칼럼에 'Using filesort'코멘트가 표시되며, 이는 조회된 레코드를 정렬용 메모리 버퍼에 복사해 퀵 소트 또는 힙 소트 알고리즘을 이용해 정렬을 수행하게 된다는 의미입니다.

• Using index(커버링 인덱스)
  데이터 파일을 전혀 읽지않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있을 때 Extra칼럼에 'Using index'가 표시됩니다.

• Using index condition

• Using index for group-by
  GROUP BY 처리를 위해 MySQL 서버는 그루핑 기준 칼럼을 이용해서 정렬 작업을 수행하고 다시 정렬된 결과를 그루핑하는 형태 고부하 작업을 필요로 합니다. 하지만 GROUP BY 처리가 인덱스를 이용하면 정렬된 인덱스 칼럼을 순서대로 읽으면서 그루핑 작업만 수행합니다. GROUP BY 처리에 인덱스를 이용하면 레코드 정렬이 필요하지 않고 인덱스의 필요한 부분만 읽으면 되기 떄문에 효율적으로 빠르게 처리됩니다.
  - 타이트 인덱스 스캔을 통한 GROUP BY 처리
  - 루스 인덱스 스캔을 통한 GROUP BY 처리

• Using index for skip scan

• Using join buffer

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

• Using sort_union(), Using union(), Using intersect()

• Using temporary
  MySQL 서버에서 쿼리를 처리하는 동안 중간 결과를 담아 두기 위해서 임시테이블을 사용합니다.

• Using where

• Zero limit