10. 실행 계획

통계 정보

테이블 및 인덱스 통계 정보

MySQL 서버의 통계 정보

통계 정보는 innodb_index_stats and innodb_table_stats 테이블에 관리, 옵션을 통해 메모리에 저장할 수 있음

다음과 같은 이벤트가 발생하면 자동으로 통계 정보가 갱신된다.

• 테이블이 새로 오픈되는 경우
• 테이블의 레코드가 대량으로 변경되는 경우 (약 1/16)
• ANALYZE TABLBE 명령이 실행되는 경우
• SHOW TABLE STATS or SHOW INDEX FROM 명령이 실행되는 경우
• InnoDB 모니터가 활성화되는 경우
• innodb_stats_on_metadata 시스템 설정이 ON인 상태에서 SHOW TABLE STATS 명령이 실행되는 경우

자주 통계 정보가 갱신되면 레인지 스캔으로 잘 처리되던 쿼리가 풀 스캔으로 실행되는 상황이 발생할 수 있다. innodb_stats_auto_recalc 설정을 통해 자동으로 갱신되는 것을 막을 수 있다.

더 정확한 통계 정보 수집에는 많은 시간이 소요되겠지만, 통계 정보의 정확성에 의해 성능이 결정되기 때문에 충분한 가치가 있다. innodb_stats_persistent_sample_pages를 통해 높은 값을 설정하면 더 정확한 통계 값을 수집할 수 있다.

히스토그램

MySQL 5.7 버전까지의 통계 정보는 단순히 인덱스된 칼럼의 유니크한 값의 개수 정도만 가지고 있었는데, 이는 옵티마이저가 최적의 실행을 수립하기에는 많이 부족했다. MySQL 8.0부터는 칼럼의 데이터 분포도를 참조할 수 있는 히스토그램 정보를 활용할 수 있다.

히스토그램 정보 수집 및 삭제

히스토그램 정보는 칼럼 단위로 관리되는데, 자동으로 수집되지 않고 ANALYZE TABBLE ... UPDATE HISTOGRAM 명령을 실행해 수동으로 수집 및 관리된다. 수집된 정보는 딕셔너리에 함께 저장되고 MySQL 시작될 때 column_statistics 테이블로 로드한다.

• Singleton 히스토그램 : 칼럼값 개별로 레코드 건수를 관리하는 히스토그램
• Equi-Height 히스토그램 : 칼럼값의 범위를 균등한 개수로 구분해서 관리하는 히스토그램

히스토그램은 버킷(Bucket) 단위로 구분되어 레코드 건수나 칼럼 값의 범위가 관리되는데, Singleton은 칼럼이 가지는 값별로 버킷이 할당되고, Equi-Height 개수가 균등한 칼럼 값의 범위별로 하나의 버킷이 할당된다.

히스토그램의 용도

히스토그램 정보가 없으면 옵티마이저는 데이터가 균등하게 분포돼 있을 것으로 예측한다. 하지만 히스토그램이 있으면 특정 범위의 데이터가 많고 적음을 식별할 수 있다. 각 칼럼에 대해 히스토그램 정보가 있으면 어느 테이블을 먼저 읽어야 조인의 횟수를 줄일 수 있을지 옵티마이저가 더 정확히 판단할 수 있다.

코스트 모델

MySQL 서버는 쿼리에 대해 다양한 작업이 얼마나 필요한지 예측하고 전체 비용을 계산한 결과를 바탕으로 최적의 실행 계획을 찾는다. 이렇게 필요한 작업들의 비용을 코스트 모델이라고 한다.

서버의 소스 코드에 상수화돼 있던 각 단위 작업의 비용을 DBMS 관리자가 조정할 수 있다. 코스트 모델은 2개 테이블에 저장돼 있는 설정값을 사용한다.

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

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

• key_compare_cost
  비용을 높이면 옵티마이저가 가능하면 정렬을 수행하지 않는 방향의 실행 계획을 선택할 가능성이 높아진다.
• row_evaluate_cost
  비용을 높이면 풀 스캔을 실행하는 쿼리들의 비용이 높아지고, 옵티마이저는 가능하면 인덱스 레인지 스캔을 사용하는 계획을 선택할 가능성이 높아진다.
• disk_temptable_create_cost and disk_tesktable_row_cost
  비용을 높이면 옵티마이저는 디스크에 임시 테이블을 만들지 않는 방향의 실행 계획을 선택할 가능성이 높아진다.
• memory_temptable_create_cost and memory_temptable_row_cost
  비용을 높이면 옵티마이저는 메모리 임시 테이블을 만들지 않는 방향으로 가능성이 높아진다.
