[MySQL] 통계 정보

목차

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

2. 히스토그램

3. 코스트 모델

 

 

 

MySQL 8.0 버전부터 인덱스되지 않은 컬럼들에 대해서도 데이터 분포도를 수집해서 저장하는 히스토그램 정보가 도입됐다. 쿼리를 처리하는데 필요한 세부 작업들의 비용은 코스트 모델로 관리하고 옵티마이저는 테이블 및 인덱스의 통계 정보와 히스토그램을 통해 쿼리 전체의 비용을 계산하고 실행 계획을 수립한다.

 

 

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

비용 기반 최적화에서 가장 중요한 것은 통계 정보이다.

 

(규칙 기반 최적화 vs 비용 기반 최적화)

규칙 기반 최적화(RBO)는 기본적으로 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식이다. 통계 정보를 조사하지 않고 실행 계획이 수립되기 때문에 같은 쿼리에 대해서는 항상 거의 같은 실행 방법을 만들어 낸다. 오래전부터 사용하지 않는 방식이다.

 

비용 기반 최적화(CBO)는 쿼리를 처리하기 위한 여러가지 가능한 방법을 만들고, 각 단위 작업의 비용 정보와 대상 테이블의 예측된 통계 정보를 이용해 실행 계획별 비용을 산출한다. 이렇게 산출된 실행 방법별로 비용이 최소로 소요되는 처리 방식을 선택해 최종적으로 쿼리를 실행한다.

 

통계 정보 관리 테이블

MySQL 5.6 버전부터 각 테이블의 통계 정보를 mysql 데이터베이스의 테이블로 관리할 수 있게 개선됐다.

• innodb_index_stats
• innodb_table_stats

 

● innodb_index_stats

index_name	stat_name	stat_value	sample_size	stat_description
PRIMARY	n_diff_pfx01	299202	20	emp_no
PRIMARY	n_leaf_pages	886	NULL	Number of leaf pages in the index
PRIMARY	size	929	NULL	Number of pages in the index

• stat_name='n_diff_pfx%' : 인덱스가 가진 유니크한 값의 개수
• stat_name='n_leaf_pages' : 인덱스의 리프 노드 페이지 개수
• stat_name='size' : 인덱스 트리의 전체 페이지 개수

 

● innodb_index_stats

n_rows	clustered_index_size	sum_of_other_index_sizes
299202	929	642

• n_rows : 테이블의 전체 레코드 건수
• clustered_index_size : 프라이머리 키의 크기 (innoDB 페이지 개수)
• sum_of_other_index_sizes : 프라이머리 키를 제외한 인덱스의 크기 (innoDB 페이지 개수)

 

통계 정보 영구 보관

테이블을 생성할 때 STATS_PERSISTENT 옵션으로 통계 정보를 보관을 할지 설정할 수 있다.

• STATS_PERSISTENT=0 : 통계 정보 관리 테이블에 저장하지 않음
• STATS_PERSISTENT=1 : 통계 정보 관리 테이블에 저장함
• STATS_PERSISTENT=DEFAULT : innodb_stats_persistent 시스템 변수의 값으로 결정

innodb_stats_persistent 시스템 설정으로 통계 정보를 영구적 또는 단기적으로 보관하도록 설정할 수 있다.

• innodb_stats_persistent=0 : 통계 정보를 단기적으로 보관
• innodb_stats_persistent=1 : 통계 정보를 영구적으로 보관

 

통계 정보 갱신

다음의 경우에 통계 정보가 갱신된다.

• 테이블이 새로 오픈되는 경우
• 테이블의 레코드가 대량으로 변경되는 경우(테이블 레코드의 1/6 정도가 INSERT / UPDATE / DELETE)
• ANALYZE TABLE 명령이 실행되는 경우
• SHOW TABLE STATUS 명령이나 SHOW INDEX FROM 명령이 실행되는 경우
• innoDB 모니터가 활성화되는 경우
• innodb_stats_on_metadata 시스템 설정이 ON인 상태에서 SHOW TABLE STATUS 명령이 실행되는 경우

 

통계 정보 자동 갱신

통계 정보가 자동으로 자주 갱신되면 쿼리가 의도와 다르게 동작할 수 있기 때문에 innodb_stats_auto_recalc 변수의 값을 OFF로 설정하면 자동 갱신을 막을 수 있다.

• STATS_AUTO_RECALC=1 : 테이블의 통계 정보를 자동 수집
• STATS_AUTO_RECALC=0 : 테이블의 통계 정보는 ANALYZE TABLE 명령을 실행할 때만 수집
• STATS_AUTO_RECALC=DEFAULT : 테이블의 통계 정보 수집을 innodb_stats_auto_recalc 변수의 값으로 결정

 

통계 정보 수집시 페이지 샘플링

테이블의 통계 정보를 수집할 때 몇 개의 innoDB 테이블 블록을 샘플링 할지 설정할 수 있다.

• innodb_stats_transient_sample_pages : 기본값 8, 자동으로 통계 정보 수집이 실행될 때 임의로 8개의 페이지만 샘플링해서 분석하고 통계 정보로 활용
• innodb_stats_persistent_sample_pages : 기본값 20, ANALYZE TABLE 명령이 실행되면 임의로 20개의 페이지만 샘플링해서 분석하고 통계 정보로 활용

 

2. 히스토그램

MySQL 8.0 부터 실행 계획을 수립할 때 통계 정보와 함께 컬럼의 데이터 분포도를 참조할 수 있는 히스토그램 정보를 활용한다.

 

히스토그램 정보 수집