• io_block_read_cost
  비용이 높아지면 옵티마이전는 가능한 버퍼 풀에 페이지가 많이 적재돼 있는 인덱스를 사용하는 계획으로 가능성이 높아진다.
• memory_block_read_cost
  비용이 높아지면 버퍼 풀에 적재된 페이지가 상대적으로 적다고 하더라고 그 인덱스를 사용할 가능성이 높아진다.

실행 계획 확인

쿼리의 실행 시간 확인

쿼리의 실행 계획과 단계별 소요된 시간 정보를 확인할 수 있는 EXPLAIN ANALYZE 기능이 있고 TREE 포맷으로 실행 계획에서 들여쓰기는 호출 순서를 의미하며, 다음 기준으로 읽으면 된다.

• 들여쓰기가 같은 레벨에서는 상단에 위치한 라인이 먼저 실행
• 들여쓰기가 다른 레벨에서는 가장 안쪽에 위치한 라인이 먼저 실행

EXPLAIN ANALYZE 명령은 EXPLAIN 명령과 달리 실행 계획만 추출하는 것이 아니라 실제 쿼리를 실행하고 사용된 실행 계획과 소요된 시간을 보여주는 것이다.

실행 계획 분석

• 각 라인은 사용된 테이블(임시 테이블 포함) 개수만큼 출력
• 실행 순서는 위에서 아래로 순서대로 표시된다.
• 출력된 실행 계획에서 위쪽에 출력된 결과일수록(id 값이 작을수록) 쿼리의 Outer 부분이거나 먼저 접근한 테이블이다.

id 칼럼

단위 SELECT 쿼리별로 부여되는 식별자 값이다. JOIN되는 경우에는 id 값이 증가하지 않고 같은 값으로 부여된다. id 칼럼이 테이블의 접근 순서를 의미하지 않는다는 것을 주의 해야한다.

select_type 칼럼

select_type	설명
SIMPLE	UNION 이나 서브쿼리를 사용하지 않는 단순한 SELECT 쿼리인 경우. 쿼리 문장이 아무리 복잡해서 SIMPLE인 단위 쿼리는 하나만 존재한다.
PRIMARY	서브쿼리를 가지는 SELECT 쿼리의 실행 계획에서 가장 Outer에 있는 단위 쿼리는 PRIMARY 로 표시된다.
UNION	UNION으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두 번째 이후는 UNION으로 표시된다. 첫 번째는 전체 쿼리를 대표하는 select_type으로 설정됐다. UNION ALL인 경우 임시 테이블을 만들어서 사용함으로 첫 번째 쿼리는 DERIVED 갖는다.
DEPENDENT UNION	UNION or UNION ALL로 집합하는 쿼리에서 표시된다. 내부 쿼리가 외부의 값을 참조해서 처리될 때 키워드가 표시된다.
UNION RESULT	UNION 결과를 담아두는 테이블, 별도의 id 값은 부여되지 않는다.
SUBQUERY	FROM절 이외에서 사용되는 서브쿼리만을 의미한다.
DEPENDENT SUBQUERY	서브쿼리가 Outer SELECT 쿼리에서 정의된 칼럼을 사용하는 경우. 외부 쿼리가 먼저 수행된 후 내부 쿼리가 실행돼야 하므로 일반 서브쿼리보다는 처리 속도가 느릴 때가 많다.
DERIVED	FROM 절에 사용된 SELECT 실행 결과로 메모리나 디스크에 임시 테이블을 생성하는 것을 의미한다.
MATERIALIZED	ROM 절이나 IN 형태의 쿼리에 사용된 서브쿼리의 최적화를 위해 사용된다.

table 칼럼

실행 계획은 단위 SELECT 쿼리 기준이 아니라 테이블 기준으로 표시된다. 테이블의 이름에 멸칭이 부여된 경우에는 별칭이 표시된다. </dereived N> or </union M,N> 과 같이 <>는 임시 테이블을 의미한다. 안에 숫자는 id 값을 지칭한다.

partitions 칼럼

READ 된 파티션 표시

type 칼럼