히스토그램 정보는 ANALYZE TABLE ... UPDATE HISTOGRAM 명령을 이용해 수동으로 수집하며 시스템 딕셔너리에 컬럼 단위로 저장되어 관리된다. 또한, MySQL 서버가 시작될 때 이 정보를 information_schema 데이터베이스의 column_statistics 테이블로 로드하기 때문에 이 테이블에서 참조할 수 있다.

 

ANALYZE TABLE employees.employees
UPDATE HISTOGRAM ON gender, hire_date;

SELECT *
FROM COLUMN_STATISTICS
WHERE SCHEMA_NAME = 'employees'
AND TABLE_NAME = 'employees'\G;

 

히스토그램 종류

● Singleton(싱글톤 히스토그램) : 컬럼값 개별로 레코드 건수를 관리. (Value-Based 히스토그램 또는 도수 분포)

누적된 레코드 건수의 비율을 갖기 때문에 gender 가 'F'인 레코드의 비율은 (1 - 'M'의 비율) 이다.

유니크한 값의 개수가 상대적으로 적은 경우 사용된다.

 

 

● Equi-Height(높이 균형 히스토그램) : 컬럼값의 범위를 균등한 개수로 구분해서 관리. (Height-Balanced 히스토그램)

컬럼값의 각 범위에 대해 레코드 건수 비율이 누적으로 표시되기 때문에 그래프의 기울기가 일정하면 각 범위가 비슷한 값(레코드 건수)을 가진다는 것이다.

 

히스토그램 정보 삭제

히스토그램의 삭제 작업은 딕셔너리의 내용만 삭제하기 때문에 다른 쿼리 처리의 성능에 영향을 주지 않는다.

히스토그램이 삭제되면 쿼리의 실행 계획이 달라질 수 있기 때문에 주의해야 한다.

ANALYZE TABLE employees.employees
DROP HISTOGRAM ON gender, hire_date;

 

히스토그램의 용도

실제 응용 프로그램의 데이터는 항상 균등한 분포도를 가지지 않기 때문에 테이블과 인덱스의 통계 정보만으로는 부족하다. 히스토그램은 특정 컬럼이 가지는 모든 값에 대한 분포도 정보를 가지지는 않지만 각 범위(버킷)별로 레코드의 건수와 유니크한 값의 개수 정보를 가지기 때문에 훨씬 정확한 예측을 할 수 있다.

 

히스토그램과 인덱스

MySQL 8.0에서 인덱스된 컬럼을 검색 조건으로 사용하는 경우 그 컬럼의 히스토그램은 사용하지 않고 실제 인덱스 다이브를 통해 직접 수집한 정보를 활용한다. 따라서 히스토그램은 주로 인덱스되지 않은 컬럼에 대한 데이터 분포도를 참조하는 용도로 사용된다.

(인덱스 다이브)

옵티마이저는 쿼리의 실행 계획을 수립할 때 사용 가능한 인덱스들로부터 조건절에 일치하는 건수를 예측하기 위해 실제 인덱스의 B-Tree를 샘플링해서 살펴본다. 이 작업을 인덱스 다이브라고 하며 항상 히스토그램보다 정확한 결과를 기대할 수 있다.

 

 

3. 코스트 모델

MySQL 서버는 쿼리를 처리하는데 다음과 같은 다양한 작업들이 얼마나 필요한지 예측하고 전체 작업의 비용을 계산해 최적의 실행 계획을 찾는다. 이 때 단위 작업들의 비용을 코스트 모델이라고 한다.

• 디스크로부터 데이터 페이지 읽기
• 메모리(InnoDB 버퍼 풀)로부터 데이터 페이지 읽기
• 인덱스 키 비교
• 레코드 평가
• 메모리 임시 테이블 작업
• 디스크 임시 테이블 작업

 

코스트 모델 테이블

MySQL 8.0 서버의 코스트 모델은 mysql DB에 존재하는 다음 2개 테이블에 저장돼 있는 설정값을 사용한다.

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

 

● server_cost

• cost_name : 코스트 모델의 각 단위 작업
• default_value : 각 단위 작업의 비용(기본값이며 MySQL 서버 소스 코드에 설정된 값)
• cost_value : DBMS 관리자가 설정한 값(NULL이면 default_value 컬럼의 비용 사용)
• last_update : 단위 작업의 비용이 변경된 시점
• comment : 비용에 대한 추가 설명

 

● engine_cost

engine_cost 테이블은 위의 5개 컬럼에 추가로 2개의 컬럼을 더 가진다.

이 테이블을 이용해 스토리지 엔진별로 단위 작업의 비용을 달리 설정할 수 있다.

• engine_name : 비용이 적용된 스토리지 엔진
• device_type : 디스크 타입

 

코스트 모델 작업 단위

	cost_name	default_value	설명
server_cost	disk_temptable_create_cost	20.00	디스크 임시 테이블 생성
	disk_temptable_row_cost	0.50	디스크 임시 테이블의 레코드 읽기
	key_compare_cost	0.05	인덱스 키 비교
	memory_temptable_create_cost	1.00	메모리 임시 테이블 생성
	memory_temptable_row_cost	0.10	메모리 임시 테이블의 레코드 읽기
	row_evaluate_cost	0.10	레코드 비교
engine_cost	io_block_read_cost	1.00	디스크 데이터 페이지 읽기
	memory_block_read_cost	0.25	메모리 데이터 페이지 읽기

 

코스트 모델 작업 단위의 비용 조절 예

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

 

 

 

 

 

참고

Real MySQL 8.0 (1권)