type	설명
system	레코드가 1건만 존재하는 테이블 또는 한 건도 존재하지 않는 테이블을 참조하는 형태의 접근 방법
const	쿼리가 PK or Unique Key 칼럼을 이용하는 WHERE 조건절을 가지고 있으며 반드시 1건만 반환하는 방식
eq_ref	여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시 조인에서 PK or Unique key 칼럼의 검색 조건에 사용될 때 두 번째 이후에 읽는 테이블의 type 칼럼에 표시
ref	조인의 순서와 관계없이 사용
fulltext	Full-text-Search 인덱스를 사용해 레코드를 읽는 접근 방법
ref_or_null	ref 에 NULL 비교가 추가된 형태
unique_subquery	WHERE 조건절에서 IN 형태의 쿼리를 위한 접근 방법 중복되지 않는 유니크한 값만 반환할 때
index_subquery	IN 형태의 조건은 괄호 안 중복된 값을 인덱스를 이용해 제거할 수 있을 때
range	<, >, IS NULL, BETWEEN, IN, LIKE 연산자를 이용해 인덱스를 검색하고자 사용
index_merge	2개 이상의 인덱스를 이용해 각각의 검색 결과를 만든 후, 그 결과를 병합해서 처리하는 방식
index	인덱스를 처음부터 끝까지 읽는 것
ALL	테이블을 처음부터 끝까지 읽는 것

possible_keys 칼럼

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

key 칼럼

실행 계획에서 사용하는 인덱스를 의미
index_merge는 1개 이상의 인덱스가 표시된다.

key_len 칼럼

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

ref 칼럼

type이 ref면 참조 조건으로 어떤 값이 제공됐는지 보여준다.

rows 칼럼

실행 계획의 효율성을 위해 예측했던 레코드 건수를 보여준다. 통계 정보를 참조해 옵티마이저가 산출해 낸 예상값이다.

Extra 칼럼

쿼리의 실행 계획에서 성능에 관련된 중요한 내용이 Extra 칼럼에 자주 표시된다.

Extra	설명
const row not found	const 접근 방법으로 테이블을 읽었지만 1건도 존재하지 않는 경우
Distinct	해당 하는 칼럼에 중복 없이 유니크하게 가져오는 경우
FirstMatch	테이블에서 첫 번째로 일치하는 한 건만 검색한다는 것
Full scan on NULL key	NULL 데이터로 인해 서브쿼리 테이블에 대해서 풀 테이블 스캔을 사용할 것
Impossible HAVING	쿼리에 사용된 HAVING 절의 조건을 만족하는 레코드가 없을 때
Impossible WHERE	WHERE 조건이 항상 FALSE가 될 수밖에 없는 경우
LooseScan	LooseScan 최적화 전략이 사용된 경우
No matching min/max row	MIN() or MAX() 집합 함수가 있는 쿼리의 조건절에 일치하는 레코드가 한 건도 없을 때
No matching row in const table	조인에 사용된 테이블에서 const 방법으로 접근할 때 일치하는 레코가 없다면
No matching rows after partition pruning	파티션에서 UPDATE or DELETE할 대상 레코드가 없을 때
No tables used	FROM DUAL 형태의 쿼리에서 출력
Not exists	테이블 조인 조건에 일치하는 레코드가 여러 건이 있다고 하더라도 1건만 조회해보고 처리를 완료
Plan isn't ready yet	실행 계획을 수립하지 못한 상태
Range checked for each record	레코드마다 인덱스 레인지 스캔을 체크
Recursive	재귀 쿼리
Rematerialize	레터럴 조인되는 테이블은 선행 테이블의 레코드별로 서브쿼리를 실행해서 임시 테이블로 저장
SELECT tables optimezd away	MIN() or MAX()로 만 SELECT or GROUP BY로 MIN() or MAX()를 조회하는 쿼리가 인덱스를 오름차순 또는 내림차순으로 1건만 읽는 형태의 최적화
Start Temporary, End temporary	세미 조인 최적화 중에서 Duplicate Weed-out 최적화 전략이 사용
Unique row not found	2 개의 테이블이 각각 유니크(PK 포함) 칼럼으로 OUTER JOIN 수행하는 쿼리에서 일치하는 레코드가 없을 때
Using filesort	ORDER BY 처리가 인덱스를 사용하지 못할 때
Using index	데이터 파일을 읽지 않고 인덱스만 읽어서 처리할 수 있을 때
Using index condition	옵티마이저가 Index condition pushdown 최적화를 사용할 때
Using index for group-by	GROUP BY 처리가 인덱스를 이용할 때
Using index for skip scan	옵티마이저가 Index skip scan 최적화를 사용할 때
Using join buffer	실행 계획에서 조인 버퍼가 사용되는 경우
Using sort_union Using union Using interesct	index_merge 접근 방법으로 실행되는 경우
Using temporary	쿼리를 처리하는 동안 중간 결과를 담아 두기 위해 사용하는 임시 테이블
Using where	MySQL 엔진에서 별도의 가공을 해서 필터링 작업을 처리한 경우
Zero limit	실제 테이블 레코드는 읽지 않고 메타 정보만 반환